In SQL, if you declare a string in any of the four formats,
NVARCHAR, without specifying its length, the string is given the length of one character. If you coerce a string, using
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
Problems with forgetting the string length
Normally, but not always, your mistake will be quickly apparent.
CREATE TABLE dbo.Deleteme (MyString VARCHAR NOT null) INSERT INTO dbo.Deleteme (MyString) VALUES ('first'),('Second'),('Third') /* Msg 8152, Level 16, State 14, Line 5 String or binary data would be truncated. The statement has been terminated. */ drop TABLE dbo.Deleteme
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.
DECLARE @MyString VARCHAR ='this ends badly' SELECT @MyString --no error. Produces value 't'
The same is true of parameters in a function or procedure …
CREATE FUNCTION dbo.deleteme (@param1 VARCHAR, @param2 CHAR) RETURNS VARCHAR(100) AS BEGIN RETURN @param1 + @param2; END; Go SELECT dbo.deleteme('this is likely to','go very badly') --returns 'tg'
And in the returned value of a function:
CREATE FUNCTION dbo.deletemeAlso (@param1 VARCHAR(100), @param2 Varchar(100)) RETURNS VARCHAR AS BEGIN RETURN @param1 + @param2; END; GO SELECT dbo.deletemeAlso('this is likely to','go very badly') --returns 't'
If you forget to declare the variable string length during a
CAST operation, the behavior is different. You get a string of thirty characters.
SELECT Convert(nVARCHAR,'12345678901234567890123456789012345678901234567890' ,113) /* ------------------------------ 123456789012345678901234567890 (1 row affected) */
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.
SELECT Convert(CHAR,GetDate() ,113) SELECT system_type_name FROM sys.dm_exec_describe_first_result_set( 'SELECT Convert(VARCHAR,GetDate() ,113)', NULL, 1) --varchar(30)
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:
--create an alias data type CREATE TYPE dbo.String FROM VARCHAR(30) NOT NULL GO DECLARE @MyString String ='this ends well' SELECT @MyString ------------------------------ --this ends well
However, the downside is that, for some reason, you can’t use it in either
CONVERT operations on the grounds that it isn’t a defined system type.
SELECT Convert(dbo.String,GetDate() ,113) SELECT Cast(GetDate() AS String) /*Msg 243, Level 16, State 2, Line 40 Type dbo.String is not a defined system type. Msg 243, Level 16, State 2, Line 41 Type String is not 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.
SELECT name FROM (VALUES --one to twenty ('Yan'), --in Lincolnshire dialect ('Tyan'), ('Tethera'), ('Methera'), ('Pimp'), ('Sethera'), ('Lethera'), ('Hovera'), ('Dovera'), ('Dik'), ('Yanadik'), ('Tyanadik'), ('Tetheradik'), ('Metheradik'), ('Bumfitt'), ('Yanabumfit'), ('Tyanabumfitt'), ('Tetherabumfitt'), ('Metherabumfitt'), ('Giggot'))f(name)
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
INTO with a temporary table…
SELECT name INTO #MyTemp FROM (VALUES --one to twenty ('Yan'), --in Lincolnshire dialect ('Tyan'), ...etc ... ('Giggot'))f(name)
…and then checking on the width of the column created…
SELECT system_type_name FROM sys.dm_exec_describe_first_result_set('SELECT * FROM #MyTemp',NULL,1) /*system_type_name varchar(14) */
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
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
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.
SELECT TOP 1000 * FROM bigdirectory
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
VALUES statement, which means its subject to the 1000-row limit. If there are more, then Prompt switches to using individual
We’ll stick to 1000-rows in this case, and adapt this code slightly to use
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 name + ' ' + system_type_name + CASE WHEN is_nullable = 1 THEN ' NULL' ELSE ' NOT NULL' END FROM sys.dm_exec_describe_first_result_set(' SELECT * FROM #temptable', NULL, 1);
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):
id int NOT NULL Name varchar(37) NOT NULL Address1 varchar(34) NULL Address2 varchar(23) NULL Town varchar(22) NOT NULL City varchar(19) NOT NULL County varchar(18) NOT NULL Postcode varchar(8) NOT NULL Region varchar(22) NOT NULL BusinessType varchar(55) NULL Leads varchar(17) NULL Phone varchar(12) NULL Fax varchar(12) NULL Website varchar(56) NULL
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).
CREATE TABLE BusinessDirectory ( id INT NOT NULL, Name VARCHAR(40) NOT NULL, Address1 VARCHAR(40) NULL, Address2 VARCHAR(50) NULL, Town VARCHAR(30) NOT NULL, City VARCHAR(20) NOT NULL, County VARCHAR(20) NOT NULL, Postcode VARCHAR(8) NOT NULL, Region VARCHAR(30) NOT NULL, BusinessType VARCHAR(60) NULL, Leads VARCHAR(20) NULL, Phone VARCHAR(15) NULL, Fax VARCHAR(15) NULL, Website VARCHAR(60) NULL ); INSERT INTO BusinessDirectory (id, Name, Address1, Address2, Town, City, County, Postcode, Region, BusinessType, Leads, Phone, Fax, Website) SELECT id, Name, Address1, Address2, Town, City, County, Postcode, Region, BusinessType, Leads, Phone, Fax, Website FROM BigDirectory;
If you dislike this method you can, of course, get the maximum actual lengths easily from the original table in this case.
SELECT Max(Len(Name)), Max(Len(Address1)), Max(Len(Address2)), Max(Len(Town)), Max(Len(City)), Max(Len(County)), Max(Len(Postcode)), Max(Len(Region)), Max(Len(BusinessType)), Max(Len(Leads)), Max(Len(Phone)), Max(Len(Fax)), Max(Len(Website)) FROM BigDirectory;
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.
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
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
VARCHAR, it produces one with a more sensible length of 30.
Always specify a length to any text-based datatype such as
VARCHAR. Don’t over-use the
MAX specification either as the resulting column then can’t be indexed and comes with performance baggage.