An SQL text by Erland Sommarskog, SQL Server MVP. Most recent version 2010-02-11.
As you may imagine, my wishlist for new features in the next version of SQL Server, exceeds what most kids send to Santa Claus. This article is a collection of suggestions filed on Microsoft Connect that I think Microsoft should implement in the next version of SQL Server. And while there are over 40 Connect items included in this wishlist, this is not all my wishes; to keep a focus, I have constrained this list to items that is related to general development. That is, new constructs for querying and programming and database design. This is my main arena in SQL Server and I think this was an area that was disfavoured in SQL 2008. We good some goodies with the new date/time data types, the MERGE statement, filtered indexes and the table-valued parameters that Microsoft left half-baked. But MS definitely had the focus elsewhere.
Many of these suggestions are my own, but there are also items submitted by my fellow MVPs as well as suggestions from other users. In case you don't know, you can also submit bugs and suggestions on Microsoft Connect, all you need is a Windows Live ID. You can also go to Connect to vote on other people's suggestions, and the chief reason that I publish this article is indeed to whip up more votes for good proposals. And, yes, Microsoft does care about these votes.
If you have an item on Connect that you think warrants better exposure, feel free to contact me. If I like your suggestion (and it is in the development realm), I will add it to the article.
I have divided the wishlist into three parts: Querying, Programming and DDL (Database Definition Language). However, I have put two items that I think are the most important in sections of their own first in the article. Here is a table of contents:
SET STRICT_CHECKS ON – Checks for More Robust Development
Completing the OVER Clause
Improvements for Querying
A Built-in Table of Numbers
Is this Convert Valid?
IS [NOT] DISTINCT FROM
Type the Literal!
Make YYYY-MM-DD Safe
Row Constructors
QUALIFY for concise queries
Remove Illogical Restrictions to
Regular Expressions
Dynamic Pivot
Improvements for Programming
Make Table Parameters Read-Write
Module-Level Table Expressions
Inline Scalar UDFs
Enums in
Get the Call Stack!
SET TRANSACTION ISOLATION LEVEL READ COMMITTED LOCK
Autonomous Transactions
Improvements for Bulk Load
Get Position within an XML Document
TRUNCATE TABLE and Foreign Keys
Really Small Things
DDL Improvements
Server-Side CLR Compiles
Give us Real Domains!
Immutable Column Values
Support for UTF-8
Synonyms for Databases and Linked Servers
BEFORE triggers
ALTER TABLE Improvements
Revision History
Face it, SQL Server as a programming environment is extremely poor. Many stupid errors like misspelling a table name are not caught until run-time. This has a serious impact on the development cost for SQL Server applications. I have therefore for submitted Connect item 260762, entitled Add optional checks for more robust development where I suggest a new command SET STRICT_CHECKS ON. When strict checks are in force, you would get compile-time errors for a lot of things that does not blow up until run-time today – or never produce any error at all, just incorrect results.
To further help Microsoft to get this right, I have a separate article on my web site, Ideas for SET STRICT_CHECKS ON, where I discuss what could be included in this command. Here is a brief summary, with direct links to the various sections in that article:
If you have further ideas of what could be included, or ideas on what I already have in there, feel free to drop me a line.
One of the absolutely most valuable additions in SQL Server 2005 was the analytical functions and the OVER clause. In ANSI SQL there is a lot more you can do with OVER. There are several common problems that can be solved efficiently with the full OVER clause (provided a proper implementation of course!). One typical example is a running sum, something which today is best implemented in a cursor, because the performance for a set-based query on a large data set is abysmal.
SQL Server MVP Iztik Ben-Gan has submitted eight Connect items for various enhancements to the OVER clause. The items refers back to a document that he written together with Sujata Metha, SQL Server Feature Enhancement Request – OVER Clause and Ordered Calculations (this is a Word document), and you are likely to need to read the document to understand the items. Here is a list of Itzik's items.
254387 – OVER clause enhancement request – ORDER BY for aggregates.
254388 – OVER clause enhancement request – LAG and LEAD functions.
254390 – OVER clause enhancement request – TOP OVER.
254391 – OVER clause enhancement request – Vector Expressions.
254392 – OVER clause enhancement request – ROWS and RANGE window sub-clauses.
254393 – OVER clause enhancement request – DISTINCT clause for aggregate functions.
254395 – OVER clause enhancement request – FIRST_VALUE, LAST_VALUE functions.
254397 – OVER clause enhancement request – Progressive ordered calculations.
The last item goes beyond what ANSI mandates; it suggests a function whereby you could retrieve the current value in the calculation. This idea origins from SQL Server MVP Marcello Poletti. The document Progressive calculation and Previous function in SQL Server (again a Word document), written by Marcello with some collaboration from me, discusses this idea in more detail.
There are quite a few SQL problems where you need a suite of numbers to generate a dimension. For instance: Show me sales per day. Days for which there were no sales at all should appear with a 0. To generate the days missing from the Sales table, you need a calendar table – or a table with numbers from 1 to as many days there are in the period. If you have read my article Arrays and Lists in SQL Server, you have seen several methods to unpack a list into a table that employ a table of numbers. You can easily generate such a table, but if you generate too few numbers, you queries will produce an incorrect result when you run out of numbers. Another issue is that the optimizer thinks this that is just another table, and does not understand its special properties, and therefore does not make a very good job with it.
On the other hand of there was such a built-in table (which of course would not be a physical table, but a logical concept), you would never run out of numbers, and the optimizer could make shortcuts for better performance. For this reason I have submitted Connect item 258733, Add a built-in table of numbers.
Often when you need to convert from one data type to another, you need to handle the case that the source is not convertible to the target type; typically in this case the source in a string. You may have found that you are caught between a rock and a hard place, because
often the only way to find out if something converts is to try to
convert it. But if some data does not convert, the entire query fails, and you have very little idea of what data that caused the query to fail. SQL Server offers
What we need is to be able to say things like
CASE is_valid_convert(int, somecol) WHEN 1 THEN convert(int, somecol) END
Connect item 354766, Add a is_valid_convert function.
Say that you want to compare the same tables in two databases with the same schema and find all rows for which there are differences. You need something like this:
SELECT ...
FROM db1.tbl a
JOIN db2.tbl b ON a.keycol = b.keycol
WHERE (a.col1 <> b.col1 OR
a.col1 IS NULL AND b.col1 IS NOT NULL OR
a.col1 IS NOT NULL AND b.col1 IS NULL)
AND ... -- The same expression for all other columns.
You need to write it this tediously, because the plain
SQL:1999 defines the operators IS DISTINCT FROM and IS NOT DISTINCT FROM which are the same as <> and = respectively, except that they also applies to NULL values. With this operator, the above becomes:
SELECT ... FROM db1.tbl a JOIN db2.tbl b ON a.keycol = b.keycol WHERE a.col1 IS DISTINCT FROM b.col1 OR a.col2 IS DISTINCT FROM b.col2 OR ...
This is captured in Connect item 286422, Add language and optimizer support for ISO <distinct predicate>, submitted by SQL Server MVP Steve Kass.
Today there are three base types of literals in
ANSI SQL suggests a path where you can prefix the literal with the type, for instance DATE '2008-09-21', a bit in analogy how we do it with Unicode literals today. SQL Server MVP Steve Kass has suggested this in Connect
item 356470, Support syntax for typed literal values.
Consider this:
set language German
go
select convert(datetime, '1989-06-12'),
convert(datetime2, '1989-06-12'),
convert(date, '1989-06-12')
The output is:
1989-12-06 00:00:00.000 1989-06-12 00:00:00.0000000 1989-06-12
SQL Server has always interpreted XXXX-YY-ZZ as Year-Day-Month when the date format setting is DMY (which German implies), a very unnatural interpretation. Microsoft fixed this for the new date/time data types in SQL 2008, but did not have the guts to change it for existing types, although SQL Server MVP Steve Kass suggested this in Connect item 290971, Deprecate the date literal interpretation 'YYYY-DD-MM' except in compatibility modes <= 90.
If you want your UPDATE statements to be ANSI-compliant and update several columns from the same source you need to write:
UPDATE tbl
SET col1 = (SELECT somecol FROM ...),
col2 = (SELECT someothercol FROM ...),
...
Not only is this tedious; since SQL Server is not able to recognize that the subqueries are the same, performance can suffer as well. In true ANSI SQL, this is not an issue, because you can use row constructors:
UPDATE tbl SET (col1, col2, ...) = (SELECT somecol, someothercol, ... FROM ...)
The regular workaround in SQL Server is to use the proprietary syntax with the FROM clause, and personally I prefer that syntax in most situations, but sometimes it's more natural to use a subquery.
Another situation where row constructors can be handy is for queries like:
SELECT ... FROM tbl WHERE (col1, col2, col3) IN (SELECT col1, col2, col3 FROM somewhereelse)
In this case, too, there is a good alternative, the EXISTS clause, and it's not even properitary. But again, sometimes it more natural to write it this way.
The most compelling case for row constructors is the SET statement:
SET (@a, @b, @c) = (SELECT a, b, c FROM ...)
The alternative today is to use the SELECT statement, but the behaviour when the SELECT hits anything but exactly one row, is not always intuitive. With the addition of row constructors, SET would become the prime alternative for assignment.
There are a whole slew of Connect items in this area. The one that have garnered most votes this far is item 127024, Support row constructors for conditional expressions, submitted by Ahsukal. Other items are 299231 (Hugo Kornelis), 262284 ("myudkin"), 127028 (Chris Hedgate) and 288772 (mimrsh).
The problem when you use the
FROM-JOIN-ON
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
Since the ranking is defined in the SELECT list, you cannot refer to it in WHERE, only in SELECT and ORDER BY. This also explains why you cannot use column aliases in WHERE clauses.
Teradata implements a fourth filtering clause beside ON, WHERE and HAVING, which is evaluated after SELECT and after ORDER BY. They call it QUALIFY. While QUALIFY does not bring any new query powers, it opens the possibility to make queries more concise queries, since the need to use nested queries is reduced. Therefore it would be a very welcome addition to SQL Server.
SQL Server MVP Itzik Ben-Gan has submitted QUALIFY - Request for a New Filtering Clause. His Connect item also includes a more detailed discussion on QUALIFY as well as examples.
When you have a query in
IF EXISTS (WITH tab AS (SELECT 1 AS col)
SELECT * FROM tab)
PRINT 'okiedokie'
ELSE
PRINT 'not ok';
or
IF EXISTS (SELECT * FROM tbl
OPTION (MAXDOP 1))
PRINT 'yes';
ELSE
PRINT 'no';
The workaround is certainly simple: run a SELECT that assigns the result into a variable, and the test on the variable. But nevertheless the inconsistency sticks out like a sore thumb. And, assume that you are in a stressful situation where you need to address a performance problem with an IF EXISTS query. You realise that the MAXDOP hint is what you need. Certainly you get far less nervous if you could only insert the query hint, rather than having to actually edit the code to move the query, add a variable and all that.
SQL Server MVPs Aaron Bertrand and Hugo Kornelis have submitted Connect items 270480, Query hints not allowed after or inside
If you have any experience of regular expressions from the Unix world, Perl or the RegEx classes in .Net, you probably don't find LIKE in SQL very powerful. On top of that it uses wildcard characters that are a bit, well, unusual. SQL:1999 has a more powerful operator SIMILAR TO, but it's still not in par with the real McCoy, and it's still uses the odd wildcard characters. Microsoft could implement SIMILAR TO while they are at it, but the prime importance is that we get something that draws on the Unix traditions to bring the full power of regular expressions to the SQL Server users.
I'm not including an example of how this would look like, because if you already know about regular expressions you don't need any examples to be convinced. And if you never have seen one, you would only be frightened. :-) Admittedly, regular expressions have a bit of a learning curve, and they are not always pretty. But they are extremely powerful.
SQL Server MVP Simon Sabin has submitted Connect item 261342, Regex functionality in pattern matching. If you look closer you see that there is a duplicate item. That duplicate is mine, as I did not find Simon's item first. Vote for Simon's, which already has garnered a couple of votes.
It's nothing I often – or ever – have reason to do myself, but I see the question regularly in the public newsgroups, so I don't need to be convinced that there is a business case. Essentially, what it is all about is that you want to make rows into columns for a report. And you can easily do this with or without the PIVOT operator, as long as you know which columns you want. But often the names and the number of columns are not known until run-time. Today you have to address this with dynamic SQL, which is not overly fun.
Admittedly, this is a bit tricky, because a regular SELECT statement is composable, that is, you can use it as input to an outer SELECT statement. That is of course not possible if the number of columns are not known until run-time. So the syntax would have to be something else, maybe TRANSFORM as the command reportedly is in Access, and you would not be able to do something else with your dynamic crosstab query than returning data to the client. Which nevertheless would be a huge leap forward.
I didn't have to submit my own item, as someone using the alias Pich submitted connect item 127071, PIVOT, long ago.
Table variables are a nice addition to SQL 2008, but since they are read-only they are not half as exciting as they could have been. During the beta of SQL 2008, I submitted a Connect item where I argued that Microsoft should lift this restriction for calls between stored procedures, but retain it for calls from client APIs. My thinking was that the problems that had caused MS to introduce the restriction lied in the API:s and the communication with them. Unfortunately, the request was not honoured in SQL 2008. But Connect item 299296, Relax restriction that table parameters must be readonly when SPs call each other. is still active.
I argue further for my position in the article Why Read-only Table Parameters is Not Enough. I've updated it a bit from the original version, and there is now a discussion on why output table parameters could be very useful to client API:s as well.
Some time back I found myself writing a stored procedure where I prepared data coming from an incoming source, which included a lot of validations and transformations. I had a bunch of UPDATE statements that went:
UPDATE mytable SET some_col = ... FROM mytable m JOIN ... WHERE m.fileid = @fileid AND m.status = 'New'
And it made me a bit nervous. What if I accidently left out the conditions on fileid or status and updated rows I did not want to touch? It occurred to me that it would have been really nice, if I had been able to say:
DECLARE @mynewrows AS
(SELECT *
FROM mytable
WHERE fileid = @fileid
AND status = 'New')
Then my UPDATE could be written as:
UPDATE @mynewrows SET some_col = ... FROM @mynewrows JOIN ...
Essentially, a Common Table Expression, but on module level. Note that a view would not cut it, due to the reference to the local variable. A inline-table function would work, but I would still have to repeat that @fileid again and again. And I would have to litter the system catalogue with something I need in one single stored procedure only.
That inspired me to Connect item 343067, Module-level table expressions.
If you are a programmer with a background in traditional languages, you probably like to modularise your code, but you can easily overdo it in
I found Connect item 273443, The Scalar Expression function would speed performance while keeping the benefits of functions, submitted by Andrew Novick and quickly added it to my wishlist.
We probably all have lot of constants in our code. Maybe we use numbers, in which case we will have to know that 1 for order type means "Internet order", 2 means "telephone order" and so on. (Assuming here that order types are system-defined and not user-defined.) Or maybe we try to use mnemonic codes. Whatever, there is no safety net if we mix up the numbers or spell the codes wrongly.
SQL Server MVP Simon Sabin has submitted a suggestion that
You can get the currently executing module through the @@procid function, and you can pass it as a parameter to a general logging function. But there are many cases, where you would like to know the entire call stack. For instance,
say that you have a generic error-handling procedure that you call from your CATCH handlers. Today you can log
When I originally submitted Connect item 124537, I called it Provide function to retrieve the entire call stack. Today, I think that a DMV might be better. Whatever, the information must be accessible for a plain user, else you cannot put it in application code.
A similar problem exists with impersonations with EXECUTE AS. There is SYSTEM_USER which shows the currently impersonated login, and there is
The ability to read from a snapshot, either with true snapshot isolation, or with READ COMMITTED SNAPSHOT was a great addition in SQL 2005. But there are a few situations where the snapshot can lead you into trouble. The most critical case is in triggers where you perform validations of recently inserted/updated data. If you perform those checks against the snapshot, you may perform them against stale data. SQL Server MVP Hugo Kornelis discusses this in a series of blog posts, Snapshot isolation: A threat for integrity?
You can work around the issue with the READCOMMITTEDLOCK hint, but it is somewhat bulky. What is really devilish, is that when you write your trigger you may not anticipate that someone six months later will turn on READ COMMITTED SNAPSHOT in production. If you were able to specify the isolation level with a SET TRANSACTION ISOLATION LEVEL command, you would able to sleep better at night. I've requested this SET command in Connect item 125573.
Today if you nest BEGIN and COMMIT TRANSACTION, inner COMMIT:s just decrease the transaction count, and only when you come to the outermost COMMIT, the actual commit happens. And most of the time, this is what you want. But there are situations where you want to do things outside the current transaction. A typical example is when you log something to an error table. You want that error message to persist, even if the transaction is rolled back. Another example is given in Connect item 296870, Add support for autonomous transactions, submitted by Anthony Bloesch.
Bulk load – i.e. BCP, BULK INSERT and OPENROWSET(BULK) - is an area that could serve from some enhancements.
This is an item which is similar to the first Bulk Load point. That is, you have an XML document, you need to shred
it into a table, but you need to preserve the order in document for one reason or another. As one example why would to
do this, consider an incoming document with Order and OrderDetails data in a business-to-business system, and you need
to be able to correlate the order and the details. However, there is not really any good key in the document, at least
not that you can feel that you can trust. That is, it is perfectly possible that there are two Order elements that are
identical on that level. (The details may differ.) If you look at the document, it's still a piece of cake, it's easy:
that's the third order and that's the fourth order and so on. But if you are shredding the documents with .
There is a very good Connect item 383888,
Fully support
As you may know, TRUNCATE TABLE does not work when there are referencing foreign keys. This is a little silly if all referencing tables are empty. If you have a bunch of tables that you empty and reload regularly, TRUNCATE TABLE is much more attractive than DELETE if the tables are big, since TRUNCATE TABLE is minimally logged. Today, you have the choice of running DELETE and tax the transaction log, or drop all constraints and put them back. Someone asked about this on the newsgroups, which prompted me to submit Connect item 312074, Permit TRUNCATE TABLE when referencing tables are empty.
125347 – Improved error message when you try to put too large a value in a column. That is, change the message String or binary data would be truncated, so that it hints about which value for which column that was truncated. Submitted by SQL Server MVP Louis Davidson.
124627 – Schema not reported in the ERROR_PROCEDURE function. As long as you have all your objects in dbo, this is no biggie, but the day you start using multiple schemas... Submitted by Louis Davidson.
320998 – Add datediff_big. A datediff that returns bigint is good when you have sysadm things that show long time processes have been connected in milliseconds, and the result is > 20 days.
293333 – Add
258728 – Add new substring function where third parameter is end and not length. As it works in many other languages.
532275 – Make it possible to declare variables that are only visible within a block. This would put T-SQL in line with modern programming langauges.
The ability to create stored procedures and functions in .Net languages was one of the most spectacular additions to SQL 2005. And used correctly, this is also very powerful. One problem, though, is that it's fairly bulky to create a CLR module. You first have to compile your C#/VB code, load it into SQL Server with CREATE ASSEMBLY, and finally run CREATE PROCEDURE/FUNCTION/etc to create the object. Visual Studio can perform these steps for you, and if you create a larger assembly from which you define a whole number of procedures and functions that may be alright. But if all you want to do is to write a 50-line utility function, using Visual Studio is even bulkier than using the command line. Not the least if you are a DBA sort of person, who don't have Visual Studio installed at all.
It would be so much more convenient if you could say:
CREATE PROCEDURE my_proc @par1 int,
@par2 int WITH
EXTERNAL LANGUAGE C#, PERMISSION_SET = EXTERNAL_ACCESS AS
using System;
using System.Data;
using System.Data.SqlClient;
...
SQL Server MVP Greg Low has submitted the very compelling Connect item 265266, Add server-side compilation ability to SQL CLR about this.
Today you can define user-defined data types with CREATE TYPE, and you can bind rules and default to them. But the types are merely aliases, and the bound rules and defaults are deprecated features. In ANSI SQL they have domains. I will
have to confess that I don't know that much what you can do with domains, but I am under the impression you can tie CHECK constraints and defaults to them. I also believe that if you have two columns that are in different domains you can not mix them freely, even
if the base data type is the same. This is great for avoid incidental mix-ups like WHERE a.OrderID = b.CustomerID.
But one thing is clear, there is little point to ask for radical changes with the current types. Microsoft should work with real domains instead. Andrew Mackie submitted this request since a few years back: 124645, Add CREATE DOMAIN command.
An additional request is that you should be able to specify the collation when you define your domain. There are entities in a database that should be case-insensitive: customer names, addresses etc to make searching simple. But there are also things like codes, external identifiers where you only accept uppercase or where case is significant. To this end, I have submitted Should be possible to specify collation when define a type/domain.
Think of it, how many columns are there in your database that once they have been written must never change again? Or, when the row has passed some status change, it is impermissible to change the data. For instance, in an Orders table, you cannot permit the customer id to change, at least not if the order has been shipped and invoiced. How many of you enforce these rules consistently? Let me see a show of hands! Hmm, that was not many. I will have to admit that I don't do it very often myself, only days when I'm really paranoid, because it requires quite a lot of trigger code.
But if there was DRI to do it... I've submited connect item 346200, Add DRI to enforce immutable column values. Jason Kresowaty has a similar request which also calls for DDL to enforce columns to have a certain value after an UPDATE, even if the UPDATE does not touch this column, which would be very good for auditing columns. Connect item 355461, Trigger Performance for Simple/Common Scenarios. (Despite the name it suggests new DRI.)
Today you can store Unicode data in SQL Server in the
Thus, it would be desireable to be able to store Unicode data in SQL Server as
(While not relevant for this wishlist, I like to add that for languages like Chinese and Japanese,
Synonyms were added in SQL 2005, and they can be useful. Maybe the most common use for synonyms is when your application is split up on over several databases, so get your cross-databases queries running you can set up a synonym for every table, and a setup script to run with that. But it's a bit roundabout, it would be simpler if all you had to do was to set up a synonym for the other database, and then you would be done. SQL Server MVP Aaron Bertrand has submitted Connect iem 311079, Expand synonym to other entities (database, linked server).
SQL Server has AFTER and INSTEAD OF triggers. Other products also have BEFORE triggers which is a more natural thing. INSTEAD OF triggers, mainly introduced to make more views updatable, are a little funky as they require you to redo the command. This can introduce a maintenance problem and sometimes other difficulties as well. A BEFORE trigger is just something thet executes before the statement that fires it, but which can prevent the statement from be executed if the trigger finds a business-rule violation. And there is an obvious performance advantage here: a big bad statement will not need a big bad rollback, because it's stopped before it takes off. This is maybe even more important for DDL triggers. If you want to prevent indexes from being created during peak hours, an AFTER trigger is not a good idea...
There was already an existing Connect item, 285655, triggers before insert and row level. But this item also calls for per-row triggers, which I'm fairly cool to, and since I wanted to stress the importance for DDL triggers, I submitted on my own item, 361223, Add BEFORE triggers – not the least for DDL statements.
My favourite in this area is this oldie, item 124781, ALTER TABLE syntax for changing column order submitted by someone using the alias PNPwi. It's a bit controversial: every time someone asks on the newsgroups how to do this, they are told that they shouldn't and that column order doesn't matter. It's true that today this is a complicated operation, since you have create a new table, copy data over, drop the old table and remember to recreate indexes, constraints and triggers. And move referencing foreign keys. However, I don't give much for the argument that column order does not matter. True, from a technical standpoint it does not matter, but it matters for humans. When I document a table, I want to describe the columns in the order they appear in the table, so you easily can map to what you see with SELECT *. But I also want to document the columns in a logical order, and not in the order they happened to be added to the table.
What if you realise that you have a table with the IDENTITY property, and that was a mistake? Or the other way around, you should have made that column an IDENTITY from the beginning? Strangely enough can you use ALTER TABLE to change this in SQL Server Compact Edition. But in the "big" SQL Server your only option is to drop or reload the table. SQL Server MVP Adam Machanic has submitted Connect item 252226, Allow enabling and disabling of a column's IDENTITY property to call for a change.
A small thing, but nevertheless. When you change or drop a column you say ALTER TABLE tbl DROP/ALTER COLUMN, but when you add a column, COLUMN is not even permitted in the syntax! Connect item 124626, Add COLUMN keyword to the ADD COLUMN SYNTAX (as alternate), submitted by SQL Server MVP Louis Davidson.
2010-02-27 – Added request for scoped variables.
2010-02-11 – Added request for QUALIFY to permit for more concise queries.
2009-07-09 – Added request for getting the position in an XML document.
2009-05-16 – An additional request with regards to domains.
2008-09-28 – Two more items for strict checks.
2008-09-20 – Added one more Connect item to the section on Immutable Column Values.
2008-08-22 – Added an entry for UTF-8 support.
2008-08-19 – Added entries for row constructors and database synonyms.
2008-08-16 – Initial Version.