Azure Data Lake ACL Introduction (2021-08-21 DBW)

A Lack of Error Handling

I saw a post by Brent Ozar the other day, and it was a bit disturbing to me. A survey he posted on Twitter showed most people don't bother with error handling in T-SQL. I'm not surprised, though I wonder if people think about the "rare" as including all the one single statement queries they write. I certainly don't expect error handling for simple queries when they are a single statement used to return data to a client.

However, in stored procedures and multi-statement batches, I see the same thing as the results Brent published. Very little error handling. I've tried in my demos to add it and be sure that I set a good example, even though most of the time I'm showing code that just always works. I know what data I'll enter for a demo, and I don't run into issues.

Many years ago, decades actually, when I was in college and early in my career, I saw many application software packages written without a lot of error handling. This included C/C++ at the time, which seems crazy. In the last decade, I've seen a lot more robust error handling (and testing) added to the work of many application developers. It's the request that my professors and more than a few bosses always made, but in the late 80s/early 90s, few people actually followed through with. In the 2010s, this seems to be more common, and not surprisingly, software quality has improved.

In many ways, database developers are less mature than application software developers in many ways. Less error handling, less automated testing, and that's understandable. We haven't had great tools or patterns to help us easily adopt these practices as a habit. In addition, the way in which code is compiled and then executed doesn't make this easy for us. The platform and structure of the language create complexity that isn't present in application languages.

If you haven't written this in the past, as Brent notes, there's not need to worry about going back now. Either your code is working or not working, and if it's the former, no reason to revisit it. However, I might ask that you experiment with error handling for new code. Learn how to use these structures to protect against your users entering strange data. Something they are likely to do.

Steve Jones - SSC Editor

Join the debate, and respond to the editorial on the forums

How to create a single set of SQL migration scripts for Flyway that we can use across multiple database systems, or for all regional variants of a database.

A set of PowerShell cmdlets that will 'diff' two versions of a database and provide a high-level overview of the major database changes made by successive Flyway migrations. You can 'diff' a SQL Server database to the same one on PostgreSQL and find out which objects are the same and which are different.

Reasoning frameworks of artificial intelligence used in criminal justice and health care systems move us to rethink how AI can be constructed to help foster an equitable society. We...

We unpack what you need to know about artificial intelligence for IT operations and how AIOps benefits multiple parties in enterprise IT.

The problem with spear phishing is that it takes t...

Introduction AutoML in Azure Machine Learning is used to build machine learning models on its own as we discussed in the previous article. Over there, we discussed what AutoML...

SQL Server Transparent Data Encryption (TDE) enables encryption on database files to secure its databases. Enabling TDE might have some adverse effects on your database system or on some...

When you are troubleshooting query performance, it’s important to consider when SQL Server last restarted. Each time SQL Server restarts, buffer pool and plan cache get wiped out. This...

Kendra’s query was a good starting point, and I used most of it in the first CTE shown below. This query basically looks at msdb.dbo.sysjobhistory and msdb.dbo.sysjobactivity, joining them... The...

Have you heard of SQL Server’s dedicated administrator connection? The dedicated administrator connection (DAC) can come in handy in an emergency scenario so you should have it enabled and...

As SQL Server DBAs, we’ve heard that index structures can dramatically improve the performance of any given query (or set of queries). Still, there are certain details that many...

One of the most important aspects when looking to migrate from AAS to PPU is what will costs be. This plays an important part in the decision. In this blog post instead of doing an AAS and PPU comparison I am going to do this side by side, which I feel will make it easier to compare.

RDD, Dataframe and Dataset are all Spark APIs introduced in Spark at different points in time. The goal of these API is to help us work with large datasets in a distributed fashion in Spark with performance in mind.

In the previous blog, I illustrated how to create sub-queries in Kusto. However, sometimes we may face even more complex situations and we may need to create not only a sub-query, but a function.

This article explores the Maximum Degree Of Parallelism (MAXDOP) for the Azure SQL Database. Introduction A DBA needs to do proactive monitoring, troubleshooting, and work on optimizing your query...

Access Control Lists (ACLs) offer low-level control of access to the folders within your Azure Data Lake, whilst Role-Based Access Control (RBAC) offers high-level control to the entire lake.

When you jump into a data lake, you’ll find that if properly designed, it will be split into designated zones. Each zone has a mission to fulfill that justifies...

Companies asking “What is hybrid IT?” may be actually implementing it. Here’s how to take a strategic approach.

Communities of practice bring people together to share ideas and learn from each other about a common interest. In this article, Robert Sheldon explains and gives some advice on...

Editor’s Note: I wrote this last December, and went back and forth on publishing. As I see some of the poor leadership patterns from PASS, creeping into new offshoots...

Before we discuss how to change the slow query log threshold on RDS, let’s quickly establish what the slow query log is.

Click to learn more about author Keith Neilson. Multi-cloud is positioned to take over this year, as over 90% of enterprises worldwide are expected to depend on a blend...

In this article, we will go over some of the most fundamental ways to filter data hosted in PostgreSQL. Introduction Data is hosted in a variety of data repositories,...

I may be completely off base here, but I’ve noticed a correlation between folks who use Amazon Web Services and their understanding that once you scale up a service... The...

Join Donovan Brown, Partner Program Manager, and Mark Fussell, Lead Program Manager from the Azure CTO Incubations team at Microsoft as they talk all things modern Cloud Native development with Dapr.

Join Grant Fritchey and database professionals from WestJet, Fiducia Gad and Richemont to learn how effective estate monitoring enables them to manage continually evolving environments, and intensifying data demands.

This fall, I’m going to teach you the fundamentals of Microsoft database performance tuning, live, for free. The first class is my How to Think Like the Engine class....

Fun With PowerShell Code Formatting

Today’s quick makeover post was inspired by a discussion in a recent client workshop. At the beginning of our sessions, we ask attendees to share what they want to...

When people think about sports, many things may come to mind: Screaming fans, the intensity of the game and maybe even the food. Data doesn’t usually make the list....

Data protection, now more than ever, is providing peace of mind while helping you ensure business continuity. The world of data protection is wide and varying, across new technology and...

As I was browsing Twitter today, I noticed a tweet from the Colorado Department of Transportation about their anti-DUI campaign. Shown below, it contains a bar chart that appears... The...

Reading Time: 4 minutes In this post I want to cover an Azure Test Plans example for Azure Data Factory as part of a series of posts. For...

The portable SSD market has been steadily expanding thanks to the increasing digital footprint of consumers. Technological advancements such as 3D NAND with high layer counts and the emergence...

"Alchemist" will be Intel's first serious dedicated gaming GPU.

ADDMISSINGITEMS: Add the rows with empty measure values back. https://dax.guide/addmissingitems/

This article explains how the CONTAINS function works and what can be used as better alternatives in DAX in common use cases. The CONTAINS function in DAX has been...

GENERATE: The second table expression will be evaluated for each row in the first table. Returns the crossjoin of the first table with these results. https://dax.guide/generate/ GENERATEALL: The second...

Microsoft is investing in software startup Rubrik and the two companies will combine on products that will help customers hit by ransomware recover their critical data without paying hackers.

Cached Out There are lots of examples of how to do...

When you’ve got a process that uses temp tables, and you want to speed it up, it can be tempting to index the temp table to help work get...

In part 3, I tie it together and show how to use relational logic to further eliminate false positives.

This article intends to give information about the SQL Server filtered indexes and their performance impacts. Introduction Indexes are the special data structures that help to improve the performance...

tldr: No. You have to use sp_refreshview. One of the only good things to come out of all of the ... Continue reading

Seriously If you’re using Entity Framework, and sending in queries that build up IN clauses, they won’t end up getting parameterized. Even Forced Parameterization won’t help you if you’re...

This is not the first time I have shared this concept. In my previous article I showed how it is possible to add a prefix to every column in...

There are 3 signs that we look for to help tell us...

ABSTRACT ?? Visualizing large amounts of data can be overwhelming and frustrating regardless of one’s skill level, so imagine how frustrated and exhausted the viewing audience can become when...

Power BI Push Tools is a set of open-source tools to work with Power BI push datasets. Read Implementing real-time updates in Power BI using push datasets instead of...

Frustrated that a you can only do a full refresh? ...

Using Power BI Goals to manage Objective and Key Results (OKRs) are a great way to keep your remote and hybrid employees in sync with minimal overhead. The OKR technique enables you to create clear behavior targets for groups and individuals that are tied to underlying activities and data.

Let’s Orchestrate… If you have read Part 3 and Part 4 of this series, you’ve probably noticed that I was building towards something. By combining the best practices in version control and the best practices in testing, you can start to automate the building and testing of Power BI reports/datasets.

Learn a unique way to utilize a waterfall by restricting its categorical comparison to just two values, with a breakdown comparison between. By combining a slicer and a bit of DAX magic in Power BI.

You probably know that it’s a best practice to build your Power BI datasets in a separate .pbix file from your reports – among other things it means that...

This article explains the fastest ways to restore the previous version of the database, to recover from a failed Flyway migration that leaves the database in an indeterminate state,...

This article will get you up and running quickly with Flyway migrations on MariaDB or MySQL databases, from PowerShell. The post Flyway with MariaDB for Those of a Nervous Disposition...

We're officially announcing the release of mssql-django v1.0 as an open source project! At Microsoft we've heard from the community loud and clear - SQL Server is the biggest enterprise...

Last year, we introduced the sixth generation of EC2 instances powered by AWS-designed Graviton2 processors. We’re now expanding our sixth-generation offerings to include x86-based instances, delivering price/performance benefits for workloads...

No, not Azure Data Studio or Databricks notebooks (yet) – I wanted to give Google Colab a spin and the big G hosted Jupyter notebook has an expansive free tier with more RAM than my work computer and a graphics card attached to boot!

Implementing a unit of work in Python can be an interesting challenge. Consider the following code: This is about as simple a code as possible, to associate a tag to...

From the previous two blog posts: Creating REST API for reading data from Microsoft SQL Server in web browser Writing DAta to Microsoft SQL Server from web browser using...

Over the course of the last two and a half years, ...

Data reportedly includes SSNs, driver's license numbers, and more for 100 million people.

It’s a big one: As first reported by Motherboard on Sunday, someone on the dark web claims to have obtained the data of 100 million from T-Mobile’s servers and is...

'DeepBlueMagic' also deletes Volume Shadow copy for Windows, making recovery all but impossible without a decryption key.

CISA, Mandiant, and ThroughTek share the details of a vulnerability that could allow attackers to observe camera feeds and remotely control devices.

Apple’s NeuralHash algorithm — the one it’s using for client-side scanning on the iPhone — has been reverse-engineered. Turns out it was already in iOS 14.3, and someone noticed: Early tests...

Everything in an event-driven architecture centers around the event. Put simply, an event is anything interesting that happens in your application. Events at all levels of your application, from the end-user client level down to the network connectivity level, may be noteworthy.

In previous blog post Creating REST API for reading data from Microsoft SQL Server in web browser we have looked into creating REST API calls for reading data from...

David Tovee asked a great question in yesterday’s Mastering Query Tuning class. He asked his fellow students, “How many of you actually use TRY/CATCH?” I turned it into a...

My friends! Last time together, we discussed using the STRING_SPLIT function and how it’s used in combination with the CROSS APPLY.

After the last post on Cumulative Distribution Function (CDF) or as it is known in TSQL CUME_DIST(), I realized that although I showed how to use it, I didn’t...

My friends! Last time together, we discussed using...

In a multi-user environment, it is essential to maintain truncation concurrency. These locks are in-memory structures of 96 bytes in size. Their role is to maintain data integrity, consistency,...

Here is the simple and practical use case of NTILE function. We’ve used it to divide the rows of sys.columns into N batches. N is the batch size. We’ve... The...

User-defined functions in SQL Server (UDFs) are key objects that each developer should be aware of. Although they are very useful in many scenarios (WHERE clauses, computed columns, and...

This app had an Android Auto-like interface, but for people without compatible cars.

"I know this is very, very hard on our NASA and Bo...

(last updated: 2021-08-13 @ 19:50 ET / 2021-08-13 ...

This article will deploy SQL Server on Azure VM using a Jupyter notebook in Azure Data Studio. Introduction The SQL on Azure VM is an Infrastructure-as-a–Service(IaaS) for migrating or...