My SQL Server Wishlist
Version 2008

An SQL text by Erland Sommarskog, SQL Server MVP. Most recent version 2012-04-30.

Introduction

This is the original version of my SQL Server Wishlist as it looked after the release of SQL 2008, with some additions made on the road to the release of SQL 2012. In a box at the end of each section you find what has happened with the wish. Most wishes have the status Not implemented. Still on the wishlist. But some wishes have been fulfilled in whole, and others in part. I've chosen to withdraw a few wishes for various reasons. Microsoft has closed some Connect items with Won't Fix, which i've noted in the annotations.

For the current version of the wishlist, please see here.

   Introduction
   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 IF EXISTS()
      Regular Expressions
      Dynamic Pivot
   Improvements for Programming
      Make Table Parameters Read-Write
      Module-Level Table Expressions
      Inline Scalar UDFs
      Enums in T-SQL
      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

SET STRICT_CHECKS ON – Checks for More Robust Development

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.

No, this wish was not satisfied. However, some of the checks are implemented in SQL Server Data Tools, which is a new impressing offering that comes with SQL 2012. I like what I see in SSDT, but you have adapt to a certain workflow and if that does not fit your organisation, SSDT is not for you. The wish still stands, and I've updated the Ideas document with some new ideas, while removing one or two I no longer believe in.

Completing the OVER Clause

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.
600499 – OVER clause enhancement request – Reuse of Window Definitions with WINDOW Clause.
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.

If think that one number is out of sync with the rest, this is because Itzik has superseded one of his original proposals with one that uses ANSI syntax, which the original did not do.

YES! We got the improved OVER clause and Window functions in SQL Server 2012. If you ask me, this is the single-most important improvement in SQL 2012 by far. Not all of the items above were included. More precisely 254387, 254388, 254392 and 254395 were implemented as well as a few more things not listed above. Of the other four, the last has been closed as Won't Fix. While there still is more to ask for, I have dropped the OVER clause for the wishlist for now. I first want to play with the goddies in SQL 2012.

Improvements for Querying

A Built-in Table of Numbers

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.

Not implemented. Still on the wishlist.

Is this Convert Valid?

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 isdate() and isnumeric(), but isdate() only validates against the old types datetime and smalldatetime, not the new types. And isnumeric() tells us, yes, this string is convertible to some numeric data type, but if that is float, money or int we have no idea.

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.

YES! A small but very important improvement is the addition of try_convert and try_cast (as well as try_parse) in SQL 2012. It's just a shame they did not do this earlier.

IS [NOT] DISTINCT FROM

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 <> condition will not return rows where only one of the columns are NULL, since NULL is never equal or unequal to another NULL, but NULL <> NULL yields UNKNOWN.

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.

Not implemented. Still on the wishlist.

Type the Literal!

Today there are three base types of literals in T-SQL: numbers, strings and binary strings (those starting with 0x). This means that for data types like date, uniqueidentifier etc, there is no way you can achieve a literal of that type. Add to this that it's more or less a science trying to figure out what is the data type of numeric literal. You can work around this by using cast() or convert(), but it is quite bulky.

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.

Not implemented as suggested above. However, SQL 2012 adds the functions datefromparts, datetimefromparts etc which to some extent meet these needs. But they are a bit bulky compared to what is in ANSI, and they are only for some data types, so the wish still stands.

Make YYYY-MM-DD Safe

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.

Not implemented. Still on the wishlist.

Row Constructors

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

Not implemented. Still on the wishlist.

QUALIFY for more concise queries

The problem when you use the row_number() or any other ranking function is that if you want to filter by the row_number, you need to put the ranking function in a CTE or a derived table. While this is simple to write, the code gets somewhat tedious. The reason for this is that an SQL query is evaluated in this order:

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.

Not implemented. Still on the wishlist.

Remove Illogical Restrictions to IF EXISTS()

When you have a query in IN/WHILE EXISTS(), you cannot use CTE:s or query hints, restrictions that appear as illogical. That is, you cannot say:

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 EXISTS(), and 308744, Allow Common Table Expressions in all query expressions.

Not implemented. Still on the wishlist.

Regular Expressions

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.

Not implemented. Still on the wishlist.

Dynamic Pivot

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.

Not implemented. Still on the wishlist.

Improvements for Programming

Make Table Parameters Read-Write

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.

Very sadly, this has not been implemented. Still on the wishlist.

Module-Level Table Expressions

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.

Not implemented. Still on the wishlist.

Inline Scalar UDFs

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 T-SQL. A common mistake is put a scalar query in a scalar UDF. This is a mistake, because no matter how simple your query is, the UDF query is never expanded into the surrounding query, and the optimizer cannot change computation order to something better. This can drastically degrade performance.

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.

Not implemented. Still on the wishlist. It's difficult to understand what holds Microsoft back.

Enums in T-SQL

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 T-SQL should permit us define enums to get away from this, a very interesting idea that already has attracted over 100 votes. Connect item 254293 Allow literals and read only table data to be represented as enums.

Not implemented. Still on the wishlist.

Get the Call Stack!

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 error_procedure() and also @@procid if you take it as a parameter. But if the procedure that failed is called from several places, you may have very little knowing how you ended up there. Another situation is in a trigger where you would like to bypass a check if the trigger was invoked by a certain procedure that is permitted to break the rules (maybe because it clean it up later).

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 original_login() which gives you the user that actually logged in. However, that may be a proxy user that impersonates the real user behind the keyboard. This is covered by Connect item 238908, Entire impersonation stack should be retrievable.

Not implemented. (Well, you can probably do it with X-events, but it's completely crazy to use server-level configuration to implement a programming need.) The first point is still on the wishlist, while I've cut the second, since it has recieved less than 10 votes.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED LOCK

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.

Not implemented. Still on the wishlist. It has not received many votes, but I'm a strong believer in robust programming, for which this hint is needed.

Autonomous Transactions

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.

Not implemented. Still on the wishlist.

Improvements for Bulk Load

Bulk load – i.e. BCP, BULK INSERT and OPENROWSET(BULK) - is an area that could serve from some enhancements.

The first of these items have been fulfilled in a very surprising way. Microsoft claims that it works the way I suggest already in SQL 2008, and have closed the Connect item with No Repro. Since many others have told me not to rely on IDENTITY, I don't know what to believe. But I have removed that item from the wishlist. Of the other points the second and the last have been closed as Won't Fix by Microsoft, but they are all still on the wishlist.

Get Position within an XML Document

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 .nodes(), there is no way you can find out which node that is the first, the second and so on.

There is a very good Connect item 383888, Fully support position() in xquery,  submitted by the signature phe1129 which discusses this. The Connect item also suggests some workarounds, but as the author points out, they are inefficient with large XML documents. My thanks to SQL Server MVP Alejandro Mesa who pointed me to this item.

Not implemented. Still on the wishlist.

TRUNCATE TABLE and Foreign Keys

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.

Not implemented. And more unbelievable, it has been closed as Won't Fix! To be fair, it has been closed in favour of another Connect item which aims at the same problem, by suggesting minimally logged deletes, which to me looks both more dangerous and more difficult to implement. The item remains on the wishlist.

Really Small Things

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 sysdate(). A sysdate() function would make things easier when you want query a date column for things that happened today.

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.

None of these have been implemented. The first two have have been folded into a new Connect item submitted by Microsoft. The last two have been closed as Won't Fix, and I've deleted both from the current wishlist. On the other hand, I've added another item that they have also closed as Won't Fix...

DDL Improvements

Server-Side CLR Compiles

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.

Not implemented. Still on the wishlist.

Give us Real Domains!

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

Not implemented. Still on the wishlist. My second item has been closed as Won't Fix. Some problems on Connect incorrectly led me to believe that the first item had disappeared (it is still active), why I opened a new item in which I include the thing about collations.

Immutable Column Values

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

Not implemented. The first item has been closed as Won't Fix, the second is still open. Both items remain on the wish list.

Support for UTF-8

Today you can store Unicode data in SQL Server in the UCS-2 encoding. There are two problems with this:

Thus, it would be desireable to be able to store Unicode data in SQL Server as UTF-8, probably through a new data type. I've submitted Connect item 362867, Add support for storing UTF-8 natively in SQL Server.

(While not relevant for this wishlist, I like to add that for languages like Chinese and Japanese, UTF-8 is a less efficient format, as each character in these languages typically require three bytes.)

When it comes to storage, this request was met in SQL 2008 R2, which added Unicode compression. (Although it is only available in Enterprise Edition.) I've also come to the conclusion that adding a new data type or something similar is not a good idea, why I have removed this item from the wish list. Adding a new data type is expensive for Microsoft, and what do you gain? There is no logical difference between UTF-16 (the encoding that SQL Server uses today) and UTF-8. The storage differs but that can be handled with compression. When passing data from a client this is something the API should be able to handle. There is still a feature gap for BCP and BULK INSERT, since they cannot read UTF-8 files. I have not submitted a Connect item for this though, nor have I added that part to the wish list. But if you have a Connect item, I could be persuaded.

Synonyms for Databases and Linked Servers

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

Not implemented. Still on the wishlist.

BEFORE triggers

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.

Not implemented. The first item is still active, where as mine has been closed as Won't Fix. Still on the wishlist.

ALTER TABLE Improvements

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.

Not implemented. Due to problems on Connect, I incorrectly assume that the first and last item had disappeared - they are both still active. For this reason, I've submitted a new item for the point and sneaked the third into it. Still on the wishlist.

Revision History

2012-04-30 – Made this the SQL 2008 version of the wishlist, and added comments with the siutation after the release or SQL 2012.

2010-09-18 – Updated the section about the OVER clause with a new Connect item from Itzik Ben-Gan.

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.

Back to my home page.