Shop Talk with TriPASS

The Recording

The Panelists

• Kevin Feasel
• Mala Mahadevan
• Tracy Boggiano
• Mike Chrestensen

Notes: Questions and Topics

SQL Server 2022 Now GA

The big news of this week was around SQL Server 2022 now being generally available. We caught up with some of the things Tracy is excited about (mostly on the administrative side) and Mala pointed out Bob Ward’s SQL Server 2022 workshop as a getting started guide.

Conference Wrap-Up

We got three different perspectives on two conferences in this section. Mala attended PASS Summit virtually, whereas Tracy was there in person. Both of them enjoyed the conference and it sounds like it was a lot of fun. TriPASS alumnus Tom Norman was also there so hopefully you had a chance to see him.

Meanwhile, I was in Orlando for Live! 360 so I shared some thoughts about that.

TriPASS Elections Now Open

We have three seats on the TriPASS board up for election this November: President (me), VP of Marketing (Rick Pack), and Treasurer (Mala). There’s only one candidate for each seat, so assuming a rash of no confidence hits TriPASS members, we’ll have our board set for another year.

Voting is open and runs through the first Thursday in December. Terms will begin in February of 2023 and run for 2 years. If you are a TriPASS member, you will have received an e-mail providing instructions on how to vote. If you aren’t a TriPASS member, sign up at Meetup. I mean, it’s free, so what do you have to lose?

TriPASS Survey Results

Over the past month or so, we have been canvassing TriPASS members to fill out our semi-annual survey, which drives the direction of the organization. You’ve spoken and now you’ll have to deal with the fallout, getting what you deserve.

A quick summary of the survey results is as follows:

• A large majority of people want a return to hybrid user group meetings, though nobody had a place they could offer up. We’ll start canvassing places to see who’s willing to host us and aim to return back to hybrid meetings in spring of 2023.
• Almost everybody who responded wants a SQL Saturday Raleigh 2023. The board still needs to meet to determine the feasibility of it but we’ll try to make it happen. Expect a call for volunteers assuming the basic groundwork is there.

TriPASS Call for Speakers

With the imminent(?) return to hybrid events, we’ll also want some in-person speakers. Our TriPASS call for speakers is officially open, so if you’re interested in presenting at a future TriPASS meeting, submit a session or three. We will give preference to people who can meet with us in person, though there will be slots available for remote speakers as well.

Thank You

As we round in on Thanksgiving, I wanted to give out a few thanks.

• Thank you to Mala, Mike, and Tracy, who keep Shop Talk from being an hour of me talking about me. They limit it to a much more appropriate 55 minutes of me talking about me.
• Thank you to the regulars who drop in and ask questions, try to derail my train of thought (usually with great success), and make the TriPASS water cooler a better place to be.
• Thank you to everyone who has asked a question. As a quick reminder, if you do have any questions you would like us to answer on the air, you can always e-mail us. The address is shoptalk and the domain is tripass.org. Throw a little at symbol in there and you’re good to go.

The Recording

The Panelists

• Kevin Feasel
• Mala Mahadevan
• Mike Chrestensen

Notes: Questions and Topics

Upcoming TriPASS Elections

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.

TriPASS Survey

If you are an active TriPASS member, this is your last chance to fill out our semi-annual survey. It will be open for a few more weeks and this helps shape the next two years for the organization.

Data Platform Conferences

The main theme of tonight’s show was around data platform conferences, as both PASS Summit and Live! 360 are coming up next week. We covered a few topics:

• How specific should data platform conferences be?
• How difficult it can be for high-end people to learn much at conferences. Alternatively, why are there typically so few 300+ level sessions?
• Some of the background on session selection and trying to balance competing needs.
• How many times can Kevin say the word “ecumenical” on a broadcast?

The Recording

The Panelists

• Kevin Feasel
• Mala Mahadevan
• Mike Chrestensen

Notes: Questions and Topics

Upcoming TriPASS Elections

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.

TriPASS Survey

If you are an active TriPASS member, please fill out our semi-annual survey. It will be open for a few more weeks and this helps shape the next two years for the organization.

SQL Saturday Richmond Recap

The first thing we talked about was a quick recap of SQL Saturday Richmond, the first SQL Saturday event in driving distance since 2020. It was a good time, though probably about 40% or so of normal turnout. Still, it was nice to see some regional folks at the event.

Keeping Up to Date with T-SQL

One of the themes from the Richmond recap was around keeping up to date with T-SQL. This started with a discussion of STRING_AGG() but I extended to include a variety of functions and functionality that have been introduced since SQL Server 2012 with the express intention of making life easier for people maintaining existing code. My quick survey is as follows:

SQL Server 2012

• Updated window functions
• CONCAT()
• THROW()
• TRY_CAST() and TRY_CONVERT(). Also TRY_PARSE() though that’s a lot slower.

SQL Server 2016

• CREATE OR ALTER
• DROP IF EXISTS
• TRUNCATE TABLE WITH(PARTITIONS)
• STRING_SPLIT()
• SESSION_CONTEXT (thanks to Solomon for bringing that one up)

SQL Server 2019

• APPROX_COUNT_DISTINCT()

SQL Server 2022

• GREATEST() / LEAST()
• STRING_SPLIT() (again)
• DATE_BUCKET() and DATE_TRUNC()
• GENERATE_SERIES()
• APPROX_PERCENTILE_CONT() and APPROX_PERCENTILE_DISC()
• Defining a window for a window function

From the standpoint of maintaining legacy SQL, SQL Server 2022 is shaping up to be one of the best releases in a while.

fn_my_permissions()

Mike needed to sort out permissions for a user and I pointed to a blog post I had written a while back on fn_my_permissions(). This only works for the current user (or whoever you are currently impersonating if you’re a sysadmin) but is the single most comprehensive look at permissions in SQL Server.

The Recording

The Panelists

• Kevin Feasel
• Mala Mahadevan

Notes: Questions and Topics

Upcoming TriPASS Elections

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.

The Cost of 2005

One quick topic I came up with impromptu came from something Mala mentioned around SQL Server 2005. The question was, how much of a raise would you need to work solely in an environment where the only database platform was SQL Server 2005? In other words, no upgrades, no side jobs to keep current, nothing except SQL Server 2005. Mala and I were rather boring about it and both of us said “nope, can’t pay me enough.”

TriPASS Survey

If you are an active TriPASS member, please fill out our semi-annual survey. It will be open for a few more weeks and this helps shape the next two years for the organization.

A Righteous Rant

I spent a lot of time praising a rant by Sean Gallardy around the concept of SQL Server vulnerabilities. Such vulnerabilities exist but if your method of exploit is brute force password attack, that’s not an exploit. Or if you do fall prey to it, that’s a “you” problem, not a SQL Server problem.

ANY and ALL

We also covered a couple bits of SQL syntax: ANY/SOME and ALL.

The Recording

The Panelists

• Kevin Feasel
• Mala Mahadevan
• Mike Chrestensen

Notes: Questions and Topics

Upcoming TriPASS Elections

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.

Get Well, Hugo

Mala brought up the next topic: we hope Hugo Kornelis, who has been blogging about his health problems, makes a full recovery.

SQL Server 2022 RC1

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.

The Recording

The Panelists

• Kevin Feasel
• Mala Mahadevan
• Mike Chrestensen

Notes: Questions and Topics

TriPASS Elections Forthcoming

This is an early warning for all TriPASS members that elections will be coming up. We’ll keep you posted with all of the details.

Influential People

Mala and Tracy ended up on the Redgate 100 most influential database community people list. Congratulations to both of them.

Tibbling and Hungarian Notation

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.

The Recording

The Panelists

• Kevin Feasel
• Mike Chrestensen

Notes: Questions and Topics

SQL Server 2022 RC0

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 Recording

The Panelists

• Kevin Feasel
• Tracy Boggiano
• Mike Chrestensen

Notes: Questions and Topics

Storing DMVs and Admin Data in Tables

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.

Generating Text with NimbleText

Mike brought up a tool that he found useful: NimbleText. It looks to be quite useful for text manipulation. Mike used the free version, though pricing looks quite reasonable.

The Recording

The Panelists

• Kevin Feasel
• Mala Mahadevan
• Tracy Boggiano
• Mike Chrestensen

Notes: Questions and Topics

SQL Server 2022 CTP 2.1

A big part of tonight’s episode was around the announcement of SQL Server 2022 CTP 2.1. I referenced a post from Bob Ward as well as the official documentation.

We also spent a fair bit of time talking about new syntax, including IS NOT DISTINCT FROM and DATETRUNC.

Don’t FHIR the Reaper

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.

The Recording

The Panelists

• Kevin Feasel
• Mala Mahadevan

Notes: Questions and Topics

Partial Containment

We start off the night with a question from friend of the show Mark Gordon, whose team read a note on partially contained databases and was a bit confused:

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.