sql-server-technical-posts

Last week, I presented on the topic of TSQL User Defined Functions (UDFs) in SQL Server at the PASS Summit.

I’ve been enjoying working with SQL Operations Studio lately - a new, free, cross-platform editor from Microsoft.  The tool is under active development and it’s a great time to try it out and suggest changes for how you’d like to see the product work. (Here’s a suggestion I made this week, for example.)

BREAK is a useful piece of control-of-flow language in SQL Server, but it only works in specific situations. Break Questions, Answers, and Explanations 1) If you run this code in a SQL Server Management Studio session, what will appear in the Messages window? BREAK; PRINT 2; Answer: Msg 135, Level 15, State 1, Line 6 Cannot use a BREAK statement outside the scope of a WHILE statement. How’d people do?

RETURN is a “control of flow” language keyword in Transact-SQL. It’s very useful for managing when code does – and does NOT – get executed in procedural code, and it can also be used to communicate the status from procedural code.

But not everyone knows how to use RETURN, and learning about it can be awkward because of some quirks of SQL Server Management Studio. It is well worth taking the time to get used to it, though!

User defined functions are fairly simple to create in SQL Server, but figuring out exactly how they are being used can take a little time - even in a simple execution plan. This is because the functions can be tucked away into lots of different operators in the plan, even join operators.

The good news: once you learn what to look for, it’s not terribly complicated. There are some fancy terms involved, but the definitions are pretty simple.

Forgetfulness can lead to learning something new. This is a bit of a nightmare when it happens in production, but a treat when it happens in an isolated test system– and that’s how I learned this.

I left a bit of blocking open on my test VM, and forgot about it.

It’s tough to keep track of which features work in each version of SQL Server, and which Editions support them.

My memory told me that the new Adaptive Joins feature in SQL Server 2017 was Enterprise Edition only…  and that’s correct, but I didn’t realize that the fancy new feature to make Multi-Statement TVFs smarter has much wider licensing.

The new Enterprise Automatic Tuning feature in SQL Server 2017 may sound intimidating at first – one question I get a lot lately is whether or not there’s a future for DBAs. Will Auto-Tune mean we don’t need any more human tuning?

Well, not anytime super soon.

It’s time to write a blog post, so I can save time when I go searching for this in a couple years!

Most of the time in SQL Server, the MAX() function and a TOP(1) ORDER BY DESC will behave very similarly.

If you give them a rowstore index leading on the column in question, they’re generally smart enough to go to the correct end of the index, and – BOOP! – just pluck out the data you need without doing a big scan.

Identifying that a query plan has been bossed around in Query Store can be a bit tricky, because it can appear in different ways.

I got a great question about transactions by email recently about transactions.

I got a question last week from a very smart fellow:

How can I tell when statistics were last updated for a heap?

Before I could email him back, I soon got another email in which he answered his own question.

I recently got a great question: if I order by a column where all rows in that column have the same value, will SQL Server then order the results by the clustered index key?

I’m very that my session on table partitioning from the 2017 SQL PASS Summit is being featured as one of the “Best of Summit” videos.

I had a terrific time presenting this session, thanks to everyone in the audience: you were awesome!

You can watch the video here, and follow the link above to see even more great videos from the conference for free.

I had a great time this week talking about isolation levels with Hekaton – aka In-Memory OLTP – aka Memory Optimized tables. Here’s a video of the recorded webcast.

One of the great things about writing presentations is that it spurs you to “clean up” your definitions. When it comes to writing a slide about something, I ask myself, “Do I really know what that is?” I check my assumptions, and clarify how I think about something.

This week I was working with SQL Server memory settings, and I “cleaned up” my understanding of the following definitions.

I received a question recently asking about disk access patterns for index seeks in SQL Server. The question suggested that index seeks would have a random read pattern.

Is this necessarily the case?

Last week, I had a great time giving a webcast for the terrific DBA Fundamentals Virtual Chapter. If you like free online training, check them out and sign up for future events.

One cool little feature in SQL Server 2016 is COMPRESS(). It’s a TSQL function available in all editions that shrinks down data using the GZIP algorithm (documentation).