EXEC vs. sp_executeSQL

When we want to execute a TSQL string we can use both EXEC and sp_executesql statements. But there are some very important differences between them sp_executesql allows for statements to be parameterized Therefore It’s more secure than EXEC in terms of SQL injection sp_executesql can leverage cached query plans. The TSQL string is built only…

8

Data Mismatch on WHERE Clause might Cause Serious Performance Problems

Like Applying Function on WHERE clause problem, any datatype mismatch on WHERE clause might cause serious performance problems. Predicates on both sides of comparisons (for example on WHERE clause) always must match datatypes. It means that if the left side of predicate is integer than the right side needs to be integer. If the datatypes…

1

Applying Functions on WHERE Clause Columns might Cause Serious Performance Problems

Any function such as collate, convert, substring etc. applied on a column might make impossible for SQL Server to use any index on this column. This because SQL Server query optimizer considers the column after the function as a new column, because the column values are not stored in the index pages as returned by…

2

SQLSaturday #243, Cape Town : “Top 10 Performance Tips for SQL Server Developers”

I just presented my “Top 10 Performance Tips for SQL Server Developers” session at SQLSaturday #243, Cape Town. It was really awesome audience. You can download the deck and demo scripts below. Top 10 Performance Tips for SQL Server Developers Performance of a SQL Server depends on many factors and due to the nature of…

0