SQL 101: What Happens When...

I had an interesting question asked this last Saturday when I was giving my Intro to SQL session at SQL Saturday Redmond: “What happens if you cast a NVARCHAR string as VARCHAR?”

I had an educated guess but I didn’t have the time during the session to test it out: Casting NVARCHAR to VARCHAR. It won’t throw an error but it will destroy any data in the string that isn’t VARCHAR compatible. Try out the test below yourself!

/* Preface the string with N or
SQL will pre-emptively destroy
the data before storing the string */
DECLARE @nv AS NVARCHAR(10) = N'╥«┘{║‼℈≠'
    , @v AS VARCHAR(10);

SELECT @nv, CAST(@nv AS VARCHAR(10));

/* And by reassigning we can see
the data is permanently lost */

SELECT @v = @nv;
SELECT @v, CAST(@v AS NVARCHAR(10));

The question was asked initially because she had found a place where she had to convert a uniqueID to a string and was concerned that perhaps this could cause issues. The uniqueidentifier is a series of hexadecimal numbers concatenated together with hyphens (for example: 851CC794-E0CB-49DC-976F-1687F62D2188). They will always be 36 “characters” long. The good news is that according to BOL the uniqueidentifier is consider a character type for the purposes of conversion. Attempting to cast a string that is longer than 36 characters will truncate the original string to the first 36 before it attempts the conversion. Conversely casting a uniqueidentifier to a string datatype will result in a 36 character string (unless you specify less than 36 characters in which case SQL will throw an error).