Development

  1. Home
  2. Development
  3. (Page 19)

Breaking News: 2016 Query Store cleanup doesn’t work on Standard or Express Editions

If you’re using SQL Server 2016’s awesome new feature, Query Store, there’s a new bug with automatic cleanup. Books Online explains: Automatic data cleanup fails on editions other than Enterprise and Developer. Consequently, space used by the Query Store will grow over time until configured limit is reached, if data is not purged manually. If…
Read More
Tara Kizer

What TRY/CATCH Doesn’t Handle

SQL Server, T-SQL
25 Comments
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…
Read More

RAM and Sympathy

With the release date for 2016 finally announced Everyone can start gearing up to gaze upon its far shores from the 2008R2 instance they can’t or won’t upgrade for various reasons. I’m excited for a lot of the improvements and enhancements coming along, and generally hope I’m wrong about customer adoption. One annoyance with the…
Read More

Creating Tables and Stored Procedures in TempDB – Permanently

No, not #tables – actual tables. Here’s how: USE tempdb; GO /* This one is only available during my session: */ CREATE TABLE #myTempTable (ID INT IDENTITY(1,1), Stuffing VARCHAR(100)); GO /* This one is global, meaning it's available to other sessions: */ CREATE TABLE ##myTempTable (ID INT IDENTITY(1,1), Stuffing VARCHAR(100)); GO /* You can create…
Read More

Breaking News: Query Store in All Editions of SQL Server 2016

Bob Ward talking Query Store at SQL Intersection Onstage at SQL Intersections in Orlando this morning, Bob Ward announced that Query Store will be available in all editions of SQL Server 2016. This is awesome, because Query Store is a fantastic flight data recorder for your query execution plans. It’ll help you troubleshoot parameter sniffing…
Read More

Stats Week: Messin’ With Statistics

SQL Server, Statistics
7 Comments
If there’s one thing living in Texas has taught me It’s that people are very paranoid that you may Mess With it. Even in Austin, where the citizenry demand weirdness, they are vehemently opposed to any form of Messing, unless it results in mayonnaise-based dipping sauce. Me? I like Messing With stuff. Today we’re going…
Read More

Stats Week: Do Query Predicates Affect Histogram Step Creation?

Auto Create Statistics is your friend It’s not perfect, but 99% of the time I’d rather have imperfect statistics than no statistics. This question struck me as interesting, because the optimizer will totally sniff parameters to compile an initial plan. If you don’t have index statistics, or system statistics already on a column in a…
Read More

Give Your T-SQL a Semicolonoscopy

SQL Server, T-SQL
38 Comments
In theory, all of your T-SQL statements are supposed to end with a semicolon, like this: SELECT * FROM dbo.Posts; SELECT * FROM dbo.Comments; GO 123 SELECT * FROM dbo.Posts;SELECT * FROM dbo.Comments;GO Why? Well, SQL Server 2005’s Books Online says: Many code examples use a semicolon (;) as a Transact-SQL statement terminator. Although the semicolon…
Read More

The Five Stages of Dynamic SQL Grief

Development
18 Comments
Dynamic SQL can be an incredibly powerful tool in the pocket of a SQL Server developer. Developers frequently express amazement at the level of flexibility dynamic SQL offers, an astonishment at how quickly things get out of hand, and finally the humbling realization that such machinery nearly tore their limbs off. This process can be…
Read More

Does Creating an Indexed View Require Exclusive Locks on an Underlying Table?

An interesting question came up in our SQL Server Performance Tuning course in Chicago: when creating an indexed view, does it require an exclusive lock on the underlying table or tables? Let’s test it out with a simple indexed view run against a non-production environment. (AKA, a VM on my laptop running SQL Server 2014.)…
Read More

Window Functions and Cruel Defaults

SQL Server, T-SQL
5 Comments
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…
Read More
Menu