'ReallyVirtual' on Wed, 25 Aug 2004 20:36:02 GMT, writes:

Hi,

In the first post (blameMike's example), the first table contains the username/password only, which makes sense to me.

Consider a heavily used website running this database (maybe a webbased email with 1000 hits per second).

Users will probably log on thousands of times each year, but change their address once. For each password verification query, the database will need to scan db pages and pull up information. If we merge the two tables, the database will need to do a lot of extra work.
Deciding the clustered index/non-clustered indices for a database takes some thought if you are optimizing for performance, and usually results in scenarios like this.

Also, as you mentioned, if the database is in production/online, and you need to change its schema later, a partitioned approach like this will not lock tables unnecessarily.
There are other possible applications in replication scenarios as well.

I think you can probably find other good reasons on http://www.sqlserverperformance.com .

URL: http://www.reallyvirtual.com

'blameMike' on Sun, 02 May 2004 01:24:03 GMT, writes:

Holy unneccesary JOIN Batman

I just ran across something similar to this in a UML diagram one of my colleges sent me. It looks like this:

Table:
CustomerAccess

Fields:
CustomerID
UserName
Password

Table:
Customers

Fields:
CustomerID
FirstName
LastName
Address
City
State
Zip

To tell you the truth, I have no idea why he wanted to do this. In my opion there is no real advantage to this paradigm. In fact, I think this scheme will be slower if all this data needs to be returned due to the JOIN operation.

URL: http://www.blamemike.com

'rich...' on Wed, 28 Apr 2004 07:26:08 GMT, writes:

i'm with you on this one..dude..

'Mike Gunderloy' on Thu, 22 Apr 2004 11:38:46 GMT, writes:

Reasons I've seen this actually used:

1) Differential security
2) Getting by limits on number of columns in a table or number of bytes in a row. Extremely wide tables sometimes require this technique when you get down to the real database product.
3) Compatibility with other systems - ie, right now I'm involved with a project that is importing data from System X into System Y, and System Y needs extra columns. For other grotty infrastructure reasons we can only get full tables from X, not deltas, so it's simplest to keep our own data in extender tables.

URL: http://www.larkware.com

'Grant Queenin' on Thu, 22 Apr 2004 11:20:31 GMT, writes:

This is called vertical partitioning. It is used for a variety of reasons but performance is often cited. It is also of use where you have a canned application and need to extend it. Many commercial apps use this approach for user customisations.

Keeping volatile especially variable length fields away from more slowly changing or static fields. A positive sideeffect is that it slows the frag rate on the more slowly changing/static table and reduces maintenance burden. It also improves cache effectiveness as more data is packed into each read.

It can have some attractiveness for isolating columns which require more secure or different handling / treatment. Views are usually used for this.

Another reason to use this is to store archive/historical transactions (potential here for federated storage) in one instance of a table, and the current transactions
in a smaller table and these are unioned for queries with a view.

and the list of reasons goes on!

URL:

'Justin King' on Thu, 22 Apr 2004 04:36:50 GMT, writes:

My first instinct would be to say that indexing would be quicker, plus if you are only looking for queries to bring back part of data and not the Contact details extended would extract the data a little bit quicker.

But in this day and age with servers getting unbelievably fast, unless you are running 100TB databases then performance for mid size databases will not suffer as much.

All for good database design though!

URL: http://geekswithblogs.net/jking

'secretGeek' on Thu, 22 Apr 2004 04:31:02 GMT, writes:

(my own theory is that it saves on rebuilding a clustered index on the first table any time the extended table is altered [via Alter Table, I mean]. but i think there are other, better, reasons)

URL: http://www.secretgeek.net/one_to_why.asp