Since embracing NHibernate, I've reveled in not writing any ADO code. On the other hand, I've become a bit rusty, and a bit nostalgic for that matter, with SQL these days. To my chagrin, I find myself having to look up bits and pieces of SQL that I used to be able to recite in my sleep. To help cut down on the number of times SQL docs needs to be opened, below is a quick summary of the most common and useful SQL scripts for SQL Server 2005 maintenance; most apply to 2000 as well. (For the record, I'm no DBA but just a lowly developer who uses SQL daily.)
Return "quick" row count
If you're dealing with larger tables, SELECT COUNT(*) can be a time consuming operation. For a very quick row count - albeit, not guaranteed to be accurate in real time - use the following instead:
SELECT rows
FROM sysindexes
WHERE id = OBJECT_ID('table_name')
AND indid < 2
Delete all table rows
Similar to using SELECT COUNT(*), the most common approach isn't always the most efficient. Using DELETE FROM table_name is time consuming - and storage consuming - as deletions create log entries. For an incredibly fast deletion of all table records, but - as a warning - without the log entries possibly needed for a recovery:
TRUNCATE TABLE table_name
Truncate the DB log
If you find yourself running out of storage space on your hard drive, it may be due to an enormous SQL log file. The following allows you to truncate and shrink the log file to a smaller size...2 MB to be exact. Warning: dumping the transaction log has ramifications with respect to recovery options (or lack there of), so use at your own risk!
DUMP TRAN db_name WITH NO_LOG
DBCC SHRINKFILE (db_log_name, 2) WITH NO_INFOMSGS
Shrink the DB
The following will remove empty space from your DB file, leaving 10% left open.
DBCC SHRINKDATABASE (db_name, 10)
Limit Logging Altogether
Although you can't turn off logging altogether in SQL Server, you can limit logging by setting the DB's recovery mode to "simple." For recovery purposes, it's recommended to keep it on "full" recovery mode for the rest of the time.
-- Limit logging with "simple" recovery mode
ALTER DATABASE db_name
SET RECOVERY SIMPLE
-- Restore logging to "full" recovery mode
ALTER DATABASE db_name
SET RECOVERY FULL
...There are many variations and options to each of the following, as described in your SQL docs...
Add a column to a table
ALTER TABLE table_name
ADD column_name type(length) NOT NULL
DEFAULT default_value
Drop a column from a table
ALTER TABLE table_name
DROP COLUMN column_name
Modify an existing column
ALTER TABLE table_name
ALTER COLUMN column_name type(length) NOT NULL
Add a foreign key
ALTER TABLE foreign_key_table_name
ADD FOREIGN KEY (fk_name)
REFERENCES primary_key_table_name(pk_name)
What's listed here are my most commonly used, and regularly looked up, SQL scripts and optimization tricks. Hopefully it'll save you some lookup time as well...feel free to submit other snippets that you feel should be added to the list of bare essentials.
Billy