DevPapers - article Tips And Tricks For Advanced MS SQL Server Developers

Here's a great article entitled DevPapers - article Tips And Tricks For Advanced MS SQL Server Developers. It includes a few tips I'd never heard of before such as how to quickly determine how many rows are in a table without using "count(*)."

For reference, here's the contents of the above mentioned article

1. Use "TRUNCATE TABLE" statement instead of "DELETE" clause if you want to delete all rows from a table. It is much faster then "DELETE" statement without any conditions. "TRUNCATE TABLE" frees all the space occupied by that table's data and indexes, without logging the individual row deletes. 2. Always use owner prefix in T-SQL queries: SELECT mycolumn FROM dbo.mytable In this case query optimizer does not have to decide whether to retrieve from dbo.mytable or other owner’s table and avoids recompilation. Recompilation results in no performance advantages of stored procedures usage. 3. Don't use "sp_" as your prefix for stored procedures – it is a reserved prefix in MS SQL server! MS SQL server searches for a stored procedure with "sp_" prefix in the system procedures first, and only after that looks for them in client procedures. 4. If you are unable to install MSDE at home because of unknown error – check that you did not stop "Server" system service on you PC… 5. There are thousands of examples, when developers use "SELECT COUNT(*)" statement. But there is another, much faster way to accomplish the task: SELECT rows FROM sysindexes WHERE id = OBJECT_ID('Table_Name') AND indid < 2 6. Include "SET NOCOUNT ON" statement in your stored procedures to greatly reduce network traffic. 7. Use the "BETWEEN" clause instead of "IN" for greater performance: SELECT productId FROM customer WHERE productId BETWEEN 1 AND 9 Instead of: SELECT productId FROM customer WHERE productId IN (1, 2, 3, 4,5,6,7,8,9) 8. Use Table variables - new feature of MS SQL 2000 instead of temp tables. Table variables are created in memory, not written to the tempdb database, and therefore they are much faster. However, be careful to use them only with not very huge amount of data that you want to allocate in temp tables, otherwise you can easily get the server down.