SQL Server

Microsoft SQL Server 2017 powers your entire data estate by supporting structured and unstructured data sources. It builds on previous versions of SQL Server, which have been industry leading for four years in a row and a leader in TPC-E. It scales to petabytes of data and allows customers to process big data through PolyBase using T-SQL over any data. SQL Server has also been the least vulnerable database during the last seven years. SQL Server 2017 brings data insights with business intelligence capabilities that provide analytics at a fraction of the cost on any device along with advanced analytics with support for R and Python.

More details about SQL Server are available in the SQL Server documentation.
If you have a technical issue, please open a post on the developer forums through Stack Overflow or MSDN.

  • Hot ideas
  • Top ideas
  • New ideas
  • My feedback
  1. Correctly escape predefined entities in deadlock XML

    If you create a table that has &, ", <, or > in the name, it's not correctly escaped in all instances of deadlock XML.

    This causes an error when trying to convert extended event/system health data to valid XML for analysis.

    https://en.wikipedia.org/wiki/List_of_XML_and_HTML_character_entity_references

    99 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  1 comment  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  2. Select All option for File Import Wizard

    The Flat File Import Wizard should have a Select/Unselect All option on Allow NULLs

    2 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    under review  ·  Matteo Taveggia responded

    Thanks for the suggestion. We’ll take a look at it and prioritize accordingly.

    Thanks,
    -Matteo

  3. Enable Query Store for collection on a read-only replica in an Availability Group

    Currently, Query Store can only be enabled for the read-write database in Availability Group. As many customers issue queries against the read-only replicas, capturing query and performance metrics for those queries would be beneficial for understanding the workload, troubleshooting performance issues, etc.

    255 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    14 comments  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
  4. Replication and Availability Groups with per database DTC support

    In SQL Server 2017, when using Availability Groups with Per-Database DTC Support enabled, you are unable to configure replication.

    The error received is:
    Msg 3933, Level 16, State 1
    Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction.

    By design, per db DTC support does not allow save points. However, Replication system stored procedures make extensive use of save points. As a result, this creates a conflict where you cannot have a database in an AG with both Replication and per DB DTC support.

    As a workaround, you must disable per…

    102 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  12 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  5. SQL Server 2017 Minimal Logging Not Behaving As Documented

    According to my interpretation of the docs (https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008/dd425070(v=sql.100)) the following scenarios should minimally log...

    Recovery model must be Simple or Bulk Logged
    Table must be either
    A heap and insert done with TABLOCK
    A heap + nonclustered index with TABLOCK and either trace flag 610 or SQL Server 2016+
    A clustered index with no data and TABLOCK
    A clustered index with no data and either trace flag 610 or SQL Server 2016+
    A clustered index with data and trace flag 610 or SQL Server 2016+
    A clustered index with nonclustered indexes and TABLOCK and trace flag 610 or…

    48 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  6. Use column aliases directly in SELECT clause

    It would be nice if queries like this were possible:

    SELECT
    A AS B
    ,C AS D
    ,D-B AS E
    FROM table

    In other words, use column directly in the SELECT clause where they are defined. Other database vendors have implemented this, so I assume it's not rocket science.

    Right now, we have to use subqueries just to define an extra calculation.

    164 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  16 comments  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
  7. SSIS Script tasks losing code

    I have a very strange issue happening that is causing Script Task code to clear out. I have been able to test on 2-3 different machines. We are running SSDT 15.4 preview. The steps to reproduce were as follows.

    1. Create a script task inside of a foreach loop container. 2. Create a comment in the script task. 3. Change or add a variable mapping in the foreach. 4. Save package. 5. Open the script task and the comment will have vanished.

    As my last attempt for success,I have upgraded to 15.5.1 and the problem still exists.

    90 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  17 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  8. Add a -connectionstring parameter to Read-SQLTableData / Write-SQLTableData commands

    Given that the commands were based off of Chad Miller's original code, I noticed that the connection strings were essentially hard coded in for easier use. The problem with this is that from a security perspective, there might be set connectionstrings required from the Azure Portal that should be used.

    3 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    2 comments  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
  9. SQL 2017 CU7 - using In-Memory OLTP table variable inside MSTF causes error

    The code from attached file causes error as:
    Msg 3628, Level 16, State 1, Line 39
    The Database Engine received a floating point exception from the operating system while processing a user request. Try the transaction again. If the problem persists, contact your system administrator.

    30 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  6 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  10. Microsoft.SqlServer.SqlManagementObjects library not working as expected in .net core

    var sql = new Microsoft.SqlServer.Management.Smo.Server();
    sql.ConnectionContext.LoginSecure = true;
    sql.ConnectionContext.ConnectAsUser = true;
    sql.ConnectionContext.ConnectAsUserName = "DomainUser";
    sql.ConnectionContext.ConnectAsUserPassword = "Password";
    sql.ConnectionContext.ServerInstance = @"Server\Instance";
    sql.ConnectionContext.Connect();

    above code should connect sql server with different user, but it always taking credential of currently logged in user. Its working fine with .Net Framework, only having issues with .Net Core.

    1 vote
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  11. TRY-CATCH should always work

    Consider this:

    CREATE PROCEDURE inner_sp AS
    BEGIN TRY
    PRINT 'inner_sp starting'
    SELECT col FROM doesnotexist
    PRINT 'inner_sp stopping'
    END TRY
    BEGIN CATCH
    PRINT 'Let''s catch the error: ' + error_message()
    END CATCH
    go
    SET XACT_ABORT OFF
    EXEC inner_sp

    It's reasonable to assume that this would print:

    Let's catch the error: Invalid object name 'doesnotexist'.

    But in fact the output is

    inner_sp starting
    Msg 208, Level 16, State 1, Procedure inner_sp, Line 4
    Invalid object name 'doesnotexist'

    That is, the CATCH handler is not activated. This is true for all binding errors that occurs at run time. Note that this includes…

    65 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  3 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  12. Columnstore REORGANIZE command increases modification_counter on table statistics

    Reorganizing a columnstore index can, in some cases, cause the modification_counter on the table's statistics to increase. This is unexpected because while the underlying structure of the table is being changed, the data itself isn't being modified. This can lead to issues where SQL is automatically updating statistics the next time a query is executed against the table in cases where the modification_counter increases a significant amount because of the REORGANIZE. This has been particularly problematic for our large fact table with 150+ billion rows and full scan statistics across 40+ columns.

    Attached is a reproduction script with three examples:

    21 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  13. Generate scripts feature returns an error on 18.1 (15.0.18131.1)

    Whenever I try to generate db scripts for any of dbs on the server I got an error (attached on this post).

    Running SSMS 18.1 over Microsoft SQL Server 2014 (SP2-GDR) (KB4019093) - 12.0.5207.0 (X64)

    1 vote
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    under review  ·  Matteo Taveggia responded

    Thanks for the suggestion. We’ll take a look at it and prioritize accordingly.

    Thanks,
    -Matteo

  14. Ability to run specific SQL Agent job steps

    Occasionally a user may need to run only specific steps of an agent job, or need the job stop after a certain step.

    While SQL Server Agent currently provides the ability to "Start job at step", this is all the flexibility the user has at runtime. Anything beyond that requires the job to be edited.

    It would be useful for those with permission only to run (not edit) jobs to have greater flexibility at runtime. E.g. a screen which allows the user to choose which steps to run or skip for that particular execution.

    26 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  1 comment  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
  15. Add setting so LEN counts trailing whitespace

    Maybe there is a historic reason why LEN('a') and LEN('a ') return the same number.

    But that's stupid.

    The internet is awash with people like me who were stunned to discover this counter intuitive quirk (Google it).

    None of the solutions to this problem are particularly elegant or efficient:
    https://stackoverflow.com/questions/2025585/len-function-not-including-trailing-spaces-in-sql-server/2025587

    I'm assuming since this has been baked in for ages, we can't change default behavior.

    So how about:
    - Add a parameter to the LEN function to count white space
    - Add an SQL Option that more generally keeps SQL Server's hands off my white space - everywhere

    21 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  5 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  16. SQL 2017: "SET FMTONLY ON" doesn't return any resultset on UDF w/ COMPATIBILITY_LEVEL=140

    Our application uses at various places the SchemaOnly attribute (see https://msdn.microsoft.com/en-us/library/system.data.commandbehavior(v=vs.110).aspx). Lately, we noticed some strange errors when the application runs against a DB on SQL Server 2017. I boiled the issue down to the attached repro script in T-SQL, that shows clearly that SQL Server doesn' return any metadata, if a table-valued function is called using SET FMTONLY ON with COMPATIBILITY_LEVEL=140. But it works as expected using COMPATIBILITY_LEVEL=130. As I didn't find any word on this breaking change in the docs, I consider this a bug.

    20 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  5 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  17. Column Store Deadlock XML Is Missing Information

    Deadlock XML for column store indexes is missing several pieces of information about which objects were deadlocked in the resource-list section of the XML

    See here for background https://www.brentozar.com/archive/2018/04/column-store-deadlocks-missing-information/

    Also attaching files.

    55 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  0 comments  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
  18. Bug in SSMS on Statistics Properties screen

    There is a "Update statistics for these columns" checkbox In SQL Management Studio in Statistic Properties dialog.

    However when I click on this checkbox and press OK it has no effect what so ever. Statistics are not updated and when I try to script the action I get "There is no action to be scripted".

    1 vote
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  19. Import Flat File wizard, add check box for file does not include header row.

    When importing a csv without a header row, it's removing the first row of data. It would be nice if the wizard had a option to say that this file does not contain a header row. The modify columns already allows for adjusting the column names but it still removed the first row of the .csv.

    1 vote
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  20. SET STATISTICS IO should also show schema name for tables

    When you use schemas in your database, and you have the same table names in multiple schemas, it is not clear, which schema the table belongs too in the output of the SET STATISTICS IO. E.g. a table called Profile, or configuration in different schema is very common at our databases

    Today the output is just
    Table 'Profile'. Scan count 1, logical reads 123, physical reads 1, read-ahead reads 121, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Profile'. Scan count 3, logical reads 13, physical reads 1, read-ahead reads 342, lob logical reads 0,…

    100 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  2 comments  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
  • Don't see your idea?

Feedback and Knowledge Base