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. Add a built-in table of numbers

    There are several SQL problems that can be solved with a table of numbers or dates.
    This is a simple one-column table with numbers from 1 and up. Typical common problems
    solved by with such a function:

    o Show me sales for all months, including months for which there were no sales.
    o Iterate over all characters in a string in a set-based statements.
    o Any other query which you need to drive with a consecutive seres.

    It's easy to construct and fill such a table. However, there are a couple of problems
    with it:
    1) If you query needs…

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

    We’ll send you updates on this idea

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

    Upvotes: 278

    <=-=Feb 1 2008 7:18PM=-=>

    Thanks for the valuable suggestion.

    This seems more like adding the sequence support which we’re seriously considering for the next major release.

    Meanwhile, would using identity column help?

    <=-=Feb 2 2008 2:11AM=-=>

    It does not seem that you understood the request. This definitely has nothing to do with
    IDENTITY. I am less versed about sequences, but I don’t think they will cut it either. If you think
    it does, maybe you could provide an example? Take this problem: For the Orders table in
    Northwind, write a query that lists the number of orders for all days in 1997. The result set should
    include all 365 days, and list zero for days without a number.

    This is a typical problem where you need a table of of numbers (or dates). While it’s easy to
    create such a table, I argue in this request that…

  2. Change limit/behaviour of msdb.dbo.suspect_pages table

    Currently the msdb.dbo.suspect_pages table has a 1000 row limit, once the table gets to 1000 rows it stops collecting new errors. This means that on servers where the suspect_pages tables has filled up you are unable to see if new errors are still being discovered.

    My suggestion would be to remove, or at least greatly increase the hard limit of 1000 rows in the suspect_pages table.

    Books online Documentation - Manage the suspect_pages Table
    https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/manage-the-suspect-pages-table-sql-server?view=sql-server-ver15

    "The suspect_pages table contains one row per page that failed with an 824 error, up to a limit of 1,000 rows."

    "Database administrators are responsible…

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

    We’ll send you updates on this idea

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

    153 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  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
  4. In-Memory OLTP - allow removal of Filegroup for memory_optimized_data

    It should be possible to remove the memoryoptimizeddata 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.

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

    We’ll send you updates on this idea

    21 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

  5. Option to specify a location for the DBCC CHECKDB snapshot

    When you run DBCC CHECKDB, by default the snapshot is stored on the same drive as the database data file(s).

    It would be nice to be able to specify the location, if you have


    • Faster drives elsewhere

    • No space on the same hard drive

    • Less I/O traffic elsewhere

    144 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 →
  6. Provide XEvents target directly into SQL Server table

    Provide a way to directly write XEvent output directly into a SQL Server database table in a same or different instance and same or different database.

    The goal being to be able to not have any events missed but have the entire stream captured to the table and also provide header information for when the capture table was created for easier management and deletion. Ideally there should be a purge task that could be activated to clean out such captures after X days.

    XEvents is a great and powerful tool.
    Customers should have an easier way to get it directly…

    137 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 →
  7. 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,…

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

    We’ll send you updates on this idea

    24 comments  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
  8. Add support for standard nested window functions in T-SQL

    The ISO/IEC SQL:2016 standard defines a concept called nested window functions that allows you to nest two kinds of window functions as an argument of a window aggregate function. The idea is to allow you to interact with row markers representing strategic points in windowing elements like the beginning or end of the partition, beginning or end of the frame, the current outer row, and the current inner frame row.
    The two standard nested window functions are the nested row number function and the nested value_of expression at row function.
    The former allows you to refer in an argument of…

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

    We’ll send you updates on this idea

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

    116 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 →
  10. Optimise for Analytics configuration option

    Would love to have a new Server-Wide as well as the Database Scoped Configuration configuration that would be called "Optimise for Analytics" and that would server the purpose of tuning the Storage Engine & Query Processor specifically for the Analytical Workloads (Business Intelligence, Data Warehousing, Data Preparation for ML, etc).

    The actual functionalities to be controlled by this option would be such as: -E configuration, Query Optimiser huge preference for the Hash Joins, Bigger preference for the Batch Execution Mode for the Rowstore tables, Bigger preferences for the Scans and Prefetching for the Storage Engine, Bigger Columnstore Object Pool allocation…

    112 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 →
  11. Porting SMO Library to .NET Core

    SQL Server has been ported to Linux for over a year now and it is time the complete SMO library (or what can be) is ported over to cross-platform as well.

    Only 9 or so core files have been ported over to .NET Core and this allowed the sqlserver module to port over a small set of cmdlets to PS Core. This does not work for other modules like dbatools that use a wider berth of the library.

    There are plenty of other modules in PowerShell (and applications too) that would like to provide cross-platform support for managing SQL Server…

    111 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 →
  12. 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 …

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

    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,…

  13. 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.

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

    We’ll send you updates on this idea

    5 comments  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
  14. Search box in SSMS Object Explorer

    Please add a search box in SSMS Object Explorer similar to what Visual Studio has in the Solution Explorer! It should include autocomplete and the robust filtering abilities (by database, by object type, by schema)

    It is extremely slow to constantly have to navigate through the tree to find the objects you want. I just want to type where I want to go.

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

    Object Explorer doesn’t populate the entire tree in memory, unlike visual studio’s solution explorer. It wouldn’t know if database “MyDatabase” existed until you expanded the Databases node.
    There are third party plugins that provide this type of functionality, I believe, by downloading a bunch of data in the background and exposing a search.

  15. 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

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

    We’ll send you updates on this idea

    5 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 =…

  16. Cardinality Estimator - Specify Execution hints at object definition and/or execution level

    Today we can set execution context at different levels. In particular decide which Cardinal Estimator (CE) version to use to execute our workload.
    At Instance , session , database and/or query levels by using trace flags, database scoped configuration and/or query hints.
    There are cases where some workload runs better using the legacy CE and others with the current (or most recent) CE

    I believe it will be extremely useful to be able to specify execution content at object level (procedures and functions) so if the object is defined to use a particular CE, the execution plan will be generated…

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

    We’ll send you updates on this idea

    18 comments  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
  17. 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, FIRSTVALUE, LASTVALUE) 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…

    90 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.

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

    85 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 →
  19. Allow SQL databases to exist in a resource group different than the SQL Server

    The idea here is that I would like to have multiple databases in different resource groups, but one server to hold them all. The server would be in its own resource group.

    This is possible with App Service and App Service Plan.

    83 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 →
  20. 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…

    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 →
  • Don't see your idea?

SQL Server

Categories

Feedback and Knowledge Base