Declaring Length for VARCHAR and NVARCHAR datatypes IS Necessary

I have seen some new SQL Server developers declaring Varchar and Nvarchar data types without specifying a length, since it is optional. Although this works in some other programming languages like C#, SQL Server behaves differently and this is a bad practice overall. Let us see with an example of what’s wrong with not declaring a length for the Char or Varchar data type.

Consider the following t-sql code

varchar-length

OUTPUT

image

Surprised seeing the output? When a length is not specified while declaring a variable, the default length is 1 and when a length is not specified when using the CAST and CONVERT functions, the default length is 30. That’s the reason you get the output you see above.

Note: Try doing the same when you create a table with a CHAR column, without specifying the length.

To get the desired results, rewrite the t-sql code in the following manner:

DECLARE @v1 AS char(30)
DECLARE @v2 AS varchar(60)
SET @v1 = 'abcdefghijkl'
SET @v2 = 'The quick brown fox jumped over the lazy dog'

SELECT
DATALENGTH(@v2),
DATALENGTH(CAST(@v2 as varchar(60)))
GO


Now you get the desired results

image

Another place where you got to be careful is while creating stored procedures with parameters. If you have created a stored procedure that accepts a parameter with a VARCHAR datatype with no length, you will be in for a surprise to learn that SQL Server silently truncates the string and adds some leading characters.

Overall, always specify a length for the CHAR, VARCHAR, NVARCHAR and similar data types. It’s a good practice!


About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook

3 comments:

Aaron Bertrand said...

I blogged about this here:

http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length.aspx

Note the connect items as well:

http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=244395

http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=267605

Suprotim Agarwal said...

Nice post Aaron. They should make the length mandatory for varchar and similar dt's. Not sure what was the thought process of not doing it in the first place.

Nilesh said...

I've blogged about this few days back here :

http://nilthakkar.blogspot.com/2012/01/specify-size-for-character-data-type.html