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.

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

Don’t Really Copy That Blank Line I Selected

Turning Off ‘Apply Cut of Copy commands to blank lines when there is no selection’

Copy – Paste – Copy – Paste.  Most of us can do this in our sleep, we have copied and pasted so much text over the years.  With that said, we have all been in the middle of copying text, we get on a roll and sometimes hit the wrong keys.  In SQL Server Management Studio (SSMS), and most Microsoft products, Copy is CTRL+C and Paste is CTRL+V.  The C and the V are next to each other on the keyboard and I have fat fingered a paste and hit CTRL+C instead.  Ugh…go back to the original text and start again.

There is a setting that Microsoft has turned on by default in SSMS that could help when you have fat fingers.  It is the ‘Apply Cut or Copy commands to blank lines when there is no selection.’  With the setting turned off, when you have copied something and want to paste it but you hit CTRL+C a second time in the blank space, it does not get rid of what you have copied.  Hit CTRL+V and your text will be copied.  To change the setting, go to the Text Editor settings and disable the setting.

Tools -> Options -> Text Editor -> Transact-SQL -> General

Apply_SQL

 

There are different types of editors in SSMS so make sure the setting is changed for both Transact-SQL and Plain Text if you use both.  The text editor is used to edit documents not associated with any particular language.  For instance, if you open a .txt file in SSMS, you need to make sure that the setting is disabled for the Plain Text editor for your fat fingers to be saved.

Tools -> Options -> Text Editor -> Plain Text -> General

Apply_TXT

The box where you change your settings look very similar, so if you make the changes and it doesn’t work, double check which setting that you changed.  You want to make sure you have disabled the option for the right type of editor.  You might want to disable it in both places.

If you have not or do not want to disable this setting, use the Clipboard Cycle Ring feature to find the text that you copied.

T-SQL Tuesday #114 – Puzzle Me This

The topic for this month’s T-SQL Tuesday is a Puzzle Party hosted by Matthew McGiffen.  We were given the challenge to write a blog post combining puzzles and T-SQL.

 

One of the first assignments I had in my programming class in college (after writing Hello World) was to assign seats on the airplane.  A lot of people who have taken any type of programming class have probably also completed this assignment.  It was kind of a standard assignment for beginning programming.  This was always one of my favorite assignments, and working on the assignment was when I really got hooked on computers and programming.

 

For this month’s blog, I am taking the airline seat assignment task and make it more relevant to the SQL Family.  My puzzle is to create a schedule of sessions for one day at PASS Summit.  I pulled the session data from last year’s PASS Summit to use for this puzzle.  (PASSSchedule) Within the Excel spreadsheet, there is one sheet for sessions, one for rooms, and one for time slots.  Save each sheet as either .csv or .txt files to import into SQL .

In this challenge, you are responsible for assigning a room and time slot for each session.  There are a few rules you have to follow.  Everything is outlined below.

 

The Challenge:

  • There are 4 time slots and 16 rooms which allows for 64 session slots available
  • There are 49 sessions from one of 7 different technology focus areas
    • Advanced Analytics
    • Application Development
    • Big Data and IoT
    • Business Intelligenge and Data Warehousing
    • Cloud Solutions
    • Database Administration and Development
    • Other (Professional Development)
  • There must be at least one session from each technology focus per time slot for all but Big Data and IoT sessions
  • There can be no two sessions in the same time slot for Big Data and IoT
  • There can be no more than 2 Business Intelligence and Data Warehousing sessions per time slot
  • There can be no more than 2 Application Development sessions per time slot
  • There can be no more than 2 Cloud Solutions sessions per time slot
  • There can be no more than 2 Other (Professional Development) sessions per time slot
  • There can be no more than 3 Advanced Analytics sessions per time slot
  • There can be no more than 4 Database Administration and Development sessions per time slot
  • The Speaker Idol session must be held in the 4:45pm-6:00pm time slot in the TCC Skagit 4 room

 

When you are finished, you should have a complete schedule.  With 64 session slots and only 49 sessions, there will be some empty session slots in the schedule.

 

Good luck!

 

 

Cycle Clipboard Ring

Standard in most Microsoft products are the commands CTRL+C to copy data and CTRL+V to paste data.  In SSMS, there is a tool called Cycle Clipboard Ring that allows the user to cycle through the last 20 items that have been saved to the clipboard.  Once you have gone through all of the items, you will start again through the items, thus the name ring.

In SSMS, open a new query window and use CTRL+SHIFT+V to paste the Cycle Clipboard Ring’s current item.  You also can access Cycle Clipboard Ring from the Edit dropdown menu.  However, Cycle Clipboard Ring may not appear in the Edit dropdown menu until it has been used once.

CycleClipboardRing

 

Repeatedly press CTRL+SHIFT+V to cycle through each entry within the Clipboard Ring until you find the item you want to use.  Each time you press CTRL+SHIFT+V, the current item replaces the last entry pasted.

Shrinking a large database with limited outage windows — SQL Studies

Quote

Yes, I realize you shouldn’t shrink your database (data files or log files), but I recently had a case where it was completely appropriate to do a shrink. We had a table that was almost a terabyte on its own. It was decided to put in an archive process that got rid of 3/4 of […]

via Shrinking a large database with limited outage windows — SQL Studies

Results to Grid

What is Results to Grid and what can it do for you?  Results to Grid are Query Results options in SQL Server Management Studio (SSMS) that can help users customize their query results in a variety of ways that can help make users more efficient.  Some of these might be little changes, but when used often throughout the day, they can make a big difference.  Once you change the setting, you will need to open a new query window for the change to go into effect.  These changes can be made at:

Tools > Options > Query Results > Results to Grid

ResultsToGrid

 

Include Query in the Result Set

The default for Include Query in the Result Set is off.  When toggled on, the query is included in the messages tab of the results.

QueryInResult

Include Column Headers When Copying or Saving the Results

The default for Include Column Headers When Copying or Saving the Results is off.  When it is toggled on, the column headers are copied or saved with the output.  To copy the results, you would highlight the upper left corner of the result set and right click.  You can select Copy or Copy with Headers.  (You can also use CTRL+C (copy) or CTRL+SHIFT+C (copy with headers).)  Either way, the column headers will be copied along with the data.

When you save the query results (Save Results As…), there is not an option to include headers from the menu.  To include columns headers when saving data, this feature must be toggled on and it must be turned on prior to running the query for the Column Headers to be included in the output.

ColumnWhileCopying

 

Retain CR/LF on Copy or Save

When copying information into Notepad, the carriage return and line feeds from the text are not always carried over.  In the example below, I will show the difference in the results when this option is toggled on and off.

Note the two queries.  They are slightly different to make it easier to identify the results.  The query on the left has the Retain CR/LF on Copy or Save toggled on, the one on the right has the feature toggled off.  Looking at the results of each query, they are the same in the Results tab.  The difference occurs when you copy the data into another format, such as Notepad.

CR_LF

Discard Results After Execution

Really…discard the results after execution of a query.  Isn’t the point of running the query to get the results of the query?  Not always.  Suppose you need the results of the execution plan and not necessarily that of the data.  That would be one situation where you would not care about the result set.  By toggling on this option, you will save on memory but not CPU or IO.  If you are working with a large data set, this could also save you time as you do not have to wait for the dataset to load before getting the execution plan.  This is primarily a tool to use for troubleshooting.

DiscardResults

Display Results in a Separate Tab

The default setting in Query Editor is with Editor pane at the top and the Results and Messages pane at the bottom of the window.  Users can also set up the Results and Messages panes to appear at the top of the query window, allowing more space for results.  With the results in a separate tab, the tabs are on the same horizontal plane and it is easier to switch from the Editor to the Results to the Messages because they are all together.  To make this change, go to the following and toggle on ‘Display Results in a separate tab’.

The images below show the default Results pane at the bottom of the Query Window (Left) and with the results in a separate tab (Right).

SeparateTab

 

There is a setting under the Display Results in which you can toggle on: ‘Switch to the results tab after the query executes’.  With the display panes in separate, horizontal tabs, you can’t see the results after running a query like in the default format without switching to the Results pane.  This feature will save you that step of running the query then tabbing to the Results pane.  The Results tab will automatically open when executing the query.  The two options really go hand-in-hand.

 

AutoRecover in SSMS

 

Periodically there is a crash, power surge, or sudden reboot of your computer.  Of course, you had SQL Server Management Studio (SSMS) open and you were working on something important.  That seems to be the only time there is a crash/reboot.  You can lose work that that was open in SSMS but has not saved.  There is an AutoRecover feature in SSMS so all may not be lost.  The AutoRecover default settings in SSMS are:

Save AutoRecover information every 5 minutes

Keep AutoRecover information for 7 days

With the default settings, you can lose up to 5 minutes of work on a sudden reboot.  The recovered information will be kept for 7 days so there is some time to get your files.  These settings can be modified, changing how often auto recover happens and how long the information is retained.  It depends on the criticality of things that you work on as to how often you would want AutoRecover to save your scripts.  This feature can be turned off but it is not recommended.  To change the settings, go to:

Tools -> Options -> Environment -> AutoRecover

AutoRecover

 

When opening SSMS after that unexpected shutdown, SSMS tries to recover the unsaved queries and prompts a dialog box to the user.  You have the choice to recover files at that time.  If you skip over that step or the dialog box does not appear on re-open, the AutoRecovered files can be found on the server.  Navigate to the folder where the backup files are located and recover files
from there.

AutorecoverLocation

 

The settings in SSMS will determine what is available to recover and when it was last saved.  You don’t want AutoRecover to constantly run, but it needs to be set to a time that is appropriate for you and your job.