sql-server-technical-posts

The BREAK Keyword in Transact SQL

The BREAK Keyword in Transact SQL

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?
Using RETURN in Transact SQL: Quiz Results

Using RETURN in Transact SQL: Quiz Results

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!

Find the Scalar Function Call: What it Means If It's Hiding in Probe Residual

Find the Scalar Function Call: What it Means If It's Hiding in Probe Residual

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.

Adaptive Queries in Standard Edition: Interleaved Exec for Multi-Statement TVFs

Adaptive Queries in Standard Edition: Interleaved Exec for Multi-Statement TVFs

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.

Locked Pages, Working Set, and Private Bytes

Locked Pages, Working Set, and Private Bytes

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.