SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 14 of 31

SQL Server Interview Questions and Answers
Print Book Available (207 Pages) | Sample Chapters

Please read the Introductory Post before continue reading interview question and answers.

List of all the Interview Questions and Answers Series blogs

3) 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 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 (http://blog.SQLAuthority.com)

6 thoughts on “SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 14 of 31

  1. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Complete Downloadable List – Day 0 of 31 Journey to SQLAuthority

  2. sir,
    This is Kasikumar,
    I have 50 millions of records.But i can’t retrieve the whole records quickly.Have you any solution . Please help me

    Like

  3. Hi Pinal,
    I have an issue with TRY… CATCH.
    Iam a developer and for delivery I have a query script which contains scripts of multiple objects like Creation of Stored Proc, Table, Indexes etc. where I first check for existence of object and if exist already I drop it and then I create it from the script. There is one table in which I insert data (which was supposed to be there in the client database but it is not).
    Now here is my problem:
    In TRY block when I insert data in that table control doesn’t goes to the CATCH block instead control jumps out of the END statement of the whole block. Below is the example
    BEGIN
    BEGIN TRY
    INSERT INTO table1 (col1, col2) VALUES(‘val1′,’val2′)
    INSERT INTO table1 (col1, col2) VALUES(‘val3′,’val4′)
    END TRY
    BEGIN CATCH
    ———- Exception handling.
    END CATCH
    END <—
    | control jumps after this END statemen instead of going into the
    CATCH block.

    Any help would be much appreciated.
    Thanks
    Puneet

    Like

  4. Pingback: SQL SERVER – Weekly Series – Memory Lane – #038 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s