Column Properties Different Length

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Column Properties Different Length

Author

Topic

andriancruz
Starting Member

38 Posts

Posted - 2009-06-11 : 07:05:22

Hi Everyone,

I'm not really sure if this is a issue in SQL 2005. What i did is i want to see the data type and length of of each column in my table. From query analyzer I highlighted the table name, then press ALT+F1, then they show all the properties of the table. For example.

Column_name Type Computed Length
ItemTypeCode nvarchar no 200
Description nvarchar no 200

But when I use the the GUI to check the properties I noticed the length is only 100.

Is anyone can explain why they display different. Thank you in advanced.

Regards,
Andrian

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-11 : 07:14:10

A nvarchar column stores 2 bytes for every character.

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-11 : 07:15:53

Compare the CHARACTER_MAXIMUM_LENGTH and CHARACTER_OCTET_LENGTH.

E 12°55'05.63"
N 56°04'39.26"

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-11 : 07:20:12

DATALENGTH shows the size in bytes.
LEN shows the size in characters.
Example:



DECLARE @foo TABLE (
	[valA] VARCHAR(50)
	, [valB] NVARCHAR(50)
	)

INSERT @foo ([valA], [valB])
      SELECT 'a', 'a'
UNION SELECT 'b', 'b'
UNION SELECT 'bar', 'bar'

SELECT
	[valA]
	, LEN([valA])
	, DATALENGTH([valA])
	, [valB]
	, LEN([valB])
	, DATALENGTH([valB])
FROM
	@foo

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

andriancruz
Starting Member

38 Posts

Posted - 2009-06-11 : 07:26:45

Thank you very much for the reply. now I understand. More power to all of you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources