Kalen Delaney Virtual Workshop: SQL Server 2008 Indexes – Internals and Best Practices
Feb. 25, 2011 – Along with a good database design I would say that Indexes are a cornerstone to good database performance. There are a lot of other skills you can develop in relational databases; but without these two skills, you’re going to find yourself in a heap of trouble. The SSWUG.org’s virtual workshop, conducted by Microsoft SQL Server MVP Kalen Delaney, will give you the skills you need to understand indexes, evaluate their effectiveness and make the necessary adjustments for performance. Register today to save your spot.
$$SWYNK$$
Featured Article(s)
Why I Choose Database Mirroring
Database mirroring or replication, maybe even t-log shipping or clustering. Have you taken a case study look at why you may want to pick one technology solution over another?
Featured White Paper(s)
VIDEO WHITEPAPER – How to Avoid Downtime – VIDEO WHITEPAPER
In this Experts & Insights video, we take a close-up look at the data protection challenges and solutions surrounding downtim… (read more)
Tip Of the Month
I did a tip of the month a while back on using a table, DELETE WITH (READPAST) and OUTPUT, as a form of a message queue. This is not to be confused with real message queuing supported in SQL Server. Anyway, I got a lot of feedback that the tips were useful.
So, now it’s time for a new one. Again, we are going to take advantage of the OUTPUT clause added in SQL Server 2005.
What do you do when you want to add data into your database for a table that uses an IDENTITY column? You have to insert the data, and then return the IDENTITY value assigned before you are able to add records to any tables where the value would be a foreign key. This means that if you have data resulting in multiple rows, you have to make multiple insert statements, each with a separate round trip.
There are lots of architectural options, even to the point of not using Surrogate keys, or externally assigning those value. Today’s tip allows you to insert sets of data, utilizing identity columns, and return the results. It does require that the data being inserted must have some unique identification within the list being added.
The Insert, Update and Delete TSQL statements all support an OUTPUT clause, returning a table of rows modified in the same form as a trigger. There is a virtual table INSERTED containing new rows or the modified values of updated rows. There is also a virtual table DELETED containing deleted rows or the old values of updated rows.
You can take advantage of this to insert multiple rows of data into a table, and return each IDENTITY value assigned. You do this by creating a temporary table for the results of your insert. In your Insert statement add the OUTPUT clause and map the INSERTED.[ColumnName] values into your temporary table. Your temporary table now contains the identity values assigned to each row from the set of data you inserted.
No longer are you restricted to inserting one row at a time and using SCOPE_IDENTITY() to find the identity value assigned. Now you can add multiple rows and determine the IDENTITY value assigned to each individual row.
Do you have a tip you’d like to share? Send it to me at btaylor@sswug.org and I’ll post it in a future newsletter.
Cheers,
Ben