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. Bug in SSMS on Statistics Properties screen

    There is a "Update statistics for these columns" checkbox In SQL Management Studio in Statistic Properties dialog.

    However when I click on this checkbox and press OK it has no effect what so ever. Statistics are not updated and when I try to script the action I get "There is no action to be scripted".

    1 vote
    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 →
  2. T-SQL Common Table Expression "Materialize" Option

    T-SQL Common Table Expression "Materialize" Option

    I have run into a couple performance and results issues using Common Table Expressions (CTEs).

    The first case is where I coded a data retrieval as a single, large query with a CTE that is referenced multiple times in the query. The CTE has an expensive query. I was able to determine, via execution plans, etc., that the results of the CTE were being recalculated multiple times, resulting in slow execution. I rewrote the data retrieval as multiple steps where the query of the CTE was first executed into a table variable, and I…

    108 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  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
  3. SET STATISTICS IO should also show schema name for tables

    When you use schemas in your database, and you have the same table names in multiple schemas, it is not clear, which schema the table belongs too in the output of the SET STATISTICS IO. E.g. a table called Profile, or configuration in different schema is very common at our databases

    Today the output is just
    Table 'Profile'. Scan count 1, logical reads 123, physical reads 1, read-ahead reads 121, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Profile'. Scan count 3, logical reads 13, physical reads 1, read-ahead reads 342, lob logical reads 0,…

    104 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  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
  4. ssms 18 hangs

    SSMS version 18.0 hangs when trying to connect to the Master database in Azure SQL DB, when the user does not have access to the Master database.

    1 vote
    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 →
  5. Maintenance Plan Portability

    Maintenance Plans should be scriptable, deployable and maintainable without the need to manually create/update a plan on each server/instance. Current functionality requires the DBA to manually generate a SQL Maintenance Plan one at a time for each instance in the environment. Built in SQL Maintenance Plans should be scriptable, deployable and maintainable with TSQL or in some automated fashion that will allow deployment to large enterprise environments that do not require the use of a GUI.

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

    We’ll send you updates on this idea

    under review  ·  7 comments  ·  Setup + Deployment  ·  Flag idea as inappropriate…  ·  Admin →
  6. In SQL Server Always oafter a failover, SQL Server keep the connections open without killing any session

    Hi Msft Team!
    I want to suggest one improvement to your sql server always on feature. Normally, once we perform failovers we need to ask app teams for maintenance windows... or in worst cases ,if this happens automatically, we need to discover the RCA of this because all the application or users connections had been terminated. Also many times that we had automatic failovers or perform failovers with long executions happening the secondary instance turned into recovery state because it was waiting for this session to finish to synchronize again. So, is it possible to add the capability for failovers…

    67 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  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
  7. 62 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  7 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  8. Issue Flat File Export

    When I export data from SQL Server into flat file, the wizarad ask for destination file name, locale, code page,...
    When I select English for locale, I can show the preview, but when press finish the export, I get this error message for validation

    Validating (Error)
    Messages
    Error 0xc00470b6: Data Flow Task 1: The LocaleID 9 is not installed on this system.
    (SQL Server Import and Export Wizard)

    Error 0xc004706b: Data Flow Task 1: "Destination - PRUEFAUF1_txt" failed validation and returned validation status "VS_ISBROKEN".
    (SQL Server Import and Export Wizard)

    Error 0xc004700c: Data Flow Task 1: One or more component…

    1 vote
    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. In-Memory OLTP - allow removal of Filegroup for memory_optimized_data

    It should be possible to remove the memory_optimized_data Filegroup and files, if all in-memory tables and procedures have been deleted.

    Currently you cannot do this "by design", however this is a potential blocker to people using this technology due to the "no-going-back once it's enabled". It is now several years since Hekaton was first released - support for this feature is long overdue!

    At present you get errors such as:
    Cannot drop the last memory-optimized container
    The filegroup ... cannot be removed because it is not empty.

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

    We’ll send you updates on this idea

    15 comments  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 3

    <=-=Jan 8 2018 12:16PM=-=>

    Thanks for the suggestion.
    We’ll consider it for a future release.

    Are there specific limitations imposed when you have the filegroup, e.g., no database snapshot, that hold you back from creating the filegroup?
    Or is it only the feeling of not being able to turn back?


    Jos de Bruijn – Database Systems PM

  10. UTF-8 in SQL 2019: An error occurred while executing batch. Error message is: Internal connection fatal error. Error state: 15, Token : 0

    In SQL Server 2019 CTP 2, the query below gets a fatal error when executing in a database that has a "_UTF8" default Collation. The fatal error is:

    "An error occurred while executing batch. Error message is: Internal connection fatal error. Error state: 15, Token : 0"

    and the connection is terminated.

    Better yet, if you go to the "Results" tab and scroll down to the bottom, it should completely crash SSMS (I tested in 17.9 ; have not yet tried 18.0). Of course, this is when using "TOP (142)" or greater. If using a value of 128 - 141…

    1 vote
    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 →
  11. Move measures between tables in SSAS Tabular

    In Power BI Desktop and Power Pivot, you can easily move a DAX measure between tables (since a measure isn't actually related to a table anyway).
    However, this is not possible in SSAS Tabular (current version: 2017). You can cut a measure, but only paste it again in the same table, not in a different table.
    The current process is to create a duplicate of the original measure with a different name, delete original measure and then rename the new measure. Time consuming.

    50 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  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
  12. SSMS 18.0 GA Create script for functions missing GO statements after SET statements

    SSMS 18.0 GA Create script for functions missing GO statements after SET statements.

    1 vote
    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 →
  13. Incorrect error message for missing parameter with sp_execute_external_script

    I use sp_execute_external_script and I spell a parameter name incorrectly. For instance:

    EXEC sp_execute_external_script @language = N'Python',
    @script = N'import re, pandas
    Ret = InputDataSet
    Ret["an"] = pandas.Series([re.sub("[^0-9]", "", i) for i in Ret["an"]], index = Ret.index, dtype = "int32")
    ',
    @input_data_1 = N'SELECT an FROM alphanum',
    @output_data = N'Ret'

    The error message is

    Msg 214, Level 16, State 175, Procedure sp_execute_external_script, Line 1 [Batch Start Line 4]
    Procedure expects parameter '@params' of type 'ntext/nchar/nvarchar'.

    Which is confusing , since sp_execute_external_script has a parameter by that name, but it is not mandatory.

    In fact, I get the same message, even…

    3 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 →
  14. Sql Server Management Studio / Always Encrypted Parametrization

    The order of the parameters in SSMS (v.17.9.1) seems to be critical when using encrypted and not encrypted columns in one statement.

    Declaring the not encrypted variable before encrypted vars works, the other way it doesn't work (ERROR: ncryption scheme mismatch for columns/variables .. see more details and example in the file attached.)

    best regards,
    Roman

    1 vote
    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. SSMS 18.0 RC1 is prompting for authentication upon scripting out an object from Object Explorer.

    I know this is happening on version 15.0.18098.0.

    The Scenario: You have a server in Object Explorer that you authenticated to. You navigate to a table in one of the databases. You right-click on the table. You choose "Script Table as" -> "CREATE To" -> "New Query Editor Window". You are then prompted with the "Connect to Database Engine" window for server selection and authentication.

    This is not something that happens in SSMS 17. I installed this yesterday and when I did so I choose the option to port over all my settings from 17. Everything else has been working…

    1 vote
    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. T-SQL for changing table column order

    Once a table is created, the ordinal position of the columns is fixed. This is especially problematic for large data warehouse tables, where new columns are expected to be added over time.

    The only solution currently to re-order columns is to drop and recreate the table (or let the SSMS designer do this for you), but this is often not feasible for a large, highly used table, and it cannot be easily scripted.

    Please add some ALTER syntax that will allow changing column order.

    16 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  ·  Other  ·  Flag idea as inappropriate…  ·  Admin →
  17. Parallel SELECT INTO from sys.messages causes intra-query deadlock

    Loading data into a new table from sys.messages using parallel SELECT INTO causes consistent self-deadlocks. Testing on a machine with default parallelism settings and 4 logical cores. This occurs in SQL Server 2014, 2016 and 2017. Here's a reproduction:

    BEGIN TRANSACTION;

    SELECT TOP 10000
    m.message_id, m.[text]
    INTO SomeNewTable
    FROM sys.messages m

    For some reason, removing the "BEGIN TRANSACTION" reduces the frequency of deadlocks caused by this query. I've attached a deadlock graph from the issue.

    It's possible that bulk loading from sources like sys.messages is simply not a supported scenario - but, if so, this should be documented (or potentially…

    11 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 →
  18. Tempdb spill columns in sys.dm_exec_query_stats should account for batch mode operator spills

    I am testing against SQL Server 2017 RTM-CU4. As far as I can tell, only tempdb spills associated with row mode operators are tracked in the new columns for tempdb spills in sys.dm_exec_query_stats: last_spills, total_spills, max_spills, and min_spills. Spills for batch mode operators are not included. None of the documentation that I can find mentions this as a limitation, so I assume that this is a bug.

    Batch mode execution brings additional challenges around memory grant management and tempdb spills are common for the workloads that I look at. Adaptive query memory feedback is not always sufficient to resolve all…

    34 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. "Call Stack"

    Provide DMV to provide the call stack of stored procedures, triggers, CLR functions etc prefferable in JSON format so it is parsable.

    As Erland Sommarskog says this is available but only in the "event Blocked Process Report:

    <executionStack>
    <frame line="17" stmtstart="1332" stmtend="2906" sqlhandle="0x03000d00399f00558c840601a29800000100000000000000"/>
    <frame line="1" sqlhandle="0x01000d00638e300f302e10ac000000000000000000000000"/>
    </executionStack>

    That is, here the call stack is available. Through the sqlhandle it is possible to retrieve
    the objectids of the calling procedures.

    If if it's available this way, it can't be too difficult to expose this in a DMV. Only exposing the
    sqlhandle would not match with my observation with permissions above, but…

    59 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  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
  20. OPENROWSET T-SQL and MOLAP provider fail on complex DAX statements

    Running the OpenRowSet (SQL2016) against a SSAS 2017 on-premise or Azure SSAS will fail with error

    OLE DB provider "MSOLAP" for linked server "(null)" returned message "OLE DB error: OLE DB or ODBC error: An unexpected exception occurred.."

    T-SQL Statement:

    SELECT *
    FROM OpenRowset('MSOLAP','DATASOURCE=<Servername>;Initial Catalog=<cube>;User ID=<userid>;Password=<password>’,
    '<DAX STATEMENT>’)

    Running the DAX directly against SSAS (2017) returns the correct results, also the OpenRowSet command works fine in SSAS 2016 using the same DAX statement.

    Bug maybe due to the properties differences in the OpenRowSet implementation rather than the MOLAP provider (these are the XML properties included in the MDX query to…

    38 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