I recently saw the worst example of how not to design a table and indexes in SQL Server.
So here it is:
Role : Log any activity that a user is invited for.
PrimaryKey: … Wait for it … tada : UserID,ActivityID,ActivityType,AddDate … Wait you say …. YES … all table columns are used for the primary key and of course by default this will be a clustered index. Which means that if you want to insert 100K records in this table … it will split a lot of pages to reorganize the index beside actually adding the table records.
Wait … it gets better…
Someone created a non clustered index on ActivityID,UserID,,ActivityType,AddDate … yes in that order … and NO it does not help but I guess not everybody knows that a non clustered index will contain the clustered index besides the actual index.
So let’s recap:
Table : 4 Columns
PK Clustered Index: Same 4 Columns
Non clustered index: Same 4 columns x2
Insert efficiency -> extremely poor.
I posted a comment on Kevin Kline’s blog regarding:
“A spokesperson for Microsoft said that the problem was not bandwidth but that its SQL Server database had reached excessive fragmentation levels caused by the tremendous surge of queries”. Read about it here:
and also here:
Why didn’t anyone involved bother to think thru the fact that competitors and various elements of anti-Microsoft factions can make a lot of hay about how Microsoft’s own product?!?
Just as politicians only ever admit to “wanting to spend more time with family”, a technology company shouldn’t point to their own product as the culprit of a major technology failure, especially when there are thousands of other reasons that the meltdown might’ve happened.
What are your thoughts?
Here are my thoughts.
I think the truth is somewhere in the middle. I have worked with both Oracle and SQL Server and while I agree that in a lot of regards Oracle is a superior product it is also because of their experience.
Let’s face it …Oracle started in 1979 while Microsoft bought Sybase in ’89.
In my opinion the first great product they had were Oracle 8 in 1997 and SQL Server 2005 in 2005. Right now Oracle has a stable multi tiered scalable clustered architecture (including app server) whereas Microsoft is still discussing about deployment dates.
Also … Oracle query optimizer is IMO one of the best in the industry since Oracle 8. Microsoft has finally developed a good (should I say … comparable)query optimizer in 2008.
So while SQL Server is a good product we should compare apples to apples because IMO Oracle and Microsoft play in different leagues.