January 26, 2012 at 11:18 pm
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:
namesystem_type_iduser_type_idmax_lengthprecisionscalecollation_name
COL_A23123120000Latin1_General_CI_AS
COL_B56564100NULL
COL_C1081089188NULL
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.
January 26, 2012 at 11:56 pm
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
January 26, 2012 at 11:59 pm
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
January 27, 2012 at 12:30 am
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.
January 27, 2012 at 12:35 am
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
January 27, 2012 at 12:36 am
per BOL page on sys.all_columns:
Maximum length (in bytes) of the column.
2 bytes = 1 NCHAR
200 bytes = 100 NCHAR's
January 27, 2012 at 12:57 am
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.
January 27, 2012 at 12:59 am
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.
January 27, 2012 at 1:03 am
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
January 27, 2012 at 2:04 am
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.
January 27, 2012 at 2:15 am
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
January 27, 2012 at 2:24 am
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');
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply