MS SQL Server interview questions

• Describe SQL Server Databases Architecture.

SQL database is divided into logical and physical components.

• Logical Components

1. Database objects,
2. Login,
3. Users,
4. Roles,
5. Groups.
   

• Physically components exist as two or more files on disk.

Physically files can be of three types.

1. Primary data files;starting point of a database. It also points to other files in database. Extension: .mdf
2. Secondary data files:All data files except primary data file is a part of secondary files. Extension: .ndf
3. Log files:All log information used to recover database. Extension: .ldf

• Every SQL Server instance has primarily 4 system databases:master, model, tempdb and msdb

• A single SQL Server instance is capable of handling thousands of users working on multiple databases.

• What are the basic functions for master, msdb, model, tempdb and resource databases?

1. The master database holds information for all databases located on the SQL Server instance and is the glue that holds the engine together. Because SQL Server cannot start without a functioning master database.
2. The msdb database stores information regarding database backups, SQL Agent information, DTS packages, SQL Server jobs, and some replication information such as for log shipping
3. The tempdb holds temporary objects such as global and local temporary tables and stored procedures
4. The model is essentially a template database used in the creation of any new user database created in the instance.
5. The resource Database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata.

• what is extent and page ? the relation between them

Extent is a basic unit of storage to provide space for tables. Every extent has a number of data pages. As new records are inserted new data, pages are allocated. There are eight data pages in an extent. So as soon as the eight pages are consumed, it allocates a new extent with data pages.


References:
http://www.dotnetspider.com/resources/21249-SQL-Data-Base.aspx
http://www.careerride.com/SQL-Server-Databases-Architecture.aspx