CoolInterview.com - 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
COOLINTERVIEW.COM DATABASE INTERVIEW QUESTIONS MS SQL SERVER INTERVIEW QUESTIONS 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: CoolInterview.com
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: CoolInterview.com
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
ORDER BY counts DESC
GO



id url counts tag

102 www.globalguideline.com 972 DBA

105 www.google.com 960 DBA

10 Source: CoolInterview.com
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:

DROP FUNCTION Welcome;
GO

CREATE FUNCTION Welcome(@url VARCHAR(40))
RETURNS VARCHAR(40)
AS BEGIN
RETURN 'Welcome to '+@url;
END;
GO

PRIN Source: CoolInterview.com
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
GO
5

TRUNCATE TABLE ggl_rates
GO

SELECT COUNT(*) FROM ggl_rates
GO
0 Source: CoolInterview.com
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.id, l.url, r.comment FROM ggl_links l
FULL OUTER JOIN ggl_rates r ON l.id = r.id
GO



id url comment
Source: CoolInterview.com
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: CoolInterview.com
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)

GO

id url Source: CoolInterview.com
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: CoolInterview.com
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: CoolInterview.com
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:

CREATE DATABASE YourDataBaseName
GO

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: CoolInterview.com
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: CoolInterview.com
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;
GO

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

-- Create a table with primary key
CREATE TABLE ggl_random (
id INT,
rand_integer INT,
rand_number numeric(18,9),
rand_datetime Source: CoolInterview.com
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
GO



tab counts url created

DBA 390 www.mysql.com 2006-01-01

DBA 960 www.oracle.com 2005-01-01

DBA 972 globalguideline.com 2007-05-19

DEV 120 Source: CoolInterview.com
Question: What Are Triggers in MS SQL Server?

Answer: A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. A trigger is really an event handler. SQL Server allows users to create triggers (event handlers) for 3 types of events:

* DML Event - Occurs when a DML (Data Manipulation Language) statement: INSERT, UPDATE or DELETE, is executed.
* DDL Event - Occurs when a DDL (Data Definition Language) statement: CREATE, ALTER, or DROP, is executed.
* Logon Event - Occurs when Source: CoolInterview.com
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: CoolInterview.com
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: CoolInterview.com
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: CoolInterview.com
Question: How To Locate and Take Substrings with CHARINDEX() and SUBSTRING() Functions?

Answer: Transact-SQL is not a language designed for manipulating strings, but it does have two simple functions to locate and take substrings: CHARINDEX() and SUBSTRING(). The tutorial exercise below assumes two given strings: 'Pages: 18' and 'Words: 3240'. The objective is to calculate the number of words per page. Read the script below to see how this is done by using CHARINDEX() and SUBSTRING() functions:

DECLARE @sPages VARCHAR(40), @sWords VARCHAR(40);
SET @sPages = 'P Source: CoolInterview.com
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: CoolInterview.com

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.015625 Seconds