SDU Tools: Using T-SQL to find operating system versions, locales, SKUs, and configuration

When I'm writing utility scripts for SQL Server, I often need to make decisions or report on, details of the operating system that I'm running on. Unfortunately, SQL Server doesn't have views that return this sort of information.

In our free SDU Tools for developers and DBAs, we added a series of views to help.

OperatingSystemConfiguration has details of OperatingSystemRelease, OperatingSystemName, ServicePackLevel, OperatingSystemSKU, OperatingSystemSKUName, and LanguageName.

OperatingSystemVersions has details of OS_Family, OS_Version, and OS_Name.

Ever wondered what locale 1033 means? OperatingSystemLocales has details of OS_Family, LocaleID, and LanguageName.

OperatingSystemSKUs has details of OS_Family, SKU, and SKU_Name.

We periodically check the list of these within Windows and keep it up to date.

You can see two of the views in the image above and also see the views in this video:

To become an SDU Insider and to get our free tools and eBooks, please just visit here:

http://sdutools.sqldownunder.com

 

Another new online course: Protecting SQL Server Data with Encryption

We all see news articles talking about how company after company exposed private details of their clients, customers, and sometimes, even staff. In so many cases, a reasonable approach to encryption would have avoided these issues. Time and again though, I see people doing dodgy work on protecting their data; some even trying to roll their own protection.

Don't do this!

Encryption is one of the most important yet least understood technologies in SQL Server. It's improved so much over the years and 2019 makes it even better.

If you are a DBA or a database developer and you don't know your way around the encryption options in SQL Server, it's time to learn.

We've now made it easy. Take our new online and on-demand course: Protecting SQL Server Data with Encryption.

In no time, you'll be discussing and implementing encryption like a pro. You'll get a refresher on encryption terminology and concepts, then drill in keys, certificates, cell-level encryption, Transparent Database Encryption (TDE), extensible key management (EKM), and Always Encrypted (in 2016) and with secure enclaves (in SQL Server 2019).

And if you get into it by May 17th, you'll be able to use coupon code ENCRYPTNOW to get another 25% off the brand new course.

And like all our courses, we have video instruction, hands-on-labs with downloadable resources, quizzes to check your learning, and a certificate at on completion.

You'll find it, and our other courses here:

https://training.sqldownunder.com

 

 

 

T-SQL 101: #14 Using two part names for SQL Server tables and other objects

If you look carefully at the following simple query:

you'll notice that I didn't just say FROM Cinemas, I said FROM dbo.Cinemas. The "dbo." part is the name of the schema. I talked about schemas in an earlier post. And all through these T-SQL 101 blog posts, I keep mentioning that you should always use the schema name as well as the object name.

By why?

Image by Ken Treloar

The first reason is that whenever you write T-SQL scripts of any type, you need to be precise. When you say just Cinemas, SQL Server has to work out which Cinemas table you mean.

Now you might think that you only have one. But SQL Server doesn't know that when it starts to run your query, and it has to go to look to see if there are others.

Every user has a Default Schema associated with them. When you just say Cinemas, SQL Server first looks to see if there is a Cinemas table (or view) in your default schema. If it doesn't find one, it then looks in the dbo schema. That's why it seems to find it just fine when you simply say Cinemas.

Small as it is, why have it doing this extra work?

A worse situation is if you're writing a query to create the table. If you just say Cinemas, the table would be created in the default schema of the person running the script, not necessarily in dbo. That's not reliable query writing.

Stored Procedures and other objects too

Exactly the same issues relate to stored procedures. If you say:

EXEC SomeStoredProcedure;

How does SQL Server know if that's dbo.SomeStoredProcedure or Sales.SomeStoredProcedure ? Sales might be your default schema.

And the same issues arise when you create stored procedures. You want to write scripts that reliably create them where you want them.

But wait, there's more

A further complication is around query plan caching. If SQL Server caches a query plan for accessing the dbo.Cinemas table (once it's found it), and you run a query that's for just the Cinemas table, it doesn't already know if it can use the same query plan, until it works out which object you're talking about.

In many places in the T-SQL documentation, you'll find you're advised to use two-part object names. They're not joking. For these and other reasons, please just get used to doing this.

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

SQL: Do Indexed Views really require Enterprise Edition of SQL Server?

According to Wikipedia, Betteridge's law of headlines is an adage that states: "Any headline that ends in a question mark can be answered by the word no." In this case, they're correct because the answer is no. But as with most things in this industry, there are subtle issues with that answer.

In SQL Server, a view is just a SELECT statement that's been given a name. That's pretty much it. No data is stored, and every time you access the view, the query optimizer expands the view into the surrounding query. By the time you get to an execution plan, the view is nowhere to be found.

Let's see an example:

The WideWorldImporters database has a view called Website.Customers. I've selected from it and then asked for an estimated execution plan. If you look all through the plan below and even search the XML for the plan, you'll find that the name Website.Customers isn't part of the plan. That's because SQL Server takes the T-SQL code from the view and pushes it back (i.e. inlines it) into the surrounding query, which is then optimized.

So what are indexed views?

An indexed view is a curious beast. It's created by adding a clustered index to the view. That makes it actually store the data. There are lots of rules and limitations surrounding indexed views, and we'll talk about them another day, but the important concept for now is that they hold the data, it's updated automatically as the underlying data changes, and the view itself can supply the data in a query.

In the AdventureWorks database, there is a view called Person.vStateProvinceCountryRegion. Note that it has an index:

Indexed Views and Query Plans

Note the following query plan:

You can see that the query retrieved the data from the view. It did not expand the view. If we'd like it expanded, we can request that:

So what about Enterprise Edition?

The query optimizer considers indexed views in Enterprise Edition, Evaluation Edition, and Developer Edition. In other editions, the views are expanded before optimization. So there is a perception that Standard Edition and Express Edition cannot use indexed views.

However, if you always specify a NOEXPAND hint in your queries, you can use indexed views just the same in both Standard Edition and Express Edition.

And it doesn't cause any issue on any edition. It still works as expected on Enterprise Edition too.

SQL: Fixing Locking and Blocking Issues in SQL Server – Part 1

I get lots of requests to help customers with their SQL Server systems. Many, many requests start with customers asking me if I can help them fix their blocking issues. Most customers aren't actually aware of true blocking issues. Invariably what they mean, is that they've started to have deadlocks and things are blowing up.

Awesome image by Jens Johnsson
Awesome image by Jens Johnsson

Is blocking really the issue?

I always start by working out if they really have a blocking issue. When I see performance problems, after eliminating obvious hardware and configuration issues, I'd say the issues fall into these buckets:

  • Application Design 50%
  • Indexing 35%
  • Blocking/concurrency 10%
  • Other tricky stuff 5%

While wait statistics and other intricate details are interesting and geeky, don't mess your head with them at this point. The number 1 cause of blocking issues of any type (including deadlocks), is actually query performance. If your queries are running much longer than they should be, you have far more chance of them blocking each other.

So often, once I fix the query performance issues, all the blocking problems have vanished.

Fixing the application design issues is hard, and often political, but fixing the indexing issues is usually a lot easier.

To fix the query performance issues caused by indexing, you can do the following:

  • Find out which queries are causing the problems (it's not worth wasting your time on the others)
  • Fix the obvious indexing issues

If you don't know how to do that, I have a free course (4 Steps to Faster SQL Server Applications) that walks you through an effective process. It's online, and on-demand so you can do it right now. It'll only take a few hours.

You'll find it here:

https://training.sqldownunder.com/p/4-steps-to-faster-sql-server-applications

That covers the really obvious issues. For trickier indexing issues, we have another course (SQL Server Indexing for Developers). It's not free but it's great value with helping you understand SQL Server indexing. You'll find it here:

https://training.sqldownunder.com/p/sql-server-indexing-for-developers

In Part 2 of this series of posts, I'll assume you've already fixed these issues and your queries aren't running slow.

 

SDU Tools: All the Versions of SQL Server using SQLServerProductVersions

I can't tell you over the years how many times I've gone searching for details of a SQL Server build and/or related knowlegebase article. There are some sites that provide much of this, but I'd like to have it in a programmatic form. I can easily then use it in utilities and in my SQL scripts.

In our free SDU Tools for developers and DBAs, we added a view and a number of functions to help with this. The primary one is a view called SQLServerProductVersions.

We constantly keep this up to date as each CU (cumulative update) is released, and we've started adding in all the related knowledgebase articles for each build.

It's shipped as a view that returns the following columns: SQL Server Version, Base Level, Patch Level, Major Version Number, Minor Version Number, Build Number, Release Date, Core KB Article Name, Core KB Article URL, Additional KB Articles.

You can see the view in the image above and also see the view and a number of related functions, in this video:

To become an SDU Insider and to get our free tools and eBooks, please just visit here:

http://sdutools.sqldownunder.com

 

New Online Course: SQL Server Service Broker for Developers and DBAs

I'd love to be running Service Broker classes more often. Service Broker is one of the most powerful, yet least understood technologies in SQL Server. Many of our enterprise clients are using it and loving it.

What we tend to find though, is that we get requests from a number of interested people, spread over time, and locations. But not enough to run in-person classes in those locations.

So we decided to fix that, for this course and for a number of our other courses that have similar demand (i.e. Spatial, Replication, and more).

Our brand new SQL Server Service Broker for Developers and DBAs course is now out the door.

Now you can learn about it whenever you want.

If you've been wondering if you should look into Service Broker or aren't even sure what it is, now's a great time to find out. Coupon SBROKERINTRO will get you 25% off the brand new course until the end of the month.

And like all our courses, we have video instruction, hands-on-labs with downloadable resources, quizzes to check your learning, and a certificate at on completion.

You'll find it, and our other courses here:

https://training.sqldownunder.com

 

 

 

T-SQL 101: #13 Quoting (Delimiting) Identifiers in T-SQL

If you look carefully at the following two queries in the image below:

you'll notice there are a couple of differences. In the first query, the word Description is blue, and in the second, it isn't blue but it has square brackets around it. The second is an example of quoting or delimiting an identifier.

In this case, the reason that the word was blue in the first query is that somewhere in SQL Server, this word is part of the syntax of the language itself. That's much the same as SELECT or ORDER BY. So SQL Server Management Studio (SSMS) color-codes it the same way it does for the word SELECT.

This query would work fine without the quoting, but if I tried to have a column called ORDER or FROM, SQL Server would complain. I'd suggest that doing that is often a pretty lousy idea anyway. But if I did really want to do it, surrounding the name with square brackets would allow me to do it.

Now it's not just reserved words where this applies. If I have an object name with spaces in the middle of it, I have to quote the name. For example [Ordered By] could be a column name. Generally I try to avoid spaced in column and other object names.

There are always exceptions

There are two exceptions to this though:

In dimensional data warehouses, I try to have non-key (i.e. displayable) columns human readable, and that means they'll often have spaces in them. I don't want to be renaming them before they're added to every report, spreadsheet, Power BI dataset, analytic data model and so on.

The other situation is that if I'm using Service Broker, it's pretty common to use names that look like URLs for the names of message types, and contracts. But that's an advanced topic that we'll have to talk about another day.

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

SQL: Fix – Missing Font Options in SQL Server Management Studio (SSMS) 17.x

Ever since about version 17.3 of SQL Server Management Studio, I've had problems with fresh installs (as opposed to upgrades). I've been unable to set the fonts and colors. When I go into that dialog, I see the list as shown in the main image above.

All the normal option for windows that you can make settings for aren't there.

On my current machine, I had just installed a v18 SSMS and it was fine. All font options, etc. were there. But I had to install v17.9.1 of SSMS side-by-side because the ssbdiagnose tool was missing on my machine. (I needed it for our new Service Broker course and v18 doesn't install it for some reason).

Once I'd installed v17.9.1 as a fresh side-by-side install, I found that I was back to the font problem on the v17 copy of SSMS. (v18 was still fine).

Previously when trying to fix this, the suggestions I've heard were to uninstall it, install v17.2, then upgrade it. That was a pain but didn't always fix it for me either.

One Solution for Now !

So today, Matteo Taveggia from the product group was trying to help me work out what was wrong. Turns out it's caused by missing files. At this point, we don't know why they were missing, but here's what Matt got me to do.

I checked the following folder:

C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\1033

It should have contained a file: Microsoft.DataWarehouse.VsIntegration.rll but it wasn't there. I found another copy of it on my machine (in VS2017 folders) but you could get it from another machine. I copied it into that folder.

Then, Matt asked me to edit the following file: ssms.pkgundef which I found in the parent folder:

C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio

I added another line at the end, saying:

[$RootKey$\FontAndColors\Data Warehouse Designer]

The Verdict ?

When I restarted SSMS v17.9.1, all was good in the world again:

I'd like to know more about what other files were missing and what they do, but if you have this problem, I hope it fixes it for you too.

Many thanks to Matt for his help.

SDU Tools: CalculateTableLoadingOrder – follow table dependencies in T-SQL

If there is a reasonable number of tables in a SQL Server database, and I've also got foreign keys linking them, it can be difficult to work out the order of the dependencies. That's a hassle when I want to load data, and for other admin functions that I might need to perform. So we've fixed that.

In our free SDU Tools for developers and DBAs, there's a stored procedure called CalculateTableLoadingOrder.

It takes one parameter: the @DatabaseName.

The procedure works out all the dependencies and then lists the tables in order. It shows the following:

LoadOrder – the overall order
LoadingPhase – tables in the same phase have no dependency on each other and could be loaded concurrently
SchemaName – name of the schema
TableName – the name of the table
TableObjectID – the object_id for the table
IsSystemTemporal – is this a temporal table (we may need to worry about history)
IsTemporalHistory – is this the history table for a temporal table (and if so, the TemporalHistorySchemaName and TemporalHistoryTableName tell you which table)

You can see this procedure in action in the main image above, and in this video:

To become an SDU Insider and to get our free tools and eBooks, please just visit here:

http://sdutools.sqldownunder.com