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. 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 is_forced = 1 in sys.query_store_plan. In the plan properties, there is no use_plan indication.

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

    This "morally equivalent plan" has is_forced = 0 in sys.query_store_plan. In the execution plan properties, use_plan = true.

    These "morally equivalent plans" are currently difficult to identify in the Query Store reports and in the…

    30 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 →
  2. [Graph Database] Implementation of Transitive Closure and Transitive Reduction

    It'd be great to have the transitive closure and reduction implemented in SQL Server Graph Database. A lot of scenarios will benefit from them.

    Here a description of the two problems:
    https://goo.gl/j9U5nb

    30 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 →
  3. Identical symmetric keys do not work between SQL Server 2017 and other SQL Server version

    hi everybody!

    I believe, there is a general product issue in SQL Server 2017. The problem concerns encrypting & decrypting data using the same symmetric key on different servers.
    I have an issue in SQL Server 2017 CU3 (version 14.0.3015.40). I need to create identical symmetric keys on two servers of different versions, as it is described in MS article:

    https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/create-identical-symmetric-keys-on-two-servers

    These steps work well within and between different versions of SQL Servers (2012, 2014, 2016), but not between SQL Server 2017 and any other server.
    I can create identical symmetric keys on SQL Server 2012, 2014 and 2016. So…

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

    We’ll send you updates on this idea

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

    28 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 →
  5. Add setting so LEN counts trailing whitespace

    Maybe there is a historic reason why LEN('a') and LEN('a ') return the same number.

    But that's stupid.

    The internet is awash with people like me who were stunned to discover this counter intuitive quirk (Google it).

    None of the solutions to this problem are particularly elegant or efficient:
    https://stackoverflow.com/questions/2025585/len-function-not-including-trailing-spaces-in-sql-server/2025587

    I'm assuming since this has been baked in for ages, we can't change default behavior.

    So how about:
    - Add a parameter to the LEN function to count white space
    - Add an SQL Option that more generally keeps SQL Server's hands off my white space - everywhere

    27 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 →
  6. Enable SSRS parameter visibility to be set dynamically by expression

    SSRS columns can be set to display dynamically based on an expression, but SSRS parameters can only be set to Visible, Hidden, or Internal. My suggestion is to add the ability to control SSRS parameter visibility by expression as well.

    27 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 →
  7. Ability to run specific SQL Agent job steps

    Occasionally a user may need to run only specific steps of an agent job, or need the job stop after a certain step.

    While SQL Server Agent currently provides the ability to "Start job at step", this is all the flexibility the user has at runtime. Anything beyond that requires the job to be edited.

    It would be useful for those with permission only to run (not edit) jobs to have greater flexibility at runtime. E.g. a screen which allows the user to choose which steps to run or skip for that particular execution.

    26 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 →
  8. Multi-threaded rebuilds of Clustered Columnstore Indexes break the sequence of pre-sorted segment ordering (Order Clustering)

    In order to get a better performance as well as compression results, we can sort the data in a RowStore engine before creating Columnstore Index to get a better Segment Elimination.

    What happens is that if we build a Clustered Columnstore directly after sorted RowStore with (DROP_EXISTING=ON) to get a perfect Order Clustering, we have the order maintained just partially.

    To test it, download ContosoRetailDW (http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=18279),
    and run the following script:

    ALTER TABLE dbo.[FactOnlineSales] DROP CONSTRAINT [FK_FactOnlineSales_DimCurrency];
    ALTER TABLE dbo.[FactOnlineSales] DROP CONSTRAINT [FK_FactOnlineSales_DimCustomer];
    ALTER TABLE dbo.[FactOnlineSales] DROP CONSTRAINT [FK_FactOnlineSales_DimDate];
    ALTER TABLE dbo.[FactOnlineSales] DROP CONSTRAINT [FK_FactOnlineSales_DimProduct];
    ALTER TABLE dbo.[FactOnlineSales]…

    26 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: 47

    <=-=Jul 3 2014 10:03AM=-=>

    This would definitely be a good build. I walked through an initial investigation of clustered columnstore on one of our data sets, and the number of reads was reduced by about 80% for some queries in the workload once I started paying careful attention to segments and loading data in a single-threaded manner to optimize segment elimination. However, this results in slower loading of data, and it would be great to be able to create columnstore indexes in order for segment elimination.

    Having an optional ORDER BY clause for the columnstore initial creation, removing the need to first create a clustered index in order to control order and allowing parallelism without breaking order, would be particularly powerful.

    <=-=Jul 3 2014 10:30AM=-=>

    Neugebauer: thanks. you identified the issue correctly. This is something we are actively looking. One question
    (1) once the index is build, the…

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

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

  10. CDW from 2016 to 2017 ends with "Property HasMemoryOptimizedObjects is not available for Database"

    Hi
    I'm trying to copy a couple of databases from a SQL2016 to SQL2017 using the Copy Database Wizard.
    I'm using SQL Management Object Method in SSMS 17.3
    SSIS Proxy account is sysadmin on both instances and local admin i Windows on both servers.
    When running the job I get the error "Property HasMemoryOptimizedObjects is not available for Database '[DATABASENAME]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights.
    The databases has no Memory Optimization configured.

    If I try to use detach/attache methods I can't select any of the databases witch…

    25 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 →
  11. Change the way Script Task/Components are handled in SSIS

    When having to work with script task or components inside of SSIS it becomes quiet tedious because the loading times are exessive and the functionalities are rather limited.
    I would like to propose a new form of Script Task, a Script Task v2 if you will. Instead of just exposing a subset of VS making this Script Task a proper C#/VB.net proj that compiles down to a dll. That has a Main Class that implements an interface that contains a execute-method.
    These Projects would be added to the SSIS Solution as seperate Projects offering everything from Nuget to properly allowing…

    24 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 →
  12. SSIS package fails to start - application lock timeout in SSISDB.catalog.create_execution

    Details are described here:
    https://connect.microsoft.com/SQLServer/feedback/details/783291/ssis-package-fails-to-start-application-lock-timeout-in-ssisdb-catalog-create-execution

    In short: when a scheduled job tries to execute an SSIS package , there's a time-out in the communication between the job and the SSISDB database. The package is never executed. The original bug reports was closed as duplicate (no duplicate to be found though).

    I encountered this bug in SQL Server 2017, patched to the latest CU.

    Error message: Failed to execute IS server package because of error 0x80131904. Description: The operation failed because the execution timed out. Source: .Net SqlClient Data

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

    We’ll send you updates on this idea

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

    24 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 →
  14. Traceflag 460 causing truncation errors on code paths that are not followed

    This is an odd one. I have tested this on both SQL Server 2017 CU12 and CU13 (in a container and on my production boxes).

    If you have an IF/ELSE condition, and you are inserting into a table variable in the ELSE, and that INSERT would cause a truncation issue, it will throw an error, EVEN IF THE CODE IS NOT EXECUTED.

    I have attached a reprop script that creates a database called "Repro" and runs the code. When TF460 is enabled, the ELSE block with throw an error when inserting into a table variable, even though it is impossible…

    23 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  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  15. Fix the Problem of Converting Hijri Dates to Gregorian Dates

    Currently Microsoft tsql function called Convert() is not able to convert the Hijri date 'yyyy-02-30' to its corresponding Gregorian Date, here are the details of this issue

    https://social.msdn.microsoft.com/Forums/en-US/b86b0cf6-280d-436e-ba92-ff168794c420/error-in-converting-hijri-date-to-gregorian-date?forum=sqlnetfx&prof=required

    23 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. SSMS screen is not correctly redrawn when switching tabs

    Sometimes when switching between tabs in SSMS the screen is not correctly refreshed (redrawn).
    The result is a corrupt screen partly showing the previous tab contents and partly showing the newly active tab contents.
    In particular this happens when using "Database Diagrams" tabs.

    This has been a permanent bug through a number of the latest SSMS releases including this release:
    Release 16.5.1
    File version: 13.0.16100.1

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

    We’ll send you updates on this idea

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

    Upvotes: 6

    <=-=Dec 12 2016 1:42AM=-=>

    This has been reported more times than you have used SSMS. MS are too incompetent and lazy to fix it.

    <=-=Dec 12 2016 1:46AM=-=>

    Here is the link to another which also has a link to another report

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

    please click on the I CAN TOO in the repros section

    <=-=Dec 21 2016 7:39AM=-=>

    I can replicate this on multiple different computers, each with freshly installed Windows (either 8.1 or 10) and latest of all drivers. It happens both with Intel integrated graphics (for me is HD 4600) and NVidia cards. It’s clearly a bug in how you’re using the Visual Studio shell.

    This is absolutely HORRIBLE!! When closing a tab, then attempting to execute the query in a tab that was already open and is now focused (because you just closed one), you see the wrong text in the tab, and…

  17. SQL Server 2017 Installer should recognize Java JDK 9

    SQL Server 2017 Installer should recognize Java 9

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

    We’ll send you updates on this idea

    5 comments  ·  Setup + Deployment  ·  Flag idea as inappropriate…  ·  Admin →
  18. SQL 2017: "SET FMTONLY ON" doesn't return any resultset on UDF w/ COMPATIBILITY_LEVEL=140

    Our application uses at various places the SchemaOnly attribute (see https://msdn.microsoft.com/en-us/library/system.data.commandbehavior(v=vs.110).aspx). Lately, we noticed some strange errors when the application runs against a DB on SQL Server 2017. I boiled the issue down to the attached repro script in T-SQL, that shows clearly that SQL Server doesn' return any metadata, if a table-valued function is called using SET FMTONLY ON with COMPATIBILITY_LEVEL=140. But it works as expected using COMPATIBILITY_LEVEL=130. As I didn't find any word on this breaking change in the docs, I consider this a bug.

    22 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  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  19. SSDT 17.x - Unable to preview SSIS OLE DB Data Source when using Oracle Connector

    I have an SSIS packages that queries an Oracle data source. When trying to preview the query in the data source configuration window, it errors out.

    Technical Details:
    Visual Studio 2015, Update 3, Version 14.0.25431.01
    SSDT 17.4 (Build 14.0.61712.050)
    Oracle Client 12.1.0
    OLE DB Connector: Oracle Provider for OLE DB
    Error Details:
    ===================================

    There was an error displaying the preview. (Microsoft Visual Studio)

    ===================================

    The system cannot find message text for message number 0x80040e51 in the message file for OraOLEDB. (OraOLEDB)

    ------------------------------
    Program Location:

    at Microsoft.DataTransformationServices.Design.UnsafeNativeMethods.ICommandWithParameters.GetParameterInfo(IntPtr& pcParams, IntPtr& prgParamInfo, IntPtr& ppNamesBuffer)
    at Microsoft.DataTransformationServices.Design.DesignUtils.GetQueryParameters(ConnectionManager connectionManager, String sqlStatement)
    at Microsoft.DataTransformationServices.Design.PipelineUtils.ShowDataPreview(String sqlStatement, QueryParameter[]…

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

    We’ll send you updates on this idea

    under review  ·  6 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  20. Installation option for Lock Pages in Memory

    I would like to have an option to set Lock Pages in Memory in the SQL Server command line installation.

    Something like this:

    /SQLSVCLOCKPAGESINMEMORY

    https://docs.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-from-the-command-prompt

    22 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