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

 

 

 

 

 

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

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

 

 

 

 

 

Shortcut: Zooming and navigating execution plans in SSMS

SQL Server execution plans can become quite large. That makes them hard to navigate because you are endlessly scrolling around the results pane in SQL Server Management Studio (SSMS).

The pane does have some zoom features. Note that if I right-click in the whitespace, I get these options:

So I can zoom in and out, set a custom zoom level, or zoom until the entire plan fits. Generally though, that would make the plan too small to read, as soon as you have a complicated plan.

But in one of the least discoverable UI features in SSMS, there is an option to pan around the plan.

If you click and hold that little + sign, you'll find you can pan around within the plan:

That's very nice but I think it needs to be a little easier to find.

SDU Tools: Script SQL Server Database Users

In our free SDU Tools for developers and DBAs, there's a tool that makes it easy to create a script for creating SQL Server database users.  No surprise, it's called ScriptDatabaseUsers.

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.

You can see it in use 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

Happy new year from SQL Down Under and from me

Hi Folks,

Just a short note today to wish everyone a happy new year. I know that new year is a time when many people make resolutions, with the intention of changing something about themselves during the upcoming year. It makes it a time full of hope for fresh starts.

I don't tend to make too many resolutions as I think that needs to happen on an ongoing basis rather than once per year, but I understand why people do.

From a company point of view, these are things you are likely to see from us this year:

  • Many new online and on-demand courses. (I know that many of our customers like in-person courses better but this is the low-cost and fast way to learn some topics, compared to waiting for us to run them in-person. And we've put a lot of effort into making sure the experience is as close to the in-person experience as it can be, with hands-on labs, quizzes, etc.) You'll find them at http://training.sqldownunder.com
  • New podcasts. I try to create a number of podcasts when a new version of SQL Server is about to appear. That gives me a chance to discuss the concepts behind the product features with the people who know about them, right while they're still finalizing them. You'll find the first SQL Server 2019 podcast with Argenis Fernandez here: https://sqldownunder.com/pages/sql-down-under-podcast
  • More in-person classes. Most will be in Melbourne, but it depends upon demand. Query Performance Tuning and Advanced T-SQL is one of our all-time favorites. It's running in Melbourne early next month. Early bird pricing is available now. Would love to see you there. If you can't make the full 5 days, you can do 2 days of Query Performance Tuning or 3 days of Advanced T-SQL separately. You'll find more here: https://sqldownunder.com/pages/sql-server-query-performance-tuning-and-advanced-t-sql-5-days, here: https://sqldownunder.com/pages/queryperformancetuning, and here: https://sqldownunder.com/pages/sql-server-advanced-t-sql-3-days
  • New eBooks. I have a couple of these planned. We'll see how many get out the door this year but if you don't have the current SSMS Tips and Tricks one, you'll find it here: http://ssmsbook.sqldownunder.com
  • Many more of our free developer and DBA tools: SDU Tools. Even if you aren't wanting a full set of tools, these are great examples of how to do things in SQL Server using T-SQL. If you don't have them yet, look here: http://sdutools.sqldownunder.com Version 13 is out now, and we've already got some great additions coming in Version 14.

Regardless, I hope you are all safe and well, and have an awesome new year!