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

    19 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. 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  ·  17 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  3. 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 →
  4. 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…

    18 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. Active Directory Integrated Authentication: Add Support for AAD-DS joined VMs

    For Cloud-only deployments using Azure AD Domain Services and AADDS joined VMs, Active Directory Integrated Authentication should be possible to connect from a Windows Application to Azure SQL Database.

    At the moment SSMS fails to login with:

    Error code 0xCAA9001F; state 10
    Integrated Windows authentication supported only in federation flow. (.Net SqlClient Data Provider)
    Procedure: ADALGetAccessToken

    Since there is AADDS + AAD, no federation to a onprem AD exists.

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

    We’ll send you updates on this idea

    2 comments  ·  Setup + Deployment  ·  Flag idea as inappropriate…  ·  Admin →
  6. Improve visual analysis of the Columnstore Indexes by adding an option to highlight the iterators executing with Batch Execution M

    Current performance analysis of the complex execution plans with Columnstore Indexes, requiring to open each of the important iterators and consult its properties in order to find whether its running with Batch Execution Mode.

    Please consider providing a visual option to highlight the iterators that are running with Batch Mode within the execution plan.
    It can be something simple as a default "Lightning Bolt" icon on the iterators within the execution plan, to something more collaborate as a right-mouse click that will highlight the batch iterators.

    18 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 →
  7. Performance of shredding the XML data type using nodes() / value().

    Performance of shredding XML to rows using nodes() and value() get linearly worse with the number of columns returned.
    The reason is that each column is handled by a separate branch of its own in the query plan.
    A nested loop join against a table valued function that returns the value.
    Comparing performance of shredding using nodes() with openjson I have seen that when there are three or more columns shredded,
    openjson is the clear winner.

    My suggestion is that you build a way of shredding XML similar to how it is done for shredding JSON.
    An openxml that can…

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

    <=-=Mar 4 2017 9:33AM=-=>

    We understand the problem. XML is designed differently and the goal was to provide rich query language with XPath support, which might require a lot of memory and processing logic. OPENJSON is designed to be more lightweight and just scan JSON text and return values where it finds them. There are pros and cons for both approaches (similar to pros and cons for DOM and SAX parsers).

    The key differentiators between JSON and XML is the fact that JSON is better for scan based processing of JSON columns where you just pick few values from JSON text, and XML is better for rich querying and indexing.

    I will keep this item open and let people vote for this; however, we cannot guarantee that this kind of re-design of XML will be done in near future.

    If you need to use shredded nodes in some…

  8. 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 DISCOVERXMLMETADATA 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>

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

    We’ll send you updates on this idea

    7 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  9. begin of month function

    We now have the EOMONTH function the give us the end of the month, which is a fantastic addition and it saves us quite some typing in date logic. However, it would be nice to also have a BOMONTH function for the start of the month. Right now we have to do the following:

    DATEADD(DAY,1,EOMONTH(GETDATE(),-1))

    This expression can be replaced by one simple function call: BOMONTH(GETDATE())

    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 →
  10. 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 →
  11. 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 →
  12. GRANT EXECUTE ANY EXTERNAL SCRIPT not recognised by SSDT

    Hello,

    The permission GRANT EXECUTE ANY EXTERNAL SCRIPT is not recognised by SSDT therefore makes dacpac deployment of a project using R (and I guess Python too) services a hassle.

    This is broken in all versions of SSDT up to and including 17.3.

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

    We’ll send you updates on this idea

    3 comments  ·  Setup + Deployment  ·  Flag idea as inappropriate…  ·  Admin →
  13. 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 →
  14. SSRS 2016 Report Descriptions Missing from Tiles

    We have an SSRS 2012 Native Mode instance we are looking to upgrade to SSRS 2016 SP1 Native Mode. In SSRS 2012, the report Descriptions appear on the report Tiles. We put a lot of work into carefully crafting the Names and Descriptions of our many reports into coordinated pairs that fit perfectly on the Tiles and clearly identify reports (and folders) to our users. In SSRS 2016 SP1, the Descriptions are not displayed; this will definitely diminish the usability of our SSRS site when we upgrade. It is very troublesome when an upgrade removes features that an implementation is…

    17 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 →
  15. 17 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 →
  16. Query store create database

    If you enable Query Store on model, then attempt to create a database, Query Store will be enabled on the new database.

    However, if you attempt to create a database using a non-default collation, the CREATE database fails with a deadlock on sys.sysschobj, in the middle of a DQS transaction. (I'm using 2017 CU4)

    I should be able to specify a non-default collation when creating a database, and not have DQS cause a failure.

    --Enable Query Store on model
    ALTER DATABASE model SET QUERYSTORE (OPERATIONMODE = READWRITE);
    GO
    --Create a database using any collation other than your

    16 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 →
  17. xp_delete_file on SQL Server 2017/Linux

    The xpdeletefile 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 @BACKUPDIR NVARCHAR(256);

    EXEC master.dbo.xp
    instanceregread
    @rootkey = 'HKEY
    LOCALMACHINE',

    @key = 'Software\Microsoft\MSSQLServer\MSSQLServer',

    @value
    name = 'BackupDirectory',
    @BackupDirectory = @BACKUPDIR OUTPUT;
    SELECT @BACKUP
    DIR

    EXEC master.sys.xpdeletefile 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 →
  18. SSRS 2016 Web Portal Customization (also missing RepotingServices.css)

    Hello Connect Team,

    I am trying to change the user experience of an SSRS 2016 Web Portal by "hiding" or "customizing" popover menus:

    If I assign the lowest possible role to a user ("View Report"), they are still able to see and change "Parameters" on that report.

    We want to provide a simple and streamlined experience for over 200 users to only be able to:
    1. Open a report
    2. View History
    3. Add to favorite
    Hopefully without the need of multiple clicks and only one popover menu.

    Based on my research, In order to accomplish this we need to…

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

    <=-=Jun 26 2017 2:07AM=-=>

    I need that css file, too. Just want to change the color of the header. Not everyone want to licence a Enterprise-Edition for branding their SSRS. And in prior version it was possible.

    <=-=Jul 21 2017 6:18AM=-=>

    I would like the ability to add a link to my SSRS home page (https://ourreportserver/reports) – like a link to Logout or back to our corporate site.

    <=-=Sep 13 2017 10:58AM=-=>

    Just voted.

    <=-=Nov 15 2017 8:42PM=-=>

    Also looking to migrate from SQL2014 to 2017 Express but not being able to continue with my customisations will probably halt that progress.

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

    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

  20. Enhance the "AT TIME ZONE" functionality to allow specifying "use server local time" instead of an explicit time zone name.

    Enhance the "AT TIME ZONE" functionality to allow specifying "use server local time" instead of an explicit time zone name. Right now you need to know the server's time zone when using the AT TIME ZONE functionality.

    Something like:

    DECLARE @TZ NVARCHAR(100) = 'Server Time Zone'
    DECLARE @D DATETIMEOFFSET = '2017-01-01T00:00:00+00:00'
    SELECT @D AT TIME ZONE @Tz

    Right now you need to know the server's time zone (e.g., 'Eastern Standard Time'). This isn't always know when you are installing software around the world.

    I don't care about the syntax.

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

SQL Server

Categories

Feedback and Knowledge Base