Q:
If you roll back an insert transaction into a table with an identity column, that identity value gets used up, and the table has a gap in the identity numbers. Usually, a gap isn't a problem, but I'm cheating a bit with an accounts receivable system and using an identity column to generate invoice numbers. How can I eliminate the gaps?
We wish we had happier news for you, but no magic setting will make identity behave as you would like. In this case, you have to design a custom number-generation process if you must avoid gaps.
Using identity this way is expensive, because this method serializes all transactions in the database that need to insert into that table. Ouch! You have to serialize transactions (i.e., only one user can receive a new identity value at a time) because you can't assign an identity value to User #10 until you know User #9 has already committed an identity value. Without serialization, you'd have a gap if User #10 received an identity value and User #9 later decided to roll back the transaction.
Generating Key Values Without the Identity Property
Generating key values became much easier when Microsoft introduced the identity property in SQL Server 6.0, but the identity property is not appropriate for all situations. (The identity property automatically generates a key value that you can retrieve with SELECT @@IDENTITY after an insert command executes.) You sometimes want to be able to retrieve the value before the insert takes place--and SQL Server doesn't offer a way to do so with Identity.
Listing 1, page 210, shows a solution for generating key values. This approach is easy to use and doesn't cause the overhead or concurrency problems associated with most key-generation techniques. We have used this approach, and it's always worked well.
This approach is simple. Store the last value used for each of the key types in a NextKeyValue table (see Listing 1), and use GetNextKeyValue, a stored procedure that accepts a key type such as EmployeeId and returns the next available value for EmployeeId. This technique isn't as transparent as identity, but it gives you access to the next key value before the update occurs.
You might be thinking, "Horrible contention will occur if all the key values are stored in the same table and many people are grabbing different types of keys at the same time." We admit that contention might be a problem, but our solution takes advantage of the way updates and locks work in SQL Server and thus avoids most potential problems.
Look at the six Empty1 through Empty6 fields in the NextKeyValue table. These fields make up the first trick you'll need to make the GetNextKeyValue approach work. SQL Server 6.5 doesn't support row locks on updates. However, if you want to avoid serious hot spots (when several users try to grab different key types at the same time), you need row-level concurrency. The empty fields provide fake row locks by padding the size of the row so only one row can fit on a page. SQL Server will still be doing page-level locking, but we've implemented row locks on the table because each page lock affects only a single row. You don't want to use this technique on large tables because it wastes space, but it works on a table with a few dozen rows.
The second trick required to make this approach work is forcing the update to occur in place. (You can perform an update in four ways; for more information, see Knowledge Base article Q135871, "INF: Update Methods Used in SQL Server 6.0," at http://premium.microsoft.com/support/kb/articles/q135/8/71.asp.) Placing a unique clustered index on the KeyName column and ensuring that the KeyValue isn't a variable length data type (which also means it must be NOT NULL) lets GetNextKeyValue perform the update as in-place. (Although Q135871 describes other requirements for performing an update in place, you don't need to worry about them in the example shown in Listing 2.)
As Listing 2 shows, you pass in a key name (such as EmployeeId), and the procedure returns the next KeyName value for you to use in your application. For flexibility, this procedure returns the next key value as an output parameter and as a result set, but you can choose the one that works best for your needs.
What type of locking behavior can you expect with this model? The test scripts in Listing 3 show you how the process handles locks. You must run each script from a separate connection, so you must run them from separate query windows. You see that the first three connections run fine with no blocking. You can determine the next key value for DepartmentId in Connection 2 even though Connection 1 has page locks on the NextKeyTable, as Table 1 shows. Connection 4 is trying to get a key value for AccountId, but Connection 3 is in the middle of a transaction that's trying to do the same thing. Connection 4 will be blocked until Connection 3 either commits or rolls back. Note that users can obtain NextKeyValues for different key types, such as EmployeeId and DepartmentId, but users will block each other if they simultaneously try to grab the next key value for the same key types.
Q: Is there a way to bcp out either single rows (preferably), or perhaps a subset of data rows? I'm trying to initiate the export of a record from a trigger and get one flat file that has all the transactions that were processed during the day.
Unfortunately, there's no direct way to bcp out a single row from a table. We can think of two techniques that might accomplish what you want to do:
- Bcp out of a view. If you can create a view matching the row subset you want, you can bcp out of the view the same way you would out of a table.
- Insert the result set of a stored procedure directly into a table, by using a call something like this:
INSERT INTO ResultSetStorage EXEC CreateMyResultSet
Create a stored procedure using cursor logic that returns the exact result set you want. (This procedure should be easy to create because cursors let you process a result set one row at a time.) Then insert the filtered result set created by the procedure into a temporary holding using the INSERT INTO ResultSetStorage EXEC CreateMyResultSet method. Finally, bcp out from your newly created table. You don't need to use a stored procedure. The EXEC statement will accept any string for dynamic execution.
Removing Replication Settings
If you've ever used SQL Server's replication, you know that getting rid of replication after you've installed it isn't easy. Microsoft provides wizards for setting up replication, but not for undoing it. Fortunately, we came across the following procedure. Microsoft designed the sp_Msunpublish procedure to remove replication-related information from a database that SQL Server has loaded from a dump of a published database. Sp_Msunpublish removes publication and subscription information stored in the database, marks published objects as unpublished, and removes any transactions marked for replication in the transaction log. You can find an installation script and usage instructions at http://support.microsoft.com/download/support/mslfiles/sqlunpub.exe. You can use this procedure with SQL Server 6.0 and 6.5. Microsoft has vastly improved replication in SQL Server 7.0.