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 Microsoft Q&A or Stack Overflow

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.

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…

199 votes

We're glad you're here
Please sign in to leave feedback

Signed in as (Sign out)

Close

Close

We’ll send you updates on this idea

under review · 20 comments · Suggestions · Flag idea as inappropriate… · Delete… · Admin →
adomd core

ADOMD.NET Client Needs .NET Core Support

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

187 votes

We're glad you're here
Please sign in to leave feedback

Signed in as (Sign out)

Close

Close

We’ll send you updates on this idea

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

178 votes

We're glad you're here
Please sign in to leave feedback

Signed in as (Sign out)

Close

Close

We’ll send you updates on this idea

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

177 votes

We're glad you're here
Please sign in to leave feedback

Signed in as (Sign out)

Close

Close

We’ll send you updates on this idea

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

We're glad you're here
Please sign in to leave feedback

Signed in as (Sign out)

Close

Close

We’ll send you updates on this idea

0 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:
• 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 ROWNUMBER(FRAMEROW) NOT IN ( ROWNUMBER(BEGINPARTITION), ROWNUMBER(ENDPARTITION) ) 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…

152 votes

We're glad you're here
Please sign in to leave feedback

Signed in as (Sign out)

Close

Close

We’ll send you updates on this idea

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

151 votes

We're glad you're here
Please sign in to leave feedback

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

143 votes

We're glad you're here
Please sign in to leave feedback

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…

124 votes

We're glad you're here
Please sign in to leave feedback

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

124 votes

We're glad you're here
Please sign in to leave feedback

Signed in as (Sign out)

Close

Close

We’ll send you updates on this idea

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

124 votes

We're glad you're here
Please sign in to leave feedback

Signed in as (Sign out)

Close

Close

We’ll send you updates on this idea

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

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

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

121 votes

We're glad you're here
Please sign in to leave feedback

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

119 votes

We're glad you're here
Please sign in to leave feedback

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

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

117 votes

We're glad you're here
Please sign in to leave feedback

Signed in as (Sign out)

Close

Close

We’ll send you updates on this idea

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

116 votes

We're glad you're here
Please sign in to leave feedback

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

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 execution plan warning for hard-coded estimations

When query plans have hard-coded estimates, like the 30% hard-coded estimate for < or > when adding multiple columns:

SELECT * FROM dbo.Users u WHERE u.UpVotes + u.DownVotes > 10000000;

Then the query plan operator that does that filtering should show a yellow bang. The estimates on that operator will be 30% of the number of rows in the table, regardless of whether the query uses a greater than, less than, and regardless of which number we put in the filter.

More info & demos: https://www.brentozar.com/go/nobangs

112 votes

We're glad you're here
Please sign in to leave feedback

Signed in as (Sign out)

Close

Close

We’ll send you updates on this idea

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

105 votes

We're glad you're here
Please sign in to leave feedback

Signed in as (Sign out)

Close

Close

We’ll send you updates on this idea

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

96 votes

We're glad you're here
Please sign in to leave feedback

Signed in as (Sign out)

Close

Close

We’ll send you updates on this idea

2 comments · 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…

93 votes

We're glad you're here
Please sign in to leave feedback

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
New default value for Cost Threshold for Parallelism

The default value for Cost Threshold for Parallelism has remained the same since the release of SQL Server 7 back in the late 90s. Apparently this is based on the estimated query run times on one of the SQL Server devs team's desktop PC. Anyway hardware has moved on since the late 90s but the default setting hasn't - this leads to too many queries being considered for parallel plans with associated problems - higher compile costs, high CPU usage. Lots of people in the community writing about it - but no connect item yet - so here it is :-)

http://sqlblog.com/blogs/joe_chang/archive/2017/02/20/fixing-the-query-optimizer-cost-model.aspx
https://www.brentozar.com/archive/2017/03/why-cost-threshold-for-parallelism-shouldnt-be-set-to-5/
http://www.scarydba.com/2017/02/28/determining-the-cost-threshold-for-parallelism/
http://www.scarydba.com/2017/03/13/change-the-cost-threshold-for-parallelism/

The default value for Cost Threshold for Parallelism has remained the same since the release of SQL Server 7 back in the late 90s. Apparently this is based on the estimated query run times on one of the SQL Server devs team's desktop PC. Anyway hardware has moved on since the late 90s but the default setting hasn't - this leads to too many queries being considered for parallel plans with associated problems - higher compile costs, high CPU usage. Lots of people in the community writing about it - but no connect item yet - so here it is…

93 votes

We're glad you're here
Please sign in to leave feedback

Signed in as (Sign out)

Close

Close

We’ll send you updates on this idea

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

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

Upvotes: 38
<=-=Mar 16 2017 11:48AM=-=>
Or better yet, include an option to automatically adjust it based on wait stats.
<=-=Mar 17 2017 12:34AM=-=>
Or implement adaptive query plans that will adjust the parallelism dynamically based �n query execution stats
<=-=Mar 17 2017 10:56AM=-=>
Thanks for the feedback. We�ll be keeping this item open for future consideration.

Thanks,
shreya

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

T-SQL Common Table Expression "Materialize" Option

adomd core

Change limit/behaviour of msdb.dbo.suspect_pages table

In-Memory OLTP - allow removal of Filegroup for memory_optimized_data

Option to specify a location for the DBCC CHECKDB snapshot

Add support for standard nested window functions in T-SQL

Provide XEvents target directly into SQL Server table

SET STATISTICS IO should also show schema name for tables

Porting SMO Library to .NET Core

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

SQL Agent Job Chaining

Add language and optimizer support for ISO <distinct predicate>

Search box in SSMS Object Explorer

Optimise for Analytics configuration option

Add support for ANSI standard row value constructors

Add execution plan warning for hard-coded estimations

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

HTTP Request Function

AlwaysOn Availability Groups - Handling of Instance Level Objects

New default value for Cost Threshold for Parallelism

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!

We're glad you're here

We're glad you're here

We're glad you're here

We're glad you're here

We're glad you're here

We're glad you're here

We're glad you're here

We're glad you're here

We're glad you're here

We're glad you're here

We're glad you're here

We're glad you're here

We're glad you're here

We're glad you're here

We're glad you're here

We're glad you're here

We're glad you're here

We're glad you're here

We're glad you're here

We're glad you're here

We're glad you're here

We're glad you're here

SQL Server

Categories

Searching…

Your password has been reset