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. 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 →
  2. dm_db_stats_histogram missing row when column/histogram step contains NULL

    When statistics on a nullable column contain a NULL sys.dmdbstatshistogram will not return a row for the corresponding histogram step (when RANGEHIKEY is a NULL). DBCC SHOWSTATISTICS returns all steps including the aforementioned missing one. Without all the histogram levels dmdbstats_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 →
  3. 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 →
  4. 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 →
  5. 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…

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

  7. 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 →
  8. EXCEPTION_ACCESS_VIOLATION when returning inlined UDF with UserDataAccess from stored procedure

    SQL Server version: Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)
    Script to reproduce erorr:

    USE tempdb;
    GO

    DROP TABLE IF EXISTS dbo.testtbl;
    GO
    CREATE TABLE dbo.test
    tbl(val int NOT NULL);
    GO

    INSERT INTO dbo.test_tbl(val) VALUES (0);
    GO

    CREATE OR ALTER FUNCTION dbo.test_fn(@val int)

    RETURNS INT
    
    WITH SCHEMABINDING

    AS
    BEGIN

    IF EXISTS(SELECT val FROM dbo.test_tbl) -- UserDataAccess
    
    RETURN @val;
    RETURN 1;

    END
    GO

    CREATE OR ALTER PROCEDURE dbo.test_proc(@val int)
    AS

    RETURN dbo.test_fn(@val);
    

    GO

    EXEC dbo.test_proc @val = 10;
    GO

    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 →
  9. SSMS Extended Events UI Selects Actions on Hover

    When selecting events in extended events session properties > events page the events are automatically selected when you hover your mouse over the row. I've attached a gif showing the behavior. I'm not clicking on anything in the gif, just moving my mouse.

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

    Andrew,

    Thanks for bringing this back to our attention as not being the expected fix. The behavior was changed in 18.4 so that the selection event no longer occurs on hover (and the items below the grid only update when a new item is clicked on)
    However the grid still follows the mouse with what looks like selection and we will fix that to reduce confusion. Whatever item is currently selected will have its details below the grid.

  10. 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 →
  11. 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 →
  12. 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 →
  13. 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 →
  14. 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 →
  15. 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 →
  16. 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 →
  17. 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 →
  18. SQL Server won't install on Linux Ubuntu 18.04 due to Openssl version issue

    Doing an Ubuntu OS upgrade to VERSION="18.04 LTS (Bionic Beaver)", my SQL Server had to be removed, and can't be reinstalled. The following message shows during the apt-get installation step:

    "
    maxt@mars:~$ sudo apt install -y mssql-server
    Reading package lists... Done
    Building dependency tree

    Reading state information... Done
    Some packages could not be installed. This may mean that you have
    requested an impossible situation or if you are using the unstable
    distribution that some required packages have not yet been created
    or been moved out of Incoming.
    The following information may help to resolve the situation:

    The following packages have…

    9 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 →
  19. 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  ·  4 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  20. 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 →
  • Don't see your idea?

SQL Server

Categories

Feedback and Knowledge Base