We have three seats on the TriPASS board up for election this November: President (me), VP of Marketing (Rick Pack), and Treasurer (Mala). Nominations are open, so if you are a TriPASS member on the Meetup, reach out to me if you are interested in running for one of the board seats. Rick, Mala, and I are running but if you want to throw your hat into the ring, you are welcome to do so.
Voting will start on the 3rd Thursday in November and run through the first Thursday in December. Terms will begin in February of 2023 and run for 2 years.
SQL Server 2022 RC1 was quietly released last week. It doesn’t look like there are new features, just bugfixes. We’re getting close to RTM.
Non-Surrogate Surrogate Keys
Our next major topic was around people who use surrogate keys in a way which imputes business value. For example, values greater than 0 in a table represent user-defined information and values less than 0 represent system-defined information. We talked about why this might happen, what it can do to query development, and some alternatives.
We went a bit further down into the topic of tibbling than I had expected but I think it was an interesting discussion. If you’re not familiar with the term, “tibbling” is prefixing tables with “tbl” in cases like tblUser or tblDocument. It’s a degenerate form of systems Hungarian notation.
We hit on two key things. First, is it as bad to use PK_ or CK_ or IX_ as prefixes for constraints or indexes as it is to use tbl for tables or vw for views? My thought on the matter is, no and the reason is information. In general, we don’t need to distinguish between tables and views in queries—this is, in fact, one of the best things about views: we can query the as though they were tables. Furthermore, the tooling we have in SQL Server makes it really easy to see the list of tables and the list of views, meaning that a tbl prefix doesn’t provide us any valuable information we could not easily get.
By contrast, the specific type of key constraint is a little harder to get. Yes, you could drill into constraints on a table in SSMS or Azure Data Studio, or you could query sys.key_constraints, sys.check_constraints and sys.default_constraints to find out about specific constraint types. Similarly, you can query sys.indexes to get details on whether that index is unique or not, columnstore or B-tree, clustered or nonclustered, etc. But those pieces of information are more hidden away, so there’s a benefit to including them in the name.
The other thing I tried to hit on was apps vs systems Hungarian Notation. The quick version is that systems Hungarian notation specifies the actual data type of a variable, so we might have iMyValue, where the i represents 32-bit integer. We might also have lMyValue and sMyValue for long and short types, respectively. The problem with systems Hungarian notation is that data types change over time, so either you need to rename the variable everywhere (and hope you caught all references in other files, other projects, etc.) or just live with iMyValue actually being a long now that you broke 2 billion values.
By contrast, apps Hungarian notation delineates uses of data types, so you might have a safe string sMyString and an unsafe string usMyString. The difference here is that the “safe” string has gone through regular expression checks, validation mechanisms, etc. and is known to be good. By contrast, an “unsafe” string may have come from a user’s console or an external API call. In this case, we don’t care what the data type is but rather a special characteristic of the variable, something we could not easily infer from IntelliSense or going back to the variable declaration.
Watch Parties
The last topic was a quick tip. My team has recently been watching technical videos together. We’re all remote employees so I’ve landed on watchparty.me for these trainings. This is a service which allows you to keep YouTube, et al, videos in sync for several people and also includes chatroom and voice chat functionality. We don’t really use the voice chat functionality at all, sticking to the chatroom instead. It’s a good way of watching a technical lecture together and you generally get more out of it than asking everybody to watch the video beforehand and discussing it.
Our first topic of the night is SQL Server 2022. RC0 was released a few days before and we talked a bit about what that means for the SQL Server timeline. My wild guess with zero insider information is that it probably releases for Ignite or PASS Summit—that’s what they’ve done with several recent versions of SQL Server and there’s enough time for RC1 and an RC2 if needed before RTM.
Data Virtualization in SQL Server 2022
The main topic of the night was a bit of a soliloquy on data virtualization in SQL Server 2022. I got into the history of PolyBase in SQL Server, how it has evolved through the years, and some of the changes in SQL Server 2022. I also ranted a bit about Windows authentication bugs and long-standing issues that I hoped would have been fixed but no dice.
LastPass Breach
LastPass announced a breach in which attackers were able to steal source code and technical documents. They didn’t get access to do anything to encrypted vaults, though what they did get is bad enough.
The main topic for tonight started with a blog post Tracy wrote to store BlitzIndex results to a table. We expanded this to discuss a variety of other topics, like what else we might store in tables: wait stats, sp_whoisactive results, and other things which are transient. We also talked about cleanup strategies, the folly of not having a cleanup strategy, and long-term data retention. There was a bit of meandering and ranting here, as you’d expect.
Mike talked about some pain that some of his colleagues have been having with Azure Data Factory and data cleanup / obfuscation via the FHIR service. It doesn’t sound like there’s a great answer to the problems his colleague ran into, as they had similar issues with two other providers.
When contained databases are enabled, database users with the ALTER ANY USER permission, such as members of the db_owner and db_accessadmin database roles, can grant access to databases and by doing so, grant access to the instance of SQL Server. This means that control over access to the server is no longer limited to members of the sysadmin and securityadmin fixed server role, and logins with the server level CONTROL SERVER and ALTER ANY LOGIN permission. Before allowing contained databases, you should understand the risks associated with contained databases. For more information, see Security Best Practices with Contained Databases.
They interpreted this as granting rights to the partially contained database user to access other databases on the instance. Instead, what it’s saying is that any partially contained user must have the ability to connect to the instance in order to access the partially contained database. But they cannot connect to any other databases on the instance.
Thoughts on MySQL
Mala has been doing some work with MySQL lately. For the sake of being the bad cop, I made fun of MySQL, though that is in good fun—I like MySQL for what it is but it’s probably number 4 on my list of relational databases, well behind SQL Server, PostgreSQL, and Oracle. Anyhow, Mala is impressed with their diagram creation tool, which is way better than what SQL Server has built in.
Our first topic of the night was a quick discussion about SQL Server 2022, including some of the features forthcoming and how to try it out. If you haven’t tried it yet, the easiest approach at the moment would be to grab the 2022 CTP container. But if you want to try out the Windows version on a VM, that’s another good approach.
CVE-2022-29143
The next topic was around CVE-2022-29143, which affects all versions of SQL Server since 2014 (but probably not 2012?). As of the time of recording, there wasn’t much information available on this issue, as it was not an issue in the wild and sounds like the problem was discovered by Microsoft themselves or a customer and not a third-party security researcher. That means no cool websites, exciting names, fancy logos, or mascots.
Still, patch your servers.
SSISDB in Full Recovery Mode?
Our third topic came from an e-mail by friend of the show Mark Gordon, who ran into a SQL Server instance whose SSISDB was in Full recovery mode while pretty much every other database was in Simple. In this segment, we talked a bit about why having SSISDB in Full recovery mode may (or more likely, will not) matter. The short version: it’s probably not too important as long as you take regular database backups. But if you have it in Full recovery mode, that certainly doesn’t harm things.
Indexed Views in SQL Server
Our final topic came from Mike, who wasn’t able to make it to the show. He asked about materialized views, a concept in Oracle, and what the SQL Server equivalent is. The closest equivalent is indexed views, so I talked about them. One thing I just glanced upon but didn’t go into detail over was that indexed views are indeed different from Oracle (and PostgreSQL)’s concept of materialized views. SQL Server’s indexed views are truer to the concept of views-as-tables, in the sense that any update to the underlying tables also necessarily updates the indexed view. There is no concept of deferring updates or running them on demand. This means that you won’t get stale data from an indexed view but also means that they can be the cause of performance issues when the underlying tables are sufficiently busy.
Our first major topic of the night came from an Aaron Bertrand post covering expressive T-SQL queries. This led me to a discussion of language generations. This post does a good job of describing the generations though I disagree strongly with the idea that Perl, Python, Ruby, etc. are 4th generation languages—they are third generation languages. The most popular 4th gen language is either SQL or the Unix shell but it turns out that there were a few more popular languages on the list that I didn’t see during the show.
For a really quick synopsis:
The first generation of programming language is machine code.
The second generation is assembly, which acts as an abstraction of machine code.
The third generation is structured, imperative programming languages. C and its family are some of the big boys here but almost every general-purpose programming language fits in this space.
The fourth generation covers declarative languages: I explain to you what I need and you determine how to get it. I don’t tell the database engine how to get the data, just that I need data meeting certain criteria.
The fifth generation of programming languages were intended for knowledge systems: I explain some facts to you and you draw inferences from them. In practice, 5GL never really took off, though we do see use of it in academia and AI research.
Fun with Indexing
Mike had a couple of questions which ate up the rest of the show.
If I have no filters on a query and select an indexed column vs. not, is there a performance difference? In other words, if I have EmployeeID INT which has a non-clustered index and SomeOtherID INT which does not have a non-clustered index, is there a performance difference if I run SELECT EmployeeID FROM Table versus SELECT SomeOtherID FROM Table?
If an index has multiple columns, how do you best make use of the index in terms of writing a query?
Mike had a data problem. Specifically, he had “parent” and “child” records in the same table. He knew which was a parent and which a child and he also knew that the children always came immediately after their parent alphabetically. The question is, how could he associate child records with their parents?
One easy answer to this is a concept called Last Observation Carried Forward (LOCF). LOCF is possible…in Azure SQL Edge. It’s now available in SQL Server 2022 but when we recorded this, Build had not yet happened and therefore we didn’t know anything about SQL Server 2022.
Fortunately, for those of you who didn’t immediately jump and put a first CTP into production, there are still solutions. Itzik Ben-Gan, naturally, has a solution. Specifically, he has two solutions but one of them was way more complex than I wanted to describe on the air.
Saying Goodbye to User Groups
Mala made the point that quite a few user groups have gone by the wayside these past couple of years. As things (slowly) begin to re-open, we’re seeing some user groups return from dormancy—for example, TriNUG, our local .NET user group, has started back up and you should stop on by one of these days. But for every TriNUG, there seem to be several groups whose leaders have given up the ghost. We talk a bit about why that can be.
New Doesn’t Mean Better
Our final topic of the night turned out to be an extended ranty discussion based on a really good blog post by Andy Leonard. The gist of Andy’s post is that novelty is not, strictly speaking, a virtue. A few of the highlights of this include:
The benefits of old code
Chesterton’s Fence, aka why you shouldn’t make rash decisions without sufficient information
Ugly code is usually ugly for a reason and if you don’t understand that reason, your beautiful new code will get real ugly
On the other side, technological decay does happen and things become obsolete
Still, anybody pushing new technology is trying to cross the chasm and drag you along with it, whether it makes sense or not
Today’s episode of Shop Talk didn’t really have a theme other than “watch Kevin fight allergies and rant about various things.” We talked about Windows 11, IoT, data privacy, the joys of The Internet Archive, and more.