Press "Enter" to skip to content

Curated SQL Posts

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

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

Finding Assigned Synapse RBAC Roles in Powershell

Published 2022-06-15 by Kevin Feasel

Charith Caldera wants to know your access level:

One of the key use cases that most customers face difficulties while retrieving or assigning the role-based access control in Azure Synapse Analytics, that they cannot find the correct usernames, group names or the service principal names using the PowerShell cmdlet “Get-AzSynapseRoleAssignment”. The PowerShell cmdlet only provides limited information and it’s difficult to understand since that contains the object IDs. 

Read on for a script which helps with this problem.

Comments closed

Using DATE_BUCKET() in SQL Server

Published 2022-06-15 by Kevin Feasel

Hasan Savran starts bucketing:

The Date_Bucket function is introduced in Azure SQL Edge which is mainly used by IoT devices. This useful function returns the date-time value corresponding to the start of each date-time bucket from the timestamp defined by the origin parameter, or the default origin value of 1900-01-01 00:00:00.000. In other words, it lets you arrange data into groups that represent fixed intervals of time. SQL Server 2022 includes this useful function in its database engine.

Results of Data_Bucket might be confusing, Let’s look at its syntax first.

Hasan is quite right here: the results of DATE_BUCKET() are not intuitive, though they do make some sense…eventually…

Comments closed

Visualizing SHAP Values in R with shapviz

Published 2022-06-14 by Kevin Feasel

Michael Mayer announces a new package:

SHAP (SHapley Additive exPlanations, Lundberg and Lee, 2017) is an ingenious way to study black box models. SHAP values decompose – as fair as possible – predictions into additive feature contributions.

When it comes to SHAP, the Python implementation is the de-facto standard. It not only offers many SHAP algorithms, but also provides beautiful plots. In R, the situation is a bit more confusing. Different packages contain implementations of SHAP algorithms

Read on to see how shapviz works, how to install it, and the types of visuals you can create from it.

Comments closed

Lasso and Ridge Regression

Published 2022-06-14 by Kevin Feasel

Niraj Kumar explains how two regression techniques work:

Lasso Regression is a regularization technique used for feature selection using a Shrinkage method also referred to as the penalized regression method.

Lasso is short for Least Absolute Shrinkage and Selection Operator, which uses both for regularization and model selection.

If a model uses the L1 regularization technique, then known as lasso regression.

Click through for a summary of the two techniques.

Comments closed

Practical Code Development Standards

Published 2022-06-14 by Kevin Feasel

Tom Zika writes out a list of coding standards:

No magic constants 

WHERE p.ProductType <> 4

What is 4? Just set a variable (constant) from a lookup table. Or write a comment with an explanation.
It’s the least you can do.

Read on for a set of things I generally agree with. This one’s a little bit of a tough case, as in extremely high-performance systems where the cost of a lookup is just too expensive, I’d prefer to use keys rather than values and save the compute cost and possible worse query plan. That said, even in that case, yes, definitely write a comment explaining what 4 is. That said, had I picked any of the other dozen or so recommendations, I wouldn’t have had anything interesting to say other than “go read this.”

2 Comments

Management Strategies: Architects and Gardeners

Published 2022-06-14 by Kevin Feasel

Derik Hammer discusses two management strategies for team leadership:

There are two analogies for leaders that have made a visceral impact on my life and career, the architect leader and the gardening leader. These analogies became central to my personal and professional growth ever since I formally entered management five years ago. Arguably, I have been a leader for much longer than that in the various team or technical leadership positions and my time in the military. However, it wasn’t until my focus moved predominantly to management that I began building models for leadership mindsets.

My philosophy on this hews really well with the book Turn the Ship Around! by David Marquet. I highly recommend it for anybody in management or looking to go into management. One really short synopsis of the strategy I try to follow is to make goals clear, get the level below you invested (in part by bringing them in and actually listening to what they say), and be hands-off enough to let people learn and take initiative in how they solve problems and meet your goals.

Comments closed

Point-in-Time Recovery with Azure SQL DB and Managed Instances

Published 2022-06-14 by Kevin Feasel

Ahmed Mahmoud looks at point-in-time recovery and answers some frequently asked questions:

On some occasions, after the failover is initiated, the current Primary DR will start a new backup chain from that point and old backups are available on the current secondary DR. If we want to restore the backups which exists in Secondary it will not allow us to perform, apparently restore cannot be initiated on the Primary as the backup is not available.

Also, sometimes we observe in secondary DR for few databases, PitR restore point is available and for few databases it shows “no restore point available”    

Read on to understand why that happens and what you can do about it.

Comments closed