Why declaring VARCHAR without length is not a good practice?

 Posted by Niladri.Biswas on 5/16/2013 | Category: Sql Server Interview questions | Views: 1203 | Points: 40
Answer:

Server has some inconsistent rules about how long a string can be, depending on how the value is defined. Consider the following examples:

DECLARE @x CHAR = 'foo';

SELECT a = @x, b = CAST('foo' AS CHAR), c = CONVERT(CHAR, 'foo');

One would expect in all three cases to see 'foo' returned, but in fact the first column in the query returns only the letter 'f'. This is because when a CHAR-based variable is
declared without a length, the length becomes 1 (and this follows the ANSI standard).On the other hand, when we use CAST or CONVERT to specify that a string should be a
CHAR-based type, the length becomes 30. This behavior can also come into play when we create tables.If we create a stored procedure that accepts a parameter with the exact same type (VARCHAR with no length), there’s no error message, and the string is silently truncated and SQL Server quite happily puts the leading character into the column:

CREATE PROCEDURE dbo.x_insert

@y VARCHAR
AS
BEGIN
SET NOCOUNT ON;
INSERT dbo.x(y) SELECT @y;
END
GO
EXEC dbo.x_insert @y = 'foo';
SELECT Result = y FROM dbo.x;

Results:
Result
-
f

This means that it can take quite a bit of manual effort, or maybe even luck, to discover that the strings we are passing into the stored procedure aren’t remaining
intact when they get written to the table. This problem goes away if we always declare a length for the CHAR-based columns.


Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Login to post response