A Fine Slice Of SQL Server
When doing inserts, updates or delete statements using T-SQL always, and I mean always, run them inside a transaction.
You can do this in different ways in SQL Server. One of the most popular ways is to start an explicit transaction by using the begin transaction command. From there you can either commit the transaction if it is good or issue a rollback command to undo the transaction.
Read on for the why. This is excellent advice.
Comments closedMatt Allington pulls out the dynamite:
OK, so what’s the problem? The problem is that sometimes you get such a deep, nested path of measures that the underlying formula is more complex than it needs to be, and it is virtually impossible to debug. If you simply explode/expand a final measure into the full, underlying DAX code, it makes it much easier to see what is going on. Returning to the DAX example above, this is what the third measure actually looks like under the hood, removing all references to the dependent measures
Click through for an article and a video.
Comments closedChad Callihan has some advice:
A quick T-SQL/SSMS trick that I know and love is the ability to add text to multiple lines at a time. I tend use macros in Notepad++ to accomplish these types of repetitive actions but this type of work can be completed in SSMS.
Annoyingly, this doesn’t work quite the same way in VS Code or Azure Data Studio. Alt + Down moves a line down one, Alt + Shift + Down copies the current line. To replicate this behavior in VS Code / Azure Data Studio, hold down Alt + Shift and use the mouse to select the lines you want.
Comments closedCathrine Wilhelmsen gets some Lego data:
In the April session of the Azure Synapse Analytics and Microsoft MVP series, I got to show one of my favorite demos: ingesting and exploring LEGO datasets! 🤓 In just a few minutes, I grab a compressed file from the web, decompress it, store it as a Parquet file in my data lake, and run SQL queries on it using Serverless SQL. It really is that simple:
Click through for a video and a step-by-step walkthrough.
Comments closedJust released this fall, is the fully managed Synapse Connector. Azure Synapse Analytics provides a platform for data analysts and data scientists to analyze and combine data from multiple sources. Within Confluent Cloud, data can be synched to dedicated SQL pools via the fully managed Synapse sink connector and attached to Synapse Analytics workspace. Once added to the Synapse Analytics workspace, analysts have the ability to perform advanced analytics and reporting on data in the Confluent pipeline. The ability to access event-level data enables event-level analytics and data exploration.
Click through for two examples, one of loading data into a dedicated SQL pool and one of streaming data into Spark Streaming running on (naturally) a Spark pool.
Comments closedMilind Oke, et al, describe RBAC in Amazon Redshift:
Amazon Redshift is a fully managed, petabyte-scale data warehouse service in the cloud. With Amazon Redshift, you can analyze all your data to derive holistic insights about your business and your customers. One of the challenges with security is that enterprises don’t want to have a concentration of superuser privileges amongst a handful of users. Instead, enterprises want to design their overarching security posture based on the specific duties performed via roles and assign these elevated privilege roles to different users. By assigning different privileges to different roles and assigning these roles to different users, enterprises can have more granular control of elevated user access.
In this post, we explore the role-based access control (RBAC) features of Amazon Redshift and how you can use roles to simplify managing privileges required to your end-users. We also cover new system views and functions introduced alongside RBAC.
Read on to learn about system-defined roles as well as creating user-customizable roles.
Comments closedMarco Russo and Alberto Ferrari explain the idea of filter context:
This article is part of a series of articles about the basics of DAX. In a previous article, we introduced the first evaluation context in DAX: the row context. If you are not familiar with the row context, we strongly suggest that you start by reading that article first. Here, we build upon your knowledge of the row context to introduce the second evaluation context: the filter context.
Be mindful that the most relevant information you need to master about evaluation contexts is the difference between the row context and the filter context. You cannot appreciate any difference until you know exactly what the two contexts are. This is the reason why approaching the filter context without any existing knowledge about the row context would be only partially useful.
Read on to understand how this all differs from row context.
Comments closedSanjay Raut introduces an interesting report:
The Azure Synapse Analyzer Report was created to help you identify common issues that may be present in your database that can lead to performance issues. This report focuses on known best practices that Microsoft has identified with SQL Dedicated Pools. Following these best practices will help to get the best performance out of your solution.
One thing I appreciate about this is that it covers many items which people don’t know to think about when moving over from SQL Server or Azure SQL Database.
Comments closedCraig Porteous begins a new series:
Reference architectures are great! You’ve got all of the key components in there, nice and clear. Colourful lines showing how data moves through each stage, product, or service. Great for a slide deck or a proposal to get rid of that old creaking data warehouse and into a shiny new Data Lakehouse.
Not so great for the finer details demanded by security operations teams however.
This promises to be an interesting series.
Comments closedAndy Leonard provides an after-action report:
During delivery of the class, I popped over to a much older data factory and fired up a couple integration runtimes (IRs). You see, on this older data factory, I trigger a couple pipelines that check to see if I’ve left an IR running. If so, each pipeline will shut down its respective IR. The trigger fires each evening. I blogged about the pipeline design almost two years ago in a post titled Stop an Azure-SSIS Files Integration Runtime (Safely).
Read on for the full report, some takeaways on how to limit the risk, and possible next steps if you find yourself in a situation like Andy did.
Comments closed