BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
A guide to how to create databases in SQL Server Management Studio, including creating tables, indexing columns and using database diagrams to create relationships between tables.
- Creating a Database and Tables in SQL Server
- Creating Tables within a Database
- Creating Relationships or Joins between Tables
- Indexing Columns in a SQL Server Table (this blog)
Posted by Andy Brown on 19 November 2012
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Indexing Columns in a SQL Server Table
If you've been following this blog so far, you've now got two tables linked together with a relationship:
The two tables in our database, with the relationship linking them together.
The last thing I'll look at in this blog is how to speed up sorting of tables, by indexing columns.
What is an Indexed Column?
Imagine if you create and run a query showing all of the people in your database:
-- show all people's names
When you run this, you'll get the following results:
The people in surname order. Moira Anderson appears first, as her last name comes first in alphabetical order.
To produce these results, SQL Server has had to sort the rows by the LastName field. For 7 rows this will be almost instantaneous, but for 700,000 rows you could be waiting a while. That's where indexing comes in. You can think of indexing as keeping track for a column of where its value appears in alphabetical or numerical order in a table:
The index shows where each person would appear in the list if you were to sort the table by the LastName column.
As a guide, you will typically index about 4 or 5 columns in a table. These should include:
- columns that form one part of a relationship (the CompanyId column for our example); and
- columns that you frequently will want to sort by (the LastName column, probably).
The downside of indexing columns is that it takes longer to add new records (indexes also take up a fair amount of memory).
Creating an Index
To index the LastName column as above, first go into design view for the tblPerson table:
Right-click on the table whose column(s) you want to index, and choose Design.
Choose to edit the table's indexes (should this be indices? I think not in this context):
Click on the tool shown (if you can't see this Table Designer toolbar, use the menu shown below instead).
Instead of clicking on the tool shown, you could use the menu:
Choose this menu option as an alternative to the tool shown.
The dialog box which appears shows that you already have one index created - the primary key for the table - but you can click as shown to create another:
The primary key is automatically an index - but you can Add another index.
You can now specify how your index will behave, by completing the following steps (as shown in the diagram below):
- Choose which column or columns you want the index to affect (when you click in the box, you'll have the chance to change the column). Here the index will be for the LastName column.
- Choose whether duplicate values for the index are allowed. Here the LastName isn't unique (once you've entered one person called Smith in the table, you don't want to be restricted from entering any more). Typically only the primary key in a table is unique.
- Give your index a name. As for relationship names, index names have to be unique for a database. One way to ensure that this is the case is just to suffix the default name given (IX_tblPerson) with the name of the column being indexed.
- Optionally, type in a description of the index.
Here are these numbered steps:
Complete the numbered steps as shown above.
When you've finished choose Close, then save and close your table.
As mentioned earlier, columns which are part of relationships will not automatically be indexed by SQL Server - so you should also index the CompanyId column in the tblPerson table.