SQL Server

SQL Server Integrated Service 2008 ROCKS!
 
Frequently in a Software Developer's life a product comes along that is just so delicious to use.  C#, .Net Framework, .Net Compact Framework, SQL Server(all flavors), Ajax, XML, SOAP protocol, PowerBuilder, Visual FoxPro, Foxpro, Emerald Bay, etc.  SSIS 2008 (SQL Server Integrated Services) is another one of those powerful and fun-to-use products.  If you have only used DTS then you have a big treat in store for you.  It is so much more powerful and easier to use.  You can now write C# Scripts inside SSIS!  And Sequence Containers are just so wonderful.  I'll talk more about these parts of SSIS as time rolls on.  For now, try it on for size!  You will NOT be returning it!

Stored Procedure - How to Retrieve the Identity Key from an Insert - CORRECTLY!
Ok, so you are using identity keys (bigint I hope) for your primary keys in your SQL Server tables and thus foreign keys in your child tables. Superb choice! Do not put intelligence into your keys and you will live happily ever after! You will also have fewer connection rejections and less deadlocks. Your joins will be faster and you will never have to modify your keys due to attribute changes in a row(s).
Now, are you using @@IDENTITY to retrieve the identity (surrogate) key from your insert? You are??? WHOOPS! That is gonna hurt. In a low volume system it will work (sometimes as long as you do not have triggers for that table that do inserts also). In a heavily accessed system you are gonna get burned big time (and in a single user system if you have triggers in the table that also do inserts). You are going to wind up with orphan child table rows. Or, even worse, they will not be orphaned rows but attached to the WRONG PARENT row! That is why you are getting items shipped to the wrong customer, among other problems. Worse than that, you may never know you have a problem, reports just never crossfoot.

In comes the white knight to save the day/week/month/year/career! His name is SCOPE_IDENTITY(). He will always give you the identity key from the last insert from within your stored procedure (you ARE using stored procedures, right?), EVEN if you have a trigger on that table that does an insert.Check SQL Server Books Online for further explanation of this CRITICAL technique!


Surrogate Keys Rule!
Surrogate Keys (bigint or int Identity keys in SQL Server all flavors) are THE only choice as primary keys in a relational database. There, someone finally said it boldly and frankly.