Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
 Posted - 2003-08-21 : 10:18:42
|
Hi all,We don't have a design forum, so I'm posting this here.What rules do you use when creating a table. Let's say your given a 3rd normal form ER model, whch for arguments sake, has 3 tables, nicely linked with RI.Table Design, you decide that there is no reason for de-normalization , or hyper-normalization (there should be relateively few cases where these are needed, but they do ocur!).So, you have a 10 column table. 1 column primary key, lets say integer. 2 foreign keys, also integer. 2 datetime fields. 2 small char fields - say char(5) and char(8). 2 varchar columns (25) and varchar(250), and a timestamp which is frequently updated.So, what rules do you use to decide what columns go where (order) ?My rules, currently based on personal experience/ other DBMS's, and some limited research, is the following:1. Primary key column as first column. If a composite key, then the most selective column is the first column of the key, and the columns are put down in order of selectity, as defined in the key. 2. Most frequently updated column last.3. Variable length columns last, unless there is a a frequently updated column (per rule 2), in which case it would be just before the frequently updated columns.4. Foreign key columns just after primary key columns, unless they are impacted by rules 2 or 3.I have seen reasons for these on other DBMS's (specifically DB2 for 0x/390). I would tend to want to apply they as generics ... what do you think ? What should change, what else should be considered.Based on the example I gave above, I would have :TABLE xxxPKFK1FK2datetimedatetimechar(5)char(2)varchar(25)varchar(250)timestampOther thoughts - would there be an argument to say that most frequently selected/used columns should be "higher" (closer to top) of a table - so if the char(5) is frequently used, then move it above the datetime's ?Inputs / general laughter at question welcome ... *#&#* *#&#* *#&#* *#&#* Chaos, Disorder and Panic ... my work is done here! |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-08-21 : 12:05:54
|
| I'm guessing that you realize that column order in not part of the Relational Model and any rules/guidelines about column order are purely implementation details that are product specific ...That said.As far as I know, the only tip I've ever read with regard to column order had to do with making sure your most selective column was listed first in the creation of a multi-column clustered index. I think (and I'm talking out of my ass here) that the optimizer will only build a distribution historgram on the first column of the index. However, this has nothing to do with column order on the table.Jay White{0} |
 |
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2003-08-22 : 02:30:15
|
@Jay - yeah, do know that column order a product specific issue.I have seen the tip you refer to (it from The guru's guide ... Rob can fill in the blanks ...). It says quote:
"Along the lines of making indexes highly selective, order the key columns in a multicolumn index by selectivity, placing most selective columns first ..."
That means to me that given a 3 col index a,b,c with selectivity A>C>B, the recommended index would be A,C,B.Btw - his - that is, Ken Henderson's, formula stated to calculate selectivity is : =# of unique keys/# of rows, and he reckons thata non-clustered index with a selectivity < 0.1 are not efficient, and won't be used by the optimizer.Still doing some research on the other thoughts I listed - I will post back as and when...*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
|
|
|
|
|
Table Design thoughts