Category: T-SQL

T-SQL & Development

#TSQL2sday: System-Maintained LastUpdatedDate, LastUpdatedBy Columns

January 10, 2017

For this month's T-SQL Tuesday, we're talking about interesting Connect requests to fix or change SQL Server behavior.

In our Senior DBA Class, one of the exercises involves figuring out changes to a table after an Always On Availability Group failover. In every class, several students always say the same thing: "I really wish I had a LastUpdatedDate column on all of my tables that just updated automatically."

Read more about #TSQL2sday: System-Maintained LastUpdatedDate, LastUpdatedBy Columns 13 comments — Join the discussion
T-SQL & Development

What TRY/CATCH Doesn’t Handle

May 17, 2016

We were once asked in class what TRY/CATCH doesn't handle besides object existence errors.

It's well documented in Books Online (BOL). If you're like me, then tl;dr. Are we even calling it Books Online these days? I still say "bookmark lookup" instead of "key lookup". I suppose I'll be saying Books Online for quite some time too. At least these days it really is online.

Read more about What TRY/CATCH Doesn’t Handle 25 comments — Join the discussion
T-SQL & Development

Don’t Use Scalar User-Defined Functions in Computed Columns.

January 26, 2016

Scalar functions in computed columns cause all queries that hit that table to execute serially. But it gets worse!
Scalar functions in computed columns
cause index maintenance to go single-threaded.
If you're running Expensive Edition, index rebuilds can be both online and parallel. That's pretty cool, because it keeps all your gadgets and gizmos mostly available during the whole operation, and the parallel bit usually makes things faster.

Read more about Don’t Use Scalar User-Defined Functions in Computed Columns. 27 comments — Join the discussion
T-SQL & Development

Give Your T-SQL a Semicolonoscopy

December 17, 2015

In theory, all of your T-SQL statements are supposed to end with a semicolon, like this: [crayon-6a79cc6e339af131617410/] Why? Well, SQL Server 2005's Books Online says: Many code examples use a semicolon (;) as a Transact-SQL statement terminator. Although the semicolon is not required, using it is considered a good practice. You know, like flossing your teeth,…

Read more about Give Your T-SQL a Semicolonoscopy 39 comments — Join the discussion
T-SQL & Development

Window Functions and Cruel Defaults

September 29, 2015

My First Post Here...
Well, my first technical post, was about how the default index creation method is OFFLINE. If you want that sweet, sweet Enterpri$e Edition ONLINE goodness, you need to specify it. It's been a while since that one; almost six months to the day. So here's another one!
But Window Functions Are Awesome
Heck yeah they are. And how. Boy howdy. Etc. You get the point. I'm enthusiastic. What can be cruel about them? Glad you asked!

Read more about Window Functions and Cruel Defaults 5 comments — Join the discussion
T-SQL & Development

The Top 3 Mistakes T-SQL Developers Make

March 3, 2015

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. Every answer I got was unique, which was both telling (so…

Read more about The Top 3 Mistakes T-SQL Developers Make 31 comments — Join the discussion
Performance Tuning

Are SQL Server Functions Dragging Your Query Down?

October 14, 2014

In most coding languages, functions are often-used blocks of code that can be reused from multiple locations, leading to less code – and cleaner code. SQL Server also lets us create functions that can be used the same way. They are reusable blocks of code that can be called from multiple locations. So, if you need to format phone numbers a certain way, or parse for specific characters, you can do so using a function.

Read more about Are SQL Server Functions Dragging Your Query Down? 20 comments — Join the discussion
T-SQL & Development

Refactoring T-SQL with Windowing Functions

March 31, 2014

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.

Read more about Refactoring T-SQL with Windowing Functions 6 comments — Join the discussion
T-SQL & Development

How to Cache Stored Procedure Results

December 26, 2013

Say you run an online store, and on each item's page, you need to show related items that people purchased. Let's take an Amazon page for my favorite rechargeable AA batteries:

In a perfect world, we would cache this data in the web/app tier - but back here in the real world, sometimes our developers build stored procedures to fetch this kind of data, and the stored procedure ends up getting called way too often.

Read more about How to Cache Stored Procedure Results 23 comments — Join the discussion
T-SQL & Development

Windowing Function Examples for SQL Server

July 14, 2011

Aggregations and grouping can be a pain in the rear for the novice SQL developer. Way back in 2003, the ANSI/ISO standards people figured this out and added windows and ranking functions to the standard. In 2005, Microsoft added a few of these functions (ROW_NUMBER(), RANK(), DENSE_RANK(), and NTILE()) as well as the OVER() clause…

Read more about Windowing Function Examples for SQL Server 13 comments — Join the discussion