A Fine Slice Of SQL Server
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 closedTracy 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 closedStorage 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 closedCharith 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 closedHasan 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…
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.
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 closedRobert Cain continues a look at KQL query syntax:
As with other languages such as SQL, KQL has an operator for returning a unique list of values in a column:
distinct. Using this you can return the values in a column, but only once, removing any duplicate values from the result set.
Click through for a few examples of how you can use distinct as well as one terrible pun.
Tom Zika writes out a list of coding standards:
No magic constants
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 CommentsDerik 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