Press "Enter" to skip to content

Category: T-SQL Tuesday

Sources of Data Structure Truth

Published 2022-07-13 by Kevin Feasel

Deb Melkin performs database epistomology:

The “source of truth” is my newly made up phrase for whatever you are using to say this is my database schema and initial data needed to start up the application. This can be your script directory; this can be a dacpac or bacpac; this can be your data model; this can be a combination of these things. My go-to “source of truth” right now is my source control repository. I’ve got both the schema and the default data needed in the same location. In the past, I would have probably included the data model as way to help me make sure whatever database table changes I have in my source control are there, especially for that one database which only had tables and views. (A different rant for a different time.) Whatever you use, it absolutely CANNOT be an actual database. There are two main reasons for this:

Read on for those reasons.

Comments closed

SQL Login of the Week Club

Published 2022-07-13 by Kevin Feasel

Mala Mahadevan recounts a horror story:

Its been a few years since I left consulting. But the last gig I was at – we encountered something like this. We had a big client who had outsourced all their database development and manual update work (no not to us, to some third-party contracting company). These were contractors paid by the hour, and the turnover was really high. Our client did not want to issue windows based authenticated logins to these people for some reason (do not recall what). So every week, when the week started, the contractor working on a particular server would get a SQL Server authenticated login they could use. This was valid just for that week and would expire the next week. And, every weekend , it was our job, as the remote DBA company, to set up those logins.

But wait, it gets worse.

Comments closed

The Risks of “Unused” Databases

Published 2022-07-13 by Kevin Feasel

Chad Callihan reminds us about things in use:

You may have been updating data someone needed for validating a fix. The “_OLD” table that you assumed could be dropped may still have been useful to the person who created it. There might be a database covered in cobwebs that should have been dropped years ago but it could also be a database that’s used for some type of reporting every few months.

Yeah, that’s a pretty common problem. A couple of things which help mitigate this issue:

  • Check wherever you can to see if the database (or database object) is in use: cached plans, stored procedure calls, application calling code, SQL Agent jobs, SSIS packages, etc.
  • Take (and test!) backups of databases before you drop tables or get rid of them.
  • Keep those database backups around for quite a while.
  • Take databases offline for a while before dropping them. That way, if somebody really does use it on occasion, it’s easy to bring back online rather than needing to restore from a backup.

At the end of the day, however, you shouldn’t be afraid to drop things. Do the appropriate amount of diligence and make it a controlled demolition.

Comments closed

Test Those Backups

Published 2022-07-12 by Kevin Feasel

Kevin Chant comes at us with important advice:

In reality, not being able to restore a database can damage your company’s reputation and you can end up losing colleagues for various reasons. For example, if they ask for restores to be tested and it never happens.

To save your colleagues from a database restore nightmare you must test restoring your database backups at some level. Otherwise, it can lead to serious issues.

As the saying goes, you don’t have a backup until you’ve successfully restored it.

Comments closed

Application Names and Database Queries

Published 2022-07-12 by Kevin Feasel

Tom Zika does not like those missing application names:

Whenever I’m trying to debug a problem using sp_whoisactive or Extended Events (XE) and I see either Core Microsoft SqlClient Data Provider or .Net SqlClient Data Provider, my blood begins to boil.

It means I’ll probably spend hours asking around to try and find the owner. Sometimes knowing the host_name helps, but there can be a multi-purpose host that runs many applications – which one is having the problem?

How do you set the name? Read the post to find out.

Comments closed

Database Project Versioning and Identification

Published 2022-07-12 by Kevin Feasel

Eitan Blumin answers an important question:

“What is SSDT“, you ask? Oh, you didn’t? Well, let me tell you anyway! SSDT is the go-to solution from Microsoft for versioning SQL Server databases and performing state-based deployments (and it’s free!). It has many useful capabilities for developing and publishing changes from your SQL Database project to your SQL Database in production (or wherever).

One of the things that are not so clear about SSDT specifically and database versioning in general, is how should one identify which “version” of your database project was last deployed to your server?

Eitan includes several ways of tracking and controlling database versions.

Comments closed

Against Tibbling

Published 2022-06-16 by Kevin Feasel

Hugo Kornelis hates tibbling:

Probably the one I hate most. And one that is stubbornly persistent. Object name prefixing.

Or, to be more precise, the standard that enforces that all table names need to start with a prefix that designates them as a table, and all view names with a different prefix to clearly mark them as a view. Typically tbl_ and vw_ are used, though I have also seen just the letters t and v, and I have seen them with or without underscores.

I hate this coding standard (or rather, naming standard) with a vengeance. For a few reasons. The perceived benefit is in fact not a benefit at all. It is detrimental to a quick understanding of what I see on the screen. But my biggest objection is that it negates one of the greatest benefits of views.

Read on to understand why this is a bad idea. I completely agree with Hugo on this.

Comments closed

Coding Standards Writ Large

Published 2022-06-16 by Kevin Feasel

Kenneth Fisher points out the downside of coding standards:

I’m betting you can start to see the problem right? Joe is supporting application A. His team wrote it, and they wrote it using a strict set of coding standards. Jane supports application C. Her team didn’t write it, it was transferred over during the reorg last March. Her team has a set of coding standards they enforce, unfortunately, application C wasn’t written with them. All of their new code is however because their manager wants strict enforcement of their coding standards. Oh, and Joe and Jane are both being moved to be part of a new team next week. They’ll be supporting some older code, that, you guessed it, is using a whole different set of coding standards, if any.

Starting from the Coasean notion of the firm as a means of internalizing externalities and achieving economies of scale + scope, this is just about where you hit the margin for added productivity… Rephrasing this not to be in economist jibber-jabber, this kind of thing is a big part of why really large companies essentially spin off mini-companies and act nearly-independently under the parent company’s umbrella. It’s essentially impossible to create and enforce a meaningful set of standards once you hit a certain threshold of developers, especially when it comes to the more opinion-heavy standards.

Comments closed

Pull Request Standards

Published 2022-06-15 by Kevin Feasel

Deb Melkin stands in front of the gate:

When I think of what my coding standards are, I tend to think of it as how would I review scripts for a pull request (PR). I think my past coworkers can attest that I can get quite picky when I look at code. So if I were your reviewer, what sort of things would I comment on?

Click through for a checklist.

Comments closed