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. SSRS Bug: Choose new target for linked report offers no reports

    Install and configure SQL Server Reporting Services 2016SP1CU7 (mine was a Migration from 2008R2SP3, but an empty one should do). Upload a 'master' Report and create a link to it. Delete the 'master' and re-upload it. The link is now orphaned as expected.

    In previous Versions, one could re-map the link to a different master without losing parameter configuration. It had problems with hidden folders, but allowed to enter a path explicitly by hand.

    2016 pops up a selector box (aggravatingly using tiles no matter how often I dell it I want lists), which allows browsing folders, but all folders…

    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 →
  2. 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 →
  3. 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 →
  4. 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 →
  5. 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. .

    11 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 →
  6. Cannot open Extended Event when default database not master

    You might get an error message when looking at Extended Event data (file, etc) if Object Explorer isn't connected to the master database.

    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 →
  7. ssdt build does support 'CREATE OR ALTER'

    'CREATE OR ALTER' in StoredProcedure .sql files in any Database project show errors in Visual Studio 2015 SSDT
    Build database, schema compare.

    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: 23

    <=-=Aug 1 2017 1:08PM=-=>

    Would be great to get this added.

    <=-=Nov 6 2017 1:42AM=-=>

    This is supported for “non build” scripts, not sure the use case for having for “build” files

    <=-=Nov 7 2017 7:49PM=-=>

    The scenario for supporting the CREATE OR ALTER in a build .sql for a stored procedure, is as follows:

    Since the [Test, modify, fail, modify Stored Proc, modify Test -> pass] cycle is very labour intensive currently in the VS DB project with the DB SQL Test feature, I have been experimenting with how to make writing the SQL for the test in a quicker manner.

    I have written a SQL file that has [Arrange, Act, Assert] set of SQL statements, and after finished developing the final SQL ‘Assert’ statements they are then added to the DB Test file type’s formal Assertions.

    The fact that running the Build of a DB and…

  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. SSMS 17.3 excessive memory usage

    SSMS consumes large amounts of memory & CPU, even when idle. Disabling intellisense has no effect

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

    We’ll send you updates on this idea

    6 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  10. 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 →
  11. save passwords v18

    SSMS v18.0 doesn't save password properly for each server. Save the password from one server, then attempt to connect to another server with a save password and it will fail. Using SQL authentication and version 15.0.18118.0 and migrated my settings from SSMS v17.9.1.

    10 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 →
  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. SQL SERVER 2016 (SP2-CUI) BUG? MERGE and INSERT with COLUMNSTORE index creates crash dump - script supplied

    Hi

    I have managed to create a script that (on my system) reliably generates the following message

    Location: pageref.cpp:955
    Expression: IS_OFF (BUF_MINLOGGED, m_buf->bstat) || pageModifyType != PageModifyType_Contents || GetPagePtr ()->IsTextPage ()
    SPID: 59
    Process ID: 5080
    Msg 3624, Level 20, State 1, Line 45
    A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update…

    10 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 →
  14. VS2017 and SSDT publishing Graph Database objects created before schemas

    VS 15.7.4
    SSDT 15.1.61804.210

    We created a new SQL Server Database Project in VS2017 and imported a database that uses Graph DB features.

    Trying to publish the project we receive and error indicating one schema is missing (a custom schema that contains graph database objects).
    We dug into publish t-sql script and we found that CREATE TABLE statements for graph db edge tables were placed before CREATE SCHEMA statements, therefore, publish won’t ever complete successfully.

    To complete deploy we had to move CREATE SCHEMA statements above and run the script manually.

    10 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  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  15. dm_db_stats_histogram missing row when column/histogram step contains NULL

    When statistics on a nullable column contain a NULL sys.dm_db_stats_histogram will not return a row for the corresponding histogram step (when RANGE_HI_KEY is a NULL). DBCC SHOW_STATISTICS returns all steps including the aforementioned missing one. Without all the histogram levels dm_db_stats_histogram is not usable.

    I can confirm this behaviour with INT and VARCHAR columns, I haven't tested other data types). I have attached the reproduction code and a screenshot. Any questions please let me know. Thank you.

    SQL Version: 14.0.3025

    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 →
  16. SSDT error SQL46010 on iif

    SSDT reports an error on this valid syntax:

    create procedure test
    as
    if (iif(1 = 1, 1, 0)
    )
    < 2
    select 'ok'

    Severity Code Description Project File Line Suppression State
    Error SQL46010: Incorrect syntax near ). Stored Procedures\Procs2\test.sql 4

    See https://social.msdn.microsoft.com/Forums/sqlserver/en-US/8ec0a584-1eec-4ffb-b5b2-1df6092817e6/sql46010-incorrect-syntax-near-?forum=ssdt

    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 →
  17. 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 →
  18. SQLPackage.exe - Needs to be made into at least 3 cmdlets

    SQLPackage.exe - Needs to be made into at least 3 cmdlets (and possibly more; we have added ideas for additional cmdlets below). The first 3 cmdlets that need to be made into are:
    Export-SqlDatabase
    Import-SqlDatabase
    Compare-SqlDatabase

    Export-SqlDatabase
    At a minimum Export-SqlDatabase should take parameters such as -ServerInstance -Database -TargetFile -IncludeData (ExtractAllTableData).

    It would also be nice if these cmdlets would work 'smartly' from within the provider. For instance, if you were within the provider inside a database (PS SQLSERVER:\sql\localhost\default\databases\AdventureWorks> ) and wanted to run Export-SqlDatabase you should only have to supply a filename for your export; the cmdlet should infer…

    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 →

    Upvotes: 55

    <=-=Apr 5 2016 10:09AM=-=>

    first FIX DACfx then the important verbs /publish etc

    <=-=Apr 5 2016 12:30PM=-=>

    PowerShell wrappers around DacFX seems like a good idea. Some brainstormed thoughts:
    -The word “publish” is part of the DacFX lexicon. Should Import-SqlDatabase not be called Publish-SqlDatabase? And if so, shouldn’t it be called Publish-Dacpac. Strictly speaking its not the database that’s being published, its the dacpac. Regardess of this bullet point, I’ll refer to Import-SqlDatabase in the rest of this comment.
    -Similarly I think Export-SqlDatabase should be called Export-Dacpac
    -Love the idea of piping Export-SqlDatabase to Import-SqlDatabase
    -Import-SqlDatabase needs an option to only produce the change script, not execute it.
    -Compare-SqlDatabase should have the option to compare a dacpac to a dacpac
    -Generating a DriftReport feels to me like a different cmdlet rather than being an option of Compare-SqlDatabase

    <=-=Apr 6 2016 6:12AM=-=>

    I’ve created a wrapper that accomplishes Publish…

  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. Keep system sessions from entering a single-user database

    I am super hesitant to put a database into single_user mode without being in the database because system sessions (session_id <50) will get into the database and I cannot get the sessions out. I can't kill them because they are system sessions and the KILL command doesn't work.

    This comes into play when I am in the database, set the database into single_user mode, get out of the database and have a slight delay and then try to do something with the database (such as backup the log with norecovery).

    10 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  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  • Don't see your idea?

SQL Server

Categories

Feedback and Knowledge Base