Introduction to SQL Server

Print 3 min read

31 Aug 2022

Beginner

142K Views

Microsoft SQL Server is a Relational Database Management System(RDBMS) developed by Microsoft. It is designed to run on a central server, so that multiple users can access the same data simultaneously. Generally users access the database through an application.

SQL Server Release History

Version

Year

Release Name

1.0 for OS/2

1989

SQL Server 1.0(16bit)

1.1 for OS/2

1991

SQL Server 1.1(16bit)

4.21 for WinNT

1993

SQL Server 4.21

6.0

1995

SQL Server 6.0

6.5

1996

SQL Server 6.4

7.0

1998

SQL Server 7.0

8.0

2000

SQL Server 2000

8.0

2003

SQL Server 2000 (64-bit)

9.0

2005

SQL Server 2005

10.0

2008

SQL Server 2008

10.5

2010

SQL Server 2008 R2

Sql Server Components

Protocol Layer
Protocol layer implements the external interface to SQL Server. TDS is an application layer protocol, that is used to transfer data between a database server and a client.
Data Storage
The main unit of data storage is a database, which is a collection of data. The data in the SQL Server database is stored in primary data files with an extension .mdf and Secondary data files, with an extension .ndf extension are used to store optional metadata. Log files in SQL Server are recognized with the .ldf extension.
Buffer Management
SQL Server buffers pages in RAM to minimize disc Input/Output. A 8 KB page could be buffered in-memory and the set of all pages currently buffered is called the buffer cache. On the basis of available memory, SQL Server decides how many pages will be cached in memory. The buffer cache is managed by the Buffer Manager.
Logging and Transaction
SQL Server uses a transaction to make sure that any operation either totally completes or is undone if fails, but never leaves the database in an intermediate state. Any changes made to a page will update the in-memory cache of the page and simultaneously all the operations performed will be written to a log, along with the transaction ID. Each log entry is recognized by an increasing Log Sequence Number (LSN) which makes sure that no event overwrites another event. SQL Server makes sure that the log will be written onto the disc before the actual page is written back.
Concurrency and locking
when multiple users update the same data, or attempt to read data that is in the process of being changed by another user. In SQL Server we have two modes of concurrency control - pessimistic concurrency and optimistic concurrency. In pessimistic concurrency control, SQL Server controls concurrent access by using locks (shared or exclusive).

In Optimistic concurrency control, a new version of a row is created whenever the divrow is updated. Both the versions of the row are stored and maintained into a system-defined database Tempdb.
Data Retrieval
Data retrieval from SQL Server is done using T-SQL. SQL Server also allows us to write stored procedures to query the data.
SQL CLR (Common Language Runtime)
SQL Server 2005 also has a new component named SQL CLR via which it integrates with .NET Framework. When we write code for SQL CLR, data stored in SQL Server databases can be accessed by using the ADO.NET APIs like any other application that accesses SQL Server data.