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:

id forename surname
1 Luke Alderton

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.


By Luke Alderton at 11 Jul 2016, 12:51 PM

Tags: Database,SQL

Comments

Post a comment
Sit tight...
We're adding your comment.
Thank you.
Your comment has been added.
There's been a problem.
Please try again later.
Share with
Tags
Latest Comments
By dipbleds on Raspberry Pi - Bluetooth using Bluecove on Raspbian
12 Dec 2017, 21:29 PM
By Ich on Replacing the Xamarin Header/Navigation bar with a custom view/template
26 Nov 2017, 17:11 PM
By Ravi Motha on My experience at Umbraco UK Festival 2017
12 Dec 2017, 08:39 AM
By Borges on How to add a Xamarin Forms Loading Screen/Overlay
7 Nov 2017, 19:11 PM
By Rutul Mehta on Auto/Custom height on Xamarin Forms WebView for Android and iOS
4 Nov 2017, 03:15 AM
By Pablo on Xamarin MasterDetailPage has large margin/padding at top
26 Oct 2017, 15:31 PM
By faiza on Using MaryTTS or OpenMary in Java
22 Oct 2017, 12:54 PM
By Nietoperz on Using MaryTTS or OpenMary in Java
29 Sep 2017, 14:34 PM
Categories
App Development
Event
Game Development
Mapping
Modelling
Programming
Review
Robotics
Tutorial
Web Development