SQL Server

Microsoft SQL Server 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 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. 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 →
  2. 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 →
  3. SQL Server 2017 Express LocalDB Shared Instance Connections Fail

    Using SQL Server 2016 Express LocalDB I can create a shared instance and connect to it by name, e.g. "(localdb).\SharedTestingInstance". After upgrading to SQL Server 2017 and applying the latest CU (puts me at version 14.0.3048.4) the connection now times out. Here are the steps to recreate:

    SqlLocalDB versions
    SqlLocalDB create TestingInstance
    SqlLocalDB share TestingInstance SharedTestingInstance
    SqlLocalDB info
    SQLCMD -S "(localdb)\TestingInstance" -Q "SELECT 'hello'"
    SQLCMD -S "(localdb).\SharedTestingInstance" -Q "SELECT 'hello'"
    SqlLocalDB unshare TestingInstance
    SqlLocalDB stop TestingInstance
    SqlLocalDB delete TestingInstance

    And here is the output:

    PS C:\WINDOWS\system32> SqlLocalDB versions
    Microsoft SQL Server 2017 (14.0.3048.4)
    PS C:\WINDOWS\system32> SqlLocalDB create TestingInstance
    LocalDB instance…

    9 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 →
  4. 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 →
  5. 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 →
  6. 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 →
  7. -Database parameter on Invoke-SqlCmd powershell cmdlet should accept the output from Get-SqlDatabase cmdlet

    Following on from a conversation on https://www.powershellgallery.com/packages/Sqlserver/21.0.17279
    The -Database parameter on the Invoke-SqlCmd cmdlet takes a [string] as its input, but ideally should be able to cope with receiving the output object from the Get-SqlDatabase cmdlet.
    As the -Instance parameter does successfully take the output of the Get-SqlInstance cmdlet, I assume this is a bug.
    Issue can be reproduced like so:

    $SQLCred = Get-Credential -UserName "dbUser"

    $instance = Get-SqlInstance 192.168.1.100 -Cred $SQLCred #Works fine

    $db = Get-SqlDatabase -Name "test_db" -ServerInstance $instance #Works fine

    $results = Invoke-SqlCmd -Database $db -ServerInstance $Instance -Query "Select column FROM view" -Cred $SqlCred # Throws that…

    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 →
  8. SSRS 2016 SP2 no longer respects text/html MIMEType

    An HTML file uploaded to SSRS 2016 SP1 would be identified as mimetype text/html. From the Report Manager portal you could click the link and it would be rendered in a browser window.

    After installing 2016 sp2 the mime type for the exact same file is now application/octet-stream. When clicking the link for this file from report manager, it prompts for download instead of opening in a browser window.

    This occurs in I.E, Edge, FF and Chrome.

    The Use case is uploading an HTML file to include a link/redirect to our data dictionary website to keep our users inside of…

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

    We’ll send you updates on this idea

    under review  ·  5 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  9. 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 →
  10. 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 →
  11. 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 →
  12. The "Number of Rows Read" property is incorrect when the Predicate property contains a PROBE

    Scan and Seek operators in newer versions of SQL Server report a property "Number of Rows Read" in the actual execution plan which is incredibly usesful when troubleshooting slow queries. However, it reports a misleading number of the execution plan contains a Bitmap operator and the bitmap is probed as part of the Predicate of the scan operator.

    This can be demonstrated in ContosoRetailsDW with the queries below:

    SELECT ds.StoreManager,

           dp.BrandName,
    
    SUM(fos.TotalCost)

    FROM dbo.FactOnlineSales AS fos
    INNER JOIN dbo.DimStore AS ds

      ON   ds.StoreKey = fos.StoreKey
    

    INNER JOIN dbo.DimProduct AS dp

      ON   dp.ProductKey = fos.ProductKey
    

    WHERE ds.EmployeeCount < 30
    AND dp.ColorName…

    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 →
  13. 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 →
  14. 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 →
  15. 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 →
  16. 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 →
  17. SSMS 17.3 Intellisense casing for built-in functions

    Although my settings are set to lower case, built-in functions are ALWAYS converted to upper case

    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 →
  18. Manual failover without data loss not working in SQL2019

    We have setup two Linux SQL2019 CU3 instances on Ubuntu with HA enabled and I get an error when I execute: ALTER AVAILABILITY GROUP [ag6x52] SET (ROLE = SECONDARY); to manually failover over to the secondary replica.

    The message is:
    Msg 41104, Level 16, State 5, Line 1
    Failover of the availability group to the local replica failed because the availability group resource did not come online due to a previous error.

    My AG is as follows

    Create AVAILABILITY GROUP ag6x52
    WITH (CLUSTER_TYPE = NONE )
    FOR REPLICA ON
    N'sql52' WITH (

      ENDPOINT_URL = N&#39;tcp://172.16.6.52:5022&#39;,
    
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
    FAILOVER_MODE =
    8 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 →
  19. BCP always adds a row delimiter after 2022 chars and has a bug when declaring an empty row delimiter

    There are a couple of bugs in BCP that don't allow you to extract a proper formatted JSON (or XML) file. In details:


    • BCP always adds a row delimiter after 2022 chars

    • BCP uses 63.502 white spaces (0x20) as row delimiter using -c if you pass an empty row delimiter (-r). So you'll find 63.502 white space after 2022 chars and at the end of the string.

    • BCP uses 63.502 combinations of chars 0x20 and 0x00 as row delimiter using -w if you pass an empty row delimiter (-r). So you'll find 63.502 white space after 2022 chars and at…
    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 →
  20. when i switched between tabs, new tab still show previous tab text. Please fix asap

    when i switched between tabs in SSMS 18.4, new tab still show previous tab text. Please fix asap

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

SQL Server

Categories

Feedback and Knowledge Base