How do I restore specific tables from a database backup in SQL Server 7.0?
The only way to restore individual tables from a backup in SQL Server 7.0 is to use filegroups, then create a table with a clustered index locating the clustered index in the file group. However, when you restore a file group, you need to apply the transaction logs to make the file group consistent with the rest of the database. From a full backup in SQL Server 7.0, you can't restore a single table in any other way. The code in Listing 1 demonstrates how to locate a clustered index in the filegroup myfilegroup.
I recently changed my SQL Server configuration based on the Microsoft white paper Microsoft SQL Server 7.0 Performance Tuning Guide (http:// www.microsoft.com/sql/techinfo/perftuninguide .htm). In my previous configuration, drive F (Raid 0+1) contained the SQL Server system databases, tempdb, and the transaction log for the production database. Drive J (Raid 0+1) contained the production databases with all nonclustered indexes on the same filegroup (Primary). In the current configuration (based on the tuning guide's suggestions), drive F (Raid 0+1) contains the SQL Server system databases, tempdb, the transaction log for the production database, and the nonclustered indexes on a new filegroup. Drive J (Raid 0+1) contains the production database (on the Primary filegroup). After moving the nonclustered indexes from the J drive to the F drive, I'm seeing a dramatic performance decrease. Why?
You need to isolate your transaction logs because the logs will have heavy write activity on most online transaction processing (OLTP) databases. In your case, the disk controller is probably too busy trying to service the log writes and index reads to do both adequately. We'll update the Microsoft SQL Server 7.0 Performance Tuning Guide to include this recommendation.
I recently used a stored procedure in SQL Server 7.0 that didn't behave as I expected. Consider the following code snippet:
declare @zip varchar(20)
set @zip="'98001','98002'"
select * from ks_list where
zip in (@zip)
This query returns 0 rows. However, if I enter
select * from ks_list where
zip in ('98001','98002')
I get the data. Why doesn't SQL Server properly substitute the value of @zip?
The SQL Server parser interprets @zip as a single value. So the IN compares your zip column with the single value in the parentheses, which is a string with a comma in it, and your zip column won't match that. However, you can change the query slightly to
declare @zip varchar(20)
set @zip="'98001','98002'"
exec('select * from ks_list where zip in (' + @zip + ')')
and you'll get the expected results. In this case, the value of the variable is resolved first. Then, when the parser evaluates the IN list, the parser sees two values separated by a comma and doesn't know that those two values came from a single variable.
End of Article
Prev. page
1
[2]
next page -->
