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.

Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

Enter your idea

(thinking…)

Enter your idea and we'll search to see if someone has already suggested it.

If a similar idea already exists, you can support and comment on it.

If it doesn't exist, you can post your idea so others can support it.

Enter your idea and we'll search to see if someone has already suggested it.

Add support for Row Pattern Recognition in T-SQL (SQL:2016 features R010 and R020)

The ISO/IEC 9075:2016 standard (aka SQL:2016) introduces support for Row Pattern Recognition (RPR) in SQL. Similar to using regular expressions to identify patterns in a string, RPR allows you to use regular expressions to identify patterns in a sequence of rows.
To me, it's the next step in the evolution of window functions. If you think that window functions are profound and useful, RPR is really going to bake your noodle.
RPR has limitless practical applications, including identifying patterns in stock market activity, handling time series, fraud detection, material handling, shipping applications, DNA sequencing, gaps and islands, top N per group, and many others.
Similar to window functions, RPR supports partitioning and ordering, which are the basis for finding a pattern in a given sequence of rows, within the indicated partitions, based on the indicated ordering. Also similar to window functions, this feature lends itself for good optimization, with the ability to rely on index ordering to avoid sorting the data.
You can find coverage of the feature including examples in a 90-page technical report ISO/IEC TR 19075-5, available for free here:
https://standards.iso.org/ittf/PubliclyAvailableStandards/c065143_ISO_IEC_TR_19075-5_2016.zip
You can also find it as part of the SQL:2016 Part 2: Foundation document, available for purchase here: https://www.iso.org/standard/63556.html
I also provide a summarized coverage of this feature in an article (publication date April 10, 2019) here:
https://sqlperformance.com/2019/04/t-sql-queries/row-pattern-recognition-in-sql

SQL:2016 defines two RPR-related features:
Feature R010, “Row pattern recognition: FROM clause”
Feature R020, “Row pattern recognition: WINDOW clause”
R010 defines a clause/table operator called MATCH_RECOGNIZE, which you apply to an input table or table expression in the FROM clause, and you get a virtual table as output. The context is similar to that of other table operators like APPLY, PIVOT and UNPIVOT. Here’s the general form of a query using this feature:

SELECT <select_list>
FROM <source_table>
MATCH_RECOGNIZE
(
<match_recognize specification>
) AS <table_alias>;

As an example adapted from the aforementioned technical report, suppose that you’re given a table dbo.Ticker, with columns symbol, tradedate and price. The following query identifies V shapes in the stock prices (period with strictly falling price followed by period with strictly increasing price):

SELECT
MR.symbol, MR.matchnum, MR.startdate, MR.startprice,
MR.bottomdate, MR.bottomprice, MR.enddate, MR.endprice, MR.maxprice
FROM dbo.Ticker
MATCH_RECOGNIZE
(
PARTITION BY symbol
ORDER BY tradedate
MEASURES
MATCH_NUMBER() AS matchnum,
A.tradedate AS startdate,
A.price AS startprice,
LAST(B.tradedate) AS bottomdate,
LAST(B.price) AS bottomprice,
LAST(C.tradedate) AS enddate, -- same as LAST(tradedate)
LAST(C.price) AS endprice,
MAX(U.price) AS maxprice -- same as MAX(price)
ONE ROW PER MATCH -- default
AFTER MATCH SKIP PAST LAST ROW -- default
PATTERN (A B+ C+)
SUBSET U = (A, B, C)
DEFINE
-- A defaults to True, matches any row
B AS B.price < PREV(B.price),
C AS C.price > PREV(C.price)
) AS MR;

The PARTITION BY clause defines that you want to handle each stock symbol separately.
The ORDER BY clause defines ordering based on trade date.
The MEASURES clause defines measures that you want to compute for each match for the pattern.
ONE ROW PER MATCH means that the result table will have one row per pattern match, similar to the outcome of grouping. The alternative is ALL ROWS PER MATCH where you want the detail rows returned per pattern match.
AFTER MATCH SKIP PAST LAST ROW means that once a match is found, you want the next attempt to find a match to start after the last row of the current match. There are other alternatives like looking for the next match in the row following the first row of the current match (SKIP TO NEXT ROW), and others.
The DEFINE clause defines row pattern variables representing the different subsequences of rows in the pattern. In the above example A represents any row as a starting point, B represents a subsequence of decreasing prices (B.price < PREV(B.price)), and C represents a subsequence of decreasing prices (C AS C.price > PREV(C.price)).
The PATTERN clause uses regular expressions to identify a pattern. In the above query the pattern is (A B+ C+), meaning (any row, followed by one or more rows with increasing prices, followed by one or more rows with decreasing prices). By default the quantifiers are greedy, but you can define them to be reluctant.
Here’s the expected output of this query:

symbol matchnum startdate startprice bottomdat bottomprice enddate endprice maxprice
------- --------- ----------- ---------- ----------- ----------- ----------- --------- ---------
STOCK1 1 2019-02-13 151.00 2019-02-18 142.00 2019-02-20 152.00 152.00
STOCK1 2 2019-02-27 154.00 2019-03-04 140.00 2019-03-06 143.00 154.00
STOCK2 1 2019-02-14 329.00 2019-02-18 325.00 2019-02-20 328.00 329.00
STOCK2 2 2019-02-21 326.00 2019-02-25 317.00 2019-02-27 325.00 326.00
STOCK2 3 2019-03-01 324.00 2019-03-05 319.00 2019-03-07 326.00 326.00

R020 uses row pattern recognition as part of the windowing OVER clause (or WINDOW clause when naming a window specification) to further restrict a window frame. Just like the window partition restrict the rows of the input table expression (FROM…WHERE…GROUP BY….HAVING), and the window frame further restricts the window partition, with R020 you further restrict the window frame to the first pattern match in the windowed sequence of rows. Here’s a query demonstrating this:

SELECT T.symbol, T.tradedate, T.price,
startdate OVER W, startprice OVER W,
bottomdate OVER W, bottomprice OVER W,
enddate OVER W, endprice OVER W,
maxprice OVER W
FROM dbo.Ticker T
WINDOW W AS
(
PARTITION BY symbol
ORDER BY tradedate
MEASURES
A.tradedate AS startdate,
A.price AS startprice,
LAST(B.tradedate) AS bottomdate,
LAST(B.price) AS bottomprice,
LAST(C.tradedate) AS enddate, -- same as LAST(tradedate)
LAST(C.price) AS endprice,
MAX(U.price) AS maxprice -- same as MAX(U.price)
ROWS BETWEEN CURRENT ROW -- RANGE and GROUPS are not permitted with row pattern matching in windows
AND UNBOUNDED FOLLOWING
AFTER MATCH SKIP PAST LAST ROW
INITIAL -- pattern must start at first row of full window frame; alternative is SEEK
PATTERN (A B+ C+)
SUBSET U = (A, B, C)
DEFINE
-- A defaults to True, matches any row
B AS B.price < PREV(B.price),
C AS C.price > PREV(C.price)
);

Currently, the only platform I know of that implements R010 is Oracle. I don’t know of any platform that implements R020 yet.
It’s completely understood that for Microsoft to add a feature to T-SQL—especially such a substantial one—is a pretty significant investment. But what’s great about features that are added to T-SQL is that they pretty much stay there forever. There’s a huge community hungry for T-SQL improvements such as this one.

Thanks for your consideration!
Itzik
The ISO/IEC 9075:2016 standard (aka SQL:2016) introduces support for Row Pattern Recognition (RPR) in SQL. Similar to using regular expressions to identify patterns in a string, RPR allows you to use regular expressions to identify patterns in a sequence of rows.
To me, it's the next step in the evolution of window functions. If you think that window functions are profound and useful, RPR is really going to bake your noodle.
RPR has limitless practical applications, including identifying patterns in stock market activity, handling time series, fraud detection, material handling, shipping applications, DNA sequencing, gaps and islands, top N per…

256 votes

Sign in
prestine

(thinking…)

Sign in with: Microsoft

Forgot password?

Create a password

I agree to the terms of service

Signed in as (Sign out)

Close

Close

We’ll send you updates on this idea

7 comments · Suggestions · Flag idea as inappropriate… · Delete… · Admin →
SSMS Crash when dragging table from Object explorer into new query window

Have just installed this version v18.1 and everytime I drag a table from Object explorer into new query window SSMS crashes, I have to kill it.
I have no add-ins installed, have rebooted since installing.

O/s Win 10 15063.1387

93 votes

Sign in
prestine

(thinking…)

Sign in with: Microsoft

Forgot password?

Create a password

I agree to the terms of service

Signed in as (Sign out)

Close

Close

We’ll send you updates on this idea

49 comments · Bugs · Flag idea as inappropriate… · Delete… · Admin →

started · AdminMicrosoft SQL Server (Product Manager, Microsoft Azure) responded

Issue has been identified and a fix is being made. It will be rolled out with the next update of SSMS 18.

Thanks,
-Matteo
Add support for standard nested window functions in T-SQL

The ISO/IEC SQL:2016 standard defines a concept called nested window functions that allows you to nest two kinds of window functions as an argument of a window aggregate function. The idea is to allow you to interact with row markers representing strategic points in windowing elements like the beginning or end of the partition, beginning or end of the frame, the current outer row, and the current inner frame row.
The two standard nested window functions are the nested row number function and the nested value_of expression at row function.
The former allows you to refer in an argument of a window aggregate function to a row number at a specified row marker. Here’s the syntax of the nested row number function:

<agg_func>(< argument with ROW_NUMBER(<row marker>) >) OVER(<specification>)

You can use the following row markers:
• BEGIN_PARTITION
• END_PARTITION
• BEGIN_FRAME
• END_FRAME
• CURRENT_ROW (current outer row)
• FRAME_ROW (current inner frame row)

As an example for using a nested row number function, consider the following query against the Sales.OrderValues view (ER diagram can be found here: http://tsql.solidq.com/SampleDatabases/Diagrams/TSQLV5.jpg ) in the TSQLV5 sample database (source code can be found here: http://tsql.solidq.com/SampleDatabases/TSQLV5.zip):

SELECT orderid, custid, orderdate, val,
val -
AVG( CASE WHEN ROW_NUMBER(FRAME_ROW) NOT IN ( ROW_NUMBER(BEGIN_PARTITION), ROW_NUMBER(END_PARTITION) ) THEN val END )
OVER( PARTITION BY custid ORDER BY orderdate, orderid ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS diff
FROM Sales.OrderValues;

This query computes the difference between the current order value, and the customer average, but excluding the customer’s first and last orders from the average.
As for the nested value_of expression at row function, this function allows you to interact in an argument of a window aggregate function with the value of an expression at any of the aforementioned row markers. Here’s the syntax of the function:

<agg_func>(< argument with
VALUE OF <expression> AT <row marker> [<delta>] [, <default>]
>) OVER(<specification>)

As you can see, you can specify a certain negative or positive delta with respect to the row marker, and optionally provide a default value in case a row doesn’t exist at the specified position.
As an example, suppose you wanted to compute the difference between the current order value, and the customer’s average, but excluding orders placed on the current order’s order date. What you need is something similar to a correlation within the window function. By referring to the row marker CURRENT_ROW, you interact with the current outer row, and this way you can emulate a correlation. Here’s the query that you would use to achieve this task:

SELECT orderid, custid, orderdate, val,
val - AVG( CASE WHEN orderdate <> VALUE OF orderdate AT CURRENT_ROW THEN val END )
OVER( PARTITION BY custid ) AS diff
FROM Sales.OrderValues;

The other useful thing about nested window functions is that you can use them to emulate full support for the RANGE window frame unit, which is missing in T-SQL. For example, according to the standard, you can use the following query to return the moving average order value of the customer’s orders in the last 14 days:

SELECT orderid, custid, orderdate, val,
AVG(val) OVER( PARTITION BY custid ORDER BY orderdate
RANGE BETWEEN INTERVAL '13' DAY PRECEDING AND CURRENT ROW ) AS movingavg14days
FROM Sales.OrderValues;

But this syntax isn’t supported in T-SQL. If we had support for nested window functions, you would have been able to achieve the task like so:

SELECT orderid, custid, orderdate, val,
AVG( CASE WHEN DATEDIFF(day, orderdate, VALUE OF orderdate AT CURRENT_ROW) BETWEEN 0 AND 13 THEN val END )
OVER( PARTITION BY custid ORDER BY orderdate RANGE UNBOUNDED PRECEDING ) AS movingavg14days
FROM Sales.OrderValues;

These functions seem to be very powerful, and it would be good to have support for them in T-SQL.
Thanks for your consideration!
The ISO/IEC SQL:2016 standard defines a concept called nested window functions that allows you to nest two kinds of window functions as an argument of a window aggregate function. The idea is to allow you to interact with row markers representing strategic points in windowing elements like the beginning or end of the partition, beginning or end of the frame, the current outer row, and the current inner frame row.
The two standard nested window functions are the nested row number function and the nested value_of expression at row function.
The former allows you to refer in an argument of…

88 votes

Sign in
prestine

(thinking…)

Sign in with: Microsoft

Forgot password?

Create a password

I agree to the terms of service

Signed in as (Sign out)

Close

Close

We’ll send you updates on this idea

5 comments · Suggestions · Flag idea as inappropriate… · Delete… · Admin →
SSMS 18.0 redraw issues when switching between multiple query windows

SSMS 18.0 redraw issues when switching between multiple query windows

Repro:
1. Open SSMS
2. Open a new query window and enter and execute a command, such as:
SELECT @@SPID;
3. Open a second query window
4. Using your mouse, switch back and forth between the two open query windows by clicking on their tabs.
5. After several clicks (five to ten?), the content of the first window (query and result pane) may appear to be displayed in the second window, but you can't interact with any of the text.
6. Switching back and forth a few more times may clear up the issue temporarily.
7. The same issue may carry over to additional query windows which are opened, after switching between the various windows several times.

SSMS 18.0 redraw issues when switching between multiple query windows

Repro:
1. Open SSMS
2. Open a new query window and enter and execute a command, such as:
SELECT @@SPID;
3. Open a second query window
4. Using your mouse, switch back and forth between the two open query windows by clicking on their tabs.
5. After several clicks (five to ten?), the content of the first window (query and result pane) may appear to be displayed in the second window, but you can't interact with any of the text.
6. Switching back and forth a few more times may…

51 votes

Sign in
prestine

(thinking…)

Sign in with: Microsoft

Forgot password?

Create a password

I agree to the terms of service

Signed in as (Sign out)

Close

Close

We’ll send you updates on this idea

55 comments · Bugs · Flag idea as inappropriate… · Delete… · Admin →

planned · AdminMicrosoft SQL Server (Product Manager, Microsoft Azure) responded

Thanks everybody for providing the list. I’ve passed the info to the Visual Studio Team.

In the meanwhile, I have 2 more requests:
1) If you happen to have Visual Studio 2017 (version 15.9.11 or greater), could you check and see if it shows the same behavior?
2) If you happen to have SSMS 17.9.1 on the same machine, could you check and see if it shows the same behavior?

I’m hearing from folks here that “changing SSMS such that it disables h/w acceleration by default” is not a recommended thing to do… However, quite honestly, at this point, it may be the only option I can think about.. :-(

-Matteo
Database Diagrams

Please add Database Diagrams back in SQL SERVER Management Studio

64 votes

Sign in
prestine

(thinking…)

Sign in with: Microsoft

Forgot password?

Create a password

I agree to the terms of service

Signed in as (Sign out)

Close

Close

We’ll send you updates on this idea

36 comments · Suggestions · Flag idea as inappropriate… · Delete… · Admin →

started · AdminMicrosoft SQL Server (Product Manager, Microsoft Azure) responded

Thanks for the feedback, folks. I’m looking into this right now.

-Matteo
Put Debugger back into SSMS 18

The SSMS Debugger is critical for debugging complex T-SQL Queries, please return it to SSMS 18

223 votes

Sign in
prestine

(thinking…)

Sign in with: Microsoft

Forgot password?

Create a password

I agree to the terms of service

Signed in as (Sign out)

Close

Close

We’ll send you updates on this idea

76 comments · Suggestions · Flag idea as inappropriate… · Delete… · Admin →
Unable to deploy SSIS packages from Visual Studio 2017 (15.8.0)

The latest version of Visual Studio 2017 (15.8.0) appears to have introduced a bug preventing deployment of SSIS project files.

When attempting to connect to a server the deployment wizard returns the following error:

TITLE: SQL Server Integration Services
------------------------------

Could not load file or assembly 'Microsoft.SqlServer.Management.IntegrationServicesEnum, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The parameter is incorrect. (Exception from HRESULT: 0x80070057 (E_INVALIDARG)) (mscorlib)

------------------------------
ADDITIONAL INFORMATION:

The parameter is incorrect. (Exception from HRESULT: 0x80070057 (E_INVALIDARG)) (mscorlib)

------------------------------
BUTTONS:

OK
------------------------------

406 votes

Sign in
prestine

(thinking…)

Sign in with: Microsoft

Forgot password?

Create a password

I agree to the terms of service

Signed in as (Sign out)

Close

Close

We’ll send you updates on this idea

under review · 85 comments · Bugs · Flag idea as inappropriate… · Delete… · Admin →
Wrong Results When Selecting SQLServer2016 JOIN DATETIME2(3) with DATETIME

I'm getting unexpected results when joining between DATETIME2(3) column to DATETIME column with PK , in SQLServer 2016.

The details are :

I have the following table:

CREATE TABLE DATETIME_TEST (
[DATETIME] DATETIME NOT NULL,
[DATETIME2_3] DATETIME2(3));
ALTER TABLE DATETIME_TEST ADD CONSTRAINT PK_DATETIME_TEST PRIMARY KEY ([DATETIME]);
INSERT INTO DATETIME_TEST
( [DATETIME],[DATETIME2_3])
VALUES
('20020202 02:02:02.000', '20020202 02:02:02.000' ),
('20020202 02:02:02.003', '20020202 02:02:02.003' ),
('20020202 02:02:02.007', '20020202 02:02:02.007' ),
('2019-04-28 07:23:29.447', '2019-04-28 07:23:29.447' )
;

SELECT * FROM DATETIME_TEST WHERE CONVERT(DATETIME2(3), [DATETIME]) = [DATETIME2_3]
The results :

DATETIME DATETIME2_3
2002-02-02 02:02:02.000 2002-02-02 02:02:02.000
2002-02-02 02:02:02.003 2002-02-02 02:02:02.003
2002-02-02 02:02:02.007 2002-02-02 02:02:02.007
2019-04-28 07:23:29.447 2019-04-28 07:23:29.447

As you can see above, the values are equal .

SELECT
a.DATETIME,
a.DATETIME2_3
FROM DATETIME_TEST a
INNER JOIN DATETIME_TEST b ON CONVERT(DATETIME2(3),a. [DATETIME]) = b.[DATETIME2_3]
The results :
DATETIME2_3 DATETIME
2002-02-02 02:02:02.000 2002-02-02 02:02:02.000

although values are equal, I get only part of the rows.

But If I remove the PK or change compatibility level to COMPATIBILITY_LEVEL = 120

I get all rows as expected

Is it a bug ?

Is there a better way to do this join.

Note: I join to the same table only for the simplicity of the example in real life I join between 2 different tables.

I'm getting unexpected results when joining between DATETIME2(3) column to DATETIME column with PK , in SQLServer 2016.

The details are :

I have the following table:

CREATE TABLE DATETIME_TEST (
[DATETIME] DATETIME NOT NULL,
[DATETIME2_3] DATETIME2(3));
ALTER TABLE DATETIME_TEST ADD CONSTRAINT PK_DATETIME_TEST PRIMARY KEY ([DATETIME]);
INSERT INTO DATETIME_TEST
( [DATETIME],[DATETIME2_3])
VALUES
('20020202 02:02:02.000', '20020202 02:02:02.000' ),
('20020202 02:02:02.003', '20020202 02:02:02.003' ),
('20020202 02:02:02.007', '20020202 02:02:02.007' ),
('2019-04-28 07:23:29.447', '2019-04-28 07:23:29.447' )
;

SELECT * FROM DATETIME_TEST WHERE CONVERT(DATETIME2(3), [DATETIME]) = [DATETIME2_3]
The results :

DATETIME DATETIME2_3
2002-02-02 02:02:02.000 2002-02-02 02:02:02.000
2002-02-02 02:02:02.003 2002-02-02 02:02:02.003
2002-02-02 02:02:02.007 2002-02-02 02:02:02.007
2019-04-28…

54 votes

Sign in
prestine

(thinking…)

Sign in with: Microsoft

Forgot password?

Create a password

I agree to the terms of service

Signed in as (Sign out)

Close

Close

We’ll send you updates on this idea

0 comments · Bugs · Flag idea as inappropriate… · Delete… · Admin →
SSMS 18.0 GA can't be installed

When I'm trying to install SSMS 18.0 GA, I get error: "SSMS can only be upgraded by installing the package of the matching language. Please, use the matching version of the installer, or uninstall the current version of SSMS and run SSMS setup again." And I'm not alone: https://social.technet.microsoft.com/Forums/en-US/16d0e279-6ff4-4d5f-ad0a-5039235f03b9/ssms-2018-setup-blocked?forum=sqltools

41 votes

Sign in
prestine

(thinking…)

Sign in with: Microsoft

Forgot password?

Create a password

I agree to the terms of service

Signed in as (Sign out)

Close

Close

We’ll send you updates on this idea

25 comments · Bugs · Flag idea as inappropriate… · Delete… · Admin →

started · AdminMicrosoft SQL Server (Product Manager, Microsoft Azure) responded

I’m looking into this issue.

In the meanwhile, could you take a look at the SSMS setup logs (%TEMP%\SSMSSetup\SSMS-Setup-ENU_20190430231104.log). I’m interested in the lines in the range 133-216. Could you share those (tip: have a look at the lines and sanitize them, if you think they may contain sensible information?

Also, if you could share the output of:
reg query “HKLM\SOFTWARE\WOW6432Node\Microsoft\Microsoft SQL Server Management Studio” /reg:32 /s

And, finally, to unblock yourself:
1) Make sure you don’t have any previous version (RC, Preview) of SSMS 18.0 installed (check Add/Remove programs; if you do, uninstall it)
2) Make sure “C:\Program Files (x86)\Microsoft SQL Server Management Studio 18” does not exist (if it does, the delete it)
3) Delete the mentioned registry key (e.g. by running from an elevated cmd prompt:
reg DELETE “HKLM\SOFTWARE\WOW6432Node\Microsoft\Microsoft SQL Server Management Studio” /reg:32)
4) Retry setup
Please, let me know how it goes.

Thanks,
-Matteo

I’m looking into this issue.

In the meanwhile, could you take a look at the SSMS setup logs (%TEMP%\SSMSSetup\SSMS-Setup-ENU_20190430231104.log). I’m interested in the lines in the range 133-216. Could you share those (tip: have a look at the lines and sanitize them, if you think they may contain sensible information?

Also, if you could share the output of:
reg query “HKLM\SOFTWARE\WOW6432Node\Microsoft\Microsoft SQL Server Management Studio” /reg:32 /s

And, finally, to unblock yourself:
1) Make sure you don’t have any previous version (RC, Preview) of SSMS 18.0 installed (check Add/Remove programs; if you do, uninstall it)
2) Make sure “C:\Program Files (x86)\Microsoft SQL Server Management Studio 18” does not exist (if it does, the delete it)
3) Delete the mentioned registry key (e.g. by running from an elevated cmd prompt:
reg DELETE “HKLM\SOFTWARE\WOW6432Node\Microsoft\Microsoft SQL Server Management Studio” /reg:32)
4) Retry setup
Please, let…
SSMS 18.1 Crashes when Opening a Database Diagram

SSMS 18.1 crashes (and restarts) when opening a database diagram without any error message.

I have created several diagrams from scratch thinking it might have to do with old formatting, but this does not appear to be the case.

You can edit a diagram (adding dozens of tables) with no problem. It is the opening of the diagram that results in the crash.

Latest bits for SSMS and SQL Server 2017 installed today.

SQL Server Management Studio 15.0.18131.0
Microsoft Analysis Services Client Tools 15.0.1347.0
Microsoft Data Access Components (MDAC) 10.0.18362.1
Microsoft MSXML 3.0 6.0
Microsoft Internet Explorer 9.11.18362.0
Microsoft .NET Framework 4.0.30319.42000
Operating System 10.0.18362

SSMS 18.1 crashes (and restarts) when opening a database diagram without any error message.

I have created several diagrams from scratch thinking it might have to do with old formatting, but this does not appear to be the case.

You can edit a diagram (adding dozens of tables) with no problem. It is the opening of the diagram that results in the crash.

Latest bits for SSMS and SQL Server 2017 installed today.

SQL Server Management Studio 15.0.18131.0
Microsoft Analysis Services Client Tools 15.0.1347.0
Microsoft Data Access Components (MDAC) 10.0.18362.1
Microsoft MSXML 3.0 6.0
Microsoft Internet Explorer 9.11.18362.0
Microsoft .NET…

26 votes

Sign in
prestine

(thinking…)

Sign in with: Microsoft

Forgot password?

Create a password

I agree to the terms of service

Signed in as (Sign out)

Close

Close

We’ll send you updates on this idea

10 comments · Bugs · Flag idea as inappropriate… · Delete… · Admin →

planned · Matteo Taveggia responded

Thanks for the suggestion. We’ll take a look at it and prioritize accordingly.

Thanks,
-Matteo
Update produces impossible result

The following produces an illogical result:

DECLARE @Target table
(
c1 integer PRIMARY KEY,
c2 integer NOT NULL,
c3 integer NOT NULL
);

DECLARE @Source table
(
c1 integer NULL,
c2 integer NULL,
c3 integer NULL,

INDEX c CLUSTERED (c1)
);

INSERT @Target
(c1, c2, c3)
VALUES
(1, 0, 0);

INSERT @Source
(c1, c2, c3)
VALUES
(1, 2, NULL),
(1, NULL, 3),
(1, 4, 4);

UPDATE T
SET T.c2 = S.c2,
T.c3 = S.c3
FROM @Target AS T
JOIN @Source AS S
ON S.c1 = T.c1;

SELECT * FROM @Target AS T;

Result:

c1 c2 c3
1 2 3

The update is non-deterministic, but it should not produce a result that comes from a mixture of matched rows. It should throw an error in this case because the target does not accept nulls in any row, and the matching source rows all have a null.

The following produces an illogical result:

DECLARE @Target table
(
c1 integer PRIMARY KEY,
c2 integer NOT NULL,
c3 integer NOT NULL
);

DECLARE @Source table
(
c1 integer NULL,
c2 integer NULL,
c3 integer NULL,

INDEX c CLUSTERED (c1)
);

INSERT @Target
(c1, c2, c3)
VALUES
(1, 0, 0);

INSERT @Source
(c1, c2, c3)
VALUES
(1, 2, NULL),
(1, NULL, 3),
(1, 4, 4);

UPDATE T
SET T.c2 = S.c2,
T.c3 = S.c3
FROM @Target AS T
JOIN @Source AS S
ON S.c1 = T.c1;

SELECT * FROM @Target AS T;

Result:

c1 c2 c3
1 2 3

The…

27 votes

Sign in
prestine

(thinking…)

Sign in with: Microsoft

Forgot password?

Create a password

I agree to the terms of service

Signed in as (Sign out)

Close

Close

We’ll send you updates on this idea

4 comments · Bugs · Flag idea as inappropriate… · Delete… · Admin →
Optimise for Analytics configuration option

Would love to have a new Server-Wide as well as the Database Scoped Configuration configuration that would be called "Optimise for Analytics" and that would server the purpose of tuning the Storage Engine & Query Processor specifically for the Analytical Workloads (Business Intelligence, Data Warehousing, Data Preparation for ML, etc).

The actual functionalities to be controlled by this option would be such as: -E configuration, Query Optimiser huge preference for the Hash Joins, Bigger preference for the Batch Execution Mode for the Rowstore tables, Bigger preferences for the Scans and Prefetching for the Storage Engine, Bigger Columnstore Object Pool allocation for the server, Actual Cached Plans improvements, etc

Would love to have a new Server-Wide as well as the Database Scoped Configuration configuration that would be called "Optimise for Analytics" and that would server the purpose of tuning the Storage Engine & Query Processor specifically for the Analytical Workloads (Business Intelligence, Data Warehousing, Data Preparation for ML, etc).

The actual functionalities to be controlled by this option would be such as: -E configuration, Query Optimiser huge preference for the Hash Joins, Bigger preference for the Batch Execution Mode for the Rowstore tables, Bigger preferences for the Scans and Prefetching for the Storage Engine, Bigger Columnstore Object Pool allocation…

109 votes

Sign in
prestine

(thinking…)

Sign in with: Microsoft

Forgot password?

Create a password

I agree to the terms of service

Signed in as (Sign out)

Close

Close

We’ll send you updates on this idea

3 comments · Suggestions · Flag idea as inappropriate… · Delete… · Admin →
Run DBCC CHECKDB WITH PHYSICAL_ONLY automatically in the background

When RAID controllers are idle, they automatically check the storage to make sure it's still okay. Why not SQL Server too?

Some of the pieces are there – for example, SQL Server already has the ability to watch for idle CPU times and run Agent jobs when it’s bored. For starters, that’d probably be good enough to save a lot of small businesses from heartache. For the databases over, say, 100GB, it’d be really awesome to have resumable physical_only corruption checking – tracking which pages have been checked (just like how the differential bitmap tracks page changes), with page activity reset when the page is changed (again, just like the differential bitmap.) This wouldn’t count the same as a real CHECKDB, which needs to do things like compare index contents – but it'd be a big improvement over what we have now in environments where folks just aren't aware that the onus for data integrity is on them, not SQL Server.

When RAID controllers are idle, they automatically check the storage to make sure it's still okay. Why not SQL Server too?

Some of the pieces are there – for example, SQL Server already has the ability to watch for idle CPU times and run Agent jobs when it’s bored. For starters, that’d probably be good enough to save a lot of small businesses from heartache. For the databases over, say, 100GB, it’d be really awesome to have resumable physical_only corruption checking – tracking which pages have been checked (just like how the differential bitmap tracks page changes), with page activity…

356 votes

Sign in
prestine

(thinking…)

Sign in with: Microsoft

Forgot password?

Create a password

I agree to the terms of service

Signed in as (Sign out)

Close

Close

We’ll send you updates on this idea

16 comments · Suggestions · Flag idea as inappropriate… · Delete… · Admin →

under review · AdminMicrosoft SQL Server (Product Manager, Microsoft Azure) responded

This aligns with our vision for improved manageability, particularly for VLDBs. Will consider for future development.
Eager Index Spools Should Generate Missing Index Requests

In query plans where an Eager Index Spool is directly after a data access operator, a missing index should be generated in the query plan, and/or missing index DMVs that describes the definition of the index needed to make the spool unnecessary.

I would not expect this to happen when a Lazy Index Spool occurs above a subtree.

29 votes

Sign in
prestine

(thinking…)

Sign in with: Microsoft

Forgot password?

Create a password

I agree to the terms of service

Signed in as (Sign out)

Close

Close

We’ll send you updates on this idea

0 comments · Suggestions · Flag idea as inappropriate… · Delete… · Admin →
Allow Updating Statistics on the Secondary Replicas of the Availability Groups

Allow remote (on the secondary replicas) statistics update for the Availability Groups. Given that a lot of time the secondary replicas have extra resources
Potentially this could lead into spreading & parallelising the statistics recalculation processes while doing maintenance or potentially even ad-hoc.
The original idea behind this is described in the following blogpost – http://www.nikoport.com/2019/06/20/updating-statistics-on-secondary-replicas-of-the-availability-groups/

22 votes

Sign in
prestine

(thinking…)

Sign in with: Microsoft

Forgot password?

Create a password

I agree to the terms of service

Signed in as (Sign out)

Close

Close

We’ll send you updates on this idea

under review · 2 comments · Suggestions · Flag idea as inappropriate… · Delete… · Admin →
Option to specify a location for the DBCC CHECKDB snapshot

When you run DBCC CHECKDB, by default the snapshot is stored on the same drive as the database data file(s).

It would be nice to be able to specify the location, if you have

- Faster drives elsewhere
- No space on the same hard drive
- Less I/O traffic elsewhere

105 votes

Sign in
prestine

(thinking…)

Sign in with: Microsoft

Forgot password?

Create a password

I agree to the terms of service

Signed in as (Sign out)

Close

Close

We’ll send you updates on this idea

0 comments · Suggestions · Flag idea as inappropriate… · Delete… · Admin →
Database diagrams on new release 18 not work

I installed SSMS 18.0 and cannot find Database diagrams folder. It is just missing. Where is the problem?
- Untitled.png 110 KB
91 votes

Sign in
prestine

(thinking…)

Sign in with: Microsoft

Forgot password?

Create a password

I agree to the terms of service

Signed in as (Sign out)

Close

Close

We’ll send you updates on this idea

53 comments · Other · Flag idea as inappropriate… · Delete… · Admin →
Support DISTINCT for STRING_AGG

Currently STRING_AGG aggregates all strings passed as an input. It would be very useful to support DISTINCT, so it would concatenate unique strings only.

Example:
CREATE TABLE dbo.Test (a VARCHAR(255))
INSERT INTO dbo.Test (a)
VALUES('Str1')
,('Str2')
,('Str1')

SELECT STRING_AGG(a, ',') FROM dbo.Test t

It returns: 'Str1,Str2,Str1'

SELECT STRING_AGG(DISTINCT a, ',') FROM dbo.Test t

It would return: 'Str1,Str2'

151 votes

Sign in
prestine

(thinking…)

Sign in with: Microsoft

Forgot password?

Create a password

I agree to the terms of service

Signed in as (Sign out)

Close

Close

We’ll send you updates on this idea

9 comments · Suggestions · Flag idea as inappropriate… · Delete… · Admin →
Parallelize Script Update Mode

When moving SQL Server between Cumulative Updates, Script Update Mode runs against each database on the instance in series. This works fine on smaller instances, but with hundreds or even thousands of databases on an instance, this can add a lot of time (several hours) to the process of starting the instance for the first time with the new CU.

Suggestion: Run Script Update Mode in parallel, up to the MAXDOP setting for the instance.

40 votes

Sign in
prestine

(thinking…)

Sign in with: Microsoft

Forgot password?

Create a password

I agree to the terms of service

Signed in as (Sign out)

Close

Close

We’ll send you updates on this idea

3 comments · Suggestions · Flag idea as inappropriate… · Delete… · Admin →
Eager Index Spools Should Generate Wait Stats

In query plans where an Eager Index Spool is directly after a data access operator, wait stats should be generated while the Spool is built. In a parallel plan, EXECSYNC waits are generated, but in a serial plan, you don't see anything. Problem scenarios will become more common when FROID is released and adopted.

I would not expect this to happen when a Lazy Index Spool occurs above a subtree.

16 votes

Sign in
prestine

(thinking…)

Sign in with: Microsoft

Forgot password?

Create a password

I agree to the terms of service

Signed in as (Sign out)

Close

Close

We’ll send you updates on this idea

0 comments · Suggestions · Flag idea as inappropriate… · Delete… · Admin →

← Previous 1 2 3 4 5 … 476 477 Next →

Don't see your idea?

SQL Server

Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

Add support for Row Pattern Recognition in T-SQL (SQL:2016 features R010 and R020)

SSMS Crash when dragging table from Object explorer into new query window

Add support for standard nested window functions in T-SQL

SSMS 18.0 redraw issues when switching between multiple query windows

Database Diagrams

Put Debugger back into SSMS 18

Unable to deploy SSIS packages from Visual Studio 2017 (15.8.0)

Wrong Results When Selecting SQLServer2016 JOIN DATETIME2(3) with DATETIME

SSMS 18.0 GA can't be installed

SSMS 18.1 Crashes when Opening a Database Diagram

Update produces impossible result

Optimise for Analytics configuration option

Run DBCC CHECKDB WITH PHYSICAL_ONLY automatically in the background

Eager Index Spools Should Generate Missing Index Requests

Allow Updating Statistics on the Secondary Replicas of the Availability Groups

Option to specify a location for the DBCC CHECKDB snapshot

Database diagrams on new release 18 not work

Support DISTINCT for STRING_AGG

Parallelize Script Update Mode

Eager Index Spools Should Generate Wait Stats

Feedback

SQL Server

Feedback and Knowledge Base

Searching…

Give feedback

Microsoft Azure

Your password has been reset