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. BUG: Statistics cannot be set to incremental when the Partition Function contains a "." (dot)

    Hi,

    We are moving SQL 2012 databases to SQL 2016, and some databases contain partitioned tables.

    Being SQL2016, I want to make use of incremental statistics, but when I enable incremental statistics on the existing statistics/indexes, I always received the following error: "Incorrect syntax near '.'."

    This error shows up for each partition that contains data.

    After a lot of testing, I found out that it is caused by the name of the Partition Function.
    This contains a "." ("PF_dbo.ID") .

    Has anybody run into this problem and found a way to solve it without needing to rebuild each partitioned…

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

    We’ll send you updates on this idea

    3 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  2. Provide XEvents target directly into SQL Server table

    Provide a way to directly write XEvent output directly into a SQL Server database table in a same or different instance and same or different database.

    The goal being to be able to not have any events missed but have the entire stream captured to the table and also provide header information for when the capture table was created for easier management and deletion. Ideally there should be a purge task that could be activated to clean out such captures after X days.

    XEvents is a great and powerful tool.
    Customers should have an easier way to get it directly…

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

    We’ll send you updates on this idea

    under review  ·  4 comments  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
  3. Change Tracking auto cleanup error 22123

    Just upgraded to 2016 SP2 (13.0.5026.0) and receiving frequent error messages logged from change tracking auto cleanup: Error 22123, severity 16, state 1 Change Tracking autocleanup is blocked on side table of "<***>". If the failure persists, check if the table "<***>" is blocked by any process . Routinely for different tables, not repeatedly from the same table.

    I suspect this was added as part of the changes to auto cleanup released in SP2, and is raised when a table is blocked and marked for retry. Can anyone please confirm this?

    Additionally, this seems to me like more of an…

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

    We’ll send you updates on this idea

    under review  ·  18 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  4. 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.

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

    We’ll send you updates on this idea

    15 comments  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
  5. 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 →
  6. 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…

    107 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 →
  7. 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.

    182 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 →
  8. 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…

    49 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 →
  9. 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.

    2 votes
    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 →
  10. 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.

    94 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 →
  11. 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 →
  12. 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…

    72 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 →
  13. 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.

    31 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 →
  14. 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 →
  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

    24 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 →
  16. 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 →
  17. 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

  18. 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.

    22 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 →
  19. 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 →
  20. 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 →
  • Don't see your idea?

SQL Server

Categories

Feedback and Knowledge Base