not all heroes wear a cape... thank you!

Do your own testing, Mr Lazy

Index has nothing to do with performance? The entire point of indexes is performance. Granted, there are scenarios where indexes can hurt performance, but just blankly saying "index has nothing to do with performance" is just wrong.

He didn't change the business logic. I believe you're talking about what he says at 13:50 (section starts at 11:25) - what he's saying is if you are changing your query to improve the performance, you must make sure that your new query is logically the same - that means, your modified query must always still return exactly the same result that the old query did. Otherwise if you don't, then if this query was a part of some application, then your change has just introduced a bug where the system isn't returning the correct results any more. In his example, he is querying a DateTime column for records on a particular date (i.e. from midnight to midnight - the 24 hour period of that date). In the first (bad) query, he achieves this in his WHERE clause by converting the DateTime value to a string which does not include the time component, and compares it to a string of the date he wants to get results for. In the fixed query, the new WHERE clause is selecting rows where the value is >= the date he wants results for AND < the following day. The improvement is that he's not converting the column to a string, instead he's keeping the data as a DateTime as it already was. The result is that the query will return results for that entire day, from midnight to midnight. Meaning, his new query is logically the same and returns exactly the same results - that's the part where he says to make sure your modified query is logically the same. In the improved query, now SQL Server is able to use the index to find the data much faster (seek - skipping only to the relevant rows in the table) because it isn't doing the string conversion. In the old query it would need to do a scan - it would need to convert the DateTime to a string for every row in the table before it is able to do the string comparison to get the results.

Use reverse(column) and use xxxx% instead of %xxxx

Problem is in min(plan_handle) as sample_sql_handle. Change it to: , min(plan_handle) as sample_plan_handle

The more indexes you have on a table, the slower INSERT commands will be since after every insert, EVERY index will be updated. That scales really badly if you have a big table with various columns with too many indexes, not to mention it will consume more disk storage. Indexes trade insert performance in favor of search performance but its usually not an issue if you use indexes in a smart way (IE not having an index for unused columns and such)

@@pizzaiolom thanks a lot

Please drop an email to contact@sqlmaestros.com