Data Base

• SQL Server

  Database management system
• Database Files


  • SQL Server maps a database over a set of operating-system files
  • Data and log information are never mixed in the same file
  • Individual files are used only by one database
  • SQL Server databases have three types of files: Primary data files, Secondary data files, Log files
• Primary data file


  The starting point of the database and points to the other files in the database. Every database has one primary data file. File name extension is .mdf.
• Secondary data files


  Make up all the data files, other than the primary data file. Some databases may not have any secondary data files, while others have several. File name extension is .ndf.
• Log files


  Hold all the log information that is used to recover the database. There must be at least one log file for each database, although there can be more than one. File name extension is .ldf.
• Logical file name


  The name used to refer to the physical file in all Transact-SQL statements. Must comply with the rules for SQL Server identifiers and must be unique among logical file names in the database.
• OS file name


  The name of the physical file including the path. Must follow the rules for the operating system file names.
• File Size


  • Files are created by specifying their initial size
  • If size is missing, the size of the primary file in the model database will be used
  • File growth increment can also be specified
  • Every time the file is filled, it increases its size by the growth increment
  • Each file can also have a maximum size specified
• Database Filegroups


  Database objects and files can be grouped together in filegroups for allocation and administration purposes. Log files cannot be placed in a filegroup. No file can be a member of more than one filegroup. Tables, indexes, and large object data can be associated with a specified filegroup.
• Default filegroup


  One filegroup in each database is designated the default filegroup. When a table or index is created without specifying a filegroup, they will be created in the default filegroup. Only one filegroup at a time can be the default filegroup. If no default filegroup is specified, the primary filegroup is the default filegroup.
• Primary filegroup


  Contains the primary data file and any other files not specifically assigned to another filegroup. All pages for the system tables are allocated in the primary filegroup.
• User-defined filegroup


  Any filegroups that are specified by using the FILEGROUP keyword in a CREATE DATABASE or ALTER DATABASE statement.
• Create Database [Syntax]

  1. CREATE DATABASE database_name

  2. ON [PRIMARY]

  3. [<filespec> [ ,...n ]]

  4. [<filegroup> [ ,...n ]]

  5. LOG ON {<filespec> [ ,...n ]}
• Example 3


  • CREATE TABLE [database_name.[owner].] table_name
  • (<column name> <data type>
  • [[DEFAULT <constant expression>]
  • | [IDENTITY [(seed, increment)] ] ]
  • [NULL|NOT NULL]
  • [<column constraints>]
  • | [column_name AS computed_column_expression]
  • | [<table_constraint>]
  • [,...n]
  • )
  • [ON {<filegroup>|DEFAULT}]
  • [TEXTIMAGE_ON {<filegroup>|DEFAULT}]
• Example 4


  • ALTER DATABASE MyDB
  • MODIFY FILEGROUP MyDB_FG1 DEFAULT;
  • CREATE TABLE MyTable
  • ( cola int PRIMARY KEY, colb char(8) )
  • ON MyDB_FG1;
• Transaction Log


  SQL Server uses the Transaction Log to keep track of the changes made in the memory pages. Changes made on the memory pages are instantly written onto the transaction Log files (on disk). This is what makes the transaction Log file fundamental to SQL Server's Recovery process. This is why SQL Server's Transaction Log is called Write-Ahead-Log.
• When SQL Server needs to modify data:
• Write Ahead Log


  SQL Server's transaction log mechanism where changes made on the memory pages are instantly written onto the transaction Log files (on disk).
• System Databases


  • SQL Server provides four main system databases: master, msdb, model, and tempdb.
• master Database


  Stores all the system-level information of an SQL Server instance, including server configuration settings, logon accounts, startup stored procedure, and file locations of user databases. If the master database is unavailable, the SQL Server cannot start.
• msdb Database


  Used by the SQL Server Agent for scheduling jobs and alerts, and stores the history of the SQL Agent jobs.
• model Database


  SQL Server uses the model database as the template for creating other databases. When you create a new database, SQL Server copies the contents of the model database including database options to the new database.
• tempdb Database


  Stores temporary user objects that you explicitly create, temporary tables and table variables, and the internal objects that the database engine creates. SQL Server recreates the tempdb database every time it starts, and it cannot be backed up or restored.