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

    56 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 →
  2. 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.

    52 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 →
  3. Schema files have blank lines appended after schema compare

    After doing a schema compare from the DB to the project, most schema files have a blank line appended. This causes the files to be checked out of source control and then show as changes when they shouldn't

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

    We’ll send you updates on this idea

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

    Upvotes: 19

    <=-=Mar 13 2015 2:44PM=-=>

    This is happening for me too; it’s been a long time issue in the SSDT. It creates noise when doing compares, merges, and checkins where these don’t pertain to the real changes of objects.

    Microsoft Visual Studio Ultimate 2013
    Version 12.0.31101.00 Update 4
    Microsoft .NET Framework
    Version 4.5.51209

    Installed Version: Ultimate

    Scaffolding: A framework for building and running code generators
    Server Explorer extensions for Microsoft Azure Websites

    NuGet Package Manager 2.8.50926.663
    NuGet Package Manager in Visual Studio. For more information about NuGet, visit http://docs.nuget.org/.
    SQL Server Data Tools 12.0.50226.0
    Microsoft SQL Server Data Tools

    <=-=Apr 1 2015 10:31AM=-=>

    Thank you for submitting this feedback. We are investigating this issue and will update when we have more information.

    Steven Green
    SQL Server Data Tools team

    <=-=Feb 1 2016 11:30AM=-=>

    This is one of those issues that convinces me that nobody at MSFT

  4. Make is possible to retrieve all error messages in a CATCH handler

    When you trap an error in a CATCH handler, you can use the functions errormessage, errornumber etc to get information about the error. This works well, as long as only one error message is produced. However, there are situations where multiple error message. In this case you can only retrieve one error message, and with a bit of bad luck this is only a generic error message. A typical example is BACKUP-RESTORE:
    BEGIN TRY
    BACKUP DATABASE msdb TO DISK = 'X:\nosuchdisk\dxx.bak'
    END TRY
    BEGIN CATCH
    PRINT error_message()
    END CATCH

    The output is "BACKUP DATABASE is terminating abnormally." The…

    47 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 →
  5. 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 →
  6. DIVIDE function in T-SQL

    The languages MDX and DAX both have a DIVIDE function, which has the following syntax:

    DIVIDE(<numerator>, <denominator> [,<alternateresult>])
    The alternate result is specified as the result when a division by zero is encountered. If it isn't specified, NULL is returned.

    This would be a fine addition to the T-SQL language.

    45 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 →
  7. Option to store query store data in a filegroup other than PRIMARY

    Query store data is incredibly useful but depending on activity and settings it can grow to quite a large size. I would like an option to store this data on a filegroup other than PRIMARY. This would allow discretion regarding storage used, reduce the impact to recovery times and give DBAs more flexibility in managing query store data.

    44 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: 57

    <=-=Feb 8 2017 2:13PM=-=>

    We disallow ALL use of the PRIMARY filegroup on the premise that any use of PRIMARY is unauthorized, and PRIMARY is set to a small size with no autogrow. Since the Query Store on very active servers with non-parameterized workloads grows very, very fast, setting it to a filegroup whose data file is on a specific LUN (based on speed and cost, i.e. a specific storage tier) is vital.

    <=-=Apr 2 2017 7:56AM=-=>

    Thank you for taking time to post this issue! We understand that this could be an important issue for you.

    We get a lot of feedback regarding PRIMARY filegroup from the field and many MVPs. �
    This item is high on our priory list, but unfortunately, we do not plan to include a fix for this issue in the upcoming release. Although, we might include it as an improvement in future…

  8. Named Parameters for User Defined Functions without EXEC

    Currently, stored procedures and scalar user-defined functions invoked with the EXEC keyword support named parameters.

    User-defined functions invoked as part of a SELECT or other statement require ordered parameter specification without parameter names.

    Specifying parameter names should be supported when invoked as part of a SELECT statement.

    When any parameters are named, all must be named,

    When any parameters are named, any omitted parameters should receive default values. Omitted parameters without default values should result in an error.

    See also:

    https://connect.microsoft.com/SQLServer/Feedback/Details/361449

    43 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: 25

    <=-=Feb 7 2017 7:34PM=-=>

    Yes! I myself a use heavily inline table value functions and that would improve the clarity of their use even more!

    <=-=Mar 5 2017 2:48PM=-=>

    We understand the requirement, and it is good that it gets a lot of votes in very short period. It is in out backlog and we will consider it for some of the next releases; however, currently we cannot confirm when it will be implemented..

    <=-=Dec 22 2017 3:17PM=-=>

    This would be quite incredibly useful. When it was added to C#, it was a “so-what?” thing for me at the time – but now, I don’t know how I ever coded without named parameters. It has saved me countless hours getting things right the first time, and not chasing around dumb problems. I am sure the same would be true if SQL Server supported named parameters in User-Defined Functions.…

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

    42 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 →
  10. SQL2012 Could not locate Statistics on secondary replica

    When querying a table thru a linked server, got the following error message.

    Msg 2767, Level 16, State 1, Procedure sptablestatistics2rowset, Line 105
    Could not locate statistics '
    WASys00000007_47DBAE45' in the system catalogs.

    The database was a secondary replica, primary replica was fine. Ran dbcc show statistics on secondary replica, that failed to find statistics distribution. There was an entry in sys.stats on both. Drop statistics on primary, to resolve.

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

    We’ll send you updates on this idea

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

    Upvotes: 48

    <=-=Jul 9 2014 11:04PM=-=>

    This is happening to us, at least 3 times a week, on different tables in the database. The drop statistics on primary works, but the processes that are meant to run on the secondary via, linked servers are failing! A real pain!

    <=-=Apr 2 2015 1:38AM=-=>

    >A real pain!
    +1

    <=-=May 4 2015 3:39AM=-=>

    Microsoft folks – Can we please have some kind of explanation on why this happens for dynamic stats and on the asynchronous node only? It has been a real pain for us to manually drop the statistic on the primary node to clean up the corrupt statistic on the asynchronous secondary node.

    <=-=May 19 2015 11:48PM=-=>

    Clearing the userstore_dbmetadata related pools on ASYNC commit resolves the issue.
    DBCC FREESYSTEMCACHE (‘dbname’)

    Or create a stored procedure to check if the instance is an ASYNC commit, then clear the cache using an…

  11. Add sp_estimate_data_compression_savings to SQL Azure Database

    Now Data Compression is available in Azure SQL Database (https://msdn.microsoft.com/en-us/library/cc280449.aspx), but the spestimatedatacompressionsavings is not available yet. The error message is:

    Msg 534, Level 16, State 1, Procedure spestimatedatacompressionsavings, Line 20 [Batch Start Line 14]
    'spestimatedatacompressionsavings' failed because it is not supported in the edition of this SQL Server instance 'MSSQLSERVER'. See books online for more details on feature support in different SQL Server editions.

    The workaround is to create another copy via database backup, compress and compare the table and index. But it takes more time, resource and money to get the result.

    42 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. Filtered index not used when IS NULL and key lookup with no output

    Filtered index not used when IS NULL is used. IS NOT NULL works fine, but IS NULL does a key lookup. Same as this issue but on different version of SQL Server
    https://connect.microsoft.com/SQLServer/feedback/details/454744/filtered-index-not-used-and-key-lookup-with-no-output

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

    We’ll send you updates on this idea

    3 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  13. Add Application-period temporal tables and Bitemporal tables to SQL Server 2016

    SQL Server 2016 has temporal tables however these are only for system period temporal tables.

    Please add Application-period temporal tables and Bitemporal tables similar to the existing feature in DB2.

    41 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: 29

    <=-=Jul 27 2016 5:17PM=-=>

    This feature vastly simplifies development of certain types of applications. Would be interesting at least know if microsoft has some intention to implement it.

    <=-=Jul 27 2016 5:19PM=-=>

    A fully compliant implementation to ansi sql 2011 would nice too.

    <=-=Jul 28 2016 1:01AM=-=>

    I would also like to see bitemporal support added, it would be most useful!

    <=-=Mar 7 2017 11:54AM=-=>

    This feature would really help people trying to migrate their DBs from DB2 or Oracle to SQL Server that much easier.

  14. SSMS and Grouping by Schema

    Let SSMS group tables & other objects by Schema! This makes large databases a lot easier to maintain.

    If I have a database with a lot of objects, say 300, organized in a lot of schemas (say 20), it is not easy to keep the overview. In Visual Studio there is a solution: You can group the objects by schema. There is even an outdated SSMS extension: https://ssmsschemafolders.codeplex.com

    41 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 →
  15. 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.dmexecquerystats: lastspills, totalspills, maxspills, 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…

    40 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 →
  16. 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…

    37 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 →
  17. Add SQL Code Formatter

    Please add a SQL code formatter to the query editor. It should work like the C# code formatter in Visual Studio except for SQL.

    37 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 →
  18. Indicate "morally equivalent" forced plan where use_plan = true but is_forced = 0

    Currently, if you force a plan in query store (or if it is forced by automatic plan correction), the exact plan which is forced has isforced = 1 in sys.querystoreplan. In the plan properties, there is no useplan indication.

    After this plan is forced, often a "morally equivalent" plan will be compiled, which has a different queryplanid and different costs, but the same plan shape.

    This "morally equivalent plan" has isforced = 0 in sys.querystoreplan. In the execution plan properties, useplan = true.

    These "morally equivalent plans" are currently difficult…

    36 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 →
  19. Scalar MAX function

    It would be nice to have a scalar MAX function (name it S_MAX) that returns a maximum of two numbers. Right now you have to use CASE or write your own TVF and CROSS APPLY it, both are quite clumsy. Same for minimum of two:

    SELECT
    SMAX(1, 0) --yields 1
    ,S
    MIN(1, 0) --yields 0

    35 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 →
  20. Use gMSA accounts for everything

    Currently gMSA accounts can be used as Windows Service accounts for SQL Server. gMSA you never have to provide the password just the account name for approved devices. It would be nice if you could also use gMSA accounts for SQL Server Credentials for SSIS Jobs. For SSRS Data Connectors to a database. Essentially it would make things more secure than using a traditional "Service Account" that we put into a data source or credential and never update the password. The password is saved in some document somewhere. Same accounts being used for multiple purposes. By using a gMSA account…

    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  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
  • Don't see your idea?

SQL Server

Categories

Feedback and Knowledge Base