What are some SQL Server Query features / suggestions to avoid?

Question

What are some examples of SQL Server Query features / suggestions to avoid?

I recently found out that the NOT IN clause degrades performance.

Do you have more examples?

Answer 1

The reason why NOT IN should be avoided is not really performance, because it has really amazing behavior when the collection contains zero. For instance:

 select 1 where 1 not in (2,null) 

This will not return strings because where interpreted as:

 where 1 <> 2 and 1 <> null 

The first 1 <> null evaluates to unknown. Then 1 <> 2 and unknown is evaluated as unknown. This way you will not get any rows.

Answer 2

I avoid correlated subqueries (uncorrelated subqueries and views are ok) and any cursors I can avoid. Also avoid when you can make loops. Think of datasets, not string processing.

If you use UNION, check if UNION ALL will work. There is a potential difference in results, so be sure to make changes before making any changes.

I always look at the word DISTINCT as a hint to see if there is a better way to present the data. DISTINCT is expensive compared to using a view or some other way to avoid using it.

Avoid the syntax of the implied connection to avoid accidental cross-connection (which people often fix, shudder, with crisp). (Creating a list of 4 million records, and then making differences to get the three you want, is expensive.)

Avoid views that trigger other views! We have people who designed a whole customer database in this way, and the performance is AWESOME! Do not go down this path.

Avoid syntax like WHERE MyField Like "% test%"

These are other irrelevant arguments that the optimizer may contain from using indexes.

Answer 3

Avoid

CURSOR - use a dial-based option

SELECT * - explicitly indicate columns

EXEC (@dynamic_sql_with_input_parms) - use sp_executesql with input parameters.

Answer 4

I recently changed the view from

 Select Row1, Row2 FROM table Where blahID = FKblahID UNION Select Row1, Row2 FROM table2 Where blah2ID = FKblahID 

simply

 Select Row1, Row2 FROM table Where blahID = FKblahID 

and saw that the query that ran ~ 8 minutes now only required ~ 20 seconds, and not 100% sure why such a big change.

The second union returned only 200 records, and the first returned a couple of thousand.