Press "Enter" to skip to content

Curated SQL Posts

Explaining SQL_VARIANT

Published 2020-04-17 by Kevin Feasel

Kenneth Fisher explains what the SQL_VARIANT data type is used for:

The SQL_VARIANT data type is an interesting beast. It is a data type that can store most types of data. So a date, decimal, int, varchar etc. can be stored in this single data type. This sounds great right? Well there are a few issues here.

I don’t think I’ve ever used SQL_VARIANT data types before. It always struck me as a refuge for not wanting to think about what the proper data type should be. In fairness to it, though, I’ve seen plenty of unthoughtful solutions using NVARCHAR as well.

Comments closed

Querying Database and Log File Sizes with T-SQL

Published 2020-04-17 by Kevin Feasel

Allen White takes us through an easy technique to check database and log file sizes:

As a consultant, I have to be able to quickly spot problems, and one of the problems I frequently find is transaction log files that are incorrectly sized.

There are two catalog views in the master database which make this easy to do – sys.master_files and sys.databases. The sys.master_files view contains the database and individual file names, and the data_space_id column always has a value of 0 for the log file. The size column returns the value in 8KB pages, so we have to multiply the column by 8, then divide by 1024 to get the size in megabytes (MB).

Click through for the demo.

Comments closed

Inserting Geospatial Data into Cosmos DB

Published 2020-04-16 by Kevin Feasel

Hasan Savran shows us how we can use the .NET SDK to insert geospatial data into Cosmos DB:

GeoSpatial Data can help you to answer many questions in your business If you know how to use Spatial data. Searching data by radius can bring you all kind of interesting data. For example, If you know the path of hurricane, you can make searches by using the path and find all your customers under that path. Then you can be proactive and do something about this upcoming problem for your business.

     Many databases support Spatial Data Types, I will cover how to store Spatial Data in Azure Cosmos DB in this post. I have an earlier post about how to import Spatial Data into Azure Cosmos DB by using Data Migration Tool. I will focus on how to store spatial data by using .NET SDK in this post. I used .NET SDK 3.8.0, you can get the latest SDK from here..

Click through for a demonstration.

Comments closed

The Importance of Gridlines

Published 2020-04-16 by Kevin Feasel

Stephanie Evergreen shows why (subtle) gridlines are so important in visuals:

Here’s the thing: This chart NEEDS gridlines. I’ve said this before but I find this anti-gridline trend so common that I need to address this topic explicitly.

The *medium gray not black* gridlines are necessary because I do not have data labels on every one of the dots in the chart. 

A quick reminder is that even Edward Tufte (a key proponent of the “gridlines are bad” school) doesn’t hate all gridlines. Subtlety is key with them: they should be there when you need them but easily ignored when you don’t.

Comments closed

Working with Jupyter Books in Azure Data Studio

Published 2020-04-16 by Kevin Feasel

Jamie Wick takes us through using Jupyter Book in Azure Data Studio:

The first thing to know is that Jupyter Books and “Jupyter Book support” (in Azure Data Studio) are slightly different concepts. Jupyter Books let you build web-based collections of Jupyter notebooks. Jupyter Books support allows you to build collections of Jupyter notebooks on your local computer or network (ie. not web-based). Additionally, all of the standards and functionality of the online Jupyter Books may not be fully supported/implemented in Azure Data Studio.

Click through for what this means as well as what the March 2020 release brought us.

Comments closed

Bulk Migration of Data and Log Files in SQL Server

Published 2020-04-16 by Kevin Feasel

David Fowler shows how you can change file paths for all of your databases in one fell swoop:

You’ve got a SQL Server with a few hundred databases on it (to be honest it doesn’t even need to be quite that many) and you need to move all the data and log files to a new location. Perhaps you’re going to be migrating onto a new, shiny SAN or maybe your disks are just about full and you need to shift a bunch of the files off somewhere else.

The first thing that you’re going to need to do is change the paths of the files in SQL. That’s easy enough to do with an ALTER DATABASE statement.

ALTER DATABASE SQLUndercover MODIFY FILE (NAME SQLUndercover_Log, FILENAME = 'F:\SQLLogs\SQLUndercover_Log.ldf'

But that’s going to get very tedious very quickly if you’ve got to do that for a whole lotta databases. So to help out, I thought I’d share a little script that I’ve been using for a while (or a variation on it at least) to make the process far easier and generate all the ALTER statements for you.

Click through for the script as well as a bit of advice around the actual moving of the files.

Comments closed

CHECKDB Matters in the Cloud Too

Published 2020-04-16 by Kevin Feasel

Daniel Janik takes us through an ordeal related to CHECKDB on an Azure Managed Instance:

This is crazy! What now? Open at ticket with MSFT? This seemed the only choice and what was the root cause? Apparently in Azure Managed Instances, Microsoft will check databases for corruption and will take the database offline if detected.

When in this special offline state there’s no way to access the database and Microsoft must be contacted. You can’t set the DB in recovery mode or change it to ONLINE. Microsoft does “contact” someone to notify that the database was taken offline due to corruption but if you work at a larger company this notification may never reach the right people.

Read on to see what Daniel ended up doing and some tips on making the process smoother.

Comments closed

Installing Power BI Management Cmdlets for Powershell

Published 2020-04-16 by Kevin Feasel

Nicky van Vroenhoven takes us through installation of the Power BI Management commands for Powershell:

The first hurdle I encountered was the fact that there was already an older version installed. After some fiddling, trying to uninstall the old modules and going through the fixed described below, I figured the documentation was probably the best start, and it was. 🙂

Nicky documents several issues but was ultimately able to pull it in.

Comments closed

Avoiding the sp_ Prefix in Stored Procedures

Published 2020-04-16 by Kevin Feasel

Randolph West takes an Erik Darling video one step further:

Erik Darling, of Erik Darling Data (blog | Twitter) recently posted a very interesting video (my YouTube playlist is all Erik Darling Data videos and Honest Trailers). In this video he demonstrates an easily repeatable issue about naming weirdness and the CREATE OR ALTER PROCEDURE syntax, with stored procedures starting with sp_. The video is 141 seconds long, so go watch it. I’ll wait.

Cool. As many of my readers know — because you’re intelligent and attractive in equal measure — Microsoft does not want us using sp_ for stored procedure prefixes for general use, because it’s kind of reserved:

Read on for some fun with system-like objects.

Comments closed

The Observer Effect when Troubleshooting

Published 2020-04-16 by Kevin Feasel

Dmitri Korotkevitch reminds us that monitoring has a cost:

It is relatively easy with the tools – you can detect those inefficiencies just by looking at the expensive queries in the system. This is not always the case, however, with other technologies, like with monitoring done by Extended Events. They can stay almost invisible in the system, especially if targets are keeping up and don’t generate waits.

Let me show you an example and create xEvent session that captures queries that were executed in the system. This is very common one, is not it? 

Click through for the example and what happens under heavy load.

Comments closed