Tips and Tricks about query optimization [SQL Server 2005]

Question

I am asking this question in stackoverflow because its the right place to ask... I know its a very vast topic to start but some small ones which may be really handy... It might be useful for young developers like me to know about query optimization.. Some Tips and Tricks about query optimization in SQL Server 2005..

Accepted Answer

Based on questions here

- Avoid datatype precedence (eg always much like for like, including length of varchar etc)

eg

```
...WHERE tinyintcol = @intvalue
 means a conversion of the column and invalidates an index
...WHERE tinyintcol = @tinyintvalue
```

- Avoid functions on columns in WHERE clauses

eg

```
 ...WHERE DATEADD(day, 1, MyCol) > GETDATE()
 should be
 ...WHERE MyCol > DATEADD(day, -1, GETDATE())
```

Covering indexes

GUIDs: not clustered indexes

Tips and Tricks about query optimization [SQL Server 2005]

Solution

Problem