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. 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 →
  2. 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 →
  3. 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.dmosworkers DMV, which gives numbers in terms of "msticks." Converting these ticks to actual clock time requires pulling an "msticks" value from the sys.dmossys_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…

    24 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 →
  4. provide lpad/rpad funtion

    In 15 years of sql server development, was the lack of native lpad/rpad string funtions, one of the main pain. Please provide native left padding (lpad) and right padding (rpad) funtions.
    Every main DBMS supports this function, except MS SQL Server.
    I think this an easy subject that has to be changed.

    The follwolling DBMS supports rpad/lpad:
    - Oracle
    - Postgresql ()
    - MySQL
    - DB2
    - Informix

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

    Upvotes: 1

    <=-=May 29 2017 1:07PM=-=>

    Thanks you for the idea. We will keep it into the backlog but we cannot guarantee when it would be implemented. We have already added several new string functions STRING_AGG, TRIM, TRANSLATE, and it would be good to add more. We are prioritizing functions that might be added so we cannot confirm when we will add this one.

  5. Group Tables By Schema in Object Explorer

    Hi,
    For efficiency, it would be nice that, in object explorer of MSSMS, tables be grouped by schema.

    Thanks!

    24 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 →
  6. 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

    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 →
  7. 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 →
  8. 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 COMPATIBILITYLEVEL=140. But it works as expected using COMPATIBILITYLEVEL=130. As I didn't find any word on this breaking change in the docs, I consider this a bug.

    23 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 →
  9. 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 spexecuteremote has to be copied into a temporary table variable, which impacts performance, just to strip away the $ShardName field.

    23 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 →
  10. Add array-support to SQL server engine

    Arrays are a core part of programming, except in SQL-server, where they don't exist.

    PostgreSQL has had array support for over 15 years (version 7.1 in 2001) by now...
    https://www.postgresql.org/docs/current/static/arrays.html

    And yet, in the year 2016, in the MS-universe, we still can't even parametrize an IN-clause in a SIMPLE & performant way, for example in a table-valued function or when passing a multiple-choice drop-down selection from SQL-Server reporting-services, or the combination of the two...

    Instead, there are a multitude of horrible, error-prone, complicated, user-unfriendly, slow & unperformant ways of working around the problem. For example, when you need to prevent…

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

    Upvotes: 6

    <=-=Mar 3 2017 2:00PM=-=>

    We are aware that this is important missing feature; however, we have no plans to add this in the near future. We will keep this request in backlog to see how many votes will get.

    <=-=Jul 1 2017 7:03PM=-=>

    Can’t even remember how many times I’ve wished to have this in SQL server.

    If you want to get a somewhat realistic feel of how talked about this feature really is, you can check out the number of votes and more importantly views that this StackOverflow question has:

    https://stackoverflow.com/questions/1732613/how-to-declare-array-inside-sql-server-stored-procedure

    Unfortunately, not many people visit this website and participate in voting here so it’s not a reliable indicator of how much any particular feature is requested. Please consider moving to UserVoice. Visual Studio UserVoice for example has loads more activity.

    <=-=Jul 1 2017 11:17PM=-=>

    We totally understand that t-sql as a language itself is desigened to…

  11. Sort order of Environment Variables in "Set Parameter Value" dialog of SSIS Solution

    When mapping Parameters to Environment Variables in the Configure screen of an SSIS project, the dropdown containing environment variables isn't sorted alphabetically. This makes finding the required variable difficult when working with large lists of values.

    23 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 →
  12. SSMS - Right-click menu option to "Design" tables configured as Temporal Tables is missing

    I am using the new Temporal Table feature on certain tables in my database.

    When I navigate to such tables via SSMS Object Explorer, I have found that the "Design" right-click menu option is not present for Temporal Tables. (Or as SSMS labels them, "System-Versioned" tables.)

    The "Design" option is, however, still available for NON-temporal tables.

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

    We’ll send you updates on this idea

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

    Upvotes: 0

    <=-=Mar 7 2017 2:10AM=-=>

    Thank you for taking time to post this issue!
    I can confirm that Design option in SSMS Object Explorer is not available for temporal (system-versioned) tables.
    Unfortunately, we do not plan to include a fix for this issue in the upcoming release.
    We might include it as an improvement in a future release, though.
    How important for you is to have this option available for temporal tables?

  13. Columnstore REORGANIZE command increases modification_counter on table statistics

    Reorganizing a columnstore index can, in some cases, cause the modificationcounter on the table's statistics to increase. This is unexpected because while the underlying structure of the table is being changed, the data itself isn't being modified. This can lead to issues where SQL is automatically updating statistics the next time a query is executed against the table in cases where the modificationcounter increases a significant amount because of the REORGANIZE. This has been particularly problematic for our large fact table with 150+ billion rows and full scan statistics across 40+ columns.

    Attached is a reproduction script with…

    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  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  14. 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.

    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  ·  Other  ·  Flag idea as inappropriate…  ·  Admin →
  15. 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].[dmoshostinfo] and [sys].[dmdblogstats], and the recently released SQL Server 2016 SP2 adds a new DMV [sys].[dmdbloginfo] and new columns to the DMV [sys].[dmossysinfo] which will be missing from the SQL Server 2016 master.dacpac.

    Any referenes to new DMV's/columns generate…

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

    22 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 →
  17. Accessible PDF Generation from SSRS

    It should be possible to generate accessible, WCAG/Section 508 compliant PDFs from SSRS. Please add this capability to SSRS.

    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 →
  18. Add columns has_victims and is_intra_query_deadlock to the event xml_deadlock_report

    When working with deadlocks I have found that it would be useful to to have some columns added to the event xmldeadlockreport.


    • hasvictims - SQL Server sometimes generates large number of intra-query xmldeadlock_report - events with no victims, and no errors. It would be useful to be able to filter them out.


    • isintraquery_deadlock - Intra-query deadlocks where the process is aborted (getting an error) are rather serious bugs in SQL Server.
      When working with Microsoft Support you are often asked for stack dumps. You can use extended events to generate stack dumps, but then…

    22 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 →
  19. 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" :…

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

    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.

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

  • Don't see your idea?

SQL Server

Categories

Feedback and Knowledge Base