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.

Learning Mandarin: Two words for two

In an earlier post, I finished showing how to count in Mandarin, including large numbers and some of the odd features of the counting, like the way that Chinese say two ten-thousands rather than twenty-thousand.

But another thing that I constantly messed up when first learning Mandarin was the word for two. And that's because there are two words for two. I suppose that's not surprising if you think about how many words we use for zero.

(èr) is two and

(liǎng) is also two

(èr) is most commonly used for counting like one, two, three, and so on.

It's also used for positions:

第 二 个 (dì èr gè) means "the second one"

第 二 次 (dì èr cì) means "the second time"

But when you are describing a number of things (and using measure words), you typically use (liǎng) instead.

两 天 (liǎng tiān) is two days

两 个 月 (liǎng gè yuè) is two months

两 块 (liǎng kuài) is two pieces (of something – and can be money)

But there are always exceptions

I would have expected two o'clock to be the counting version but it's not. It's:

两 点 (liǎng diǎn) is two o'clock (I can only imagine that it somehow relates to two positions on the clock)

And even in numbers, 两 (liǎng) can be used, to count the number of hundreds and so on:

二百 (èr bǎi) is two hundred but 两百 (liǎngbǎi) is also commonly used.

Sometimes, mixtures will be used:

一千两百零二本书 (yī qiān liǎng bǎi líng èr běn shū) is one thousand, two hundred and two books

So you can be forgiven if it's not all immediately obvious.

Learning Mandarin

I'll write more soon on the best methods for learning. If you want to get a taste for it in the meantime though, my current favorite site is iTalki, and my favorite teacher by far is Amy He. If you decide to try it, click here and it's cheaper for both you and me.

SQL in the City – Brisbane, Christchurch, Melbourne – Hope to see you there

I'm presenting a session on Azure DevOps for SQL Server DBAs that's designed as an intro for data people who haven't really worked with it before, at Red-Gate's SQL in the City events in Brisbane (May 31), Christchurch (June 7), and Melbourne (June 14).

Looks like a fun lineup for the day, and it'd be great to catch up with you at one of those events. You can find more info here:

https://www.red-gate.com/hub/events/redgate-events/sqlinthecity-summit/

Also, the day after each of those events, I'll likely also be presenting at SQL Saturday in the same cities. I'll let you know more when the speaking lineup for those events is released, but either way, I'll be at those events too and would love to catch up with you.

 

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

 

Power BI: AddWeekdays function for Power Query M language

In our free SDU Tools for Developers and DBAs was an AddWeekdays function. Now that was for T-SQL. Recently though, I needed to do that for Power Query. While the M language has a wonderful set of date-related functions, it didn't have this one.

That made it time to write one. Here's the code that's required:

So how does it work?

Forgive the formatting to fit this window, but let's take a quick look through it:

I started by creating a list of dates that could possibly be in range. The start of that list is FirstListDate. If the number of days is zero or positive, I've used the StartDate. Otherwise, if NumberOfDays is negative, I went back to a date that's twice the number of required days backwards.

The reason that I've used twice the number of days is that I need to later exclude weekends. I know that twice the number of days will include all the days I need, but won't include a crazy large number of days.

I then create GeneratedDates as a list of dates from the FirstListDate. That gives me a list that either starts at the StartDate (if the NumberOfDays is zero or positive), or ends with the StartDate (if the NumberOfDays is negative).

WeekDays is then created as a list that only contains weekdays, by doing a List.Select and excluding Saturday and Sunday. I did that by setting the start day for the week to Monday (i.e. it's zero) and then looking for days < 5 (which would be Saturday).

DaysInRange is then created by removing either the front or end of the list based upon the target NumberOfDays.

Finally, ReturnDate is calculated from the first or last value in the list, depending upon whether NumberOfDays was positive, zero, or negative.

I hope that code helps someone.