Field size versus actual data size

Question

Field size versus actual data size

SQL33

SSCrazy

Points: 2044
More actions
December 10, 2010 at 8:23 am

#232360

When designing a table is the size of the field relevant to database performance, or is it more dependent on the actual size of the resulting data which ends up in the field?
Example.. if you set the datatype for a field as Varchar (1024) but the longest data is only 19 characters long, what is the impact on performance of the field size setting?

Viewing 4 posts - 1 through 3 (of 3 total)

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

AFAIK, varchars under (8000) chars are handled the same; no performance difference when they are included with the rest of the data you are selecting...unless you are SEARCHING tha column with a WHERE statement;

if you had to search for a value in the column, it will require a tablescan every time. THAT slows things down.

to improve that issue, you would want to add an index, right> and that is where you need to make the smart decision:

the max width of an index is ~900 chars...so that means you can create an index on a varchar(900), but not one on a varchar(1024);

so if the column is ever going to be used in a WHERE statement, you want to do your best to keep them small (or add full text indexing, and all that implies)

keep in mind that indexes based on smaller columns are more tightly packed into the pages of memory, so they tend to be faster...instead of reading 10 pages, you might read only 1 or two for a specific value that was indexed...if the index was based on varchar(20) instead of varchar(900)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!