Welcome to EMC Consulting Blogs

As a concise example of the evils of type coercion I came up with the following:

INSERT INTO t
VALUES (
CAST(2 AS INT),
CAST(2 AS INT));

SELECT
CASE WHEN x + z = 4 THEN '4' ELSE 'NOT 4' END xPLUSz
FROM t;

Result:

xPLUSz
------
NOT 4

It is left as an exercise for the reader to work out what types x and z might be!

UPDATE FROM is dead … or it should be. Hugo Kornelis has done a good job of explaining why.

Apart from a badly thought out and non-portable syntax, UPDATE FROM suffers a notorious bug/feature. I say “notorious” but it’s probably not notorious enough because it is still common to see people get tripped up by it. If I write:

UPDATE Alpha
SET z = Beta.z
FROM Alpha
JOIN Beta
ON Alpha.x = Beta.x ;

and if Beta.x is not unique then Alpha.z will get updated with some random value from one of the matching rows in Beta.z. This is documented in Books Online as follows:

“The results of an UPDATE statement are undefined if the statement includes a FROM clause that is not specified in such a way that only one value is available for each column occurrence that is updated, that is if the UPDATE statement is not deterministic.”

MERGE handles the same situation more wisely and safely: it returns a runtime error if the join is non-deterministic:

MERGE INTO Alpha
USING Beta
ON Alpha.x = Beta.x
WHEN MATCHED THEN UPDATE
SET z = Beta.z ;

Msg 8672, Level 16, State 1, Line 42
The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

MERGE is also standard SQL, works in other DBMSs and frequently results in more efficient execution plans than UPDATE FROM.

UPDATE FROM isn’t originally Microsoft’s fault because it’s a legacy of SQL Server’s ancient historical roots at Sybase. But it is Microsoft’s fault they haven’t deprecated it yet. You can vote for deprecating UPDATE FROM on Connect:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=332437

Whether and when to upgrade to SQL Server 2008? It seems that there is still a school of thought that the release version of a product is not to be trusted and that the best policy is to "wait for SP1". Now this has never made much sense to me, not least because Service Packs can mean quite different things in different products. Sticking with SQL Server 2008 though, I believe there are plenty of reasons to upgrade now and NOT to wait for SP1.

Firstly, SQL Server 2008 is an extremely high quality and stable product and has already notched up a great many server-years of collective testing, both before release and after. I personally have been developing and testing with it on projects for a year now and it has been the best quality preview version of the product I've ever worked with. SQL Server 2008 RTM is in very much better shape now than SQL Server 2005 was at this point in its life.

Secondly, it has to be said that the history of SQL Server Service Packs is not an entirely happy one. There have been some very unfortunate problems with SQL SPs. For sure, the SQL Server team will learn from their mistakes but the fact is that an SP is never going to get the level of testing at customer sites that a major release gets. This is natural enough because SPs rarely contain new functionality so it's harder to justify the investment needed to build and test with the beta versions of SPs.

In terms of quantifiable risk then, installing a 2008 service pack right after release is potentially far more risky than installing SQL Server 2008 RTM right now.

Thirdly, there are the real returns you could get from upgrading to 2008 even with minimal effort. Upgrading from 2000 to 2005 was and is a big undertaking. Between 2000 and 2005 there is a whole raft of new features, deprecated features, breaking changes - all of which adds up to a lot of regression testing and potential development effort to take advantage of new features. In comparison, the additional effort to move from 2005 to 2008 or from 2000 to 2008  is a far smaller piece of work. Fewer new things will be broken or need re-working. Plus you can take advantage of new features like compression and policy based management with zero change to existing applications.

Andrew Fryer says that you may have to wait a long time for a SQL 2008 SP. At the UK User Group meeting last night he also had a suggestion. Even if you aren't upgrading your servers yet, at least install and use 2008 Management Studio so that you can take advantage of the new features in the tool!

The MERGE syntax I used here has changed. In 2008 RC0 the "WHEN SOURCE NOT MATCHED" clause is replaced with "WHEN NOT MATCHED BY SOURCE" which is supposed to make the meaning clearer.

Although MERGE is part of standard SQL 2003 it's worth noting that the SOURCE NOT MATCHED / NOT MATCHED BY SOURCE clause is not part of the standard. It's an extension added by Microsoft - a very useful one in my view.

MERGE INTO a
USING b
ON a.keycol = b.keycol
WHEN MATCHED THEN
UPDATE SET
  col1 = b.col1,
  col2 = b.col2,
  col3 = b.col3
WHEN NOT MATCHED THEN 
  INSERT (keycol, col1, col2, col3) 
  VALUES (b.keycol, b.col1, b.col2, b.col3)
WHEN NOT MATCHED BY SOURCE THEN 
  DELETE;

According to InfoWorld, Bill Gates thinks that declarative modelling of business logic has not caught on because of "weak models".

This is a surprise. E.F.Codd's earliest papers on the Relational Model described a relational calculus for the purpose of supporting declarative business logic. Even the SQL standards committee added some powerful features to standard SQL, such as CREATE ASSERTION and generalised CHECK constraints with subqueries. Despite this, InfoWorld identifies the Relational Model as one of the "weak" models referred to by Gates!

What is stopping people putting declarative business logic in their data models today? The answer is not the model. It is the DBMS products that are supposed to implement the model. Here's what Terry Halpin said about declarative constraint support in SQL back in 2001

"Although the above SQL syntax [CREATE DOMAIN and CHECK constraint with subquery] is legal as far back as SQL-92, some commercial versions of SQL do not yet support all of this syntax. For data definition, some versions have barely progressed beyond the old SQL-89 standard (which had no domain clauses, and restricted check clauses to conditions on a single row). In practice, some features of a relational schema may need to be specified as a procedural code rather than declaratively."

(Information Modeling and Relational Databases, p411)

In 2000, Fabian Pascal wrote:

"most commercial DBMSs have not implemented the [SQL] standard's advanced level integrity features. Some implement integrity procedurally via user-written stored procedures expressed in proprietary SQL extensions that can be quite complex, vary across products and have various limitations."

(Practical Issues in Database Management, p64)

And here's my take on the same problem:
http://blogs.conchango.com/davidportas/archive/2007/02/19/Trouble-with-CHECK-Constraints.aspx

Database practitioners have been saying for years that most SQL DBMSs do a poor job of supporting declarative business logic. Dear Bill: the relational model is just great. Please fix the omissions in the current products before going on to invent whole new ones!

MERGE is a new DML statement in SQL Server 2008. Microsoft have implemented the ISO SQL 2003 and 2007 standard MERGE statement (as seen in Oracle and DB2) and added some extensions of their own.

In a nutshell, MERGE allows you to perform simultaneous UPDATE, INSERT and/or DELETE operations on one table. There are new physical operators that combine these operations so that they can be performed in a single scan rather than multiple scans.

MERGE has loads of possible applications. For the first time you can assign the contents of one table or query to another in a single operation. The following example requires SQL Server 2008 CTP4. Given this schema and data:

CREATE TABLE a
 (keycol INT PRIMARY KEY,
  col1 INT NOT NULL,
  col2 INT NOT NULL,
  col3 INT NOT NULL);

CREATE TABLE b
 (keycol INT PRIMARY KEY,
  col1 INT NOT NULL,
  col2 INT NOT NULL,
  col3 INT NOT NULL);

INSERT INTO a VALUES (1,0,0,0),(2,0,0,0);
INSERT INTO b VALUES (1,1,1,1),(3,3,3,3);

The following MERGE will populate table a with the same data as table b:

MERGE INTO a
USING b
 ON a.keycol = b.keycol
WHEN MATCHED THEN
 UPDATE SET
  col1 = b.col1,
  col2 = b.col2,
  col3 = b.col3
WHEN NOT MATCHED THEN
 INSERT (keycol, col1, col2, col3)
 VALUES (b.keycol, b.col1, b.col2, b.col3)
WHEN SOURCE NOT MATCHED THEN
 DELETE;

In the relational world this is the operation known as Relational Assignment ie:

 a := b

Unfortunately the SQL syntax is less pretty and requires just a little more typing!

MERGE also makes a good "upsert" for application CRUD stored procedures, removing the need for constructs like:

IF NOT EXISTS ...
  INSERT ...

Here's an example I created today. It inserts a new Vendor if and only if the name doesn't already exist. Whether the name previously existed or not, it returns the IDENTITY value of the existing or newly inserted row.

CREATE PROC dbo.usp_VendorUpsert

(

     @pVendorID INT OUTPUT,

     @pVendorName VARCHAR(80)

)

 

AS

BEGIN

 

     SET NOCOUNT ON;

 

     MERGE dbo.Vendor t

     USING (SELECT @pVendorName

            ) p(VendorName)

     ON t.VendorName = @pVendorName

 

     WHEN NOT MATCHED THEN

     INSERT (VendorName)

     VALUES (@pVendorName)

 

     WHEN MATCHED THEN

     UPDATE SET @pVendorID = VendorID;

 

     SET @pVendorID = COALESCE(SCOPE_IDENTITY(),@pVendorID);

 

END

 

RETURN

 

It's amazing that it took nearly 20 years for the SQL standards committee to come up with MERGE. Perhaps the delay is a legacy of the decision to make INSERT, UPDATE and DELETE the basic data update operators. INSERT, UPDATE and DELETE can all be defined as different kinds of relational assignment - assignment being the most basic type of update possible. So arguably MERGE is the more primitive and fundamental data update operator that ought to have been around earlier rather than later.

One of my regular gripes about SQL Server concerns the number of Transact SQL features that give nondeterministic results. Unfortunately SQL Server 2005 added to the list of those. One of the new ones is ROW_NUMBER(), which is strictly due to ANSI/ISO rather than Microsoft. ROW_NUMBER() is inherently non-deterministic unless the PARTITION / ORDER BY criteria define a unique criteria for each row - something that isn't mandatory. What I think is surprising however, is Microsoft's approach to updateable views and CTE's, which gives an interesting twist to the effect of ROW_NUMBER().

For example, ROW_NUMBER() can be used to solve the problem of removing duplicates from a set and it's undeniably useful for that purpose. Here's an apparently simple example. The goal is to delete all but one row for each distinct value of Z.

CREATE TABLE tbl (x INT NOT NULL PRIMARY KEY, z CHAR(1) NOT NULL);

INSERT INTO tbl (x,z) VALUES (1,'A');
INSERT INTO tbl (x,z) VALUES (2,'A');
INSERT INTO tbl (x,z) VALUES (3,'B');
INSERT INTO tbl (x,z) VALUES (4,'B');

WITH t(r) AS /* Updateable CTE */
 (SELECT ROW_NUMBER() OVER (PARTITION BY z ORDER BY z)
  FROM tbl)
 DELETE t WHERE r > 1;

The problem is: which rows get deleted? The CTE doesn't contain a key, in fact it doesn't contain anything except the ROW_NUMBER() in column "r". The ORDER BY clause doesn't reference a key either which means the number returned for each row is nondeterministic. Yet despite these facts the CTE is still updateable. I was surprised when I first discovered that the DELETE statement above is legal. I was even more surprised to find that Books Online is totally silent on the point of exactly when a CTE is updateable and when it isn't. The lack of documentation is a serious omission because those who have been using SQL Server for a few years will know that undefined behaviour tends to get dropped or changed between releases.

Right now, my habit is that if I need to update a CTE then I make sure it contains a key. Usually it makes sense for the OVER... ORDER BY clause to use a key also. Like:

WITH t(x,r) AS /* Updateable CTE */
 (SELECT x, ROW_NUMBER() OVER (PARTITION BY z ORDER BY x)
  FROM tbl)
 DELETE t WHERE r > 1;

(note the difference: SELECT x, ... ORDER BY x).

 

Alice: What do you mean by practical languages?
Riccardo: select from where.
Alice: That's it?
Vittorio: Well, there are of course lots of bells and whistles.
Sergio: But basically, this forms the core of most practical languages. [1]

Oracle is celebrating its 30th birthday this month. They didn't adopt Oracle as the company name until 1982, before which they were known as Relational Software Inc. Of course their very excellent, landmark database product is also "relational" by name and reputation but strictly non-relational in fact. All SQL DBMSs borrow certain features and terminology of the relational model while remaining fundamentally anti-relational in many important respects[2]. IBM began by declaring that its SQL language was in fact "relational" and the name has stuck.

Once or twice I have floated the idea that a new relational database language should be added to SQL Server as an alternative to the T-SQL language. In my opinion the time is right for it. SQL Server already has several data access languages and layers of abstraction - such as SSAS/MDX, LINQ, SSIS - so adding another one isn't exactly a radical idea. What I'm talking about would be a relational language that lives up to Date and Darwen's description of a "D" language.

Naturally the new language would imply a new data model as well - the relational data model instead of the SQL one - but I don't think it would be difficult to build it on top of SQL Server's existing engine. In fact one of the key benefits would be that the engine could be freed of certain restrictions imposed by SQL (for example the requirement to support duplicates and column order). Implementing a new language would be a much more modest step than it was to add the OLAP Services engine into SQL Server 7.0 back in 1998.
 
I was recently asked for some examples of problems solved by a true RDBMS that aren't solved already by SQL Server (or other SQL DBMSs for that matter). A full list would be pretty long! Among other things an RDBMS would deliver a truly unified model suitable for both OLTP and OLAP applications. Performance and storage improvements due to better data independence. Support for declarative constraint logic. A more powerful and generic type system.
 
A relational language would also fix some of the more irritating limitations that waste so much SQL programming time today. Duplicate rows and the consequent failure to support guaranteed access. No relational assignment or comparison operators. No multiple assignment. Three-value logic.
 
Is it going to happen? I don't know, but I do think the combination of today's business demands and today's technology make it a real possibility - perhaps more so than at any time in the last 30 years. SQL was a data access language for the 1980's but a 21st century replacement is well overdue. Comments and feeback are welcome!

--

[1] From "Foundations of Databases", Chapter 7, by Abiteboul, Hull and Vianu. Addison-Wesley

[2] There are numerous references that cover the details. I can recommend "Database in Depth" by Chris Date. "The Askew Wall" by Hugh Darwen, republished in "Relational Database Writings 1989-1991" explains the origins of SQL and some of its limitations.