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.

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 a built-in table of numbers
There are several SQL problems that can be solved with a table of numbers or dates.
This is a simple one-column table with numbers from 1 and up. Typical common problems
solved by with such a function:

o Show me sales for all months, including months for which there were no sales.
o Iterate over all characters in a string in a set-based statements.
o Any other query which you need to drive with a consecutive seres.

It's easy to construct and fill such a table. However, there are a couple of problems
with it:
1) If you query needs more numbers than there are in the table, you
get incorrect results.
2) Same thing happens if some smartass delets rows in the middle - or adds
a zero to the table.
3) There are queries where the optimizer does not know what the table is for
and makes incorrect assumptions.

The latter calls for some explanation. Assume this function:

CREATE FUNCTION inlinesplitme(@param nvarchar(MAX))
RETURNS TABLE AS
RETURN(SELECT ltrim(rtrim(convert(nvarchar(4000),

substring(@param, Number, charindex(N',' COLLATE Slovenian_BIN2, @param + N',', Number) - Number) ))) AS Value FROM Numbers WHERE Number <= convert(int, len(@param)) AND substring(N',' + @param, Number, 1) = N',' COLLATE Slovenian_BIN2)

This function is used to convert a comma-separated list of numbers to a
table. If I say.

CREATE PROCEDURE getcompanynamesinline @customers nvarchar(2000) AS
SELECT C.CustomerID, C.CompanyName
FROM Northwind..Customers C
JOIN inlinesplitme(@customers) s ON C.CustomerID = s.Value
go
EXEC getcompanynamesinline 'ALFKI,BONAP,CACTU,FRANK'

and have one million rows in my Numbers table, the optimizer thinks that
the Numbers table will return 300000 rows. Had Numbers been a
built-in table, the optimzier would have known the hit rate exactly.

Here is a further list, courtsey of Jeffery Moden:
Here are several things that I use it for.
1.� Continue to use it for a splitter because the STRING_SPLIT function doesn't return element positions nor guarantee any order.
2. Luhn mod 10 checksum for credit card, bank routing, and other similar checksum algorithms.
3. Relational multiplication where rows must be replicated based on a value within the row.
4. Solving complex problems such as many different overlapping dates, currently active rows, distributions of values over multiple months, etc, etc.
5.�Using it as a read-less "Pseudo-Cursor" (a row source with no SQL Level loop, every SELECT is a "Pseudo-Cursor") to generate millions of rows of test data in a very easy and intuitive manner in just seconds.
6. Many different string functions including numeric base translations, "initial caps", "data purification" functions such as returning all alpha or all numeric digit strings or removal of accidental control characters, unconventional splitters such as what I used to solve the conversion of million row Adjacency Lists to Nested Sets in less than a minute instead of days using the old push-stack method, removal of duplicated characters, special numeric checksum and encoding for GS1/Uuc/Ean-128 and other encoders/decoders, etc, etc, etc.�� There are several other non-delimited, character based places where performance would have suffered greatly had it not been for a Tally Table or Tally Function (numbers or sequence table or function for many of you).
That's certainly not the limit of what I used it for.� I just can't remember all the places I've used it.� It's one of those "imagination limited" tools where, to a good imagination, it's an unlimited tool.� Like I said, I�even use it as a simple "readless row source" to power some things that don't even need�a counter but still needs to act as a high performance loop.� Heh... and ask Itzik Ben-Gan about all the things he's used it for.
It would be really nice if it were a built in function that operated at machine language speeds, hopefully even faster than the "pseudo-cursors" that loop behind the scenes in every Insert, Select, Update, and Delete.
There are several SQL problems that can be solved with a table of numbers or dates.
This is a simple one-column table with numbers from 1 and up. Typical common problems
solved by with such a function:

o Show me sales for all months, including months for which there were no sales.
o Iterate over all characters in a string in a set-based statements.
o Any other query which you need to drive with a consecutive seres.

It's easy to construct and fill such a table. However, there are a couple of problems
with it:
1) If you query needs…
178 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

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

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

Upvotes: 278
<=-=Feb 1 2008 7:18PM=-=>
Thanks for the valuable suggestion.

This seems more like adding the sequence support which we’re seriously considering for the next major release.

Meanwhile, would using identity column help?
<=-=Feb 2 2008 2:11AM=-=>
It does not seem that you understood the request. This definitely has nothing to do with
IDENTITY. I am less versed about sequences, but I don’t think they will cut it either. If you think
it does, maybe you could provide an example? Take this problem: For the Orders table in
Northwind, write a query that lists the number of orders for all days in 1997. The result set should
include all 365 days, and list zero for days without a number.

This is a typical problem where you need a table of of numbers (or dates). While it’s easy to
create such a table, I argue in this request that there would be a number of advantages of
having such a table (function) builtin. For more details on the optimization problems, see my
article on Arrays and Lists in SQL Server, http://www.sommarskog.se/arrays-in-sql.html.
<=-=Feb 27 2008 11:40AM=-=>
Where do you stop with the content of the numbers table?

1-100? 1-1000000? What if I need 10billion? What if I need numbers up 2^64?? More?

Seems like an awful waste of space for something that may or may not be used.

As per workaround, use a function. (would be nice if that function was built-in..maybe that’s what they mean by ‘sequence’?)
<=-=Jul 3 2008 6:11AM=-=>
I would say 100000 by default
Another option would be if that 100000 is not good enough for you there would be a built in proc that will generate this table for you, you would have to pass in the start and end value and the proc would generate this table
<=-=Jul 3 2008 8:58AM=-=>
This would be a great asset and I believe it to be worth the storage requirement. 100,000 is a good number to default to and if more numbers are needed; I am sure some sort of system sp that builds the table dynamically. The bottom line is a lot of people are not acclimated with the concept of a numbers table and are missing out of all the performance gains from using one. Having MS built-in numbers table not only exposes the numbers table, but also allows those who do not want to manage an additional table to reap the benefits.
<=-=Jul 7 2008 5:11AM=-=>
Adding a built in table of numbers from 1 to 2 billion would certainly take up a lot of disk space especially if you consider the fact that most people never use one with a count over 8000 and those that do almost never use one over a million.

What I’d rather see is a whole number “sequence generator” where you could identify the StartValue, EndValue, and optional Increment which would default to 1. And, it should be able to order the values in an ascending or descending fashion as if it had a Clustered Primary Key doing so.
<=-=Jul 15 2008 9:59AM=-=>
Why would anything need to be stored on disk? Make the query engine intelligently produce the numbers on demand. It would be an in-memory operation, perhaps not even needing all rows to be materialized, as in the case of splitting a string on a delimiter where the majority of the numbers are discarded.

The optimizer could pay attention to how the numbers are used and do an in-memory loop through the string rather than creating a table, temp or otherwise. The table is uniform and regular, and re-generating it each time in a tight +1 loop is probably faster than building the whole thing. It’s like a kind of native index where you know in advance where everything is because the lookup index is exactly equal to the lookup value.

Look at it this way. What would the query engine have to do to satisfy this query:

SELECT Num FROM Numbers WHERE Num = 123

It wouldn’t have to hit the Numbers table at all: the input IS the result. The answer can be given at parse time. Similarly,

SELECT Num FROM Numbers WHERE Num BETWEEN 1 AND 100000

Again, why hit the table at all? It already knows the desired result, the numbers 1- 100000. It would be far more efficient to just generate the values in memory than go read them somewhere. (give me 1st value: okay, got 1. Give me 2nd value, okay, got 2. …)

Many problems are easily solvable with a numbers table that are difficult or impossible otherwise.
<=-=Aug 5 2008 12:58PM=-=>
Indeed, the table would only be logical, it would not be stored on disk.
<=-=Nov 25 2010 11:53PM=-=>
A little bit off-topic, but could I also get the same thing for dates? It just seems so stupid to create table that has dates so that you can join it with other tables to get one row for each day even if data doesn’t exist for that day.
<=-=Dec 14 2010 2:54PM=-=>
Please read feedback id 347442. Such a built-in function would have (potentially) significant performance impacts, lacking in user defined functions/solutions (mentioned feedback id has examples).
<=-=Jan 1 2011 6:00PM=-=>
Glad to see this suggestion. This functionality would help with many of the perennial problems that come up on news groups and forums.
<=-=Mar 14 2012 4:17PM=-=>
I see Postgres has the “generate_series” function for this need.
<=-=Jun 11 2012 1:19PM=-=>
Neither identity nor sequence will help here. It is hard to believe you even read the question.

+1
<=-=Nov 27 2012 12:04PM=-=>
Having had the “joy” of splitting a multi-XML-document proprietary 3rd-party supplied file of over 3MB, my Numbers table was 12M+ rows, just in case a heavy ordering day bumped the file size…

On SQL2012, using page-level compression, it consumes over 130MB.

If, as current best practices appear to recommend, our SQL Servers are stacked with huge quantities of RAM, 130MB in, say 120GB isn’t going to matter a great deal…

If Microsoft gave us a “standard set” of Numbers that is configurable, but which could be bumped on demand and shrunken automatically to the standard-set-size, memory pressure would be reduced while still delivering high-speed data-access.

My Dates table stretches from 2008 (birth of system) to 2099, comprising 33K rows of 32 columns (at present) and 16MB compressed, with alternate indexes. Again, a triflingly small memory “hog”.

I’m less persuaded that a Microsoft-supplied Dates table is a “need” simply because the slices and representations of dates in short/long/week/month/quarter, etc. are really site-specific (hence my 32 columns!).

+1 for the Numbers table…
<=-=Mar 19 2013 1:14PM=-=>
Please, please, please.
<=-=Nov 24 2014 9:27PM=-=>
Howdy there, Microsoft,

This suggestion is going to be 8 years old in 3 months. It would be wonderful to celebrate that anniversary with a CU for both 2012 and 2014 that contains this wonderful suggested tool. It shouldn’t take you long.

I’ll also suggest that it take two parameters so that folks can start it “0” if they need to (which is more common than you’d think).

Thank you for your time.
<=-=Nov 26 2014 4:42AM=-=>
Personally, I think this relatively simple addition is long overdue. The Tally table has been called the Swiss Army Knife of SQL and I have to agree. It can be used for everything from splitting strings to date generation. An ITVF that’s built into the product would allow for optimization that’s probably beyond what DBAs can do. I’ve been using a table of numbers for years and consider it completely normal, as do many others in the SQL Server community. Please consider the addition of this feature to the product.
<=-=Nov 26 2014 6:31AM=-=>
This would be handy even if it it were no larger than a single record and additional functionality were added to SQL Server such as “Connect BY” and so forth to allow for larger virtual numbers tables could be accessed. Many uses are out there for such a table and if there is any doubt, take a look at the DUAL table in Oracle.
<=-=Dec 1 2014 7:55AM=-=>
This is fairly trivial to develop, but also the issues raised above (integrity) are potentially a problem in app code. Think SQL Injection or some other tampering that wouldn’t be the case in a native numbers table.

While this would be a nice backport, I would argue this is fairly minor work for SQL Server 2014+ (in an SP) and giving us a table with an integer’s worth of numbers, would be very handy. I would suggest that the table be a DMV visible in every database.
<=-=Apr 2 2015 7:56AM=-=>
CREATE FUNCTION [dbo].[fn_Nums](@n AS BIGINT)
RETURNS TABLE
WITH SCHEMABINDING,ENCRYPTION
AS
RETURN
WITH
L0 AS,
L1 AS,
L2 AS,
L3 AS,
L4 AS,
L5 AS,
Nums AS OVER AS n FROM L5)
SELECT n FROM Nums WHERE n <= @n;
GO
<=-=Nov 4 2015 1:00AM=-=>
The easiest trick to generate table of numbers with OPENJSON:

create function
dbo.fn_gen_numbers(count int) returns table return (select cast([key] as int) as number from OPENJSON( '[1' + replicate(',1',count-1)+’]’))

You will need SQL Server 2016 CTP3 or higher for this.
<=-=Mar 5 2016 8:30AM=-=>
This should not be a real table with a fixed number of rows. This should be a generator TVP that can provide any number range, e.g.

select Number from Builtin_Numbers(1, 10000000)

It would be a pity if the built in numbers table would be restricted to some range, e.g. [1, 1M].

We also need such a generator for dates (all dates in a given range).
<=-=Mar 16 2016 12:15AM=-=>
I really love to have a number sequence generator build into SQL Server as it removed a lo

Upvotes: 278
<=-=Feb 1 2008 7:18PM=-=>
Thanks for the valuable suggestion.

This seems more like adding the sequence support which we’re seriously considering for the next major release.

Meanwhile, would using identity column help?
<=-=Feb 2 2008 2:11AM=-=>
It does not seem that you understood the request. This definitely has nothing to do with
IDENTITY. I am less versed about sequences, but I don’t think they will cut it either. If you think
it does, maybe you could provide an example? Take this problem: For the Orders table in
Northwind, write a query that lists the number of orders for all days in 1997. The result set should
include all 365 days, and list zero for days without a number.

This is a typical problem where you need a table of of numbers (or dates). While it’s easy to
create such a table, I argue in this request that…
Change limit/behaviour of msdb.dbo.suspect_pages table

Currently the msdb.dbo.suspect_pages table has a 1000 row limit, once the table gets to 1000 rows it stops collecting new errors. This means that on servers where the suspect_pages tables has filled up you are unable to see if new errors are still being discovered.

My suggestion would be to remove, or at least greatly increase the hard limit of 1000 rows in the suspect_pages table.

Books online Documentation - Manage the suspect_pages Table
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/manage-the-suspect-pages-table-sql-server?view=sql-server-ver15

"The suspect_pages table contains one row per page that failed with an 824 error, up to a limit of 1,000 rows."

"Database administrators are responsible for managing the table, primarily by deleting old rows. The suspect_pages table is limited in size, and if it fills, new errors are not logged."

Currently the msdb.dbo.suspect_pages table has a 1000 row limit, once the table gets to 1000 rows it stops collecting new errors. This means that on servers where the suspect_pages tables has filled up you are unable to see if new errors are still being discovered.

My suggestion would be to remove, or at least greatly increase the hard limit of 1000 rows in the suspect_pages table.

Books online Documentation - Manage the suspect_pages Table
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/manage-the-suspect-pages-table-sql-server?view=sql-server-ver15

"The suspect_pages table contains one row per page that failed with an 824 error, up to a limit of 1,000 rows."

"Database administrators are responsible…

172 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

6 comments · Suggestions · Flag idea as inappropriate… · Delete… · Admin →
T-SQL Common Table Expression "Materialize" Option
T-SQL Common Table Expression "Materialize" Option

I have run into a couple performance and results issues using Common Table Expressions (CTEs).

The first case is where I coded a data retrieval as a single, large query with a CTE that is referenced multiple times in the query. The CTE has an expensive query. I was able to determine, via execution plans, etc., that the results of the CTE were being recalculated multiple times, resulting in slow execution. I rewrote the data retrieval as multiple steps where the query of the CTE was first executed into a table variable, and I replaced the CTE references in my query with the table variable. The data retrieval ran much faster.

The second case involves a table that has a varchar column that stores user-typed input. The system knows for each row whether the user-typed input should be interpreted as text, numbers or dates. We wrote a query like this:

SELECT tt.TableID, CONVERT(int, tt.UserInput)
FROM dbo.TheTable tt
INNER JOIN dbo.Interpretation i
WHERE tt.IntprID = i.IntprID
AND i.Type = 'integer';

When we run the query we get the error "Conversion failed when converting the varchar value '4/7/2006 12:05:40 PM' to data type int." This is presumably because the optimizer is choosing to run the CONVERT statement before it has computed the join result.

I envision a T-SQL enhancement that would solve both of these problems. It would be to allow database developers to write a T-SQL common table expression something like this:

WITH MyCTE [(Col, …n)] [WITH {[UNIQUE] CLUSTERED INDEX (Col, …n)} [, INDEX(Col, …n), …n]]AS (
select ExpressionA, ExpressionB, …n
from MyTableSource
)
...

This would force the SQL Server Database Engine to compute and store the results of the CTE query in the equivalent of a table variable (or whatever), and subsequent references to the CTE name will be serviced from the stored results. Like any table or join hint, WITH INDEX should be only used as a last resort when default performance is unacceptable.

For the first case, it would essentially do what I did by explicitly creating and populating a table variable. It would limit the execution of the expensive query to one time.

For the second case, one could rewrite the query like this:

WITH TheCTE (TableID, UserInput) WITH CLUSTERED INDEX (TableID) AS (
SELECT tt.TableID, tt.UserInputCONVERT(int, tt.UserInput)
FROM dbo.TheTable tt

INNER JOIN dbo.Interpretation i WHERE tt.IntprID = i.IntprID AND i.Type = 'integer';

)
SELECT TableID, CONVERT(int, UserInput)
FROM TheCTE;

In this example, the rows where the UserInput is something other than integer have been forced to be filtered out before the CONVERT function is executed, and we will not get the "Conversion failed..." error.

Giving this suggestion serious consideration would be greatly appreciated.
T-SQL Common Table Expression "Materialize" Option

I have run into a couple performance and results issues using Common Table Expressions (CTEs).

The first case is where I coded a data retrieval as a single, large query with a CTE that is referenced multiple times in the query. The CTE has an expensive query. I was able to determine, via execution plans, etc., that the results of the CTE were being recalculated multiple times, resulting in slow execution. I rewrote the data retrieval as multiple steps where the query of the CTE was first executed into a table variable, and I…
153 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 · 18 comments · Suggestions · Flag idea as inappropriate… · Delete… · Admin →
In-Memory OLTP - allow removal of Filegroup for memory_optimized_data

It should be possible to remove the memoryoptimizeddata Filegroup and files, if all in-memory tables and procedures have been deleted.

Currently you cannot do this "by design", however this is a potential blocker to people using this technology due to the "no-going-back once it's enabled". It is now several years since Hekaton was first released - support for this feature is long overdue!

At present you get errors such as:
Cannot drop the last memory-optimized container
The filegroup ... cannot be removed because it is not empty.

147 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

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

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

Upvotes: 3
<=-=Jan 8 2018 12:16PM=-=>
Thanks for the suggestion.
We’ll consider it for a future release.

Are there specific limitations imposed when you have the filegroup, e.g., no database snapshot, that hold you back from creating the filegroup?
Or is it only the feeling of not being able to turn back?

—
Jos de Bruijn – Database Systems PM
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
144 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 →
Provide XEvents target directly into SQL Server table

Provide a way to directly write XEvent output directly into a SQL Server database table in a same or different instance and same or different database.

The goal being to be able to not have any events missed but have the entire stream captured to the table and also provide header information for when the capture table was created for easier management and deletion. Ideally there should be a purge task that could be activated to clean out such captures after X days.

XEvents is a great and powerful tool.
Customers should have an easier way to get it directly into SQL Server so they can consume the historization back via normal SQL syntax. XEvent output is structured in nature.

2 new target types in sys.dmxeobjects would likely be helpful:
synchronouswriteSQLtable
asynchrounouswriteSQLtable

Appropriate Security management would need to be put in place.

Provide a way to directly write XEvent output directly into a SQL Server database table in a same or different instance and same or different database.

The goal being to be able to not have any events missed but have the entire stream captured to the table and also provide header information for when the capture table was created for easier management and deletion. Ideally there should be a purge task that could be activated to clean out such captures after X days.

XEvents is a great and powerful tool.
Customers should have an easier way to get it directly…

137 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 · 4 comments · Suggestions · Flag idea as inappropriate… · Delete… · Admin →
Bring Back TFS and Git in SSMS

According to this post: https://blogs.technet.microsoft.com/dataplatforminsider/2016/11/21/source-control-in-sql-server-management-studio-ssms/ these features were removed "because they didn't fit in the overall experience" for SSMS. This seems like an incredibly short-sighted decision for your user base. Many SQL developers use SSMS specifically because it is lightweight and does not require the massive baggage and learning curve of the full VS. That doesn't mean we don't need source control or task tracking in our process. Removing functionality that integrates two Microsoft products seems like a massive step backward. The suggestion to "work around" this poor decision by manually editing a definition file comes across as particularly condescending, given SSMS's "intended" use as a lightweight tool. Our team can not adopt a standard that involves "hacking" configuration files.
According to this post: https://blogs.technet.microsoft.com/dataplatforminsider/2016/11/21/source-control-in-sql-server-management-studio-ssms/ these features were removed "because they didn't fit in the overall experience" for SSMS. This seems like an incredibly short-sighted decision for your user base. Many SQL developers use SSMS specifically because it is lightweight and does not require the massive baggage and learning curve of the full VS. That doesn't mean we don't need source control or task tracking in our process. Removing functionality that integrates two Microsoft products seems like a massive step backward. The suggestion to "work around" this poor decision by manually editing a definition file comes across as particularly condescending,…

133 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

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

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

Upvotes: 1
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:

<aggfunc>(< argument with ROWNUMBER(<row marker>) >) OVER(<specification>)

You can use the following row markers:
• BEGINPARTITION
• ENDPARTITION
• BEGINFRAME
• ENDFRAME
• CURRENTROW (current outer row)
• FRAMEROW (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…
131 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

8 comments · Suggestions · Flag idea as inappropriate… · Delete… · Admin →
SET STATISTICS IO should also show schema name for tables

When you use schemas in your database, and you have the same table names in multiple schemas, it is not clear, which schema the table belongs too in the output of the SET STATISTICS IO. E.g. a table called Profile, or configuration in different schema is very common at our databases

Today the output is just
Table 'Profile'. Scan count 1, logical reads 123, physical reads 1, read-ahead reads 121, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Profile'. Scan count 3, logical reads 13, physical reads 1, read-ahead reads 342, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Profile'. Scan count 10, logical reads 53, physical reads 1, read-ahead reads 1241, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Making it just much more difficult to figure out the underlying issues

Please include the schema name of the table in the output

When you use schemas in your database, and you have the same table names in multiple schemas, it is not clear, which schema the table belongs too in the output of the SET STATISTICS IO. E.g. a table called Profile, or configuration in different schema is very common at our databases

Today the output is just
Table 'Profile'. Scan count 1, logical reads 123, physical reads 1, read-ahead reads 121, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Profile'. Scan count 3, logical reads 13, physical reads 1, read-ahead reads 342, lob logical reads 0,…

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

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

111 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 · 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
Add language and optimizer support for ISO <distinct predicate>

SQL 1999 and later include the <distinct predicate> IS [NOT] DISTINCT FROM.

The definition of distinct is (quoting from the 2003 standard) "informally, not equal, not both null." This is consistent with where SQL Server (following the standard) already uses the keyword DISTINCT. Adding <distinct predicate> to T-SQL would simplify coding of joins, in particular, and as of 2008, MERGE statements in a variety of typical scenarios.

Example:

SELECT T1.this, T2.that
FROM T1 JOIN T2
ON T1.entry IS NOT DISTINCT FROM T2.entry

Currently, this must be written as

SELECT T1.this, T2.that
FROM T1 JOIN T2
ON (
T1.entry = T2.entry
OR
(T1.entry IS NULL AND T2.entry IS NULL)
)

This is a common requirement, but coding this for many columns is both tedious and error-prone (especially because of AND/OR precedence issues). Changing the setting of ANSINULLS is not a solution, because it does not affect column-to-column comparisons, only column to variable comparisons. Setting ANSINULLS to off is also non-standard and not granular enough to apply to specific comparisons in a single query.

SQL 1999 and later include the <distinct predicate> IS [NOT] DISTINCT FROM.

The definition of distinct is (quoting from the 2003 standard) "informally, not equal, not both null." This is consistent with where SQL Server (following the standard) already uses the keyword DISTINCT. Adding <distinct predicate> to T-SQL would simplify coding of joins, in particular, and as of 2008, MERGE statements in a variety of typical scenarios.

Example:

SELECT T1.this, T2.that
FROM T1 JOIN T2
ON T1.entry IS NOT DISTINCT FROM T2.entry

Currently, this must be written as

SELECT T1.this, T2.that
FROM T1 JOIN T2
ON (
T1.entry = T2.entry …

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

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

Upvotes: 289
<=-=Aug 9 2007 9:12AM=-=>
Benefits:
Improved Readability, therefore Maintainablity, therefore reduced manpower $ and time expenditure.
<=-=Aug 27 2007 6:13PM=-=>
I definitely see the value of this. Thanks for proposing it. We’ll try to squeeze it in to SQL Server 2008 but things are really tight in terms of room for changes like this. It has to compete with many other things, including a bunch that have a larger impact on query performance, or that don’t have an easy workaround. This issue has a workaround, though it is not pretty and programmability would be enhanced a lot with the proposed enhancement. I’ll see what I can do.

Best regards,
Eric
<=-=Oct 17 2007 2:06PM=-=>
Things do not look good for this enhancement for Katmai. It probably will not make it into the release. We’ll make a final assessment in a couple of weeks. Before we can consider this, we have to finish other commitments.
<=-=Mar 11 2011 6:41AM=-=>
What is the Root Duplicate for this please? This links back to 307476 which in turn links back to here but both have been closed as duplicates.
<=-=May 25 2011 3:46AM=-=>
Surely both items cannot be closed as duplicates. This makes no sense whatsoever. Please can someone at MS sort this out.
<=-=Nov 15 2011 11:28AM=-=>
This must not be just a syntax shortcut and be difficult to implement. Perhaps 2012?
<=-=Apr 23 2012 4:27PM=-=>
Hello Steve and others,
Due to a problem in our bug databases, there is some issue with several connect items not reflecting the status correctly. For this particular item, it is not possible to see the duplicate or the parent item.
But please be assured that the requests for IS DISTINCT predicate is our list of things to do for future & we are tracking it.

Thanks
Umachandar, SQL Programmability Team
<=-=Aug 15 2012 12:23AM=-=>
Yes, we could use this keyword.
On MERGE statements, we only want existing rows updated, if they actually changed.
The current syntax is very long and almost unreadable.
<=-=Jul 28 2015 8:46AM=-=>
Still waiting Microsoft. It’s been 8 years and five releases (2008, 2008R2, 2012, 2014, 2016) and still no sign of this. Do you even review Connect at all?
<=-=Jul 12 2016 6:36AM=-=>
We are using 2016 and we are still writing our Merge statements with MS’s crippled approach
<=-=Sep 22 2016 9:30AM=-=>
How do you add temporal tables, JSON data-types and the ability to dynamically stretch tables into the world’s most advanced Cloud RDBMS, and yet not find the time to implement a basic logical operator from the 17-year-old ANSI standard?
<=-=Sep 22 2016 9:34AM=-=>
It’s not just the aesthetics of the code at stake here, it’s performance. The workarounds involve either OR statements or the ISNULL function, both of which de-optimize queries in profound ways. A declarative syntax for doing this would improve execution plans around the world, saving trillions of CPU cycles and disk reads, and ultimately saving mankind. WON’T SOMEBODY THINK OF THE CHILDREN?
<=-=May 18 2017 11:14AM=-=>
Don’t you love how useful M$ Connect is for raising issues? Almost 10 years go by after someone at Microsoft says this is a “Good Idea” and yet, no results ….

Upvotes: 289
<=-=Aug 9 2007 9:12AM=-=>
Benefits:
Improved Readability, therefore Maintainablity, therefore reduced manpower $ and time expenditure.
<=-=Aug 27 2007 6:13PM=-=>
I definitely see the value of this. Thanks for proposing it. We’ll try to squeeze it in to SQL Server 2008 but things are really tight in terms of room for changes like this. It has to compete with many other things, including a bunch that have a larger impact on query performance, or that don’t have an easy workaround. This issue has a workaround, though it is not pretty and programmability would be enhanced a lot with the proposed enhancement. I’ll see what I can do.

Best regards,
Eric
<=-=Oct 17 2007 2:06PM=-=>
Things do not look good for this enhancement for Katmai. It probably will not make it into the release. We’ll make a final assessment in a couple of weeks. Before we can consider this,…
SQL Agent Job Chaining

It would be a great feature to make SQL Agent Jobs hierarchical so that one (or more) job(s) can be dependent on another job allowing SQL Agent Jobs to be chained together.

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

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

Upvotes: 1
Search box in SSMS Object Explorer

Please add a search box in SSMS Object Explorer similar to what Visual Studio has in the Solution Explorer! It should include autocomplete and the robust filtering abilities (by database, by object type, by schema)

It is extremely slow to constantly have to navigate through the tree to find the objects you want. I just want to type where I want to go.

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

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

Object Explorer doesn’t populate the entire tree in memory, unlike visual studio’s solution explorer. It wouldn’t know if database “MyDatabase” existed until you expanded the Databases node.
There are third party plugins that provide this type of functionality, I believe, by downloading a bunch of data in the background and exposing a search.
Add support for ANSI standard row value constructors

The ANSI standards for SQL define a concept of row value constructors. These make it possible to write, for instance,

WHERE (col1, col2) NOT IN (SELECT col1, col2 FROM SomeOtherTable)

SQL Server does not currently support this constructions

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

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

Upvotes: 176
<=-=Nov 13 2007 12:37AM=-=>
Hello

Thank you for your feedback. We’re certainly considering row value constructors for a future release of SQL Server.

- Sara Tahir
Microsoft SQL Server
<=-=Aug 11 2010 8:03AM=-=>
I think row constructors would be a great and important addition to T-SQL. Just wanted to point out a few more cases that I’d love to see implemented:

- Assignment
-—————————————————————————————————-

UPDATE dbo.T1
SET (c1, c2, c3) = (@p1, @p2, @p3)
WHERE keycol = @key;

— Logically equivalent to:

UPDATE dbo.T1
SET c1 = @p1,
c2 = @p2,
c3 = @p3
WHERE keycol = @key;

— Or with a subquery:

UPDATE dbo.T1
SET (c1, c2, c3) = (SELECT T2.c1, T2.c2, T2.c3
FROM T2
WHERE T2.keycol = T1.keycol)
WHERE keycol = @key;

— Logically equivalent to:
UPDATE dbo.T1
SET c1 = (SELECT T2.c1
FROM T2
WHERE T2.keycol = T1.keycol),
c2 = (SELECT T2.c2
FROM T2
WHERE T2.keycol = T1.keycol),
c3 = (SELECT T2.c3
FROM T2
WHERE T2.keycol = T1.keycol)
WHERE keycol = @key;

- Comparison Predicates
-—————————————————————————————————-

— The operators: AND follow the rules for Equality operations in the standard.

— equals operator:

SELECT keycol, c1, c2, c3
FROM dbo.T1
WHERE (c1, c2, c3) = (@p1, @p2, @p3);

— Logically equivalent to:

SELECT keycol, c1, c2, c3
FROM dbo.T1
WHERE c1 = @p1
AND c2 = @p2
AND c3 = @p3;

- Consider NULL comparison as TRUE
- For details see: https://connect.microsoft.com/SQLServer/feedback/details/286422/add-language-and-optimizer-support-for-iso-distinct-predicate?wa=wsignin1.0
WHERE (c1, c2, c3) IS NOT DISTINCT FROM (@p1, @p2, @p3);

— With composite joins:

SELECT T1.keycol AS T1_key, T2.keycol AS T2_key
FROM dbo.T1 JOIN dbo.T2
ON (T1.c1, T1.c2, T1.c3) = (T2.c1, T2.c2, T2.c3);

— not equals operator:

SELECT keycol, c1, c2, c3
FROM dbo.T1
WHERE (c1, c2, c3) <> (@p1, @p2, @p3);

— Consider NULL vs. non-NULL comparison as TRUE
WHERE (c1, c2, c3) IS DISTINCT FROM (@p1, @p2, @p3);

— Logically equivalent to:

SELECT keycol, c1, c2, c3
FROM dbo.T1
WHERE c1 <> @p1
OR c2 <> @p2
OR c3 <> @p3;

— The operators: , , AND follow the rules for Ordering operations in the standard.

— less than operator:

SELECT keycol, c1, c2, c3
FROM dbo.T1
WHERE (c1, c2, c3) < (@p1, @p2, @p3);

— Logically equivalent to:

SELECT keycol, c1, c2, c3
FROM dbo.T1
WHERE (c1 < @p1)
OR (c1 = @p1 AND c2 < @p2)
OR (c1 = @p1 AND c2 = @p2 AND c3 < @p3);

— greater than operator:

SELECT keycol, c1, c2, c3
FROM dbo.T1
WHERE (c1, c2, c3) > (@p1, @p2, @p3);

— Logically equivalent to:

SELECT keycol, c1, c2, c3
FROM dbo.T1
WHERE (c1 > @p1)
OR (c1 = @p1 AND c2 > @p2)
OR (c1 = @p1 AND c2 = @p2 AND c3 > @p3);

— less than or equals operator:

SELECT keycol, c1, c2, c3
FROM dbo.T1
WHERE (c1, c2, c3) <= (@p1, @p2, @p3);

— Logically equivalent to:
SELECT keycol, c1, c2, c3
FROM dbo.T1
WHERE (c1 = @p1 AND c2 = @p2 AND c3 <= @p3)
OR (c1 = @p1 AND c2 < @p2)
OR (c1 < @p1);

— greater than or equals operator:

SELECT keycol, c1, c2, c3
FROM dbo.T1
WHERE (c1, c2, c3) >= (@p1, @p2, @p3);

— Logically equivalent to:

SELECT keycol, c1, c2, c3
FROM dbo.T1
WHERE (c1 = @p1 AND c2 = @p2 AND c3 >= @p3)
OR (c1 = @p1 AND c2 > @p2)
OR (c1 > @p1);

- IN AND BETWEEN Predicates
-—————————————————————————————————-

— IN:

SELECT keycol, c1, c2, c3
FROM dbo.T1
WHERE (c1, c2, c3) IN (SELECT c1, c2, c3
FROM T2);

— if the subquery returns the rows R1, R2, �, Rn, the above query is then equivalent to:

SELECT keycol, c1, c2, c3
FROM dbo.T1
WHERE (c1, c2, c3) = R1
OR (c1, c2, c3) = R2
OR …
OR (c1, c2, c3) = Rn;

— BETWEEN:

SELECT keycol, c1, c2, c3
FROM dbo.T1
WHERE (c1, c2, c3) BETWEEN (pl1, @pl2, @pl3) AND (pr1, @pr2, @pr3) ;

— Logically equivalent to:

SELECT keycol, c1, c2, c3
FROM dbo.T1
WHERE (c1, c2, c3) >= (pl1, @pl2, @pl3) AND (c1, c2, c3) <= (pr1, @pr2, @pr3) ;

- MIN/MAX Aggregates
-—————————————————————————————————-

SELECT grpcol, MAX
FROM dbo.T1
GROUP BY grp;

— Logically equivalent to:

WITH C AS
(
SELECT grpcol, c1, c2, c3,
ROW_NUMBER() OVER AS rownum
FROM dbo.T1
)
SELECT grpcol, c1, c2, c3
FROM C
WHERE rownum = 1;

— with explicit ROW keyword
SELECT grpcol, MAX)
FROM dbo.T1
GROUP BY grpcol;
<=-=Aug 11 2010 8:23AM=-=>
I like Itzik’s ideas, all but the MIN/MAX construct.

To me MIN would behave similar to COALESCE in that it perhaps aggregates c1 & c2 & c3, then chooses the min result of the three.

I’m also not sure how column aliases would work when row constructs are used in the SELECT statement.
<=-=Aug 11 2010 9:15AM=-=>
There are platforms that support scalar MIN/MAX-like aggregates called LEAST/GREATEST. Actually, it would be nice to see those in SQL Server too. But I think that the potential confusion can be resolved with an explicit use of the ROW keyword: MAX). As for aliasing, normally the construct would return the original column name, but could be nice to see such a feature allows assigning our own aliases. Something like: MAX) AS (myc1, myc2, myc3).
<=-=Jan 1 2011 6:30PM=-=>
There is a bit of a clunky way currently in which aggregates can be used across columns.

SELECT [name]
,[number]
,(SELECT MAX FROM (SELECT low AS n UNION ALL SELECT high) d) n
FROM [master].[dbo].[spt_values]
<=-=Mar 12 2012 8:51AM=-=>
Is this still a feature under consideration? It’s been 4.5 years since this request was created, and there are duplicates of this request too.
<=-=Nov 8 2012 11:34AM=-=>
Having used these in ORACLE over EIGHT YEARS AGO it’s stunning that this has been on the very distant back-burner for OVER FIVE YEARS.

Would Microsoft care to update loyal community members on the status of this request, please?

(So many highly useful T-SQL functions could have been added at the expense of ruining SSMS in SQL2012 – just observing…)
<=-=Jan 4 2013 8:17AM=-=>
Any idea if we will get this in SQL 2012 or any of the SP of that?
<=-=Feb 20 2013 9:14PM=-=>
Can we have an update on this suggestion please?

Echo Phil Brammer, really like Itzik’s suggestions below. Although rather than messing with the well established MIN/MAX aggregate functions, I would suggest adding a new functions for evaluating the MIN or MAX over a value list (not set) e.g. MIN_VAL(val1, val2, val3), whereas MIN would work as per Itzik’s suggestion.
<=-=Apr 2 2015 2:25PM=-=>
What is the status of this suggestion?

Upvotes: 176
<=-=Nov 13 2007 12:37AM=-=>
Hello

Thank you for your feedback. We’re certainly considering row value constructors for a future release of SQL Server.

- Sara Tahir
Microsoft SQL Server
<=-=Aug 11 2010 8:03AM=-=>
I think row constructors would be a great and important addition to T-SQL. Just wanted to point out a few more cases that I’d love to see implemented:

- Assignment
-—————————————————————————————————-

UPDATE dbo.T1
SET (c1, c2, c3) = (@p1, @p2, @p3)
WHERE keycol = @key;

— Logically equivalent to:

UPDATE dbo.T1
SET c1 = @p1,
c2 = @p2,
c3 = @p3
WHERE keycol = @key;

— Or with a subquery:

UPDATE dbo.T1
SET (c1, c2, c3) = (SELECT T2.c1, T2.c2, T2.c3
FROM T2
WHERE T2.keycol = T1.keycol)
WHERE keycol = @key;

— Logically equivalent to:
UPDATE dbo.T1
SET c1 = (SELECT T2.c1
FROM T2
WHERE T2.keycol = T1.keycol),
c2 =…
Cardinality Estimator - Specify Execution hints at object definition and/or execution level

Today we can set execution context at different levels. In particular decide which Cardinal Estimator (CE) version to use to execute our workload.
At Instance , session , database and/or query levels by using trace flags, database scoped configuration and/or query hints.
There are cases where some workload runs better using the legacy CE and others with the current (or most recent) CE

I believe it will be extremely useful to be able to specify execution content at object level (procedures and functions) so if the object is defined to use a particular CE, the execution plan will be generated using that one no matter wich content is defined at other levels

CREATE PROCEDURE dbo.MyPROC
(@a int
OPTION(USE HINT ('FORCELEGACYCARDINALITYESTIMATION'))
)
AS
SELECT * FROM dbo.MyTABLE A
INNER JOIN dbo.MYTVF(1000) X on A.id=X.id
GO

CREATE FUNCTION dbo.MYTVF(@a int NULL)
RETURN TABLE
OPTION(USE HINT ('FORCELEGACYCARDINALITYESTIMATION'))
AS
SELECT * FROM dbo.MyTABLE1 WHERE SSI=@a
GO

Another option could be forcing the CE level when executing a stored procedure

EXEC dbo.MyPROC @A=1 OPTION(USE HINT ('FORCELEGACYCARDINALITYESTIMATION'));

The hints could be 'FORCELEGACYCARDINALITYESTIMATION' or 'QUERYOPTIMIZERCOMPATIBILITYLEVEL_n'

Today we can set execution context at different levels. In particular decide which Cardinal Estimator (CE) version to use to execute our workload.
At Instance , session , database and/or query levels by using trace flags, database scoped configuration and/or query hints.
There are cases where some workload runs better using the legacy CE and others with the current (or most recent) CE

I believe it will be extremely useful to be able to specify execution content at object level (procedures and functions) so if the object is defined to use a particular CE, the execution plan will be generated…

102 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

18 comments · Suggestions · Flag idea as inappropriate… · Delete… · Admin →
Add language support for null treatment clause (RESPECT NULLS | IGNORE NULLS) with offset window functions (LAG, LEAD, FIRST_VALUE
There�s a common need with offset window functions (LAG, LEAD, FIRSTVALUE, LASTVALUE) to request to ignore NULLs. A classic example is to return the last non-NULL in an ordered stream of values. Consider the following sample data:

DROP TABLE IF EXISTS dbo.T1;

CREATE TABLE dbo.T1
(
id INT NOT NULL CONSTRAINT PK_T1 PRIMARY KEY,
col1 INT NULL
);

INSERT INTO dbo.T1(id, col1) VALUES
( 2, NULL),
( 3, 10),
( 5, -1),
( 7, NULL),
(11, NULL),
(13, -12),
(17, NULL),
(19, NULL),
(23, 1759);

The id column represents the order of the events, and whenever the col1 value is non-NULL, this represents a value change, and from that point, the new value is considered the applicable one. You want to return the last non-NULL col1 value based on id ordering. For this sample data, the desired result is the following:

id col1 lastnonnull

2 NULL NULL
3 10 10
5 -1 -1
7 NULL -1
11 NULL -1
13 -12 -12
17 NULL -12
19 NULL -12
23 1759 1759

The SQL standard supports a <null treatment> clause with offset window functions, allowing you to specify RESPECT NULLS (default) | IGNORE NULLS. With this clause, the above task would be achieved like so:

SELECT id, col1,
LAG(col1) IGNORE NULLS OVER(ORDER BY id) AS lastnonnull
FROM dbo.T1;

Instead, currently we need to resort to more complex solutions such as this one:

WITH C AS
(
SELECT id, col1,

MAX(CASE WHEN col1 IS NOT NULL THEN id END) OVER(ORDER BY id ROWS UNBOUNDED PRECEDING) AS grp

FROM dbo.T1
)
SELECT id, col1,
MAX(col1) OVER(PARTITION BY grp

ORDER BY id ROWS UNBOUNDED PRECEDING) AS lastnonnull

FROM C;

Thanks much for your consideration!

Cheers,
Itzik
There�s a common need with offset window functions (LAG, LEAD, FIRSTVALUE, LASTVALUE) to request to ignore NULLs. A classic example is to return the last non-NULL in an ordered stream of values. Consider the following sample data:

DROP TABLE IF EXISTS dbo.T1;

CREATE TABLE dbo.T1
(
id INT NOT NULL CONSTRAINT PK_T1 PRIMARY KEY,
col1 INT NULL
);

INSERT INTO dbo.T1(id, col1) VALUES
( 2, NULL),
( 3, 10),
( 5, -1),
( 7, NULL),
(11, NULL),
(13, -12),
(17, NULL),
(19, NULL),
(23, 1759);

The id column represents the order of the events, and whenever the col1…
90 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

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

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

Upvotes: 60
<=-=Mar 29 2017 11:32PM=-=>
Just wanted to add that both Oracle and DB2 seem to support this feature:

http://docs.oracle.com/database/122/SQLRF/LAG.htm#SQLRF00652
https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_sqs_1513.htm

Cheers,
Itzik
<=-=Apr 16 2017 10:08PM=-=>
That last IBM link was for Informix!

The DB2 link is https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0023461.html
<=-=Jul 4 2017 3:30AM=-=>
Thanks for reporting this request.
It is in our backlog, and we have planned to do something like this, but we cannot confirm when it would be completed.

Jovan
<=-=Jul 10 2017 11:54AM=-=>
Thanks, Jovan; good to hear.
AlwaysOn Availability Groups - Handling of Instance Level Objects

In todays implementation when using AlwaysOn Availability Groups the DBA has to take care of the so called Instance Level Objects.
Without this an (automatic) failover will fail so this is a very sensitive and important task.
Instance Level Objects span a multitude of objects like logins, SSIS packages or other binaries, Agent Jobs/Schedules, Linked Servers, encryption keys and certificates.

The recommendation provided by you is to create automated jobs to sync these items regularly.

I request that you provide these 'syncing' functionality as part of the product out-of-the-box.
Clearly you cannot know 3rd party dependencies nevertheless there are a log of items which are within your control.

Be aware that this is the core issue for people in my organization NOT to use AOAG and instead look and AO FCI.
If you want to extent the usage of AOAG (at least in my organization) then this would be the core lever.
("Make handling of instance level objects in AOAG as easy as in FCI.")

In todays implementation when using AlwaysOn Availability Groups the DBA has to take care of the so called Instance Level Objects.
Without this an (automatic) failover will fail so this is a very sensitive and important task.
Instance Level Objects span a multitude of objects like logins, SSIS packages or other binaries, Agent Jobs/Schedules, Linked Servers, encryption keys and certificates.

The recommendation provided by you is to create automated jobs to sync these items regularly.

I request that you provide these 'syncing' functionality as part of the product out-of-the-box.
Clearly you cannot know 3rd party dependencies nevertheless there are a…

85 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

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

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

Upvotes: 18
Allow SQL databases to exist in a resource group different than the SQL Server

The idea here is that I would like to have multiple databases in different resource groups, but one server to hold them all. The server would be in its own resource group.

This is possible with App Service and App Service Plan.

83 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

1 comment · Suggestions · Flag idea as inappropriate… · Delete… · Admin →
HTTP Request Function
I have a scalar CLR function I created that allows me to make HTTP Requests and return the response. It would be awesome if SQL Server had a function built in that could do it.

This has proven to be super helpful time and time again with the biggest use case being querying web APIs directly from the database without having to involve any other programming language or integration tools to load the data into a database before being able to query it.
Often these APIs provide data back in either JSON or XML and SQL Server already has a phenomenal feature set to work with data like that.
Especially in a rapid development setting trying to explore APIs to understand the data available this is an invaluable tool.

A scalar function that accepts at least the below parameters would be super awesome.
- Method (GET/POST/etc.)
- URL
- Parameters (some way to provide key/value pairs... My CLR function mentioned above accepts an XML string)
- Headers (pretty much same has Parameters)
- Timeout

Brainstorming:
Some convenient way to handle cookies would be cool.
Maybe a way to just pass a very raw HTTP request would be helpful too.
A convenient way to parse the response to get HTTP status code (200/404/500/etc.), cookies, body, etc. would be amazing also.

Quick usage example:
SELECT

[id], [name], [field]

FROM OPENJSON(HTTP_REQUEST('GET', 'https://someurl', '{"date":"2018-06-01","rows":"500"}', '{"Authorization":"Basic APIKEY"}', 300) WITH

( [id] INT, [name] VARCHAR(50), [field] VARCHAR(200) );
I have a scalar CLR function I created that allows me to make HTTP Requests and return the response. It would be awesome if SQL Server had a function built in that could do it.

This has proven to be super helpful time and time again with the biggest use case being querying web APIs directly from the database without having to involve any other programming language or integration tools to load the data into a database before being able to query it.
Often these APIs provide data back in either JSON or XML and SQL Server already has a…
72 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

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

← Previous 1 2 3 4 5 … 210 211 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 a built-in table of numbers

Change limit/behaviour of msdb.dbo.suspect_pages table

T-SQL Common Table Expression "Materialize" Option

In-Memory OLTP - allow removal of Filegroup for memory_optimized_data

Option to specify a location for the DBCC CHECKDB snapshot

Provide XEvents target directly into SQL Server table

Bring Back TFS and Git in SSMS

Add support for standard nested window functions in T-SQL

SET STATISTICS IO should also show schema name for tables

Optimise for Analytics configuration option

Porting SMO Library to .NET Core

Add language and optimizer support for ISO <distinct predicate>

SQL Agent Job Chaining

Search box in SSMS Object Explorer

Add support for ANSI standard row value constructors

Cardinality Estimator - Specify Execution hints at object definition and/or execution level

Add language support for null treatment clause (RESPECT NULLS | IGNORE NULLS) with offset window functions (LAG, LEAD, FIRST_VALUE

AlwaysOn Availability Groups - Handling of Instance Level Objects

Allow SQL databases to exist in a resource group different than the SQL Server

HTTP Request Function

Feedback

SQL Server

Feedback and Knowledge Base

Searching…

Give feedback

Microsoft Azure

Your password has been reset

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

SQL Server

Categories

Searching…

Your password has been reset