Press "Enter" to skip to content

Author: Kevin Feasel

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

Tips for SQL Developers

Published 2022-06-16 by Kevin Feasel

Lee Markum has a few tips for you:

SQL Server Developers are under-rated.

That’s right! I’m a DBA and I said, “SQL Developers are under-rated.” Dedicated SQL Developers help I.T. teams by writing efficient code that gets just the data that is needed and in a way that leverages how the database engine works best. How do you ensure you’re doing great work for your company and building code that will stand the test of time?

I’m so glad you asked!

Click through for Lee’s advice. One big thing I’d add to Lee’s list is to understand the domain. Query writing skills are quite fungible across domains—moving from health care to auto parts sales, you don’t need to re-learn SQL—but understanding some of the arcana of the organization and its industry makes it a lot easier to know you’re writing good queries, getting valid data back, and not forgetting some important business rule.

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

Parameter Sensitivity Plan Optimization and Monitoring Scripts

Published 2022-06-16 by Kevin Feasel

Erik Darling gives us a warning:

You can read the full documentation here. But you don’t read the documentation, and the docs are missing some details at the moment anyway.

– It only works on equality predicates right now

– It only works on one predicate per query

– It only gives you three query plan choices, based on stats buckets

There’s also some additional notes in the docs that I’m going to reproduce here, because this is where you’re gonna get tripped up, if your scripts associate statements in the case with calling stored procedures, or using object identifiers from Query Store.

It’s not a deal-breaker but it does make things a lot harder for tool writers, as Erik points out. Hopefully there’s some way to tie this all together before GA.

Comments closed

SQL Server Client Tool Updates

Published 2022-06-16 by Kevin Feasel

Erin Stellato has a burndown list for us:

We introduced .NET Interactive Notebooks in Azure Data Studio through the .NET Interactive Notebooks extension. With multi-language support for Jupyter Notebooks you can now code in T-SQL, PowerShell, C#, JavaScript, and more. 

Never fear, folks: the .NET Interactive Notebooks extension does include the most important .NET language of all, F#. In all seriousness, F# is the type of language which was made for notebooks, especially with generative type providers.

Comments closed

An Overview of Clustering Algorithms

Published 2022-06-15 by Kevin Feasel

Gavita Regunath has a two-parter on clustering. First, an explanation of the concept:

Clustering, or cluster analysis, is an unsupervised machine learning method. As the name implies, unsupervised machine learning refers to how the model ‘learns’ the data. It is a learning process opposite to supervised learning. With supervised learning, models are trained or “supervised” using labelled datasets (a known function output to our data). An example of a supervised learning method is where a model is trained to recognise animals based on their labels of being a cat, dog and rabbit.

Unsupervised learning works with unlabelled data where there are no known function outputs, and the aim is to identify patterns within a dataset. There are many unsupervised learning algorithms, however, the three main types are clustering algorithms, dimensionality reduction and anomaly detection. The focus of this blog will be on clustering, as it is the most commonly used unsupervised learning technique.

Second, a review of ten clustering algorithms:

There are many clustering algorithms. In fact, there are more than 100 clustering algorithms that have been published so far. However, despite the various types of clustering algorithms, they can generally be categorised into four methods. Let’s look at these briefly:

Read on to learn more about clustering.

Comments closed

Tracking Table Updates via SQL Audit

Published 2022-06-15 by Kevin Feasel

Tracy Boggiano wants to figure out who keeps taking her lunch out of the company refrigerator:

I had a problem at work recently where a record was getting updated, and no one knew where or what was updating the record.  Our team discussed the best way to try to figure out what was happening.  The situation was if a record would be updated to active and within a ten-minute window, the record would be set back to inactive.  The system allows ad-hoc statements to run against and since it was to only a certain table, I suggested we set up a SQL Audit to track UPDATEs to the table.  The code for this is fairly simple, but since most of my colleagues don’t have exposure to SQL Audit, I figured a blog post would benefit others.

So, in this case, we are creating a Server SQL Audit that will write to D:\SQL Audit, so make sure that path exists.  Then a Database Server Audit Specification to track any UPDATEs that happen to the table.  Now, keep in mind I choose the method over running a server-side Trace or Extended Events because I knew it would capture everything without me having to worry about setting up anything else put these commands.  An important part of this is where I specify “public”.  That tells the audit to capture anybody that is updating the table.  If you want to look for a certain user or even maybe someone part of a role, you could specify that instead.

Click through for the auditing script. I wish this type of information were a lot easier to get, especially for longer-term audits. I end up creating metadata columns (created/modified user, created/modified date) but that gives limited information and requires all calling code play along.

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

Enumerating Azure Storage Replication Types

Published 2022-06-15 by Kevin Feasel

Arun Sirpal has a list:

Storage Accounts are pretty much integrated into so many different designs in Azure, whether you are using Azure Synapse, 3rd party product like Snowflake, or Event Streaming designs – we need it.

When you create a storage account there are 5 different replication types you should know about.  These are LRS, ZRS, GRS, RA-GRS and GZRS. Lots of abbreviations here, lets explain further.

Read on for the explanation.

Comments closed