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.

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!

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

120 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 →
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 →
In-Memory OLTP - allow removal of Filegroup for memory_optimized_data

It should be possible to remove the memory_optimized_data 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.

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

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

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

under review · 6 comments · Suggestions · Flag idea as inappropriate… · Delete… · Admin →
SQL Server vNext (post 2019) and NoSQL functionality

Hello,

This is an invitation for discussion to the SQL Server engine team and the community.

IMHO, SQL Server vNext (post 2019) should beef up its NoSQL functionality.
Specifically, its XML APIs.
This area is dormant since 2005/2008 release.

SQL Server currently supports the following:
- a subset of XQuery 1.0, circa 2007
- a subset of XPath 2.0, circa 2007
- XSD 1.0 circa 2001

The current w3c standards:
- XQuery 3.1
- XPath 3.1
- XSD 1.1 circa 2012

Everything is in place, even current support for the UTF-8 encoding in 2019.

Famous Obamacare is implemented by using MarkLogic as its DB, since the initial relational Oracle DB implementation failed the mission.

Just imagine if it had been MS SQL Server.
SQL Server is ready to move beyond pure relational DB.

Hello,

This is an invitation for discussion to the SQL Server engine team and the community.

IMHO, SQL Server vNext (post 2019) should beef up its NoSQL functionality.
Specifically, its XML APIs.
This area is dormant since 2005/2008 release.

SQL Server currently supports the following:
- a subset of XQuery 1.0, circa 2007
- a subset of XPath 2.0, circa 2007
- XSD 1.0 circa 2001

The current w3c standards:
- XQuery 3.1
- XPath 3.1
- XSD 1.1 circa 2012

Everything is in place, even current support for the UTF-8 encoding in 2019.

Famous Obamacare is implemented by using…

108 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

15 comments · Suggestions · Flag idea as inappropriate… · Delete… · Admin →
Add support for standard nested window functions in T-SQL

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

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

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

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

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

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

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

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

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

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

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

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

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

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

106 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 →
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,…

105 votes

Sign in
prestine

(thinking…)

Sign in with: Microsoft

Forgot password?

Create a password

I agree to the terms of service

Signed in as (Sign out)

Close

Close

We’ll send you updates on this idea

under review · 2 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…

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

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

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

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

88 votes

Sign in
prestine

(thinking…)

Sign in with: Microsoft

Forgot password?

Create a password

I agree to the terms of service

Signed in as (Sign out)

Close

Close

We’ll send you updates on this idea

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

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

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

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

3 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 =…
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 ANSI_NULLS is not a solution, because it does not affect column-to-column comparisons, only column to variable comparisons. Setting ANSI_NULLS 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 …

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

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

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

78 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
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, FIRST_VALUE, LAST_VALUE) 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, FIRST_VALUE, LAST_VALUE) 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…

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 →

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

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

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

Upvotes: 1
In SQL Server Always oafter a failover, SQL Server keep the connections open without killing any session

Hi Msft Team!
I want to suggest one improvement to your sql server always on feature. Normally, once we perform failovers we need to ask app teams for maintenance windows... or in worst cases ,if this happens automatically, we need to discover the RCA of this because all the application or users connections had been terminated. Also many times that we had automatic failovers or perform failovers with long executions happening the secondary instance turned into recovery state because it was waiting for this session to finish to synchronize again. So, is it possible to add the capability for failovers to keep the session opened? let's say that you are pointing to the listener and independently where you are your connection will not finish after a failover or if you are executing a process on the primary and you have a failover, this process will continue on the other replica as well. I hope this helps! Regards!
Hi Msft Team!
I want to suggest one improvement to your sql server always on feature. Normally, once we perform failovers we need to ask app teams for maintenance windows... or in worst cases ,if this happens automatically, we need to discover the RCA of this because all the application or users connections had been terminated. Also many times that we had automatic failovers or perform failovers with long executions happening the secondary instance turned into recovery state because it was waiting for this session to finish to synchronize again. So, is it possible to add the capability for failovers…

67 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 · 8 comments · Suggestions · Flag idea as inappropriate… · Delete… · Admin →
"Call Stack"

Provide DMV to provide the call stack of stored procedures, triggers, CLR functions etc prefferable in JSON format so it is parsable.

As Erland Sommarskog says this is available but only in the "event Blocked Process Report:

<executionStack>
<frame line="17" stmtstart="1332" stmtend="2906" sqlhandle="0x03000d00399f00558c840601a29800000100000000000000"/>
<frame line="1" sqlhandle="0x01000d00638e300f302e10ac000000000000000000000000"/>
</executionStack>

That is, here the call stack is available. Through the sqlhandle it is possible to retrieve
the objectids of the calling procedures.

If if it's available this way, it can't be too difficult to expose this in a DMV. Only exposing the
sqlhandle would not match with my observation with permissions above, but since you probably
only want this code in a few places, this could be dealt with adding extra privs with
certificate signing."

This is already requested in connect items 124537 and 2781373.

Provide DMV to provide the call stack of stored procedures, triggers, CLR functions etc prefferable in JSON format so it is parsable.

As Erland Sommarskog says this is available but only in the "event Blocked Process Report:

<executionStack>
<frame line="17" stmtstart="1332" stmtend="2906" sqlhandle="0x03000d00399f00558c840601a29800000100000000000000"/>
<frame line="1" sqlhandle="0x01000d00638e300f302e10ac000000000000000000000000"/>
</executionStack>

That is, here the call stack is available. Through the sqlhandle it is possible to retrieve
the objectids of the calling procedures.

If if it's available this way, it can't be too difficult to expose this in a DMV. Only exposing the
sqlhandle would not match with my observation with permissions above, but…

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

57 votes

Sign in
prestine

(thinking…)

Sign in with: Microsoft

Forgot password?

Create a password

I agree to the terms of service

Signed in as (Sign out)

Close

Close

We’ll send you updates on this idea

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

← Previous 1 2 3 4 5 … 203 204 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!

adomd core

Option to specify a location for the DBCC CHECKDB snapshot

Optimise for Analytics configuration option

In-Memory OLTP - allow removal of Filegroup for memory_optimized_data

T-SQL Common Table Expression "Materialize" Option

SQL Server vNext (post 2019) and NoSQL functionality

Add support for standard nested window functions in T-SQL

SET STATISTICS IO should also show schema name for tables

Porting SMO Library to .NET Core

Search box in SSMS Object Explorer

SQL Agent Job Chaining

Add support for ANSI standard row value constructors

Add language and optimizer support for ISO <distinct predicate>

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

AlwaysOn Availability Groups - Handling of Instance Level Objects

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

Bring Back TFS and Git in SSMS

In SQL Server Always oafter a failover, SQL Server keep the connections open without killing any session

"Call Stack"

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