Product has been added to your cart.

T-SQL

  1. Home
  3. Development
  5. T-SQL
    6. (Page 3)

The Silent Bug I Find in Most Triggers

Brent Ozar
T-SQL
34 Comments
Get Notebook – FAQ I don’t have a problem with triggers. They get the job done when you need to implement business logic in a hurry, and you’re not allowed to change the application. As long as you keep the number of statements to a minimum (say, 2-3), and don’t try to do something really…
Read More
Froid

Finding Froid’s Limits: Testing Inlined User-Defined Functions

Brent Ozar
Execution Plans, Parameter Sniffing, SQL Server 2019, T-SQL
16 Comments
This week, I’ve been writing about how SQL Server 2019’s bringing a few new features to mitigate parameter sniffing, but they’re more complex than they appear at first glance: adaptive memory grants, air_quote_actual plans, and adaptive joins. Today, let’s talk about another common cause of wildly varying durations for a single query: user-defined functions. Scalar…
Read More

Should we use stored procedures or queries built in the app?

Brent Ozar
T-SQL
69 Comments
A client asked a great architecture question that deserved its own blog post: Should we use more “stored procedures” for select, insert, update, delete or should that just be sent as queries from the application? If you ask a junior database administrator where to put something, she’ll probably say “in the database” because that’s the…
Read More

Tuning Dynamic SQL by Hand with Short Circuits

Brent Ozar
T-SQL
7 Comments
When we think about building dynamic SQL, we usually think about a stored procedure like this that takes input parameters, builds a string, and then executes that string. Here’s a simple example: Transact-SQL CREATE OR ALTER PROC dbo.usp_SearchUsers @SearchDisplayName NVARCHAR(40) = NULL, @SearchLocation NVARCHAR(100) = NULL, @SearchReputation INT = NULL AS BEGIN DECLARE @StringToExecute NVARCHAR(4000);…
Read More
No longer uses the index

Do Functions Stop You From Using Indexes?

Brent Ozar
Indexing, T-SQL
5 Comments
Say I’ve got a function in my WHERE clause: Transact-SQL SELECT DisplayName FROM dbo.Users WHERE LTRIM(RTRIM(DisplayName)) = 'Brent Ozar'; 123 SELECT DisplayName  FROM dbo.Users  WHERE LTRIM(RTRIM(DisplayName)) = 'Brent Ozar'; If I have an index on DisplayName, will SQL Server use it? Sure: Function in the WHERE clause Even though SQL Server can’t seek to “Brent Ozar,” it will…
Read More

What Is SQL Injection?

Brent Ozar
T-SQL
4 Comments
Say we have a stored procedure that queries the Stack Overflow database. We have two separate parameters, @DisplayName and @Location, so folks can search for people by name, location, or both. For performance reasons, we decide to build dynamic SQL: Transact-SQL CREATE OR ALTER PROC dbo.UserSearch @DisplayName NVARCHAR(40) = NULL, @Location NVARCHAR(100) = NULL AS…
Read More

SUM, AVG, and arithmetic overflow

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

Brent Ozar
Parameter Sniffing, SQL Server 2019, T-SQL
16 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
32 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

Quick Tips For Debugging Large Stored Procedures

Erik Darling
T-SQL
55 Comments
Hell Here! Get Your Fresh Hell, Here! Let’s face it — as far as developer tools go, SSMS is pretty bad. Intellisense? The jokes write themselves. Don’t get me wrong, SSMS is a good management tool, but it’s not a good development tool (and what kind of maniac sticks debug — F6 — next to execute —…
Read More

How to Get a Random Row from a Large Table

Brent Ozar
T-SQL
23 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
19 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