With all these great ORM tools and no-SQL solutions, you would almost forget that there is still a database running somewhere behind the scenes. Databases remain an important part of most applications, so we still have to be aware about what’s going on inside the database engine. On MS SQL Tips, I found a great tutorial that focuses on a few different areas where these common mistakes are made and what can be done to fix them. These areas include:
- Query writing
- Indexing
- Schema design
Following topics are covered:
- Query writing:
- How Join Order Can Affect the Query Plan
- Remove Function Calls From the SELECT List
- Avoid Using <> in WHERE Clause
- Avoid Using Functions in WHERE Clause
- Avoid Using Wildcard Characters to Start Search Criteria
- Use a Derived Table in Place of IN Predicate With Aggregate Functions
- Indexing:
- Make Sure All JOIN Columns are Indexed
- Use WHERE, JOIN, ORDER BY, SELECT Column Order When Creating Indexes
- Make Sure All Tables Have a Clustered Index Defined
- Schema design:
- Use DELETE CASCADE Option to Handle Child Key Removal in Foreign Key Relationships
- Denormalize For Performance
Go check it out: http://www.mssqltips.com/sqlservertutorial/3200/sql-server-query-performance-guidelines/