CODEWITHSUNDEEP
sqlsql-serverwindows
Andrew
Andrew

Reputation:

SQL / WINDOWS - optimal char lengths - DOES IT MATTER

Three little questions for the clever people of stackoverflow....

WINDOWS:

SQL:

Thanks for any help given.

Upvotes: 1

Views: 406

Answers (8)

Jim
Jim

Reputation:

CHAR is fixed length and will take exactly what you specify as the length regardless of whether you fill it or not.

VARCHAR is variable length and only takes what you put in it, however having a data model with the freedom to blast in 4000 characters when the real world application requires 30 is asking for trouble.

Do field sizes matter? On small to medium systems probably not because the hardware is sufficient to carry a poor design. On large high traffic high concurrency systems every byte counts.

Upvotes: 0

gbn
gbn

Reputation: 432431

Byte 0 = length, 8 bit unsigned so allows 255 characters. Zero = empty string. The other 255 are, er, the 255 limit.

char(4000) is very different to char(10). SQL Server always pads the data if it's not null.

There is no difference between varchar(4000) and varchar(10), unless you want to index it, where you have a limit of 900 bytes

SQL Server before 7 had varchar limit of 255 and no unicode support. That was a pain...

Upvotes: 1

Charles Bretana
Charles Bretana

Reputation: 146559

Lots of things used to be a max of 255, because that is the maximum value that an unsigned 1 byte ( 8-bit ) number can represent 11111111 = 255. to get to 256, you have to have 9 bits (1 0000 0000)

As to the second part I'm not sure, but the sql question is: it used to be an issue much more than it is today, What you specified controlled the maximum size of each row of data, and the Database engine would have to allocate enough space for each row to be able to store that maximum. So the values you entered affected how much space was allocated on disk for each record, and therefopre also affected the maximum number of records per "page" of disk space... The fewer the records per page, the more Disk I/O operations it takes to retrieve any specific number of records... As disk I/O is the overriding factor in Database performance, this had a major impact.

Nowadays, however, modern RDBMS systems are coded to optimize that problem away, I Think, by dynamically controlling how many records are actually stored on a page based on the data that is actually in the record instead of on the maximums you specify.

Upvotes: 2

Arjan Einbu
Arjan Einbu

Reputation: 13682

A varchar(256) will perform just the same as a varchar(8000) or a varchar(5). They are stored the same... (In MS SQL Server)

Upvotes: 0

dthrasher
dthrasher

Reputation: 41812

You'll want to give yourself considerably more room than 255 characters to store filenames in your database, if you're storing path information as well.

While the Windows shell has a maximum path of 255 characters, the NTFS file system actually supports filenames up to 32,000 characters for compatibility with UNIX.

It's trivially easy to fake out the Windows shell and trick it into storing paths/filenames that exceed 255 characters. Mapping a drive or sharing a folder can do it, for example.

I discuss this in further detail in this blog post on 256 character filenames should be enough for anybody.

Upvotes: 1

MyItchyChin
MyItchyChin

Reputation: 14031

SQL Server 2000 has a hard 8KB field, page and row size limit. In SQL Server 2005+ the 8KB limit on pages and fields still applies but rows can overflow (with dire consequences to performance of course).

I've been told by a few DBAs that ideal row sizes should be divisors of 8KB though none of them have ever been able to explain exactly how to calculate row sizes accurately.

Upvotes: 0

Bill
Bill

Reputation: 4585

1) see Charles's answer above

2) come one now. this one is too easy. Path != Filename

3) What matters is the relationship between page size and record size. If you use varchars and have lots of data changes where fields go from being short to long, SQL spends time moving records to different pages. If your char fields are really long such that not very many records fit on a page, it may hurt performance a bit. If the length of the data varies a lot, varchars will make better use of storage space. And they don't have those pesky spaces on the end of your data that you have to strip off all the time.

Upvotes: 3

mati
mati

Reputation: 5348

There are 256 possible values because the sequence starts with zero :)

Upvotes: 1

Related Questions