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.

Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

(thinking…)

Enter your idea and we'll search to see if someone has already suggested it.

If a similar idea already exists, you can support and comment on it.

If it doesn't exist, you can post your idea so others can support it.

Enter your idea and we'll search to see if someone has already suggested it.

  • Hot ideas
  • Top ideas
  • New ideas
  • My feedback
  1. 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 →
  2. 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 →
  3. 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/

    11 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 →
  4. 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 →
  5. 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 →
  6. 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 →
  7. SSMS - Right-click menu option to "Design" tables configured as Temporal Tables is missing

    I am using the new Temporal Table feature on certain tables in my database.

    When I navigate to such tables via SSMS Object Explorer, I have found that the "Design" right-click menu option is not present for Temporal Tables. (Or as SSMS labels them, "System-Versioned" tables.)

    The "Design" option is, however, still available for NON-temporal tables.

    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 →

    Upvotes: 0

    <=-=Mar 7 2017 2:10AM=-=>

    Thank you for taking time to post this issue!
    I can confirm that Design option in SSMS Object Explorer is not available for temporal (system-versioned) tables.
    Unfortunately, we do not plan to include a fix for this issue in the upcoming release.
    We might include it as an improvement in a future release, though.
    How important for you is to have this option available for temporal tables?

  8. QDS_LOADDB Wait type blocks all queries

    After restoring a database with Query Data Store enabled, all queries will encounter the QDS_LOADDB wait while the query store is starting up. They will not finish and no data can be retrieved from the database during this time. This is on Enterprise V 13.0.4422.0.

    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 →

    Upvotes: 6

    <=-=May 27 2017 7:48AM=-=>

    Thank you for taking the time to post this issue! We understand that this could be an important performance improvement.

    This happens because Query Store information is loaded in synchronous mode. In future releases, we will work on changing it to async mode. As a workaround try to apply trace flag number 7752
    Trace flags are described here (https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql)

    <=-=Nov 8 2017 10:13AM=-=>

    We have experienced the same issue. SQL logs showed ramp up in memory waits then a large spike QDS_LOADDB for 3 hours preventing any execution.
    Query Store Disabled for now.

  9. Databases not accessible by default on auto-unlocked BitLocker drive

    Take the following BitLocker set up:

    C:\ OS Drive (encrypted) - Windows and SQL Server installed here
    D:\ Database Drive (encryped + auto-unlock)

    When Windows boots, it seems that the MS SQL Server service starts before BitLocker auto-unlocks the drives. Therefore, when SSMS is started, the databases are inaccessible (labelled as Recovery Pending). Also, any attempt to access the database data in SSMS results in strange read/write error dialogs:

    -------------------------
    Msg 823, Level 24, State 2, Line 1
    The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x000000005f4000 in file 'D:\Databases\MyDb_Data.mdf'.…

    11 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 →
  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…

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

    10 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 →
  12. Non-yielding Scheduler on SQL Server 2016 (SP2-CU2) (KB4340355)

    Non-yielding Scheduler on SQL Server 2016 (SP2-CU2) (KB4340355)

    On a production server, with stable workaround, we have encountered a "non-yielding scheduler" error and system got frozen for 4 minutes, time needed to generate the stack dump:

    Call stack from the minidump:
    00 ntdll!NtSignalAndWaitForSingleObject+0xa
    01 KERNELBASE!SignalObjectAndWait+0xc8
    02 sqldk!SOS_Scheduler::Switch+0x106
    03 sqldk!SOS_Scheduler::SuspendNonPreemptive+0xd3
    04 sqldk!WorkDispatcher::DequeueTask+0x59c
    05 sqldk!SOS_Scheduler::ProcessTasks+0x232
    06 sqldk!SchedulerManager::WorkerEntryPoint+0x2a1
    07 sqldk!SystemThread::RunWorker+0x8f
    08 sqldk!SystemThreadDispatcher::ProcessWorker+0x2de
    09 sqldk!SchedulerManager::ThreadEntryPoint+0x1d8
    0a kernel32!BaseThreadInitThunk+0x22
    0b ntdll!RtlUserThreadStart+0x34

    Call stack for the process not yielding:
    00 ntdll!NtGetContextThread
    01 KERNELBASE!GetThreadContext
    02 sqldk!SOS_OS::GetThreadControlRegisters
    03 sqlmin!SystemThreadTable::MoveToNextRow
    04 sqlmin!SystemThreadTable::InternalGetRow
    05 sqlmin!CQScanTVFStreamNew::GetRow
    06 sqlmin!CQScanProfileNew::GetRowImp<1>
    07 sqlmin!CQScanCountStarNew::GetRowHelper
    08 sqlmin!CQScanStreamAggregateNew::GetCalculatedRow
    09 sqlmin!CQScanProfileNew::Open
    0a sqlmin!CQScanConcatNew::GetRow
    0b sqlmin!CQScanProfileNew::GetRowImp<1>
    0c sqlmin!CQScanSortNew::BuildSortTable
    0d sqlmin!CQScanSortNew::OpenHelper …

    10 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. 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…

    10 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 →
  14. Unable to horizontal scroll Results pane via mouse wheel in SSMS

    My mouse has a horizontal scroll wheel. I can use the horizontal scroll in the Query pane, but if I select the Results pane, I can't scroll horizontally using my mouse. If I scroll to the left by dragging the scroll bar, and then try to use the horizontal scroll wheel on my mouse, the Results pane snaps to the leftmost position.

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

    We’ll send you updates on this idea

    started  ·  2 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  15. 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…

    10 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 →
  16. Merge statement Delete does not update indexed view in all cases

    A merge statement that contains both an update clause and a delete clause, does not always update indexed views that refer the table.

    Specifically, if the delete clause is the one that is triggered, and the update clause does not update any of the columns that are used by the indexed view, the deleted row is not removed from the indexed view.

    The indexed view is therefore logically inconsistent; it contains rows that are no longer part of the source table. An SQL script that demonstrates the bug is included.

    I have replicated the bug both on SQL Server 2014…

    10 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 →
  17. SSRS report builder not working after applying SQL update

    I have noticed that I can no longer use the “Report Builder” functionality in SSRS (while opening it from Report manager URL) after patching SQL Server 2012 Sp4 with recent CU KB4057116.

    Window Server 2008 R2 Standard
    SQL Server 2012 SP4 (11.0.7001.0)
    Getting below error after installing KB 4057116

    https://support.microsoft.com/en-us/help/4057116

    Following errors were detected during this operation.
    * [26/02/2018 10:15:22] System.Deployment.Application.InvalidDeploymentException (HashValidation)
    - File, interop.shdocvw.dll, has a different computed hash than specified in manifest.
    - Source: System.Deployment

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

    We’ll send you updates on this idea

    under review  ·  8 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  18. SSMS screen is not correctly redrawn when switching tabs

    Sometimes when switching between tabs in SSMS the screen is not correctly refreshed (redrawn).
    The result is a corrupt screen partly showing the previous tab contents and partly showing the newly active tab contents.
    In particular this happens when using "Database Diagrams" tabs.

    This has been a permanent bug through a number of the latest SSMS releases including this release:
    Release 16.5.1
    File version: 13.0.16100.1

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

    We’ll send you updates on this idea

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

    Upvotes: 6

    <=-=Dec 12 2016 1:42AM=-=>

    This has been reported more times than you have used SSMS. MS are too incompetent and lazy to fix it.

    <=-=Dec 12 2016 1:46AM=-=>

    Here is the link to another which also has a link to another report

    https://connect.microsoft.com/SQLServer/Feedback/Details/3109897

    please click on the I CAN TOO in the repros section

    <=-=Dec 21 2016 7:39AM=-=>

    I can replicate this on multiple different computers, each with freshly installed Windows (either 8.1 or 10) and latest of all drivers. It happens both with Intel integrated graphics (for me is HD 4600) and NVidia cards. It’s clearly a bug in how you’re using the Visual Studio shell.

    This is absolutely HORRIBLE!! When closing a tab, then attempting to execute the query in a tab that was already open and is now focused (because you just closed one), you see the wrong text in the tab, and…

  19. Live Query Statistics only showing the first query in a batch

    In SSMS 13.0.16000.28, click Query, Include Live Query Statistics.

    Start a multi-batch query where each query takes a non-trivial amount of time, like this:

    DECLARE @DontGoToEngland TABLE (Stuffing NVARCHAR(MAX));
    INSERT INTO @DontGoToEngland
    SELECT text
    FROM sys.messages;
    INSERT INTO @DontGoToEngland
    SELECT text
    FROM sys.messages
    ORDER BY text;
    INSERT INTO @DontGoToEngland
    SELECT text
    FROM sys.messages
    ORDER BY text;

    Execute the query, and only the first query's Live Query Statistics shows up as the batch runs (in this case, the first insert.) The Live Query Statistics no longer updates after the first query finishes - until the entire batch finishes. The status bar…

    10 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 →

    Upvotes: 4

    <=-=Dec 4 2016 5:14PM=-=>

    Just to be clear – this is different than the fixed bug 1051306, which was about only showing the current query. (Now it just only shows the first.)

    <=-=Apr 26 2017 2:29PM=-=>

    This isn’t fixed in 14.0.17099.0 (the new SQL Server Management Studio 17 that went out today.)

  20. Deploying single SSIS package to SQL 2016 from SSDT 17.0-17.4

    Deploying a single SSIS package with VB script task, targeted for SQL 2016 from SSDT 17.0-17.4 causes the script tasks to not execute but the package deploys and executes reporting success. .

    10 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 →
  • Don't see your idea?

SQL Server

Feedback and Knowledge Base