Using LOCK_TIMEOUT to Skip Locked Data
Besides the READPAST hint, SQL Server 7.0 has a SET option that gives you another way to skip locked data. The option, LOCK_TIMEOUT, tells SQL Server not to wait more than a specified number of milliseconds for a lock to be released. Setting LOCK_TIMEOUT to zero means that SQL Server won't wait at all if it finds any locked data. The documentation for LOCK_TIMEOUT implies that it's interchangeable with READPAST, but there's a crucial difference. READPAST skips individual locked rows. Setting LOCK_TIMEOUT to zero causes SQL Server to stop processing the statement when it finds a locked row.
Although the LOCK_TIMEOUT setting might sound good, use it with extreme caution. The timeout doesn't automatically roll back a transaction. So if SQL Server reaches its lock timeout value, it stops trying to modify rows in the current table and moves on to the next statement. Instead of the transaction being an atomic, all-or-nothing operation, you might be left with part of the transaction incompletely executed. If you want the transaction to be all-or-nothing, you need to include a specific test for error 1222 and include an explicit ROLLBACK TRANSACTION as the action to perform when SQL Server encounters the error. You might consider adding this test to all data-modification statements in applications that adjust the LOCK_TIMEOUT value. This test is the only way to guarantee that the transactions maintain their consistency.
—Kalen Delaney
Inside SQL Server, "Controlling Locking"
November 1999, InstantDoc ID 6198
Fastest Bulk Loader in the West
Within SQL Server's storage engine, the T-SQL BULK INSERT statement, bcp, and the Data Transformation Services (DTS) Bulk Insert task all execute the same code. The T-SQL BULK INSERT statement can perform faster than the others because BULK INSERT executes completely within the SQL Server process. Bcp and DTS, utility programs that execute in their own processes, must bear the overhead of interprocess communications to pass data from the source text file to SQL Server. When you run bcp or DTS on a client computer rather than on the SQL Server system, the need to copy the data across a network connection adds significant overhead and further slows the bulk copy operation.
In my test results, the potential advantage of the T-SQL BULK INSERT statement didn't always translate into faster loading. According to Microsoft Product Support Services (PSS), the advantage is significant with very large tables (Microsoft routinely tests with 50 million–row tables), when you load multiple tables into a database in parallel, and when you use file groups to segregate onto different disk volumes the tables that you plan to load in parallel. Other factors also influence the overall performance of a bulk copy operation: Floating-point fields are faster than character fields, and files created to store data in SQL Server's native, internal, binary data format import more quickly than files in external ASCII character format.
—John Green
"Showdown: bcp vs. DTS"
April 2001, InstantDoc ID 19760
A Database by a Different Name
Q. I'm having a problem restoring a database. I backed up database ABC from server1 to the abc.bak file. Then, I created a new database called XYZ on server2. The servers belong to different companies and aren't connected to each other. I wanted to restore the abc.bak backup on the XYZ database on server2, so I selected Restore, From device, and on the Option tab, I selected Force restore over the existing database. In the option below the Force restore option, I specified current data and log file path for Move to Physical File Name. However, the Logical File Name shows ABC database names. If I try to change the ABC database names to XYZ names, the restore fails. But if I keep the ABC database names, the restore works. How can I overcome the Logical File Name problem?
A. You can't change the logical name of a file during a restore, so you must use the existing database names. Simply restore the database by using the ABC database names, and move the files to their new locations. You can then use the following ALTER DATABASE statement to change the logical names to XYZ names:
ALTER DATABASE MODIFY FILE
Note that this solution works only on SQL Server 2000, not on SQL Server 7.0.
—Richard Waymire
Answers from Microsoft, "Changing Database Names During a Database Restore"
August 2001, InstantDoc ID 21565
Hidden Treasure
You often need to query the master database for all tables or databases and perform an action on the results, such as DBCC CHECKDB. Traditionally, you used a cursor to perform this type of query, but a query this size leaves a lot of room for error. In SQL Server 6.5 and 7.0, Microsoft ships a set of undocumented stored procedures that let you perform the query in one line.
Two little-known stored procedures, sp_ MSforeachtable and sp_MSforeachdb, ship with SQL Server 6.5 and 7.0 and let you substitute a question mark for a table or database name. You can run DBCC CHECKTABLE on each table in the database you're in by using the one-line cursor query
sp_MSforeachtable "print '?' DBCC CHECKTABLE ('?')"
Microsoft has improved sp_MSforeachdb in SQL Server 7.0. In SQL Server 6.5, you have to create a temporary table, populate the table, then run sp_MSforeachdb. However, in SQL Server 7.0, you can run the following one-line query to perform a DBCC CHECKDB on all databases:
Sp_MSforeachdb @command1="print '?' DBCC CHECKTABLE ('?')"
—Brian Knight
Reader to Reader, "Undocumented Power"
December 1999, InstantDoc ID 7453