Tag t-sql tuesday

T-SQL Tuesday – Why Are DBA Skills Necessary?

T-SQL Tuesday

They aren’t. 99% of what you do could be replicated by a fairly stupid shell script.

When I started as a DBA, I didn’t have practical experience as a DBA. I had Books Online and google.

What’s necessary as a DBA has nothing to do with your knowledge of T-SQL or SQL Server’s internal fiddly bits. That’s icing on the cake.

The skills necessary to become a DBA are things that we learn over time. These are the skills and traits that make us successful professionals, students, friends, and lovers. You need patience, inquisitiveness, and a healthy dose of skepticism. You should also be able to follow a checklist. Making the checklist is for the advanced class.

The technical skills of a DBA are the same as those of a plumber – they’re both skilled trades. There are varying degrees of success and skill. You can distinguish between the skilled and unskilled very quickly by their approach to life and learning. People who are good at their job possess the skill of learning: their practical job skills themselves are secondary to their ability to learn.

T-SQL Tuesday – Indexes

arbitrary logo because a world without rules is a world with fun

When Michael J Swart asked me to take part in T-SQL Tuesday #10 – Indexes, I was incredibly flattered. Nobody’s ever asked me to do anything since a cop asked me to stop doing that one thing (speeding). I had to say yes. Here’s my contribution to T-SQL Tuesday #10.

An index is a horological lever-like something or other.

WTF is an index?

My 1967 Children’s World Book Encyclopedia doesn’t provide a definition for an index. That’s probably because it’s an encyclopedia and not a dictionary Thanks to the local gypsy garage sale going on in my neighborhood, I was able to acquire a dictionary from 1934 which defines an index as “a leverlike regulator for a hairspring.” Apparently this has something to do with clocks. If you try to verify this, you’ll notice that it’s a horological term. Apparently that has something to do with clocks and not adult entertainment.

Obviously neither my Children’s World Book Encyclopedia nor my Book of Learnin’ and Such was going to help me define an index.

In the spirit of true science, I realized that I would have to make something up. Or do my homework. One of the two.

SQL Server and a Drought of Options

SQL Server doesn’t have a wealth of indexing options available. In fact, there are fundamentally only two options – indexed and STFU. Sure, there are multiple indexing techniques, but this is my story.

At the core of SQL Server’s indexing strategy is the self-balancing binary search tree or, for those who don’t like to sound like pompous assfaces, the b-tree. B-trees are full of special magic. Well, not really. A b-tree is based on an algorithmic model that strives to keep the height of the search tree (the number of nodes between the root and the leaf nodes) as small as possible. This means that finding any single piece of information should be equally as cheap/expensive as finding any other piece of information.

That sounds great, right? Well, one of the fundamental indexing problems is that the data in the index has to be kept up to date as the data in the table changes. You can’t have data flying around willy nilly.

This is where we get to start sub-typing our b-trees.

Oh, So There ARE Options

Yes, I lied to you. Sort of. SQL Server has multiple types of indexes, just not multiple… types of index. There’s no way to control the indexing mechanism that SQL Server uses, you’re stuck with the pompous assfacery of self-balancing binary search trees (it’s not really a bad thing).

So, getting back to the topic at hand, in SQL Server we do have options. Two of them: clustered and non-clustered indexes.

Cluster This!

A clustered index is an ordered collection of nuts stored for winter and categorized appropriately… or data. One of the two. When we create a clustered index, all of the nuts/data is sorted and written to disk in the appropriate logical sort order. From this point forward, no physical order is guaranteed. Once the clustered index is created and the data is placed in physical order all bets are off. The data will be stored in logical order, but there is a chance that the data may not be physically in order due to the vagaries of disk access, deleting rows and/or tables and other assorted whatnot. (I think the horologists might have something to do with it.)

What goes in a clustered index? Everything. I told you the data was copied in. Didn’t I? Well, it is. Our clustered index is all of the data in the table and it’s in order based on the clustering key value.

The choice of the b-tree algorithm actually has some implications for how we choose the index columns for the clustered index. (I’m going to call these columns the clustering key. That’s what they are. Deal with it.) Because we’re using a B+ tree, instead of another data structure, to store and sort indexed data we have to take a few things into consideration. B+ trees do really well when every indexed value is unique. This is by design – it’s easier to look up any given row if you know that there is only one row that corresponds to that key. If there’s more than one, things get tricky and I’m not going to talk about that.

B+ trees have other characteristics that make them optimal for use in databases – it’s really easy to find any single element in the tree (even compared to other binary tree mechanisms) and they’re wonderfully optimized for read access when data is being read in sequence across ranges of data.

Searching Is Good

Sometimes you won’t know the key to reference a particular piece of data. Sometimes you’ll want to pull back records for everyone who lives in Tuscaloosa, AL. If you haven’t ordered your data by city and state, this is going to be really difficult to find without scanning the entire table. How do we get that data back?

More indexes!

In addition to clustered indexes, which define how the data should be ordered on disk, we also have non-clustered indexes. We can use these guys to help speed up our search. These are really just supplementary b-trees that point back to the clustered b-tree. You do have a clustered index, right?

That’s it?

No. Of course it can get more complicated than all of this, you can have composite indexes with multiple columns, non-unique indexes, indexes with computed columns, and even full-text indexes. Unfortunately the hookers and their lever actuated hairsprings are not going to let me keep talking.

In case you’re wondering what would happen to your database if you didn’t have any indexes, take a look at this:

i can't find any indexes!

T-SQL Tuesday 4: Io, Io, it’s off to disk we go

Fact: the earliest recorded use of cloud computing was ancient Greek porn

Io was a nymph. True story. Apparently, her father was some kind of river god. In modern times that means you’re likely to catch fire. Back in the days when the Greeks were in charge of things being a river god meant that you were somebody (the Greeks thought the earth was a giant brass plate floating a huge river, all of which was created by perverts who lived on top of a mountain). So, apparently Io’s dad was important.

Anyway, it is rumored that Io was attractive. So attractive, in fact, that Zeus, lord of the perverts, saw her taking a bath and got more than a little bit aroused. Zeus then behaved in a way that would end up in a savage beating and restraining order back where I come from – he pestered Io for nookie until her father drove her out of the house – probably because some horny lunatic who could shoot children out of his forehead was bothering his daughter. Io, being a bit strange in the head, relented. Or something. My records aren’t 100% clear seeing as how they’ve been written on pottery. The point is that Zeus turned into a giant cloud and turned Io into a cow (no, your hooves don’t make you look fat).

Somehow Zeus’s wife got involved and there was bondage involving a cow tied to a tree or something. Eventually Io gets turned back into a real live girl and gives birth to Zeus’s son. Which brought about an ethics probe into cross-species cloning.

Disk… disk… oh yeah I already mentioned that the ancient Greeks were clearly insane and thought that the world was a giant metal plate floating on a huge river name Oceanus all of it encased in a hemisphere with clouds and the sun and the moon and stuff painted all over the inside of the hemisphere.

What’s outside of the hemisphere? Shut up, that’s what. It’s turtles all the way down.

There’s a bit of humor thrown into your T-SQL Tuesday

This site is protected with Urban Giraffe's plugin 'HTML Purified' and Edward Z. Yang's Powered by HTML Purifier. 531 items have been purified.