MSSQL tips for production databases
Last updated on April 25, 2010, 20:59 by Sebastian Mihai
There are a few things you can do to have minimum impact on a production database when running queries against it.

If you are inexperienced, be extra careful when typing the words UPDATE or DELETE anywhere.

Always use TOP with SELECT to limit the number of returned rows. It's a good idea to avoid returning hundreds of thousands of rows from a production database, so always use TOP with your SELECTs as such:

SELECT TOP 50 * from table

You can be even nicer on the database by not taking any locks via:

SELECT TOP 50 * from table with(nolock)

If you're planning on deleting, or modifying more than about 10 rows, it's a good idea to save a list containing the primary keys of those rows somewhere, perhaps with an explanation of what changed. If later you find that you really shouldn't have made that change, you now have an easy way of finding the affected rows.

Run SELECT using the same constraints that you're planning on using with DELETE or UPDATE, before you do so. There are few things less pleasant than running a DELETE query, expecting it to take under a second, and watching it run for a few seconds, after which you are told that a few hundred of thousands of rows were affected...

If you're paranoid (and in a production environment you should be), then type your UPDATE and DELETE statements as such (in the query browser):

    --WHERE (your where clause)

--DELETE FROM table WHERE (your where clause)

highlight everything after -- and execute. This ensure you're always writing the constraints out first, and the -- (signifying a comment) will keep the query from executing if you accidentally hit execute on the entire query window.

If you use the materials on this page, or any other page on this web site, you do so at your own risk. They are provided "as is". No warranty is provided or implied. I neither guarantee that the materials will work, nor that they will not be harmful in any way.

One of the most interesting bugs I ever fixed

Example of indexing and searching in Lucene.Net using C#

Atari Lynx - installing a power connector

Blood Bros arcade JAMMA PCB repair log (sound problems)

Generating DC (direct current) from a plasma ball (wireless charging)

Listening to an electromagnetic field

Tesla coil - visualizing its electromagnetic field

.Net development - C# from C#

The naming convention of the C standards is Y2K-susceptible

Missing stack trace entries in Release mode assemblies in .Net 4.0 (C#)

Public constants across assemblies and default parameter values in C#

C# lambda operator

Simple two-column, three-panel web site template

An easy to use random number generator

Puppy Linux on a computer without a hard drive (on a USB stick!)

(My) Useful settings for fresh Windows installations on new computers

How to use multiple versions of Firefox on the same computer

How indexes work

Trivialization of history through technology

Entropy in code

Basic Linux tricks

MSSQL tips for production databases

Keep your computer clean with VMware