SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

SELECT Amount = 'Take a shot if...', Condition
FROM
(
  /* Bloopers */
  VALUES
  ('Zoomit'),
  ('Intel drivers'),
  ('Video starts after 4 minutes'),
  ('The video is an advertisement for a Sommarskog video')
) AS Conditions(Condition)
UNION ALL
SELECT Amount = 'Take a small sip if...', Condition
FROM
(
  /* Erik's in jokes and jabs */
  VALUES
  ('"Interns at Microsoft"'),
  ('Bats'),
  ('Beer Gut Mangzine'),
  ('Adidas T-shirt'),
  ('"Reasonable rates"'),
  ('Subtle jab at Brent Ozar and the price of his training'),
  ('Huge praise of Paul White'),
  ('"Best SQL Server consultant in..."'),
  ('"Microsoft friendship tax"'),
  ('New Zealand')
) AS Conditions(Condition)
UNION ALL
SELECT Amount = 'Take a sip if...', Condition
FROM
(
  /* Common knowledge SQL Server problems that Erik mentions a lot */
  VALUES
  ('CTEs are not a performance feature'),
  ('Operator costs are useless, stupid, and broken.'),
  ('Pagination'),
  ('Scalar UDFs'),
  ('Table variables are almost always wrong.'),
  ('Jab at Paul White''s SQL formatting')
) AS Conditions(Condition)
UNION ALL
SELECT Amount = 'Take a drink if...', Condition
FROM
(
  /* Erik's favourite legitimate and fleshed-out complaints */
  VALUES
  ('Query Store beats the plan cache for almost everything. Microsoft should use it more.
'),
  ('Microsoft made Extended Events useless by putting it all in XML.'),
  ('The Query Store GUI is an abomination'),
  ('Read Committed is a garbage isolation level.'),
  ('SQL Server does not understand numbers.'),
  ('Spools, particularly eager index spools, are often evil.'),
  ('The cloud is slow, particularly the disks.'),
  ('Bobby tables is stupid.'),
  ('Parallel merge joins are very prone to sort/exchange spills and can introduce nasty cross-thread dependencies.')
) AS Conditions(Condition)
UNION ALL
SELECT Amount = 'Take two drinks if...', Condition
FROM
(
  /* Things I truly learned from Erik */
  VALUES
  ('Don''t be afraid of query hints. If SQL Server did something stupid, hints let you ask it why.'),
  ('It can be hard to get a seek in things that have OR clauses across multiple tables. It is always okay to consider FORCESEEK.'),
  ('Don''t be afraid to hint the legacy cardinality estimator.'),
  ('Clock time is the only metric that really matters.'),
  ('Encourage batch mode by forcing hash joins'),
  ('Replace scalar aggregates sub-queries with table-valued window function sub-queries'),
  ('Indexed views are a tragically abandoned feature, but Standard Edition has no better substitute for true batch mode.'),
  ('You don''t have a parameter sniffing problem, you have a parameter sensitivity problem.'),
  ('If a pure SELECT is doing writes, it probably has evil spools such as an eager index spool.'),
  ('PAGE compress and SORT_IN_TEMPDB everything. Just do it.'),
  ('1 = (SELECT 1)'),
  ('Serial parallel queries exist and can be fixed with temp tables.'),
  ('Equality, Sort, Range.'),
  ('Temp tables are like parameter sniffing.'),
  ('Isolate relational sub-queries from the informational sub-queries. Use window functions if batch mode is available.'),
  ('Columnstore is good for paging.'),
  ('When aiming for batch mode, aggregate individual tables and then join them, rather than the reverse'),
  ('ISNULL and COALESCE are a bad way to do dynamic searches.'),
  ('IF branching is very parameter sensitive, unless you use wrapper stored procedures.'),
  ('Conditional logic in JOIN/WHERE is likely to get bad cardinality estimates.'),
  ('CROSS APPLY generally has better performance than putting CASE in your WHERE clauses.'),
  ('Avoid IN clauses or literals, try joining to VALUES instead and enjoy batch mode.'),
  ('Often, you just need to fix a key lookup enough to remove the predicate that comes with it.'),
  ('Get nervous if you see a filter, unless it is for something that needs one like a HAVING clause or a filter on a window function. They usually mean you have a complicated or non-sargable predicate.')

) AS Conditions(Condition)
UNION ALL
SELECT Amount = 'Finish your drink if...', Condition
FROM
(
  /* Erik's best tips; the things that have changed my SQL programming forever and made me money */
  VALUES
  ('Dynamic SQL is the best way to get around parameter sensitivity.'),
  ('Columnstore is great for unpredictable searches.'),
  ('Slow OR can be replaced with UNION ALL.'),
  ('JOIN [...] ON EXISTS (SELECT A.B INTERSECT SELECT C.D) is valid syntax. No FROM needed!'),
  ('Build your own plan guide with dynamic SQL.')
) AS Conditions(Condition);

Amount	Condition
Take a shot if...	Zoomit
Take a shot if...	Intel drivers
Take a shot if...	Video starts after 4 minutes
Take a shot if...	The video is an advertisement for a Sommarskog video
Take a small sip if...	"Interns at Microsoft"
Take a small sip if...	Bats
Take a small sip if...	Beer Gut Mangzine
Take a small sip if...	Adidas T-shirt
Take a small sip if...	"Reasonable rates"
Take a small sip if...	Subtle jab at Brent Ozar and the price of his training
Take a small sip if...	Huge praise of Paul White
Take a small sip if...	"Best SQL Server consultant in..."
Take a small sip if...	"Microsoft friendship tax"
Take a small sip if...	New Zealand
Take a sip if...	CTEs are not a performance feature
Take a sip if...	Operator costs are useless, stupid, and broken.
Take a sip if...	Pagination
Take a sip if...	Scalar UDFs
Take a sip if...	Table variables are almost always wrong.
Take a sip if...	Jab at Paul White's SQL formatting
Take a drink if...	Query Store beats the plan cache for almost everything. Microsoft should use it more.
Take a drink if...	Microsoft made Extended Events useless by putting it all in XML.
Take a drink if...	The Query Store GUI is an abomination
Take a drink if...	Read Committed is a garbage isolation level.
Take a drink if...	SQL Server does not understand numbers.
Take a drink if...	Spools, particularly eager index spools, are often evil.
Take a drink if...	The cloud is slow, particularly the disks.
Take a drink if...	Bobby tables is stupid.
Take a drink if...	Parallel merge joins are very prone to sort/exchange spills and can introduce nasty cross-thread dependencies.
Take two drinks if...	Don't be afraid of query hints. If SQL Server did something stupid, hints let you ask it why.
Take two drinks if...	It can be hard to get a seek in things that have OR clauses across multiple tables. It is always okay to consider FORCESEEK.
Take two drinks if...	Don't be afraid to hint the legacy cardinality estimator.
Take two drinks if...	Clock time is the only metric that really matters.
Take two drinks if...	Encourage batch mode by forcing hash joins
Take two drinks if...	Replace scalar aggregates sub-queries with table-valued window function sub-queries
Take two drinks if...	Indexed views are a tragically abandoned feature, but Standard Edition has no better substitute for true batch mode.
Take two drinks if...	You don't have a parameter sniffing problem, you have a parameter sensitivity problem.
Take two drinks if...	If a pure SELECT is doing writes, it probably has evil spools such as an eager index spool.
Take two drinks if...	PAGE compress and SORT_IN_TEMPDB everything. Just do it.
Take two drinks if...	1 = (SELECT 1)
Take two drinks if...	Serial parallel queries exist and can be fixed with temp tables.
Take two drinks if...	Equality, Sort, Range.
Take two drinks if...	Temp tables are like parameter sniffing.
Take two drinks if...	Isolate relational sub-queries from the informational sub-queries. Use window functions if batch mode is available.
Take two drinks if...	Columnstore is good for paging.
Take two drinks if...	When aiming for batch mode, aggregate individual tables and then join them, rather than the reverse
Take two drinks if...	ISNULL and COALESCE are a bad way to do dynamic searches.
Take two drinks if...	IF branching is very parameter sensitive, unless you use wrapper stored procedures.
Take two drinks if...	Conditional logic in JOIN/WHERE is likely to get bad cardinality estimates.
Take two drinks if...	CROSS APPLY generally has better performance than putting CASE in your WHERE clauses.
Take two drinks if...	Avoid IN clauses or literals, try joining to VALUES instead and enjoy batch mode.
Take two drinks if...	Often, you just need to fix a key lookup enough to remove the predicate that comes with it.
Take two drinks if...	Get nervous if you see a filter, unless it is for something that needs one like a HAVING clause or a filter on a window function. They usually mean you have a complicated or non-sargable predicate.
Finish your drink if...	Dynamic SQL is the best way to get around parameter sensitivity.
Finish your drink if...	Columnstore is great for unpredictable searches.
Finish your drink if...	Slow OR can be replaced with UNION ALL.
Finish your drink if...	JOIN [...] ON EXISTS (SELECT A.B INTERSECT SELECT C.D) is valid syntax. No FROM needed!
Finish your drink if...	Build your own plan guide with dynamic SQL.