LinkedIn
YouTube
GitHub

T-SQL

  1. Home
  3. Development
  5. T-SQL

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

Brent Ozar
SQL Server 2019, T-SQL
14 Comments
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

Brent Ozar
Bad Idea Jeans, T-SQL
29 Comments
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 Reduce the CPU Overhead of Dynamic SQL

Tara Kizer
Development, T-SQL
14 Comments
Dynamic SQL is a good choice for catch-all type queries, but you have to be careful how you execute the dynamic string. For frequently executed queries, “EXEC sp_executesql @sql”is a good choice but at the risk of encountering parameter sniffing issues. For queries that aren’t executed very often, “EXEC (@sql)” can be used, but you…
Read More

How to Get a Random Row from a Large Table

Brent Ozar
T-SQL
16 Comments
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

Erik Darling
T-SQL
15 Comments
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

Brent Ozar
T-SQL
5 Comments
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

Brent Ozar
T-SQL
13 Comments
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

Erik Darling
Development, SQL Server, T-SQL
14 Comments
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

Brent Ozar
T-SQL
15 Comments
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