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.

Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

(thinking…)

Enter your idea and we'll search to see if someone has already suggested it.

If a similar idea already exists, you can support and comment on it.

If it doesn't exist, you can post your idea so others can support it.

Enter your idea and we'll search to see if someone has already suggested it.

  • Hot ideas
  • Top ideas
  • New ideas
  • My feedback
  1. Add language and optimizer support for ISO <distinct predicate>

    SQL 1999 and later include the <distinct predicate> IS [NOT] DISTINCT FROM.

    The definition of distinct is (quoting from the 2003 standard) "informally, not equal, not both null." This is consistent with where SQL Server (following the standard) already uses the keyword DISTINCT. Adding <distinct predicate> to T-SQL would simplify coding of joins, in particular, and as of 2008, MERGE statements in a variety of typical scenarios.

    Example:

    SELECT T1.this, T2.that
    FROM T1 JOIN T2
    ON T1.entry IS NOT DISTINCT FROM T2.entry

    Currently, this must be written as

    SELECT T1.this, T2.that
    FROM T1 JOIN T2
    ON (
    T1.entry = T2.entry …

    72 votes
    Sign in
    (thinking…)
    Sign in with: oidc
    Signed in as (Sign out)

    We’ll send you updates on this idea

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

    Upvotes: 289

    <=-=Aug 9 2007 9:12AM=-=>

    Benefits:
    Improved Readability, therefore Maintainablity, therefore reduced manpower $ and time expenditure.

    <=-=Aug 27 2007 6:13PM=-=>

    I definitely see the value of this. Thanks for proposing it. We’ll try to squeeze it in to SQL Server 2008 but things are really tight in terms of room for changes like this. It has to compete with many other things, including a bunch that have a larger impact on query performance, or that don’t have an easy workaround. This issue has a workaround, though it is not pretty and programmability would be enhanced a lot with the proposed enhancement. I’ll see what I can do.

    Best regards,
    Eric

    <=-=Oct 17 2007 2:06PM=-=>

    Things do not look good for this enhancement for Katmai. It probably will not make it into the release. We’ll make a final assessment in a couple of weeks. Before we can consider this,…

  2. AlwaysOn Availability Groups - Handling of Instance Level Objects

    In todays implementation when using AlwaysOn Availability Groups the DBA has to take care of the so called Instance Level Objects.
    Without this an (automatic) failover will fail so this is a very sensitive and important task.
    Instance Level Objects span a multitude of objects like logins, SSIS packages or other binaries, Agent Jobs/Schedules, Linked Servers, encryption keys and certificates.

    The recommendation provided by you is to create automated jobs to sync these items regularly.

    I request that you provide these 'syncing' functionality as part of the product out-of-the-box.
    Clearly you cannot know 3rd party dependencies nevertheless there are a…

    69 votes
    Sign in
    (thinking…)
    Sign in with: oidc
    Signed in as (Sign out)

    We’ll send you updates on this idea

    2 comments  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
  3. 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…

    66 votes
    Sign in
    (thinking…)
    Sign in with: oidc
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  8 comments  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
  4. Add support for ANSI standard row value constructors

    The ANSI standards for SQL define a concept of row value constructors. These make it possible to write, for instance,

    WHERE (col1, col2) NOT IN (SELECT col1, col2 FROM SomeOtherTable)

    SQL Server does not currently support this constructions

    66 votes
    Sign in
    (thinking…)
    Sign in with: oidc
    Signed in as (Sign out)

    We’ll send you updates on this idea

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

    Upvotes: 176

    <=-=Nov 13 2007 12:37AM=-=>

    Hello

    Thank you for your feedback. We’re certainly considering row value constructors for a future release of SQL Server.

    - Sara Tahir
    Microsoft SQL Server

    <=-=Aug 11 2010 8:03AM=-=>

    I think row constructors would be a great and important addition to T-SQL. Just wanted to point out a few more cases that I’d love to see implemented:


    - Assignment
    -
    —————————————————————————————————-

    UPDATE dbo.T1
    SET (c1, c2, c3) = (@p1, @p2, @p3)
    WHERE keycol = @key;

    — Logically equivalent to:

    UPDATE dbo.T1
    SET c1 = @p1,
    c2 = @p2,
    c3 = @p3
    WHERE keycol = @key;

    — Or with a subquery:

    UPDATE dbo.T1
    SET (c1, c2, c3) = (SELECT T2.c1, T2.c2, T2.c3
    FROM T2
    WHERE T2.keycol = T1.keycol)
    WHERE keycol = @key;

    — Logically equivalent to:
    UPDATE dbo.T1
    SET c1 = (SELECT T2.c1
    FROM T2
    WHERE T2.keycol = T1.keycol),
    c2 =…

  5. SSAS Designer showing empty phantom/duplicate tables

    Working with an SSAS multidimensional cube that was developed using SSDT 17.3, the Data Source View for the cube is showing multiple phantom tables as the data source view is changed/refreshed. This is happening when looking at the Data Source View for both cubes as well as dimensions.

    The Data Source View object itself does not show any issues, cube functionality does not appear to be affected when it is deployed and processed.

    When trying to use the "Copy Diagram from" function, Visual Studio crashes on me.

    The issue has persisted as I've upgraded to 17.4, as well as testing…

    60 votes
    Sign in
    (thinking…)
    Sign in with: oidc
    Signed in as (Sign out)

    We’ll send you updates on this idea

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

    60 votes
    Sign in
    (thinking…)
    Sign in with: oidc
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  7 comments  ·  Setup + Deployment  ·  Flag idea as inappropriate…  ·  Admin →
  7. TRY-CATCH should always work

    Consider this:

    CREATE PROCEDURE inner_sp AS
    BEGIN TRY
    PRINT 'inner_sp starting'
    SELECT col FROM doesnotexist
    PRINT 'inner_sp stopping'
    END TRY
    BEGIN CATCH
    PRINT 'Let''s catch the error: ' + error_message()
    END CATCH
    go
    SET XACT_ABORT OFF
    EXEC inner_sp

    It's reasonable to assume that this would print:

    Let's catch the error: Invalid object name 'doesnotexist'.

    But in fact the output is

    inner_sp starting
    Msg 208, Level 16, State 1, Procedure inner_sp, Line 4
    Invalid object name 'doesnotexist'

    That is, the CATCH handler is not activated. This is true for all binding errors that occurs at run time. Note that this includes…

    59 votes
    Sign in
    (thinking…)
    Sign in with: oidc
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  3 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  8. 59 votes
    Sign in
    (thinking…)
    Sign in with: oidc
    Signed in as (Sign out)

    We’ll send you updates on this idea

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

    57 votes
    Sign in
    (thinking…)
    Sign in with: oidc
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  2 comments  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
  10. dont remove debugging from ssms 18.0

    what is ssms:

    SSMS is an integrated environment for managing any SQL infrastructure, from SQL Server to Azure SQL Database. SSMS provides tools to configure, monitor, and administer instances of SQL. Use SSMS to deploy, monitor, and upgrade the data-tier components used by your applications, as well as build queries and scripts.

    --> build queries and scripts

    if you remove debugging from ssms --> https://cloudblogs.microsoft.com/sqlserver/2018/10/03/ssms-18-0-public-preview-released/

    ssms will be degraded to an simple editor with some nice helpers to write sql but not an integrated enveronment to write scripts and common database objects.

    every developer will write good and working code,…

    55 votes
    Sign in
    (thinking…)
    Sign in with: oidc
    Signed in as (Sign out)

    We’ll send you updates on this idea

    11 comments  ·  Other  ·  Flag idea as inappropriate…  ·  Admin →
  11. Column Store Deadlock XML Is Missing Information

    Deadlock XML for column store indexes is missing several pieces of information about which objects were deadlocked in the resource-list section of the XML

    See here for background https://www.brentozar.com/archive/2018/04/column-store-deadlocks-missing-information/

    Also attaching files.

    55 votes
    Sign in
    (thinking…)
    Sign in with: oidc
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  0 comments  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
  12. Wrong Results When Selecting SQLServer2016 JOIN DATETIME2(3) with DATETIME

    I'm getting unexpected results when joining between DATETIME2(3) column to DATETIME column with PK , in SQLServer 2016.

    The details are :

    I have the following table:

    CREATE TABLE DATETIME_TEST (
    [DATETIME] DATETIME NOT NULL,
    [DATETIME2_3] DATETIME2(3));
    ALTER TABLE DATETIME_TEST ADD CONSTRAINT PK_DATETIME_TEST PRIMARY KEY ([DATETIME]);
    INSERT INTO DATETIME_TEST
    ( [DATETIME],[DATETIME2_3])
    VALUES
    ('20020202 02:02:02.000', '20020202 02:02:02.000' ),
    ('20020202 02:02:02.003', '20020202 02:02:02.003' ),
    ('20020202 02:02:02.007', '20020202 02:02:02.007' ),
    ('2019-04-28 07:23:29.447', '2019-04-28 07:23:29.447' )
    ;

    SELECT * FROM DATETIME_TEST WHERE CONVERT(DATETIME2(3), [DATETIME]) = [DATETIME2_3]
    The results :

    DATETIME DATETIME2_3
    2002-02-02 02:02:02.000 2002-02-02 02:02:02.000
    2002-02-02 02:02:02.003 2002-02-02 02:02:02.003
    2002-02-02 02:02:02.007 2002-02-02 02:02:02.007
    2019-04-28…

    53 votes
    Sign in
    (thinking…)
    Sign in with: oidc
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  13. HTTP Request Function

    I have a scalar CLR function I created that allows me to make HTTP Requests and return the response. It would be awesome if SQL Server had a function built in that could do it.

    This has proven to be super helpful time and time again with the biggest use case being querying web APIs directly from the database without having to involve any other programming language or integration tools to load the data into a database before being able to query it.
    Often these APIs provide data back in either JSON or XML and SQL Server already has a…

    51 votes
    Sign in
    (thinking…)
    Sign in with: oidc
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
  14. SQL Server 2017 Minimal Logging Not Behaving As Documented

    According to my interpretation of the docs (https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008/dd425070(v=sql.100)) the following scenarios should minimally log...

    Recovery model must be Simple or Bulk Logged
    Table must be either
    A heap and insert done with TABLOCK
    A heap + nonclustered index with TABLOCK and either trace flag 610 or SQL Server 2016+
    A clustered index with no data and TABLOCK
    A clustered index with no data and either trace flag 610 or SQL Server 2016+
    A clustered index with data and trace flag 610 or SQL Server 2016+
    A clustered index with nonclustered indexes and TABLOCK and trace flag 610 or…

    47 votes
    Sign in
    (thinking…)
    Sign in with: oidc
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  15. Add language support for null treatment clause (RESPECT NULLS | IGNORE NULLS) with offset window functions (LAG, LEAD, FIRST_VALUE

    There�s a common need with offset window functions (LAG, LEAD, FIRST_VALUE, LAST_VALUE) to request to ignore NULLs. A classic example is to return the last non-NULL in an ordered stream of values. Consider the following sample data:

    DROP TABLE IF EXISTS dbo.T1;

    CREATE TABLE dbo.T1
    (
    id INT NOT NULL CONSTRAINT PK_T1 PRIMARY KEY,
    col1 INT NULL
    );

    INSERT INTO dbo.T1(id, col1) VALUES
    ( 2, NULL),
    ( 3, 10),
    ( 5, -1),
    ( 7, NULL),
    (11, NULL),
    (13, -12),
    (17, NULL),
    (19, NULL),
    (23, 1759);

    The id column represents the order of the events, and whenever the col1 value is…

    47 votes
    Sign in
    (thinking…)
    Sign in with: oidc
    Signed in as (Sign out)

    We’ll send you updates on this idea

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

    Upvotes: 60

    <=-=Mar 29 2017 11:32PM=-=>

    Just wanted to add that both Oracle and DB2 seem to support this feature:

    http://docs.oracle.com/database/122/SQLRF/LAG.htm#SQLRF00652
    https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_sqs_1513.htm

    Cheers,
    Itzik

    <=-=Apr 16 2017 10:08PM=-=>

    That last IBM link was for Informix!

    The DB2 link is https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0023461.html

    <=-=Jul 4 2017 3:30AM=-=>

    Thanks for reporting this request.
    It is in our backlog, and we have planned to do something like this, but we cannot confirm when it would be completed.

    Jovan

    <=-=Jul 10 2017 11:54AM=-=>

    Thanks, Jovan; good to hear.

  16. Database Diagrams

    Please add Database Diagrams back in SQL SERVER Management Studio

    46 votes
    Sign in
    (thinking…)
    Sign in with: oidc
    Signed in as (Sign out)

    We’ll send you updates on this idea

    22 comments  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
  17. Replace the import wizard with Power Query

    Instead of using the outdated import wizard (not the new one that was released recently in SSMS), a new one should be created that leverages the Power Query engine (aka the M language) to import data into SQL Server.

    45 votes
    Sign in
    (thinking…)
    Sign in with: oidc
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  3 comments  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
  18. Ability to upgrade SQL Server Management Studio directly from About menu

    I think it will be really cool to update the management studio directly from the about menu when it is open and also it for checking for new versions and notify us.

    Thanks in advance

    43 votes
    Sign in
    (thinking…)
    Sign in with: oidc
    Signed in as (Sign out)

    We’ll send you updates on this idea

    4 comments  ·  Setup + Deployment  ·  Flag idea as inappropriate…  ·  Admin →

    We have a “Check for updates link” in the Tools menu today, and SSMS prompts you to upgrade when it detects a new version unless you have turned off auto-check for updates. We have no plans to further automate these updates. We are pushing more updates through WSUS for enterprise installations.

  19. Change Tracking auto cleanup error 22123

    Just upgraded to 2016 SP2 (13.0.5026.0) and receiving frequent error messages logged from change tracking auto cleanup: Error 22123, severity 16, state 1 Change Tracking autocleanup is blocked on side table of "<***>". If the failure persists, check if the table "<***>" is blocked by any process . Routinely for different tables, not repeatedly from the same table.

    I suspect this was added as part of the changes to auto cleanup released in SP2, and is raised when a table is blocked and marked for retry. Can anyone please confirm this?

    Additionally, this seems to me like more of an…

    41 votes
    Sign in
    (thinking…)
    Sign in with: oidc
    Signed in as (Sign out)

    We’ll send you updates on this idea

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

    41 votes
    Sign in
    (thinking…)
    Sign in with: oidc
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  3 comments  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
  • Don't see your idea?

SQL Server

Feedback and Knowledge Base