This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

SQL Server - create databases, tables and relationships Part four of a four-part series of blogs
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) This blog is part of the Wise Owl online SQL tutorial. We also run classoom-based courses in SQL.

SQL Server - create databases, tables and relationships

Part four of a four-part series of blogs

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)

This blog is part of the Wise Owl online SQL tutorial. We also run classoom-based courses in SQL.

Posted by Andy Brown on 19 November 2012

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

SELECT

PersonId,

FirstName,

LastName

FROM

tblPerson

ORDER BY

LastName

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.

Parts of this blog
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)

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Indexing Columns in a SQL Server Table

What is an Indexed Column?

Creating an Index

Head office

London

Manchester