A Fine Slice Of SQL Server
Steven Sanderson looks for a sub-string:
When working with text data in R, one common task is to check if a character or substring is present within a larger string. R offers multiple ways to accomplish this, ranging from base R functions to packages like
stringrandstringi. In this post, we’ll explore how to usegrepl()from base R,str_detect()fromstringr, andstri_detect_fixed()fromstringito achieve this.
Click through for three separate ways of doing this.
Comments closedHave you ever found yourself staring at a graph or slide, wondering what the creator was trying to convey? Perhaps you’ve sat through a presentation, only to be left scratching your head, unsure of what to do next. Don’t put your audience in this same uncomfortable position. Instead, connect the dots for them to make it clear what the point is and what action they should take. When you fail to explicitly state the purpose of your communication, you run the risk of the important insight being lost, or someone arriving at the wrong conclusion.
Read on for an example of comparing resource plans to actual allocations and moving from showing data to telling a story.
Comments closedTanayankar Chakraborty troubleshoots a strange issue:
Issue
An issue was brought to our attention recently where an azure SQL DB was throwing TempDB related errors although the customer felt that the TempDB usage never came close to the value published in the official Microsoft document. Here’s the error the customer had complained about:
Error
Here is a more detailed error text :
The database ‘tempdb’ has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions.’. Possible failure reasons: Problems with the query, ‘ResultSet’ property not set correctly, parameters not set correctly, or connection not established correctly.
This was an interesting problem and, admittedly, I didn’t predict the twist.
Comments closedJeff Iannucci continues a series on security in SQL Server:
SQL Server logins are often necessary, like the sa login, but be aware they present a particular set of vulnerability issues. Their lack of multifactor authentication, their susceptibility to brute force attacks, and the dumb passwords folks give SQL Server logins make them a prime target for hackers.
Read on to learn more about these three issues. Jeff also includes a note about a built-in function that lets you compare the hashed passwords in your systems versus a specific password, with the idea that you can at least find if people are using some of the most common passwords.
Comments closedKoen Verbeeck runs into an annoying error:
I have a metadata-driven ELT framework that heavily relies on dynamic SQL to generate SQL statements that load data from views into a respective fact or dimension. Such a task is well suited for generation, since the pattern to load a type 1 SCD, type 2 SCD or a fact table is always the same.
To read the metadata of the views, I use a couple of systems views, such as sys.views and sys.sql_modules. At some point, I join this metadata (containing info about the various columns and their data types) against metadata of my own (for example, what is the business key of this dimension). This all works fine in Azure SQL DB or SQL Server, but in my Fabric warehouse I was greeted with the following error:
The query references an object that is not supported in distributed processing mode.
Read on to learn more about why you get this error and one workaround for it.
Comments closedAlthough I live in Copenhagen and mostly walk or ride my bicycle in order to get around town, I do own an old car for getting around the rest of the country. In Denmark, cars go through mandatory official inspection every other year, and I’ve been through a few of these in my life. A few years ago, the mechanic doing the inspection informed me that my car’s chassis number was incorrect.
This did make me a bit nervous, because I’d bought the car used, and I was suddenly concerned that things weren’t really as I thought. Had I unwittingly bought a stolen car?
But the mechanic just walked over to his computer in order to correct the error. That’s when a different kind of unease hit me. When you’ve programmed for some decades, you learn to foresee various typical failure modes. Since a chassis number is an obvious candidate for a natural key, I already predicted that changing the number would prove to be either impossible, or have all sorts of cascading effects, ultimately terminating in official records no longer recognizing that the car is mine.
Mark uses this as a jumping-off point on a discussion about whether to use natural keys as primary keys or whether to include surrogate keys instead. I am generally in favor of using surrogate keys in the physical data model and creating unique indexes for natural keys. But you have to use natural keys in the logical data model because surrogate keys don’t exist at the level of the logical data model. Do read the comments, though, because there’s a great debate in there.
Comments closed