Shortcut: Split query windows in SQL Server Management Studio

If you are working with really long script files in SQL Server Management Studio (SSMS), you might need to work on more than one part of the script at the same time. Perhaps you need to work on a function, and also on the code that calls the function.

On the Window menu, there is a Split option.

When you first do this, you'll see a split window with the same query at top and bottom:

You can then scroll each vertically and resize them independently, and work on different parts of the same script:

The easiest way that I've found to close this, is to double-click on the dark bar in the middle, but there is also a Remove Split option in the Window menu.

SDU Tools: Script Server Role members in SQL Server

In our free SDU Tools for developers and DBAs, there's another useful scripting tool. It scripts out the logins that are members of server roles. It's called ScriptServerRoleMembers.

One key advantage of having these scripting procedures and functions is that you incorporate them into other applications, and programmatically perform the scripting if you need to.

But if you just want to create a script, that works too.

By default, it lists permissions for all logins, but you can also provide a list of the logins that you're interested in, as a comma-delimited list, to the @LoginsToScript parameter.

You can see an example of both in the main image above, and you can watch it in action here:

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

http://sdutools.sqldownunder.com

Need to learn to write T-SQL queries for SQL Server? Or need to become more confident?

Do you (or someone you know) need to learn to write T-SQL properly? Or perhaps you need to be more confident with joins, aggregations, CTEs, and more?

Our latest online on-demand course Writing Queries for SQL Server is now available, and at introductory pricing of just $95 USD.

It has detailed and comprehensive intro level coverage of the T-SQL language, and follows professional coding standards throughout. It's also not just a set of videos; it includes extensive hands-on labs and quizzes to make sure the learning is great.

The T-SQL that the course covers is also up to date. It includes content up to SQL Server 2017, but is just as useful for users of earlier versions of SQL Server.

The course would suit developers, reporting analysts, business analysts, new DBAs, and more.

This is one of a set of new courses that we're making available. Our free 4 Steps to Faster SQL Server applications is already available, and so is our SQL Server Indexing for Developers. You'll find them all here:

http://training.sqldownunder.com

SQL: Filtered indexes in SQL Server can be wonderful but be careful !

Back to the transaction table

Two weeks ago, I wrote about the issues with a large transaction table where only a handful of the rows were unfinalized, and that we would never use an index to find all the rows that were finalized. But we'd certainly want an index defined for the ones that weren't. If you haven't read that post, I'd suggest you do so before continuing to read. You'll find it here.

Now one of the challenges is that indexes like this on a very big table, can also be large. Also, every row in the table has an entry in each index.

Why filtered indexes?

If you think about it, if all we're ever going to use is one part of the index, i.e. just the unfinalized rows, having an entry in there for every single row is quite wasteful, as although the vast majority of the index will never be used, it still has to be maintained.

So in SQL Server 2008, we got the ability to create a filtered index. Now these were actually added to support sparse columns. But on their own, they're incredibly useful anyway.

The idea is that we can have a WHERE clause on the index and the index only contains entries for the rows that match the WHERE clause predicate. These indexes are often much smaller and can also be much faster, but it's really important that you're very, very careful when using them.

In particular, you normally need to include the predicates when you use them in queries. Let me show you why.

Quick demo

Here in my Indexing database, I'm going to create a table called Transactions.

I've got a TransactionID as just an identity column, a date, an amount, and IsFinalized. So now I'll populate it. I'm going to just add 100 thousand rows.

But what I also did was to have it calculate the transaction date on the fly and I've got three rows where I'm going to set the fact that the transaction was unfinalized. All the other ones have it set as finalized.

What I'm going to do is create a normal index on that IsFinalized column.

Keep in mind that what that index will contain is the IsFinalized column, along with the clustering key which was TransactionID.

Now let's look at some query plans. Here are the queries:

Here are the query plans:

 

No huge surprise there. Note that it wasn't a brilliant index, because a lookup was needed to get the date. But the first one decided that the statistics were OK for a series of lookups, so it does that. The second one just complains there is no suitable index.

Now let's try a filtered index instead:

And we see that they're basically the same:

The difference is that it's picked up the filtered index in the first query. But it could only do that because it matched the predicate entirely, and was able to do so before it ran the query.

Parameter problems

Instead, if I declare a variable, and I use it in the predicate, it's the same logical query, but the outcome is entirely different. Here are the queries that use variables:

And here are the query plans:

Yep, now they're both broken. That's because at the time the query plan was created, SQL Server didn't know what value that variable would have. (In this case, you'd think it could work that out actually but it doesn't). And so it doesn't know if the value will match the filter predicate, so it also doesn't know if it can use that filtered index, so it doesn't.

The main thing is that if you use a filtered index, although they can be absolutely wonderful, it's really, really important that you match the filter predicates exactly, even if you also have other predicates in the query.

Learn about indexing

Want to learn more about indexing? Take our online on-demand course now:
https://training.sqldownunder.com/p/sql-server-indexing-for-developers

 

 

 

 

 

Book Review: No Ordinary Disruption

I mentioned in a previous post about how one of my colleagues Orin Thomas is a prolific reader and every time I talk to him, he suggests more books that I should read. Another one in that category was No Ordinary Disruption One by Richard Dobbs, James Manyika, and Jonathan Woetzel. The sub-title is The four forces breaking all the trends.

The authors claim that our intuition on how the world works could be very wrong. I suspect that the degree of change in the accuracy of our perception is a recent, and accelerating thing. In the book, they are summarizing years of research they have done at the McKinsey Global Institute.

It's amazing today how many businesses that have been large, solid, and around for a long time, are suddenly almost swept aside by relatively new competitors. Suddenly, the world feels different. It is going to be very, very different.

What I particularly like in this book is the way that they've analyzed what's going on in China. The rise and rise of Chinese technology is a passionate interest of mine, and I'm surprised how little attention it's getting in the West today. I think we ignore it at our extreme peril, particularly financially. So many things that I see going on in China are so very disruptive. I hear people scoff at some of the quality of what they're doing, but I heard exactly the same thing about Japanese products in the 1960's and 1970's. By the 1980's, everyone was taking notice of the Japanese.

If you don't realize that technologies that are now constructing skyscrapers at three stories per day (yes 57 stories in 19 days), and others that are 3D printing entire houses, are going to be here in the near future, you aren't watching what's going on.

What we in the West aren't seeing is where the real growth is going to come from in the future. An example they give is that a single regional city in China (Tianjin) will have a GDP as large as Sweden by 2025.

I loved the way that the authors note that cities that most executives would be hard pressed to even find on a map today, will have most of the economic growth in the next decade.

The four main trends that they've mentioned are a big shift away from North Atlantic trade, across to emerging markets; the way the world is aging; the effects of ongoing urbanization; and obviously, the impacts of technological change.

But it's not all about China and emerging markets. There are other significant disruptions taking place. For example, who, in the 1980's, would have tipped the USA as the world's largest oil producer, rather than some countries in the Middle East or Russia?

The first chapter contains the essence of the book. It's a fairly straightforward read, and covers the main points of the whole book. The rest of the book is a much heavier read, with all the statistics and analytics, but interesting nonetheless.

My main concern right now, is that the upcoming disruption is very different to anything we've seen in the past, and we're nowhere near ready for it.

Bottom line?

This book is interesting and it's another one that I'm glad Orin suggested to me. I have a deep interest in these things, and this was yet another set of opinions that were worth considering.

Greg's rating: 8 out of 10

Note: as an Amazon Associate I earn (a pittance) from qualifying purchases but whether or not I recommend a book is unrelated to this. One day it might just help cover some of my site costs. (But given the rate, that's not really likely anyway 🙂

Shortcut: Pinned tabs in SQL Server Management Studio

When you get to a large number of query windows or other documents open as tabs in SQL Server Management Studio (SSMS), it can start to be difficult to keep track of them, and to find them when needed.

It's not too bad when you can immediately find the tab that you want in the drop-down list:

But if you have more tabs than are shown in this drop-down list or if, like me, you often end up with many of them without names (as they are temporary), it can get very hard to find the few that you are mainly referring to.

Just like you can with tool windows, you can pin tabs:

Once you do this, they stay against the left-hand side (by default). Now that's not bad but again if you have a few of them, there's another option that can help.

Once you configure that, another row of tabs appears in SSMS:

 

SDU Tools: Script Server Permissions in SQL Server

In our free SDU Tools for developers and DBAs, there's another useful scripting tool. It scripts out the server permissions that have been granted to logins. It's called ScriptServerPermissions.

One key advantage of having these scripting procedures and functions is that you incorporate them into other applications, and programmatically perform the scripting if you need to.

But if you just want to create a script, that works too.

By default, it lists permissions for all logins, but you can also provide a list of the logins that you're interested in, as a comma-delimited list, to the @LoginsToScript parameter.

You can see an example of both in the main image above, and you can watch it in action here:

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

http://sdutools.sqldownunder.com

Opinion: Developers, silently swallowing errors is not OK

I don't know if it's considered some sort of modern trend, but what is it with applications now that just swallow errors instead of dealing with them? Is there an edict within these companies that errors should get shown, so they can argue their app doesn't have errors?

I'm working with a SaaS app right now. It does editing. Sometimes when I save, it just doesn't save. No error, just nothing saved. Or every now and then, I find the order of what I've entered just gets changed. Again, no error, but the order was changed.

Worse, sometimes when I then try to correct the order, it shows it as done, but next time I go back to that screen, the order is back the way it was in the first place.

On many occasions, if I close my browser, open it again, and log in, it all works OK again for a while.

But it's not just these types of applications. I've lost count of the number of sites I've been to, where supposedly serious applications are being developed, yet the code is full of try/catch blocks but the catch blocks are empty ie: silently ignoring any errors that occur.

How did we get to the point that this is what passes for application development now? Apps that mostly work and fail silently?

Sorry, but this is not OK.

SQL: How do I choose a clustering key in SQL Server?

Clustered indexes vs heaps

OK so you know that a table can either be a heap or it can have a clustered index.  The first question of course, is which should I use?

If you have any doubt at all, put a clustered index on the table. Most SQL server tables will end up performing better that way. There are a few scenarios like log files where heaps will be preferable but if you're not sure, start with a clustered index.

But which column or columns should I cluster on?

That then raises the question of what sort of keys or which columns are really the best to choose for that clustering index, and of course, like many things in SQL server, the answer is that "it depends on how it's being used".

For example, if you're inserting data, and you're looking at insert performance in the table, the choices you make will affect this. For this operation, I'd start by saying that the value needs to be static, because if the value changes, then that means the row has to move. That's almost always a bad thing. Because the rows are kept in a logical sequence, you do not want to use a value that ends up changing, at all.

The second thing is, you want a short value. This value is going to be all throughout the index. Every single thing in the table is going to be sorted by that, and it's the value that's going to be present in the leaf level of every nonclustered index as well. You don't want long values here.

Large key values like long strings, will not perform well for this.

You also want the values to be unique. If SQL server doesn't know that your values are unique, it will automatically add some additional data that we call a uniqueifier to make sure that it can uniquely identify, every single row. Your world will be better if it knows that they are actually unique.

Finally, it might be helpful if the values are increasing. It doesn't have to be what's called monotonically increasing (that is going up by the same amount each time), but just always getting bigger. What that means is that data will just keep getting added to the end of the table. Most of the time that's going to be helpful.

There's always an "it depends"

There are some very high performance scenarios where the end of the table could then start to become a hotspot and can be a problem because all the inserts are happening there. But for the vast majority of tables you'll be better off having the data that gets added to the table, being added to the end of the table and an increasing clustering key will do that for you.

Learn about indexing

Want to learn more about indexing? Take our online on-demand course now:
https://training.sqldownunder.com/p/sql-server-indexing-for-developers

 

 

 

 

 

Learning Mandarin: Tones used in Mandarin

I mentioned in an earlier post that Chinese dialects are tonal. As well as words being different in different dialects (but often still the same characters), the tones are also different for different dialects. Mandarin is generally considered to have four tones or  声调 (Shēngdiào) plus a neutral tone. So, some would describe that as a total of five tones.

The first tone or 第一声 (Dì yī shēng) is often drawn as level but it's actually both high and level.

An example is  (Mā) meaning mother. The second tone or 第二声 (Dì èr shēng) is a rising sound.

An example is  (Má) which is a word for hemp. The third tone or 第三声 (Dì sān shēng) is a falling then rising sound.

An example is  (Mǎ) which means horse. The fourth tone or 第四声 (Dì sì shēng) is a sharp falling sound.

An example is 骂 (Mà) which means to curse or is a curse.

The neutral tone is both central and flat. An example is  (Ma) which is like a question mark. In the main image above (by Lufti Gaos), you can see yet another "ma". The second word from the top means "code" and is also 3rd tone. (The sentence basically means "scan code, use bike". The last word is more like "vehicle" but here it'll be an abbreviation of the word for bike).

It's important to understand that Chinese people don't hear the above words like variations of the word "ma". They hear them as different words.

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 is Tutor Ming. If you decide to try it, click here and it's a bit cheaper for you, and for me.