Press "Enter" to skip to content

Month: May 2021

Binary and Hexadecimal in Brief

Published 2021-05-20 by Kevin Feasel

Randolph West has a primer for us:

The CPU can only work with small amounts of data at a time. When the CPU is done with that data, it puts it in memory. Depending on how that data is managed (the on-or-off bit of information is literally called a bit because computer scientists are great at naming things), it usually forms part of a byte, which by convention is eight bits. In other words, you get eight bits of information per byte, represented by a series of 1s (electricity flowed for a fixed period of time) and 0s (no electricity flowed for that fixed period of time).

The uppercase “A” in the Latin alphabet, using the ASCII standard, is represented by the byte 01000001.

So, cool, this is binary. What does it actually mean? 

Read on for more.

Comments closed

Senior DBA Job Questions

Published 2021-05-20 by Kevin Feasel

Joey D’Antoni shares some sample job interview questions for hiring a senior DBA:

In my role as a consultant, it’s rare that I go on an interview anymore, though I occasionally get interviewed by a client, or interview potential DBAs for clients as part of my role. There are a number of these lists on the internet, but many of them are old and focus on trivia questions (there won’t be any questions on what’s clustered versus nonclustered index, but if you are interviewing for a senior role, you should know that anyway. I also like to focus on open ended questions, to gauge the depth of knowledge of the person I’m interviewing.

I will say that when hiring on the database engineer (i.e., development) side, the questions I love best aren’t trick questions; they’re experiential questions. For example, “Here is a common type of problem we need to solve. What would you do in this scenario?” And then we can dive in. As a quick example of one, “You’ve just taken over ownership of a database where most of the clustered index keys are uniqueidentifiers. Would you consider GUIDs a good clustered index candidate?” Then we can talk about yes or no, what makes for a good clustered index, and how you might go about changing it. Oh, and I’ll admit that my thought on this question has changed since Jeff Moden’s outstanding presentation on the topic.

Comments closed

High Availability Options for DBAs

Published 2021-05-20 by Kevin Feasel

Pamela Mooney has a list:

In previous articles in this series, I have stated that the job of the DBA is to make the right data available to the right people as quickly as possible.

Here is where we delve more into the word “available” and take it up a notch. SQL Server offers several options for high availability, and understanding the advantages and caveats of each one will give you the best chance of ensuring the availability of data in any scenario. Let’s discuss the options for high availability in general terms and find out where to go to get more information as you need it.

Due to the breadth of this article and keeping with the idea of just learning the basics, I am not going to cover Azure here except to say that Azure either has compatibility with these features in most of its offerings uses them in background processes.

Read on for the list.

Comments closed

Reasons Your T-SQL Might Fail a Code Review

Published 2021-05-20 by Kevin Feasel

Brent Ozar keeps a list:

It’s hard to set absolute rules about, “Feature X should absolutely never be used.”

However, there are some features that set off alarm bells when I see them. Usually, when I start asking more questions about when we’re using those particular features, I get answers of, “Oh, I didn’t know that was a problem.” As we have a bigger discussion, it leads to the piece of code failing the code review, and going back to the drawing board for improvements.

I’ve definitely done 1-7 on purpose and 8 in protest. 9 is something I do when running ad hoc SQL in SSMS just to make sure that I didn’t goof too hard (and usually it’s a rollback rather than commit). But this is a really good list.

Comments closed

The Value of Bubble Charts

Published 2021-05-19 by Kevin Feasel

Elizabeth Ricks takes us through a surprisingly tricky chart:

An extension of a scatterplot, a bubble chart is commonly used to visualize relationships between three or more numeric variables.  Each bubble in a chart represents a single data point. The values for each bubble are encoded by 1) its horizontal position on the x-axis, 2) its vertical position on the y-axis, and 3) the size of the bubble. Sometimes, the color of the bubble or its movement in animation can represent more dimensions. 

I say surprisingly tricky because it’s easy to overwhelm the user when trying to view bubble charts. I think the best scenarios are cases in which you have relatively few data points and the size element is mandatory.

Hans Rosling (RIP) did an outstanding job of displaying this kind of chart with the Gapminder dataset.

Comments closed

Row-Level Security in Serverless SQL Pools

Published 2021-05-19 by Kevin Feasel

Jovan Popovic has a method of implementing poor man’s row-level security in Azure Synapse Analytics serverless SQL pools:

Serverless Synapse SQL pools enable you to read Parquet/CSV files or Cosmos DB collections and return their content as a set of rows. In some scenarios, you would need to ensure that a reader cannot access some rows in the underlying data source. This way, you are limiting the result set that will be returned to the users based on some security rules. In this scenario, called Row-level security, you would like to return a subset of data depending on the reader’s identity or role.

Row-level security is supported in dedicated SQL pools, but it is not supported in serverless pools (you can propose this feature in Azure feedback site). In some cases, you can implement your own custom row-level security rules using standard T-SQL code.

Read on to see how.

Comments closed

New Features in R 4.1.0

Published 2021-05-19 by Kevin Feasel

The Jumping Rivers folks have some good news for us:

The stability of the base packages is a great strength of the R ecosystem: both underpinning, and contrasting with, the rapid pace at which contributed packages (CRAN, BioConductor) evolve.

Imagine introducing a new feature into the R language. Even if problems arise with the usability of that feature, it would need to be maintained until at least the next major release, by which time thousands of developers and analysts may depend upon it. Unsurprisingly, the R maintainers are exceedingly cautious when introducing new syntax.

Similarly, you should employ caution when using new syntax in your own code. If you do use syntax that was introduced in R-4.1, be aware that your code will not run on versions of R that precede this. For example, this may prevent your new analysis scripts from running on your colleague’s computer, or prevent users from installing your new package.

Given how many third-party packages have regular breaking changes, I do wish more people would follow this advice.

Getting into the meat of things, I really like the F#-style pipe in R: |> makes a lot of intuitive sense, though I do wish they had included a placeholder element with the native pipe.

Comments closed

SSMS Templating

Published 2021-05-19 by Kevin Feasel

Kenneth Fisher shows off templating in SQL Server Management Studio:

Several times over the last few weeks I’ve gotten a request to create a new work database. The individuals from this team each have their own database that they can use as a type of scratch pad and I guess they’ve been hiring. It’s simple enough to create the database and then grant the necessary permissions, but let’s face it, after the first time I was already tired of the GUI and scripted the process out. Running the script was better but I quickly became annoyed at having to make changes to the script. User names etc. So I decided to create a more permanent script. My first thought was to use dynamic SQL. While that would work, and I’m certainly comfortable with dynamic SQL, it just didn’t feel right for this. I decided in the end to use a trick from templates. If you set up parameter(s) in the file you can use Ctrl-Shift-M to scroll through them and make changes. In an odd twist these are called template parameters. 

Click through for a demo.

Comments closed

Using Database Projects for Declarative Database Development

Published 2021-05-19 by Kevin Feasel

Haroon Ashraf explains the principles behind database projects and demonstrates their use:

This article is all about declarative database development using Azure Data Studio for both beginners and professionals who are new to it.

Additionally, some professional life tips in the context of the topic are also shared. The importance of declarative database development over its counterparts can also be fairly understood by going through this article.

Conceptually, I love it. Focusing on the end state is easier to understand. The problem I run into is that the tooling for generating change scripts is not great. It works for trivial database sizes, but as soon as you start talking dozens or hundreds of gigabytes of data, database projects have a tendency to do rather drastic changes which require rebuilding the table, when they could (with a bit of human smarts) perform an action which is much less disruptive. So in the end, you still end up needing to create change scripts.

Comments closed

From Excel to SQL Server via Powershell

Published 2021-05-19 by Kevin Feasel

Kevin Wilkie combines Excel and dbatools like a mad scientist:

Those ways work great if you’re inserting one tab of an Excel spreadsheet. I don’t know about y’all, but I don’t want to have to do all of this work for each tab – especially if I have 30+ tabs to import into SQL Server (and yes, I’ve been asked to do that quite a few times.)

One of the easiest ways I’ve seen to insert a lot of data (once you’ve made sure that the data is how you want it) is to use the power of PowerShell.

Read on to see how.

Comments closed