SQL Server Optimization Questions

[Anti-Rich]

hi all,

i have a couple of tables in my database, one which has over 40 columns and one which has over 170 columns (gosh i hate surveys! ). are there any pitfalls or traps that i should be aware of when creating this many columns for a table, or any pitfalls or traps for coding the subroutines that control the inserts/updates/selects? ie. general performance problems, or incorrect coding etc.

i have never dealt with this many columns, so i dont want the table to fall down and kill itself because i was ignorant from the start.

cheers all

adam

 

[cjard]

no, and this is the way it must be, because each of those 170 columns is related to that one survey, so with the survey primary key we can get all the answers the applicant submitted

There is no problem with doing this, just dont make everything a varchar2(1000) - if its a multi choice questionnare and theres a column for every answer A B C D then make them ALL char(1), not even varchar, because they arent going to vary length. this will help sqls make some optimiastions about record length and space savings..

 

[Anti-Rich]

cheers cjard,

i have another question, how do i know what do choose between nchar, nvarchar, ntext and text? what type best fits what situation? are there any varying performance hits for one type to another?

cheers
adam

 

[cjard]

char = fixed field length, small amounts
varchar = varying field length, larger amounts
text = varying length, very large amounts

nXXXX = unicode version of above

in your case, use char(1) if its a multi choice or booleans if it is yes/no


why use char(1) ? well all your questions are supposed to be answered so you dont really need the varying lenght and because, with all-fixed-length records sqlserver can more easily calculate where they start and end, retrieval is supposed to be faster..