Category Archives: Fail

How (not) to design a SQL Server table

I recently saw the worst example of  how not to design a table and indexes in SQL Server.

So here it is:

Table1(UserID,ActivityID,ActivityType,AddDate)

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.

Where did Microsoft Zune coders failed? or It’s a feature!!!

Eduard gives us the following  problem:

The result is that  every 4 years for a day you realized the money spent on a Zune are wasted

http://www.engadget.com/2008/12/31/30gb-zunes-mysteriously-begin-to-fail-at-12am-december-31st/

Now for the Microsoft solution (Please refrain from ROTFL :-D! ):

Follow these steps:

  1. Disconnect your Zune from USB and AC power sources.
  2. Because the player is frozen, its battery will drain—this is good. Wait until the battery is empty and the screen goes black. If the battery was fully charged, this might take a couple of hours.
  3. Wait until after noon GMT on January 1, 2009 (that’s 7 a.m. Eastern or 4 a.m. Pacific time).

http://www.zune.net/en-us/support/zune30.htm