Creating the two new columns would be easier with a cursor than with the UPDATE statement. You can open a cursor on the table and loop through each row, updating the counter after each update. This process is slower than performing the operation in one statement but is much easier to program.
The speed of our nightly tape backups on a 50GB SQL Server 7.0 database varies widely. For example, for 21 days, the backup speed was 5MBps. Next, for 85 days, the speed dropped to 2.5MBps. The speed rose again for 106 days, fell for 2 days, then peaked for another 34 days. The fluctuation is frustrating because every time the speed drops, the backup schedule goes out of control and affects other jobs. I don't see a pattern. Why are we getting the variation?
What environmental changes might be causing the fluctuation? Nothing in SQL Server explains the behavior, so look for factors that affect the read throughput of the disks or the write throughput of the tape drive. You can use BACKUP to disk='NUL' to test maximum read throughput separate from the tape write speed. You can also try using RESTORE VERIFYONLY to see whether the backups have been corrupted.
Upgrading my databases from SQL Server 6.5 to 2000 takes more than 13 hours when I use the Upgrade Wizard. However, I trimmed upgrade time to 5.5 hours by executing SQL Server 6.5 bulk copy program (bcp) out in native mode from SQL Server 6.5 to 2000. Before I began converting the SQL Server 6.5 database, I dropped all indexes except clustered indexes. I don't want to lose any data integrity or precision with datetime, numeric, or character data. Can upgrading my databases this way cause a data-conversion problem?
Upgrading your databases to SQL Server 2000 by executing SQL Server 6.5 bcp in native mode should work fine. Just make sure that bcp copies all the data, and also make sure that you move all the other relevant system state information that the Upgrade Wizard captures, such as logins, users, security settings, custom error messages, stored procedures, triggers, and constraints.
Do I need to follow special installation procedures before I upgrade my SQL Server 7.0 database in a Windows 2000 cluster to Service Pack 2 (SP2)? For example, do I have to uncluster before I upgrade, then recluster?
The SP2 readme.txt file gives you instructions. Yes, you have to uncluster, apply SP2, then recluster.
I wrote a T-SQL script to load 80 characters from a text file into what I thought was an 80-character, fixed-length comment column. Then, I found that the column was 220 characters long. How can I write a script to append characters 81 to 220 to the comment column?
The kind of script you need to write depends on the column definition. If it's a fixed-character column, the column is already padded with spaces. If it's a varchar column, it isn't padded. Listing 2 shows the script that appends extra characters to a fixed-character column. Listing 3 shows the script for a varchar column.
My complex SELECT statement includes a NOLOCK hint for each table. However, when I run sp_lock to review the locks taken, SQL Server takes lock type TB Sch_S on all tables. How can I remove all locks?
You can't control schema stability locks because they're strictly internal to SQL Server. You need these locks to prevent someone from changing the table's schema while you're executing a query.