Michela Quecchia is a third year at KCL and a cancer survivor with an interest in healthcare and the environment. Currently juggling OCD and philosophy studies. [Featured Image: Three people with purple hair and green shirts reading they, them, theirs against a light purple background.] Whether you are a student or a professional, or simply […]
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.
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.
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.
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.
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.
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.
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]
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]
Generate Scripts Wizard
This option provides the most options for selecting what is scripted and what is included.
Right click database -> Tasks -> Generate Scripts
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).
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.
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.
Click Next to review your scripting selections.
Click Next to save/publish scripts.
Click Finish to close the Wizard.
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.
“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.
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.
Open a new query window
Move to the SQL Server Management Studio menu bar
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.
Activate the menu for a tool component
Display the context menu
Display the Open File dialog box to open an existing file
Display the Query Designer: This is a quick and dirty way to start creating a new query.
Display the New Project dialog box to create a new project
Display the Open Project dialog box to open an existing project
Display the Add New Item dialog box to add a new file to the current project
Display the Add Existing Item dialog box to add an existing file to the current project
Close a menu or dialog box, canceling the action
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
- Properties Tab
- Registered Servers Tab
- Solution Explorer
- Task List
- Template Browser
- Web Browser
Below is the ‘normal’ setup we are used to with SSMS. Everything is tucked in nicely in our application.
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.
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
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
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.
Working in SSMS, you are likely to have multiple SQL Server query windows open and you need to maneuver through the different tabs. You can click on the query window that you want to open if there are just a few tabs open . Depending on the size of your screen, you might see only 4 or 5 open query windows and it always feels like the one you want is not in that top group. There are a few ways to maneuver through your query windows, and everyone has their favorite. This is usually based on the way you learned to maneuver through the query windows and/or the type of work that you do. Some people love to use the mouse, other don’t take their hands off the keyboard. There is more than one way to skin a cat and more than one way to maneuver through your open query query windows .
CTRL + F6
When you are in SSMS, pressing CTRL + F6 will cycle through the open query windows. Continue to press CTRL + F6 until you get to the query window that you need. Pressing CTRL + SHIFT + F6 will cycle through the open query windows in the reverse order.
CTRL + TAB
Use CTRL + TAB to switch between open query window tabs. When you first hit CTRL + TAB, it will open a temporary window listing all of the open queries. While continuing to hold down the CTRL button, click on the TAB button to get to the desired query window. Using SHIFT + CTRL + TAB (holding the SHIFT + CTRL and clicking on the TAB button) will take you through the list in reverse order.
Another way to get around in your open query windows is to click in the dropdown box to the right of the open query windows with your mouse to produce a list of all of the open files. Scroll within the list and select a specific query window you need.
ALT + W
Press ALT + W to open the Windows tab from the menu bar. At the bottom is a list of open query windows. Use the up and down arrows to maneuver through the list and select the query window that you want to view.
Through the Windows menu, it is easy to clean up your open query windows. Hit ALT + W to open the Windows menu then select W or scroll down to Windows…. This will open a window all of your open query windows. You can select just one open query window or many using the CTRL key. With one, you can Activate, Save, or Close the query. If you select more than one, you close those specific query windows.
CTRL + ALT + L
Pressing CTRL + ALT + L will open the Solution Explorer on the right side of SSMS. Once in the Solution Explorer, begin typing the query name and the cursor will go to that query. This is not as handy if you have not saved or renamed the queries and they are still listed as SQLQuery1.sql, SQLQuery2.sql, SQLQuery3.sql, etc. You can also use the up and down arrows to maneuver through the Solution Explorer to find the query window that you need to access.
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.
- 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.