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.
