564 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
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.
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.
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:
The downside of indexing columns is that it takes longer to add new records (indexes also take up a fair amount of memory).
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):
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|
25 Aytoun Street