Aaron Bertrand - Bad habits in SQL

1.  DO: Specify lengths for (n)(var)char:

-- POP QUIZ : what will these yield?

-- in some cases VARCHAR is 1, others 30.

-- some violations get silent truncation, others error

DECLARE @x VARCHAR = 'aaron';

SELECT

  [variable] = @x,

  [concat]   = @x+'bertrand',

  [cast]     = CAST('aaron' AS VARCHAR),

  [convert]  = CONVERT(VARCHAR, 'aaron');

variable concat    cast                           convert

-------- --------- ------------------------------ ------------------------------

a        abertrand aaron                          aaron

(1 row(s) affected)

-- silent truncation

USE tempdb;

GO

CREATE PROCEDURE dbo.foo

  @bar VARCHAR

AS

BEGIN

  SET NOCOUNT ON;

  PRINT @bar;

END

GO

EXEC dbo.foo @bar = 'more than one character';

GO

Result:

m

DROP PROCEDURE dbo.foo;

GO

-- truncation yielding an error

DECLARE @t TABLE(x VARCHAR);

INSERT @t(x) SELECT 'aaron';

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 as wide as you expect.  In some scenarios, it will be 1 (yes, that is the number one, not a typo), and in others it will be 30.  Here is an example:

DECLARE @foo VARCHAR;

SET @foo = REPLICATE('a', 255);

SELECT LEN(@foo);

-- 1

SELECT LEN(CAST(REPLICATE('a', 255) AS VARCHAR));

-- 30

While Inserting data into table:

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 SELECT REPLICATE('b', 2);
GO
DROP TABLE dbo.foo; 

The column is actually created as a VARCHAR(1).  So the result of the INSERT statement:

.Net SqlClient Data Provider: Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.

The Varchar behaviour in SP:

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 

Result:

variable  stored--------  --------

255       1

So, please don't be lazy: declare your string lengths, always!