optimal varchar sizes

Question

optimal varchar sizes

Jeremy-475548

Hall of Fame

Points: 3065
More actions
December 5, 2008 at 12:18 pm

#196707

I was looking at Microsofts aspnetdb that gets created when the ASP.NET 2.0 membership features are used. I noticed that all of the nvarchar sizes they used are powers of 2 (16,128, 256, 1024).
I was wondering if that was some sort of best practice or just a hyper-optimization technique that we can safely forget about.
Thoughts?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 1

It's not anything I've heard of before.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gift Peddie SSC Guru Points: 73570 More actions · Answer 2

There is a valid technical reason for those sizes because the .NET Char's size is not greater than 65,535 which is an unsigned integer created to render character and the SQL Server 2005 and below Char and Varchar maps to bytes in .NET so these sizes means the database can be used by developers in all countries and still cover languages like Chinese, Japanese and Korean without issues. Microsoft have made changes in the SQL Server Nchar and Nvarchar that it now maps to the .NET char without any adjustments. I actually asked the SQL Server team to make sure Nvarchar mapping to the .NET char in bytes was not dropped.

One more thing optimization was not the goal being able to use that database to store user information correctly in all countries was the goal and it works.

Kind regards,
Gift Peddie

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 3

Ok then... anyone anywhere got any consistant demonstrable code of any type that shows any consistant performance increase using any power-of-2 length for any Varchar sitiuation in any table? Because until someone posts it, I'm not buying it. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gift Peddie SSC Guru Points: 73570 More actions · Answer 4

One more thing optimization was not the goal being able to use that database to store user information correctly in all countries was the goal and it works.

I have covered that optimization was not the goal being able to render more than 2000 character alphabets like Chinese, Japanese and Korean and render right to left Arabic and Thai was the goal for those definition. Here is the SQL Server division chart for data types conversion the SQL Server Char to ADO.NET and .NET Base Class none.

http://msdn.microsoft.com/en-us/library/ms131092.aspx

Kind regards,
Gift Peddie

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 5

Sorry, Gift Peddie... Wasn't directed at you or I'd have used your post as a quote. No... I've heard this myth about using powers of 2 for varchar lengths before, but not a single scrap of code to prove it in a demonstrable fashion. It was a challenge to the general public... I took no exception to what you stated. Sorry for the confusion.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gift Peddie SSC Guru Points: 73570 More actions · Answer 6

Hi Jeff,

My bad I also would like to take some code for a test drive. he he

:Whistling:

Kind regards,
Gift Peddie