Understanding SQL Server indexes

When you work as either a SQL Server database administrator or developer, you hear a lot about indexes.  So what is an index and what is it purpose?  An index is created on a data table or view and is used to quickly locate data without having to search every row within a data table.

While they can improve the amount of time it takes to search through your data, an index does require its own storage space.  When an index is created on a table, the data table is not changed.  Instead, a new data structure that refers to the original table is created.  This requires additional storage space so users need to consider storage vs speed when adding new indexes.

An index is made up of a set of pages that are organized in a B-tree structure.  This is a hierarchical structure that contains three levels: Root, Intermediate, and Leaf.  All of the data is hosted on the Leaf level.  The key values and pointers are contained in the Root and Intermediate levels.

BTree

 

An index can include one or more columns for the specific table that you are working with.  Without an index, a query search goes through all of the records in the database to retrieve only those records that are needed.  With an index, the search is on the index first to find the desired records and the corresponding records are retrieved.

An index can contain several columns, but not all data types can be used within an index.  Indexes can be created on most columns within a table or view except for those that are of image, ntext, or text data type.  Columns of varchar(max), nvarchar(max), varbinary(max) and xml data type are only allowed in a non-clustered index as a non-key index column.

There are two basic types of SQL Server indexes: clustered and non-clustered.  A clustered index defines the order in which the data is sorted within the table.  Since the data can only be sorted one way, there can only be one clustered index per table.  If there is a primary key in your table, it automatically creates a clustered index on that particular column.

Unlike the clustered index, the non-clustered index does not sort the data within the table.  Since the non-clustered index does not define the sort order, there can be more than one non-clustered index on a table.  Depending on the version of SQL Server you are using, you can have 999 non-clustered indexes in SQL Server.  This is true if you are using SQL Server 2008 R2 or greater, which most people are now using

Let’s look at the impact that an index can have on a relatively small, simple table. For this example, I have made a copy of the Purchasing. PurchaseOrderLines table in the WideWorldImporters database named PurchaseOrderLine_TestIndexing.  We will run the following query against the table with different indexes on the StockItemID field and look at the results.

SELECT StockItemID
FROM [Purchasing].[PurchaseOrderLine_TestIndexing]
WHERE StockItemID = 77 

For the first run of the query, there are no indexes on the table.  Because we have no indexes, a table scan was completed to find the requested data.  That means resources were used to go through the entire table to find the records where StockItemID = 77.  This table does not have a lot of records so the impact was not as intensive as a table with a lot more records, but it is a consideration.  The Estimated Subtree Cost is 0.131745 and it took 0.121s.  The Estimated Subtree Cost is the SQL Server cost to execute the query.  The lower the Estimated Subtree Cost number, the less resource intensive the query is for SQL Server.

Noindexes

 

For the next example, we added a non-clustered index to the StockItemID field.  This time, an Index Seek was completed against the table instead of a Table Scan, the Estimated Subtree Cost was reduced to 0.005226, and the amount of time it took to run the query was reduced to 0.101s.  Since the query searched through the Index instead of the entire table, it was a more efficient use of resources to find the associated records.

NonCluseredIndex

 

For the third run of the query, we removed the non-clustered index and added a clustered index to the StockItemID field.  This time a Clustered Index Seek was done, the estimated Subtree Cost and time spent to run the query was close but just slightly higher than with a non-clustered index on the same field.

ClusteredIndex

 

The clustered and non-clustered index provided similar results for our second and third examples.  You are allowed only one clustered index per table, this might not be the appropriate field for a clustered index.  For the PurchaseOrderLine_TestIndexing table, a non-clustered index on the StockItemID field is the most appropriate type of index.

I hope this gives you a good overview of the basics of SQL Server indexes.  Over the next few weeks we will explore the different types of indexes in SQL Server and the uses for each type.

Scripting Objects in SSMS

There are a multitude of reasons why users script existing objects within SQL Server.  Depending on the reason will dictate whether you are scripting one object, a few objects, or the entire database.  There are different tools within SQL Server Management Studio (SSMS) that will help you create object scripts.

 

Single Object

To script a specific SQL object: Right click on the object -> Script [Object Type] As [CREATE To: ALTER To: DROP To: DROP And CREATE To: SELECT To: INSERT To: UPDATE To: DELETE To:] -> Location [New Query Editor Window – File – Clipboard – Agent Job]

SingleObject

 

Object Explorer

Users are able to script an entire database from the Object Explorer.  This option scripts out only the database configuration options and not specific objects within the database.

Right click the selected Database -> Script Database As -> [CREATE To: DROP To: DROP And CREATE To:]  -> [New Query Editor Window File Clipboard Agent Job]

ObjectExplorer

 

Generate Scripts Wizard

This option provides the most options for selecting what is scripted and what is included.

Right click database -> Tasks -> Generate Scripts

GeerateScriptsWizard

 

This will open the Generate and Publish Scripts wizard.  You can select to script the entire database or specific object(s) within the database (Tables, Views, Stored Procedures, User-Defined Functions, User-defined Data Types, DDL Triggers, Xml Schema Collections, Schemas, Full Text Catalog).

ChooseObjects

 

Once you have selected everything to be scripted, click Next.  This will take you to the Set Scripting Options page.  Select the Output type and where the script will be saved.

SetOptions

 

Click on the Advanced button to modify any of the advanced scripting options.  When you are done making changes to the scripting options, click on the Close button.

advancedscriptingoptions

 

Click Next to review your scripting selections.

Summary

 

Click Next to save/publish scripts.

SaveScripts

 

Click Finish to close the Wizard.

T-SQL Tuesday #115: Dear 20 Year Old Self

This month our host is Mohammad Darab (b/t) he has given us the subject ‘Dear 20 year old self’. We are supposed to write a letter to ourselves in our early twenties.  I am writing this a couple of days late because I was out of town and did not get it done for Tuesday, but I still wanted to put it out there.  I love the topic and want to thank Mohammad for hosting this month.

This is a topic that I think about at times, usually around my birthday.  It is interesting that our host posted this after his 41st birthday.  I snicker at people who think they are old when they are 41.  You have a long way to go and there are going to be so many new things in your life.  It might seem like you have lost your youth, but you are not even close.

My advice to my 20 year old self is to continue to listen to your heart, it will not lead you astray.  I have made several career and personal decisions since I was 20.  Some were easy to make, others took some deep reflection.  But when I listened to my heart, I feel like I made the right decision for me.

The second thing I would tell myself is don’t be afraid to try new things.  I am where I am today because I tried new things and moved to new places.  You don’t know if you will like something until you try it, so give it a shot.  If you don’t like it, that is ok.  You can move on.  But don’t have any regrets in the future because you did not try something when you had the chance.

One of the hardest decisions I had to make was to leave my life in Texas to move back to Colorado to be near my parents.  I had a great social life and really didn’t want to leave, but my dad was having heart surgery and I wanted to be with him.  I didn’t know how many years I would have to spend with him.  My dad lived 20 years after his heart surgery and during that time we built a better, stronger relationship.  And my friends in Texas are still my friends in Texas.  I would not change a thing because there is no way I would get that time with my dad back.

I won’t say that I have had a perfect life, and there are things I wish I would have done when I was younger like buy stock in Apple a long time ago and buy a house in Denver before the real estate boom began.  But I have some of the best people in my life that I feel that if I had changed anything earlier, these people may not be in my life right now.  And these people are very important to me…more important material items.

I See Her Success. I Want That Success. — Advancing Women

Quote

“You can’t be what you can’t see”. My job is to help bosses become aware, stop talking and start doing, to create a gender balanced and inclusive workplace. Celebrating Women is just one way of doing that.

via I See Her Success. I Want That Success. — Advancing Women

SSMS Keyboard Shortcuts – Menu Activation

short·cut: An accelerated way of doing or achieving something

Within computing, there are so many shortcuts keys that make working in a specific app or program easier.  We even talk in shortcuts…BRB, LOL, TTYL.  There are even a lot of keyboard shortcuts in SQL Server Management Studio (SSMS) that can make your job easier.

 

CTRL+N

Open a new query window

 

ALT

Move to the SQL Server Management Studio menu bar

ALT
Once on the menu bar, hit F (File), E (Edit), V (View), Q (Query), P (Project), D (Debug), T (Tools) or H (Help) to access the appropriate menu.

ALTPLUS

 

ALT+HYPHEN

Activate the menu for a tool component

ALTHyphen

 

SHIFT+F10

Display the context menu

SHIFTF10

 

CTRL+O

Display the Open File dialog box to open an existing file

CTRLO

 

CTRL+SHIFT+Q

Display the Query Designer: This is a quick and dirty way to start creating a new query.

CTRLSHIFTQ

 

CTRL+SHIFT+N

Display the New Project dialog box to create a new project

CTRLSHIFTN

 

CTRL+SHIFT+O

Display the Open Project dialog box to open an existing project

CTRLSHIFTO

 

CTRL+SHIFT+A

Display the Add New Item dialog box to add a new file to the current project

CTRLSHIFTA

 

SHIFT+ALT+A

Display the Add Existing Item dialog box to add an existing file to the current project

SHIFTALTA

 

ESC

Close a menu or dialog box, canceling the action

Undock Tabs and Windows in SSMS to Create More Workspace

With all of the tabs and windows open in SSMS, you can start running out of space to work.  Most people have multiple screens now so you can take advantage of your set-up.  Starting with SQL Server 2012, users are able to undock the tabs and windows in SSMS and move them to their other monitor, thus creating more space in the query window to work.  The more monitors you have, the more you can spread out.  The tabs and windows that can be undocked and moved outside of your SSMS instance include:

  • Bookmark Window
  • Error List
  • Object Explorer
  • Object Explorer Details Tab
  • Output
  • Properties Tab
  • Registered Servers Tab
  • Solution Explorer
  • Task List
  • Template Browser
  • Toolbox
  • Web Browser

 

Below is the ‘normal’ setup we are used to with SSMS.  Everything is tucked in nicely in our application.

OneScreen

 

When you undock the tabs and windows, you are able to create more workspace within your open query window as well as increase the size of the other windows you are using.

TwoScreens