Category: T-SQL

Erik Darling wants to figure out the acceptable boundaries for NOLOCK:

Picture a couple developers who started their app in the cloud, where they can’t get fancy with tempdb, fast disks aren’t in the budget yet, along with that beefier server with some extra RAM. They may not be able to turn on RCSI or SI at the drop of a hat; tempdb would keel over with the row versioning as part of a workload that already uses it pretty heavily.

They still need to run reports, either for users, or for higher ups at the company, and they can ask for them at any time. Caching the data when user activity is low and reporting against it when someone asks may raise some questions, like “why doesn’t my sales data show anything from today?”, or worse. You could invalidate the cache every X minutes, but that doesn’t help because then you need to re-run that reporting query every X minutes. That’s only moderately better than letting users query it at will.

Figuring out when read uncommitted is acceptable is a business decision.  As much as I dislike using NOLOCK, as long as the people on the business side understand the risks, it’s their call.

Slava Murygin looks at some new functions in the vNext CTP 1.1:

Since Microsoft introduced XML support in SQL Server, the most common string concatenation technique was use of “XML PATH(”)” like this:

SELECT SUBSTRING(
(SELECT ‘, ‘ + name FROM master.sys.tables
FOR XML PATH(”))
,3,8000);
GO

Now you can aggregate your strings by using function “STRING_AGG”:

SELECT STRING_AGG(name, ‘, ‘) FROM master.sys.tables;

Read on for the other three.  This aggregation function, however, would make some of my code a lot simpler and easier to explain to junior database developers.  I just want it to perform well is all.

Denny Cherry recommends against nested views:

Now there are plenty of reasons to use views in applications, however views shouldn’t be the default way of building applications because they do have this potential problems.

While working with a client the other week we had to unwind some massive nest views. Several of these views were nested 5 and 6 levels deep with multiple views being referenced by each view. When queries would run they would take minutes to execute instead of the milliseconds that they should be running in. The problems that needed to be fixed were all indexed based, but because of the massive number of views that needed to be reviewed it took almost a day to tune the single query.

Nested views is usually an indicator of somebody trying to perform OOP on a relational database, taking advantage of encapsulation.  One big performance problem with nested views is that at some point, the query optimizer gives up trying to optimize and simply pulls in all of the tables as many times as they appear.  Make the optimizer’s life easier and it will make your life easier.

Andy Levy discusses the SET NOCOUNT operation:

So what happened here?  When you execute a query against SQL Server, both your data and some additional information is sent back to the client. This additional information is sent via a separate channel which is accessible via the SqlConnection.InfoMessages (or if you’re still using classic ADO, the InfoMessage) event. When you run queries in SSMS, you see this information in the Messages tab of the results pane most often as X row(s) affected.

That’s where my new stored procedures were causing problems. Where the original procedures were returning only one event which corresponded to the number of records returned by the single query in each procedure. But now that I’m loading temp tables, I’m getting multiple messages back – at a minimum, a count of the records affected when loading the temp table plus a count of the records returned to the calling application.

Data layers which can’t handle information streams are rare, but they do show up in the wild sometimes.