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

  4. 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.)

  5. Keep system sessions from entering a single-user database

    I am super hesitant to put a database into singleuser mode without being in the database because system sessions (sessionid <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 →
  6. sys.fn_hadr_backup_is_preferred_replica causes error 41070 in SQL Server 2017 CU15

    After installing CU15 on a SQL 2017 2-Node Always On Availability Group Cluster (Windows Server Failover Clustering Mode), sys.fnhadrbackupispreferred_replica throws the following exception when run against a database that is in an availability group:

    Msg 41070 - Configuration data for the availability group with Windows Server Failover Clustering (WSFC) resource ID 'GUID' is not found in the WSFC data store. The availability group may have been dropped, or a previous CREATE AVAILABILITY GROUP or DROP AVAILABILITY GROUP operation has failed. Please use DROP AVAILABILITY GROUP command to clean up previously failed operations before retrying the current…

    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 →
  7. SQL Server 2019 CTP 2.3 / Issue with scope of set identity_insert on/off

    Hello!

    I have SQL Server 2019 CTP 2.3 installed, and I have detected a change in the behavior of:

    SET IDENTITY_INSERT tabname ON/OFF

    It appears that the scope has changed since SQL Server 2019 CTP 2.2 and SQL Server 2017:

    I am using MS ODBC 17 ...

    When executing some SQL from ODBC with SQLExecDirect() with parameters, I see in my Extended Events log that the spexecutesql stored procedure is used, and in such case, a prior SET IDENTITYINSERT ... ON has no effect on the SQL executed inside the stored procedure.

    By no effect I mean that…

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

    We’ll send you updates on this idea

    9 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  8. Columnstore Index incorrectly returning data from deleted column

    When dropping a column from a table with a Clustered Columnstore Index and creating a new column with the same name, SQL Server will return incorrect data as it appears to be joining on the deleted column.

    MVCE:

    create table example
    (

    id int identity(1, 1),
    
    barcode char(22),
    id2 int,
    total decimal(10,2),
    statusId tinyint

    )

    declare @i int = 0

    while @i < 10000
    begin

    insert example 
    
    select @i, null, @i, 1

    set @i = @i + 1
    end

    create clustered columnstore index cci_example on example

    create nonclustered index ix_example on example (barcode, total)

    alter table example add statusId2 tinyint

    9 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 →
  9. Incorrect filepath with BACKUP LOG command causes sql server 2017 CU13 to crash

    supplying an incorrect filepath with BACKUP LOG command causes sql server 2017 CU13 to crash. It seems to occur if you give in an invalid path. A valid but non-existent path will give the predicted "Cannot open backup device". An invalid path (such as "D:mydbLogBackup.bak" without the slash causes the server to crash with nothing but "unexpected error" in event log details. I ran into this because my default backup location in the ssms gui is "D:" and I cannot change it to "D:&quot;. The error is not gui dependent though because the raw scripts fail too.

    After applying the…

    9 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 →
  10. EXCEPTION_ACCESS_VIOLATION during plan compilation with incremental statistics (SQL17 CU11)

    If a query uses one or more partitioned tables that use incremental statistics and whose partition function has been modified using the MERGE and SPLIT partition commands, a connection-terminating access violation can occur when the incremental statistics need to be automatically updated during plan compilation.

    The attached script reliably reproduces the error on multiple SQL Server 2017 database servers we've tried thus far, including a server running CU11. We've also attached dump files from both a real production occurrence of this error and a testing occurrence of the error using our repro script.

    SQL Server 2017 CU8 contained a fix…

    9 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 →
  11. Change from normal Table to Temporal Table doesn't work with dacpac and sqlpackage.exe

    Using sqlpackage.exe 15.0.4108.1

    The generated Change Script from changing a normal Table to a Temporal Tables fails with the following Error:
    Msg 515, Level 16, State 2, Line 44
    Cannot insert the value NULL into column 'SysEnd', table 'analyse.tmpmsxx_Vergleich'; column does not allow nulls. INSERT fails.

    See Attached Example:
    -#1 Orignal Table
    -#2 Changed Table in Dacpac
    -#3 Generated not working Query
    -#4 Correct Qeury in this Case

    9 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 →
  12. Cannot open Maintenance Plan steps in SSMS 18 Preview 4

    Maintenance plans were created with earlier versions of SSMS (17.x and lower) and trying to open any step of the maintenance plan results in the follow error message,

    "The task with the name "AG Replica DB Differential Backups T-SQL Statement Task" and the creation name "Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceTSQLExecuteTask, Microsoft.SqlServer.MaintenancePlanTasks, Version=15.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" is not registered for use on this computer.
    "

    9 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 →
  13. Deploy issue for SSDT database extended property / external tables

    Deploy issue for SSDT database extended property / external tables

    Summary:
    Deployment of an extended property 'MS_DESCRIPTION' on an external table fails, if the Extended property is scripted together with the external table.

    Deployment of an Extended property 'MS_DESCRIPTION' on an external table succeeds, if the Extended property is added in a postdeployment script.

    If something is changed, redeployment of the database fails, because the extended property 'MS_Description' can't be deleted.

    The following code is produced:
    EXECUTE spdropextendedproperty @name = N'MSDescription';

    This code would be correct:

    EXEC sys.spdropextendedproperty
    @name = N'MS
    Description',
    @level0type = 'Schema', @level0name =…

    9 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 →
  14. CEIP Service cause noisy error log

    We found that CEIP Service causes following error log messsage in production server.

    CImageHelper::Init () Failed load of symbol file with path = D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\;D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\BINN Error Code 0xc0000004


    • 0xc0000004 : The specified information record length does not match the length that is required for the specified information class

    following attached file is event log that I collected.
    Version of prod server is following (which is on AZURE):
    Microsoft SQL Server 2016 (SP1-CU5) (KB4040714) - 13.0.4451.0 (X64) Sep 5 2017 16:12:34 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Datacenter 6.3 <X64> (Build…

    9 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. TDE - corrupt backups when using backup compression

    After restoring a valid compressed backup of a TDE database to the same, or different, SQL server, backups of the newly restored database may be corrupt.

    This is an intermittent problem. Out of dozens of restores, 0 or more of the restored databases may no longer be able to create valid backups when using backup compression.

    If a restored database is affected by this problem, all subsequent compressed backups of the restored database are also corrupt until TDE is removed and reapplied to the database.

    For me, this problem only applies to databases restored from compressed TDE backups. Compressed backups…

    9 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. Security error connecting to SQL Server Express LocalDB in SQLCLR using EXTERNAL_ACCESS and "(localdb)\Instance" connection string

    I am using:


    • Windows 8.0 (though that shouldn't matter)

    • SQL Server 2012 Developer Edition (64 bit) SP2 (11.0.5058.0)

    • SQL Server 2014 Express Edition (64 bit) SP1 (12.0.4100.1)

    • .NET Framework 4.5.2 installed

    When connecting to SQL Server Express LocalDB via the "trusted_connection=true;server=(localdb)\InstanceName" syntax that became available in either the .NET Framework 4.0.2 Update or .NET Framework 4.5 (depending on what blog or documentation you go by), I can connect successfully to:


    • an automatic instance: (localdb)\v11.0

    • a named instance: (localdb)\Projects

    • a shared named instance: (localdb).\SharedProjects

    However, when doing that same simple test via SQLCLR in either SQL Server 2012 SP2 or SQL…

    9 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 →
  17. Task List seems incomplete

    I did a test for Task List in SSMS according to a question in the forum:

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/4a160d5f-b390-4583-bab4-eb7596729088/not-able-to-create-tasklist-in-sql-server-management-studio?forum=sqlgetstarted#f8369eb5-4ae7-4188-945d-13875649caed

    It works as expected with version 13.0.700.242 and I checked it with some older versions of SSMS, no problem there. But after some version (for instance 13.0.16000.28), Task List is incomplete. By that, I mean there's no "New Task" button around, no dropdownbox at the left of the button, no nothing.

    9 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 →
  18. Trusted Assemblies are more problematic yet less functional than Certificates - Please Remove

    The new "Trusted Assemblies" feature, introduced in SQL Server 2017 RC1, is entirely unnecessary, and pushes people farther away from good security practices (i.e. using a signature-based Login).

    The main problem it is attempting to solve -- allowing SQLCLR code in unsigned, SAFE Assemblies to work with "clr strict security" enabled and TRUSTWORTHY disabled -- was solvable back when SQLCLR was first introduced in SQL Server 2005, but in a more elegant, more functional, and more secure way.

    All that needs to be done is to sign the Assembly using ADD SIGNATURE (well, you still need the signature-based Login having…

    9 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 →
  19. Use More Than Density to Cost a Scan on the Inner Side of a Nested Loop with TOP

    Consider a simple query of the following form:

    SELECT o.ID
    FROM dbo.OUTER o
    WHERE NOT EXISTS
    (

    SELECT 1
    
    FROM dbo.INNER i
    WHERE o.ID = i.ID

    );

    Sometimes the query optimizer will implement the join as a left anti semi nested loop join
    with a TOP operator applied to the scan on the dbo.INNER table. The row goal introduced
    by TOP aggressively reduces the cost of the repeated scan on the dbo.INNER table.
    The density of the table is used in the calculation to reduce the cost of the scan.
    Using the density means that the query optimizer assumes that…

    9 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 →
  20. SSMS 18.0 unable to export Extended Events XEL to XEL

    SSMS 18.0 is unable to export an Extended Events XEL file to another XEL file.

    When viewing an XEL file in SSMS, using the Extended Events menu command "Export to" > "XEL file..." results in this error:

    (Error Summary)

    TITLE: Microsoft SQL Server Management Studio

    Method not found: 'Void System.Object.SerializeEvent(Microsoft.SqlServer.XEvent.Linq.IEventSerializer, Microsoft.SqlServer.XEvent.IMetadataGeneration)'. (Microsoft.SqlServer.XEvent.Linq)

    BUTTONS:

    OK

    (Error Detail)

    Method not found: 'Void System.Object.SerializeEvent(Microsoft.SqlServer.XEvent.Linq.IEventSerializer, Microsoft.SqlServer.XEvent.IMetadataGeneration)'. (Microsoft.SqlServer.XEvent.Linq)

    Program Location:
    at Microsoft.SqlServer.XEvent.Linq.XEventFileProvider`1.SerializeEvent(IEventSerializer serializationContext, TEvent serializableEvent)
    at Microsoft.SqlServer.XEventStorage.ComputedGridStorage.SaveData(Object param)

    I reported this same issue for a previous version of SSMS (17.?) via connect item 3136373 on 2017-06-22 and it was fixed in a later minor update…

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

SQL Server

Categories

Feedback and Knowledge Base