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 Microsoft Q&A or Stack Overflow

  • Hot ideas
  • Top ideas
  • New ideas
  • My feedback
  1. SQL 2017: "SET FMTONLY ON" doesn't return any resultset on UDF w/ COMPATIBILITY_LEVEL=140

    Our application uses at various places the SchemaOnly attribute (see https://msdn.microsoft.com/en-us/library/system.data.commandbehavior(v=vs.110).aspx). Lately, we noticed some strange errors when the application runs against a DB on SQL Server 2017. I boiled the issue down to the attached repro script in T-SQL, that shows clearly that SQL Server doesn' return any metadata, if a table-valued function is called using SET FMTONLY ON with COMPATIBILITYLEVEL=140. But it works as expected using COMPATIBILITYLEVEL=130. As I didn't find any word on this breaking change in the docs, I consider this a bug.

    23 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 →
  2. SQL Server Assertion: File: <lobss.cpp>, line = 725 Failed Assertion = '0' Should never happen.

    DBCC CHECKDB reports no errors, I rebuilt the indexes just to be sure. It happens when running this query:

    WITH t1 AS
    (
    SELECT [Endpoints].[Name] AS [f1],[MatchLocations].[Location] AS [f2],ISNULL(SUM([Matches].[LastCount]), 0) AS [f3],ROWNUMBER() OVER (ORDER BY [Endpoints].[Name] ASC) AS [f4]
    FROM MatchLocations
    INNER JOIN [Matches] ON ([MatchLocations].[Id] = [Matches].[MatchLocationId])
    INNER JOIN [Endpoints] ON ([Endpoints].[Id] = [MatchLocations].[EndpointId])
    GROUP BY [Endpoints].[Name],[MatchLocations].[Location]
    )
    SELECT [t1].[f1] AS [t1
    f1],[t1].[f2] AS [t1f2],[t1].[f3] AS [t1f3]
    FROM t1
    WHERE ([t1].[f4] BETWEEN 1 AND 100)
    ORDER BY [t1].[f4] ASC

    When the SUM and GROUP BY are removed, it runs just fine. It happens from time to…

    22 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 →
  3. VS 2017 SSDT SSIS Dark Theme fail

    VS 2017 SSDT SSIS Dark Theme: Annotations and dataflow row numbers are in white text, therefore invisible.

    22 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

    <=-=Oct 12 2017 11:54AM=-=>

    Agreed – it’s pretty much unusable with the dark theme. See attachment of screenshot.

    <=-=Oct 12 2017 11:55AM=-=>

    Apparently I can’t add an attachment so for a screenshot look here: https://i.imgur.com/X2PfTp7.png

    <=-=Oct 25 2017 9:23AM=-=>

    I am also getting this. I thought I was going crazy! When I was using VS 2015 my canvas color was black, so the dark theme worked. But now that I have installed windows 10 and VS 2017 fresh, and the white text on cream background is totally unreadable. Is there any way to change the canvas color?

  4. Query Store SELECT Performance

    Running a query against a large Query Store repository (10 GB) can be really slow if filtering on runtime stats intervals.

    It looks like an index is missing on sys.querystoreruntimestats (runtimestatsintervalid).

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

    Upvotes: 3

    <=-=May 27 2017 6:38AM=-=>

    Thank you for taking the time to post this issue! We understand that this could be an important performance improvement.
    Performance tuning is of such scenarios is not a trivial task. Change of such feature as Query Store can speed up one scenario and lead to degradation in others. We�ll look more into this particular use case and see what we can do.

  5. MSOLEDBSQL

    When trying to use the new MSOELDBSQL driver as a linked server, any attempt to run a query as simple as

    SELECT * FROM SERVER.master.sys.databases

    results in the error message "Microsoft Distributed Transaction Coordinator (MS DTC) has stopped this transaction.", even in the trivial case where you set up a loopback server to the same instance.

    There is a workaround: if you first say BEGIN DISTRIBUTED TRANSACTION, you can access the linked server.

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

    We’ll send you updates on this idea

    14 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  6. Can't SHRINK data files due to off-row LOB in large tables

    There are times where I believe it's very important to be able to SHRINK (EMPTYFILE) database files that are on obsolete storage technology in an online manner. An example is moving an secondary data file from older HDD technology to newer SSD technology in an online manner.

    However, when shrinking a database ROWS data file that has LOB data where the table has many rows it requires a table scan for each piece of off-row LOB data encountered. While this table scan occurs a LCKMSCH_S lock occurs on the table preventing operational queries from accessing the data, resulting…

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

    20 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. GROUPING SETS returns incorrect results

    The Query

    SELECT Surname,
    
    LEFT(Surname,1) AS SurnameInitial,
    COUNT(*) AS Count
    FROM Queen
    GROUP BY GROUPING SETS ( ( Surname ), (LEFT(Surname,1)) )

    Does NOT return the same results as the expanded version

    SELECT Surname,
    
    NULL AS SurnameInitial,
    COUNT(*) AS Count
    FROM Queen
    GROUP BY Surname
    UNION ALL
    SELECT NULL AS Surname,
    LEFT(Surname,1) AS SurnameInitial,
    COUNT(*) AS Count
    FROM Queen
    GROUP BY LEFT(Surname,1)

    Details here https://dba.stackexchange.com/a/267196/3690

    19 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 →
  9. SSMS 18.4 SQLServer 2019 CU2 Object explorer fails for SQLAgent

    After applicting CU2 on SQLServer 2019, SSMS18.4 fails to show SQLAgent jobs via object browser.

    TITLE: Microsoft SQL Server Management Studio

    Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

    For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476


    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)


    A severe error occurred on the current command. The results, if any, should be discarded. (Microsoft SQL Server, Error: 0)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=15.00.4013&EvtSrc=MSSQLServer&EvtID=0&LinkId=20476


    BUTTONS:

    OK

    19 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 →
  10. sql 2017 "cube processing task" ssis adomd client error

    A SQL Server 2017 Cumulative Update (somewhere between RTM and CU5) has broken 'Cube Process Tasks' in Integration Services (SSIS). Packages that run fine on SQL 2017 RTM (containing a Cube process Task) FAIL on CU5 and also CU6 with an error related to ADOMDClientUI. The specific error reported in the SSIS log is "Process Cube:Error: Could not load file or assembly 'Microsoft.AnalysisServices.AdomdClientUI, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91 or one of its components. The system cannot find the file specified."

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

    We’ll send you updates on this idea

    under review  ·  17 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  11. 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…

    18 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. STRING_AGG - Multiple STRING_AGG columns defer to first delimiter

    If you have multiple STRINGAGG columns with different delimiters, the delimiter specified in the first STRINGAGG column will be used for all other columns. For example:

    ;WITH personFruits
    AS
    (

    SELECT &#39;John&#39; AS [Person], &#39;Apple&#39;       AS [Fruit] UNION
    
    SELECT &#39;John&#39; AS [Person], &#39;Banana&#39; AS [Fruit] UNION
    SELECT &#39;John&#39; AS [Person], &#39;Kiwi&#39; AS [Fruit] UNION
    SELECT &#39;Jane&#39; AS [Person], &#39;Apple&#39; AS [Fruit] UNION
    SELECT &#39;Jane&#39; AS [Person], &#39;Strawberry&#39; AS [Fruit] UNION
    SELECT &#39;Jane&#39; AS [Person], &#39;Watermellon&#39; AS [Fruit] UNION
    SELECT &#39;Jane&#39; AS [Person], &#39;Grape&#39; AS [Fruit] UNION
    SELECT &#39;Dale&#39; AS [Person], &#39;Kiwi&#39; AS [Fruit] UNION
    SELECT &#39;Dale&#39; AS [Person],
    18 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. DROP TABLE IF EXISTS fails

    The DROP TABLE IF EXISTS does appear to be contextual, so it checks if the objects exists within sys.objects rather then the limited sys.tables, so the follow example would fail on the second run -

    https://gist.github.com/matt40k/f1793ecc7e20738ebf5c0d7425e2a4ca

    -- code start ---

    DROP TABLE IF EXISTS dbo.ObjectFormerlyATable;

    CREATE TABLE dbo.ObjectFormerlyATable
    (

    ID INT IDENTITY(1,1) NOT NULL
    
    CONSTRAINT PK_dbo_ObjectFormerlyATable PRIMARY KEY CLUSTERED (ID ASC)

    );

    INSERT INTO dbo.ObjectFormerlyATable DEFAULT VALUES;

    SELECT ID FROM dbo.ObjectFormerlyATable;

    DROP TABLE IF EXISTS dbo.ObjectFormerlyATable;
    GO

    CREATE OR ALTER VIEW dbo.ObjectFormerlyATable
    AS
    SELECT

    ID=1
    

    ;
    GO

    SELECT ID FROM dbo.ObjectFormerlyATable;

    --- code end ---

    Other examples could be DACPAC…

    18 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. BULK INSERT does not work Utf-8 support enabled for system locale

    In Windows 10 (build 1803) you can go the Control Panel and select the Region applet. Here you can go to the Administrative tab and in the second half of that tab, you can change the System Locale. In this dialog, there is a checkbox "Beta: utf8 support". If you check this, reboot Windows, you find that any attempt to use BULK INSERT fails with

    Msg 2775, Level 17, State 12, Line 3
    The code page 65001 is not supported by the server.

    For instance

    create table ttt(namn nvarchar(24) NOT NULL, col2 char(3), col3 char(3) NOT NULL)
    go
    BULK INSERT…

    18 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. SSAS Supported Compatibility level is actually showing default Compatibility in SSMS 17.3

    In SSMS 17.3 with Analysis Services Client tools 14.0.1008.227 when you click on server properties for an SSAS 2017 instance it shows the "Suppported Compatibility level" as 1200 when it should show 1100,1103,1200,1400.

    If you run a DISCOVERXMLMETADATA command against the SSAS 2017 instance you can see the correct data returned for the default and supported compatibility modes, it appears that the SSMS UI has just mapped this incorrectly.

    <ddl400:DefaultCompatibilityLevel>1200</ddl400:DefaultCompatibilityLevel>
    <ddl600:SupportedCompatibilityLevels>1100,1103,1200,1400</ddl600:SupportedCompatibilityLevels>

    18 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 →
  16. System.Data can't referenced in SQLCLR at runtime.

    SQLCLR now causes a System.Drawing.dll reference error when using System.Data.dll.

    The reason for this is that System.Data has been changed to refer to System.Drawing , but the library supported by SQL Server does not include System.Drawing.dll.

    Information on the changes can be found at https://portal.msrc.microsoft.com/en-us/security-guidance/advisory/CVE-2020-1147 and https://docs.microsoft.com/en-us/sql/relational-databases/clr-integration/database-objects/supported-net-framework-libraries

    A temporary workaround is to CREATE ASSEMBLY the System.Drawing.dll.
    But PERMISSION_SET = UNSAFE is required.

    The type initializer for 'Scope' threw an exception. ---> System.IO.FileNotFoundException: Could not load file or assembly 'System.Drawing, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'or one of its dependencies. The system cannot find the file specified.
    System.IO.FileNotFoundException:
    at System.Data.TypeLimiter.Scope..cctor()
    System.TypeInitializationException:
    at System.Data.TypeLimiter.Scope.IsTypeUnconditionallyAllowed(Type type) …

    17 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. Bug in sys.identity_columns: does not honor read uncommitted, causes blocking

    There's a bug in sys.identity_columns: it ignores requests for read uncommitted. This means if someone's rebuilding a large index on an identity column, queries get blocked.

    To reproduce it, create a table with a clustered index on an identity column, and rebuild it:

    CREATE TABLE dbo.Test (Id INT IDENTITY(1,1), CONSTRAINT PKId PRIMARY KEY CLUSTERED (Id));
    GO
    BEGIN TRAN
    ALTER INDEX PK
    Id ON dbo.Test REBUILD WITH (ONLINE = OFF);
    GO

    Then in another window, try to query sys.identity_columns with dirty reads:

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    SELECT * FROM sys.identity_columns WITH (NOLOCK);

    (I know, those two isolation level…

    17 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. Full text search host restarts repeatedly under Linux (SQL Server 2019 rc 1)

    Under Linux, after enabling full text search in a table and during the population of the index, one sees frequent messages in the log

    Error '0x80004005' occurred during full-text index population for table or indexed view 'mytable' (table or indexed view ID '629173587', database ID '5'), full-text key value '5567999'. Attempt will be made to reindex it.

    In the errorlog there are many repeated messages like
    The fulltext filter daemon host (FDHost) process has stopped abnormally. This can occur if an incorrectly configured or malfunctioning linguistic component, such as a wordbreaker, stemmer or filter has caused an irrecoverable error during…

    17 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 →
  19. SQL 2016/17 Assertion Check Bug

    Running the code below will result in a severe error and stack dump, full error message below. Had others test in both 2016 and 2017 with same results. Looks like it's partially an implicit conversion issue, if you explicitly cast to DATE inside the EOMONTH no errors. It also causes the same error if you insert into a table with a datatype of DATE. It will crash even if no data is returned.

    Location: e:\b\s3\sources\sql\ntdbms\storeng\dfs\access\rowbucketprocessor.inl:658
    Expression: compsetoptypes(pValueInfo, pParamInfo) <= 0
    SPID: 82
    Process ID: 3000
    Msg 3624, Level 20, State 1, Line 38
    A system assertion check…

    17 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. bug - string_agg on external table results in "An error occurred while executing GlobalQuery operation: 100001;"

    string_agg on external table results in "An error occurred while executing GlobalQuery operation: 100001;Failed to generate query plan."

    declare @colList varchar(max) = ''
    select @colList=stringagg(quotename(colname), ',
    ') from dbo.external
    table --external table

    on "Microsoft SQL Azure (RTM) - 12.0.2000.8 May 15 2020 00:47:08 Copyright (C) 2019 Microsoft Corporation "

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

SQL Server

Categories

Feedback and Knowledge Base