Press "Enter" to skip to content

Author: Kevin Feasel

Essential SQL Server Tools

Published 2018-04-30 by Kevin Feasel

Jens Vestergaard shares the most recent T-SQL Tuesday round-up:

Tuesday 3rd of this Month I invited people in the SQL Server community to share which tools are essential to their daily work. I was really overwhelmed by the number of stories that the topic triggered. 22 in total took the time to write down and share which tools they use for their work chores.
Going through 22 posts and aggregating them has been taking more time than I had hoped for, since my trusted laptop broke down – blinking codes are well and alive I tell you!

Click through for the 22 submissions as well as Jens’s set of links to the tools people mentioned.

Comments closed

Using Crosstabs To Learn About Categorical Variable Relationships

Published 2018-04-30 by Kevin Feasel

Stacia Varga shows one way of learning about the relationships between categorical variables in Power BI:

A common way to review categorical variable relationships is to create a cross tab, also known as a matrix, to evaluate the counts for each resulting combination.

For example, in my current data set, I can create a matrix to compare the number of players in two teams, say the Knights and the Sharks, by position and by handedness.

In descriptive analytics, I’m not trying to prove anything by looking at these values. I’m just reporting them. (Although I do find it interesting that there is a preponderance of lefties in these two teams.)

In the business world, I might do something similar by placing product categories on rows and customer geography (country or state) on columns.

Stacia also gives her explanation of descriptive analytics, so check that out too.

Comments closed

Introducing Azure Notebooks

Published 2018-04-30 by Kevin Feasel

Zach Stagers has an introductory post to Azure Notebooks:

No installation, no maintenance

As with any PaaS solution, Azure Notebooks makes it far quicker and easier to get up and running, as there’s no download or installation required. Microsoft handles all the maintenance for you too!

I’m working on a fairly big project using Azure Notebooks.  It’s very helpful getting 1GB of space, so I can include all of my data, images, etc. from a fairly large number of notebooks.  The big downside is that the server running these notebooks is pretty slow—even for a fairly simple ARIMA model, I had it sitting there for 10 minutes at 100% CPU.  So don’t expect to run a heavy workload against it.

Comments closed

Deleting A Small Number Of Rows From A Big Table

Published 2018-04-30 by Kevin Feasel

Brent Ozar has a tip for deleting a relatively small percentage of data from a very large table:

Say you’ve got a table with millions or billions of rows, and you need to delete some rows. Deleting ALL of them is fast and easy – just do TRUNCATE TABLE – but things get much harder when you need to delete a small percentage of them, say 5%.

It’s especially painful if you need to do regular archiving jobs, like deleting the oldest 30 days of data from a table with 10 years of data in it.

The trick is making a view that contains the top, say, 1,000 rows that you want to delete:

Read on for a demo.

Comments closed

Using Powershell To Find Linked Server References

Published 2018-04-30 by Kevin Feasel

Drew Furgiuele shows us how we can use the sqlserver Powershell module to find linked server references in code:

SQL Search is doing exactly what it’s designed to do here: it’s finding every object that matches that string, HumanResources. It’s unfortunate that it also happens to be the name of my linked server and a schema in my database, but such is life, right? Its returning every object it hits a match on, which includes a bunch of views, plus one of those views actually contains my linked server reference.

I love SQL Search

Let me be clear: I’m not here to gang up on the fine folks at Redgate. This tool is beautiful and I love it. Otherwise, how else could we quickly search for objects in our databases? The alterative would be either querying system views for object definitions or using cursors to call sp_helptext over and over, and then trying to do pattern matching. Same as what SQL Search does.

There’s no easy way to sort this wheat from chaff, is there? This might be a starting point; let’s narrow down the search the objects we might need to look at. Then, we’ll manually script each one out, one at a time. That sounds an awful lot like a manual process. “If only there was a way to automate this checking”, he asked, sarcastically.

Click through for a very interesting cmdlet.

Comments closed

Synchronous Kafka With Spring Request-Reply

Published 2018-04-27 by Kevin Feasel

Gaurav Gupta shows how to use Spring-Kafka to implement a request-reply pattern:

The behavior of request-reply is consistent even if you were to create, say, three partitions of the request topic and set the concurrency of three in consumer factory. The replies from all three consumers still go to the single reply topic. The container at the listening end is able to do the heavy lifting of matching the correlation IDs.

Kafka’s real advantage still comes from distributed, asynchronous processing, but if you have a use case where you absolutely need synchronous processing, you can do that in Kafka as well.

Comments closed

Scalar Function Blocking

Published 2018-04-27 by Kevin Feasel

Erik Darling notes that scalar functions can cause multi-table blocking:

Someone had tried to be clever. Looking at the code running, if you’ve been practicing SQL Server for a while, usually means one thing.

A Scalar Valued Function was running!

In this case, here’s what it looked like:

Someone had added that function as a computed column to the Users table:

Spoilers:  this was a bad idea.

Comments closed

Accessing SQL Server From Scala

Published 2018-04-27 by Kevin Feasel

Sidharth Khattri shows how to use Scala Slick, a library designed to integrate with database, to connect to SQL Server:

Now moving onto our FRM (Functional Relational Mapping) and repository setup, the following import will be used for MS SQL Server Slick driver’s API

import slick.jdbc.SQLServerProfile.api._

And thereafter the FRM will look same as the rest of the FRM’s delineated on the official Slick documentation. For the example on this blog let’s use the following table structure

CREATE TABLE user_profiles (
 id         INT IDENTITY (1, 1) PRIMARY KEY,
 first_name VARCHAR(100) NOT NULL,
 last_name  VARCHAR(100) NOT NULL
)

whose functional relational mapping will look like this:

class UserProfiles(tag: Tag) extends Table[UserProfile](tag, "user_profiles") {
 def id: Rep[Int] = column[Int]("id", O.PrimaryKey, O.AutoInc)
 def firstName: Rep[String] = column[String]("first_name")
 def lastName: Rep[String] = column[String]("last_name")
 def * : ProvenShape[UserProfile] = (id, firstName, lastName) <>(UserProfile.tupled, UserProfile.unapply) // scalastyle:ignore
}

I’m definitely going to need to learn more about this.

Comments closed

Powershell: Validating Parameters Using Private Functions

Published 2018-04-27 by Kevin Feasel

Mike Robbins shows how to split out validation from your primary function within Powershell:

They responded by asking if it was possible to move the custom message that Throw returns to the private function. At first, I didn’t think this would be possible, but decided to try the code to make an accurate determination instead of just assuming it wasn’t possible.

I’ve now learned something else which makes the whole process of moving the validation from the ValidateScript block to a private function much more user friendly which is what I think the person who asked the question was trying to accomplish.

If you have several parameters with somewhat complex validation logic, this makes maintenance a lot easier.

Comments closed