An interesting discussion/question in the SQL Community Slack today arose around how to implement blue/green deploys.
If you’re not familiar – blue/green refers to a deployment strategy with at least two hosts of your services, where you host in the green, deploy to the blue, and slowly drain the green to the blue until it becomes the green.
This has consequences in terms of keeping the lights on for both services, potentially rolling back the traffic to the green node (if the blue deployment fails some tests) and identifying things like dead code/data paths.
I was pondering how to answer the question in anything but the most generic way when this youtube video by Kevin Feasel was posted and it’s such a great resource I’m reposting it.
An interesting question asked by @danthesqlman in #sqlhelp (sqlcommunity.slack.com)
Having issues with Unicode in my XML, tried using a try_convert(varchar,fieldname) but not returning NULL. Set it to have a test on my box, and weird results. declare @n nvarchar(10) = N’ניקודות’ select try_convert(varchar(10),@n) This doesn’t return NULL, but ????????? I’m curious what would I be doing wrong, or how can i locate unicode within XML easily
And then when people suggested individual character shredding –
XML documents in a table over 200k rows, 2mb xml each, could take hours to parse 1 character at a time
There were a few suggestions, (my initial crap one was just dumping it to C#), but after a few jokes back and forth about how SQL Server was just returning normal question marks for TRY_CONVERT and how silly that was the idea came up… why not just:
Replace all question marks with something unique? (I suggested a GUID)
Run the conversion and then do a reverse replace, updating the data in place.
For a simple code example…
DECLARE @magic_value UNIQUEIDENTIFIER = NEWID() SELECT TRY_CONVERT ( VARCHAR(100), REPLACE(tar.name,'?',@magic_value) ) FROM target_table AS tar
Any new question marks that exist in the output would be characters that failed the conversion process.
The test ran in ten minutes instead of a few hours… great!
So a friend of mine had a query puzzle – he needed to print some W2 forms but the forms themselves only allows up to 4 groups of a value to be placed in a box or else they’d need to issue two forms.
The pickle is that the source data is freeform csv text, so what can we do on the database side?
I reached for my handy CROSS APPLY, a numbers table, windowing functions and a little modulo arithmetic – its not so bad to return a subgroup within a group in SQL as long as you are willing to lay out additional columns to count your grouping.
Today I was diving into some extremely wide tables, I wanted to take a quick look at things like “How many unique values does this table have in every column?”.
This can be super useful if you have a spreadsheet of results or a schema without effective normalization and you want to determine which rows are the “most unique” – or have high cardinality.
The Github gist is embedded at the bottom of the page, but I will run you through the code in case you want an explanation of how it works
The procedure takes a schema and a table/view name and combines them – I could ask the user to do this for me, but there’s parts where its useful to break them up and I dont want to mess with split logic :p
The procedure is defined as a temporary stored procedure as I didnt want to leave a trace when I disconnect.
I then define the dynamic UNPIVOT statement, which we will feed our dynamic column listing from.
Gather our columns (twice) – once for the COUNT(DISTINCT), and once for the use in the columns we want to UNPIVOT.
Here’s an example of running it against a tsqlt testresult table:
As we can see, the tsqlt testresult table has one class, two messages and results, and unique everything else (so that’s where I will focus.)
I also utilize a @magic_value variable in the code (not shown) which I use to deal with the fact that a NULL value in an UNPIVOT statement wont count as an occurrence. I want to disambiguate from NULL and any particular value that might occur, so using something like -1 or some string NULL would be inappropriate.
I know I have been writing a lot about ADS recently, but this is even bigger than the Notebook announcement.
A Postgres plugin has been announced in the insider release of ADS, and it just works!
If the term Postgres is unfamiliar – PostgreSQL is one of the preeminent open source database solutions and is showing wide adoption due to its quality and of course, price.
For those of you in the Postgres world, you already have probably bounced between a few SQL clients and if you are like me, not been totally impressed with the results.
For those of you who are SQL Server Data professionals and live in the comfortable world of SSMS and ADS, you may not know that a significant part of the world still runs queries on the command line, or is forced to rebuild their own tool to meet their needs in each language de jour.
I have been working on some side projects with Docker, Postgres, and a few other pieces of tech, so this announcement was perfect for me to test it out.
Just like my post on Notebooks, I am going to download the most recent release from the Insider’s build, but I am going to keep the screenshots a little more brief.
Make sure you enable those preview features!
Jump to the extensions section and find the Postgres plugin. (Sidebar button)
I used a local connection to a docker container hosting my Postgres instance and Azure Data Studio connected the first try!
Intellisense works great!
Notebooks instantly inferred my context from the Postgres connection! Sweet!
Even the explain button pulls the query plan 😀
I love this direction for Azure Data Studio and I hope we see more open source databases supported in the future!
Update: This blog post is already out of date due to the hard work of the ADS team! Check my Azure Data Studio Notebook Update for more details of why you can skip my warnings about your internet connection.
I have been waiting for word about the new Notebook functionality in Azure Data Studio, and when I heard it was available in the insider build, I jumped in to take a look.
A Jupyter Notebook is a web application that allows you to host programming languages, run code (often with different programming languages), return results, annotate your data, and importantly, share the source controlled results with your colleagues.
Alright, here’s what I did:
Grab the insider’s build from Github.
Install Azure Data Studio (ADS.)
Create a new Notebook (and have it download some stuff.)
Run some queries!
Keep in mind I am talking about the Insider’s Build as of as of Thursday March 7, 2019 – if you download it in the future changes are likely to have occurred.
If you want to follow along, make SURE you have a solid internet connection available, planes do NOT count.
Keep in mind you may want to disable updates when running the insider version, as it will try to update to the stable channel version. Thanks to Shawn Melton for the tip!
Install the tools by choosing File -> New Notebook.
The install process will warn you this will take awhile to download and configure the Jupyter environment (which includes python and a few other dependencies.)
Every time you create a new notebook, you are going to download the required dependencies from scratch – this is to ensure you have an isolated environment per notebook, but its worth keeping in mind for now.
Go get some coffee, its going to be a bit.
Maybe time for that second cup.
When our new notebook pops up, you should immediately be offered an option for a Code(TSQL) or a Text(markdown) box.