Download - 10 Ways To Abuse T-SQL
![Page 1: 10 Ways To Abuse T-SQL](https://reader033.vdocuments.us/reader033/viewer/2022060123/559705d21a28abc20e8b45cb/html5/thumbnails/1.jpg)
10 Ways To Abuse T-SQLCommon performance
mistakes, coding errors, and how to prevent them
![Page 2: 10 Ways To Abuse T-SQL](https://reader033.vdocuments.us/reader033/viewer/2022060123/559705d21a28abc20e8b45cb/html5/thumbnails/2.jpg)
Tracy McKibbenDBA Supervisor, Senior SQL Server DBA
Pearson VUE
Blog: realsqlguy.com
Twitter: @RealSQLGuy
I’m not saying I’m Batman, I’m just saying that nobody has
ever seen me and Batman in the same room together...
![Page 3: 10 Ways To Abuse T-SQL](https://reader033.vdocuments.us/reader033/viewer/2022060123/559705d21a28abc20e8b45cb/html5/thumbnails/3.jpg)
10 Ways To Abuse T-SQL
• Procedural coding• User-defined functions• Views• SELECT *• Non-SARGable queries• Bandaids• Mismatched Data Types• NULL• Incorrect or unnecessary ordering• Single-row triggers
![Page 4: 10 Ways To Abuse T-SQL](https://reader033.vdocuments.us/reader033/viewer/2022060123/559705d21a28abc20e8b45cb/html5/thumbnails/4.jpg)
Procedural Coding
Symptoms include:
• loops
• cursors
• repeated SQL statements
• temp tables
• if/else structures
![Page 5: 10 Ways To Abuse T-SQL](https://reader033.vdocuments.us/reader033/viewer/2022060123/559705d21a28abc20e8b45cb/html5/thumbnails/5.jpg)
Procedural Coding
Comparable to carrying groceries to the car one item at a time, when you could be using this instead.
![Page 6: 10 Ways To Abuse T-SQL](https://reader033.vdocuments.us/reader033/viewer/2022060123/559705d21a28abc20e8b45cb/html5/thumbnails/6.jpg)
Procedural Coding
Help your DBA out, learn set-based coding methods. Be a team player.
![Page 7: 10 Ways To Abuse T-SQL](https://reader033.vdocuments.us/reader033/viewer/2022060123/559705d21a28abc20e8b45cb/html5/thumbnails/7.jpg)
User-Defined Functions
Useful for
• code re-use
• packaging complex logic
• readability
![Page 8: 10 Ways To Abuse T-SQL](https://reader033.vdocuments.us/reader033/viewer/2022060123/559705d21a28abc20e8b45cb/html5/thumbnails/8.jpg)
User-Defined Functions
Useful for
• code re-use
• packaging complex logic
• Readability
• killing performance
• complicating query tuning
• inducing DBA nightmares
![Page 9: 10 Ways To Abuse T-SQL](https://reader033.vdocuments.us/reader033/viewer/2022060123/559705d21a28abc20e8b45cb/html5/thumbnails/9.jpg)
User-Defined Functions
Functions have their place, but they're not always the right tool for the job.
![Page 10: 10 Ways To Abuse T-SQL](https://reader033.vdocuments.us/reader033/viewer/2022060123/559705d21a28abc20e8b45cb/html5/thumbnails/10.jpg)
Views
Like user-defined functions, views can be used to
• re-use code
• hide complex queries
• make large queries more readable
![Page 11: 10 Ways To Abuse T-SQL](https://reader033.vdocuments.us/reader033/viewer/2022060123/559705d21a28abc20e8b45cb/html5/thumbnails/11.jpg)
Views
But what's lurking beneath the surface?
![Page 12: 10 Ways To Abuse T-SQL](https://reader033.vdocuments.us/reader033/viewer/2022060123/559705d21a28abc20e8b45cb/html5/thumbnails/12.jpg)
Views
Views can hide some scary performance issues. Make sure your view is clear.
![Page 13: 10 Ways To Abuse T-SQL](https://reader033.vdocuments.us/reader033/viewer/2022060123/559705d21a28abc20e8b45cb/html5/thumbnails/13.jpg)
SELECT *
Are you SURE you want the whole thing?
![Page 14: 10 Ways To Abuse T-SQL](https://reader033.vdocuments.us/reader033/viewer/2022060123/559705d21a28abc20e8b45cb/html5/thumbnails/14.jpg)
SELECT *
What's wrong with SELECT *?
• prone to table scans or lookups
• difficult to support with indexes
• widely considered sloppy and "lazy“
• dangerous in views
![Page 15: 10 Ways To Abuse T-SQL](https://reader033.vdocuments.us/reader033/viewer/2022060123/559705d21a28abc20e8b45cb/html5/thumbnails/15.jpg)
SELECT *
Be a hero.
Don't use SELECT * in code that counts.
![Page 16: 10 Ways To Abuse T-SQL](https://reader033.vdocuments.us/reader033/viewer/2022060123/559705d21a28abc20e8b45cb/html5/thumbnails/16.jpg)
Non-SARGable Queries
What is a SARGable query?
A query that is Search ARGument-able.
What does that mean?
![Page 17: 10 Ways To Abuse T-SQL](https://reader033.vdocuments.us/reader033/viewer/2022060123/559705d21a28abc20e8b45cb/html5/thumbnails/17.jpg)
Non-SARGable Queries
Which of these are SARGable expressions?
• SalesPersonID <> 10
• SalesPersonID = 99
• SalesPersonID >= 100
• SalesPersonID NOT IN (some subquery)
• SalesPersonID IN (some subquery)
• SalesPersonID IS NULL
• ISNULL(SalesPersonID, 0) = 0
![Page 18: 10 Ways To Abuse T-SQL](https://reader033.vdocuments.us/reader033/viewer/2022060123/559705d21a28abc20e8b45cb/html5/thumbnails/18.jpg)
Non-SARGable Queries
Which of these are SARGable expressions?
• SalesPersonID <> 10 *
• SalesPersonID = 99
• SalesPersonID >= 100
• SalesPersonID NOT IN (some subquery)
• SalesPersonID IN (some subquery)
• SalesPersonID IS NULL
• ISNULL(SalesPersonID, 0) = 0 **
* SQL 2008+ ** sargable if column is NOT NULLable
![Page 19: 10 Ways To Abuse T-SQL](https://reader033.vdocuments.us/reader033/viewer/2022060123/559705d21a28abc20e8b45cb/html5/thumbnails/19.jpg)
Non-SARGable Queries
SARG makes DBA's happy!
![Page 20: 10 Ways To Abuse T-SQL](https://reader033.vdocuments.us/reader033/viewer/2022060123/559705d21a28abc20e8b45cb/html5/thumbnails/20.jpg)
Bandaids
Don't use bandaids when corrective surgery is required
![Page 21: 10 Ways To Abuse T-SQL](https://reader033.vdocuments.us/reader033/viewer/2022060123/559705d21a28abc20e8b45cb/html5/thumbnails/21.jpg)
Bandaids
T-SQL makes it easy, too easy, to cover up coding mistakes, often with a price.
• DISTINCT (don't hide dupes, stop fetching them in the first place)
• NOLOCK (this is NOT the cure for blocking)
• UNION (figure out the logic for a proper WHERE clause)
![Page 22: 10 Ways To Abuse T-SQL](https://reader033.vdocuments.us/reader033/viewer/2022060123/559705d21a28abc20e8b45cb/html5/thumbnails/22.jpg)
Bandaids
Be tough - rip off the bandaids!
Ya big crybaby....
![Page 23: 10 Ways To Abuse T-SQL](https://reader033.vdocuments.us/reader033/viewer/2022060123/559705d21a28abc20e8b45cb/html5/thumbnails/23.jpg)
Mismatched Data Types
Wrong. So wrong.Some comparisons should never be made.
![Page 24: 10 Ways To Abuse T-SQL](https://reader033.vdocuments.us/reader033/viewer/2022060123/559705d21a28abc20e8b45cb/html5/thumbnails/24.jpg)
Mismatched Data Types
Some transformations conversions work just fine. Some create performance problems. Some just fail miserably.
Watch out for:
• NVARCHAR to VARCHAR
• character to numeric
• datetime manipulations to remove time, DATEDIFF
![Page 25: 10 Ways To Abuse T-SQL](https://reader033.vdocuments.us/reader033/viewer/2022060123/559705d21a28abc20e8b45cb/html5/thumbnails/25.jpg)
Mismatched Data Types
Keep your data types compatible
![Page 26: 10 Ways To Abuse T-SQL](https://reader033.vdocuments.us/reader033/viewer/2022060123/559705d21a28abc20e8b45cb/html5/thumbnails/26.jpg)
NULL
Improper handling of NULL values can lead to errors and/or incorrect results.
![Page 27: 10 Ways To Abuse T-SQL](https://reader033.vdocuments.us/reader033/viewer/2022060123/559705d21a28abc20e8b45cb/html5/thumbnails/27.jpg)
NULL
• NULL has NO VALUE
• NULL does not mean zero
• NULL does not mean blank
• NULL does not mean an empty string
• NULL means NULL
![Page 28: 10 Ways To Abuse T-SQL](https://reader033.vdocuments.us/reader033/viewer/2022060123/559705d21a28abc20e8b45cb/html5/thumbnails/28.jpg)
NULL
NULL values aren't difficult to work with, but be sure to read the fine print.
![Page 29: 10 Ways To Abuse T-SQL](https://reader033.vdocuments.us/reader033/viewer/2022060123/559705d21a28abc20e8b45cb/html5/thumbnails/29.jpg)
Incorrect/Unnecessary Ordering
Don't make assumptions about how data will be ordered.
If a specific order is required, specify it.
![Page 30: 10 Ways To Abuse T-SQL](https://reader033.vdocuments.us/reader033/viewer/2022060123/559705d21a28abc20e8b45cb/html5/thumbnails/30.jpg)
Incorrect/Unnecessary Ordering
Without ORDER BY, the query optimizer will surprise you with a "random" sort order, dictated by the fastest query plan it could find.
Specifying an ORDER BY can affect the query plan selected, thus affecting the performance of the query.
![Page 31: 10 Ways To Abuse T-SQL](https://reader033.vdocuments.us/reader033/viewer/2022060123/559705d21a28abc20e8b45cb/html5/thumbnails/31.jpg)
Incorrect/Unnecessary Ordering
Sometimes the order of things doesn't matter, sometimes it makes all the difference.
![Page 32: 10 Ways To Abuse T-SQL](https://reader033.vdocuments.us/reader033/viewer/2022060123/559705d21a28abc20e8b45cb/html5/thumbnails/32.jpg)
Single-row Triggers
This kind of thinking has no place in this movie. Nor does it belong inside a trigger.
Triggers fire per operation, not per row, and must be written to handle multi-row operations.
![Page 33: 10 Ways To Abuse T-SQL](https://reader033.vdocuments.us/reader033/viewer/2022060123/559705d21a28abc20e8b45cb/html5/thumbnails/33.jpg)
Single-row Triggers
Sometimes there should be only one, but not inside a trigger.
![Page 34: 10 Ways To Abuse T-SQL](https://reader033.vdocuments.us/reader033/viewer/2022060123/559705d21a28abc20e8b45cb/html5/thumbnails/34.jpg)
Lessons Learned
• Use set-based methods• Be careful with user-defined
functions and views• SELECT * - lazy and dangerous• Always obey SARG• Don’t use bandaids• Watch your data types• NULL – it’s not nothing• Order your results carefully• Triggers – many rows, not one
![Page 35: 10 Ways To Abuse T-SQL](https://reader033.vdocuments.us/reader033/viewer/2022060123/559705d21a28abc20e8b45cb/html5/thumbnails/35.jpg)
Any Questions?