Please read the Introductory Post before continuing reading interview questions and answers.
Click here to get free chapters (PDF) in the mailbox
List of all the Interview Questions and Answers Series blogs
3) Interview Questions on SQL SERVER 2008
What are the basic functions for master, msdb, model, tempdb and resource databases?
The master database holds information for all the databases located on the SQL Server instance, and it is the glue that holds the engine together. Because SQL Server cannot start without a functioning master database, you must administer this database with care.
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.
The tempdb holds temporary objects such as global and local temporary tables and stored procedures.
The model is essentially a template database used in the creation of any new user database created in the instance.
The resource Database is a read-only database that contains all the system objects that are included in the 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 the Maximum Number of Index per Table?
For SQL Server 2005:
1 Clustered Index + 249 Nonclustered Index = 250 Index.
For SQL Server 2008:
1 Clustered Index + 999 Nonclustered Index = 1000 Index. (Read more here)
Explain Few of the New Features of SQL Server 2008 Management Studio
SQL Server 2008 Microsoft has upgraded SSMS with many new features as well as added tons of new functionalities requested by DBAs for long time.
A few of the important new features are as follows:
- IntelliSense for Query Editing
- Multi Server Query
- Query Editor Regions
- Object Explorer Enhancements
- Activity Monitors
Explain IntelliSense for Query Editing:
After implementing IntelliSense, we will not have to remember all the syntax or browse online references. IntelliSense offers a few additional features besides just completing the keyword.
Explain MultiServer Query:
SSMS 2008 has a feature to run a query on different servers from one query editor window. First of all, make sure that you registered all the servers under your registered server. Once they are registered, right click on server group name and click New Query.
e.g. for server version information,
SELECT
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('ProductVersion') AS ProductVersion
Explain Query Editor Regions:
When the T-SQL code is more than hundreds of lines, after a while, it becomes more and more confusing.
The regions are defined by the following hierarchy:
From first GO command to the next GO command.
Statements between BEGIN – END, BEGIN TRY – END TRY, BEGIN CATCH – END CATCH
Explain Object Explorer Enhancements:
In Object Explorer Detail, the new feature is Object Search. Enter any object name in the object search box and the searched result will be displayed in the same window as Object Explorer Detail.
Additionally, there are new wizards which help you perform several tasks, from a policy management to disk monitoring. One cool thing is that everything displayed in the object explorer details screen can be right away copied and pasted to Excel without any formatting issue.
Explain Activity Monitors:
There are four graphs
- percent; Processor Time,
- Waiting Tasks,
- Database I/O,
- Batch Requests/Sec
All the four tabs provide very important information; however, the one which I refer most is “Recent Expensive Queries.” Whenever I find my server running slow or having any performance-related issues, my first reaction is to open this tab and see which query is running slow. I usually look at the query with the highest number for Average Duration. The Recent Expensive Queries monitors only show queries which are in the SQL Server cache at that moment. (Read more here)
List of all the Interview Questions and Answers Series blogs
Reference: Pinal Dave (https://blog.sqlauthority.com), Twitter.
