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

    7 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. Support compression on TDE enabled database transaction log backup from log shipping.

    With SQL 2016, it becomes possible to compress TDE enabled database backup using explicit option of MAXTRANSFERSIZE if it is greater than default 64K.

    However for log shipping, it seems impossible to compress LS generated transaction log backup if database is TDE encrypted. This is because the system stored procedure spaddlogshippingprimary_database does not offer parameter @maxtransfersize, therefore compression is not kicked in.

    Is this a bug or by design? Will it be fixed in future release?

    7 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 →
  4. Microsoft.SqlServer.Types - Unable to find an entry point named 'SetClrFeatureSwitchMap' in DLL 'SqlServerSpatial110.dll'.

    When trying the use EntityFramework to save changes to an Entity with a Geo-Spatial datatype I get this error.

    Unable to find an entry point named 'SetClrFeatureSwitchMap' in DLL 'SqlServerSpatial110.dll'.
    at Microsoft.SqlServer.Types.GLNativeMethods.SetClrFeatureSwitchMap(Int32 clrFeatureSwitchMap)
    at Microsoft.SqlServer.Types.SqlGeometry.IsValidExpensive()
    at Microsoft.SqlServer.Types.SqlGeometry..ctor(GeoData g, Int32 srid)
    at Microsoft.SqlServer.Types.SqlGeometry.Construct(GeoData g, Int32 srid)
    at Microsoft.SqlServer.Types.SqlGeometry.GeometryFromText(OpenGisType type, SqlChars text, Int32 srid)
    at Microsoft.SqlServer.Types.SqlGeometry.STGeomFromText(SqlChars geometryTaggedText, Int32 srid)
    at Microsoft.SqlServer.Types.SqlGeometry.Parse(SqlString s)

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

    <=-=Jan 14 2016 2:35AM=-=>

    I think this is caused by new dlls from SQL Server 2012 SP3. One system with Microsoft.SqlServer.Types.dll file version 2011.110.5058.0 works, another with 2011.110.2011.60 breaks with this error.

    <=-=Jan 14 2016 4:36AM=-=>

    Cause tracked down to version Microsoft.SqlServer.Types.dll v11.0.0 (file version: 2011.110.2011.60 ) which comes as part of MS SQL Server 2012 SP3.
    SP2 is fine and has Microsoft.SqlServer.Types.dll v11.0.0 (file version: 2011.110.5058.0 ) so I removed SP3 from my system but it was still in my GAC (check C:\Windows\assembly and look at properties of the file)
    so had to do the below to overwrite it. That solved it for me.

    >gacutil /i “path_to_the_older_nonbroken_version_of_\Microsoft.SqlServer.Types.dll” /f

    <=-=Jan 14 2016 5:31AM=-=>

    I think I may have got the version of the SP3 dll wrong, it was 11.0.6020.0

    <=-=Jan 14 2016 6:15AM=-=>

    Sorry confused myself. Version which is broken for me is Microsoft.SqlServer.Types.dll File version = 2011.110.6020,…

  5. Cannot join database to AG using GUI if replica SQL Instance is not running on default port 1433

    I am running a cluster of 2 nodes where the SQL service is not running on the default port 1433. When I try to add a database to the AG using GUI I get stuck at �Connect to Existing Secondary Replicas� window. I click on Connect but the �Connect to Server� login form has the server name with no port specified and I cannot modify it as it is grayed out.

    Similar issue when the replica has multiple IPs and you specify the SQL instance to listen to a certain IP. When connecting to existing Secondary replicas you might get…

    7 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 →
  6. fn_hadr_backup_is_preferred_replica fails for Cluster Type = "NONE" Availability Groups

    The function fnhadrbackupispreferred_replica fails when executed for a database that is a member of a clusterless Availability Group. As this function is used by Maintenance Plans to determine replica preference, it is not possible in RC2 to use maintenance plans that do not ignore backup preferences.

    Can reproduce on SQL Server 14.0.900 with SSMS 17.2 and 17.3.

    For example, if wideworldimporters is a member of an Availability Group with Cluster Type = "NONE":

    SELECT [master].sys.fnhadrbackupispreferred_replica('wideworldimporters');

    Msg 41005, Level 16, State 1, Line 1
    Failed to obtain the Windows Server Failover Clustering (WSFC)…

    7 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

    <=-=Oct 4 2017 8:49PM=-=>

    Confirmed this is present in 14.0.1000.169

    <=-=Oct 31 2017 1:55AM=-=>

    The problem still continues with CU1,
    When I run the query “SELECT [master].sys.fn_hadr_backup_is_preferred_replica(‘dbname’)” it throws the exception “Could not process the operation. Always On Availability Groups does not have permissions to access the Windows Server Failover Clustering (WSFC) cluster. Disable and re-enable Always On Availability Groups by using the SQL Server Configuration Manager. Then, restart the SQL Server service, and retry the currently operation. For information about how to enable and disable Always On Availability Groups, see SQL Server Books Online.”.
    There is no cluster, this is clusterless topology.

  7. SSDT Clustered ColumnStore Rebuild Behaviour

    When adding a column to a table with a clustered column store index SSDT performs the following actions:
    1) Drop the CCS
    2) Adds the new column
    3) Adds a standard clustered index (non unique) on the first column
    4) Adds a clustered columnstore index using with DROP EXISTING ON

    It should just add the column - there is no need to drop and create the index twice.

    7 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 →
  8. SQL Server 2016 SP1 CU1. Internal Query Processor Error: The query processor could not obtain access to a required interface.

    When i try to use partitioning function in a join's redicates over a columnstore table a get the error:

    Msg 8601, Level 17, State 46, Line 28
    Internal Query Processor Error: The query processor could not obtain access to a required interface.

    The bug can be reproduced at least on

    Microsoft SQL Server 2016 (SP1-CU1) (KB3208177) - 13.0.4411.0 (X64)   Jan  6 2017 14:24:37   
    
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 &lt;X64&gt; (Build 9600: )

    You can reproduce the behaviour. Just use the script from "Steps to Reproduce".

    7 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. Type conversion in may affect CardinalityEstimate - Convert/cast on selected columns

    Type conversion in may affect CardinalityEstimate - Convert/cast on selected columns --- It is viable that this warning be cited when columns are filtered on and the filtering clause involves cast/convert function.

    But, it seems to be an overreach when converted/casted columns are simply cited in the selected / projected column list and not at all in filtering clause.

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

    Upvotes: 1

    <=-=Dec 28 2017 10:38AM=-=>

    Defect #11427260 has been filed with our Product group on your behalf – Karly Shockley

    <=-=Dec 28 2017 11:34AM=-=>

    Shared here…

    Transact SQL � Warning � �Type conversion in expression may affect �CardinalityEstimate� in query plan choice�
    https://learningintheopen.org/2017/12/13/transactsqlwarningtypeconversion-in-expression-may-affect-cardinalityestimateinqueryplanchoice/

  10. CDC capture job stops after a while in SQL 2017

    I created a new simple database with one table in SQL 2017 (NOTE! An upgraded one from 2014). After a while and doing some CRUDs the capture job fails and stops. Here are details :

    Table definition :

    CREATE TABLE [dbo].[Table_1](

    [Id] [INT] NOT NULL,
    
    [name] [NVARCHAR](1000) NULL,
    [image] [VARBINARY](MAX) NULL,

    CONSTRAINT [PKTable1] PRIMARY KEY CLUSTERED
    (

    [Id] ASC
    

    )WITH (PADINDEX = OFF, STATISTICSNORECOMPUTE = OFF, IGNOREDUPKEY = OFF, ALLOWROWLOCKS = ON, ALLOWPAGELOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO

    Job Error (after 10 retries)

    Date 12/27/2017…

    7 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 →
  11. 18.2 Tabbing Bug Not Updating Query Window Text

    If I have multiple query tabs open at a time, switching between tabs causes a lag in actual displaying the content of the new tab.

    For example, if I have two tabs open with different queries on each tab. If I switch from one tab to another, the content doesn't appear to change in the query window, but if I highlight the text, then it'll change. This has been happening since the 18.0 GA release and I was hoping subsequent updates would fix it, but they have not. I have attached three files.

    6 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 →
  12. Avoid False-Positive reports of SQL Injection attacks from Azure Databricks

    We are receiving false-positive reports of SQL Injection attacks based on enclosing a candidate SQL statement into a sub-query and adding a "where 1=0" clause when using Azure Databricks. These reports cannot be ignored and Administrator time is wasted in reviewing these. If the connection was correctly identified, and not simply marked as "Microsoft JDBC Driver for SQL Server" or some filtering method was available, this could be avoided.

    6 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. Extended Event : "sp_cache_miss" event is not capturing data

    I have setup an extended event session for spcachemiss event. Somehow session is not capturing any data.

    If I setup profiler trace on the same event it works fine. Strange thing is if I keep both profiler trace and Extended Event session running at the same time, XE session captures data.

    I am stumped why XE session is not capturing data, please help.

    I have tested this against SQL2016 SP2-CU3 as well as on latest build Microsoft SQL Server 2016 (SP2-CU7-OD) (KB4508636) - 13.0.5343.1

    Here is the extended event session definition I am using.

    CREATE EVENT SESSION [CacheMisses]…

    6 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 →
  14. Slow or non-operational Query Store under legacy cardinality estimator

    In many of our customers environment Query Store is completely non-operational or very slow. No report of Top Resource Consuming Queries is generated in timely fashion, no matter what configuration options is set. Other reports with some background data behave the same. So the tool is practically useless.

    If I try any SELECT statement that is generated by Query Store run in standalone SSMS windows, result is the same - did not finish.

    I found out it's because our databases are in legacy cardinality estimation mode. When I run appropriate SELECT with OPTION (QUERYTRACEON 2312) the result appear in acceptable…

    6 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 →
  15. AG wizard does not run sp_control_dbmasterkey_password for databases with DMK.

    There is a bug with AG add database wizard for databases with DMK, it does not run spcontroldbmasterkey_password on replicas as expected.

    In the AG Add database wizard, if you select:
    'Full Database and Log Backup', it works as expected, and the credentials are created on the replicas.
    'Automatic Seeding' to add the database, it does not run spcontroldbmasterkey_password.

    If Automatic Seeding is selected, in the validation check "Checking password of the database master key", it says it has skipped it because: "Skipped Checking password of the database master key because you skipped initial data synchronization."

    6 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 →
  16. Accelerated Database Recovery - Incorrect Syntax Error

    I was recently working with some of the new features of SQL 2016 and 2019, found that a combination of two was causing me a lot of headaches. Not sure if you have come across this scenario but:

    While working with an Always Encrypted connection and to enable yourself to see the data you have to turn on one of the advanced setting in your connection by using - "Column Encryption Setting=Enabled".
    While this setting is used in your connection to a specific database, you will not be able to ALTER DATABASE to SET a setting like ACCELERATEDDATABASERECOVERY…

    6 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 →
  17. Query Store "Log Memory Used" metric calculation is wrong

    I used XE to capture the query being executed when viewing the "Log Memory Used (KB)" metric, and noticed it performs this calculation:

    ROUND(CONVERT(float, SUM(rs.avglogbytesused*rs.countexecutions))*1024,2) totallogbytes_used

    As you can see, it's attempting to convert bytes to kilobytes by multiplying the bytes by 1024. It should be dividing the bytes by 1024. As it stands, the values displayed in the Query Store reports are off by about a factor of a million.

    There's a repro in this Database Administrators Stack Exchange post: https://dba.stackexchange.com/questions/231682/what-is-log-memory-in-query-store-2017

    I ran an insert that generated 346,796 avglogbytesused in

    6 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. Bad cardinality estimate for subquery after patches / QUERYTRACEON 4199

    We run the latest version of SQL Server 2017(14.0.3048.4).
    When running the following query, it runs fine:

      SELECT
    
    (SELECT TOP 1 ROLEDEFINITION.NAME -- Rolle for ansvarlig dommer
    FROM AKTOER
    INNER JOIN PERSON ON PERSON.ID = AKTOER.PERSON_ID
    INNER JOIN ROLEDEFINITION ON AKTOER.ANSATTROLLEDEFSNAPSHOT = ROLEDEFINITION.ID
    WHERE AKTOER.SAK_ID = S.SAK_ID AND PERSON.ORGUNIT_ID = S.DOMMER_ORG_ID
    ORDER BY COALESCE(TODATE, &#39;9999-01-01&#39;) DESC)
    FROM SAK S
    INNER JOIN #AktuelleSaker AS AKTUELLE_SAKER ON AKTUELLE_SAKER.SAK_ID = S.SAK_ID option(recompile)

    However, If we add option(recompile, QUERYTRACEON 4199)
    it use a very wrong cardinality estimate. I included the good(no query optimizer fixes) and bad plan(query optimizer fixes).

    I have tried to run…

    6 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. 6 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. filegroup to read_only prevents dbcc checkdb from running

    My database uses a combination of partitions and file groups in a sliding window configuration. I have implemented the VLDB CheckDB pattern and found that all file groups must be in a read-write state for this to work. By placing some older file groups into a read-only state I was hoping to utilize partial backups to reduce backup times. As a workaround we are temporarily placing the file groups back into a readwrite state to allow the checks to continue - however to make sure our FG backups can be used in a restore the FG backups have to be…

    6 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