What you don’t know could hurt your query performance.
I will present a seven part series on the most common T-SQL performance anti-patterns. Part 1 – Data Types, Part 2 – Using Functions, Part 3 – Multi-Statement UDFs, Part 4 – Dirty Reads, Part 5 – Query Hints, Part 6 – The Dreaded Cursor, Part 7- Nested Views
There are three very basic idioms for writing well performing T-SQL. (1) They are cumulative. (2) Doing all of them will have a positive impact on performance. (3) Skipping or changing any of these is likely to have a negative impact on performance.
Data Structure Consistency: If you are storing a datetime data type, use datetime, not a varchar or something else. Also take advantage of the foreign key constraints and other structures that you have in place when you’re writing your T-SQL code
Take Advantage of Your Indexes: Write your code so that it can take advantage of any indexing on your table. If proper indexing does not exist then explore adding the appropriate indexes.
Write for the query optimizer: Take the time to understand how the optimizer works and write your code in such a way that you help it make the best optimization decisions.