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.

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.

Buffer management
SQL Server buffers pages in RAM to minimize disc I/O. Any 8 KB page can be buffered in-memory, and the set of all pages currently buffered is called the buffer cache. The amount of memory available to SQL Server decides how many pages will be cached in memory. The buffer cache is managed by the Buffer Manager. Either reading from or writing to any page copies it to the buffer cache. Subsequent reads or writes are redirected to the in-memory copy, rather than the on-disc version. The page is updated on the disc by the Buffer Manager only if the in-memory cache has not been referenced for some time. While writing pages back to disc, asynchronous I/O is used whereby the I/O operation is done in a background thread so that other operations do not have to wait for the I/O operation to complete. Each page is written along with its checksum when it is written. When reading the page back, its checksum is computed again and matched with the stored version to ensure the page has not been damaged or tampered with in the mean time.

History:-

SQL Server 2005

             SQL Server 2005, released in November 2006, is the successor to SQL Server 2000. It included native support for managing XML data, in addition to relational data. For this purpose, it defined an xml data type that could be used either as a data type in database columns or as literals in queries. XML columns can be associated with XSD schemas; XML data being stored is verified against the schema. XML is converted to an internal binary data type before being stored in the database. Specialized indexing methods were made available for XML data. XML data is queried using XQuery; SQL Server 2005 added some extensions to the T-SQL language to allow embedding XQuery queries in T-SQL. In addition, it also defines a new extension to XQuery, called XML DML, that allows query-based modifications to XML data. SQL Server 2005 also allows a database server to be exposed over web services using TDS packets encapsulated within SOAP requests. When the data is access over web services, results are returned as XML.

For relational data, T-SQL has been augmented with error handling features and support for recursive queries. SQL Server 2005 has also been enhanced with new indexing algorithms and better error recovery systems. Data pages are checksummed for better error resiliency, and optimistic concurrency support has been added for better performance. Permissions and access control have been made more granular and the query processor handles concurrent execution of queries in a more efficient way. Partitions on tables and indexes are supported natively, so scaling out a database onto a cluster is easier. SQL CLR was introduced with SQL Server 2005 to let it integrate with the .NET Framework.