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…

130 votes

Sign in
prestine

Your name

Your email address
(thinking…)

Password

Sign in with:

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

1 comment · 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
------------------------------

382 votes

Sign in
prestine

Your name

Your email address
(thinking…)

Password

Sign in with:

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 · 80 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…

102 votes

Sign in
prestine

Your name

Your email address
(thinking…)

Password

Sign in with:

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…

340 votes

Sign in
prestine

Your name

Your email address
(thinking…)

Password

Sign in with:

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

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

98 votes

Sign in
prestine

Your name

Your email address
(thinking…)

Password

Sign in with:

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

Your name

Your email address
(thinking…)

Password

Sign in with:

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 →
Database Level Option For Storing Wait Stats

SQL Server 2016 introduced Query Store, and SQL Server 2017 CU3 brought us aggregated wait stats at the query level. This is great information for data driven DBAs who need to be able to track plan changes over time, etc.

With that in mind, an additional data collector for wait stats per database would be a great product feature, independent from Query Store data collection.

We all know the limitations of the wait stats DMVs now:

- They're cumulative over time
- They're instance-wide
- There's a lot of potential noise from wait types that aren't registered

If you wanna figure out what's going on with them, you have to log them to a table yourself, or spend a bunch of money on a monitoring tool.

SQL Server should have a database-level option to store wait stats at a user-defined interval, for a user-defined period of time, the same way that Query Store works now. There could be additional options to only log significant waits based on occurrences and duration, etc. To make it extra useful, data could be centralized in msdb (or via another system view, like `sys.all_wait_stats` or something).

This would be an invaluable feedback mechanism for SQL Server users at all levels, especially now that Availability Groups and cloud migrations are more and more common. Admins with SQL Server responsibility need historical data like this to gauge workload efficacy on different hardware.

Thanks for reading!

SQL Server 2016 introduced Query Store, and SQL Server 2017 CU3 brought us aggregated wait stats at the query level. This is great information for data driven DBAs who need to be able to track plan changes over time, etc.

With that in mind, an additional data collector for wait stats per database would be a great product feature, independent from Query Store data collection.

We all know the limitations of the wait stats DMVs now:

- They're cumulative over time
- They're instance-wide
- There's a lot of potential noise from wait types that aren't registered

If you wanna…

238 votes

Sign in
prestine

Your name

Your email address
(thinking…)

Password

Sign in with:

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 · 1 comment · Suggestions · Flag idea as inappropriate… · Delete… · Admin →
Porting SMO Library to .NET Core

SQL Server has been ported to Linux for over a year now and it is time the complete SMO library (or what can be) is ported over to cross-platform as well.

Only 9 or so core files have been ported over to .NET Core and this allowed the sqlserver module to port over a small set of cmdlets to PS Core. This does not work for other modules like dbatools that use a wider berth of the library.

There are plenty of other modules in PowerShell (and applications too) that would like to provide cross-platform support for managing SQL Server enterprises. This cannot be done until SMO is completely ported over.

SQL Server has been ported to Linux for over a year now and it is time the complete SMO library (or what can be) is ported over to cross-platform as well.

Only 9 or so core files have been ported over to .NET Core and this allowed the sqlserver module to port over a small set of cmdlets to PS Core. This does not work for other modules like dbatools that use a wider berth of the library.

There are plenty of other modules in PowerShell (and applications too) that would like to provide cross-platform support for managing SQL Server…

97 votes

Sign in
prestine

Your name

Your email address
(thinking…)

Password

Sign in with:

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 · Suggestions · Flag idea as inappropriate… · Delete… · Admin →

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

CMS (RegisteredServers) has been ported. It will be included in the next version of SMO and/or the next version of the SQL Powershell module.

-Matteo
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'

108 votes

Sign in
prestine

Your name

Your email address
(thinking…)

Password

Sign in with:

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

8 comments · Suggestions · Flag idea as inappropriate… · Delete… · Admin →
Dark Theme for SQL Server Management Studio 2017 (SSMS 2017)

Dark Theme works well in Visual Studio and should be included in SSMS. It helps reduce eye strain after long periods of development and debugging.

449 votes

Sign in
prestine

Your name

Your email address
(thinking…)

Password

Sign in with:

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

unplanned · 32 comments · Suggestions · Flag idea as inappropriate… · Delete… · Admin →
SSDT for Visual Studio 2017 - Include as part of the VS 2017 Installer

Can we please pass over the SSDT components to the Visual Studio team so this can be included as part of the Visual Studio 2017 installation process?

The VS team can then build out the installer in a way that SSDT is always compatible with their latest build and installer. We've been stuck with the break fix break fix loop with SSDT and incompatibility with VS upgrades for too long now. The SSDT team doesn't respond quickly enough, the latest example being SSDT 15.6/VS 2017 15.7.x installation issue, why are we waiting so long for an installer update?

160 votes

Sign in
prestine

Your name

Your email address
(thinking…)

Password

Sign in with:

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 · 8 comments · Setup + Deployment · Flag idea as inappropriate… · Delete… · Admin →
Explain why indexes were skipped

Feature request: expose information about what indexes the optimizer considered while compiling an execution plan. Specifically, the index names and WHY the index was rejected for that particular query. This could potentially be included in per-operator execution plan properties (see attached mockup), an extended event target, or some other avenue.

Reason: many developers and DBAs wonder why certain indexes on their tables aren't being chosen for specific queries. This gives them better tools to figure that out and make better use of the SQL Server product.

I've blogged about this feature request in more detail here: http://joshthecoder.com/2018/06/21/feature-request-index-rejection-reasons.html

This request was inspired by a feature of the RavenDB document database: https://ayende.com/blog/183425-C/ravendb-4-1-features-explain-that-choice

Feature request: expose information about what indexes the optimizer considered while compiling an execution plan. Specifically, the index names and WHY the index was rejected for that particular query. This could potentially be included in per-operator execution plan properties (see attached mockup), an extended event target, or some other avenue.

Reason: many developers and DBAs wonder why certain indexes on their tables aren't being chosen for specific queries. This gives them better tools to figure that out and make better use of the SQL Server product.

I've blogged about this feature request in more detail here: http://joshthecoder.com/2018/06/21/feature-request-index-rejection-reasons.html

This request was…
- considered-indexes.png 6 KB
133 votes

Sign in
prestine

Your name

Your email address
(thinking…)

Password

Sign in with:

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 →
adomd core

ADOMD.NET Client Needs .NET Core Support

Please tell us when this will be available. We've been waiting years, and all ADOMD.NET does is XML over HTTP - it's pretty silly that there's no support for clients running on .NET Core!

55 votes

Sign in
prestine

Your name

Your email address
(thinking…)

Password

Sign in with:

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 →
Add SSIS to the SQL Server Docker Image

I work for a large bank which it trying to move hundreds of SSIS packages into GitLab pipelines. This task would happen faster an easier with SSIS on the MS SQL Server Docker image.

74 votes

Sign in
prestine

Your name

Your email address
(thinking…)

Password

Sign in with:

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 · 1 comment · Setup + Deployment · Flag idea as inappropriate… · Delete… · Admin →
Put Debugger back into SSMS 18

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

33 votes

Sign in
prestine

Your name

Your email address
(thinking…)

Password

Sign in with:

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

8 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
25 votes

Sign in
prestine

Your name

Your email address
(thinking…)

Password

Sign in with:

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

19 comments · Other · Flag idea as inappropriate… · Delete… · Admin →
Microsoft Command Line Utilities 15 for SQL Server fails to recognize pre-requisite ODBC 17

SQLCMD docs state that Command Line Utilities 15 requires ODBC 17 (https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-2017). However, the actual Command Line Utilities 15 installer does not recognize a previously installed ODBC 17.

Installing ODBC 13 will allow the Command Line Utilities 15 installer to complete successfully, but we end up with errors later presumably due to the Command Line Utilities 15 actually requiring OBDC 17 functionality. Installing both versions 13 and 17 seems to get around this, but obviously requires installation of both packages.

Our server for testing this is a 64 bit Windows 2008 R2 Enterprise with SP1.

Repro steps:
- Install ODBC 17 from the downloads available here: https://www.microsoft.com/download/details.aspx?id=56567
- Install Command Line Utilities 15 from the links available here: https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-2017
- Observe error message during Command Line Utilities installation: Setup is missing an installation prerequisite: -Microsoft ODBC Driver 17 for SQL Server. To continue, install Microsoft ODBC Driver 17 for SQL Server and then run the setup operation again

- Exit the utils installation.
- Install ODBC 13.1 from the downloads available here: https://www.microsoft.com/en-us/download/details.aspx?id=53339
- Run the Command Line Utilities 15 installer again.
- Observe success.

SQLCMD docs state that Command Line Utilities 15 requires ODBC 17 (https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-2017). However, the actual Command Line Utilities 15 installer does not recognize a previously installed ODBC 17.

Installing ODBC 13 will allow the Command Line Utilities 15 installer to complete successfully, but we end up with errors later presumably due to the Command Line Utilities 15 actually requiring OBDC 17 functionality. Installing both versions 13 and 17 seems to get around this, but obviously requires installation of both packages.

Our server for testing this is a 64 bit Windows 2008 R2 Enterprise with SP1.

Repro steps:
-…

25 votes

Sign in
prestine

Your name

Your email address
(thinking…)

Password

Sign in with:

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

6 comments · Bugs · Flag idea as inappropriate… · Delete… · Admin →
Add a Keyboard Shortcut that Executes the Statement where the Cursor is placed

Having to select statements by clicking and holding the mouse is just bad ergonomics. Request to have a keyboard shortcut which runs the current sql statement. The current sql statement is defined based on where the cursor is placed. For example, proposed shortcut runs only runs a single sql statement to the next semicolon. This shortcut would be an equivalent to Oracle's SQL developer's ctrl + enter shortcut. Note that this is probably the most used shortcut in SQL Developer and not having an equivalent on SQL Server Management Studio is disappointing. I know that not everyone ends their statements with semicolons but they can carry on manually selecting what they want to run and then pressing F5. But having a shortcut for running the current sql statement would make of a lot of SSMS users extremely happy.
Having to select statements by clicking and holding the mouse is just bad ergonomics. Request to have a keyboard shortcut which runs the current sql statement. The current sql statement is defined based on where the cursor is placed. For example, proposed shortcut runs only runs a single sql statement to the next semicolon. This shortcut would be an equivalent to Oracle's SQL developer's ctrl + enter shortcut. Note that this is probably the most used shortcut in SQL Developer and not having an equivalent on SQL Server Management Studio is disappointing. I know that not everyone ends their statements…

309 votes

Sign in
prestine

Your name

Your email address
(thinking…)

Password

Sign in with:

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 · Suggestions · Flag idea as inappropriate… · Delete… · Admin →

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

We’ll look at this for future release.
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 to even get to the ELSE block, since the IF logic is "IF ( 1= 1)". If you convert the code to a temp table the error goes away, and if you disable TF460 the error goes away.

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…
- TruncationBugRepro.txt 2 KB
21 votes

Sign in
prestine

Your name

Your email address
(thinking…)

Password

Sign in with:

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 · 5 comments · Bugs · Flag idea as inappropriate… · Delete… · Admin →
SQL Server Assertion: File: <lobss.cpp>, line = 725 Failed Assertion = '0' Should never happen.

DBCC CHECKDB reports no errors, I rebuilt the indexes just to be sure. It happens when running this query:

WITH t1 AS
(
SELECT [Endpoints].[Name] AS [f1],[MatchLocations].[Location] AS [f2],ISNULL(SUM([Matches].[LastCount]), 0) AS [f3],ROW_NUMBER() OVER (ORDER BY [Endpoints].[Name] ASC) AS [f4]
FROM MatchLocations
INNER JOIN [Matches] ON ([MatchLocations].[Id] = [Matches].[MatchLocationId])
INNER JOIN [Endpoints] ON ([Endpoints].[Id] = [MatchLocations].[EndpointId])
GROUP BY [Endpoints].[Name],[MatchLocations].[Location]
)
SELECT [t1].[f1] AS [t1_f1],[t1].[f2] AS [t1_f2],[t1].[f3] AS [t1_f3]
FROM t1
WHERE ([t1].[f4] BETWEEN 1 AND 100)
ORDER BY [t1].[f4] ASC

When the SUM and GROUP BY are removed, it runs just fine. It happens from time to time on some databases, on other databases (with the same schema) the query runs without errors.

Microsoft SQL Server 2017 (RTM-CU10) (KB4342123) - 14.0.3037.1 (X64) Jul 27 2018 09:40:27 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 17134: )

DBCC CHECKDB reports no errors, I rebuilt the indexes just to be sure. It happens when running this query:

WITH t1 AS
(
SELECT [Endpoints].[Name] AS [f1],[MatchLocations].[Location] AS [f2],ISNULL(SUM([Matches].[LastCount]), 0) AS [f3],ROW_NUMBER() OVER (ORDER BY [Endpoints].[Name] ASC) AS [f4]
FROM MatchLocations
INNER JOIN [Matches] ON ([MatchLocations].[Id] = [Matches].[MatchLocationId])
INNER JOIN [Endpoints] ON ([Endpoints].[Id] = [MatchLocations].[EndpointId])
GROUP BY [Endpoints].[Name],[MatchLocations].[Location]
)
SELECT [t1].[f1] AS [t1_f1],[t1].[f2] AS [t1_f2],[t1].[f3] AS [t1_f3]
FROM t1
WHERE ([t1].[f4] BETWEEN 1 AND 100)
ORDER BY [t1].[f4] ASC

When the SUM and GROUP BY are removed, it runs just fine. It happens from time to time on some databases,…

19 votes

Sign in
prestine

Your name

Your email address
(thinking…)

Password

Sign in with:

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

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

← Previous 1 2 3 4 5 … 457 458 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)

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

Optimise for Analytics configuration option

Run DBCC CHECKDB WITH PHYSICAL_ONLY automatically in the background

Option to specify a location for the DBCC CHECKDB snapshot

Parallelize Script Update Mode

Database Level Option For Storing Wait Stats

Porting SMO Library to .NET Core

Support DISTINCT for STRING_AGG

Dark Theme for SQL Server Management Studio 2017 (SSMS 2017)

SSDT for Visual Studio 2017 - Include as part of the VS 2017 Installer

Explain why indexes were skipped

adomd core

Add SSIS to the SQL Server Docker Image

Put Debugger back into SSMS 18

Database diagrams on new release 18 not work

Microsoft Command Line Utilities 15 for SQL Server fails to recognize pre-requisite ODBC 17

Add a Keyboard Shortcut that Executes the Statement where the Cursor is placed

Traceflag 460 causing truncation errors on code paths that are not followed

SQL Server Assertion: File: <lobss.cpp>, line = 725 Failed Assertion = '0' Should never happen.

Feedback

SQL Server

Feedback and Knowledge Base

Searching…

Give feedback

Microsoft Azure

Your password has been reset