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 Stack Overflow or MSDN.


  • Hot ideas
  • Top ideas
  • New ideas
  • My feedback
  1. Relax restriction that table parameters must be readonly when SPs call each other.

    I was excited when I heard that Katmai would have table parameters, but I was
    equally disappointed to hear that they would be readonly. Today programmers
    employ various tricks to pass table data between stored procedures - or give up
    and use cursors to call procedures row by row.

    While only readonly table parameters is useful when passing data from client to
    server, they are of little use when stored procedures call each other.

    For a longer discussion why read-write table parameters are essential, see
    http://www.sommarskog.se/tableparam.html where I discuss the topic in more
    detail, and argues more strongly than the…

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

    Upvotes: 337

    <=-=Oct 1 2007 2:40AM=-=>

    I could not agree less.This is more of ‘SHOULD HAVE’ and every T-SQL developer would love SQL server even more should this be implemented.

    <=-=Oct 4 2007 2:22PM=-=>

    I agree. Microsoft, if you’re listening, please implement this!

    <=-=Oct 10 2007 7:33AM=-=>

    I think it is very important.

    Alex Kuznetsov,
    SQL Server MVP

    <=-=Oct 22 2007 2:25AM=-=>

    I agree. It would be a very nice and powerfull feature in SQL server 2008.

    <=-=Oct 22 2007 5:57AM=-=>

    Unless there is a very good reason not to, I urge you to implement it, it would be very useful.

    <=-=Nov 4 2007 11:40AM=-=>

    TVPs should definitely not be limited to being read-only. Without updateability, they will be perceived as an “impaired” new feature. As lead author in the upcoming Programming Microsoft SQL Server 2008 book by MS Press, I urge the SQL team to support…

  2. Default Members on Analysis Services Tabular models

    In multidimensional, we can set the "IsAggregatable" property to False on an attribute hierarchy and choose a Default Member. This has the advantage that end-users will not be able to do multiselects on the hierarchy, and additionally the "All" member will disappear from MDX clients. This is very useful when building functionality for Currency handling, Budget Version selection, etc.

    We can simulate the same functionality in Tabular using DAX (ISFILTERED), but this is not optimal for several reasons:


    • The client tool will still display the hierarchy as if multiselect is possible

    • There will still be an "All" member on the…
    21 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

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

    Upvotes: 5

    <=-=Oct 4 2016 2:46PM=-=>

    Thank you for the suggestion, Otykier.

    We have a corresponding item on our backlog for the next release of SQL Server, but no promises. We’ll see if we can fit this in, but it may not make it given that there are other higher priority items.

    Best,
    The Analysis Services Team

    <=-=Apr 12 2017 12:33PM=-=>

    +1 … Non-aggregatable hierarchies and snapshot fact tables are much more cumbersome to query in tabular vs. multidimensional. Adding in the “IsAggregatable” flag and allowing for default members would be a huge improvement to the product imho.

    <=-=Dec 20 2017 5:08AM=-=>

    Hi,
    Anything new regarding this ‘Default Member’ feature?
    ETA?

    thanks,
    Nearsham

  3. 20 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 →
  4. Let Resource Governor control tempdb usage

    On a system with many different systems and ad-hoc requests it is not enough
    to control memory and cpu. Even RG can control IO too, it's not much help if IO is not the bottleneck on fast SAN.
    But crazy code can results in hundreds of Gb's tempdb usage.
    I would like to define particular resource group only to consume limited amount of tempdb (quota).
    tempdb is shared resource and scale-out (on other servers) not is an easy task, if the system not was designed that way from the beginning.

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

    We’ll send you updates on this idea

    1 comment  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
  5. Dynamic data deidentification (dynamic data masking enhancement)

    Existing data masking feature has limitation - when query is executed it compares the data to actual value stored in row. This allows brut force attack on dynamically data masked column to output the actual record for the column which is attacked. Even though presented column will display masked number but attacker knows the original value of that field.

    Instead of comparing the original value stored inside the database, if feature allows me with trace flag or any other way to compare data to masked value. It will result in completely unknown records and attacker will not be able to…

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

    We’ll send you updates on this idea

    1 comment  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
  6. Add a KEEP NULLS clause as part of the UNPIVOT operator's syntax

    With the current design of the UNPIVOT operator, SQL Server implicitly removes rows where the unpivoted measure is NULL. Consider for example a table called CustOrders with the following data:

    custid 2017 2018 2019


    1 NULL 2022.50 2250.50
    2 88.80 799.75 514.40
    3 403.20 5960.78 660.00
    4 1379.00 6406.90 5604.75
    5 4324.40 13849.02 6754.16
    6 NULL 1079.80 2160.00
    7 9986.20 7817.88 730.00
    8 982.00 3026.85 224.00
    9 4074.28 11208.36 6680.61
    10 1832.80 7630.25 11338.56
    11 479.40 3179.50 2431.00
    12 NULL 238.00 1576.80
    13 100.80 NULL NULL
    14 1674.22 6516.40 4158.26
    15 2169.00 1128.00 513.75
    16 NULL 787.60 931.50
    17 533.60…

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

    We’ll send you updates on this idea

    1 comment  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
  7. Enhance STRING_AGG to support multiple ORDER BY

    declare @t as table (COLUMN_NAME varchar(100))

    insert into @t values('a'), ('b'), ('c'), ('d')

    SELECT
    STRINGAGG(COLUMNNAME,',') WITHIN GROUP (ORDER BY COLUMNNAME DESC),
    STRING
    AGG(COLUMNNAME,',') WITHIN GROUP (ORDER BY COLUMNNAME ASC)
    FROM @t

    Error: Multiple ordered aggregate functions in the same scope have mutually incompatible orderings.

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

    We’ll send you updates on this idea

    0 comments  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
  8. Create an Azure Master Data Services service

    Master Data Services, as part of Sql Server, with its excel interface is a brilliant tool for maintenance of reference data, hierarchies and manual maintained groupings for analytics and data warehouse purposes.

    It would make a very good Azure service. It would give Microsoft the opportunity to rebuild the administrative interface to a modern, Azure like interface.

    The Excel interface is the brilliance of it, and to call an Azure MDS service a very simple thing to maintain.

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

    We’ll send you updates on this idea

    0 comments  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
  9. Reporting Services subscription jobs naming

    SSRS creates subscription job names with the guid of the schedule. Result: messy lists of SSRS Agent jobs and user defined Jobs in SSMS.

    SSRS should have a (configurable) prefix like SSRS; job name would be: SSRS.16EDA7C0-D3DB-4E0F-9394-A8E8C570BB77

    19 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 →
  10. SSRS 2016 Report Descriptions Missing from Tiles

    We have an SSRS 2012 Native Mode instance we are looking to upgrade to SSRS 2016 SP1 Native Mode. In SSRS 2012, the report Descriptions appear on the report Tiles. We put a lot of work into carefully crafting the Names and Descriptions of our many reports into coordinated pairs that fit perfectly on the Tiles and clearly identify reports (and folders) to our users. In SSRS 2016 SP1, the Descriptions are not displayed; this will definitely diminish the usability of our SSRS site when we upgrade. It is very troublesome when an upgrade removes features that an implementation is…

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

    We’ll send you updates on this idea

    1 comment  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
  11. Index Hints : query using dropped index should fail gracefully

    If I create an index, and then use a hint in a query to use that index, the query fails with:

    Msg 308, Level 16, State 1, Line 1
    Index 'abc' on table 'dbo.abc' (specified in the FROM clause) does not exist.

    With tools like DTA, the dropping of indexes is going to become much more common, but common sense of inspecting code for index hints is not necessarily going to get any better, until people get bitten... and even then they will probably react by only fixing the one query.

    [Filed on behalf of Greg Linwood.]

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

    We’ll send you updates on this idea

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

    Upvotes: 11

    <=-=May 20 2008 10:09AM=-=>

    Having SQL Server behave in predictable ways is important. Improvements like this one sort of bring the db engine into a fuzzier area of behaviour. Maybe good in this case, but a slippery slope.

    Consider this situation. A dba sees a query with terrible performance that is holding locks on resources everywhere. The dba fixes the query with a query hint and the index suddenly gets dropped. What’s worse? An error on the query? Or a poorly performing query (with a warning) that is once again locking resources everywhere.

    <=-=May 20 2008 4:32PM=-=>

    I agree with the sentiment of this DCR. Failing queries when an INDEX hint can’t be creates excessive dependence between the physical structure of the data and the application code. But we must find a way to provide a warning to users, e.g. through the SQL error log or a …

  12. Allow trusted services in Azure SQL server firewall

    This feature is currently available on Storage Account, it will permit the following :


    • Grant access to Azure services registered in the same subscription.

    • These trusted services will then use strong authentication to connect to our SQL Server securely.

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

    We’ll send you updates on this idea

    0 comments  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
  13. begin of month function

    We now have the EOMONTH function the give us the end of the month, which is a fantastic addition and it saves us quite some typing in date logic. However, it would be nice to also have a BOMONTH function for the start of the month. Right now we have to do the following:

    DATEADD(DAY,1,EOMONTH(GETDATE(),-1))

    This expression can be replaced by one simple function call: BOMONTH(GETDATE())

    18 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  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
  14. Permit TRUNCATE TABLE when referencing tables are empty

    TRUNCATE TABLE is an efficient command to empty a table, as deallocates all extents and the extend allocation is all that is logged.

    However, its usefulness is reduced by the fact that you cannot use it when there are referencing foreign keys. In the general case, this makes sense, as if the referencing table has data, truncating the table is almost bound to cause a constraint violation. The exception is if all rows in the referencing table has a NULL value in the FK column, but verifying this could be expensive.

    However, you may want to use TRUNCATE TABLE on…

    18 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 →
  15. Improve visual analysis of the Columnstore Indexes by adding an option to highlight the iterators executing with Batch Execution M

    Current performance analysis of the complex execution plans with Columnstore Indexes, requiring to open each of the important iterators and consult its properties in order to find whether its running with Batch Execution Mode.

    Please consider providing a visual option to highlight the iterators that are running with Batch Mode within the execution plan.
    It can be something simple as a default "Lightning Bolt" icon on the iterators within the execution plan, to something more collaborate as a right-mouse click that will highlight the batch iterators.

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

    We’ll send you updates on this idea

    0 comments  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
  16. Performance of shredding the XML data type using nodes() / value().

    Performance of shredding XML to rows using nodes() and value() get linearly worse with the number of columns returned.
    The reason is that each column is handled by a separate branch of its own in the query plan.
    A nested loop join against a table valued function that returns the value.
    Comparing performance of shredding using nodes() with openjson I have seen that when there are three or more columns shredded,
    openjson is the clear winner.

    My suggestion is that you build a way of shredding XML similar to how it is done for shredding JSON.
    An openxml that can…

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

    We’ll send you updates on this idea

    1 comment  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 8

    <=-=Mar 4 2017 9:33AM=-=>

    We understand the problem. XML is designed differently and the goal was to provide rich query language with XPath support, which might require a lot of memory and processing logic. OPENJSON is designed to be more lightweight and just scan JSON text and return values where it finds them. There are pros and cons for both approaches (similar to pros and cons for DOM and SAX parsers).

    The key differentiators between JSON and XML is the fact that JSON is better for scan based processing of JSON columns where you just pick few values from JSON text, and XML is better for rich querying and indexing.

    I will keep this item open and let people vote for this; however, we cannot guarantee that this kind of re-design of XML will be done in near future.

    If you need to use shredded nodes in some…

  17. Expose TLS Protocol Version in sys.dm_exec_connections

    My organisation would like to start switching clients over to TLS 1.2. There does not appear to be a way inside SQL Server to determine which version of TLS the clients/server was negotiated. Though it may be possible to crack open the network packets from each application this would be an arduous task. Being able to easily determine the TLS version would enable administrators to easily identify clients/applications using the older protocol and update them accordingly. Security could then be improved by disabling older versions of the protocol.

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

    We’ll send you updates on this idea

    1 comment  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
  18. AT TIME ZONE way too slow

    AT TIME ZONE shells out to Windows to perform time zone conversions. This process takes way too long when working with more than a few rows (see this blog post for details: http://sqlsoldier.net/wp/sqlserver/timezonesareadragseriously). The time zone info does not change frequently. The info from Windows required to make the conversions should be cached within SQL Server to speed up this processing.

    18 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 →
  19. AlwaysOn Secondary Parallel Redo Workers

    When your secondary replica server calculates the parallel redo worker threads and you have many databases, due to the 100 max parallel redo worker threads in an instance and 16 per database, a lot of databases become single threaded. Some of these databases could be under heavy load with writes, which causes the Redo Queue to get significantly behind and can cause extended downtime if a failover occurs.

    18 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 →
  20. Provide function to retrieve the entire call stack

    We have several log tables where among other things save the name of the current stored procedure. Typically, there is a dedicated stored procedure that writes to a log table, and we pass @@procid to a parameter.

    But there are cases where this is not really enough. Maybe the stored procedure that called the logging procedure itself is a general procedure. Or if we want log something by means of a trigger, we want to know the stored procedure that fired the trigger.

    The purpose for this information, is mainly for tracking down problems in the application, and how some…

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

    We’ll send you updates on this idea

    1 comment  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 146

    <=-=Oct 16 2006 11:24AM=-=>

    Hi Erland,

    Thanks for bringing this suggestion to our attention. This would definitly be a useful feature and we are considering implementing it in the next version of SQL Server.

    Thanks,
    Tomer Verona
    SQL Engine Development

    <=-=Oct 19 2007 2:15PM=-=>

    Any update on this? I happened to see this in the event Blocked Process Report:

    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 since you probably
    only want this code in a few places, this could be dealt with adding extra privs with
    certificate signing.

    <=-=Apr 7 2008 12:18PM=-=>

    This would be of use in the scenario I…

  • Don't see your idea?

SQL Server

Categories

Feedback and Knowledge Base