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. Plain Text Password in Maintenance Plans

    The user and password of the user that create a Maintenance Plans using the MSSMS is save in plain text, you can use the following query to access this informaci�n.

    SELECT CAST(CAST(packagedata AS VARBINARY(max)) AS VARCHAR(max)) FROM sysssispackages
    WHERE name LIKE 'MaintenancePlansName'

    This will show the xml of Maintenance Plans including the user and password.

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

    We’ll send you updates on this idea

    0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  2. LocalDb Data Defaults Data Directory to User Profile Root. Location cannot be changed via SSMS

    LocalDb for some reasons sets the default data directory to the root of the user profile directory. This means all database files, logs and backups clutter the root of this directory. There does not seem to be a way to change the default data locations. Trying to change the option in SSMS Server Properties -> Database Settings results in a RegCreateKeyEx() returned error 5, "Access is denied" error (Microsoft Sql Server, Error: 22002). Files by default should go into a sub directory of the user profile e.g Databases and this should be able to be changed via SSMS.

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

    We’ll send you updates on this idea

    5 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  3. Microsoft SQL Server 2014: Incorrect result when selecting from nested query with UNION in CROSS APPLY

    When selecting from a nested query in an OUTER APPLY statement the nested query seems to be evaluated only once in certain circumstances.

    Test Case 1

    Evaluates nested FROM query for every row in VALUES (imho expected behaviour)

    SELECT

    v,
    
    v2

    FROM

    (VALUES (1), (2), (3), (4)) AS inner_query(v)
    
    OUTER APPLY (
    SELECT
    MAX(inner_v2) AS v2
    FROM (
    SELECT
    15 AS id,
    v AS inner_v2
    ) AS outer_query
    GROUP BY id
    ) AS outer_apply

    Result:

    | v | v2|
    |---|---|
    | 1 | 1 |
    | 2 | 2 |
    | 3 | 3 |
    | 4 | 4 | …

    7 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 →
  4. TRY_PARSE and PARSE produce an error converting "NaN" value into Real or Float

    DECLARE @R REAL = TRY_PARSE('NaN' as REAL)
    SELECT @R
    GO

    DECLARE @F FLOAT = TRY_PARSE('NaN' as FLOAT)
    SELECT @F
    GO

    Both statements instead of NULL return an Error: "An error occurred while executing batch. Error message is: Arithmetic Overflow."

    Moreover, you can insert value in a table and any query, which will hit that value will fail with that error.

    DBCC CHECKDB recognizes it as a corruption.

    Seen on 2016 & 2019 on premise.

    7 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 →
  5. CmdExec job step editor slow to open

    In SSMS 18.2, when trying to edit a CmdExec job step, the editor is super slow to open, often takes 30 or more seconds. This same behavior does not happen with T-SQL job steps.

    7 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 →
  6. Database diagram kills SSMS

    When i want to open an existing diagram in SSMS 18.2 on a database which is on compativity level 100 SSMS closes and restarts immediately. I also tried it with a new diagram - this seems to work.

    7 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 →
  7. SSMS 18.x freezes when view job properties

    We use multiserver administration forour SQL Agent jobs and when attempting to view any jobstep properties of managed jobs while connected to a target server using SSMS 18.x, the program freezes for up to two minutes.

    SQL Server Management Studio 15.0.18142.0
    Microsoft Analysis Services Client Tools 15.0.1389.0
    Microsoft Data Access Components (MDAC) 10.0.14393.0
    Microsoft MSXML 3.0 6.0
    Microsoft Internet Explorer 9.11.14393.0
    Microsoft .NET Framework 4.0.30319.42000
    Operating System 10.0.14393

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

    We’ll send you updates on this idea

    2 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  8. SqlPackage.exe - CREATE USER [<name>] FOR EXTERNAL PROVIDER;

    CREATE USER [<name>] FROM EXTERNAL PROVIDER; is the correct syntax for creating database users from Azure AD.

    SqlPackage.exe generates CREATE USER [<name>] FOR EXTERNAL PROVIDER;. Executing this creates an invalid database user.

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

    We’ll send you updates on this idea

    0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  9. SSMS 18 Remembers password for user, not specific to database

    I have a number of SQL Server connections which use the same login but each has a different password. Before SSMS 18 the remember password function worked correctly, storing the password for individual connections.

    Under SSMS 18 it appears to only be storing the last used password for the login regardless of the server connection.

    Can this be restored to the previous functionality.

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

    We’ll send you updates on this idea

    2 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  10. Hybrid Buffer Pool using all conventional memory on SQL Server 2019 CTP 3.0 when trace flag 834 is enabled

    In SQL Server 2019 CTP 3:


    1. Set your max server memory so that SQL Server doesn't use all the memory on the box - in my case, I'm using a 16GB VM, and I set max memory to 10GB


    2. Enable Locked Pages in Memory and trace flag 834 (large pages)


    3. Restart the SQL Server. SQL Server uses the appropriate amount of memory, say 10GB.


    4. Enable Hybrid Buffer Pool:


    5. Enable hybrid buffer pool:


    EXEC spconfigure 'show advanced', 1;
    RECONFIGURE
    EXEC sp
    configure 'hybrid', 0
    RECONFIGURE

    And restart the SQL Server. SQL Server instantly uses all of the memory on the…

    7 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 →
  11. SSIS Server Maintenance job fails

    For most recent CUs of 2017 SQL the native SSIS Server Maintenance job Fails (for AlwaysOn at least). See notes here.

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9ce94c74-d552-48ec-8621-8be3124c53bc/ssisdb-2017-cu8-running-in-always-on-group-maintenance-procedure-bug?forum=sqlintegrationservices

    Manually modifying the proc if executes will fix the error but that isn't something end users should be changing.

    Resolution: The cursor deallocate statement on line 175 of proc "ssisdb.internal.cleanupserverretention_window" needs to be moved two lines down.

    http://answers.flyppdevportal.com/MVC/Post/Thread/238eb212-f757-4ce8-acea-e4c579628bd2?category=sqlintegrationservices

    7 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 →
  12. DacFx - LongRunningQueryTimeoutSeconds assigned wrong value

    When a DacService.Publish is run for the 1st time on a clean machine, it creates some registry keys in "Software\Microsoft\VisualStudio\10.0\SQLDB\Database", however, when LongRunningQueryTimeoutSeconds is created, it then reads back the value from 'QueryTimeoutSeconds', so it gets setup with the wrong value (60 as apposed to 0), this can cause the publish to fail, but then work correctly the next time it is run.

    7 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 →
  13. Connection-terminating error when inlining scalar UDF in a view in 2019 CTP2.2

    When we installed SQL Server 2019 CTP 2.2 on a testing server and began running a test workload, we immediately saw connection-terminating errors every time a particular view was accessed.

    We narrowed the issue down to a view that uses a scalar UDF, and that scalar UDF has a call to the USER_NAME() function. The error does not happen when a sysadmin is logged in, but does happen for a login with dbo privileges on the database.

    The attached file contains a simplified, standalone reproduction of the issue (submitted as .txt because the system says .sql files are not allowed).

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

    We’ll send you updates on this idea

    0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  14. SQL 2016 Server Agent does not read Windows Credential when running a job step with Proxy

    When running an SSIS package to copy files to Azure File Storage, a Windows Credential is used with the Azure credential information to be able to authenticate. This works fine in Visual Studio, and when executing package manually through SSISDB when deployed to server - but not when executed through SQL Server Agent using a proxy. The proxy credential have the correct Windows Credentials provided using cmdkey. The package execution fails with "The file name specified in the connection was not valid". Both UNC and drive mapping variant fails.

    A similar problem here:
    https://stackoverflow.com/questions/46497728/sql-server-agent-untrusted-domain-message/46567995#46567995

    And here:
    https://stackoverflow.com/questions/52228418/ssis-package-not-picking-up-configured-windows-credential-manager-credentials

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

    We’ll send you updates on this idea

    2 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  15. 7 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    4 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  16. Intellisense is noticeably slower in SSMS 18

    Intellisense is so slow it makes SSMS 18 Preview 4 unuable. I've reverted back to 17.9 for now. I ran them side by side on my machine to make sure it wasn't just an my machine or environment, but 17.9 performs well and 18 has extensive lag pulling up table names and fields as I type SQL queries.

    Below is the version info from my install of SSMS18:

    SQL Server Management Studio 15.0.18040.0
    Microsoft Analysis Services Client Tools 15.0.900.156
    Microsoft Data Access Components (MDAC) 10.0.16299.15
    Microsoft MSXML 3.0 6.0
    Microsoft Internet Explorer 9.11.16299.0
    Microsoft .NET Framework 4.0.30319.42000
    Operating System 6.3.16299

    7 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 →
  17. Cardinality estimator expects rows to be filtered in OUTER JOIN when dimension table has higher cardinality than fact

    Executing a query with an OUTER JOIN can cause the cardinality estimator to expect rows being filtered out. This should never be the case if we're doing an OUTER JOIN and can result in performance issues as the row set and memory needed for downstream operators is underestimated.

    Attached is a reproduction script and some sample data. A few additional observations:
    - This seems to happen when the dimension has a higher cardinality than the fact table. Perhaps not the main use case, but we have financials where codes may be added/removed over time, while the dimension table will contain…

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

    We’ll send you updates on this idea

    0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  18. Make Central Management Server remember Azure Integrated authentication for Azure SQL DB

    In SSMS 17.8.1 we have a number of Azure SQL DB instances registered in our CMS.

    Everytime we re-open SSMS the CMS switches the authentication method back to "Windows Authentication"

    Manually switching back to "Azure - Integrated" works, but this option should be persisted and remembered in the CMS.

    7 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 →
  19. VS2017 and SSDT Graph table with custom columnstore index wrong publish script

    VS 15.7.4
    SSDT 15.1.61804.210

    We have a SQL Server Database project with a Graph table and we created a Clusted Columnstore Index in it as follow:

    CREATE CLUSTERED COLUMNSTORE INDEX [IXCCSindex_name]

    ON [schema_name].[table_name];
    

    Trying to publish it from VS, wrong t-sql script is created:

    CREATE CLUSTERED INDEX [IXCCSindex_name]

    ON [schema_name].[table_name] ([$edge_id]);
    

    CREATE CLUSTERED COLUMNSTORE INDEX [IXCCSindex_name]

    ON [schema_name].[table_name] WITH (DROP_EXISTING = ON);
    

    The first CREATE INDEX fails, to successfully publish the index we had to remove square brackets specifying column name:

    CREATE CLUSTERED INDEX [IXCCSindex_name]

    ON [schema_name].[table_name] ($edge_id);
    

    CREATE CLUSTERED COLUMNSTORE INDEX…

    7 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 →
  20. VS2017 and SSDT doesn't import every Graph Database Objects

    VS 15.7.4
    SSDT 15.1.61804.210

    We created a new SQL Server Database Project in VS2017 and imported a database that use Graph DB features.
    We found that not every edge tables were impoted in project, it seems only tables with custom columns were imported.
    Edges with graph metadata columns only are ignored.

    You can see an example in the screenshot.

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

SQL Server

Categories

Feedback and Knowledge Base