Blog

The Top 3 Mistakes T-SQL Developers Make

Over the years, I’ve done all kinds of awful things with T-SQL and made countless mistakes. Some were harmless; others were borderline catastrophic (exciting times!). I was curious what kind of horrible mistakes other people make, so I threw the question out to Twitter.

Doug: Worst Dev Mistakes?

Every answer I got was unique, which was both telling (so many ways for developers to mess up) and fascinating (no consensus which was worst). Since I didn’t get any winners by popular vote, here are the top three answers I agree with most, with the worst mistake first:

#1: CODING FOR TODAY

MidnightDBA:Growth

We’ve all been there — we just need a script or stored procedure to run and we’re under the gun to get it deployed. The problem here is that as soon as it works and passes QA — you do have QA, don’t you? — we call it a success and don’t look back. We don’t account for any kind of future growth in data size or user base. Instead of moving on, this is the perfect time to check the working copy into source control and then start refactoring to improve performance.

If we don’t revisit and revise our code, we end up with a server full of code that “ran well enough at the time” but now won’t scale. Future-proofing is never at the top of anyone’s list of priorities but it’s just like exercise and healthy eating. We all know we need to do it. It’s a matter of making the commitment to write leaner, healthier code before it clogs the SQL Server’s arteries or gives it a heart attack outright.

There is no better time to refactor than when you get it to meet requirements. If you don’t start refactoring right away, you’ll forget all of the nuance and context involved with the code, you probably won’t remember how/why you arrived at the code that’s there now, and you may not even get permission to spend time on it later. Future You will thank Present You for it.

THE FIX: Buffer your development time to include refactoring, and make sure you have a performance goal in mind. For example: “We need this procedure to return the top five recommendations for the specified account in under 1500 milliseconds.”

#2: NOLOCK

Nic Cain: Nolock

Do you care if your query results are wrong? No? Congratulations, NOLOCK might be right for you!

The trouble with NOLOCK is twofold: developers usually don’t fully understand the risks involving dirty reads, and when they do, they often leave it in code because it’s better to risk sketchy results than move back to the suburbs of Slowville.

There are appropriate circumstances for NOLOCK, but developers often add it blindly as a performance enhancer, not a legitimate solution.

THE FIX: If the risks that come with NOLOCK aren’t acceptable, you can usually fix those performance problems either with code or index changes. For example, if a SELECT and UPDATE are fighting over the same index and locking each other out, an index tailored to the SELECT statement will prevent it from waiting for the UPDATE to release its lock.

#3: CURSORS

Aaron Bertrand: cursors

Cursor-Stalking Owl

“I shall hunt my cursors like silent death.”

Cursors do terrible, terrible things to performance. Every developer has this moment of enlightenment at some point. As soon as we learn cursors are performance killers, we hunt them down like a starving owl in a field of mice. We shouldn’t be embarrassed that we wrote them in the first place; we were solving the problem the best way we knew how at the time. Still, there’s a universal sense of shame when one developer sees another developer’s cursor, as if the neighbor passed by our open garage and spotted the recycling bin full of cheap beer cans and Cat Fancy magazines.

Like NOLOCK, there are times it’s appropriate to use a cursor, but those occasions are very rare.

THE FIX: Write code that operates in sets, rather than one row at a time.

HONORABLE MENTION:

  • Overuse of dynamic SQL
  • Security by obscurity
  • No indexes
  • Incorrect data types, leading to implicit conversions
  • Not following their own best practices
  • The same mistakes they made two years ago
  • Hundreds of columns in a table
  • Triggers

Why You Simply Must Have a Date Table [Video]

As a developer, one of the things I can no longer live without is a date table. Who wants to type DATENAME and DATEPART over and over again? Not this guy, and once you have a date table, you won’t want to either.

In this 16-minute video, I’ll give you four reasons why you should stop writing so many date functions and concatenations, and start looking them up in a table instead.

Doug Broke It: Microsoft Access Data Types [Video]

In this video, Doug talks about how changing a column’s data type in SQL Server can be dangerous when there are Access clients using it, and a reference table to help avoid type mismatches when planning data type changes.

Click here to see the list of SQL Server data types according to Access.

Introducing sp_BlitzRS®

Tune in here to watch our webcast video for this week! To join our weekly webcast for live Q&A, make sure to watch the video by 12:00 PM EST on Tuesday, September 23! Not only do we answer your questions, we also give away a prize at 12:25 PM EST – don’t miss it!

It’s hard to keep up with what your Report Server is doing, especially if your only tool is Report Manager. Now there’s an easier way, using the newest member of the sp_Blitz family: sp_BlitzRS! In this webcast, you’ll learn how sp_BlitzRS can help you stay informed of how well your SSRS installation is running, who gets what subscriptions, which reports would benefit from preprocessing, and more!

Have questions? Feel free to leave a comment so we can discuss it on Tuesday!

You can download sp_BlitzRS here as part of our download pack.

Doug Broke It: Reporting Services Encryption Keys [Video]

In this 5-minute video, Doug tells about the time he deleted the Reporting Services encryption key, and what it took to fix the damage. You’ll learn about what the SSRS encryption key protects, as well when to:

  • Back up the encryption key
  • Restore the encryption key
  • Change the encryption key
  • Delete the encryption key

Doug Broke It: Minimal Logging [Video]

In this 5-minute video, Doug explains how he kept filling SQL Server’s transaction log during ETL development, and how minimal logging could have prevented a lot of the log writes. You’ll learn about:

  • What minimal logging is
  • How to turn minimal logging on
  • How to tell if minimal logging is working
  • Using minimal logging for schema changes
  • Words of caution about minimal logging

The Day the SQL Server Quit [Video]

In this 5-minute video, Doug tells a story about a DBA whose SQL Server got fed up with being misconfigured and decided to do something about it. Topics include CPU settings like MAXDOP and Cost Threshold for Parallelism, memory and disk configuration.

Developer’s Guide to Understanding DBAs [Video]

You’re a developer, and you want to know:

  • What do DBAs value?
  • What do DBAs need from me?
  • What does my DBA want to help me with?
  • How big should that server be?
  • Is the DBA judging my code?
  • What happens if I get in trouble with my DBA?

Learn the answers in this 30-minute video:

Liked this? Register for our free upcoming webcasts, and check out our video archive of past webcasts.

Refactoring T-SQL with Windowing Functions

You’ve been querying comparative numbers like Year To Date and Same Period Last Year by using tedious CTEs and subqueries. Beginning with SQL Server 2012, getting these numbers is easier than ever! Join Doug for a 30-minute T-SQL tune-up using window functions that will cut down dramatically on the amount of code you need to write.

Looking for the scripts? Grab them below the video!

Script 1: Create Windowing View

--******************
-- (C) 2014, Brent Ozar Unlimited (TM)
-- See http://BrentOzar.com/go/eula for the End User Licensing Agreement.

--WARNING:
--This script suitable only for test purposes.
--Do not run on production servers.
--******************

/* Script 1: Create Windowing View
   Note: This script requires the AdventureWorks2012 database,
   which can be found here: https://msftdbprodsamples.codeplex.com/releases/view/55330
*/

USE AdventureWorks2012
GO

/* Duplicates the data with a DueDate field set to [year] - 1. */

CREATE VIEW vWindowing
AS
    ( SELECT    CustomerID ,
                CAST(SUM(TotalDue) AS DECIMAL(18,2)) AS TotalDue ,
                DATEPART(mm, DueDate) AS DueMonth ,
                CASE WHEN DATEPART(mm, DueDate) BETWEEN 1 AND 3 THEN 1
                     WHEN DATEPART(mm, DueDate) BETWEEN 4 AND 6 THEN 2
                     WHEN DATEPART(mm, DueDate) BETWEEN 7 AND 9 THEN 3
                     WHEN DATEPART(mm, DueDate) BETWEEN 10 AND 12 THEN 4
                     ELSE -1
                END AS DueQtr ,
                DATEPART(yy, DueDate) AS DueYear
      FROM      sales.SalesOrderHeader
      GROUP BY  CustomerID ,
                DATEPART(mm, DueDate) ,
                CASE WHEN DATEPART(mm, DueDate) BETWEEN 1 AND 3 THEN 1
                     WHEN DATEPART(mm, DueDate) BETWEEN 4 AND 6 THEN 2
                     WHEN DATEPART(mm, DueDate) BETWEEN 7 AND 9 THEN 3
                     WHEN DATEPART(mm, DueDate) BETWEEN 10 AND 12 THEN 4
                     ELSE -1
                END ,
                DATEPART(yy, DueDate)
      UNION
      SELECT    CustomerID ,
                CAST(SUM(TotalDue) AS DECIMAL(18,2)) AS TotalDue ,
                DATEPART(mm, DueDate) AS DueMonth ,
                CASE WHEN DATEPART(mm, DueDate) BETWEEN 1 AND 3 THEN 1
                     WHEN DATEPART(mm, DueDate) BETWEEN 4 AND 6 THEN 2
                     WHEN DATEPART(mm, DueDate) BETWEEN 7 AND 9 THEN 3
                     WHEN DATEPART(mm, DueDate) BETWEEN 10 AND 12 THEN 4
                     ELSE -1
                END AS DueQtr ,
                DATEPART(yy, DueDate) - 1 AS DueYear
      FROM      sales.SalesOrderHeader
      GROUP BY  CustomerID ,
                DATEPART(mm, DueDate) ,
                CASE WHEN DATEPART(mm, DueDate) BETWEEN 1 AND 3 THEN 1
                     WHEN DATEPART(mm, DueDate) BETWEEN 4 AND 6 THEN 2
                     WHEN DATEPART(mm, DueDate) BETWEEN 7 AND 9 THEN 3
                     WHEN DATEPART(mm, DueDate) BETWEEN 10 AND 12 THEN 4
                     ELSE -1
                END ,
                DATEPART(yy, DueDate) - 1
    )

	GO

Script 2: The Old Way of Querying

--******************
-- (C) 2014, Brent Ozar Unlimited (TM)
-- See http://BrentOzar.com/go/eula for the End User Licensing Agreement.

--WARNING:
--This script suitable only for test purposes.
--Do not run on production servers.
--******************

/* Script 2: The Old Way of Querying
   Note: This script requires the AdventureWorks2012 database,
   which can be found here: https://msftdbprodsamples.codeplex.com/releases/view/55330
*/

USE AdventureWorks2012
go

SET STATISTICS IO ON
GO

/* --------------------------------------- */
-- The old way to blend YTD and detail.
-- Pretty simple so far with just one join.
/* --------------------------------------- */

SELECT vdet.customerID, vdet.DueMonth, vdet.DueQtr, vdet.DueYear, vdet.TotalDue
, SUM(vsum.TotalDue) AS YTDTotalDue
FROM dbo.vWindowing AS vdet
JOIN dbo.vWindowing AS vsum ON vsum.CustomerID = vdet.CustomerID AND vsum.DueYear = vdet.DueYear AND vsum.DueMonth <= vdet.DueMonth
WHERE vdet.CustomerID = 11091
GROUP BY vdet.CustomerID, vdet.DueMonth, vdet.DueQtr, vdet.DueYear, vdet.TotalDue
ORDER BY vdet.DueYear, vdet.DueQtr, vdet.DueMonth

/* --------------------------------------- */
-- That's great, but what if you need other metrics?
-- How do we put QTD and YTD in the same T-SQL statement?
/* --------------------------------------- */

SELECT vdet.customerID, vdet.DueMonth, vdet.DueQtr, vdet.DueYear, vdet.TotalDue
, SUM(CASE WHEN vsum.DueQtr = vdet.DueQtr AND vsum.DueMonth <= vdet.DueMonth THEN vsum.TotalDue ELSE 0 END) AS QTDTotalDue
, SUM(vsum.TotalDue) AS YTDTotalDue
FROM dbo.vWindowing AS vdet
LEFT JOIN dbo.vWindowing AS vsum ON vsum.CustomerID = vdet.CustomerID AND vsum.DueYear = vdet.DueYear
																		AND vsum.DueMonth <= vdet.DueMonth
WHERE vdet.CustomerID = 11091
GROUP BY vdet.CustomerID, vdet.DueYear, vdet.DueQtr, vdet.DueMonth, vdet.TotalDue
;

/* --------------------------------------- */
-- Looks good. What about same YTD last year?
/* --------------------------------------- */

SELECT vdet.customerID, vdet.DueMonth, vdet.DueQtr, vdet.DueYear, vdet.TotalDue
, SUM(CASE WHEN vsum.DueQtr = vdet.DueQtr AND vsum.DueMonth <= vdet.DueMonth THEN vsum.TotalDue ELSE 0 END) AS QTDTotalDue
, SUM(vsum.TotalDue) AS YTDTotalDue
, SUM(vsum2.TotalDue) AS LastYTDTotalDue
FROM dbo.vWindowing AS vdet
LEFT JOIN dbo.vWindowing AS vsum ON vsum.CustomerID = vdet.CustomerID AND vsum.DueYear = vdet.DueYear
																		AND vsum.DueMonth <= vdet.DueMonth
LEFT JOIN dbo.vWindowing AS vsum2 ON vsum2.CustomerID = vdet.CustomerID AND vsum2.DueYear = vdet.DueYear - 1
																		AND vsum2.DueMonth <= vdet.DueMonth
WHERE vdet.CustomerID = 11091
GROUP BY vdet.CustomerID, vdet.DueYear, vdet.DueQtr, vdet.DueMonth, vdet.TotalDue
;

/* --------------------------------------- */
-- And now we have problems. What WILL give us
-- the Last YTD number we want?
/* --------------------------------------- */
;
WITH va AS
	(SELECT vdet.customerID, vdet.DueMonth, vdet.DueQtr, vdet.DueYear, vdet.TotalDue
	, SUM(CASE WHEN vsum.DueQtr = vdet.DueQtr AND vsum.DueMonth <= vdet.DueMonth THEN vsum.TotalDue ELSE 0 END) AS QTDTotalDue
	, SUM(vsum.TotalDue) AS YTDTotalDue
	FROM dbo.vWindowing AS vdet
	LEFT JOIN dbo.vWindowing AS vsum ON vsum.CustomerID = vdet.CustomerID AND vsum.DueYear = vdet.DueYear AND vsum.DueMonth <= vdet.DueMonth
	WHERE vdet.CustomerID = 11091
	GROUP BY vdet.CustomerID, vdet.DueYear, vdet.DueQtr, vdet.DueMonth, vdet.TotalDue
	)

SELECT vdet.customerID, vdet.DueMonth, vdet.DueQtr, vdet.DueYear, vdet.TotalDue
	, vdet.QTDTotalDue
	, vdet.YTDTotalDue
	, vsum.QTDTotalDue AS SameQTDLastYearTotalDue
	, vsum.YTDTotalDue AS SameYTDLastYearTotalDue
FROM va AS vdet
LEFT JOIN va as vsum ON vsum.CustomerID = vdet.CustomerID AND vsum.DueMonth = vdet.DueMonth AND vsum.DueYear = vdet.DueYear - 1

/* --------------------------------------- */
-- That's great but now we're four joins deep.
-- How much deeper dare we go? Roll 2d8 to find out.

-- Roll		Joins
-- -----	-----
-- 2-6		6
-- 7-10		8
-- 11-15	10
-- 16		Save vs. Traps or suffer 1d6 + 2 damage.

-- And how's the execution plan looking?
/* --------------------------------------- */

/* --------------------------------------- */
-- Let's add the TotalDue for everyone over that same period
-- ...if we dare.
/* --------------------------------------- */
;
WITH va AS
	(SELECT vdet.customerID, vdet.DueMonth, vdet.DueQtr, vdet.DueYear, vdet.TotalDue
	, SUM(CASE WHEN vsum.DueQtr = vdet.DueQtr AND vsum.DueMonth <= vdet.DueMonth THEN vsum.TotalDue ELSE 0 END) AS QTDTotalDue
	, SUM(vsum.TotalDue) AS YTDTotalDue
	FROM dbo.vWindowing AS vdet
	LEFT JOIN dbo.vWindowing AS vsum ON vsum.CustomerID = vdet.CustomerID AND vsum.DueYear = vdet.DueYear AND vsum.DueMonth <= vdet.DueMonth
	WHERE vdet.CustomerID = 11091
	GROUP BY vdet.CustomerID, vdet.DueYear, vdet.DueQtr, vdet.DueMonth, vdet.TotalDue
	)
	, vAll as
	(SELECT vdet.DueMonth, vdet.DueQtr, vdet.DueYear, vdet.TotalDue
	, SUM(CASE WHEN vsum.DueQtr = vdet.DueQtr AND vsum.DueMonth <= vdet.DueMonth THEN vsum.TotalDue ELSE 0 END) AS QTDTotalDue
	, SUM(vsum.TotalDue) AS YTDTotalDue
	FROM dbo.vWindowing AS vdet
	LEFT JOIN dbo.vWindowing AS vsum ON vsum.CustomerID = vdet.CustomerID AND vsum.DueYear = vdet.DueYear AND vsum.DueMonth <= vdet.DueMonth
	GROUP BY vdet.DueYear, vdet.DueQtr, vdet.DueMonth, vdet.TotalDue
	)

	SELECT * FROM vAll
	-- ...and so on...

/* --------------------------------------- */
-- I'll just stop here and say
-- it's a whole lotta work.
/* --------------------------------------- */

Script 3: The New Way of Querying

--******************
-- (C) 2014, Brent Ozar Unlimited (TM)
-- See http://BrentOzar.com/go/eula for the End User Licensing Agreement.

--WARNING:
--This script suitable only for test purposes.
--Do not run on production servers.
--******************

/* Script 3: The New Way of Querying
   Note: This script requires the AdventureWorks2012 database,
   which can be found here: https://msftdbprodsamples.codeplex.com/releases/view/55330
*/

USE AdventureWorks2012
GO

SET STATISTICS IO ON
GO

/* --------------------------------------- */
-- Remember the old way got us roughly one
-- table join per metric?
--
-- Hold on to your seat.
/* --------------------------------------- */

SELECT customerID, DueMonth, DueQtr, DueYear, TotalDue
FROM dbo.vWindowing
WHERE CustomerID = 11091
ORDER BY DueYear, DueMonth

/* We'll start slow by just adding QTD */

SELECT customerID, DueMonth, DueQtr, DueYear, TotalDue
, SUM(TotalDue) OVER (PARTITION BY CustomerID, DueYear, DueQtr
						ORDER BY DueMonth
						ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS QTDTotalDue
FROM dbo.vWindowing
WHERE CustomerID = 11091
ORDER BY DueYear, DueMonth

/* + YTD */

SELECT customerID, DueMonth, DueQtr, DueYear, TotalDue
, SUM(TotalDue) OVER (PARTITION BY CustomerID, DueYear, DueQtr ORDER BY DueMonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS QTDTotalDue
, SUM(TotalDue) OVER (PARTITION BY CustomerID, DueYear ORDER BY DueMonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS YTDTotalDue
FROM dbo.vWindowing
WHERE CustomerID = 11091
ORDER BY DueYear, DueMonth

/* What are we waiting for -- let's really see what this can do! */

SELECT customerID, DueMonth, DueQtr, DueYear, TotalDue
, SUM(TotalDue) OVER (PARTITION BY CustomerID, DueYear, DueQtr ORDER BY DueMonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS QTDTotalDue
, SUM(TotalDue) OVER (PARTITION BY CustomerID, DueYear ORDER BY DueMonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS YTDTotalDue
, SUM(TotalDue) OVER (PARTITION BY CustomerID) AS AllTimeTotalDue
, SUM(TotalDue) OVER (PARTITION BY CustomerID ORDER BY DueYear, DueMonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotalTotalDue
, SUM(TotalDue) OVER (PARTITION BY CustomerID ORDER BY DueYear, DueMonth ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS RemainingFutureTotalDue
, SUM(TotalDue) OVER (PARTITION BY CustomerID ORDER BY DueYear, DueMonth ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)
	/SUM(TotalDue) OVER (PARTITION BY CustomerID) AS PctRemainingFutureTotalDue
, SUM(TotalDue) OVER (PARTITION BY CustomerID, DueYear, DueQtr ORDER BY DueMonth) AS ThisQtrTotalDue
, SUM(TotalDue) OVER (PARTITION BY CustomerID, DueYear ORDER BY DueMonth) AS ThisYearTotalDue
, SUM(TotalDue) OVER (PARTITION BY CustomerID ORDER BY DueYear, DueMonth ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS Rolling12TotalDue
, TotalDue/SUM(TotalDue) OVER (PARTITION BY CustomerID, DueYear, DueQtr) AS PctOfThisQtrTotalDue
, TotalDue/SUM(TotalDue) OVER (PARTITION BY CustomerID, DueYear) AS PctOfThisYearTotalDue
, LAG(TotalDue, 1) OVER (PARTITION BY CustomerID ORDER BY DueYear, DueMonth) AS PrevMonthTotalDue
, LAG(TotalDue, 3) OVER (PARTITION BY CustomerID ORDER BY DueYear, DueMonth) AS SameMonthPrevQtrTotalDue
, LAG(TotalDue, 12) OVER (PARTITION BY CustomerID ORDER BY DueYear, DueMonth) AS SameMonthPrevYearTotalDue
, AVG(TotalDue) OVER (PARTITION BY CustomerID, DueYear ORDER BY DueMonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS YTDAvgTotalDue

/* ------------------ */
-- If I want to see how this customer stacks up to others,
-- I can remove the where clause, add these columns, and filter at the client

--, AVG(TotalDue) OVER (PARTITION BY DueYear ORDER BY DueMonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS YTDAvgTotalDueAllCustomers
--,	AVG(TotalDue) OVER (PARTITION BY CustomerID, DueYear ORDER BY DueMonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
--		/ AVG(TotalDue) OVER (PARTITION BY DueYear ORDER BY DueMonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS PctVarYTDAvgTotalDueVsAllCustomers
/* ------------------ */

FROM dbo.vWindowing AS va
WHERE CustomerID = 11091
ORDER BY CustomerID, DueYear, DueMonth

Alaska has a SQL Server User Group!

Alaska is on the PASS map!

Alaska is on the PASS map!

I’m really stoked to share the news: Alaska (my home state) finally has a PASS Chapter of its own! The group just got started last December, and officially welcomed into the PASS organization at the end of January. While they don’t have a Facebook or Twitter account yet, they do have a website and a YouTube channel.

The group meets every month and draws about twenty attendees, according to Chapter Leader Greg Burns. (Greg also runs the local SharePoint user group, which has about eighty members.) The audience is a mix of DBAs and developers, mostly.

Curious. Why would I mention the audience?

Because Greg is running a PASS Chapter for the first time, he could use a lot of help. He’s looking for speakers — remote or in-person — to present at upcoming meetings. If you’re interested in presenting to the group remotely, or just looking for an excuse to visit by far the largest state in the union…[prolonged eye contact with Texas]…just drop Greg a line at AlaskaSQL(at)gmail.com.

But wait, there’s more! If you’re a current or former PASS Chapter leader, you probably have some great tips on how to structure meetings, build membership, advertise your group, line up sponsors, and other things it takes to grow a user group. Rather than flood Greg’s inbox with your collective wisdom, let’s assemble them here in the comments so they’re all in one place. I can think of no better way to welcome Alaska to the SQL Server community than to show them how much we help each other.

css.php