The Recording
The Panelists
- Kevin Feasel
- Tracy Boggiano
- Tom Norman
Notes: Questions and Topics
I was a little ill last night when we recorded, so that’s why I made any mistakes I did. It was a 24-hour thing, though, so I’m going to have to come up with a new excuse for why I make so many mistakes.
Listing System Functions
Nadir hit me up via e-mail and wanted to figure out how to list system functions in T-SQL.
Answer: This script will give you the set of system functions available in SQL Server.
SELECT
o.name,
o.type,
o.type_desc,
m.definition
FROM sys.system_sql_modules m
INNER JOIN sys.all_objects o ON m.object_id = o.object_id
WHERE
o.type NOT IN ('P', 'V')
ORDER BY
o.type ASC,
o.name ASC;
GOInterestingly, there are still some functions which don’t show up at all, including metadata functions. As a note, I mentioned @@SPID as a metadata function but that’s not correct—it’s a configuration function.
Preventing Table Changes
Tom dropped the next question on us: he wants to prevent anybody from modifying a particular table, like adding or removing columns. How can we notify or prevent this sort of modification?
Answer: There are a few techniques available. Tom mentioned using SQL Audit and creating a view with schemabinding against that table—that will prevent table modifications so long as the view is shcemabound.
I also mentioned DDL triggers, which you can use to roll back specific operations like modifications to a particular table. You can also use Policy-Based Management.
Tracy calls out preventing SELECT * operations against a table by adding a computed column which divides 1/0 to teach developers not to do that. @srutzky did mention a couple reasons not to do this, though
A Little Bit on Containers
From there, I started to bring up a question that Mala had asked around why you might want to use containers. We talked about using Docker containers for androgogical purposes, though Tom pushed Azure Lab Services, which is another good option. We also covered Docker containers for automated testing, where I think it makes a huge amount of sense.
I mentioned some bits about container insecurity and there are some practices for making these better.
During this, Tom also asked the question, will SQL Server be on Windows 202X, say 5 years from now? My answer is an adamant yes. As long as there is a Windows operating system, I’d expect to see SQL Server on Windows. There are too many features only available on Windows today, and even some new features have are only partially implemented in Linux.
SQL Server Authentication Options
@iconpro5555 asked, what are the different methods for connecting to SQL Server?
Answer: There are two techniques: Active Directory and SQL authentication. Within Active Directory, we can break it out a bit further. On-premises, we have Windows Integrated Authentication. When dealing with Azure Active Directory, you have three ways of connecting: via multi-factor authentication (MFA), by typing in a username and password, or integrated Azure Active Directory.
As a side note, I wish that we had all of those AAD options for on-prem Active Directory.
Apple on ARM, Continued
Tom brought up Apple moving over to ARM processors, especially now that we’ve heard a little bit more. I mentioned rumors that Apple turned away from Intel because the Skylake CPU series was bug-laden garbage. Also, we’re all very happy that AMD is there as a legitimate competitor, and I think that, combined with Apple’s decision, will force Intel to improve.
We talked a little bit about app support, as ARM-based Macs won’t support Boot Camp. But it looks like Microsoft 365 and Adobe Creative Cloud will support it.