Tech Stuff

For Speed
Using SQL Server views is a good idea, but there are still some serious drawbacks. For the most part, they are slower than inline SQL Statements. But if you have an enterprise version of SQL Server 2000, you can add indexes to greatly improve view performance. However, the more common standard version of SQL Server 2000 doesn't support indexed views. If performance is not an issue, though, views are a good way to aggregate data and control permissions. But SQL Server must retrieve the view definition and compile it for every call.

If database tables are constantly updated, use the NOLOCK hint when running queries on these tables. You may not see a difference if certain tables aren't being updated, but if they are, you’ll see a noticeable improvement in your update commands and queries.

Stored procedures are the recommended way of querying the SQL Server and usually offer very good performance. But as with any programming language, care must be taken to make sure the code isn't sloppy or designed so poorly that performance and maintainability are forsaken. Writing stored procedures and using the sp_execsql command makes little sense and should be avoided whenever possible. If a stored procedure has been written calling sp_execsql, you should rethink the design to eliminate the need to call it and save SQL Server the time of compiling the statement. For example, using conditions to execute different statements instead of building a statement dynamically can be a better option.

Another issue always associated with stored procedures is the use of server-side cursors. Opening a server-side cursor is similar to opening an ADO record set to perform row-based manipulation of the data. In theory (and in practice) many of the same functions can be done with set-based processing.

Set-based processing is using advanced select statements and Transact-SQL (Microsoft’s SQL language) to perform data manipulation. A very simple example is using the query: update set a=a+1 instead of looping through a table, adding 1 to a value, and then updating the row. A more complex example would be using the CASE switch or other Transact-SQL (T-SQL) functions. Transact-SQL contains a lot of functionality that many programmers don't take the time to learn. Not only can Transact-SQL make your program logic cleaner, it will speed up your application considerably.

Developing with scripts
Always maintain scripts in Source Safe. This provides you with an audit trail of the script.

Back Ups
Make sure you take the database back-ups daily or weekly. For nonproduction databases, set the backup method to Simple and use the maintenance wizard to back up each night. This keeps the transaction log small and conserves hard drive space. Many a times, you bump across a Log-File Full error, or a full hard drive, or can’t restore the database to another machine with a smaller hard drive. In every case, it is because, the database was set to Bulk-Log and the transaction log has never been backed up or truncated.

If the hard drive is full: Back up the database and, inside the Shrink Database option, you can select the Files… menu item and the Transaction Log .ldf file and shrink it. I’ve shrunk a 5-GB file down to 22 MB, and then created a database plan to back up nightly and truncate the log.

For production databases, it is the same principle. But instead, you use a full recovery plan and create a separate maintenance plan that backs up and truncates the log every couple of hours. I’ve been very fortunate to have needed to conduct a restore only twice in the last three years.

Security
The best practice is to use Windows security. It takes more time to maintain but, as more people and developers use the server, giving out the same login to everyone becomes more dangerous because people can log in and you won't know who did what. If you use mixed SQL/Windows security, then guard the SA password and make sure it isn't blank. Even on development servers, having a blank password is a bad practice.

Some More Tips
Keep the log files and data files on different drives. The drive with the log file shouldn't be a RAID 5 drive but a mirrored drive or RAID 1.

Increase the tempdb database if you're conducting many imports and exports. SQL Server uses the tempdb to hold temporary data and tables. Also, if you let the tempdb grow automatically, the percentage of growth should be at least 10 percent. If the server slows down at anytime, you need to enlarge the tempdb database.

For very common tables that don’t change often (such as a product line / category table), PIN the table to memory (see Books Online). Use Books Online—I’m always impressed by how much is actually there.

Use Update Statistics/Reorganize Indexes to make sure SQL Server knows the best way to self-tune queries.

It Pays in the Long Run
Applying the above mentioned practices would eliminate the potential for some serious problems. Though these practices may appear time-consuming at the beginning of a project, you will have a much better control over the environment at a later stage.