Browse Articles

Introduction to SQL Server

31 Aug 2022
3 min read  

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

Release Name
1.0 for OS/2
SQL Server 1.0(16bit)
1.1 for OS/2
SQL Server 1.1(16bit)
4.21 for WinNT
SQL Server 4.21
SQL Server 6.0
SQL Server 6.4
SQL Server 7.0
SQL Server 2000
SQL Server 2000 (64-bit)
SQL Server 2005
SQL Server 2008
SQL Server 2008 R2

Sql Server Components

  1. 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.

  2. 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.

  3. 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.

  4. 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.

  5. 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.

  6. 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.

  7. 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.

Share Article
About Author
Shailendra Chauhan (Microsoft MVP, Founder & CEO at Scholarhat by DotNetTricks)

Shailendra Chauhan is the Founder and CEO at ScholarHat by DotNetTricks which is a brand when it comes to e-Learning. He provides training and consultation over an array of technologies like Cloud, .NET, Angular, React, Node, Microservices, Containers and Mobile Apps development. He has been awarded Microsoft MVP 8th time in a row (2016-2023). He has changed many lives with his writings and unique training programs. He has a number of most sought-after books to his name which has helped job aspirants in cracking tough interviews with ease.
Learn to Crack Your Technical Interview

Accept cookies & close this