• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 539
  • Last Modified:

in the table design I have declared datalength nvarchar as 50 but the query show 100 - why?

I am using ms sql 8
in the table design, I have declared
Job      nvarchar(50)      Checked


SELECT table_name=sysobjects.name,
         column_name=syscolumns.name,
         datatype=systypes.name,
         length=syscolumns.length
    FROM sysobjects
    JOIN syscolumns ON sysobjects.id = syscolumns.id
    JOIN systypes ON syscolumns.xtype=systypes.xtype
   WHERE sysobjects.xtype='U' and sysobjects.name='AceData' and syscolumns.name='Type'
ORDER BY sysobjects.name,syscolumns.colid

table_name      column_name      datatype      length
AceData      Type      nvarchar      100
AceData      Type      sysname      100
0
goodk
Asked:
goodk
1 Solution
 
Prafulla MaharjanCommented:
In vharchar u declared u declared as 50
but in the table width u just check whether u have 100 or not.. It is also depends on the table on the  width...
0
 
ssisworoCommented:
use the length of the column "syscolumns.prec" information,
whereas "syscolumns.length" column is used to measure the amount of storage.

SELECT table_name=sysobjects.name,
         column_name=syscolumns.name,
         datatype=systypes.name,
         length=syscolumns.prec
    FROM sysobjects
    JOIN syscolumns ON sysobjects.id = syscolumns.id
    JOIN systypes ON syscolumns.xtype=systypes.xtype
   WHERE sysobjects.xtype='U' and sysobjects.name='AceData'
ORDER BY sysobjects.name,syscolumns.colid
0
 
LowfatspreadCommented:
nvarchar uses 2 bytes per character of storage...

so your nvarchar(50)  will actually use 100 bytes to store the data

datalength(columnname)  gives the numbers of bytes used for data by a column/expression

length(columnname) gives the number of characters used for data by the column/expression ignoring trailing spaces...

a "Varchar" datatype will also typically use and additional 2bytes to store the lenght of the data its is storing.. which is not included in the "displayed" values

0
 
goodkAuthor Commented:
works like a charm - thanks
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now