Bad Habits to Kick : Declaring VARCHAR without (length)
See the full index.
One of my biggest pet peeves: declaring string variables or parameters without specifying how many characters they should hold. Thankfully, I see this issue seldom in the code I've inherited in my own systems, but it is quite common in blog posts and Q & A.
Because the length attribute is optional, people seem to make the assumption that defining a
varchar in T-SQL is like defining a string in C#: "it will just expand to whatever size you stuff into it." The problem is, if you don't define a length, SQL Server will assign one for you, and it may not be the length you expect. In some scenarios, it will be
1 ; in others,
Here is an example:
DECLARE @foo varchar = 'aaron'; SELECT foo = @foo, lenfoo = LEN(@foo); -- foo lenfoo -- ---- ------ -- a 1 SELECT LEN(CAST(REPLICATE('a', 255) AS varchar)); -- 30
I have asked for the default size of
varchar values with unspecified lengths to be consistent; Erland wants the "optional" part deprecated. We've each pleaded our case on () Connect:
- #244395 : Deprecate (n)varchar with out length specifcation
- #267605 : Make size of VARCHAR (no length) consistent
Anyway, back to the original assumption (that any size will do); this assumption is not devastating when you are creating a table, because you will get an error message if you try to stuff more than 1 character. An example:
CREATE TABLE dbo.foo(bar varchar); GO INSERT dbo.foo(bar) VALUES('abcdefg'); GO DROP TABLE dbo.foo;
The column is actually created as a
varchar(1). So the result of the
String or binary data would be truncated. The statement has been terminated.
In more modern versions:
String or binary data would be truncated in table '<dbname>.dbo.foo', column 'bar'. Truncated value: 'a'.
It will only take one or two tries before you (or one of your users!) will discover the problem; surely it will be corrected quickly. However, it can very easily lead to data loss or corruption in other scenarios, and you might not notice for some time. Imagine if you forget to specify the length of a parameter to a stored procedure; since any data value > 30 characters will be truncated silently, portions of your strings will simply disappear! A more elaborate example to illustrate this:
USE [tempdb]; GO CREATE TABLE dbo.foo ( bar varchar(255) ); GO CREATE PROCEDURE dbo.foo_create @bar varchar AS BEGIN SET NOCOUNT ON; INSERT dbo.foo(bar) VALUES(@bar); END GO DECLARE @bar varchar(255); SET @bar = REPLICATE('x', 255); EXEC dbo.foo_create @bar = @bar; SELECT [variable] = LEN(@bar), [stored] = LEN(bar) FROM dbo.foo; GO DROP PROCEDURE dbo.foo_create; DROP TABLE dbo.foo; GO
variable stored -------- -------- 255 1
This is bad news. Unit tests will succeed because a value was inserted and no exception was raised. Unless your testing process includes checking the data in the table(s) matches the input directly after the procedure call, you might not figure this problem out for a while.
So, please don't be lazy: declare your string lengths, always!
Similar problems, though probably not as severe, can happen with DECIMAL and NUMERIC types. George Mastros blogged about it this morning.
See the full index.