Introduction
This article will introduce SQL Server, what it is, and why
to use it. We’ll examine when to use a database as well when not to. In
addition, we’ll introduce some of the key terms used with SQL Server.
What is a SQL Server?
SQL Server is a Microsoft product used to manage and store
information. Technically, SQL Server is a “relational database management
system” (RDMS). Broken apart, this term means two things. First, that data
stored inside SQL Server will be housed in a “relational database”, and second,
that SQL Server is an entire “management system”, not just a database. SQL
itself stands for Structured Query Language. This is the language used to
manage and administer the database server.
Relational Database
So now that SQL Server has been broken into two terms, a
“relational database” and a “management system”, let’s explore the first.
There is a very technical definition for what is or is not a relational
database. For details on this definition, do an internet search for the terms
“Edgar Codd” (who first proposed the relational model), or the terms “relational
database tuples”. While there are databases that are not relational, most of
the products on the market today (SQL Server, Oracle, MySQL, and MS Access to
name a few) are relational database products. This means that data is stored
inside a structure called a “Table”, which uses Rows and Columns (like a
spreadsheet). Unlike a spreadsheet though, the data rows stored inside a Table
is not in any particular order. To explain this last statement, think of a typical
spreadsheet column, if we wanted to sort the data in the first column of a
spreadsheet alphabetically, we would simply click the first column and then
would click the Sort button. The Rows of data would change their order so they
were now sorted the way we wished, as shown in the before and after images
below:
|
Before:
|
After:
|
Notice that the data actually moved. The name “Dave” moved
from the third row to the first. In a Database Table, this never happens. The
data would not be rearranged. If we wanted a sorted list like this, we would
ask the database to present us with a display copy of the data sorted the way
we wanted. This request to see the data is called a Query. So when we run a
Query, we see our own personalized display copy of the data, the actual data
items are not rearranged.
When discussing SQL Server, the term “Database”, can
sometimes be thrown around loosely, meaning different things to different
people. This happens because a database is a core, central component to SQL
Server. Therefore, the term has become a slang shortcut way of meaning SQL
Server as a whole. In actuality, SQL Server is RDBMS (Relational Database
Management System). Its job is managing databases.
A “Database” is a logical container object. It’s used for
storing like types of information together, to help with organization. Also, a
Database can be used as an easy security boundary. Usually, though not a rule,
databases separate applications from each other. For example, all the
Accounting system information may be contained in one database, while all the
Marketing information is in another. Again, this is not a SQL Server rule; you
could have all your corporate information contained in a single database. It
would be very confusing from an organization perspective, but SQL Server would
allow it. Conversely, you could separate each little group of information into
their own database, having hundreds or even thousands of databases inside a
single SQL Server. This scenario would be a management nightmare, but there
are no SQL Server rules preventing it. Usually, a common sense rule is applied
for determining what a database should contain. If the data and objects are
related to each other, and it would be helpful to apply security to the group
as a whole, than this is a good candidate to be its own database. A single SQL
Server can contain over 32,000 separate databases.
When a new database is created, two physical files are created
on the hard drive. One file holds all the objects and data, the other contains
a log of all the database changes. These files are proprietary to SQL Server
and cannot be opened by Word, Excel, Notepad, or any other application. The
file size of a database can be over 500,000 Terabytes.
Why use a Database?
So, now that we know SQL Server is an application for
storing information inside a “table” structure, let’s examine the reasons why
you would use a Database rather than a spreadsheet or some other program for
data storage.
Imagine you’re creating an application for storing sales
transactions. We’ll start by saving just a few columns of information such as
the Item Sold, Quantity, Price, Sale Date, and the Customer sold to. One of
the first storage options to consider is saving this information in a large
text file. There are benefits to text file saves such as quick write times. The
problem with text files is during a read, if the text file is large, it can
take quite a bite of time to open and scan the contents of the file looking for
what we want. Also, if we wanted to see all the sales to a specific customer,
the entire text file would have to be read, and every line occurrence of the
customer name would need to be saved in some temporary place until we had them
all. If we saved to a spreadsheet instead of a text file, we would have a Sort
feature built in. So we may be able to find all the sales to a specific
customer quicker, but again, if the file was large, opening the spreadsheet
could take a great deal of time.
In addition, what if we wanted to save the customers address
as well as their name, now instead of saving five pieces of information (Item
Sold, Quantity, Price, Sale Date, and the Customer sold to), we’ll be saving nine
columns of information (all the previous plus Address, City, State, and Zip). This
means were going to almost double the size of our text file or spreadsheet to
accommodate this additional customer data. However, if we used a database, we
could save the sales data and the customer address data in two separate places,
so the size of the sales data wouldn’t get any larger. When we wanted a report
showing the customers’ address, we could “Relate” or link the address data to
the sales data.
Not only would our sales information be smaller in a
database, but the actual address data would be smaller as well. In a
spreadsheet or text file, each sales line would include a complete address. In
a database, the address would only be recorded once. It wouldn’t matter if the
customer made 100 or 100,000 purchases. All sales records would point to, or “Relate”
to, that same single address line.
This “Relating” of data, so sizes stay small is one benefit
of a database. In addition, reading and writing to database is very fast. Plus,
many databases support having multiple users access the same data at the same
time. Something text files and spreadsheets don’t do. Also, the amount or
volume of information a database can store is almost unlimited, unlike a spread
sheet where there is a fixed number or rows that can be saved.
Why Not Use a Database?
There are some problems with using a database. First, time
must be taken to learn the new system. A database is not as intuitive as a
spreadsheet. In addition, if there is only a small amount of data that doesn’t
need to be changed over time, it’s probably simpler to save it in a file.
Unfortunately, most business problems are neither simple nor small, so a
database is usually the best tool for the job.
Management System
The second term in our SQL Server definition is “Management
System”. This means that SQL Server is more than just an application to hold
data; it also includes the tools needed to structure, manipulate, and manage
that data. In addition, when you install SQL Server, there are options for
including Report Writing tools, Data Import Export applications, Analysis
tools, and Management Interfaces.
Conclusion
SQL Server is a Relational Database Management System. Data
is stored is Tables consisting of columns and rows. Tables can be linked, or “Related”,
to one another. Tables and objects that belong to the same family or require
similar security are collectively stored in a Database.
