Development

Why sp_prepare Isn’t as “Good” as sp_executesql for Performance

sp_prepare For Mediocre You may remember me from movies like Optimize for… Mediocre? and Why You’re Tuning Stored Procedures Wrong (the Problem with Local Variables)! Great posts, Kendra! Following the same theme, we found this issue while looking at queries issued from JDBC. Specifically, the prepared statement class seems to cause queries to hit the density vector…
Read More

How to Get a Random Row from a Large Table

Method 1, Bad: ORDER BY NEWID() Easy to write, but it performs like hot, hot garbage because it scans the entire clustered index, calculating NEWID() on every row: The plan with the scan That took 6 seconds on my machine, going parallel across multiple threads, using tens of seconds of CPU for all that computing…
Read More

Fifteen Things I Hate About ISNUMERIC

Yello! Transact-SQL SELECT ISNUMERIC('$') AS [What] UNION ALL SELECT ISNUMERIC('£') AS [What] UNION ALL SELECT ISNUMERIC(',') AS [What] UNION ALL SELECT ISNUMERIC('.') AS [What] UNION ALL SELECT ISNUMERIC('0e+99') AS [What] UNION ALL SELECT ISNUMERIC('2e2') AS [What] UNION ALL SELECT ISNUMERIC('12D4') AS [What] UNION ALL SELECT ISNUMERIC(',1,1,1,1,1,1,1') AS [What] UNION ALL SELECT ISNUMERIC('-') AS [What] UNION…
Read More
Pocket Square

The Annals of Hilariously Bad Code, Part 2

You’re Crazy In Part 1, I showed you code that I think has some anti-patterns in it. In case you didn’t recognize it, it’s actually code that Microsoft wrote. It’s from sp_delete_backuphistory, and it is plum awful. I have a lot of personal dislike for this one, because after inheriting a server with a 25GB msdb,…
Read More

Using LIKE on Integers Gets You Implicit Conversion

Using the Stack Overflow public export, take these two queries looking for a particular user by Id (the clustering key): Transact-SQL SELECT * FROM dbo.Users WHERE Id = 26837; SELECT * FROM dbo.Users WHERE Id LIKE 26837; 12 SELECT * FROM dbo.Users WHERE Id = 26837;SELECT * FROM dbo.Users WHERE Id LIKE 26837; The first…
Read More
Menu
{"cart_token":"","hash":"","cart_data":""}