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 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 closedTom 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 CommentsRobert 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.
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 closedAhmed 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