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.