(Note – there was one section in the podcast where I was not clear about the intent of a statement. Please see the “Addendum” section below for more on that!)
In the news, the outlook on DBA jobs looks good even in this economy, this week’s feature deals with IDENTITY values as Foreign Keys, the web link shows you how to achieve scale-out solutions with replication, and the tip of the week is on performance tuning by getting less data.
In the News
The DBA magazine DBTA.com is reporting that although the economy isn’t doing all that well, DBA jobs are doing pretty good. Citing a Robert Half study, 13 percent of IT managers plan to add DBA staff, and 80 percent plan no changes. Read more at http://dbta.com/e-edition/Jan08/5-DidYaHear.html.
This Weeks’ Feature – Using the Right Identity
Many times when you’re writing INSERT statements you need to take a data from a single location and insert it into two or more tables. Normally you’ll set up a transaction with the BEGIN TRAN statement, and then write your INSERT statements to place the appropriate data in the different tables. But you’ll need to keep the data records related to each other. Many applications use an integer field as a Primary Key, and a corresponding integer in the related table as a Foreign Key. In some cases, the Primary Key is automatically generated by setting the Primary Key field to an IDENTITY value which simply gets another number and places it in the field. It’s a simple way to update the Primary Key, even if it can present issues from time to time. The real issue is getting that value from the first table after it is inserted so that you can use it to link back from another table. Let’s take a concrete example.
Assume that you have two tables: TestTable, and ChildTestTable. In TestTable, you have two fields: an integer called TestTableKey (which is the primary key, set to be an Identity value) and a character field called FirstName. In ChildTestTable you have three fields: an integer called ChildTestTableKey (which is the primary key, set to be an Identity value), a character field called Address and another integer field called TestTableKey. As you might guess, the TestTableKey in ChildTestTable is set as a Foreign Key pointing to TestTable.
Your application provides a single screen for someone to enter their name address. You want to take the name and put it in TestTable, and the address in ChildTestTable. You’ll link them with the value automatically generated for the key in TestTable.
That brings us to the whole point of this Podcast. You can actually obtain IDENTITY information in three ways. The first is using the @@IDENTITY system variable. This returns the last IDENITY value used by any function in any database, in current session. That means that if another table has another IDENTITY field and someone does an insert after your first one but before you use the value in your second table, your numbers might not match. Also, if someone in the same session (but perhaps another transaction) does an insert into a table like that, you’ll also get the wrong number. It has some other limitations as well, dealing with replication or remote servers. It can also be affected if there are triggers that also hit an IDENTITY field.
The second method is a bit safer. You can use the SCOPE_IDENTITY() function to get the last identity value inserted into an identity column in the same scope, which means a stored procedure, a trigger, a function, or a batch. This function is across all tables in the database, however.
For even more control, you can use the IDENT_CURRENT() Function. This function is limited to the same scope, and even the specified table. You have to provide the table name to use this function.
I’ll refer to a topic in Books Online in the show notes to give you more info on these statements.
Did you know you can use SQL Server Replication to achieve a scale-out solution? Check out this whitepaper to learn how: http://www.microsoft.com/technet/prodtechnol/sql/2005/p2ptranrepl.mspx As always, the link is in the show notes on my blog.
Tip of the Week
This week’s tip deals with performance Tuning. Once you find the long-running queries, make sure you check that the code is only returning the data it needs. One of the most common performance tuning issues is querying for more data than the application actually needs.
IDENTITY Information in Books Online – http://msdn2.microsoft.com/en-us/library/ms175098.aspx
Job Market News – http://dbta.com/e-edition/Jan08/5-DidYaHear.html
Scalability using Replication – http://www.microsoft.com/technet/prodtechnol/sql/2005/p2ptranrepl.mspx
My friend Greg Low, a Most Valuable Professional down in Australia, pointed out a mistake I made in a statement:
“From: Greg Low
Sent: Sunday, March 09, 2008 4:13 PM
To: Buck Woody
Subject: Podcast #2
I listened to podcast #2 today. Great stuff. Keep them coming.
One note though: in the podcast, you said that @@IDENTITY was “any function, any database and any session”. My understanding is that it’s not. BOL says “@@IDENTITY and SCOPE_IDENTITY return the last identity value generated in any table in the current session.”
Hope you’re well.
SQL Server podcasts with key SQL Server identities: http://www.sqldownunder.com
You (and BOL) are correct. My wording is incorrect, but the sentiment was that the number can change on you in the table, but not in the current session. I’ll add this conversation to the show notes to make sure I’m clear.
Thanks for pointing this out! Also, I need to point folks to your blogs/podcasts as well.
Program Manager, Microsoft SQL Server
$DO || ! $DO ; try
try: command not found
Here’s an interesting experiement that shows the point: create a test database with a single table, and two columns. The first column is an Identity, and the second a varchar. Start another session, and enter a single record in that field, and get the next IDENTITY Value. Now switch back to the first session and enter five new records, getting the IDENTITY Value there. It should be about 5 or 6, depending on how many records you entered. Now switch back to the second session and get the IDENTITY value – it will remain at the first one you got. The point is that you can’t depend on @@IDENTITY to show the very latest value in the table across sessions. Hopefully that is clearer than what I said in the Podcast!