Posts by Kendra Little

Interviewing: How to Test TSQL Writing Skills

“Sure, I write great queries!” We sometimes help clients interview DBAs and Developers for their team. Requirements vary depending by position, but sometimes it’s important to test a candidate’s TSQL skills. While I can test candidates thoroughly on strategies for TSQL (when they’d use certain constructs, the pros and cons of various approaches), if what…
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

Filtered Indexes and IS NOT NULL

Filtered indexes can be tricky. Just getting your queries to use the filtered index can be a real pain. Parameterization may mean it’s “unsafe” to use the filtered index. The optimizer might want the filtered column in your key or include column list when it doesn’t seem like it should have to be there. The…
Read More

Replication Won’t Refresh Your Dev and Pre-Production Environments

It’s almost like the real thing, except it’s totally different. At first glance, SQL Server’s transactional replication seems like it’s useful for moving data around in all sorts of situations: it works in Standard Edition, it’s not synchronous, and you can have multiple subscribers. Why People Want Replication to Test and Pre-Production Environments Setting up…
Read More

The Nine Circles of Developer Hell

Commit message: Duck! Everyone’s code is terrible. But exactly how terrible is yours? First Circle: Code Limbo We can’t say this is good code, but for the most part nobody notices it. Second Circle: Code Lust Cool function, bro! Third Circle: Gluttonous Code There’s always one simple piece of logic that’s been written hundreds of…
Read More
Error 2571 - no permission to run DBCC TRACEON

What Permissions does QUERYTRACEON Need?

The QUERYTRACEON hint can be particularly useful for testing the new cardinality estimator in SQL Server 2014 on individual queries using Trace Flag 2312, if your database is still using compatibility level 110: Transact-SQL SELECT ClosedDate, Id FROM dbo.Posts WHERE LastActivityDate > '2013-09-05 11:57:38.690' OPTION (QUERYTRACEON 2312); GO 12345 SELECT ClosedDate, IdFROM dbo.PostsWHERE LastActivityDate > '2013-09-05…
Read More