Length vs Precision

Question

When I describe a table with nvarchar data type, what's the difference between Precision and Length? I see that Length is always the double. For example the values is nvarchar(64), the precision is 64 and the length is 128.

CREATE TABLE T(X nvarchar(64))
EXEC sp_columns 'T'

Answer 1

Precision has no meaning for text data.

As for the Length property, I think you confuse it with the Size reported by SQL Server Management Studio, which is the size of a column in bytes. The Length of an nvarchar(64) column is 64 while Size is 128.

The size of unicode types (nchar, nvarchar) is double the number of characters because Unicode uses two bytes for each character.

You can get these values using the LEN function to get the number of characters and the DATALENGTH function to get the number of bytes, eg.

select len(N'some value'), datalength(N'some value')

which returns 10 20

EDIT

From your comments I see you use sp_columns to get at the table's schema info. You shouldn't use any of the catalog stored procedures and use the catalog views instead.

As the documentation states, catalog stored procedures are used to support ODBC applications, hence their results are limited and may need interpretation, as you found out. sp_columns doesn't differentiate between character and data length for example.

Schema views like those in the INFORMATION_SCHEMA or sys schemas return detailed and unambiguous information. For example, INFORMATION_SCHEMA.COLUMNS returns the character lnegth in CHARACTER_MAXIMUM_LENGTH and byte size in CHARACTER_OCTET_LENGTH. It also includes collation and character set information not returned by sp_columns.

The INFORMATION_SCHEMA views are defined by ISO so they don't include some SQL Server-specific info like whether a column is a computed columns, stored in a filestream or replicated. You can get this info using the system object catalog views like sys.columns

Answer 2

NVARCHAR doesnt have precision. Precision is used for decimal. And length is the character length.

nvarchar [ ( n | max ) ]

Variable-length Unicode string data. n defines the string length and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size, in bytes, is two times the actual length of data entered + 2 bytes. The ISO synonyms for nvarchar are national char varying and national character varying.

From the source:-

Precision is the number of digits in a number.

Length for a numeric data type is the number of bytes that are used to store the number. Length for a character string or Unicode data type is the number of characters