Tanubhrt.This.Side

SQL Server Storage

The storage engine is responsible for storing data onto the disk and retrieving data from the disk. When a database is created two types of files are made –

1  .      Data files

1.1  Primary data files

1.2  Secondary data files

2   .    Log files

Data Files – Data files are used to store objects like tables, procedures etc. Data files are then in turn of two types –

1.                            Primary Data Files – These are the main repository of the database. They store information of the objects like stored procedures, functions, indexes which are frequently used. Extension – .mdf
2.                         Secondary Data Files – These are the optional files and can contain objects like primary data files. They contain information almost static in nature and so can be put into different disk drives. Extension – .ndf

• The .mdf file acts as the starting point of the database and can point to other files in the database.

Log Files – Log files contain information about database transactions and modifications like insert, update and delete. The information is required to do a rollback or commit on the primary file. A database must always have a log file and has an extension .log. A database may choose to have more than one log file.

SQL Server Components

SQL server consists of different databases. Each database has certain components. These are –

• System tables
• User defined tables
• Stored Procedures
• Functions
• Views
• Constraints

SYSTEM TABLES – System tables are the ones that are created by the system used for defining the database and are specified in the catalog of database server.

USER-DEFINED TABLES- These are created by the user.

STORED PROCEDURES – Stored Procedures are the pre-compiled objects stored in the database. They are of three types –

1.                  System Stored Procedure – These are the built in procedures available in the SQL server.
2.                User defined procedure – These are created by the user. These are DLL’s (Dynamic Link Library) which can be loaded dynamically and run. They are called and executed like a stored procedure.
3.               Extended procedures- These allow the creation of external routines using a programming language like C.

FUNCTIONS

Functions are pre-compiled objects like procedures and are of two types-

1. In-built functions
2. User defined Functions

2.1  Scalar Valued

2.2  Table Valued

Happy Learning 🙂