Organizing SQL Server Data

Indexes

Data is stored in your SQL Server database in tables. A table without a clustered index is a heap. What is the difference between a clustered and non-clustered index? I think it is necessary to describe the way data is organized in SQL Server, because your understanding how data is stored will help you better understand how to write effective queries.

Heap

When you create a table using the basic CREATE TABLE statement without a clustered index, you are creating a heap table. A heap is the table without any order defined. The heap contains all the columns of your table, without any defined organization. If a table is created without any indexes the heap is the table and the table is a heap.

Clustered Index

A clustered index is a table with an specific order defined on it. The clustered index contains all the columns of a table and the clustered index is the table. Clustered indexes are really B-Trees. If you declare a primary key on a table then the same key is also used for the clustered index, unless the primary key is explicitly declared as NONCLUSTERED. 

Nonclustered Index

A nonclustered index is a copy of a subset of the columns that make up the table columns with a specific order defined. A nonclustered index contains one ore more columns from the table. When talking about indexes on a table, most often the discussion refers to nonclustered indexes. Nonclustered indexes are B-Trees. 

Indexes not only speed up the retrieval of rows for selects, they also usually increase the speed of updates and deletes. This is because SQL Server must first find a row before it can update or delete the row. The increased efficiency of using the index to locate the row usually offsets the extra overhead needed to update the indexes, unless the table has a lot of indexes.

You can learn more about indexes here.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s