{"id":2818,"date":"2017-03-13T19:53:38","date_gmt":"2017-03-14T02:53:38","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/kimberly\/?p=2818"},"modified":"2018-03-27T09:23:38","modified_gmt":"2018-03-27T16:23:38","slug":"sqlskills-sql101-indexing","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/sqlskills-sql101-indexing\/","title":{"rendered":"SQLskills SQL101: Indexing Basics"},"content":{"rendered":"

SQLskills introduced our new SQL101 recently and well…\u00a0indexing is something that everyone needs to get right. But, it’s\u00a0not a simple task. And, as I start to sit down to write a SQL101 post on indexing, I suspect I’m going to struggle keeping it simple? However, there are some core points on which I will focus and I’ll be sure to list a bunch of additional resources to get you more information from here! Remember, the point of each of our SQL101 posts is to make sure that everyone’s on the same page and has the same basic idea \/ background about a topic. And, for indexing, that’s incredibly important (and, often, misunderstood).<\/p>\n

What is\u00a0an Index?<\/h2>\n

Simply, it\u2019s a structure that’s applied to a set of [or, subset of] data to enforce ordering – either to quickly check uniqueness or to aid in accessing data quickly. Simply, that’s why you create an index. You’re either wanting to enforce data integrity (such as uniqueness)\u00a0or you’re trying to improve performance in some way.<\/p>\n

How Does an Index Enforce Uniqueness?<\/h2>\n

If you want to maintain uniqueness over a column (or a combination of columns), SQL Server takes the required data\u00a0(and very likely more data than you specifically chose)\u00a0and sorts that data in an index. By\u00a0storing the data in sorted order, SQL Server is able to quickly determine if a value exists (by efficiently navigating the index structure). For this intro post, it doesn’t entirely matter exactly what’s in the index but it does matter which index you choose and for what purpose.<\/p>\n

Relational Rules Enforced by Indexing<\/h2>\n

In a relational database, relational rules rule the world. And, many learn some of the rules rather quickly. One of these rules is that relational theory says that every table must<\/em> have a primary key. A primary key can consist of multiple columns; however, none of the columns can allow NULLs and the combination of those columns must be unique. While I agree that every table should<\/em> have a primary key, what’s chosen AS the primary key can be more complex than the relational rules allow. Behind the scenes, a primary key is enforced by an index (to enforce and check for uniqueness). The type of index that is used depends on whether or not you explicitly state the index type or not. If you do not explicitly state an index type, SQL Server will default to trying to enforcing your primary key constraint with a unique clustered index. If a clustered index already exists, SQL Server will create a nonclustered index instead.<\/p>\n

And, this is where things get tricky… a clustered index is a very important index to define. Internally, the clustering key defines how the entire data set is initially ordered. If not well chosen then SQL Server might end up with a structure that’s not as efficient as it could be. There’s quite a bit that goes into choosing a good clustering and I’ll stress that I think it’s one of the most important decisions to be made for your tables. And, it also needs to be made early as later changes to your clustering key can be difficult at best (often requiring downtime and complex coordinated scripting after already suffering poor performance before you make the decision to change).<\/p>\n

So, let’s keep this simple… you’ll want to choose the clustering key wisely and early. And, you’ll want to get a good understanding on the things that depend on the clustering key. I did a discussion and demo in my Pluralsight course: Why Physical Database Design Matters<\/a> and I’ve discussed this quite a bit in my blog category: Clustering key<\/a>.<\/p>\n

The key point is that the primary does NOT have to be enforced with a clustered index. Sometimes your primary key is not an ideal choice as the clustering key. Some great clustering key choices are:<\/p>\n