Our first topic of the night hit on two upcoming conferences. First is SQLbits, which is coming up March 8-12. Registration is open right now and there are in-person and virtual options available. Note that this is a paid conference. Tracy will be there.
Second, DevIntersection, which includes the SQL Server & Azure SQL Conference, has a $100 discount if you use the code AZUREDC. This is also a paid conference and is in-person. I will be there.
Goodbye Big Data Clusters and PolyBase Scale-Out
The entirety of today’s episode was a Viking funeral for Big Data Clusters and pre-2019 PolyBase, based off of this Microsoft post. In short, Big Data Clusters are dead. PolyBase is not dead. We also talked a bit about making bets and I stretched that metaphor like riding 2-7 off-suit to a full house.
Our first topic of the night hit on two upcoming conferences. First is SQLbits, which is coming up March 8-12. Registration is open right now and there are in-person and virtual options available. Note that this is a paid conference. Tracy will be there.
Second, DevIntersection, which includes the SQL Server & Azure SQL Conference, has a $100 discount if you use the code AZUREDC. This is also a paid conference and is in-person. I will be there.
Rant One: Dimensional Modeling is Dead
Mala wound me up and I took it out on this poorly-reasoned article. The article includes a woeful misunderstanding of the purpose of normalization, makes unsupportable claims about storage and compute prices, claims that dimensional models are too hard for business users to understand while recommending that we use source systems for queries instead, and misunderstands the whole point of data lakes. Data lakes don’t replace data warehouses; they augment and help solve a different kind of problem.
In short, the gist of the article is, “Yes! You, too, can pay more money to be stupid!”
Non-Rant One: $FAMOUS_COMPANY
The above article is exactly what this article parodies. No rant on this one, only raves.
Rant Two: the Value of Normalization
Mala and Mike got me wound up a second time on the topic of normalization. We talked about this argument, that normalization isn’t used and isn’t useful. I tried to be a little less ranty here; whether or not I succeeded, you be the judge.
First, let me lay something out: normalization is a logical consequence of set theory and relational design. It isn’t a set of rules you bolt on afterward; it’s an understanding of the properties which underlie the fields of mathematics which underpin relational database technologies. It’s fundamentally about getting your data model correct. Now, a few points in increasing order of rantiness:
Normalization as formal logic is under-used in industry, so I agree to that extent with the tweeted argument.
It is also difficult to change this. For example, try getting a talk on normalization accepted. I’ve tried; it’s hard.
Where I start disagreeing is with the notion that “nobody” does normalization. I disagree with it in literal terms—I use it; therefore, that statement is not literally true. I also disagree with it in practical terms—formal application of the rules of normalization are under-used but informal application is pretty common. It could be used to better effect if people were trained in its mechanisms.
That’s where I start blaming academia: you’re not doing a good job of teaching normalization. People don’t understand the concepts because they have relatively few good examples. Therefore, we stop teaching it? Is that actually the right answer? Because the consequence is that the people you’re training end up with crappier data models as a result.
Our first topic of the night hit on two upcoming conferences. First is SQLbits, which is coming up March 8-12. Registration is open right now and there are in-person and virtual options available. Note that this is a paid conference. Tracy will be there.
Second, DevIntersection, which includes the SQL Server & Azure SQL Conference, has a $100 discount if you use the code AZUREDC. This is also a paid conference and is in-person. I will be there.
Columnstore and Rowstore
Mike came up with the theme for tonight’s episode, which is a discussion of columnstore databases. The context of this comes from a statement in 2013 that columnstore databases are 10-50 times faster than relational databases and that traditional relational databases would disappear in favor of columnstore databases.
In all fairness to the person who made the statement, columnstore databases are considerably faster for analytical queries than traditional rowstore indexing. What happened in reality, though, is that traditional relational databases gained columnstore capabilities, such as clustered columnstore indexes in SQL Server. As a result, we saw another instance of a new technology challenging the existing relational model and leading to a synthesis of the new technology into the existing paradigm.
As a quick side note, no matter how transformational or interesting your technology, it’s a bad bet to say that relational databases are going away. The most generous I can be here is saying that if you hang your hat on “traditional” you might win the technical point—technologies like SQL Server, Oracle, and PostgreSQL changed and incorporated columnstore capabilities. On the broader point, we’re nearly a decade later and relational databases are 7 of the top 10 on DB-Engines.
Our first topic of the night hit on SQLbits, which is coming up March 8-12. Registration is open right now and there are in-person and virtual options available. Note that this is a paid conference.
Becoming a Data Analyst
Our main topic of the night came in from a chat question:
I want to become a data analyst. I’m currently working in a profession not related to tech. Is this a pipe dream?
This is a great question and we spend a lot of time on (hopefully!) practical tips on how to get into the world of data analysis. Mike, Mala, and I come at this from different angles and one of the things I reiterate a few times is that data work is a career people move into, not start in. Quite often, somebody starts in a different field, begins to do data analysis within that area, and eventually decides to specialize in it when they find out how fun it is.
The main topic of this episode was around code reviews. We talked about some of the pain points when doing code reviews for database changes, how they differ from application code reviews, and also ended up going down some cryptographical rabbit holes.
Board elections for the Triangle Area SQL Server Users Group have wrapped up and we are happy to have Tracy Boggiano and Mike Chrestensen back for their two-year terms. Thank you to everybody who voted.
Technical Debt
Our big topic was around technical debt, including a description of what it is, how you can minimize it, and even how much it matters. I decided to play Devil’s Advocate a bit, as developers tend to be on the “fix all the technical debt!” train, and somebody’s got to make it interesting…
Mike brought up Martin Fowler’s tech debt quadrant, which gives one helpful way of distinguishing classes of technical debt. I fumbled around with some thoughts and will need to write a fuller blog post to get my ideas out. Jeff Moden also made an appearance and talked about his situation, which sounds to me like a really difficult one to be in (to put it nicely).
Board elections for the Triangle Area SQL Server Users Group are currently running. Get in touch with me via Meetup if you are eligible to vote (i.e., are a member of the Meetup) and have a vote up or down on the slate of candidates.
Reflections on the Year
This episode was an early “reflect on the last year and think about the next” type of episode because we didn’t have any content I was traveling. Thank you to everybody who is a part of TriPASS; we appreciate your existence.
Board elections for the Triangle Area SQL Server Users Group are coming up soon. We have two candidates running for re-election: Mike for the Vice President of Membership and Tracy for Vice President of Programs. If you are a TriPASS member in good standing, you can also run for one of the leadership positions; get in touch with me via Meetup if interested.
Conference Thoughts
All four of us shared our thoughts on the recent PASS Summit. Overall, I think it was a successful first attempt, but it did have some rough edges. From there, I spoke a bit about the Live! 360 conference last week, where it was good to see some people for the first time in a couple of years.
NULL, Sargability, and Performance
The back half of the episode mostly dealt with performance tuning considerations, particularly when dealing with NULL. I started off hinting that I’d disagree with a good bit, but in the end, most of the advice was reasonable.
We started out with some thoughts about the first day of PASS Summit, which is now almost over because I’m bad at uploading these videos in a timely fashion. I moderated Steph Locke’s pre-con on data science, which was really good. Tracy saw Rob Sewell’s pre-con on ARM templates and Bicep, which was also really good.
Special Guest Interrogation
We invited Chris Wood on to ask a whole bunch of questions about PolyBase. This is probably a good reason to point at PolyBase Revealed, the world’s best book on PolyBase.
We dove into several topics, including parts of how PolyBase works, how you can analyze problems, and where you can find details on why performance is slow.
Registration is open for PASS Data Community Summit. This includes two days of pre-cons as well as three days of conference sessions. The pre-cons are priced at $200 apiece and the main conference is free, so check it out.
Query Editors: SSMS vs Azure Data Studio vs Whatever
Our first topic of the night was based on two separate questions. First, Mike (who sadly could not make it) asked what tools people use for query editing. He uses a tool called Aqua Data Studio, which looks fine but I’d guess doesn’t have a huge uptake in the SQL Server community. Almost everybody uses SQL Server Management Studio regularly, and I was the only person who also use Azure Data Studio regularly, though this was no scientific survey.
We talked a bit about how VS Code has overwhelmed Visual Studio, yet the SQL Server analog has had so much trouble getting adoption. I threw out a couple conjectures on the topic. First, there’s a lot of functionality in SSMS that isn’t in Azure Data Studio, and sometimes there’s stuff in ADS which is garbage compared to SSMS. Execution plans are the best example of this: as they are in Azure Data Studio today, execution plans are significantly worse than what you can get in SSMS, much less a tool like SolarWinds Plan Explorer. Considering that Azure Data Studio is intended for developers, that’s a massive pain point.
The other conjecture I threw out is that DBAs and data platform developers tend to be much more reticent to change than application developers, including when it comes to tooling. Considering that we still have people yearning for the good ol’ days of SQL Server Enterprise Manager and Query Analyzer, there’s an uphill battle here for the Azure Data Studio team.
Also of interest is that we have a bunch of people who use Notepad++ as their editors for pretty much everything.
We had a couple questions around stored procedures that we covered briefly. First, how do you convince developers and managers that stored procedures are useful (at least in SQL Server; for other database platforms, this advice may vary)? I’ve found that the most useful argument is to note that stored procedures act as interfaces between the code and database, allowing you to do a lot of interesting work without needing to re-compile and re-deploy code. Developers understand and (typically) like interfaces, so that helps it click for them. It also allows you to isolate the SQL code away from other languages, which lets multiple people work on separate problems with less risk of merge conflicts. Solomon also brought up important points like the ability for enhanced security via module signing.
The other question concerned unit testing stored procedures. Some people are big on that, like Mala, who uses tSQLt a lot. Solomon prefers DbFit as an integration test framework. I like the concept of tSQLt a lot, but the big issue is that the things I most want to test are things which are really difficult to test (like triggers, schema modifications, complex dynamic SQL operations, etc.) because they have dependencies on actual objects.