The Bit Bucket

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

 

 

 

 

 

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.

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

One data type that's often been quite controversial is the bit data type. So a bit is where we have an individual value, which is either a zero or one. They're the only values allowed unless it's NULL.

Always keep in mind when you're working with bits in SQL server that they're aren't just 2 potential states, there are three. It can be zero, or one, or it can be NULL. That is, it can have no value at all.

A common misconception

Bit values can be indexed and contrary to many misconceptions that have been around over the years, indexing bits can be very, very useful.

Now I used to see a lot of Microsoft material, that said there is absolutely no point indexing bit columns because they can only have two values. But the issue is not the number of values that a column can have, the issue is how selective the value is.

Why did they think that?

Let me explain the thinking that led to this. Imagine I had a table that's holding the people who are attending a conference and we have an indication of whether or not they've previously attended the same conference. Let's say that 50% of them have been to the conference before. Now having an index on that column probably isn't going to be very useful, as that's not at all selective.

To be selective, you need something that's quite tiny in terms of the number of rows returned compared to the total number of rows in the table.

But let's consider a different situation. If I have a transaction table and it contains billions of transactions, 500 of which are unfinalized. I could have a column in the table called IsFinalized that is a bit data type. (My purist friends would argue that's not normalized properly but run with me on this for now). I would never use the index on that column to find all the ones that are finalized, because that's most of the table.

However, if I ever went looking for the unfinalized transactions, I most certainly would hope there's an index there to let me find those 500 unfinalized ones.

It would also make a good candidate for a filtered index, but that's a topic for another day.

Why is the idea so common?

The idea that indexing bits isn't useful started back in early versions of SQL Server. In SQL Server 7, you couldn't build an index on a bit column. In SQL Server 2000, you could do it via T-SQL but the tools like Enterprise Manager didn't give you the option. Even the SQL Server training materials from Microsoft said it wasn't useful.

So people got the idea that it wasn't useful. But that was never true.

Indexing bit columns can be very, very powerful where the data is selective.

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

 

One of the challenges for newcomers learning any Chinese dialect (including Mandarin), is that the languages are tonal languages. In English, we tend to use tone for emphasis but it's not really used to change to a different word. 

For example, if I said "he really wants to go", if I draw out and more heavily the word "really", I can make it sound emphatic rather than casual.

The exceptions to this would be homophones, where we have words that are spelled the same but entirely different meanings. A simple example would be "lead" where you can "lead a horse to water" but also "go down like a lead balloon".

In Chinese, words that have the same Pīnyīn letters but different tones, are usually (but not always) entirely different words. A common example is "ma", which depending upon tones can mean a horse, a mother, a question mark, and many more things. Even when the tones are the same, it can mean many different things, and you need context to make sense of it.

Because of this, what I found difficult at first, is talking to Chinese people. The problem is that if they hear "ma" with the first tone, and "ma" with the third tone, they don't hear a badly pronounced word, they hear an entirely different word. There's no mental connection between the two.

In English, that's not so much of an issue. For example, if I said "there is lead in the petrol" but pronounced "lead" like the one in "lead a horse to water", an English speaker can still understand you, and will just think you have a lousy accent. We have a mental connection between those two words.

I find that Chinese speakers do not have this mental connection. It's probably because we relate the two different words to the same spelling, whereas "ma" and "ma" are often entirely different characters in Hanzi (or Chinese characters):

It's only in Pīnyīn that the letters seem related. To a speaker, they aren't related at all. Only words with the same letters and tones seem related this way.

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.

In an earlier post, I described how query plans could be saved as .sqlplan file, shared, and loaded again in SQL Server Management Studio (SSMS). It's also possible to extract them out of SQL Server Profiler or Extended Events Profiler.

This is useful, but the same applies to deadlock graphs. SQL Server 2005 added Deadlock graph as a type of event in SQL Server Profiler. (It's also part of Extended Events Profiler).

If I open a new trace in Profiler, I can add Deadlock graph to the list of events:

Then when a deadlock occurs, we can see an entry for it in the trace:

Now if you want to share that with someone else, you can right-click the deadlock graph event and extract the event data:

When you do this for a deadlock, the output is saved as a .xdl file extension.

You can then send it to someone else and they can load it in SSMS:

One important thing to note is that a deadlock graph is actually a list of deadlocks, not just one deadlock. When a deadlock graph is opened in SSMS, it only shows the first deadlock in the graph.