SQL Server :: Why does sp_help and Design View column length have variations in results?

Well, the question of Why does sp_help and Design View column length have variations in results? has been a Million Dollar question for my SQL geek friend in office. She was confronted with this issue where the column length appears differently when sp_help is used and the correct column length is visible in design view.

Being a SQL geek herself, she usually only uses command line commands (like sp_help) to get the information she needs - but this was a very hard nut for her to crack. Well, she did crack it last evening and here's the detailed explanation on why the variations appear. Hope this information is useful for others who are struck with a similar investigation.

When we use sp_help to check the properties of the table, the length of every column which is of Unicode data type (Nchar, Nvarchar, etc.) is doubled than the original column width which can be seen in design view.

For eg. If we have a column whose data type is Nvarchar(25). When we use sp_help for this table, the column length appears 50. But when we open the table in design view it shows 25.

This is because Unicode data type takes 2 bytes for storing data. And sp_help uses int to display the length of the column.

Hope that added some gyan to you too.