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. Add SSIS to the SQL Server Docker Image

    I work for a large bank which it trying to move hundreds of SSIS packages into GitLab pipelines. This task would happen faster an easier with SSIS on the MS SQL Server Docker image.

    105 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  ·  Setup + Deployment  ·  Flag idea as inappropriate…  ·  Admin →
  2. 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,…

    105 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 →
  3. Correctly escape predefined entities in deadlock XML

    If you create a table that has &, ", <, or > in the name, it's not correctly escaped in all instances of deadlock XML.

    This causes an error when trying to convert extended event/system health data to valid XML for analysis.

    https://en.wikipedia.org/wiki/List_of_XML_and_HTML_character_entity_references

    99 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 →
  4. SSIS Script tasks losing code

    I have a very strange issue happening that is causing Script Task code to clear out. I have been able to test on 2-3 different machines. We are running SSDT 15.4 preview. The steps to reproduce were as follows.

    1. Create a script task inside of a foreach loop container. 2. Create a comment in the script task. 3. Change or add a variable mapping in the foreach. 4. Save package. 5. Open the script task and the comment will have vanished.

    As my last attempt for success,I have upgraded to 15.5.1 and the problem still exists.

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

    We’ll send you updates on this idea

    under review  ·  18 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  5. SQL Agent Job Chaining

    It would be a great feature to make SQL Agent Jobs hierarchical so that one (or more) job(s) can be dependent on another job allowing SQL Agent Jobs to be chained together.

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

    We’ll send you updates on this idea

    4 comments  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
  6. Script Tasks losing code in SSDT 17.1 for Visual Studio 2015

    I have an SSIS 2016 package with a number of For Each ADO Loops, each with a single script task, which are usually working fine.

    However, every so often, a script task will suddenly 'forget' all the code within it. I'm presented with an empty task editor which has reset to C# (if using VB), and lost the ReadOnlyVariable names.
    If I click Edit Script, I get a brand new script created in the editor. This can occur intermittently on reopening the package, or after editing _a different_ script task.

    The same thing also occurs with both VB and C#…

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

    We’ll send you updates on this idea

    27 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 47

    <=-=Jun 26 2017 1:02AM=-=>

    I have had the exact same issue, intermittently whenever I open script tasks all my code seems to have vanished and all the “ReadOnlyVaraiables” and “ReadWriteVariables” have also disappeared. It happens for c# and VB script tasks. This is is extremely frustrating and is impossible to develop a project when code randomly keeps disappearing. I’m wondering if this is a bug with Visual Studio 14.0.23107.0 D14REL. Can anyone at Microsoft shed some light on this please?

    <=-=Jun 29 2017 10:45PM=-=>

    Got the same problem. Seems like the Script Task is cleared when the project/Visual Studio i closed/shut down.

    <=-=Jul 18 2017 5:38PM=-=>

    Same problem for me, too. Once the original code in Script Task was still present after reloading the solution. But after that they are gone, along with ReadOnly/ReadWriteVariables and ConnectionManagers. I’m using SSDT 14.0.61705.170 (which just came out) with VS 14.0.25431.01 update…

  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…

    78 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 →
  8. 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…

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

    We’ll send you updates on this idea

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

    72 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    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.

  10. 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 →
  11. Bring Back TFS and Git in SSMS

    According to this post: https://blogs.technet.microsoft.com/dataplatforminsider/2016/11/21/source-control-in-sql-server-management-studio-ssms/ these features were removed "because they didn't fit in the overall experience" for SSMS. This seems like an incredibly short-sighted decision for your user base. Many SQL developers use SSMS specifically because it is lightweight and does not require the massive baggage and learning curve of the full VS. That doesn't mean we don't need source control or task tracking in our process. Removing functionality that integrates two Microsoft products seems like a massive step backward. The suggestion to "work around" this poor decision by manually editing a definition file comes across as particularly condescending,…

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

    We’ll send you updates on this idea

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

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

    We’ll send you updates on this idea

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

    63 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 →
  14. 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 →
  15. "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 →
  16. 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: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

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

    51 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 →
  18. 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…

    50 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 →
  19. 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.

    47 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  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
  20. Allow Asymmetric Key to be created from binary hex bytes string just like CREATE CERTIFICATE (SQLCLR / SSDT / Module Signing)

    Creating an Asymmetric Key (for the purpose of creating an associated Login to provide { EXTERNAL ACCESS | UNSAFE } ASSEMBLY permission for SQLCLR Assemblies and hence avoid setting TRUSTWORTHY ON) requires a file or EKM provider or Assembly. Prior to SQL Server 2017 it was at least possible to load an Assembly (marked as SAFE) that is signed with the same private key into [master] to then create the Asymmetric Key from, and then drop the Assembly.

    But, with SQL Server 2017, ALL Assemblies will require being signed with a matching key / cert -based Login having the UNSAFE…

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

    We’ll send you updates on this idea

    6 comments  ·  Setup + Deployment  ·  Flag idea as inappropriate…  ·  Admin →
  • Don't see your idea?

SQL Server

Categories

Feedback and Knowledge Base