We've just started an SQL Fundamentals course at the company where I work. Our former R&D team leader is the prime lecturer and the course is intended to bring all of the programmers to a common level of understanding of how we should program for MSSQL to perform best under increasing loads of my company's website.
I'm publishing here some of the things I've written down. I would very much like to hear what you think about the following points. If you can recommend any relevant online resources please do.
Remember that we're dealing with Microsoft SQL Server 2000 here, used in a large website and with performance and scalability as prime goals:
- There is no need to define relationships, constraints and public keys in the DB. These add load to the MSSQL server and can all be enforced at the application level instead.
- Aggregate functions are expensive if the column to be aggregated is not indexed.
- Using WHERE on indexed columns reduces load on the MSSQL server because less rows are retrieved. Using HAVING does not reduce the load, because it is executed only after all the rows have been retrieved.
- When using SELECT TOP, the MSSQL server retrieves (from disk to memory) all the rows and only then selects the top rows from that. It is still attractive to use because it reduces the number of rows sent to the client.
- Using a SELECT INTO query is dangerous because it will TRUNCATE the target table before inserting into it.
- It is better to use JOINed SELECT queries than nested SELECT queries because the latter forces a specific order of execution for the sub-queries whereas the first does not, and allows the built-in optimizer to decide in what order to execute.
Thanks,
Uri