The Recording
The Panelists
- Kevin Feasel
- Mala Mahadevan
- Tracy Boggiano
- Tom Norman
Notes: Questions and Topics
PASS Summit Speakers
I wanted to call out that there are nine separate North Carolinian speakers for PASS Summit this year.
On Shop Talk, Tracy Boggiano, Tom Norman, and I will all be speaking at PASS Summit. Other speakers include friends of the show Chris Voss and Jared Poche.
Angela Henry heads up the group from the Triad area.
Melissa Coates (whose pre-con is on a different day from mine, so you can see both of ours!), Brad Llewellyn, and Ben DeBow round things out from the Charlotte area.
Dealing with XML and JSON
@iconpro555555 asked a question at the very end of last week’s Shop Talk around handling structured data—that is, XML and JSON—in SQL Server.
SQL Server has a long history of dealing with XML data, including an XML data type, XML-specific indexes, and XPath query notation. It’s notorious among DBAs as “Something we don’t want,” and Tracy points out the biggest problem: once you start to get a decent amount of data, everything falls apart.
In SQL Server 2016, we got JSON support through the OPENJSON, FOR JSON, and JSONVALUE commands. There is no separate JSON datatype or direct indexing, though if you want to, you could create a persistent computed column using JSONVALUE to extract a specific value from the JSON and index that persistent computed column.
I don’t necessarily mind XML or JSON data in the database and it can be the best solution. But one pre-condition for it being the best solution is that you shouldn’t be shredding that data regularly. If you do, build out a proper, normalized data model and take advantage of what relational databases do best.
Storing and Using Secrets in Powershell
The next topic was around secrets in Powershell. This was a question from the very beginning of Shop Talk’s history, but because Rob Sewell recently blogged about this, I wanted to bring it up.
A Rant on Database Diagramming Tools
My official Rant of the Night was around how weak the database diagramming tool is in SQL Server Management Studio. The thing barely works at all, requires you to create database objects (meaning that developers with read-only permissions can’t even create these diagrams for themselves), provides only a modicum of relevant information, and are a royal pain to disentangle if you have more than just a couple of tables.
Tom defended the honor of SSMS database diagrams, while folks in chat recommended tools like DBeaver and DBDiagram. I brought up Mermaid, as I’ve just started getting into it for presentations and it allows for code-centric modeling, meaning that you write Python code to represent your model. I could see that tying in well with queries against system tables to get tables and columns, foreign keys, and the like. I also mentioned sqlDBm in an off-hand fashion because I couldn’t remember the name of it. I’ve used it before to reasonably good effect and the free version works pretty well if you have one fairly stable database. Visio database reverse engineering is excellent as well when your goal is to understand an existing, stable design.