T-SQL

SUM, AVG, and arithmetic overflow

You Shoulda Brought A Bigger Int Sometimes you run a query, and everything goes fine. For a while. For example, if I run this query in the 2010 copy of Stack Overflow, it finishes pretty quickly, and without error. Transact-SQL SELECT u.Id, u.DisplayName, SUM(p.Score) AS SumPostScore, AVG(c.Score) AS SumCommentScore FROM dbo.Users AS u JOIN dbo.Posts…
Read More

What’s New in SQL Server 2019: Faster Table Variables (And New Parameter Sniffing Issues)

For over a decade, SQL Server’s handling of table variables has been legendarily bad. I’ve long used this Stack Overflow query from Sam Saffron to illustrate terrible cardinality estimation: Transact-SQL declare @VoteStats table (PostId int, up int, down int) insert @VoteStats select PostId, up = sum(case when VoteTypeId = 2 then 1 else 0 end), down…
Read More

Stupid T-SQL Tricks

Presented without comment: Transact-SQL CREATE TABLE dbo.[FROM] ([SELECT] INT, [WHERE] INT, [LIKE] INT); GO SELECT [SELECT] FROM [FROM] WHERE [WHERE] LIKE [LIKE]; GO 1234 CREATE TABLE dbo.[FROM] ([SELECT] INT, [WHERE] INT, [LIKE] INT);GOSELECT [SELECT] FROM [FROM] WHERE [WHERE] LIKE [LIKE];GO Next up, can you break up a query with spaces? Yep: Transact-SQL SELECT * FROM…
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

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

Two Code Patterns That Don’t Virtualize Well

Virtualization used to be a really Big Deal™ for database admins: we had to do a lot of careful planning to get a virtualization project done right. These days, virtualization is more and more of a no-brainer: most apps make the transition just fine. Every now and then, though, an exception pops up – usually…
Read More

SQL Server Workarounds

360 Questions We’ve been asking you folks a lot about what you’d add to SQL Server, and we’ve gotten some great answers. There are even some that have been answered in the last couple versions of SQL Server. For instance, 2016 brought us STRING_SPLIT! SQL Server 2017 has a whole bunch of neat stuff: CONCAT_WS…
Read More

The Surprising Behavior of Trailing Spaces

In every training class we do, I learn stuff from students. Here’s last week’s surprise. Create a temp table, and put in a value with trailing spaces. Then query for it using the = and <> operators, using different numbers of trailing spaces: Transact-SQL CREATE TABLE #Wines (Winery VARCHAR(50)); INSERT INTO #Wines VALUES ('Cliff Lede…
Read More
Menu
{"cart_token":"","hash":"","cart_data":""}