Table Attributes & Strange Behavior 1

[MKVAB]

Okay, this is very strange...

I was attempting an insert into a table. The insert fails because the value I'm inserting is too long for, say Field1. The string I was inserting into Field1 was 55 characters long. I did sp_help [TableName] to see the length of Field1 and it said it was varchar(100). Hummm….

So, I run sp_help again just to make sure I'm not delirious, and sure enough it says the length is 100. I do: select * from syscolumns where name = Field1 and the length listed in syscolumns is also 100. Then I look at the table design in Enterprise Manager and the field length says 50.

This table has not been altered recently. Anybody know why the system tables are reporting different attributes values than are in the table?

Do I need to run some sort of statistics to update the system tables?

Has anybody found similar inconsistencies?

If anybody knows why this is happening, or how I can sync the system tables up, please let me know!

Thank you!
-MK

 

[JayKusch]

Your you looking at duplicate tables in different DBs? If not, you may need to run a DBCC CheckTable against your systems DBs just to make sure there is not corruption involved.

Thanks

J. Kusch

 

[nigelrivett]

Is it an nvarchar? 100 bytes for 50 chars.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[MKVAB]

Jay, thanks for the response! It is the same database.

Nigel, YES! I wasn't paying attention to the data type before. That explains it. Thank you!

Explanation: The field is an nvarchar field (not sure why it was created as a unicode data type, but it is). A unicode data type takes up twice as much storage space as non-unicode data types. So, from sp_help we're seeing the length in bytes that is available, but because it is Unicode the length allowed is half the length in bytes.

Question: for some reason, most fields in our tables are using unicode data types. At the time the DB was developed this was thought to be the best practice. However, knowing the field takes up twice as much space, why would one use unicode over non-unicode? I have read BOL about "Using Unicode Data" but I'm still a little fuzzy on it.


Thanks again Jay and Nigel.
-MK