Why you should never use default string lengths.

Leave a comment

March 25, 2020 by Kenneth Fisher

TL;DR;

  • You probably don’t know what you’re going to get.
  • Even if you do know the person maintaining your code probably doesn’t.
  • This is how mistakes are made.

Every now and again I see someone get lazy and declare a string (CHAR, NCHAR, VARCHAR and NVARCHAR) without specifically declaring what the length is going to be. This can lead to some interesting problems. First of all it’s usually going to be a length of one.

-- Parameters
CREATE PROCEDURE #sp_Test 
	@Param1 varchar
	AS
BEGIN
	PRINT @Param1;
END
SELECT * FROM tempdb.sys.parameters WHERE name = '@Param1';
-- 1
----------------------------------------------------
-- Variables
DECLARE @Var1 VARCHAR = REPLICATE('a',8000);
SELECT DATALENGTH(@Var1);
-- 1
----------------------------------------------------
-- Columns
CREATE TABLE #Temp (Col1 VARCHAR);
EXEC tempdb..sp_help '#Temp';
-- 1

 
Which is a pretty unusual need. That said it does happen. The first problem though, is that most people don’t realize what they are going to end up with and only getting one character can be a nasty surprise.

DECLARE @Var1 VARCHAR = 'Surprise';
PRINT @Var1;
-- S

 
But let’s say you do know, and it is what you intended. There is a pretty good chance the person after you won’t realize what’s going on. Not many people do after all. This is going to cause a serious maintenance issue. Any code that someone after you can’t understand is going to cause a maintenance issue. In those circumstances you usually just add a comment or two. Of course at that point it’s faster and easier to just specify the length.

I should also point out that it’s not always going to be a length of one. You can figure out when and how long the string is going to be on your own though. 🙂 Just to help drive home that you should always define the string length. It’s just easier.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,152 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: