Using a Variable-length Datatype Without Explicit Length: The Whys and Wherefores (BP007/8) | Redgate (2024)

Guest post

• SQL Prompt
• SQL Code Analysis

If you declare a variable-length string , or coerce a string, without specifying its length, you can fall foul of ‘silent’ string truncation. Some developers resort to using the (MAX) specification, which is a mistake too. Phil Factor explains the dangers and then offers a workaround for the problem, when you're importing text and simply don't know the correct length of each string.

In SQL, if you declare a string in any of the four formats, CHAR, NCHAR, VARCHAR or NVARCHAR, without specifying its length, the string is given the length of one character. If you coerce a string, using CAST or CONVERT, and make the same mistake, it gets a length of thirty characters. Why one and thirty? Historical reasons only, but other RDBMS react in similar ways.

In either case, you can get yourself into trouble with ‘silent’ string truncation, so SQL Prompt has some built-in code analysis rules to warn you if you forget to specify string length both during variable-length string declaration (BP007) and coercion (BP008).

Some developers assume that, if they leave the length out, SQL Server will work out what they need. This isn’t as far-fetched as it might seem, because it does so in other contexts, as I’ll go on to demonstrate. Often, the context for this mistake is when you’re importing from a data source where you don’t know the right string length for the columns, and you need to create the destination table. Alternatively, you might end up using MAX as the length, which brings troubles of its own.

Later in the article, I’ll show you a handy trick to get around this. It uses Prompt’s Script as Insert feature to script out the first 1000 rows of a table, and then we persuade SQL Server to assign the correct datatypes and string lengths, by importing the data via a table-value constructor (TVC) within a SELECT INTO statement.

Problems with forgetting the string length

Normally, but not always, your mistake will be quickly apparent.

This can get more unpleasant if you do the same with a variable, because the string gets truncated to a single character, but there is no error.

The same is true of parameters in a function or procedure …

And in the returned value of a function:

If you forget to declare the variable string length during a CONVERT or CAST operation, the behavior is different. You get a string of thirty characters.

Conventional wisdom at the time said that most database strings were less than thirty characters in length. Even a string that is inevitably less than thirty characters gets the treatment.

I’m not sure why anyone would want to use a VARCHAR without specifying its length. It may be a habit caught from procedural coding. Things are different for any RDBMS; you either ensure that strings are stored economically or just airily wave your hand and consign the string to a (MAX) length specification, with all the indexing and performance compromises that entails (these datatypes cannot be specified as index key columns).

Using an alias data type

If you need a default string length as part of your design, you can create an alias data type. This is much safer:

However, the downside is that, for some reason, you can’t use it in either CAST or CONVERT operations on the grounds that it isn’t a defined system type.

Using a Table-Value Constructor to assign reasonable string lengths during data import

SQL Server can do much better than shrug and give up like this. When it needs to be, it can be very clever. It can, for example, work out the length of the datatype and its nullability in a Table-Value Constructor (TVC) statement like this.

If you SELECT INTO a table using this TVC, you will be able to see that it has created a VARCHAR column with the length of the longest strings in the values within the column (in this cas, ‘Tetherabumfitt’ and ‘Metherabumfitt’; fourteen characters).

We can verify this easily by SELECT INTO with a temporary table…

…and then checking on the width of the column created…

This suggests that if you need to import from an external text-based source, which contains a tediously-wide table with a lot of strings, then the best way to ensure that you get reasonable-length string datatypes is to use a TVC.

The TVC has a limit of 1000 rows when used in an INSERT INTO…VALUES statement, and you’ll see Error 10738 if you exceed it. However, there is no limit that I can detect when using a TVC in a SELECT INTO statement that uses VALUES, like the one illustrated above.

Let’s try it. Here is a first import of a business directory, spoofed with SQL Data Generator of course. If you want to play along, you can download the build script and .sqlgen file from my Table does not have clustered index (BP021) article. To demonstrate this, we get the first thousand rows into the grid view of SSMS.

Now, if you have SQL Prompt, you’ll be bobbing up and down in your chair whilst reading this, because you have a rather cute advantage here. Click the top left square of the grid to highlight the whole lot then right-click and select the Script as Insert option.

SQL Prompt uses a TVC in an INSERT INTO <tablename> VALUES statement, which means its subject to the 1000-row limit. If there are more, then Prompt switches to using individual INSERT statements.

We’ll stick to 1000-rows in this case, and adapt this code slightly to use SELECT INTO with a temporary table, from a TVC, thereby escaping the 1000-row limit:

…and so on until…

Execute the above code, and let’s use the handy sys.dm_exec_describe_first_result_set DMV again, to get the metadata of the resultselt for a SELECT * FROM #temptable query:

It’s specified the datatypes, lengths and nullability for each column, as follows (for this to work well, you’ll need some long strings and nulls in your sample rows):

I’ve tested this up to 10,000 rows but others have done more. Once you have the right datatypes, length and nullability for all the columns, you can do a bit of a rounding up of the lengths to allow for outliers, create a good tidy table and then use it to import the entire data (four million rows in this experiment).

If you dislike this method you can, of course, get the maximum actual lengths easily from the original table in this case.

However, it is trickier to detect whether the columns are nullable.

When you have imports from external text-based sources, the TVC technique could be handy. The main point, however, is to show you that SQL Server can be very good at detecting the right length and nullability of a string datatype, if it wants to be.

Conclusions

SQL Server requires you to specify the lengths of string datatypes. You might assume that, because it doesn’t object when you leave out the length, it obligingly detects the length for you. No, not at all. If you declare a column as a CHAR, NCHAR, VARCHAR or NVARCHAR without a length, SQL Server reads that as a length of 1. This is such a silly length for any variable-length string that it amounts to unhelpful nonsense, but we are stuck with it.

If you define a variable string without a length, it will exact an even more horrible vengeance on you. Not only will it assume that it has a length of 1 character, it will also silently and discreetly reduce every value assigned to it to one character. SQL Server must have been in a silly mood when it decided on reducing a varchar to one character if you didn’t specify the length, because if you make the same mistake when casting a datatype to an NVARCHAR or VARCHAR, it produces one with a more sensible length of 30.

Always specify a length to any text-based datatype such as NVARCHAR or VARCHAR. Don’t over-use the MAX specification either as the resulting column then can’t be indexed and comes with performance baggage.

Write, format, and refactor SQL effortlessly

Find out more