- World's Largest Collection of Interview Questions & Answers, FAQs, queries, sample papers, exam papers, dumps, what, why, how, where, when questions
Our Services
Get 9,000 Interview Questions & Answers in an eBook.

Get it now !!
Send your Resume to 6000 Companies
MS SQL Server Interview Questions & Answers - Learning Mode

MS SQL Server Interview Questions & Answers - Learning Mode

SQL Server is Microsoft's relational database management system (RDBMS). It is a full-featured databse primarily designed to compete against competitors Oracle Database (DB) and MySQL. Like all major RBDMS, SQL Server supports ANSI SQL, the standard SQL language. SQL Server offers the right editions to meet your needs, including Enterprise, Business Intelligence, Standard, Express, and Developer. SQL Server is a Microsoft product used to manage and store information. Technically, SQL Server is a ?relational database management system? (RDMS). Broken apart, this term means two things. First, that data stored inside SQL Server will be housed in a ?relational database?, and second, that SQL Server is an entire ?management system?, not just a database. SQL itself stands for Structured Query Language. This is the language used to manage and administer the database server.

Try MS SQL Server Interview Questions & Answers - Exam Mode

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 >> Next

Sort By : Latest First | Oldest First | By Rating

MS SQL Server Interview Questions & Answers - Learning Mode
Try MS SQL Server Interview Questions & Answers - Exam Mode
Question: What Is Index Fragmentation in MS SQL Server?

Answer: Index fragmentation is a phenomena where index contents are no longer stored continuously in the storage. When index contents become scattered in the storage, fragmented, performance on index will degrade.

If you want to see the fragmentation level of an index, you can use the system function called sys.dm_db_index_physical_stats() in the following format:

SELECT * FROM sys.dm_db_index_physical_stats(
database_id, table_id, index_id, DEFAULT, DEFAULT

The tutorial exe Source:
Question: What tool is available for ad hoc reporting by non IT end users in Reporting Services 2005?

Answer: Report Builder
Report Builder is bundled as part of Reporting Services 2005 and is targeted at end users needing to build ad hoc reports. BI Design Studio could be used, but typically requires more time and experience to use than the average end user will be willing to invest. Source:
Question: How To Delete All Rows with TRUNCATE TABLE Statement in MS SQL Server?

Answer: If you want to delete all rows from a table, you have two options:

* Use the DELETE statement with no WHERE clause.
* Use the TRUNCATE TABLE statement.

The TRUNCATE statement is more efficient the DELETE statement. The tutorial exercise shows you a good example of TRUNCATE statement:

SELECT COUNT(*) FROM ggl_rates


SELECT COUNT(*) FROM ggl_rates
0 Source:
Question: How To Divide Query Output into Multiple Groups with the GROUP BY Clause in MS SQL Server?

Answer: Sometimes, you want to divide the query output into multiple groups, and apply group functions on each individual groups. Dividing query output into multiple groups can be done with the GROUP BY clause. Here is the syntax of a SELECT statement with a GROUP BY clause.

SELECT group_level_fields FROM source_tables
WHERE search_condition
GROUP BY group_by_expression

* group_by_express - An list of columns to be used as the group criteria - Rows that have the same combination of v Source:
Question: How To Return the Top 5 Rows from a SELECT Query in MS SQL Server?

Answer: If you want the query to return only the first 5 rows, you can use the "TOP 5" clause. The TOP clause takes one parameter to indicate how many top rows to return. The following statements returns the first 5 rows and 3 rows from the ggl_links:

SELECT TOP 5 id, url, counts, tag FROM ggl_links

id url counts tag

102 972 DBA

105 960 DBA

10 Source:
Question: How To Provide Values to User Defined Function Parameters?

Answer: If a user defined function is created with parameters, you need pass values to those parameters when calling the function with one of two formats listed below:

expression... function_name(value_1, value_2, ... value_n)...

The tutorial exercise below shows you how to pass values to function parameters:


RETURN 'Welcome to '+@url;

PRIN Source:
Question: How To Use Subqueries with the IN Operators in MS SQL Server?

Answer: A subquery can be used with the IN operator as "expression IN (subquery)". The subquery should return a single column with one or more rows to form a list of values to be used by the IN operation. The following tutorial exercise shows you how to use a subquery with the IN operator. It returns all links with ids in the ggl_rates table.

SELECT id, url, tag, YEAR(created) As year

FROM ggl_links WHERE id IN (SELECT id FROM ggl_rates)


id url Source:
Question: What is a database table?

Answer: A table in database is a data object used to store data. Tables have the following features:

* Data is stored in a table with a structure of rows and columns.
* Columns must be pre-defined with names, types and constrains.
* A table object may have other associated data objects like, constrains, triggers, indexes, and statistics.

For example, a table called Address may have columns defined to store different elements of an address like, street number, city, country, postal co Source:
Question: How To Create a Large Table with Random Data for Index Testing in MS SQL Server?

Answer: If you want to see how index can be used to improve data search performance, you have to build some large tables, which requires large amount of random data. This tutorial exercise helps you to build a large table with pure random data:

USE GlobalGuideLineDatabase;

-- Drop the old table, if needed
DROP TABLE ggl_random;

-- Create a table with primary key
CREATE TABLE ggl_random (
id INT,
rand_integer INT,
rand_number numeric(18,9),
rand_datetime Source:
Question: How To Write a Query with a Full Outer Join in MS SQL Server?

Answer: If you want to query from two tables with a full outer join, you can use the FULL OUTER JOIN ... ON clause in the FROM clause. The following query returns output with a full outer join from two tables: ggl_links and ggl_rates. The join condition is that the id in the ggl_links table equals to the id in the ggl_rates table:

SELECT, l.url, r.comment FROM ggl_links l
FULL OUTER JOIN ggl_rates r ON =

id url comment
Question: Where are Notification Services event messages logged in SQL Server 2005?

Answer: In the Windows Application Log
Event messages are logged in the Windows Application log. Source:
Question: What is the simplest way to create a new database in MS SQL Server?

Answer: The simplest way to create a new database is to use the "CREATE DATABASE" statement with this syntax:

CREATE DATABASE database_name

For example, run this statement:


A new database called "YourDataBaseName" should be created in the SQL server. Of course, YourDataBaseName database should be empty at this moment - no tables. But it should have some other data objects automatically created by the server. Source:
Question: What Is SQL Language?

Answer: SQL, SEQUEL (Structured English Query Language), is a language for RDBMS (Relational Database Management Systems). During the 1970s, a group at IBM's San Jose research center developed a database system "System R" based upon Codd's model. Structured English Query Language ("SEQUEL") was designed to manipulate and retrieve data stored in System R. The acronym SEQUEL was later condensed to SQL. SQL was adopted as a standard by ANSI (American National Standards Institute) in 1986 and ISO (I Source:
Question: What Are the Basic Features of a Trigger in MS SQL Server?

Answer: Since a SQL Server trigger is a really an event handler, it has the following basic features similar to event handlers in other programming languages:

* Event Type - It must be declared to handle a specific event, like a DELETE event.
* Object Scope - It must be declared to handle events in a specific database object scope, like a specific table.
* Statement Body - It must have a statement body, a batch of statements to be executed when the specified event occurs in specified databa Source:
Question: How Can Windows Applications Connect to SQL Servers via ODBC?

Answer: One way of connecting a windows application to a SQL Server is to use ODBC drivers. The requirements to do this is summarized here:

* The SQL Server must have TCP/IP protocol enabled with a specific port number.
* The SQL Server Browser Service must be running on the server machine.
* A ODBC DSN must be created to represent the connection to SQL Server on the specified port number.
* The local windows application must be able to support ODBC API calls.

The diagram below sh Source:
Question: How To Use GO Command in "sqlcmd"?

Answer: "sqlcmd" is a command line client application to run Transact-SQL statements on a target SQL Server. When "sqlcmd" is started and connected to a SQL Server, it will start a new batch and prompt you to enter the first statement of the batch. You can enter one or more statements in one or more lines to form a Transact-SQL statement batch. To end a batch of statements and send it to the SQL Server for execution, you need to enter the GO command. The following "sqlcmd" tutorial session sends two bat Source:
Question: What Are the Advantages of Passing Name-Value Pairs as Parameters?

Answer: When calling a stored procedure defined with parameters, you can pass values to those parameters in two ways:

* Passing only values in the same order as parameters defined in the stored procedure.
* Passing name-value pairs in any order.

The advantages of passing name-value pairs to stored procedure parameters are:

* Makes the calling statement more readable - You know which value is passed to which parameter.
* Makes it possible to pass values in an order different th Source:
Question: What algorithm is used to encrypt the Database Master Key when it is created?

Answer: Triple DES
When you create a Database Master Key, it is encrypted using the password you supply with the Triple DES algorithm. Source:
Question: Can the Query Output Be Sorted by Multiple Columns in MS SQL Server?

Answer: You can specifying multiple columns in the ORDER BY clause as shown in the following example statement, which returns rows sorted by "tag" and "counts" values:

SELECT tag, counts, url, created
FROM ggl_links ORDER BY tag, counts

tab counts url created

DBA 390 2006-01-01

DBA 960 2005-01-01

DBA 972 2007-05-19

DEV 120 Source:
Question: How to set the current database in MS SQL Server?

Answer: Once you are connected to the SQL Server, you should select a database to work with and set it as the current database using the "USE" statement with this syntax:

USE database_name

The following tutorial example shows you how to set "YourDataBaseName" as the current database, and create a table in "YourDataBaseName":

USE YourDataBaseName
Changed database context to 'YourDataBaseName'.


SELECT name, ty Source:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 >> Next

India News Network
Latest 20 Questions
Payment of time- barred debt is: (a) Valid (b) Void (c) Illegal (d) Voidable
Consideration is defined in the Indian Contract Act,1872 in: (a) Section 2(f) (b) Section 2(e) (c) Section 2(g) (d) Section 2(d)
Which of the following is not an exception to the rule, "No consideration, No contract": (a) Natural love and affection (b) Compensation for involuntary services (c) Completed gift (d) Agency
Consideration must move at the desire of: (a) The promisor (b) The promisee (c) The promisor or any other party (d) Both the promisor and the promisee
An offer which is open for acceptance over a period of time is: (a) Cross Offer (b) Counter Offer (c) Standing Offer (d) Implied Offer
Specific offer can be communicated to__________ (a) All the parties of contract (b) General public in universe (c) Specific person (d) None of the above
_________ amounts to rejection of the original offer. (a) Cross offer (b) Special offer (c) Standing offer (d) Counter offer
A advertises to sell his old car by advertising in a newspaper. This offer is caleed: (a) General Offer (b) Special Offer (c) Continuing Offer (d) None of the above
In case a counter offer is made, the original offer stands: (a) Rejected (b) Accepted automatically (c) Accepted subject to certain modifications and variations (d) None of the above
In case of unenforceable contract having some technical defect, parties (a) Can sue upon it (b) Cannot sue upon it (c) Should consider it to be illegal (d) None of the above
If entire specified goods is perished before entering into contract of sale, the contract is (a) Valid (b) Void (c) Voidable (d) Cancelled
______________ contracts are also caled contracts with executed consideration. (a) Unilateral (b) Completed (c) Bilateral (d) Executory
A offers B to supply books @ Rs 100 each but B accepts the same with condition of 10% discount. This is a case of (a) Counter Offer (b) Cross Offer (c) Specific Offer (d) General Offer
_____________ is a game of chance. (a) Conditional Contract (b) Contingent Contract (c) Wagering Contract (d) Quasi Contract
There is no binding contract in case of _______ as one's offer cannot be constructed as acceptance (a) Cross Offer (b) Standing Offer (c) Counter Offer (d) Special Offer
An offer is made with an intention to have negotiation from other party. This type of offer is: (a) Invitation to offer (b) Valid offer (c) Voidable (d) None of the above
When an offer is made to the world at large, it is ____________ offer. (a) Counter (b) Special (c) General (d) None of the above
Implied contract even if not in writing or express words is perfectly _______________ if all the conditions are satisfied:- (a) Void (b) Voidable (c) Valid (d) Illegal
A specific offer can be accepted by ___________. (a) Any person (b) Any friend to offeror (c) The person to whom it is made (d) Any friend of offeree
An agreement toput a fire on a person's car is a ______: (a) Legal (b) Voidable (c) Valid (d) Illegal
Cache = 0.046875 Seconds