Images in this post missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at imagehelp@codebetter.com
Common SQL Scripts and Tips

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...

Rename a table

EXEC sp_rename 'old_table_name', 'new_table_name'

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

Drop a constraint (e.g., a default) from a table

ALTER TABLE table_name
DROP CONSTRAINT constraint_name

Rename a column

EXEC sp_rename
    @objname = 'table_name.old_column_name',
    @newname = 'new_column_name',
    @objtype = 'COLUMN'

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 CONSTRAINT constraint_name
FOREIGN KEY (fk_name)
REFERENCES primary_key_table_name(pk_name)

Drop a foreign key from a table

ALTER TABLE table_name
DROP fk_name

Disable / Re-enable all Constraints (including foreign keys)

To Disable:
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

To Reactivate:
exec sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Replace string within a text datatype

update table_name
set column_name = REPLACE(SUBSTRING(column_name, 1, DATALENGTH(column_name)), 'string_to_find', 'replacement_string')

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 McCafferty


Posted 10-24-2006 12:47 PM by Billy McCafferty
Filed under:

[Advertisement]

Comments

Brendan Tompkins wrote re: Common SQL Scripts and Tips
on 10-25-2006 3:48 PM

Great tips Billy!

Haacked wrote re: Common SQL Scripts and Tips
on 10-26-2006 4:49 PM

One issue with truncating a table is if there are foreign keys that point to that table.  Typically, you have to drop the foreign keys, truncate table, and then re-add the FK.

Billy McCafferty wrote re: Common SQL Scripts and Tips
on 10-27-2006 11:55 AM

Good call Haacked...that's an important item to note.

About The CodeBetter.Com Blog Network
CodeBetter.Com FAQ

Our Mission

Advertisers should contact Brendan

Subscribe
Google Reader or Homepage

del.icio.us CodeBetter.com Latest Items
Add to My Yahoo!
Subscribe with Bloglines
Subscribe in NewsGator Online
Subscribe with myFeedster
Add to My AOL
Furl CodeBetter.com Latest Items
Subscribe in Rojo

Member Projects
DimeCasts.Net - Derik Whittaker

Friends of Devlicio.us
Red-Gate Tools For SQL and .NET

NDepend

SlickEdit
 
SmartInspect .NET Logging
NGEDIT: ViEmu and Codekana
LiteAccounting.Com
DevExpress
Fixx
NHibernate Profiler
Unfuddle
Balsamiq Mockups
Scrumy
JetBrains - ReSharper
Umbraco
NServiceBus
RavenDb
Web Sequence Diagrams
Ducksboard<-- NEW Friend!

 



Site Copyright © 2007 CodeBetter.Com
Content Copyright Individual Bloggers

 

Community Server (Commercial Edition)