NVARCHAR - Data Type

Question

Post reply

NVARCHAR - Data Type

sudhirnune

SSCrazy

Points: 2342
More actions
January 26, 2012 at 11:18 pm

#267330

Hi All,

A typical issue found in SQL Server 2008, not sure if any one observerd.

I created a table with followign SQL:

CREATE table TESTER (COL_A NVARCHAR(100), COL_B INT, COL_C NUMERIC(18,8));

When we export the Create Script by Right Click on the table > "Script Table To" > "Create To" > "new Query Window"> Script is As Below

CREATE TABLE [dbo].[TESTER](

[COL_A] [nvarchar](100) NULL,

[COL_B] [int] NULL,

[COL_C] [numeric](18, 8) NULL

) ON [PRIMARY]

When I try Qurieng the meta Data table "SYS.ALL_COLUMNS" usign the following Script

Select * from sys.all_columns where OBJECT_NAME(object_id) = 'Tester'

Result is:

name system_type_id user_type_id max_length precision scale collation_name

COL_A 231 231 200 0 0 Latin1_General_CI_AS

COL_B 56 56 4 10 0 NULL

COL_C 108 108 9 18 8 NULL

Which shows me the Column Size where NVARCHAR is Declared is shown Double (200 Instead on 100).

Can some body Explain why is this issue occuring, I is screwing teh Proc's which are written based on the Meta Data, for Fetching the Create Script of a table.

please need help.....!

Thanks,

Sudhir Nune.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259015 More actions · Answer 1

100 is the number of characters you can store, not the size in bytes.

Since you are using a unicode column (datatype preceded by n), you are using twice as much storage.

I also think 200 is still not correct, as you are using a variable length column, an additional 2 bytes are used, so the total should actually be 202.

http://msdn.microsoft.com/en-us/library/ms186939.aspx

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Paul White SSC Guru Points: 150457 More actions · Answer 2

Paul White

SSC Guru

Points: 150457

January 26, 2012 at 11:59 pm

#1439154

sudhirnune (1/26/2012)

Which shows me the Column Size where NVARCHAR is Declared is shown Double (200 Instead on 100).

Unicode data requires 2 bytes per character.

See the documentation: Storage and Performance Effects of Unicode

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

sudhirnune SSCrazy Points: 2342 More actions · Answer 3

sudhirnune

SSCrazy

Points: 2342

January 27, 2012 at 12:30 am

#1439171

Thanks Paul,

But the Query / Columns in All_Columns is meant to show the Size of the Column and not the Size,

I that case the Size is defined as 100, should be displayed as 100 Correct.

Koen Verbeeck SSC Guru Points: 259015 More actions · Answer 4

sudhirnune (1/27/2012)

Thanks Paul,

But the Query / Columns in All_Columns is meant to show the Size of the Column and not the Size,

I that case the Size is defined as 100, should be displayed as 100 Correct.

The MSDN page for that system view (table?) explains clearly:

Maximum length (in bytes) of the column.

sys.all_columns (Transact-SQL)

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

SpringTownDBA SSCertifiable Points: 6806 More actions · Answer 5

SpringTownDBA

SSCertifiable

Points: 6806

January 27, 2012 at 12:36 am

#1439173

per BOL page on sys.all_columns:

Maximum length (in bytes) of the column.

2 bytes = 1 NCHAR

200 bytes = 100 NCHAR's

Paul White SSC Guru Points: 150457 More actions · Answer 6

sudhirnune (1/27/2012)

But the Query / Columns in All_Columns is meant to show the Size of the Column and not the Size,

I that case the Size is defined as 100, should be displayed as 100 Correct.

No it is bytes as Koen has said. Also, why are you using sys.all_columns? This includes internal system objects, which you should not care about. Use sys.columns instead. The max_length column is still in bytes, not characters of course, but at least you will only be looking at user objects.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150457 More actions · Answer 7

Koen Verbeeck (1/26/2012)

I also think 200 is still not correct, as you are using a variable length column, an additional 2 bytes are used, so the total should actually be 202.

The view shows the length of the column in bytes, it does not include storage-engine level overheads like the 2 byte pointer you are referring to. It doesn't reflect compression either; it is simply the byte size of the column data itself.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Koen Verbeeck SSC Guru Points: 259015 More actions · Answer 8

SQL Kiwi (1/27/2012)

Koen Verbeeck (1/26/2012)

I also think 200 is still not correct, as you are using a variable length column, an additional 2 bytes are used, so the total should actually be 202.

The view shows the length of the column in bytes, it does not include storage-engine level overheads like the 2 byte pointer you are referring to. It doesn't reflect compression either; it is simply the byte size of the column data itself.

Ah ok, thanks for the clarification.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

sudhirnune SSCrazy Points: 2342 More actions · Answer 9

Found Some Answer which is Explaining teh Casue but Defeting the Purpose Max Length Column in the Sys.all_columns:

The value from sys.all_columns shows to you a size in bytes, while when you specifing the length in create table, you specify it in number of symbols. As for unicode type, 2 bytes stands for one character, you got 2(bytes)*100(char-s)=200 bytes.

Koen Verbeeck SSC Guru Points: 259015 More actions · Answer 10

sudhirnune (1/27/2012)

Found Some Answer which is Explaining teh Casue but Defeting the Purpose Max Length Column in the Sys.all_columns:

The value from sys.all_columns shows to you a size in bytes, while when you specifing the length in create table, you specify it in number of symbols. As for unicode type, 2 bytes stands for one character, you got 2(bytes)*100(char-s)=200 bytes.

Indeed, that's what we have been saying for the past hours. (at least 4 times)

The purpose of the max length column is not defeated. It does what it is supposed to do: show the length in bytes. You just gave it another interpretation and blamed the result on Microsoft.

If you really need the length in characters, check out INFORMATION_SCHEMA.COLUMNS.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Paul White SSC Guru Points: 150457 More actions · Answer 11

I'm not sure you are reading our replies, but this could help you:



CREATE TABLE dbo.Test

(

    Column1     nvarchar(25) NULL,

    Column2     varchar(50) NULL

);



-- Documented

SELECT

    c.TABLE_CATALOG,

    c.TABLE_SCHEMA,

    c.TABLE_NAME,

    c.COLUMN_NAME,

    c.ORDINAL_POSITION,

    c.DATA_TYPE,

    c.CHARACTER_MAXIMUM_LENGTH,

    c.CHARACTER_OCTET_LENGTH

FROM INFORMATION_SCHEMA.COLUMNS AS c

WHERE

    c.TABLE_NAME = N'Test';

-- Undocumented

SELECT

    COLUMNPROPERTY(OBJECT_ID(N'dbo.Test'), N'Column1', 'CharMaxLen'),

COLUMNPROPERTY(OBJECT_ID(N'dbo.Test'), N'Column2', 'CharMaxLen');

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi