Selecting records from an MS SQL database and improving query performance

Understanding simple select statements and how they are used to select data/records/rows from a table with an overview of how to use indexes as a go further exercise.

The Basics:

SQL is pretty much the same for most variations of SQL servers. For this tutorial I'll be explaining how a select statement is structured and I'll assume you're using MS SQL Server but in most cases the SQL will just work on any instance.

SQL stands for Structured Query Language and as such, it's pretty simple to read and understand.

A basic select statement consists of the word SELECT, a list of column names and the word FROM followed by a table name.

Tables are the things that hold your data in the database. This data is stored by giving each piece of data a label, and these labels are called columns.

Imagine a table on Microsoft Excel, you have all your columns of the table going from left to right with names in headers, for example: id, forename, surname, these essentially label the data below them and this is exactly how SQL works.

The data data within an SQL table, consists of rows, for the table above, a sample set of data or a row would contain an id, a forename and a surname, e.g. 1, Luke, Alderton. Now our table would look like this:

Now all that's left is to assume a name for our table, lets call it Persons.

An example of a SELECT statement to get all the data from our Persons table would be:

SELECT id, forename, surname FROM Persons

But say we wanted to get only a specific row from our table, we'd need to sell the SQL Server somehow, and this is where the WHERE statement comes in. The below shows how to select a single specified row from our table.

SELECT id, forename, surname FROM Persons WHERE id = 1

If you've worked with other languages before, you might be used to putting two equals signs for comparison, but SQL only requires the one.

And that's it! You can now easily see how a table in SQL is structured and how to build two different select statements for different uses.

Go Further:

Setting the id column as the Primary Key for the table and setting it to auto increment will ensure that the id column will increment as rows are added to the table. This is useful because you now no longer need to worry about adding the id yourself and you'll be sure that the id will be different every time.

Now that you have a Primary Key in your Persons table, we've also sped things up a bit, since adding id as the Primary Key causes SQL Server to add that column to an index, this makes the SQL server keep a catalogue of all the data in that column for easy searching. You won't notice much of a change for tables with only a few rows but when you start to build thousands, it will really make a difference. You can add indexes to almost any column to speed to searching, I recommend adding indexes for the most used queries on the table, for example, if your person had a username and your table was used on a website, chances are that instead of searching for people by id, you'd be searching for them by their username, so you'd add that to an index.