This article will introduce SQL Server, what it is, and why to use it. Well examine when to use a database as well when not to. In addition, well 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.
So now that SQL Server has been broken into two terms, a relational database and a management system, lets 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:
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. Its 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, lets examine the reasons why you would use a Database rather than a spreadsheet or some other program for data storage.
Imagine youre creating an application for storing sales transactions. Well 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), well 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 wouldnt 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 wouldnt 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 dont 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 doesnt need to be changed over time, its 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.
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.
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.