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 Central Management Server support to Azure SQL Database

    Registering a new Central Management Server currently requires msdb and as such, we cannot register a Central Management Server in an Azure SQL Database. It would be great to have this feature to help support managing on-premise database instances as well as Azure SQL Servers.

    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 →
  2. Query Store SELECT Performance

    Running a query against a large Query Store repository (10 GB) can be really slow if filtering on runtime stats intervals.

    It looks like an index is missing on sys.query_store_runtime_stats (runtime_stats_interval_id).

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

    We’ll send you updates on this idea

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

    Upvotes: 3

    <=-=May 27 2017 6:38AM=-=>

    Thank you for taking the time to post this issue! We understand that this could be an important performance improvement.
    Performance tuning is of such scenarios is not a trivial task. Change of such feature as Query Store can speed up one scenario and lead to degradation in others. We�ll look more into this particular use case and see what we can do.

  3. sql 2017 "cube processing task" ssis adomd client error

    A SQL Server 2017 Cumulative Update (somewhere between RTM and CU5) has broken 'Cube Process Tasks' in Integration Services (SSIS). Packages that run fine on SQL 2017 RTM (containing a Cube process Task) FAIL on CU5 and also CU6 with an error related to ADOMDClientUI. The specific error reported in the SSIS log is "Process Cube:Error: Could not load file or assembly 'Microsoft.AnalysisServices.AdomdClientUI, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91 or one of its components. The system cannot find the file specified."

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

    We’ll send you updates on this idea

    under review  ·  16 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  4. Reporting Services 2017 installation - domain controller.

    Please give back the ability to install Reporting Services 2017 on a domain controller.
    Every installation of SSRS from 2008 to 2016 works without any problem.
    Reporting Services as part of Sql Server are crucial for many SMBs that don't have the infrastructure to install separate machines for domain controller and sql server.
    I understand recommending, but not prohibiting.
    Kind regards

    19 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  ·  Setup + Deployment  ·  Flag idea as inappropriate…  ·  Admin →
  5. 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 →
  6. sys.dm_os_sys_info/ms_ticks in Azure SQL DB

    It is often useful, when doing real-time activity monitoring, to be able to understand when workers are spending a long time sitting on the runnable queue after entering a wait state.

    This information is available in the on-prem product by using the sys.dm_os_workers DMV, which gives numbers in terms of "ms_ticks." Converting these ticks to actual clock time requires pulling an "ms_ticks" value from the sys.dm_os_sys_info DMV.

    Unfortunately, Microsoft pulled the latter DMV from Azure SQL DB, making this information completely inaccessible. And it would be quite useful there, especially when DTU throttling is taking place.

    My request is to…

    18 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 →
  7. Support "natural sorting" / DIGITSASNUMBERS as a Collation option

    Quite often** people want to sort a string containing either only numbers, or a mix of letters and numbers in a pattern, such that the numeric portion of that string is sorted as an actual number. For example, "Class40g" would come before "Class300c", not after. This behavior has already been integrated into File Explorer, starting in Windows 7.

    Currently when people need to do this type of sorting, they need to create silly and inefficient methods of parsing the string into separate alpha and numeric chunks.

    ** often = numerous questions regarding how to accomplish this type of sorting on…

    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: 11

    <=-=Jul 15 2016 6:51AM=-=>

    I offer as evidence for the desire for this to happen, the 1k+ views of my stackoverflow question from 2011. A little under a person a day.

    http://dba.stackexchange.com/questions/3828/is-there-a-collation-to-sort-the-following-strings-in-the-following-order-1-2-3?noredirect=1#comment273118_3828

    <=-=Jul 15 2016 8:40AM=-=>

    Adding to the link that Justin posted to his Stack Overflow question:

    http://dba.stackexchange.com/questions/117379/sort-a-varchar-type-column
    http://dba.stackexchange.com/questions/126744/order-by-and-comparison-of-mixed-strings-of-letters-and-numbers

    http://stackoverflow.com/questions/34509/natural-human-alpha-numeric-sort-in-microsoft-sql-2005
    http://stackoverflow.com/questions/3158917/natural-sort-for-sql-server
    http://stackoverflow.com/questions/22461619/complex-sort-of-field-string-number-string
    http://stackoverflow.com/questions/24989365/sorting-a-string-numerically-in-sql-server
    http://stackoverflow.com/questions/26552435/sorting-mixed-numbers-and-letters
    http://stackoverflow.com/questions/26932310/sql-server-string-convert-numeric-and-sorting-issue
    http://stackoverflow.com/questions/28278467/sort-dropdown-list-in-alpha-numeric-order
    http://stackoverflow.com/questions/28372359/natural-sort-for-sql-server
    http://stackoverflow.com/questions/29676432/alphanumeric-sort
    http://stackoverflow.com/questions/30502118/sort-float-numbers-as-a-natural-numbers-in-sql-server
    http://stackoverflow.com/questions/35001818/alphanumeric-and-numeric-sorting-in-sql-server
    http://stackoverflow.com/questions/37728476/sql-sorting-numeric-and-string

    http://www.codeproject.com/Articles/51602/How-to-Sort-Alphanumeric-Data-in-SQL

    <=-=Mar 3 2017 1:02PM=-=>

    We understand the scenario and if it gets more votes we will consider it for the next releases. It is assigned to our collation & string processing team.

  8. Add a "delete" option for JSON_MODIFY() to remove deleted keys instead of setting them to NULL

    Originally based on this Connect item: https://connect.microsoft.com/SQLServer/feedback/details/3120404

    The JSON_MODIFY delete functionality via NULL works inconsistently. On properties, NULL removes the item from the JSON completely. On arrays, NULL nullifies the value but does not remove it from the array.

    Example:
    -- Our JSON data with three properties:
    DECLARE @data nvarchar(100) = JSON_QUERY('{ "Property1" : "A", "Property2" : "B", "Property3" : "C" }')
    -- Output: { "Property1" : "A", "Property2" : "B", "Property3" : "C" }

    -- Deleting the second property with NULL completely removes it from our JSON:
    SELECT JSON_MODIFY(@data, '$.Property2', NULL)
    -- Output: { "Property1" : "A", "Property3" :…

    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 →

    Upvotes: 10

    <=-=Apr 10 2017 4:40AM=-=>

    Thanks for the feedback. We agree with your proposal and this request is in our backlog. We will let people to vote for this. Also feel free to report any other option that you find useful. Note that we cannot commit when this request ill be implemented, because it depends on the number of votes and schedule.

  9. New SQL version of SQL Server

    Would love to see sql server go the direction of nuodb or memsql and implement a fully distributed version of MS SQL SERVER.

    17 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  ·  Other  ·  Flag idea as inappropriate…  ·  Admin →
  10. Provide a way to update SSDT system database reference DACPACs

    Please provide a method for updating the master.dacpac and model.dacpac reference databases that install with SSDT.

    The versions that install with SSDT are static and do not reflect any changes/enhancements/bug-fixes that are made within SQL Server.

    For example, the SQL Server 2017 master.dacpac is missing the new system DMV's [sys].[dm_os_host_info] and [sys].[dm_db_log_stats], and the recently released SQL Server 2016 SP2 adds a new DMV [sys].[dm_db_log_info] and new columns to the DMV [sys].[dm_os_sys_info] which will be missing from the SQL Server 2016 master.dacpac.

    Any referenes to new DMV's/columns generate 71502 (unresolved reference) errors. The workaround is to ignore these errors, but…

    17 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 →
  11. Fix Export/Import of Registered Servers WITH Passwords

    I just did an Export of Registered Servers from one machine, running SSMS 17.5, followed by an IMPORT of that file in a machine running SSMS 17.6. Upon exporting, I left the check box blank for "Do not include user names and passwords in the export file". Yes, I am aware that it is a security risk, but I don't want to have to re-enter all my passwords again.
    Upon import, I got about a dozen errors stating "Key not valid for use in specified state." (I'm in Connecticut. Does it only work in Washington state?)
    Then it says, "Import…

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

    We’ll send you updates on this idea

    under review  ·  10 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  12. Please add the Scroll bar to the Parameters section in SSRS Report Manager like in SSDT

    When viewing a SSRS report on a device with a smaller screen, such as an iPad or iPhone, if there are several parameters, it takes up a lot of space, and with SSRS 2016, it does not scroll out of view - only the report section scrolls.
    Could you please provide scroll bar for parameters section in Report Manager like in SSDT.

    17 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 →
  13. SSAS Supported Compatibility level is actually showing default Compatibility in SSMS 17.3

    In SSMS 17.3 with Analysis Services Client tools 14.0.1008.227 when you click on server properties for an SSAS 2017 instance it shows the "Suppported Compatibility level" as 1200 when it should show 1100,1103,1200,1400.

    If you run a DISCOVER_XML_METADATA command against the SSAS 2017 instance you can see the correct data returned for the default and supported compatibility modes, it appears that the SSMS UI has just mapped this incorrectly.

    <ddl400:DefaultCompatibilityLevel>1200</ddl400:DefaultCompatibilityLevel>
    <ddl600:SupportedCompatibilityLevels>1100,1103,1200,1400</ddl600:SupportedCompatibilityLevels>

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

    We’ll send you updates on this idea

    6 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  14. Major Columnstore Performance Enhancement: ALTER INDEX REBUILD WITH (ORDER BY (ColumnA, ColumnB, ...))

    Greetings SQL Server Columnstore Team,

    SQL Server's columnstore performance is highly dependent upon rowsegment elimination. We have been working with mssql16 and competing product memsql to prototype a financial timeseries tick database. We were able to get mssql16 to outperform memsql, however, it took substantial data re-load engineering on our part that we think would be better made available in the ALTER INDEX REBUILD command.

    // sample schema:
    CREATE TABLE trades(
    SecurityID int,
    DateTimeKey datetime2(7),
    Price float,
    Size float,
    Source char(4),
    ConditionCode char(4)
    );

    In mssql16, we partition this table on the datetime2 field (quarterly partitions). Nearly all of our…

    17 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 →
  15. T-SQL for changing table column order

    Once a table is created, the ordinal position of the columns is fixed. This is especially problematic for large data warehouse tables, where new columns are expected to be added over time.

    The only solution currently to re-order columns is to drop and recreate the table (or let the SSMS designer do this for you), but this is often not feasible for a large, highly used table, and it cannot be easily scripted.

    Please add some ALTER syntax that will allow changing column order.

    16 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  ·  Other  ·  Flag idea as inappropriate…  ·  Admin →
  16. xp_delete_file on SQL Server 2017/Linux

    The xp_delete_file system stored procedure for deleting bakup file does not work on Linux client OS.
    Wee tested on Microsoft SQL Server 2017 (RTM-CU1) (KB4038634) - 14.0.3006.16 (X64) Enterprise Edition (64-bit) on Linux (Ubuntu 16.04.3 LTS)

    Script we used :
    DECLARE @BACKUP_DIR NVARCHAR(256);
    EXEC master.dbo.xp_instance_regread
    @rootkey = 'HKEY_LOCAL_MACHINE',
    @key = 'Software\Microsoft\MSSQLServer\MSSQLServer',
    @value_name = 'BackupDirectory',
    @BackupDirectory = @BACKUP_DIR OUTPUT;
    SELECT @BACKUP_DIR
    EXEC master.sys.xp_delete_file 0, @BACKUP_DIR, N'bak', N'21111111';
    GO

    Error message :
    Msg 22049, Niveau 15, État 0, Ligne 67
    Error executing extended stored procedure: Invalid Parameter

    16 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. Remove or make optional the $ShardName column for sp_execute_remote

    This will enhance portability between SQL Server and Azure SQL Database. It will also make the output look like sp_executesql. Otherwise, when doing cross-database queries, we cannot use the following logic:

    IF @@VERSION LIKE '%Azure%'
    BEGIN
    INSERT INTO myTable
    EXEC sp_execute_remote @stmt
    END
    ELSE
    BEGIN
    INSERT INTO myTable
    EXEC sp_executesql @stmt
    END

    Instead the output of sp_execute_remote has to be copied into a temporary table variable, which impacts performance, just to strip away the $ShardName field.

    16 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 →
  18. SSDT 17.2 for VS 2015 - SSIS 2016 packages can't open Analysis Services Processing tasks

    I upgraded an SSIS project from 2014 to 2016 using SSDT 14.0.61707.300 in Visual Studio 2015. Upon doing so, whenever I open a Analysis Services Processing Task, I get the following error:

    TITLE: Analysis Services Processing Task
    ------------------------------
    Method not found: 'Microsoft.AnalysisServices.ErrorConfiguration Microsoft.AnalysisServices.Commands.BatchCommand.get_ErrorConfiguration()'.
    ------------------------------

    If I revert back down to 2014, I can open it just fine. If I create a new AS Processing Task in the same package or new package in the same solution, I get the same error. If I upgrade the SSIS project to SQL2017, it magically works. The issue is only when the target version…

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

    Upvotes: 13

    <=-=Dec 4 2017 3:53AM=-=>

    Hi Gabe,
    I’m having a similar problem, but not only with upgraded packages.
    I’m having problems also if I create a completely new package
    with Integration Services 2016 in Visual Studio 2015 (SDDT version installed is 17.2).
    It works if I use Integration Services 2014 or 2017.

    I’ve not yet found solution to that issue.

    Please let me know if you identify a solution to your issue

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

    15 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 →
  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…

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

SQL Server

Categories

Feedback and Knowledge Base