Development

Do You Have Tables In Your Tables?

This Isn’t A Trick Question Hopefully it’ll get you thinking about your tables, and how they’re designed. One of the most consistent problems I see with clients is around wide tables. I don’t mean data types, I mean the number of columns. Going back to Michael Swart’s 10% Rule, if your tables have > 100…
Read More

A Query That Should Be Contradicted

Innocent Enough I was writing another query, and became enamored with the fact that HAVING will accept IS NULL or IS NOT NULL as a predicate. What I ended up writing as an example was this query: Transact-SQL SELECT v.PostId, SUM(v.UserId) AS whatever FROM dbo.Votes AS v WHERE v.UserId IS NULL GROUP BY v.PostId HAVING…
Read More

Election Injection

Setting politics aside (Lord knows I’d like to), this ABC 7 Chicago news story covers how Russians hacked the Illinois State Board of Election in 2016: SQL, an acronym for Structured Query Language, is a database programming language. An “SQL injection” is a common piece of cyber-trickery used to illegally gain access to government, financial,…
Read More

A Quirk In Parallel Hash Join Plans

Bailing, Takes Me Away Both of these queries will return zero rows, in the 2010 version of the Stack Overflow database. Transact-SQL SELECT COUNT(*) AS records FROM dbo.Votes AS v JOIN dbo.Posts AS p ON v.UserId = p.OwnerUserId WHERE v.CreationDate >= '2011-01-01'; SELECT COUNT(*) AS records FROM dbo.Votes AS v JOIN dbo.Posts AS p ON…
Read More

A Surprising Simplification Limitation

When It Comes To Simplification Rob Farley has my favorite material on it. There’s an incredible amount of laziness ingenuity built into the optimizer to keep your servers from doing unnecessary work. That’s why I’d expect a query like this to throw away the join: Transact-SQL SELECT COUNT(u.Id) FROM dbo.Users AS u JOIN dbo.Users AS…
Read More

Can Non-SARGable Predicates Ever Seek?

Cheating At Candy Crush The short answer is that yes, they can. But only with a little extra preparation. Before I show you what I mean, we should probably define what’s not SARGable in general. Wrapping columns in functions: ISNULL, COALESCE, LEFT, RIGHT, YEAR, etc. Evaluating predicates against things indexes don’t track: DATEDIFF(YEAR, a_col, b_col),…
Read More
Menu
{"cart_token":"","hash":"","cart_data":""}