MSSQL Database

A database is a collection of tables that store data for an organization. For instance, a database can be designed to hold information related to a specific business need, such as finance data. Each table in a finance database would represent a specific finance object, such as income or revenue. Each table in a DB has columns or attributes that relate to table object and rows that contain specific data for that table.

A database file has both a logical filename used in Transact-SQL statements and a physical filename used by the operating system.

-Type of Database: Flat Model, Hierarchical Model, Relational Model and Network Model.

-System Database: Master, Model, MSDB, TempDB, Resource

-Database Files: Primary data files (.mdf), Secondary data files (.ndf), Log files (.ldf); Each DB has one primary data file and may include one or more secondary data files; the .mdf contains the system tables and file location information including secondary files and transaction log files.

-Database File Groups: Used to organize data files. SQL Server always contains at least one file group – the primary file group. The primary file group contains the primary data file. You can create your own file group called User Define. A data file, can be assigned only to one file group.

[Log files are never part of a file group. Log space is managed separately from data space.]

-Database Pages/Extents: The fundamental unit of data storage in SQL Server is the page, single row on a page is 8,060 bytes (8 KB) this does not include the data stored in the Text/Image page type. A physical record cannot span pages. A physical record has to fit in a page.

Extents are the basic unit in which space is managed. An extent is eight physically contiguous pages, or 64 KB. Two types of extents: Uniform extents are owned by a single object; all eight pages in the extent can only be used by the owning object. Mixed extents are shared by up to eight objects. Each of the eight pages in the extent can be owned by a different object.

-Database Objects: DB Object Name max 128 char long, first character letter, No Space and No Special Character. An RDBMS such as SQL

Server contains many objects. (The database, Filegroups, The transaction log, Tables, Diagrams, Views, Stored procedures, User Defined

Functions, Indexes, Assemblies, Reports, Full-text catalogs, User-defined data types, Roles, Users, Triggers, Constraints, Defaults)

-Transaction Logs: Record all transactions and the modifications made to a database. A DB has at least one transaction log (.ldf). Used to recovery of the database in the event of a system failure. When reading / writing data in the DB, SQL Server retrieves the data pages from disk

and loads them into an area of memory called the buffer cache. Each modification to a data page is made to the copy in the buffer cache prior to writing it to disk. A modified data page in cache for which the change has not been written to disk is called a dirty page. Because modifications are written to transaction log before being written to the disk, a transaction can be rolled back or rolled forward in event of a system failure. Each log record is identified by a log sequence number (LSN), and log records are written in sequential order. Each log record contains the ID of the transaction it records. All log records associated with a transaction are linked using backward pointers.

Leave a Reply

Your email address will not be published. Required fields are marked *