I know, I know, it is a well-worn cliche, but in SQL size indeed does matter, at least for some (data types). If you think I am just pontificating, I am going to demonstrate it to you here. With examples! Aren’t I awesome!?!
So, here are a few SQL statements. I will explain later what each of these is doing, but before you read my brilliant explanations, I want YOU to guess what the results would be. Ok, so here we go:
-- The string literal that I am using here: -- '12345678901234567890123456789012' is 32 characters long. If you -- don't trust me, go ahead and count, ye of little faith!! -- 1. What will you get for Length and Text in the following? SELECT LEN(CAST('12345678901234567890123456789012' AS VARCHAR)) [Len], CAST('12345678901234567890123456789012' AS VARCHAR) AS [Text]; GO
-- 2. What will you get for length and text in this example? DECLARE @x VARCHAR = '12345678901234567890123456789012'; SELECT LEN(@x) AS [Len], @x AS [Text]; GO -- 3. This is very similar to #1 -- What will you get for length in each case? SELECT LEN(CAST('12345678901234567890123456789012' AS VARBINARY)) [Len], CAST('12345678901234567890123456789012' AS VARBINARY) AS [Text]; GO -- 4. This is very similar to #2 -- What will you get for length in each case? DECLARE @x VARBINARY; SET @x = CAST('12345678901234567890123456789012' AS VARBINARY); SELECT LEN(@x) AS [Len], @x AS [Text];
Ok, now that you have guessed the answers for all four cases, go ahead and copy the code, and run it in an SSMS query window.
For those of you who are not near a computer with SSMS, here are the answers:
-- #1 Len Text 30 123456789012345678901234567890 -- #2 Len Text 1 1 -- #3 Len Text 30 0x313233343536373839303132333435363738393031323334353637383930 -- # 4 Len Text 1 0x31
Even though my string literal was 32 characters long, in one case it got truncated to 30 characters, and in another case to just one character. The moral of the story? “ALWAYS SPECIFY A LENGTH FOR YOUR CHARACTER DATA TYPES”. For example, like this:
SELECT LEN(CAST('12345678901234567890123456789012' AS VARCHAR(64))) [Len], CAST('12345678901234567890123456789012' AS VARCHAR(64)) AS [Text]; GO DECLARE @x VARCHAR(64) = '12345678901234567890123456789012'; -- and similarly for varbinary.
If you guessed every result in my quiz correctly, then I have a question for you: “What the heck are you doing wasting your time reading my blog?” If you guessed it correctly I know you always specify the length for every data type which has the option to specify the length.
If you did not guess the results correctly, let this be a lesson for you!! And that lesson being – “For every data type for which there is an option to specify a length, specify the length”.
By the way, now you also know that when someone tells you that the default length of VARCHAR is 30, they are lying to you. It isn’t. If you assumed that it is 30 and tried to rely on that in stored procedure parameters, you are in for a shock.
Although I didn’t include it in my quiz for you, here are couple more examples that you can copy and run to see what they do. One of those would in fact give you an error (which is a relief because it won’t silently give you the wrong answer).
DECLARE @n DECIMAL = 123456789012345678901234567890; SELECT @n; GO DECLARE @n DECIMAL = 1.23456789; SELECT @n; GO
The right way to declare decimal is using a precision and scale. So, like shown below:
-- I know, I know, this number is so huge that the only -- practical use for it is for representing US Federal deficit. DECLARE @n DECIMAL(38,0) = 123456789012345678901234567890; SELECT @n; GO DECLARE @n DECIMAL(19,9) = 1.23456789; SELECT @n;
I almost forgot! I told you at the very beginning of this tirade that I will give you brilliant explanations for the behavior that you are seeing, didn’t I? If you are looking for those brilliant explanations: Psych!!
In my quiz, did you notice that I did not tell you what the default length in each case is, or how large a value the DECIMAL data type declared without a precision and length can hold? That was not because I am shallow, that is because I NEVER want you to use those without specifying a length. I hope that doesn’t take away anything from my shallowness.
Ok boys and girls, I think I have pontificated enough for one day. I hope you all strive towards mediocrity like me! Adios!!