SQL Fiddle

SQL Fiddle is a new on-line tool that allows you to illustrate SQL DDL/DML. It currently defaults to SQL Server 2008 R2 but also supports 2012 and five other databases.

Check out a simple example here. This example shows the table and queries I built to answer this Stack Overflow question. When you build something in the tool, it automatically generates a unique URL for that schema/query pair.

Bravo to Jake Feasel for creating such a great tool.

Rob

UPDATE a Column While Simultaneously Setting a Local Variable

I saw an interesting pattern in a Microsoft-supplied stored procedure today. They update a column and write a local variable at the same time.

Here is an illustration of the technique.

Code:
USE tempdb; 
SET NOCOUNT ON;
GO

CREATE TABLE test1 (
   ColId       INT             IDENTITY,
   ColValue1   VARCHAR(20),
   ColValue2   VARCHAR(20)
);

INSERT INTO test1 (ColValue1)
VALUES ('Col1-Initial'); 

INSERT INTO test1 (ColValue2)
VALUES ('Col2-Initial');

DECLARE @value VARCHAR(20);

SELECT ColValue1, ColValue2 FROM test1;

UPDATE test1 

SET @value = ColValue2 = ColValue1 + '-Updated'

WHERE ColId = 1;

SELECT @value AS '@value';
SELECT ColValue1, ColValue2 FROM test1;

Results:

ColValue1            ColValue2
-------------------- --------------------
Col1-Initial         NULL
NULL                 Col2-Initial

@value
--------------------
Col1-Initial-Updated

ColValue1            ColValue2
-------------------- --------------------
Col1-Initial         Col1-Initial-Updated
NULL                 Col2-Initial

Comparing Nullable Strings

To test if nullable @String1 is different from nullable @String2, I had this fairly complex code:

IF   (@String1 <> @String2
  OR (@String1 IS     NULL AND @String2 IS NOT NULL)
  OR (@String1 IS NOT NULL AND @String2 IS     NULL)) ...
 
Someone saw this and decided it would be simpler to do this instead:

IF (COALESCE(@String1, '') <> COALESCE(@String2, '')) ...

The problem is, that doesn't work in all cases. Here is the case where it doesn't work, followed by the cases where it does work.

First, declare and set the two string variables:

DECLARE @String1    VARCHAR(20);
DECLARE @String2    VARCHAR(20);

SET @String1 = NULL; 
SET @String2 = '';
 
Then the test:

IF (COALESCE(@String1, '') <> COALESCE(@String2, ''))
   PRINT 'Strings Differ';

IF   (@String1 <> @String2
  OR (@String1 IS     NULL AND @String2 IS NOT NULL)
  OR (@String1 IS NOT NULL AND @String2 IS     NULL))
   PRINT 'Strings Differ';

The first test does not recognize the difference, but the second test does.

All of these values pass the test:

-- 1: Both NOT NULL, different

SET @String1 = 'OldDesc';
SET @String2 = 'Desc'; 

-- 2: Both NOT NULL, same

SET @String1 = 'Desc';
SET @String2 = 'Desc';

-- 3: Both NULL

SET @String1 = NULL;
SET @String2 = NULL;

-- 4: One NULL, one NOT NULLSET @String1 = 'OldDesc'; 
SET @String2 = NULL;

First Simple-Talk Article

My first article at Simple-Talk is now live:What's the Point of Using VARCHAR(n) Anymore?

It is exciting to have the opportunity to write for such a well-respected magazine. It was also a very different writing experience than I'm used to. The editor is quite technical, so he provided feedback on the writing itself as well as the technical content.

I look forward to writing more for them in the future.