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. SQLLocalDB Utility 2016 returns "FormatMessageW failed. Error code returned: 15100"

    A recent update to SQL Server Express LocalDB 2016 causes errors when attempting to display output for several options while using the sqllocaldb.exe command-line utility.

    I current have Microsoft SQL Server 2016 (version 13.0.5026.0) installed (only LocalDB). "SELECT @@VERSION;" displays:

    Microsoft SQL Server 2016 (SP2-CU3) (KB4458871) - 13.0.5216.0 (X64)

    When executing the following program:

    "C:\Program Files\Microsoft SQL Server\130\Tools\Binn\SqlLocalDB.exe"

    the following options get the stated errors:

    -? as well as any invalid option receives:
    FormatMessageW failed. Error code returned: 15100

    i {invalid_instance_name} receives:
    FormatMessageW failed. Error code returned: 15100
    FormatMessageW failed. Error code returned: 15105

    i {valid_instance_name} receives:

    FormatMessageW failed. Error…

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

    We’ll send you updates on this idea

    5 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  2. STRING_AGG - Multiple STRING_AGG columns defer to first delimiter

    If you have multiple STRING_AGG columns with different delimiters, the delimiter specified in the first STRING_AGG column will be used for all other columns. For example:

    ;WITH personFruits
    AS
    (
    SELECT 'John' AS [Person], 'Apple' AS [Fruit] UNION
    SELECT 'John' AS [Person], 'Banana' AS [Fruit] UNION
    SELECT 'John' AS [Person], 'Kiwi' AS [Fruit] UNION
    SELECT 'Jane' AS [Person], 'Apple' AS [Fruit] UNION
    SELECT 'Jane' AS [Person], 'Strawberry' AS [Fruit] UNION
    SELECT 'Jane' AS [Person], 'Watermellon' AS [Fruit] UNION
    SELECT 'Jane' AS [Person], 'Grape' AS [Fruit] UNION
    SELECT 'Dale' AS [Person], 'Kiwi' AS [Fruit] UNION
    SELECT 'Dale' AS [Person], 'Watermellon' AS…

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

    We’ll send you updates on this idea

    1 comment  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  3. SQL Server Management Studio - search interface bug, crashing SSMS

    There is a bug in SQL Server Management Studio (17.9 and all below) that force the application to crash/restart. In order to reproduce follow the steps below:

    1. Select database from the object explorer
    2. Press F7 to open Object Explorer Details window and perform a search to object
    3. After the search completes, select the object, right click on it and then click "synchronized" from the menu.
    4. Then click the `BACK` button several times (fast). You will get the following error:

    "There is already a commandd handler for the menu command 'guid goes here: 189."

    After several clicks…

    13 votes
    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 →
  4. BULK INSERT does not work Utf-8 support enabled for system locale

    In Windows 10 (build 1803) you can go the Control Panel and select the Region applet. Here you can go to the Administrative tab and in the second half of that tab, you can change the System Locale. In this dialog, there is a checkbox "Beta: utf8 support". If you check this, reboot Windows, you find that any attempt to use BULK INSERT fails with

    Msg 2775, Level 17, State 12, Line 3
    The code page 65001 is not supported by the server.

    For instance

    create table ttt(namn nvarchar(24) NOT NULL, col2 char(3), col3 char(3) NOT NULL)
    go
    BULK INSERT…

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

    We’ll send you updates on this idea

    1 comment  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  5. MDS Explorer should show Attribute description of Name and Code attributes.

    It's possible to set Attribute description on system attributes Name and Code in the same way as user defined attributes on an entity in MDS. But Description only shows in MDS Explorer for user defined attributes not for Name and Code. I expected this to work the same way for system and user defined attributes.

    SQL Server version 13.0.4466.4
    MDS Schema version 13.1.0.3

    13 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. Don't show diagram in Microsoft SQL Server management studio 18

    Hello.

    I have error. I didn't see diagram in database. You can help me show diagram. I use Microsoft SQL Server management studio 18

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

    We’ll send you updates on this idea

    2 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  7. MERGE not working when index created on temporal table.

    Following are the steps to reproduce the MERGE statement issue in any SQL 2017 database.
    Script is attached for all the steps.

    --Steps to reproduce fail Scenario

    1. Create table in SQL Server 2017 database with named system versioned temporal/history table.
    2. Create non-clustered index on the temporal/history table.
    3. Insert 400+ records in table.
    4. Execute the merge statement (source table should have data for both Insert and Update scenario), and it will fail with following error

    Msg 681, Level 16, State 3, Line 453
    Attempting to set a non-NULL-able column's value to NULL.

    --Steps to see success scenario

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

    We’ll send you updates on this idea

    4 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  8. Query store create database

    If you enable Query Store on model, then attempt to create a database, Query Store will be enabled on the new database.

    However, if you attempt to create a database using a non-default collation, the CREATE database fails with a deadlock on sys.sysschobj, in the middle of a DQS transaction. (I'm using 2017 CU4)

    I should be able to specify a non-default collation when creating a database, and not have DQS cause a failure.

    --Enable Query Store on model
    ALTER DATABASE model SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
    GO
    --Create a database using any collation other than your instace default
    CREATE…

    12 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. Support Trace Flag 834 with Columnstore

    Trace flag 834 has been marked as unsupported with columnstore by Microsoft since SQL Server 2012, but that trace flag resolves columnstore and batch mode related bottlenecks with the RESERVED_MEMORY_ALLOCATION_EXT wait when running workloads at high concurrency. In addition, vendors use TF 834 with columnstore when competing in the TPC-H benchmarks.

    Please support TF 834 with columnstore.

    References with reproducible workloads:

    https://orderbyselectnull.com/2018/03/06/large-cci-etls-cannot-scale-without-tf-834/

    12 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 →
  10. Self referencing scalar function nesting level exceeded when adding a select

    Bug report on Self referencing scalar functions following this question, answered by Paul White here:

    https://dba.stackexchange.com/questions/239324/self-referencing-scalar-function-nesting-level-exceeded-when-adding-a-select

    In short a self referencing scalar function's references are evaluated when it should not be, resulting in an infinite loop in certain circumstances.

    Reproducing the issue:

    CREATE FUNCTION dbo.test6(@i int)
    RETURNS INT
    AS
    BEGIN
    RETURN(
    SELECT TOP 1
    CASE
    WHEN @i = 1 THEN 1
    WHEN @i = 2 THEN 2
    WHEN @i = 3 THEN (SELECT dbo.test6(1) + dbo.test6(2))
    END
    )END;

    SELECT dbo.test6(3);

    Resulting in the error:
    Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

    Part of the answer…

    11 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 →
  11. Specified sample rate is not followed when statistics object is created on an empty table

    A statistics update "WITH RESAMPLE" does not respect specified sample rate if the statistics object was created on an empty table. This behavior is best understood with an example (see the attached script).

    This bug is a bit annoying because it requires the user to take a specific order of operations with their table creation, data population, and stats population to get around it. The workaround we currently have is to just insert a row with default values of each data type, create stats with the desired sample rate, and then delete that row.

    x

    11 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 →
  12. LAST_MODE should map to LCK_M_RX_X in the lock_mode Extended Event

    This is just plain wrong.

    SELECT mv.name, mv.map_value, xo.description, xp.description
    FROM sys.dm_xe_map_values AS mv
    JOIN sys.dm_xe_objects AS xo
    ON mv.object_package_guid = xo.package_guid
    AND mv.name = xo.name
    JOIN sys.dm_xe_packages AS xp
    ON xo.package_guid = xp.guid
    WHERE mv.name = 'lock_mode'
    ORDER BY mv.map_key;

    11 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 →
  13. Azure SQL can't upgrade data-tier application

    Using DacFx 17.4.1 / SSMS 17.8.1, it is not possible to deploy and then upgrade an Azure SQL Server Database as a data-tier application.

    The initial deployment works correctly, but attempting to upgrade using SSMS or sqlpackage (using /p:BlockWhenDriftDetected=true /p:RegisterDataTierApplication=True) results in the following error:

    Databases registered as a DAC database must be hosted by an instance of SQL 2005 SP4, SQL 2008 SP2, SQL 2008 R2, SQL 2012, SQL 2014 or Microsoft Azure SQL Database.
    Unable to proceed with DAC operations on the target instance because it does not support DACs. Specify another instance, or upgrade to a version…

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

    We’ll send you updates on this idea

    2 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  14. Parallel SELECT INTO from sys.messages causes intra-query deadlock

    Loading data into a new table from sys.messages using parallel SELECT INTO causes consistent self-deadlocks. Testing on a machine with default parallelism settings and 4 logical cores. This occurs in SQL Server 2014, 2016 and 2017. Here's a reproduction:

    BEGIN TRANSACTION;

    SELECT TOP 10000
    m.message_id, m.[text]
    INTO SomeNewTable
    FROM sys.messages m

    For some reason, removing the "BEGIN TRANSACTION" reduces the frequency of deadlocks caused by this query. I've attached a deadlock graph from the issue.

    It's possible that bulk loading from sources like sys.messages is simply not a supported scenario - but, if so, this should be documented (or potentially…

    11 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 →
  15. smo script bug cannot drop table #sver

    I tried to script using SMO via following powershell script

    $conn = new-object system.data.sqlclient.sqlconnection
    $conn.connectionstring = "data source=(local);initial catalog=nxdba;integrated security=sspi;"
    $conn.open();

    ## SMO 사용
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
    $srv = New-Object "Microsoft.SqlServer.Management.Smo.Server"
    $so = New-Object "Microsoft.SqlServer.Management.Smo.ScriptingOptions"
    $so.AgentJobId=$true;
    $so.ScriptBatchTerminator=$true;
    $so.ToFileOnly=$true;
    $so.FileName="D:\Dba\Job.sql"
    ## Job Scheduler 백업
    $srv.JobServer.Jobs |
    foreach {
    if (($_.Name -notlike '*NXDBA*') -and ($_.Name -ne 'syspolicy_purge_history')){
    $_.Script($so);
    }
    }

    and above powershell turns into following t-sql script and causing error "cannot drop the table #sver"

    here is extended event result

    <RingBufferTarget truncated="0" processingTime="0" totalEventsProcessed="1" eventCount="1" droppedCount="0" memoryUsed="5191">
    <event name="error_reported" package="sqlserver" timestamp="2018-06-08T07:19:58.924Z">
    <data name="error_number">
    <type name="int32" package="package0"></type>
    <value>3701</value>
    </data>
    <data name="severity">
    <type…

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

    We’ll send you updates on this idea

    2 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  16. Computed column matching bug with ISNULL on BIT type

    A computed column with bit data type and outermost ISNULL is expanded but not matched back, preventing index matching.

    Workarounds:

    1. Ensure the computed column is not typed as bit; or
    2. Do not use an outermost ISNULL (column marked nullable); or
    3. Persist the computed column and enable trace flag 174.

    Demo:

    -- Uses index
    DROP TABLE IF EXISTS dbo.Example1;

    CREATE TABLE dbo.Example1
    (
    a1 bit NOT NULL,
    a2 bit NOT NULL,
    d AS a1 | a2,

    INDEX i (d)
    );

    SELECT COUNT_BIG(*)
    FROM dbo.Example1 AS E
    WHERE E.d = CONVERT(bit, 'true');

    -- Does not use index
    DROP TABLE…

    11 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. No hash warning on adaptive join

    When an execution plan uses an Adaptive Join, and the actual row count exceeds the estimated row count by sufficient to cause the hash table to spill to tempdb, no hash warning is exposed in the execution plan.

    (The hash warning is visible in an extended events session; and the Batch Mode Adaptive Memory Grant feature does also kick in correctly, so the spill is detected and handled internally; just not exposed in the execution plan).

    11 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 →
  18. Attempting to create a database diagram causes error "Index was outside the bounds of the array"

    When attempting to create a new database diagram on an on premises SQL 2014 database using SQL Server Management Studio 16.5, an error window is shown with error "Index was outside the bounds of the array". Additionally, SSMS 2014 is able to create a database diagram just fine. When back in SSMS 2016, I can view the new diagram, but it is in a window on another monitor, with no ability to resize the window (window chrome is missing).

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

    We’ll send you updates on this idea

    25 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →

    Hello all

    Database Diagrams is being deprecated for SSMS 18. As such, we have not planned any work on this feature for a while. Please use Visio or any of the 3rd party ER modeling tools for this functionality. I understand some of you may have dependency on this feature. You are welcome to continue using 17.9.1 if it works for you.

    thanks,
    Dinakar

  19. SSDT (14.0.61709.290) Unable execute SSIS package on server that contains a script component.

    When using Visual Studio 2015 (14.0.25431.01) and the latest version of SSDT (14.0.61709.290) on my client machine we are no longer able to execute a SSIS packages from the SSISDB in SQL Server 2016 (13.0.4451.0) on the server machine when it contains a script component and the single package is deployed. I have made sure project TargetServerVersion is SQL Server 2016.

    When we try to execute the package from the server we get the following errors.

    The component is missing, not registered, not upgradeable, or missing required interfaces.

    Script failed validation and returned error code 0xC0048021

    The component metadata for…

    11 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 →
  20. An internal error happened while generating a new DBTS for database "test_db". Please retry the operation.

    This error happens during INSERT/UPDATE queries, even simple ones, if the server is busy for example restoring another database

    The SQL Server is the Enterprise version, it has the SP1 applied.

    SELECT @@VERSION produces: Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)

    It runs on VMWare 5.5 build 4180647.

    The OS is Windows 2012 R2 Standard, no SP applied. The server is configured with 4 vCPUs and 80 GB of RAM. Disks are handled…

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

    We’ll send you updates on this idea

    8 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  • Don't see your idea?

SQL Server

Categories

Feedback and Knowledge Base