Product has been added to your cart.

T-SQL

Using ChatGPT for T-SQL Code Reviews

Brent Ozar
T-SQL
23 Comments
For this month’s T-SQL Tuesday, Pinal Dave asked us if AI has helped us with our SQL Server jobs. For me, there’s been one instant, clear win: code reviews. I usually keep a browser tab open with ChatGPT 4, and I paste this in as a starting point: You are an experienced, diligent database developer…
Read More

Query Exercise: Finding Sister Locations to Help Each Other

Brent Ozar
Query Exercises
12 Comments
For this week’s query exercise, let’s start with a brief query to get a quick preview of what we’re dealing with: Transact-SQL SELECT TOP 100 Location, COUNT(*) AS Population, AVG(Reputation) AS AvgReputation FROM dbo.Users GROUP BY Location ORDER BY COUNT(*) DESC; 123456 SELECT TOP 100 Location, COUNT(*) AS Population, AVG(Reputation) AS AvgReputationFROM dbo.UsersGROUP BY LocationORDER…
Read More

Query Exercise: Find Recent Superstars

Brent Ozar
Query Exercises
44 Comments
For this week’s Query Exercise, we’re working with the Stack Overflow database, and our business users have asked us to find the new superstars. They’re looking for the top 1000 users who were created in the last 90 days, who have a reputation higher than 50 points, from highest reputation to lowest. In your Stack…
Read More

Finding Long Values Faster: Answers & Discussion

Brent Ozar
Query Exercise Answers
8 Comments
In last week’s Query Exercise, our developers had a query that wasn’t going as fast as they’d like: Transact-SQL CREATE INDEX DisplayName ON dbo.Users(DisplayName); GO SELECT * FROM dbo.Users WHERE LEN(DisplayName) > 35; 12345 CREATE INDEX DisplayName ON dbo.Users(DisplayName);GOSELECT *  FROM dbo.Users  WHERE LEN(DisplayName) > 35; The query had an index, but SQL Server was refusing to…
Read More

Query Exercise: Finding Long Values Faster

Brent Ozar
Query Exercises
43 Comments
Our developers have come to us with a problem query that isn’t as fast as they’d like. Using any Stack Overflow database: Transact-SQL CREATE INDEX DisplayName ON dbo.Users(DisplayName); GO SELECT * FROM dbo.Users WHERE LEN(DisplayName) > 35; 12345 CREATE INDEX DisplayName ON dbo.Users(DisplayName);GOSELECT *  FROM dbo.Users  WHERE LEN(DisplayName) > 35; It has an index, but SQL Server…
Read More

Improving Cardinality Estimation: Answers & Discussion

Brent Ozar
Query Exercise Answers
12 Comments
Your challenge for last week was to take this Stack Overflow database query to show the top-ranking users in the most popular location: Transact-SQL CREATE INDEX Location ON dbo.Users(Location); GO CREATE OR ALTER PROC dbo.GetTopUsersInTopLocation AS SELECT TOP 200 u.Reputation, u.Id, u.DisplayName, u.WebsiteUrl, u.CreationDate FROM dbo.Users u WHERE u.Location = (SELECT TOP 1 Location FROM…
Read More

The Last Ticket/Issue I Closed #TSQL2sday

Brent Ozar
T-SQL
19 Comments
For this month’s T-SQL Tuesday, I asked y’all to write about the most recent ticket or issue that you closed. (If you want to see other peoples’ posts, you can check out the comments on that invite post, or wait til next week and I’ll publish a wrap-up of everyone’s answers.) A past client emailed…
Read More

Query Exercise: Improving Cardinality Estimation

Brent Ozar
Query Exercises
33 Comments
Your challenge for this week is to tune a query. Say Stack Overflow has a dashboard that shows the top-ranking users in their most popular location. It’s even got an index to support it: Transact-SQL CREATE INDEX Location ON dbo.Users(Location); GO CREATE OR ALTER PROC dbo.GetTopUsersInTopLocation AS SELECT TOP 200 u.Reputation, u.Id, u.DisplayName, u.WebsiteUrl, u.CreationDate…
Read More

Query Exercise: Find Tagged Questions Faster.

Brent Ozar
Query Exercises
42 Comments
For this week’s Query Exercise, we’re going to start with an existing query that has performance problems from time to time, depending on how it’s used. This query’s goal is to find the top-voted Stack Overflow questions for any given tag – for example, here are the top-voted questions tagged “sql-server”. What are tags? I’m…
Read More

Find Posts with the Wrong CommentCount: Answers & Discussion

Brent Ozar
Query Exercise Answers
4 Comments
Your Query Exercise was to find denormalization accuracy problems: checking the accuracy of a reporting column, Posts.CommentCount. There were two parts: finding the top 100 most problematic Posts with the biggest variances, and thinking about a long term solution to keep the CommentCount accuracy as high as practical. Question 1: Finding the Problematic Posts Your…
Read More

Query Exercise: Find Foreign Key Problems

Brent Ozar
Query Exercises
21 Comments
For 2024, I’m trying something new: weekly homework challenges! For this week, let’s say we’ve decided to implement foreign keys, and we need to find data that’s going to violate our desired keys. We’re going to use the Stack Overflow database, and we’ll focus on these 3 tables: dbo.Users table: with Id column as its…
Read More