Press "Enter" to skip to content

Category: T-SQL Tuesday

Preconceived Notions with Filtered Indexes

Aaron Bertrand has learned a thing or two about filtered indexes:

Confession time. For filtered indexes, I have long held the following impressions:

1. That the definition of the filter had to exactly match the predicate in the query.

2. That col IN (1, 2) was not supported in a filter definition, since it’s the same as col = 1 OR col = 2, which is definitely not supported.

If I were to take a wild guess, I’d think impression 1 was probably influenced by the extreme limitations filtered indexes have with parameterized queries. Anyhow, read the whole thing and learn why both of these are wrong.

Leave a Comment

Preconceived Notions on Rules

Richard Swinbank is a rule-breaker:

When I started out writing T-SQL, I learned rules – things I should do, things to avoid – from other, more experienced developers. For example, when writing stored procedures:

– don’t use dynamic SQL

– don’t set variables wih SELECT

– never use WITH RECOMPILE

There are often great reasons for these. Dynamic SQL can leave you prone to SQL injectionSELECTing into variables can have unexpected effects, and RECOMPILE can bring an OLTP system to its knees.

I think most rules of this sort tend to fit into something like four categories: convenience, take caution, observe in the breech, or shibboleths. Some rules, like formatting rules, are for convenience, whether that be for yourself or others. Not using dynamic SQL is a cautionary rule, for the reasons Richard mentions. The final two categories are variants on rules which exist to show that you are one of us, with observation in the breech being rules that we say we follow but only do when others are looking, and shibboleths being rules we follow regardless of whether people are looking. Formatting might fit into this as well, or having rules prohibiting (or enforcing) use of subqueries in the SELECT clause.

Very rarely are prohibitional rules 100% applicable, though as Richard notes, rules are a very useful shorthand.

Leave a Comment

Preconceived Notions of Business Logic in the Database

Mala Mahadevan discusses business logic:

There are many things on which I’ve changed my opinion in my journey as a data professional. For this blog post, I picked a common one – where business logic should reside. During my years as a production DBA – there was (and probably is) a hard-held belief that databases are meant for CRUD operations only and business logic belongs in the application/middle tier. This belief has its place – DBAs don’t like debugging code that is application-specific, or be tasked with why data looks a certain way or what code caused it. Business logic can also be very complex and get deeply embedded in one place (the database). I have believed in this and fought for this at most places I’ve worked as a DBA.

Over time though, my opinion has changed. 

Building on top of Mala’s point, people often conflate “business logic” with “data logic.” Rules governing the shape of data are as much data logic rules as they are business logic. The simplest cases include things like nullability, data sizes and lengths, and how data elements interact. It can also include ensuring the non-existence of invalid states through constraints. There can be practical reasons why we wouldn’t implement some piece of data logic in the database itself—for example, perhaps the particular data logic rule would be prohibitively expensive or becomes too complex to maintain as triggers—but in general, I’d estimate that people and firms under-specify their data logic in the database rather than over-specifying business logic.

One last bit that I’d hit on is that it’s okay to have overlapping logic in both, especially when it comes to data validation. We shouldn’t blithely accept inputs from client-side scripts—we evaluate it server-side and ensure that nobody has fiddled with the form and tried to create an invalid state. Then, when writing to the database, it can still make sense to check again for the exact same reason.

Leave a Comment

Preconceived Notions around Temp Tables

Two overlapping posts on temp tables? Order up! First, Deb Melkin realizes tempdb is like a hotel room:

I was always taught to clean up after myself so I thought dropping my local temp tables after creating them in a proc did that. I knew that once the session was dropped, the table no longer was there so SQL Server would eventually take care of it. But then you had the cases where you ran the same stored proc multiple times in the same session so you wanted to make sure the table was gone at the end of your procedure to prevent issues.

Second, Deepthi Goguri learns the importance of getting temp tables right the first time:

I always had an opinion of changing the schema of the temp tables or adding indexes later after the temp table creation doesn’t affect in any way but it’s actually not a good idea. Yes, you heard that right. Altering or adding indexes later after temp table creation within the stored procedures will be marked for deletion and so the cached temp table metadata cannot be reused. It is suggestable to create the index as you create the temp table using the inline index syntax.

Both reference Pam Lahoud’s tempdb talk at EightKB (video), so check that out too.

Leave a Comment

Preconceived Notions: “Databases Are Easy”

Rob Farley takes us back to school:

At university I studied Computer Science, which felt like it was mostly about algorithms and paradigms. It covered how to approach particular kinds of problems, what languages suited what problems and why, and how to model things. The answer to a lot of things was “C’, whether it was a multiple choice question, or the question about which language would be used to solve something.

I skipped the database subject. Everyone said it was overly basic, easy marks, and not particularly interesting. I wasn’t interested in it. Not when there were subjects like Machine Learning where we’d implement genetic algorithms in LISP to find ways to battle other algorithms in solving the prisoner’s dilemma. Or the subject where we’d create creatures (in software) that would follow each other in a flocking motion around a cityscape. Everything I heard about databases was that they were largely of no importance.

In fairness, university database classes tend to fall into one of two categories: either mathematical forays into set theory or fluffy, school-of-business-friendly “Today we’re going to learn how to write the word SELECT. Next time, we’ll learn how to write the word FROM” types of courses, at least from what I’ve experienced.

Leave a Comment

T-SQL Tuesday 144 Roundup

Victoria Holt recaps T-SQL Tuesday #144:

This month’s T-SQL Tuesday attracted some great responses! Thank you to everyone who participated!

My invitation for this month’s #tsql2sday was 3 fold on sharing your experiences on data governance

– The current cost of data governance versus its benefits

– The amazing things data governance has enabled you to achieve or will enable you to achieve in the future

– The potential uses for Azure Purview within your estates and the automated deployment options for that

Read on for the recap.

Comments closed

The Importance of Data Governance

Rob Farley riffs on another T-SQL Tuesday topic:

But the checks that we do are more about things that the database can allow, but are business scenarios that should never happen.

Plenty of businesses seem to recognise these scenarios all too well, and can point them out when they come across them. You hear phrases like “Oh, we know that’s not right, it should be XYZ instead”. And they become reasons why they don’t really trust their data. It’s a data quality issue, and every time someone comes across a data quality issue, they trust the data a little less.

Click through for Rob’s thoughts.

Comments closed

T-SQL Tuesday 143 Round-Up

John McCormack summarizes T-SQL Tuesday #143:

What an honour it was to host T-SQL Tuesday this month and I received some really great submissions. This wrap up post aims to give a quick insight into each of them in the hope that more members of the SQL Family can find some time to click on them and learn more. I counted 22 posts including my own which was a great response. If you missed the original invite, you can find the link below.

Click through for all of the responses.

Comments closed

Moving Files Associated with Availability Groups

Eitan Blumin has a doozy of a short script:

Today, I’m sharing with you a cool Powershell script that basically implements the methodology necessary to move database files to a new location in AlwaysOn Availability Groups, without breaking HADR.

It’s based on a few very useful step-by-step guides on the topic such as this one and this one and this one. But it takes it a step further by being a single cohesive Powershell script that does everything end-to-end.

Well… Almost everything… The only thing it’s missing is somehow disabling any SQL Agent jobs that may be performing backups. I still haven’t figured out how to possibly automate such a thing, so you’d have to do that manually on your own.

Click through for instructions, notes, and warnings, as well as the script itself.

Comments closed