Why are these different?

Why are these different?

Post by Wayne Wenger » Tue, 13 Aug 2002 21:41:13



Using SQL Server 2000. When I open Enterprise Manager and select "tables"
and then right click on a table and choose "design" I see the field types
and sizes. These match the sizes in the Access 2000 table from which the
table was imported. If I open Query analyzer and run the query "sp_help
'table_name'" all of the text fields (nvarchar is SQL Server) are shown with
a length double what they should be?

Which size is true? Why are they different?

--
------------------------------------
Wayne Wengert

 
 
 

Why are these different?

Post by Jasper Smit » Tue, 13 Aug 2002 22:42:18


Enterprise Manager uses sp_MShelpcolumns to return the column info
and not sp_help. sp_help uses the length in syscolumns.length which is in
bytes wheras sp_MShelpcolumns converts it into number of characters for
text strings and divides by 2 if its nchar or nvarchar. Thus sp_help is
showing
you the byte length and sp_MShelpcolumns  is showing you the length in
characters
To illustrate

select len(N'12345') as 'Charlength',
datalength(N'12345') as 'Bytelength'

The below is the note at the top of sp_MShelpcolumns which explains :

   /* For non-string columns, sp_MShelpcolumns returns the length in
syscolumns.length, */
   /* which is defined in BOL as "maximum physical storage length from
systypes".       */
   /* For string columns (including types based on string types),
sp_MShelpcolumns      */
   /* returns this maximum length in characters (i.e. it returns
syscolumns.length      */
   /* adjusted to whether the column is based on char or nchar).
*/

HTH
Jasper Smith


> Using SQL Server 2000. When I open Enterprise Manager and select "tables"
> and then right click on a table and choose "design" I see the field types
> and sizes. These match the sizes in the Access 2000 table from which the
> table was imported. If I open Query analyzer and run the query "sp_help
> 'table_name'" all of the text fields (nvarchar is SQL Server) are shown
with
> a length double what they should be?

> Which size is true? Why are they different?

> --
> ------------------------------------
> Wayne Wengert



 
 
 

Why are these different?

Post by Jasper Smit » Tue, 13 Aug 2002 22:49:48


And to answer you first question, both are correct as long
as you realise that sp_help returns length in bytes and that
Enterprise Manger returns length in characters :-)
This is because the nvarchar uses 2 bytes to store each character
using the UNICODE UCS-2 character set

HTH
Jasper Smith


> Using SQL Server 2000. When I open Enterprise Manager and select "tables"
> and then right click on a table and choose "design" I see the field types
> and sizes. These match the sizes in the Access 2000 table from which the
> table was imported. If I open Query analyzer and run the query "sp_help
> 'table_name'" all of the text fields (nvarchar is SQL Server) are shown
with
> a length double what they should be?

> Which size is true? Why are they different?

> --
> ------------------------------------
> Wayne Wengert


 
 
 

Why are these different?

Post by Wayne Wenger » Tue, 13 Aug 2002 23:44:13


Thanks Jasper;

That explains it.

Wayne


> And to answer you first question, both are correct as long
> as you realise that sp_help returns length in bytes and that
> Enterprise Manger returns length in characters :-)
> This is because the nvarchar uses 2 bytes to store each character
> using the UNICODE UCS-2 character set

> HTH
> Jasper Smith



> > Using SQL Server 2000. When I open Enterprise Manager and select
"tables"
> > and then right click on a table and choose "design" I see the field
types
> > and sizes. These match the sizes in the Access 2000 table from which the
> > table was imported. If I open Query analyzer and run the query "sp_help
> > 'table_name'" all of the text fields (nvarchar is SQL Server) are shown
> with
> > a length double what they should be?

> > Which size is true? Why are they different?

> > --
> > ------------------------------------
> > Wayne Wengert