Back To Basics: Tables, without them we have nothing to do

Tables are the core of any database platform.  Without tables we would be able to process data, but we would have no way to store the data.  In the simplest terms tables look like Excel sheets.  They both have columns and rows.  When you view a table in the client tools it looks much like an Excel sheet does.  While the basic concept is the same tables are very, very different that Excel sheets.  SQL Server is optomized to store large quanties and data and search through that data as quickly as possible.

Unlike Excel, tables have indexes.  Indexes are copies of the column which comprise the index.  The reason that we use indexes is to speed up the processing of the query.  Whle the index is a copy of the column, the copy within the index in sorted in order while the table is not.  Because the data is sorted SQL Server can search through the data much easier and there for faster.

To make this easier to understand think of an Excel sheet with 1000 rows of data in it.  Each row has a single value in column A.  Each value is a random number between 1 and 1000.  Now try to find all the rows with the value of 25.  You need to search down the column looking for the data.  This is how your table work.  When you tell SQL Server to search the table for the value of 25 using this statement SQL has to look at every record in the table.

SELECT *
FROM Table
WHERE Column1 = 25

Now when you create an index on this column think of sorting the Excel sheet in order.  Now find the records with the value of 25.  You can simply scan down the column and find the records and not look any further.  This is the same thing that SQL Server does when you us the same command but with the index.  SQL Server uses the index automatically, so no changes to code are required when you create the index.

Denny

Share

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Trust DCAC with your data

Your data systems may be treading water today, but are they prepared for the next phase of your business growth?