> > Microsoft SQL Server MCQs > Basic Microsoft SQL Server MCQs

Basic Microsoft SQL Server MCQ

1.
What will be the output of the following SQL query? SET SHOWPLAN_TEXT ON GO SELECT * FROM abcsite.abctable GO

It will show the query plan and the estimated cost of running

It will show all the rows of the "abctable" table

It will show all the rows of the "abctable" table and the table structure

It will show the identities and Constraints applied on this table

Answer

Correct Answer: It will show the query plan and the estimated cost of running

Note: This Question is unanswered, help us to find answer for this one

2.
Which of the following "insert queries" will be inserted in the XML field?
The creation query of the table is:
CREATE TABLE Branches(recordID int, description XML)

INSERT Into Branches(recordID, description) VALUES(1, '<log><application>Sales</application><description>The connection timed out.</description></log>')

INSERT UniversalLog(recordID, description) VALUES(1, 'database unavailable')

INSERT Into Branches(recordID, description) VALUES(1, '<log><application>Sales<description>The connection timed out')

XML cannot be inserted into database

XML can be inserted but syntax in all queries is wrong

Answer

Correct Answer: INSERT Into Branches(recordID, description) VALUES(1, '<log><application>Sales</application><description>The connection timed out.</description></log>')

Note: This Question is unanswered, help us to find answer for this one

3.
Consider the following tables:

Customers
- - - - - - - -
Customerid
Customername
Address

Orders
- - - - -
Orderid
Customerid
Orderdate
Comments
What will be the query to fetch Customername and 1st Orderdate for order placed by each customer?

Select Customers.Customername,(Select Min(Orderdate) From Orders Where Customers.Customerid=Orders.Customerid) From Customers

Select Customers.Customername,(Select Max(Orderdate) From Orders Where Customers.Customerid=Orders.Customerid) From Customers

Select Customers.Customername,(Select Orderdate From Orders Where Customers.Customerid=Orders.Customerid) From Customers

Select Customers.Customername,Min(Orders.Orderdate) From Orders,Customers Where Customers.Customerid=Orders.Customerid

None of these

Answer

Correct Answer: Select Customers.Customername,(Select Min(Orderdate) From Orders Where Customers.Customerid=Orders.Customerid) From Customers

Note: This Question is unanswered, help us to find answer for this one

Which of the following commands will be used to see the structure of the table named 'salary'?

sp_helptext salary

sp_define salary

sp_help salary

define salary

Answer

Correct Answer: sp_help salary

Note: This Question is unanswered, help us to find answer for this one

We are creating Stored Procedure in Database and using "sp_" prefix in its name. Which of the following statements is correct in accordance with the performance of the Stored Procedure execution?

The prefix will speed up the execution as it is in cache and will always get executed with high priority

The prefix will slow down the execution as the prefix is reserved for system stored procedures

The performance will not be affected as "sp_" is just a prefix for a stored procedure

The prefix will speed up the execution as prefix "sp_" is used for stored procedures by default

Answer

Correct Answer: The prefix will slow down the execution as the prefix is reserved for system stored procedures

Note: This Question is unanswered, help us to find answer for this one

The commands GRANT and REVOKE are examples of :

DDL (Data Definition Language)

DML (Data Manipulation Language)

TCL (Transaction Control Language)

DCL (Data Control Language)

Answer

Correct Answer: DCL (Data Control Language)

Note: This Question is unanswered, help us to find answer for this one

What exactly is the Full-Text Catalog?

The Full-Text catalog is an index for searching specific keywords

The Full-Text catalog is a file which contains full-text indexes

The Full-Text catalog is basically an inbuilt function for checking the validation of a text

The Full-Text catalog is a system trigger for checking text validation

Answer

Correct Answer: The Full-Text catalog is an index for searching specific keywords

Note: This Question is unanswered, help us to find answer for this one

Consider the following table "Students":

Students

- - - - - -

Name Hobbies

John Driving

Miller Fishing

David Writing

Robert Singing

What will be the output of the following query?

Select Substring(Hobbies,3) From students Where name= "john"

Dri

ivi

iving

ving

It will give an error

Answer

Correct Answer: It will give an error

Note: This Question is unanswered, help us to find answer for this one

Why are Joins generally better than the Correlated sub-query?

The Correlated sub-query scans the whole table

Joins pick up the rows according to the condition of the queries

The Correlated sub-query creates a nested loop

The Correlated sub-query makes the statement complex

Answer

Correct Answer: The Correlated sub-query creates a nested loop

Note: This Question is unanswered, help us to find answer for this one

10.

Starting from the slowest, arrange the following operators in the order in which they produce results in "SQL WHERE" query.

1. >, >=, <, <=

2. <>

3. =

4. LIKE

1,4,3,2

4,1,2,3

3,2,1,4

2,4,1,3

Answer

Correct Answer: 4,1,2,3

Note: This Question is unanswered, help us to find answer for this one

11.

Which one of the following statements is correct?

You can create DDL triggers

You can create DML triggers

Both a and b are correct

Neither a nor b is correct

Answer

Correct Answer: Both a and b are correct

Note: This Question is unanswered, help us to find answer for this one

12.

State whether True or False.

Triggers can be created in Assemblies.

True

False

Answer

Correct Answer: True

Note: This Question is unanswered, help us to find answer for this one

13.

What exactly is a Super key?

A Super key is a column or a set of columns that uniquely identifies rows in a table

A Super key checks the number of columns in a table which have non-null values

A Super key is used to match columns in other tables

A Super key checks the number of rows in a table which have non-null values

Answer

Correct Answer: A Super key is a column or a set of columns that uniquely identifies rows in a table

Note: This Question is unanswered, help us to find answer for this one

14.

__________ is a container that holds tables, views, procedures, functions and so on.

Instance

Cluster

Metadata

Schema

Answer

Correct Answer: Schema

Note: This Question is unanswered, help us to find answer for this one

15.

Which one of the following statements about Indexes is wrong?

Indexes are created in an existing table to locate rows more quickly and efficiently

It is possible to create an index on one or more columns of a table

A table is scanned when index is not available

Indexes can only be implemented on integer datatypes

Answer

Correct Answer: Indexes can only be implemented on integer datatypes

Note: This Question is unanswered, help us to find answer for this one

16.

Which of the following mechanisms is used by "ENCRYPTION by passphrase" to encrypt the data in the SQL SERVER 2005?

DES (Data Encryption Standard)

AES (Advanced Encryption Standard)

user defined password

Answer

Correct Answer: DES (Data Encryption Standard)

Note: This Question is unanswered, help us to find answer for this one

17.

Which command is used to create User defined Data Type in the SQL SERVER 2005?

sp_addtype

Create Type

Create Data Type

None of these

Answer

Correct Answer: sp_addtype
Create Type

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

18.

Which of the following is not a Data type in the SQL SERVER 2005?

ntext

nvarchar(n)

xml

nsmallmoney

Answer

Correct Answer: nsmallmoney

Note: This Question is unanswered, help us to find answer for this one

19.

Which is the best method to handle errors and deadlocks?

Errors and deadlocks can be handled by using stored procedures

Errors and deadlocks can be handled by using cursors

Errors and deadlocks can be handled by using Try and Catch constructs

Errors and deadlocks can be handled by using inline queries

Answer

Correct Answer: Errors and deadlocks can be handled by using Try and Catch constructs

Note: This Question is unanswered, help us to find answer for this one

20.

Columns are called _____________ of a table in a relational model.

Tuples

Elements

Objects

Attributes

Constraints

Answer

Correct Answer: Attributes

Note: This Question is unanswered, help us to find answer for this one

21.

What is the maximum storage capacity of XML data type in SQL SERVER 2005?

1GB

2GB

4GB

Answer

Correct Answer: 2GB

Note: This Question is unanswered, help us to find answer for this one

22.

Consider the following table:

Order

- - - - -

Orderid

Customerid

Orderdate

Comments

Select ISNULL (Comments, 'No comments') AS comments From Order

What will be the output of the above query?

The output will always be "No comments" irrespective of the value in the Comments field

The output will be null in all the rows

The output will be "No comments", when the database entry is Null

The output will be "No comments", when the database entry is Not Null

This query is erroneous

Answer

Correct Answer: The output will be "No comments", when the database entry is Null

Note: This Question is unanswered, help us to find answer for this one

23.

The table Comments has an auto incremented column called "Commentid" and a "UserComments" column, both of which store the comments of the users visiting the site. What will be the correct query to display the latest comments first?

Select usercomments From Comments Order by commentid

Select usercomments From Comments Order by commentid desc

Select usercomments From Comments Order by usercomments

Select usercomments From Comments Order by usercomments desc

Answer

Correct Answer: Select usercomments From Comments Order by commentid desc

Note: This Question is unanswered, help us to find answer for this one

24.

Which of the following statements is wrong?

Truncate is faster than Delete

Truncate is a DDL command

Truncate retains the identity of the table

Truncate cannot be rolled back

Truncate removes all rows from the table

Answer

Correct Answer: Truncate retains the identity of the table

Note: This Question is unanswered, help us to find answer for this one

25.

We have a table named "Customer" which has a column named "Customerid" having datatype Integer. We need to fetch the output with column title: 'Customer id is (value of customerid from the table)'. What will be the query?

Select 'Customer id is' + customerid From Customer

Select 'Customer id is' + CAST(customerid AS varchar) From Customer

Select 'Customer id is' + CAST(customerid AS integer) From Customer

Both a and b

None of these

Answer

Correct Answer: Select 'Customer id is' + CAST(customerid AS varchar) From Customer

Note: This Question is unanswered, help us to find answer for this one

26.

Which of the following are correct?

With DDL, you can create and remove tables, schemas, domains, indexes and views

Select, Insert and Update are DCL commands

Grant and Revoke are DML commands

Commit and Rollback are DCL commands

Answer

Correct Answer: With DDL, you can create and remove tables, schemas, domains, indexes and views

Note: This Question is unanswered, help us to find answer for this one

27.

You have a table "engineers" with the following table structure:

enggid int(4)

deptname varchar(50)

salary int(4)

You want to display the minimum and maximum salaries of the individual departments. Which of the following queries will fetch the desired results?

select deptname, min(salary) as Minimum, max(salary) as Maximum from engineers

select deptname, min(salary) as Minimum, max(salary) as Maximum from engineers group by deptname

select deptname, min(salary) as Minimum, max(salary) as Maximum from engineers group by salary

select deptname, min(salary) as Minimum, max(salary) as Maximum from engineers order by deptname

None of these

Answer

Correct Answer: select deptname, min(salary) as Minimum, max(salary) as Maximum from engineers group by deptname

Note: This Question is unanswered, help us to find answer for this one

28.

The - (Negative) and ~(Bitwise NOT) are examples of the _________ type of operators.

logical

arithmetical

assignment

unary

Answer

Correct Answer: unary

Note: This Question is unanswered, help us to find answer for this one

29.

What will the following query do?

Delete Orders

It will remove the Orders Table from the database

It will remove up to 1000 rows from the Orders Table

It will remove all rows from the Orders Table

It will remove all the rows as well as the indexes and constraints from the Orders Table

It will produce error on execution

Answer

Correct Answer: It will remove all rows from the Orders Table

Note: This Question is unanswered, help us to find answer for this one

30.

Which one of the following statements regarding "views" is incorrect?

A view is like a window through which data on tables can be viewed or changed

A view is stored as a select statement only

A view cannot be derived from another view

A view has no data of its own

Answer

Correct Answer: A view cannot be derived from another view

Note: This Question is unanswered, help us to find answer for this one

31.

A table of employees has the following values for its salary field:

10000, 11000, 12000, 10000, 14000, 12000, 13000, 10000, 14000, 10000

What will the following query return?

Select distinct (salary) from employees

10000, 14000, 12000, 10000, 11000

10000, 11000, 12000, 10000, 14000, 12000

10000, 11000, 14000, 12000, 13000, 10000

10000, 11000, 12000, 13000, 10000, 14000

10000, 11000, 12000, 14000, 13000

Answer

Correct Answer: 10000, 11000, 12000, 14000, 13000

Note: This Question is unanswered, help us to find answer for this one

32.

Which of the following statements regarding Trigger and Stored Procedures is correct?

Triggers are fired manually and Stored Procedures are fired automatically

Triggers can call Stored Procedures but Stored Procedures cannot call Triggers

Both Stored Procedures and Triggers can pass parameters

None of These

Answer

Correct Answer: Triggers can call Stored Procedures but Stored Procedures cannot call Triggers

Note: This Question is unanswered, help us to find answer for this one

33.

What exactly is a Foreign key?

A Foreign key is a column or a set of columns that uniquely identifies rows in a table

A Foreign key checks the number of columns in a table which have non-null values

A Foreign key is used to match columns in other tables

A Foreign key checks the number of rows in a table which have non-null values

Answer

Correct Answer: A Foreign key is used to match columns in other tables

Note: This Question is unanswered, help us to find answer for this one

34.

State whether True or False.

The 'Having' clause can not be used when there is a 'Where' clause in the statement already.

True

False

Answer

Correct Answer: False

Note: This Question is unanswered, help us to find answer for this one

35.

State whether True or False.

You can send emails through the Database engine.

False

True

Answer

Correct Answer: True

Note: This Question is unanswered, help us to find answer for this one

36.

State whether True or False.

Many-to-One is a type of relationship in RDBMS.

False

True

Answer

Correct Answer: True

Note: This Question is unanswered, help us to find answer for this one

37.

Consider the following table structure of employees:

empid int(4)

deptname varchar(50)

salary int(4)

Which query will be used to display the Department (deptname) that is giving the maximum salary?

select deptname from employees where salary =max(salary)

select deptname from employees where salary =max(salary) group by deptname

select deptname from employees where salary = (select salary from employees group by salary)

select deptname from employees where salary =(select max(salary) from employees)

select deptname from employees where salary in (select salary from employees group by salary)

select deptname from employees where max(salary) in (select salary from employees group by salary)

Answer

Correct Answer: select deptname from employees where salary =(select max(salary) from employees)

Note: This Question is unanswered, help us to find answer for this one

38.

State whether True or False.

While using the Select query, the IN clause is a better choice than the EXIST clause.

True

False

Answer

Correct Answer: False

Note: This Question is unanswered, help us to find answer for this one

39.

Which edition of the SQL SERVER 2005 will best meet the following scenario?

A company has an online shopping website named www.shoppingxyz.com. It also has to handle its line of business portion. Its main server supports 4 CPUs and a multi-core processor.

SQL SERVER 2005 Enterprise Edition

SQL SERVER 2005 Standard Edition

SQL SERVER 2005 Workgroup Edition

SQL SERVER 2005 Developer Edition

SQL SERVER 2005 Express Edition

Answer

Correct Answer: SQL SERVER 2005 Workgroup Edition

Note: This Question is unanswered, help us to find answer for this one

40.

You have a table named "employees" with the following table structure:

empid int(4)

deptname varchar(50)

salary int(4)

The following select query is executed on this table:

SELECT * FROM employees ORDER BY deptname DESC, salary DESC

What will be the output of the above query?

The above SQL query will give an error

Records will be shown with the salary field displayed in the descending order

Records will be shown with the deptname field in the ascending order and salary in the descending order

Records will be shown with the deptname field and the salary field in the descending order

Records will be shown in a jumbled manner

Answer

Correct Answer: Records will be shown with the deptname field and the salary field in the descending order

Note: This Question is unanswered, help us to find answer for this one

41.

Which functions does the following SQL QUERY perform?

DBCC CHECKIDENT ('table_name', RESEED, new_reseed_value)

It checks the current identity field value of the specified table

It sets the identity field value to the new reseed value

It sets the identity field value to 1

None of the above

Answer

Correct Answer: It checks the current identity field value of the specified table

Note: This Question is unanswered, help us to find answer for this one

42.

Which of the following are not Database Models?

Hierarchical model

Network model

Composite model

Relational model

Recursive model

Answer

Correct Answer: Composite model

Note: This Question is unanswered, help us to find answer for this one

43.

Examine the data in the EMPLOYEES table given below:

LAST_NAME DEPARTMENT_ID SALARY

Allen 10 3000

Miller 20 1500

King 20 2200

Davis 30 5000

Which of the following sub-queries work?

SELECT * FROM employees where salary > (SELECT MIN(salary) FROM employees GROUP BY department_id)

SELECT * FROM employees WHERE salary = (SELECT AVG(salary) FROM employees GROUP BY department_id)

SELECT distinct department_id FROM employees Where salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id)

SELECT department_id FROM employees WHERE SALARY > ALL (SELECT AVG(salary) FROM employees GROUP BY department_id)

SELECT department_id FROM employees WHERE salary > ALL (SELECT AVG(salary) FROM employees GROUP BY AVG(SALARY))

Answer

Correct Answer: SELECT * FROM employees where salary > (SELECT MIN(salary) FROM employees GROUP BY department_id)

Note: This Question is unanswered, help us to find answer for this one

44.

You have a table named 'employees', having the following structure.

empid int(4)

deptname varchar(50)

salary int(4)

And a view is created as follows:

create view viemployees as select * from employees

You want to insert a new row into the table having the following values:

empid=1010, deptname=HR, salary=10000.

Which of the following are the correct insert SQL queries?

insert into employees values (1010, HR, 10000)

insert into employees values (1010, 'HR', 10000)

insert into viemployees values (1010, 'HR', 10000)

insert into viemployees (empid, deptname, salary) values (1010, HR, 10000)

insert into employees (empid, deptname, salary) set values (1010, 'HR', 10000)

Answer

Correct Answer: insert into employees values (1010, 'HR', 10000)

Note: This Question is unanswered, help us to find answer for this one

45.

Which of the following statements regarding "views" are incorrect?

A view has a data of its own

Data can be inserted into the table using its own view

Data can be updated in the table using its own view

A table can be altered by altering its corresponding view

A View is derived from another table

Answer

Correct Answer: Data can be inserted into the table using its own view

Note: This Question is unanswered, help us to find answer for this one

46.

Which of the following operators perform pattern matching?

_ (underscore)

is =

Answer

Correct Answer: %

Note: This Question is unanswered, help us to find answer for this one

47.

Which of the following are the SQL Transaction statements?

REVERT STATEMENT

ROLLBACK STATEMENT

START STATEMENT

COMMIT STATEMENT

END STATEMENT

Answer

Correct Answer: ROLLBACK STATEMENT

Note: This Question is unanswered, help us to find answer for this one

48.

SQL SERVER 2005 provides a new feature to enable partitioning of the data in the database. But only those database objects can be partitioned which store data in the database. In accordance with the above statement, which of the following database objects can be partitioned?

Functions

Triggers

Table

Indexes

Answer

Correct Answer: Table

Note: This Question is unanswered, help us to find answer for this one

49.

You have a table "engineers" with the following table structure:

enggid int(4)

name varchar(50)

salary int(4)

You want to select the top 2 engineers in the decreasing order of their salaries, starting with the maximum salary. Which of the following SQL queries will fetch this data?

SELECT TOP 2 * FROM engineers ORDER BY max(salary) DESC

SELECT TOP 2 * FROM engineers ORDER BY salary DESC, GROUP BY salary

SELECT TOP 2 * FROM engineers GROUP BY salary DESC

SELECT TOP 2 * FROM engineers ORDER BY salary DESC

Answer

Correct Answer: SELECT TOP 2 * FROM engineers ORDER BY salary DESC

Note: This Question is unanswered, help us to find answer for this one

50.

What is the function of "DBCC CHECKDB" command?

It checks the allocation of all the objects in the specified database

It checks the structural integrity of all the objects in the specified database

It checks for consistency in and between system tables in the specified database

It checks the consistency of disk space allocation structures of a specified database

Answer

Correct Answer: It checks the allocation of all the objects in the specified database

Note: This Question is unanswered, help us to find answer for this one

51.
Which of the following statements is correct?

Modifications made in a table will be faster if the number of indexes is more

Modifications made in a table will be slower if the number of indexes is more

The number of indexes does not affect the modification process of a table

Answer

Correct Answer: Modifications made in a table will be slower if the number of indexes is more

Note: This Question is unanswered, help us to find answer for this one

52.
State whether True or False.

A User Defined Function 'findsum' is to be created as follows:

Create function findsum(@x int, @y int)

As

Begin

Return(@x+@y)

End

But the SQL SERVER is unable to create the desired function and gives an error. The error can be removed by specifying the return type of the function

False

True

Answer

Correct Answer: True

Note: This Question is unanswered, help us to find answer for this one

53.
Which of the following is a database object?

Tables

Stored Procedures

Indexes

UDF(User Defined Functions)

All of the above

Answer

Correct Answer: All of the above

Note: This Question is unanswered, help us to find answer for this one

54.
Which of the following statements is/are wrong?

Both Primary key and Unique key enforce uniqueness

Primary keys create a clustered index by default

Both Primary key and Unique key don't allow NULL values

Unique keys create a non-clustered index by default

It is possible to have more than one Primary key in a table

Answer

Correct Answer: Both Primary key and Unique key don't allow NULL values

Note: This Question is unanswered, help us to find answer for this one

55.
Which of the following methods is used to backup the Transaction log on to a secondary database in order to backup and recover data in a synchronized manner?

Using the backup of Transaction log (ldf) file

Taking Backup using flat files

Using Log Shipping

Taking Database Snapshot of Transaction log

Answer

Correct Answer: Using Log Shipping

Note: This Question is unanswered, help us to find answer for this one

56.
Which data type in the SQL SERVER 2005 converts itself into the type of data that is inserted into it?

real

rowversion

type_var

sql_variant

monetary

Answer

Correct Answer: sql_variant

Note: This Question is unanswered, help us to find answer for this one

57.
Which query will be used for a particular column of a table, if 'Reading' is to be replaced by 'Writing'?

Update [Table Name] set [Field Name]= (replace('Reading','Writing'))

Update [Table Name] set [Field Name]= (replace('Writing',' Reading'))

Update [Table Name] set [Field Name]= (replace([Field Name],' Writing'))

Update [Table Name] set [Field Name]= (replace([Field Name],' Reading '))

Update [Table Name] set [Field Name]= (replace([Field Name],'Reading','Writing'))

Update [Table Name] set [Field Name]= (replace([Field Name],'Writing ','Reading'))

Answer

Correct Answer: Update [Table Name] set [Field Name]= (replace([Field Name],'Reading','Writing'))

Note: This Question is unanswered, help us to find answer for this one

58.
You want to select a row using cursors in the table 'qlist'. Which of the following statements DECLARES a cursor 'qselect' to perform the desired operation?

DECLARE qselect CURSOR ON SELECT * FROM qlist

DECLARE qselect CURSOR FOR SELECT * FROM qlist

DECLARE qlist CURSOR ON SELECT * FROM qselect

DECLARE qselect CURSOR AS SELECT * FROM qlist

Answer

Correct Answer: DECLARE qselect CURSOR FOR SELECT * FROM qlist

Note: This Question is unanswered, help us to find answer for this one

59.
Consider the following query:

Select name from employees where name like "%_n_"

Which names will be displayed?

Names starting with n

Names containing n

Names containing n as the second letter

Names containing n as the second last letter

Names having 3 ns

Answer

Correct Answer: Names containing n as the second last letter

Note: This Question is unanswered, help us to find answer for this one

60.
Which of the following is not an Aggregate function?

AVG()

LEN()

COUNT()

SUM()

MAX(column)

Answer

Correct Answer: LEN()

Note: This Question is unanswered, help us to find answer for this one

61.
Which level of data abstraction describes the data that is stored in the database and their corresponding relationships?

View Level

Procedural Level

Physical Level

Logical Level

Conceptual Level

Answer

Correct Answer: Logical Level

Note: This Question is unanswered, help us to find answer for this one

62.
Which of the following SQL queries correctly selects the rows from the table "employees" that have NULL value in the "salary" column?

SELECT * FROM employees WHERE salary=null

SELECT * FROM employees WHERE salary equals null

SELECT * FROM employees WHERE salary is null

SELECT * FROM employees WHERE salary contains null

Answer

Correct Answer: SELECT * FROM employees WHERE salary is null

Note: This Question is unanswered, help us to find answer for this one

63.
Which of the following is the new sample database in the SQL SERVER 2005?

Northwind

pubs

AdventureWorks

master

tempdb

Answer

Correct Answer: AdventureWorks

Note: This Question is unanswered, help us to find answer for this one

64.
State whether True or False.

The plus (+) and minus (-) operators cannot be used to perform increment/decrement operations on datetime and smalldatetime values.

True

False

Answer

Correct Answer: False

Note: This Question is unanswered, help us to find answer for this one

65.
You have two tables emp_1 and emp_2, having the following data:

Table emp_1

Name                Date

Robert               1/5/2008

John                    1/6/2008

Michael                1/7/2008

Rachael                1/10/2008

Joey                    1/10/2008

Table emp_2

Name                Date

Lewis                1/05/2008

David                1/07/2008

George               1/11/2008

Jerry                 1/12/2008

Monica                1/12/2008

The following query is run in the Query Analyzer:

SELECT Date FROM emp_1

EXCEPT

SELECT Date FROM emp_2

What will be the result of the query?

1/6/2008, 1/10/2008, 1/11/2008, 1/12/2008

1/6/2008, 1/10/2008, 1/10/2008

1/6/2008, 1/10/2008, 1/11/2008, 1/12/2008, 1/12/2008

1/6/2008, 1/10/2008

The query will give an error as EXCEPT is only concerned with varchar values

Answer

Correct Answer: 1/6/2008, 1/10/2008

Note: This Question is unanswered, help us to find answer for this one

66.
Which among the following are the new datatypes introduced in the SQL Server 2005?

varchar (max)

varbinary (max)

nvarchar (max)

nvarbinary (max)

image

uniqueidentifier

xml

Answer

Correct Answer: varchar (max)

Note: This Question is unanswered, help us to find answer for this one

67.
Which clause will be used to extract data from Tables A and B having a similar structure but no relation between themselves?

Join

Group By

Order By

Union All

Where

Answer

Correct Answer: Union All

Note: This Question is unanswered, help us to find answer for this one

68.
Which of the following is not a type of Database backup in the SQL SERVER 2005?

Complete backup

Differential backup

Transaction Log backup

Partial backup

File(s) and Filegroup(s) backup

Answer

Correct Answer: Partial backup

Note: This Question is unanswered, help us to find answer for this one

69.
State whether True or False.

The "text" and "ntext" data type can store character based data. These data types can be used in joins

True

False

Answer

Correct Answer: False

Note: This Question is unanswered, help us to find answer for this one

70.
Which of the following operators will be evaluated first in the following statement?

Select (salary+40^2*30/5) from employees

Answer

Correct Answer: ^

Note: This Question is unanswered, help us to find answer for this one

71.
Suppose e1 and e2 denote two decimal expressions with precisions p1 and p2 and scales s1 and s2, respectively. What is the result precision of the following operation on the two expressions?

p1 - s1 + s2 + max(6, s1 + p2 + 1)

max(s1, s2) + max(p1 - s1, p2 - s2) + 1

p1 + p2 + 1

Answer

Correct Answer: p1 - s1 + s2 + max(6, s1 + p2 + 1)

Note: This Question is unanswered, help us to find answer for this one

72.
In which of the following statements can TOP clause be used in SQL Server 2008?

Only SELECT

Only SELECT and INSERT

Only SELECT, INSERT, UPDATE, DELETE

Only SELECT, INSERT, UPDATE, MERGE, DELETE

Answer

Correct Answer: Only SELECT, INSERT, UPDATE, MERGE, DELETE

Note: This Question is unanswered, help us to find answer for this one

73.
Which of the following commands is used to start the Resource Governor in SQL Server 2008?

ALTER Resource Governor Enable

ALTER Resource Governor Reconfigure

ALTER Resource Governor Set

None of the above

Answer

Correct Answer: ALTER Resource Governor Reconfigure

Note: This Question is unanswered, help us to find answer for this one

74.
Analyze the following code snippet for creating a stored procedure in SQL Server 2008:

CREATE PROCEDURE Person.GetEmployees

@LastName nvarchar(50),

@FirstName nvarchar(50)

AS

SET NOCOUNT ON

SELECT FirstName, LastName, JobTitle, Department

FROM Person.EmployeeDepartment

WHERE FirstName = @FirstName AND LastName = @LastName

GO

Which of the following is a valid code to execute the GetEmployees stored procedure?

EXECUTE Person.GetEmployees N'John', N'Miller'

EXEC Person.GetEmployees @LastName = N'Miller', @FirstName = N'John'

EXECUTE Person.GetEmployees @FirstName = N'John', @LastName = N'Miller'

All of the above

Answer

Correct Answer: All of the above

Note: This Question is unanswered, help us to find answer for this one

75.
Analyze the following permissions:

1.ALTER permission on the database.

2.CONTROL permission in the database.

3.ALTER ANY SCHEMA permission and CREATE XML SCHEMA COLLECTION permission in the database.

Which of the above permissions is/are required to create an XML SCHEMA COLLECTION?

1 and 2

2 and 3

1 and 3

1 or 2 or 3

1 and 2 and 3

Answer

Correct Answer: 1 or 2 or 3

Note: This Question is unanswered, help us to find answer for this one

76.
A local temporary stored procedure name can contain a maximum of __________ characters

116

128

512

Answer

Correct Answer: 116

Note: This Question is unanswered, help us to find answer for this one

77.
FILESTREAM storage is best used when the BLOB file sizes average 1MB or higher.

CREATE VIEW permission in database

ALTER permission on schema

CONTROL permission on object

Answer

Correct Answer: CREATE VIEW permission in database

Note: This Question is unanswered, help us to find answer for this one

78.
Which of the following statements using GROUP BY clause are allowed in SQL Server 2008?

SELECT Column1 + Column2 FROM Table GROUP BY Column1, Column2

SELECT Column1, Column2 FROM Table GROUP BY Column1 + Column2

SELECT Column1 + constant + Column2 FROM Table GROUP BY Column1 + Column2

SELECT Column1 + Column2 FROM Table GROUP BY Column1 + Column2

Answer

Correct Answer: SELECT Column1 + Column2 FROM Table GROUP BY Column1, Column2

Note: This Question is unanswered, help us to find answer for this one

79.
Which of the following is used to comment a portion of an SQL Server 2008 script?

Double asterisk (**)

Double forward slash (//)

Double dash (--)

Double back slash (\\)

Answer

Correct Answer: Double dash (--)

Note: This Question is unanswered, help us to find answer for this one

80.
Which of the following is the correct precedence order of the data types in SQL Server 2008?

1.xml

2.text

3.bigint

4.image

5.float

6.datetime

1,2,3,4,5,6

6,5,4,3,2,1

1,6,5,3,2,4

1,4,2,3,6,5

Answer

Correct Answer: 1,6,5,3,2,4

Note: This Question is unanswered, help us to find answer for this one

81.
Which of the following queries is used to rename a database in SQL Server 2008?

sp_renamedb oldname,newname

sp_renamedb newname,oldname

sp_dbrename oldname,newname

sp_rename oldname,newname

Answer

Correct Answer: sp_renamedb oldname,newname

Note: This Question is unanswered, help us to find answer for this one

82.
The output of a Transact SQL query using the GROUPING function of SQL Server 2008 is of __________ return type.

varchar

boolean

int

tinyint

Answer

Correct Answer: tinyint

Note: This Question is unanswered, help us to find answer for this one

83.
What should a computed column be marked as so that SQL Server Database Engine can physically store values in a table?

WRITE

PERSISTED

Answer

Correct Answer: PERSISTED

Note: This Question is unanswered, help us to find answer for this one

84.
Which of the following is a valid code to remove the key used to encrypt a database using Transparent Data Encryption algorithm?

ALTER DATABASE DROP ENCRYPTION KEY

DROP DATABASE ENCRYPTION KEY

REMOVE DATABASE ENCRYPTION KEY

Answer

Correct Answer: DROP DATABASE ENCRYPTION KEY
DROP DATABASE ENCRYPTION KEY

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

85.
Analyze the following data types and schemas:

1.The SQL Server system data type.

2.The default schema of the current user in the current database.

3.The dbo schema in the current database.

In which order does the SQL Server Database Engine refer to type_name when a type_schema_name is NOT specified while creating a table in SQL Server 2008?

3,2,1

2,3,1

3,1,2

1,2,3

Any order

Answer

Correct Answer: 2,3,1

Note: This Question is unanswered, help us to find answer for this one

86.
Which of the following code snippets causes only partition number 1 to be rebuilt while changing the compression of a partitioned table?

ALTER TABLE Table1 REBUILD WITH (DATA_COMPRESSION = PAGE)

ALTER TABLE Table1 REBUILD PARTITION = 1 WITH (DATA_COMPRESSION = NONE) GO

ALTER TABLE Table1 REBUILD PARTITION ALL WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1) )

All of the above

Answer

Correct Answer: ALTER TABLE Table1 REBUILD PARTITION = 1 WITH (DATA_COMPRESSION = NONE) GO

Note: This Question is unanswered, help us to find answer for this one

87.
What happens to the audit records if a failure to write the Audit event does NOT trigger the SQL Server instance to shut down?

The server blocks any new activity to be performed to the SQL Server instance.

Audit events are buffered in memory until they can be flushed to the target.

The audit is automatically disabled.

An error is displayed.

Answer

Correct Answer: Audit events are buffered in memory until they can be flushed to the target.

Note: This Question is unanswered, help us to find answer for this one

88.
Which of the following namespaces is used to manage the audit configuration programmatically in SQL Server 2008?

Microsoft.SqlServer.Management.Smo

Microsoft.SqlServer.Server

Microsoft.sqlserver.management.common

Microsoft.sqlserver.connectioninfo

Answer

Correct Answer: Microsoft.SqlServer.Management.Smo

Note: This Question is unanswered, help us to find answer for this one

89.
Which of the following default passwords is used by sqlcmd if -P option is used at the end of the command prompt without a password?

0000

NULL

1234

None of the above

Answer

Correct Answer: NULL

Note: This Question is unanswered, help us to find answer for this one

90.
The login timeout when you try to connect to a server must be a number between __________.

0 and 66020

1 and 65530

0 and 65534

1 and 65535

Answer

Correct Answer: 0 and 65534

Note: This Question is unanswered, help us to find answer for this one

91.
Which of the following permissions is required to drop a stored procedure in SQL Server 2008?

SELECT permission on the schema

ALTER permission on the schema

CONTROL permission on the procedure

Both a and b

Either b or c

Both b and c

Answer

Correct Answer: Either b or c

Note: This Question is unanswered, help us to find answer for this one

92.
What is the default precision of the TIME data type in SQL Server 2008?

5 digits

6 digits

7 digits

10 digits

Answer

Correct Answer: 7 digits

Note: This Question is unanswered, help us to find answer for this one

93.
Which of the following commands is used to run a Transact-SQL script file by using sqlcmd?

sqlcmd -S myServer\instanceName -i C:\newScript.sql

sqlcmd -S instanceName -o C:\newScript.sql

sqlcmd -S myServer\instanceName -r C:\newScript.sql

sqlcmd -S instanceName -i C:\newScript.sql

Answer

Correct Answer: sqlcmd -S myServer\instanceName -i C:\newScript.sql

Note: This Question is unanswered, help us to find answer for this one

94.
Which of the following commands is used to connect to a named instance of SQL Server using the sqlcmd utility of SQL Server?

sqlcmd -C myServer\instanceName

sqlcmd -S instanceName

sqlcmd -C instanceName

sqlcmd -S myServer\instanceName

Answer

Correct Answer: sqlcmd -S myServer\instanceName

Note: This Question is unanswered, help us to find answer for this one

95.
Which of the following is the correct order of steps to be followed while using the Transparent Data Encryption security feature of SQL Server 2008?

1. Create a database encryption key. 2. Create or obtain a certificate protected by the encryption key. 3. Create a master key and protect it by the certificate. 4. Set the database to use encryption.

1. Create a database encryption key. 2. Create a master key. 3. Create or obtain a certificate protected by the master key. 4. Set the database to use encryption.

1. Create a master key. 2. Create or obtain a certificate protected by the master key. 3. Create a database encryption key and protect it by the certificate. 4. Set the database to use encryption.

Answer

Correct Answer: 1. Create a master key. 2. Create or obtain a certificate protected by the master key. 3. Create a database encryption key and protect it by the certificate. 4. Set the database to use encryption.

Note: This Question is unanswered, help us to find answer for this one

96.
Which of the following values specifies that each instance of the XML data type in column_name can contain multiple top-level elements?

DEFAULT

CONTENT

DOCUMENT

FILESTREAM

Answer

Correct Answer: CONTENT

Note: This Question is unanswered, help us to find answer for this one

97.
What is the maximum number of columns that can be combined into a single composite index key?

Answer

Correct Answer: 16

Note: This Question is unanswered, help us to find answer for this one

98.
Which of the following data types can be specified as SPARSE?

image

text

nchar

geography

Answer

Correct Answer: nchar

Note: This Question is unanswered, help us to find answer for this one

99.
What is the name of the default instance installed by SQL Server 2008 Express in your computer?

sqlinst

sqlcmd

sqlexpress

sqldef

Answer

Correct Answer: sqlexpress

Note: This Question is unanswered, help us to find answer for this one

100.
Which of the following is a valid syntax of the ALTER SERVER AUDIT statement used to enable a server audit?

ALTER SERVER AUDIT <audit_name> WITH (ENABLE=TRUE)

ALTER SERVER AUDIT <audit_name> SET (ENABLE=TRUE)

ALTER SERVER AUDIT <audit_name> WITH (STATE=ON)

ALTER SERVER AUDIT <audit_name> SET (STATE=ON)

Answer

Correct Answer: ALTER SERVER AUDIT <audit_name> WITH (STATE=ON)

Note: This Question is unanswered, help us to find answer for this one

101.
What range is supported by the TIME data type in SQL Server 2008?

00:00:00.0000000 through 23:59:59.9999999

00:00:00.0000000 through 24:00:00.0000000

00:00:00.0000000 through 23:59:00.0000000

00:00:00.0000000 through 23:59:59.0000000

Answer

Correct Answer: 00:00:00.0000000 through 23:59:59.9999999

Note: This Question is unanswered, help us to find answer for this one

102.
Which of the following roles is required to connect to a Central Management Server?

ServerGroupReaderRole

ServerGroupAdministratorRole

PolicyAdministratorRole

db_ssisoperator

Answer

Correct Answer: ServerGroupReaderRole

Note: This Question is unanswered, help us to find answer for this one

103.
Which of the following clauses of SQL Server 2008 avoids enforcing a constraint when repeated inserts are performed?

IDENTITY

SPARSE

NOT FOR REPLICATION

COLLATE

Answer

Correct Answer: NOT FOR REPLICATION

Note: This Question is unanswered, help us to find answer for this one

104.
Analyze the following code snippet:

DECLARE @myDate DATETIME2

SELECT @myDate = '2009/10/08 12:35:29.2348 +12:15'

SELECT @myDate AS '@myDate'

What is the output of the above code?

2009-10-08 12:35:29

2009/10/08 12:35:29.2348000

2009-10-08 12:35:29.2348000

2009/10/08 12:35:29

Answer

Correct Answer: 2009-10-08 12:35:29.2348000

Note: This Question is unanswered, help us to find answer for this one

105.
Which of the following stored procedures is used to delete data from the management data warehouse of a Data Collector?

core.sp_delete_data

core.sp_purge_data

core.sp_remove_data

None of the above

Answer

Correct Answer: core.sp_purge_data

Note: This Question is unanswered, help us to find answer for this one

106.
Which of the following is the default authentication mode for sqlcmd in SQL Server 2008?

Windows Authentication

SQL Server Authentication

IIS Authentication

Digest Authentication

Answer

Correct Answer: Windows Authentication

Note: This Question is unanswered, help us to find answer for this one

107.
Multiple CHECK constraints can be applied to a single column and a single CHECK constraint can be applied to multiple columns in SQL Server 2008.

True

False

Answer

Correct Answer: True

Note: This Question is unanswered, help us to find answer for this one

108.
Analyze the following command:

-o "C:\Folder\<file_name>"

Which of the following statements is true if a file with the file name specified in the above command already exists?

An error is generated.

The file is overwritten.

None of the above.

Answer

Correct Answer: The file is overwritten.

Note: This Question is unanswered, help us to find answer for this one

109.
Which of the following features of SQL Server 2008 enables you to limit the memory used by incoming application requests?

Hot Add CPU

Dynamic management views

Resource Governor

Backup Compression

Answer

Correct Answer: Resource Governor

Note: This Question is unanswered, help us to find answer for this one

110.
Which of the following is a valid code to change the index created on a column of a table to page compression?

ALTER INDEX Index_Product ON Table1 WITH ( DATA_COMPRESSION = PAGE ) GO

ALTER INDEX Index_Product ON Table1 SET ( DATA_COMPRESSION = PAGE ) GO

ALTER INDEX Index_Product ON Table1 REBUILD WITH ( DATA_COMPRESSION = PAGE ) GO

ALTER INDEX Index_Product ON Table1 REBUILD SET ( DATA_COMPRESSION = PAGE ) GO

Answer

Correct Answer: ALTER INDEX Index_Product ON Table1 REBUILD WITH ( DATA_COMPRESSION = PAGE ) GO

Note: This Question is unanswered, help us to find answer for this one

111.
PRIMARY KEY constraint defaults to __________ index.

Clustered

Nonclustered

None of the above

Answer

Correct Answer: Clustered

Note: This Question is unanswered, help us to find answer for this one

112.
What amount of storage is required by a date variable in SQL Server 2008?

3 bytes

5 bytes

6 bytes

8 bytes

Answer

Correct Answer: 3 bytes

Note: This Question is unanswered, help us to find answer for this one

113.
What is the default precision of the date data type in SQL Server 2008?

4 digits

6 digits

8 digits

10 digits

Answer

Correct Answer: 10 digits

Note: This Question is unanswered, help us to find answer for this one

114.
What is the default timeout for login to sqlcmd while you try to connect to a server?

5 seconds

8 seconds

10 seconds

15 seconds

1 minute

Answer

Correct Answer: 8 seconds

Note: This Question is unanswered, help us to find answer for this one

115.
Which of the following is a valid code to delete a clustered index with a PRIMARY KEY constraint in SQL Server 2008?

ALTER TABLE Production.ProductCost DROP CONSTRAINT PK_Product WITH (ONLINE = ON) GO

ALTER TABLE Production.ProductCostHistory DROP CONSTRAINT PK_Product DROP INDEX Index_Product WITH (ONLINE = ON) GO

DROP INDEX Index_Product ON Production.ProductCost GO

Answer

Correct Answer: ALTER TABLE Production.ProductCost DROP CONSTRAINT PK_Product WITH (ONLINE = ON) GO

Note: This Question is unanswered, help us to find answer for this one

116.
Windows Security log can be specified as a target for Audit in SQL Server 2008.

True

False

Answer

Correct Answer: True

Note: This Question is unanswered, help us to find answer for this one

117.
How many groupings are generated when CUBE is used with the GROUP BY clause of SQL Server 2008?

n (n= number of expressions in the element list passed to CUBE)

n+1 (n= number of expressions in the element list passed to CUBE)

2^n (n= number of expressions in the element list passed to CUBE)

2^n+1 (n= number of expressions in the element list passed to CUBE)

Answer

Correct Answer: 2^n (n= number of expressions in the element list passed to CUBE)

Note: This Question is unanswered, help us to find answer for this one

118.
Which of the following features of SQL Server 2008 evaluates servers for compliance with a set of predefined conditions and prevents making undesirable changes to servers?

Data Compression

Resource Governor

Policy-Based Management

Auditing

Answer

Correct Answer: Policy-Based Management

Note: This Question is unanswered, help us to find answer for this one

119.
Which of the following statements is true if you create a global temporary table in SQL Server 2008 and specify a FOREIGN KEY with the CREATE TABLE command?

The table is created successfully with the FOREIGN KEY constraint.

The table is not created and no message is returned.

The table is created without the FOREIGN KEY constraint and a warning message is returned.

The table is not created and an error message is displayed.

Answer

Correct Answer: The table is created without the FOREIGN KEY constraint and a warning message is returned.

Note: This Question is unanswered, help us to find answer for this one

120.
Which of the following evaluation modes of Policy-Based Management CANNOT be automated in SQL Server 2008?

On demand

On change: prevent

On change: log only

On schedule

Answer

Correct Answer: On demand

Note: This Question is unanswered, help us to find answer for this one

121.
The regular identifiers of SQL Server 2008 may contain a range of _____________ characters.

1 to 116

1 to 127

1 to 128

1 to 512

Answer

Correct Answer: 1 to 128

Note: This Question is unanswered, help us to find answer for this one

122.
What is the default value of the SORT_IN_TEMPDB clause of the relational_index_option specified at the time of creation of an index on a table in SQL Server 2008?

OFF

TRUE

FALSE

Answer

Correct Answer: OFF

Note: This Question is unanswered, help us to find answer for this one

123.
Which of the following is used by SQL Server 2008 Management Studio to execute scripts in regular mode in the Query Editor?

sqlcmd

Microsoft .NET Framework SqlClient

OLE DB provider

None of the above

Answer

Correct Answer: Microsoft .NET Framework SqlClient

Note: This Question is unanswered, help us to find answer for this one

124.
Analyze the following code snippet:

DECLARE @myTime TIME(4) = '02:02:02.234567 +01:01'

SELECT @myTime AS '@myTime'

What is the output of the above code?

02:02:02.2345

02:02:02

02:02:02.2346

Answer

Correct Answer: 02:02:02.2346

Note: This Question is unanswered, help us to find answer for this one

125.
Which of the following commands is used to save the output to a text file after you run a Transact-SQL script using sqlcmd?

sqlcmd -S instanceName -o C:\new.txt

sqlcmd -S instanceName -i C:\myScript.sql -o C:\new.txt

sqlcmd -S myServer\instanceName -o C:\new.txt

sqlcmd -S myServer\instanceName -i C:\myScript.sql -o C:\new.txt

Answer

Correct Answer: sqlcmd -S myServer\instanceName -i C:\myScript.sql -o C:\new.txt

Note: This Question is unanswered, help us to find answer for this one

126. User defined data type columns can be designated with ROWGUIDCOL.

True

False

Answer

Correct Answer: False

Note: This Question is unanswered, help us to find answer for this one

127.
What is returned by NULLIF if the two expressions that it compares are NOT equal?

It returns the first expression.

It returns the second expression.

It returns a null value.

It returns an error.

Answer

Correct Answer: It returns the first expression.

Note: This Question is unanswered, help us to find answer for this one

128.
An identifier denoting a global temporary object in SQL Server 2008 starts with a ______ sign.

At sign(@)

Double number(##)

Underscore(_)

None of the above

Answer

Correct Answer: Double number(##)

Note: This Question is unanswered, help us to find answer for this one

129.
What is the maximum value for the sum of the values of MIN_CPU_PERCENT and MIN_MEMORY_PERCENT for all the resource pools of SQL Server?

100

150

200

Answer

Correct Answer: 100

Note: This Question is unanswered, help us to find answer for this one

130.
The Server Audit object and the Server Audit Specification object of SQL Server 2008 share a __________ relationship.

One-to-one

One-to-many

Many-to-one

Many-to-many

Answer

Correct Answer: One-to-one

Note: This Question is unanswered, help us to find answer for this one

131.
Analyze the following code used to update a Common Table Expression:

USE demodb

GO

DECLARE @a TABLE (ID int, Value int);

DECLARE @b TABLE (ID int, Value int);

INSERT @a VALUES (1, 10), (2, 20);

INSERT @b VALUES (1, 100),(2, 200);

WITH cte AS (SELECT * FROM @a)

UPDATE cte

SET Value = b.Value

FROM cte AS a

INNER JOIN @b AS b ON b.ID = a.ID

SELECT * FROM @a

GO

What Will be the output of the above code?

ID Value ----- ----- 1 100 2 200

ID Value ----- ----- 1 10 2 100

ID Value ----- ----- 1 10 2 200

ID Value ----- ----- 1 100 2 100

Answer

Correct Answer: ID Value ----- ----- 1 100 2 200

Note: This Question is unanswered, help us to find answer for this one

132.
Columns marked with which of the following constraints/properties allow null values to be inserted in them?

PRIMARY KEY

UNIQUE

IDENTITY

FOREIGN KEY

Answer

Correct Answer: UNIQUE

Note: This Question is unanswered, help us to find answer for this one

133.
For which of the following data types can a COLLATE clause be applied?

text

int

varchar

bigint

Answer

Correct Answer: text

Note: This Question is unanswered, help us to find answer for this one

134.
Which of the following permissions are required to create a view in SQL Server 2008?

CREATE VIEW permission in database

ALTER permission on schema

CONTROL permission on object

Answer

Correct Answer: CREATE VIEW permission in database

Note: This Question is unanswered, help us to find answer for this one

135.
Which of the following statements are true about FILESTREAM storage in SQL Server 2008?

Transact-SQL can be used to SELECT, INSERT, UPDATE, DELETE FILESTREAM data.

The size of the stored data is limited to 2 GB.

You cannot take backup of a database without the FILESTREAM data.

FILESTREAM storage is best used when the BLOB file sizes average 1MB or higher.

Answer

Correct Answer: Transact-SQL can be used to SELECT, INSERT, UPDATE, DELETE FILESTREAM data.

Note: This Question is unanswered, help us to find answer for this one

136.
Which of the following spatial data types are used in SQL Server 2008?

Geography

Longitude

Altitude

Geometry

Latitude

Answer

Correct Answer: Geography

Note: This Question is unanswered, help us to find answer for this one

137.
Which of the following statements are true about the FILESTREAM argument of SQL Server 2008?

If a table contains FILESTREAM data and the table is partitioned, the FILESTREAM_ON clause must be included.

The FILESTREAM column can be partitioned even if a table is not partitioned.

There may not be a FILESTREAM filegroup if a table is not partitioned and the FILESTREAM_ON clause is not specified.

The filegroup in the FILESTREAM_ON <filegroup>> clause must have one file defined for the filegroup.

Answer

Correct Answer: If a table contains FILESTREAM data and the table is partitioned, the FILESTREAM_ON clause must be included.

Note: This Question is unanswered, help us to find answer for this one

138.
Which of the following counters are used to monitor compression of the whole instance of SQL Server?

Page compression attempts/sec

Row compression/sec

Pages compressed/min

Pages compressed/sec

Rows compressed/min

Answer

Correct Answer: Page compression attempts/sec

Note: This Question is unanswered, help us to find answer for this one

139.
Which of the following are valid DATETIME functions in SQL Server 2008?

SYSDATE()

SYSTIME()

SYSDATETIME()

SYSUTCDATETIME()

Answer

Correct Answer: SYSDATETIME()

Note: This Question is unanswered, help us to find answer for this one

140.
Which of the following formats are supported by the language_term argument while altering a full-text stoplist in SQL Server 2008?

Integer

Binary

Decimal

Hexadecimal

Answer

Correct Answer: Integer

Note: This Question is unanswered, help us to find answer for this one

141.
Which of the following encryption algorithms are used by the Transparent Data Encryption security mechanism of SQL Server 2008?

Tiny Encryption Algorithm

Advanced Encryption Standard(AES)

Blowfish

Triple Data Encryption Standard(3DES)

Answer

Correct Answer: Advanced Encryption Standard(AES)

Note: This Question is unanswered, help us to find answer for this one

142.
Which of the following security features have been introduced in SQL Server 2008?

Extensible Key Management

SOAP Security

Hashing

Transparent Data Encryption

Answer

Correct Answer: Extensible Key Management

Note: This Question is unanswered, help us to find answer for this one

143.
Which of the following statements are true regarding row and page compression in SQL Server 2008?

When a table or index is created, data compression is set to NONE, unless otherwise specified.

Nonclustered indexes inherit the compression property of the table.

New pages allocated in a heap as part of DML operations will not use PAGE compression until the heap is rebuilt.

When you are compressing indexes, leaf-level pages can be compressed with only page compression.

Answer

Correct Answer: When a table or index is created, data compression is set to NONE, unless otherwise specified.

Note: This Question is unanswered, help us to find answer for this one

144.
Which of the following statements are true?

IGNORE_DUP_KEY cannot be set to ON for XML indexes.

IGNORE_DUP_KEY can be set to ON for spatial indexes.

IGNORE_DUP_KEY can be set to ON for filtered indexes.

IGNORE_DUP_KEY can be set to ON for XML indexes.

IGNORE_DUP_KEY cannot be set to ON for indexes created on a view.

Answer

Correct Answer: IGNORE_DUP_KEY cannot be set to ON for XML indexes.

Note: This Question is unanswered, help us to find answer for this one

145.
Which of the following data types are supported by SQL Server 2008?

date

datetime

datetime1

datetime2

datetimeoffset

Answer

Correct Answer: date

Note: This Question is unanswered, help us to find answer for this one

146.
Which of the following options are available for the DATA_COMPRESSION clause specified for a table in SQL Server 2008?

Page

Table

Row

Column

Answer

Correct Answer: Page

Note: This Question is unanswered, help us to find answer for this one

147.
Which of the following rules must be followed for combining the result sets of two queries by using UNION?

The number of columns must be same in all queries.

The order of columns must be same in all queries.

The data types of the columns of the queries must be compatible.

There should be at least one common column in the tables which are part of the UNION operation.

Answer

Correct Answer: The number of columns must be same in all queries.

Note: This Question is unanswered, help us to find answer for this one

148.
Which of the following statements using GROUP BY clause are allowed in SQL Server 2008?Which of the following statements using GROUP BY clause are allowed in SQL Server 2008?

Which of the following statements using GROUP BY clause are allowed in SQL Server 2008?

SELECT Column1, Column2 FROM Table GROUP BY Column1 + Column2

SELECT Column1 + constant + Column2 FROM Table GROUP BY Column1 + Column2

SELECT Column1 + Column2 FROM Table GROUP BY Column1 + Column2

Answer

Correct Answer: Which of the following statements using GROUP BY clause are allowed in SQL Server 2008?

Note: This Question is unanswered, help us to find answer for this one

149. How many audit action groups are available for a server in SQL Server 2008 to perform different actions on audit data?

Answer

Correct Answer: 35

Note: This Question is unanswered, help us to find answer for this one

150. Which of the following options is used to change the value of the password variable and exit using sqlcmd?

-p

-P

-z

-Z

Answer

Correct Answer: -Z

Note: This Question is unanswered, help us to find answer for this one

151. Which of the following Audit objects primarily describes the destination where the audit data of SQL Server 2008 gets stored?

Server Audit object

Server Audit Specification object

Database Audit Specification object

Answer

Correct Answer: Server Audit object

Note: This Question is unanswered, help us to find answer for this one

152. Which of the following statements is NOT true about the UNIQUE constraint of SQL Server 2008?

Multiple UNIQUE constraints can be defined on a table.

When using the UNIQUE constraint, only one null value is allowed per column.

UNIQUE constraint cannot be referenced by a FOREIGN KEY constraint

Answer

Correct Answer: UNIQUE constraint cannot be referenced by a FOREIGN KEY constraint

Note: This Question is unanswered, help us to find answer for this one

153. Which of the following arguments of the fn_get_audit_file() function of SQL Server 2008 specifies the directory indicating the location and audit file set to be read?

Initial_file_name

File_pattern

Audit_file_offset

None of the above<br>

Answer

Correct Answer: File_pattern

Note: This Question is unanswered, help us to find answer for this one

154. All MDX, DMX and XML/A errors are displayed in the Error List window of the SQL Server 2008. Management Studio.

True

False

Answer

Correct Answer: False

Note: This Question is unanswered, help us to find answer for this one

155. Analyze the following code which uses the TOP clause of SQL Server 2008:

USE Person
GO
DECLARE @p AS int
SELECT @p=10
SELECT TOP(@p)
FROM Employee
GO

What Will be the output of the above code?

No output is displayed.

A list of first 10 employees is displayed from the Person database.

An incorrect syntax error is displayed.

None of the above.

Answer

Correct Answer: An incorrect syntax error is displayed.

Note: This Question is unanswered, help us to find answer for this one

156. Which of the following compression features have been introduced in SQL Server 2008 that were NOT supported by SQL Server 2005?

Row-level compression

Page-level compression

Data file level compression

Backup level compression

Answer

Correct Answer: Data file level compression
Backup level compression

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

157. What is the data type of the parameter "Audit_file_offset" of function "fn_get_audit_file()" in SQL Server 2008?

varchar

nvarchar

int

bigint

Answer

Correct Answer: nvarchar

Note: This Question is unanswered, help us to find answer for this one

158. Which of the following scripting options are available while creating a script using the shortcut menu from the Object Explorer in SQL Server 2008 Management Studio?

DELETE To

MERGE To

CREATE INDEX To

INSERT To

UPDATE To

Answer

Correct Answer: CREATE INDEX To
UPDATE To

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

159. Analyze the following code using wild card characters of SQL Server 2008:

SELECT Name
FROM person.employees
WHERE Name LIKE '[_]n'
GO

Which of the following values will be returned by the above code?

sin

All of the above

Answer

Correct Answer: _n

Note: This Question is unanswered, help us to find answer for this one

160. Which of the following methods can be specified for the lock escalation of a table in SQL Server 2008?

ENABLE

AUTO

DISABLE

TABLE

DEFAULT

Answer

Correct Answer: ENABLE
AUTO
DISABLE

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

161. Which of the following options is NOT supported by the ON DELETE clause of a FOREIGN KEY constraint of SQL Server 2008?

CASCADE

DELETE

SET NULL

SET DEFAULT

Answer

Correct Answer: DELETE

Note: This Question is unanswered, help us to find answer for this one

162. What is the maximum number of processors that can be used in parallel plan execution using the MAXDOP option in SQL Server 2008?

32 processors

50 processors

64 processors

75 processors

Answer

Correct Answer: 64 processors

Note: This Question is unanswered, help us to find answer for this one

163. Each table in SQL Server 2008 can have up to ______ nonclustered indexes.

777

888

999

1000

Answer

Correct Answer: 999

Note: This Question is unanswered, help us to find answer for this one

164. What is the default maximum precision of decimal data type in SQL Server 2008?

Answer

Correct Answer: 18

Note: This Question is unanswered, help us to find answer for this one

165. Which of the following features is used by SQL Server Audit to create and manage audits in SQL Server 2008?

System stored procedures

Triggers

SQL DDL syntax

None of the above

Answer

Correct Answer: SQL DDL syntax

Note: This Question is unanswered, help us to find answer for this one

166. What is the return type of the value returned by @@DATEFIRST in SQL Server 2008?

datetime

date

int

tinyint

Answer

Correct Answer: date

Note: This Question is unanswered, help us to find answer for this one

167. The AND operator displays a row if ANY conditions listed are true. The OR operator displays a row if ALL of the conditions listed are true

True

False

Answer

Correct Answer: False

Note: This Question is unanswered, help us to find answer for this one

168. Which of the following constraints can be used to enforce the uniqueness of rows in a table?

DEFAULT and NOT NULL constraints

FOREIGN KEY constraints

PRIMARY KEY and UNIQUE constraints

IDENTITY columns

CHECK constraints

Answer

Correct Answer: PRIMARY KEY and UNIQUE constraints

Note: This Question is unanswered, help us to find answer for this one

169. What is the correct SQL syntax for selecting all the columns where the "LastName" is alphabetically between (and including) "Hansen" and "Pettersen"?

SELECT * FROM Persons WHERE LastName; 'Hansen', LastName ; 'Pettersen'

SELECT LastName ; 'Hansen' AND LastName; 'Pettersen' FROM Persons

SELECT * FROM customers WHERE LastName ; 'Hansen' AND LastName ; 'Pettersen'

SELECT * FROM Persons WHERE LastName BETWEEN 'Hansen' AND 'Pettersen'

Answer

Correct Answer: SELECT * FROM Persons WHERE LastName BETWEEN 'Hansen' AND 'Pettersen'

Note: This Question is unanswered, help us to find answer for this one

170. The sales database contains a customer table and an order table. For each order there is one and only one customer, and for each customer there can be zero or more orders. How should primary and foreign key fields be placed into the design of this database?

A primary key should be created for the customer_id field in the customer table and also for the customer_id field in the order table

A primary key should be created for the order_id field in the customer table and also for the customer_id field in the order table

A primary key should be created for the customer_id field in the customer table and a foreign key should be created for the customer_id field in the order table

A primary key should be created for the customer_id field in the customer table and a foreign key should be created for the order_id field in the order table

None of these

Answer

Correct Answer: A primary key should be created for the customer_id field in the customer table and a foreign key should be created for the customer_id field in the order table

Note: This Question is unanswered, help us to find answer for this one

171. Which of the following is not a valid character datatype in SQL Server?

BLOB

CHAR

VARCHAR

TEXT

VARTEXT

Answer

Correct Answer: VARTEXT

Note: This Question is unanswered, help us to find answer for this one

172. What is the correct SQL syntax for returning all the columns from a table named "Persons" sorted REVERSE alphabetically by "FirstName"?

SELECT * FROM Persons WHERE FirstName ORDER BY FirstName DESC

SELECT * FROM Persons SORT REVERSE 'FirstName'

SELECT * FROM Persons ORDER BY -'FirstName'

SELECT * FROM Persons ORDER BY FirstName DESC

Answer

Correct Answer: SELECT * FROM Persons ORDER BY FirstName DESC

Note: This Question is unanswered, help us to find answer for this one

173. Consider the following queries:

1. select * from employee where department LIKE "[^F-M]%";
2. select * from employee where department = '[^F-M]%';

Select the correct option:

Query 2 will return an error

Both the queries will return the same set of records

Query 2 is perfectly correct

Query 2 would return one record less than Query 1

Answer

Correct Answer: Query 2 is perfectly correct

Note: This Question is unanswered, help us to find answer for this one

174. State which of the following are true

Views are a logical way of looking at the logical data located in the tables

Views are a logical way of looking at the physical data located in the tables

Tables are physical constructs used for storage and manipulation of data in databases

Tables are logical constructs used for storage and manipulation of data in databases

Answer

Correct Answer: Views are a logical way of looking at the physical data located in the tables

Note: This Question is unanswered, help us to find answer for this one

175. Examine the query:-

select (2/2/4) from tab1;

where tab1 is a table with one row. This would give a result of:

.25

Answer

Correct Answer: .25

Note: This Question is unanswered, help us to find answer for this one

176. What is the numeric range that is supported by the datatype tinyint?

0-25

0-100

0-255

0-3276

Answer

Correct Answer: 0-255

Note: This Question is unanswered, help us to find answer for this one

177. Which of the following is/are not (a) valid binary datatype in SQL Server?

BINARY

VARBINARY

BIT

IMAGE

TIMESTAMP

Answer

Correct Answer: BIT

Note: This Question is unanswered, help us to find answer for this one

178. Which of the following queries is valid?

Select * from students where marks > avg(marks);

Select * from students order by marks where subject = 'SQL';

Select * from students having subject ='SQL';

Select name from students group by subject, name;

Select group(*) from students;

Select name,avg(marks) from students;

None of the above

Answer

Correct Answer: Select name from students group by subject, name;

Note: This Question is unanswered, help us to find answer for this one

179. Which one of the following correctly selects rows from the table myTable that have null in column column1?

SELECT * FROM myTable WHERE column1 is null

SELECT * FROM myTable WHERE column1 = null

SELECT * FROM myTable WHERE column1 EQUALS null

SELECT * FROM myTable WHERE column1 NOT null

SELECT * FROM myTable WHERE column1 CONTAINS null

Answer

Correct Answer: SELECT * FROM myTable WHERE column1 is null

Note: This Question is unanswered, help us to find answer for this one

180. The STUDENT_GRADES table has these columns:

STUDENT_ID        INT
SEMESTER_END        DATETIME
GPA                FLOAT

Which of the following statements finds the highest Grade Point Average (GPA) per semester?

SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL

SELECT (gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL

SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL GROUP BY semester_end

SELECT MAX(gpa) GROUP BY semester_end WHERE gpa IS NOT NULL FROM student_grades

SELECT MAX(gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL

Answer

Correct Answer: SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL GROUP BY semester_end

Note: This Question is unanswered, help us to find answer for this one

181. Which of the following datatypes is not supported by SQL-Server?

Character

Binary

Logical

Date

Numeric

All are supported

Answer

Correct Answer: All are supported

Note: This Question is unanswered, help us to find answer for this one

182. Is the following statement true or false?

A column that allows NULLs requires more space to store a value.

True

False

Answer

Correct Answer: True

Note: This Question is unanswered, help us to find answer for this one

183. The IF UPDATE (column_name) parameter in a trigger definition will return
TRUE in case of an INSERT statement being executed on the triggered table:

Yes

It returns TRUE only if an UPDATE query is executed

Both b and c

Answer

Correct Answer: Yes

Note: This Question is unanswered, help us to find answer for this one

184. Sample Code

CREATE TABLE table1(
column1 varchar(50),
column2 varchar(50),
column3 varchar(50),
column4 varchar(50));

Which one of the following is the correct syntax for adding the column named "column2a" to the table shown above?

ALTER TABLE table1 ADD column2a varchar(50);

MODIFY TABLE table1 ADD column2a;

INSERT INTO table1 column2a AS varchar(50);

ALTER TABLE table1 INSERT column2a varchar(50);

CHANGE TABLE table1 INSERT column2a;

Answer

Correct Answer: ALTER TABLE table1 ADD column2a varchar(50);

Note: This Question is unanswered, help us to find answer for this one

185. Examine the description of the STUDENTS table:

STD_ID                INT
COURSE_ID        VARCHAR (10)
START_DATE        DATETIME
END_DATE        DATETIME

The aggregate functions valid on the START_DATE column are:

SUM(start_date)

AVG(start_date)

COUNT(start_date)

AVG(start_date, end_date)

MIN(start_date)

Answer

Correct Answer: COUNT(start_date)

Note: This Question is unanswered, help us to find answer for this one

186. Consider the following table structure of students:
        rollno        int
        name          varchar(20)
        course        varchar(20)

What will be the query to display the courses in which the number of students
enrolled is more than 5?

Select course from students where count(course) > 5;

Select course from students where count(*) > 5 group by course;

Select course from students group by course;

Select course from students group by course having count(*) 5;

Select course from students group by course where count(*) 5;

Select course from students where count(group(course)) 5;

Select count(course) 5 from students;

None of the above

Answer

Correct Answer: Select course from students group by course having count(*) 5;

Note: This Question is unanswered, help us to find answer for this one

187. Consider the transaction:

        Begin Transaction
                Create table A ( x smallint ,  y smallint )
                Create table B ( p smallint , q smallint )

                Update A set x=600 where y > 700
                Update B set p=78 where q=99

                If @@ error != 0
                Begin
                        RollBack Transaction
                        Return
                End

        Commit Transaction

Select the correct option:

The transaction will work perfectly fine

It will report an error

The error handling routine contains a syntax error

None of the above

Both b and c

Answer

Correct Answer: It will report an error

Note: This Question is unanswered, help us to find answer for this one

188. What does BLOB stand for?

Binary Large Object

Binary Lower Byte

Bit Large Object

Bit Lower Byte

Answer

Correct Answer: Binary Large Object

Note: This Question is unanswered, help us to find answer for this one

189. What happens if you type the following statement at the T-SQL prompt?

SELECT getdate()

It will print the current date

It will generate an 'Undefined Function' error message

It will generate an 'Incorrect Syntax' error message because you cannot write a function name after SELECT

Answer

Correct Answer: It will print the current date

Note: This Question is unanswered, help us to find answer for this one

190. Which of the following are aggregate functions in SQL?

Avg

Select

Order By

Sum

Union

Group by

Having

Answer

Correct Answer: Avg

Note: This Question is unanswered, help us to find answer for this one

191. Which of the following is an invalid statement for manipulation of binary data?

displaytext

readtext

writetext

updatetext

Answer

Correct Answer: displaytext

Note: This Question is unanswered, help us to find answer for this one

192. What does referential integrity (also called relational integrity) prevent?

Loss of data from employee sabotage  

Loss of data from any one corrupted table

Recursive joins

One-to-many or many-to-many relationships between columns in a table

Data redundancy

Answer

Correct Answer: One-to-many or many-to-many relationships between columns in a table

Note: This Question is unanswered, help us to find answer for this one

193. Which one of the following must be specified in every DELETE statement?

Table Name

Database name

LIMIT clause

WHERE clause

Column Names

Answer

Correct Answer: Table Name

Note: This Question is unanswered, help us to find answer for this one

194. Which of the following are false for batches (batch commands)?

Statements in a batch are parsed, compiled and executed as a group

None of the statements in the batch is executed if there are any syntax errors in the batch

None of the statements in the batch is executed if there are any parsing errors in the batch

None of the statements in the batch is executed if there are any fatal errors<br>in the batch

Answer

Correct Answer: None of the statements in the batch is executed if there are any fatal errors<br>in the batch

Note: This Question is unanswered, help us to find answer for this one

195. Examine the two SQL statements given below:

SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY salary DESC

SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY 2 DESC

What is true about them?

The two statements produce identical results

The second statement returns an error

There is no need to specify DESC because the results are sorted in descending order by default

None of the above

Answer

Correct Answer: The two statements produce identical results

Note: This Question is unanswered, help us to find answer for this one

196. Is it possible to insert several rows into a table with a single INSERT statement?

Yes

Answer

Correct Answer: Yes

Note: This Question is unanswered, help us to find answer for this one

197. You want to display the titles of books that meet the following criteria:

1. Purchased before November 11, 2002
2. Price is less than $500 or greater than $900

You want to sort the result by the date of purchase, starting with the most recently bought book.

Which of the following statements should you use?

SELECT book_title FROM books WHERE price between 500 and 900 AND purchase_date < '11/11/2002' ORDER BY purchase_date;

SELECT book_title FROM books WHERE price IN (500, 900) AND purchase_date < '11/11/2002' ORDER BY purchase date ASC;

SELECT book_title FROM books WHERE (price < 500 OR > 900) AND purchase_date DESC;

SELECT Book_title FROM books WHERE (price < 500 OR price > 900) AND purchase_date < '11/11/2002' ORDER BY purchase_date DESC;

Answer

Correct Answer: SELECT Book_title FROM books WHERE (price < 500 OR price > 900) AND purchase_date < '11/11/2002' ORDER BY purchase_date DESC;

Note: This Question is unanswered, help us to find answer for this one

198. Which of the following statements are true?

soundex(Smith) is the same as soundex(Smithson)

soundex(SMITH) is different from soundex(smith)

soundex(smith) is the same as soundex(smyth)

soundex(smith) is different from soundex(smythe)

All are false

Answer

Correct Answer: soundex(smith) is the same as soundex(smyth)

Note: This Question is unanswered, help us to find answer for this one

199. Which of the following statements are false?

trim() function is used to remove leading and trailing spaces from a string

ltrim() function is used to remove leading spaces from a string

rtrim() function is used to remove trailing spaces from a string

alltrim() function is used to remove leading and trailing spaces from a string

Answer

Correct Answer: trim() function is used to remove leading and trailing spaces from a string

Note: This Question is unanswered, help us to find answer for this one

200. How can you view the structure of a table named "myTable" in SQL Server?

desc myTable

desc table myTable

sp_columns myTable

None of the above

Using either option a or c

Answer

Correct Answer: sp_columns myTable

Note: This Question is unanswered, help us to find answer for this one

201. You should avoid the use of cursors because:

They are very difficult to implement

Programs with cursors take more time to run, hence performance degrades

programs with cursors are more lengthy, hence they consume more space/memory

No, you must maximize the use of cursors because they improve performance

Answer

Correct Answer: Programs with cursors take more time to run, hence performance degrades

Note: This Question is unanswered, help us to find answer for this one

202. View the following Create statement:

1 Create table Pers
2(EmpNo Int not null,
3 EName Char not null,
4 Join Datetime not null,
5 Pay Smallmoney)

Which line contains an error?

Answer

Correct Answer: 4

Note: This Question is unanswered, help us to find answer for this one

203. Which of the following is not a column property?

Null

Not Null

Default

Identity

Answer

Correct Answer: Default

Note: This Question is unanswered, help us to find answer for this one

204. A production house needs a sale report where total sale of the day is more than $20,000. Which of the following query should be used?

select OrderDate,Amount from orders where sum(amount) 20000

select OrderDate,Amount from orders where sum(amount) 20000 order by OrderDate

select count(OrderDate),sum(amount) from orders group by OrderDate having sum(amount) 20000

select count(OrderDate),sum(amount) from orders group by OrderDate where sum(amount) 20000

Answer

Correct Answer: select count(OrderDate),sum(amount) from orders group by OrderDate having sum(amount) 20000

Note: This Question is unanswered, help us to find answer for this one

205. What is the order of precedence among the following operators?

                        1 IN
                        2 NOT
                        3 AND
                        4 OR

1,2,3,4

2,3,4,1

1,2,4,3

1,4,3,2

4,3,2,1

4,1,2,3

4,2,1,3

3,2,1,4

Answer

Correct Answer: 1,2,3,4

Note: This Question is unanswered, help us to find answer for this one

206. Which of the following is not a global variable?

@@colcount

@@error

@@rowcount

@@version

All are valid global variables

Answer

Correct Answer: @@colcount

Note: This Question is unanswered, help us to find answer for this one

207. Which of the following is not a valid Arithmetic operator in SQL Server?

Answer

Correct Answer: \

Note: This Question is unanswered, help us to find answer for this one

208. Which one of the following fields is the ideal candidate for the primary key in a student record base?

Parent's name

Address

System generated ID

Student name

Phone number

Answer

Correct Answer: System generated ID

Note: This Question is unanswered, help us to find answer for this one

209. Is the FROM clause necessary in every SELECT statement?

Yes

Answer

Correct Answer: No

Note: This Question is unanswered, help us to find answer for this one

210. Choose the appropriate query for the Products table where data should be displayed primarily in ascending order of the ProductGroup column. Secondary sorting should be in descending order of the CurrentStock column.

Select * from Products order by CurrentStock,ProductGroup

Select * from Products order by CurrentStock DESC,ProductGroup

Select * from Products order by ProductGroup,CurrentStock

Select * from Products order by ProductGroup,CurrentStock DESC

None of the above

Answer

Correct Answer: Select * from Products order by ProductGroup,CurrentStock DESC

Note: This Question is unanswered, help us to find answer for this one

211. What is the maximum value that can be stored for a datetime field?

Dec 31, 9999

Jun 6, 2079

Jan 1, 2753

Jan 1, 2100

Answer

Correct Answer: Dec 31, 9999

Note: This Question is unanswered, help us to find answer for this one

212. Which of the following options is correct about identity(seed, increment)?

The increment parameter can never be negative

The increment can be 0

More than one columns in a table can be assigned the identity property

The identity property value for a column assures uniqueness of a row

None of the above

Answer

Correct Answer: None of the above

Note: This Question is unanswered, help us to find answer for this one

213. Which of the following statements about SQL Server comments is false?

/* ... */ are used for multiline comments

// is used for single line comments

-- is used for single line comments

Nested comments are allowed i.e. /* comment 1 /* comment 2 */ comment 1*/

' is used for single line comments

Answer

Correct Answer: // is used for single line comments

Note: This Question is unanswered, help us to find answer for this one

214. Which of the following is not a control statement?

if...else

if exists

do...while

while

begin...end

Answer

Correct Answer: do...while

Note: This Question is unanswered, help us to find answer for this one

215. Which of the following is/are true with reference to Triggers?

A trigger cannot maintain cascading referential integrity

Manipulating columns of datatype IMAGE / TEXT does not cause triggers to be executed

Only the database owner can create triggers

Triggers are executed on INSERT, UPDATE, DELETE and SELECT statements

Answer

Correct Answer: Manipulating columns of datatype IMAGE / TEXT does not cause triggers to be executed

Note: This Question is unanswered, help us to find answer for this one

216. When designing a database table, how do you avoid missing column values for non-primary key columns?

Use UNIQUE constraints

Use PRIMARY KEY constraints

Use DEFAULT and NOT NULL constraints

Use FOREIGN KEY constraints

Use SET constraints

Answer

Correct Answer: Use DEFAULT and NOT NULL constraints

Note: This Question is unanswered, help us to find answer for this one

217. What is the correct order of clauses in the select statement?

1        select
2        order by
3        where
4        having
5        group by

1,2,3,4,5

1,3,5,4,2

1,3,5,2,4

1,3,2,5,4

1,3,2,4,5

1,5,2,3,4

1,4,2,3,5

1,4,3,2,5

Answer

Correct Answer: 1,3,5,4,2

Note: This Question is unanswered, help us to find answer for this one

218. A company has the following departments:

marketing, Designing, production, Packing

What will be the result of the following query?

select * from table where department < 'marketing';

The query will return "Designing, Packing"

The query will return "Designing, production, Packing"

The query will return "packing"

Strings cannot be compared using operator

The query will return "Designing"

Answer

Correct Answer: The query will return "Designing"

Note: This Question is unanswered, help us to find answer for this one

219. Study the situation described below and identify the nature of relationship?

Each student can enroll into more than one class. Each class can accommodate more than one student.

1 to N

1 to 1

M to N to 1

M to N

N to 1

Answer

Correct Answer: M to N

Note: This Question is unanswered, help us to find answer for this one

220. Which of the following is the syntax for creating an Index?

CREATE [UNIQUE] INDEX index_name OF tbl_name (index_columns)

CREATE [UNIQUE] INDEX OF tbl_name (index_columns)

CREATE [UNIQUE] INDEX ON tbl_name (index_columns)

CREATE [UNIQUE] INDEX index_name ON tbl_name (index_columns)

Answer

Correct Answer: CREATE [UNIQUE] INDEX index_name ON tbl_name (index_columns)

Note: This Question is unanswered, help us to find answer for this one

221. Which of the following is false with regards to sp_help?

When a procedure name is passed to sp_help, it shows the parameters

When a table name is passed to sp_help, it shows the structure of the table

When no parameter is passed, it provides a list of all objects and user-defined datatypes in a database

All of the above are true

Answer

Correct Answer: All of the above are true

Note: This Question is unanswered, help us to find answer for this one

222. Select the correct option:

Optimistic locking is a locking scheme handled by the server, whereas pessimistic locking is handled by the application developer

Pessimistic locking is a locking scheme handled by the server, whereas optimistic locking is handled by the application developer

Answer

Correct Answer: Optimistic locking is a locking scheme handled by the server, whereas pessimistic locking is handled by the application developer

Note: This Question is unanswered, help us to find answer for this one

223. Consider the following two tables:

1.  customers( customer_id, customer_name)
2.  branch ( branch_id, branch_name )

What will be the output if the following query is executed:

    Select *, branch_name from customers,branch

It will return the fields customer_id, customer_name, branch_name

It will return the fields customer_id, customer_name, branch_id, branch_name

It will return the fields customer_id, customer_name, branch_id, branch_name, branch_name

It will return an empty set since the two tables do not have any common field name

It will return an error since * is used alone for one table only

Answer

Correct Answer: It will return the fields customer_id, customer_name, branch_id, branch_name, branch_name

Note: This Question is unanswered, help us to find answer for this one

224. A table has following values for its department field:

marketing, production, production, sales, NULL, NULL, Marketing, Null

What will the following query return:

Select distinct(department) from employees

marketing, production, sales

marketing, production, sales, NULL

marketing, production, sales, NULL, NULL

marketing, production, sales, NULL, Marketing

marketing, production, sales, NULL, NULL, Marketing

Answer

Correct Answer: marketing, production, sales, NULL

Note: This Question is unanswered, help us to find answer for this one

225. Is this statement true or false:

A cursor is a pointer that identifies a specific working row within a set

True

False

Answer

Correct Answer: True

Note: This Question is unanswered, help us to find answer for this one

226. In which sequence are queries and sub-queries executed by the SQL Engine?

primary query; sub query ; sub sub query and so on

sub sub query ; sub query ; prime query

the whole query is interpreted at one time

there is no fixed sequence of interpretation, the query parser takes a decision on the fly

Answer

Correct Answer: sub sub query ; sub query ; prime query

Note: This Question is unanswered, help us to find answer for this one

227. Consider the following statements and pick the correct answer:

1. ceiling() - returns the smallest integer greater than or equal to the specified value
2. floor() - returns the largest integer less than or equal to the specified value

1 is true and 2 is false

1 is false and 2 is true

Both 1 and 2 are true

Both 1 and 2 are false

Answer

Correct Answer: Both 1 and 2 are true

Note: This Question is unanswered, help us to find answer for this one

228. ___________ is the highest level of a transaction isolation implemented by SQL Server.

Read Uncommitted

Read Committed

Repeatable Read

Serializable

Answer

Correct Answer: Serializable

Note: This Question is unanswered, help us to find answer for this one

229. Examine the code given below:

SELECT employee_id FROM employees WHERE commission_pct=.5 OR salary > 23000

Which of the following statements is correct with regard to this code?

It returns employees whose salary is 50% more than $23,000

It returns employees who have 50% commission rate or salary greater than $23,000

It returns employees whose salary is 50% less than $23,000

None of the above

Answer

Correct Answer: It returns employees who have 50% commission rate or salary greater than $23,000

Note: This Question is unanswered, help us to find answer for this one

230. How can you change "Hansen" into "Nilsen" in the LastName column in the Persons
Table?

UPDATE Persons SET LastName = 'Nilsen' WHERE LastName = 'Hansen'

UPDATE Persons SET LastName = 'Hansen' INTO LastName = 'Nilsen'

SAVE Persons SET LastName = 'Nilsen' WHERE LastName = 'Hansen'

SAVE Persons SET LastName = 'Hansen' INTO LastName = 'Nilsen'

Answer

Correct Answer: UPDATE Persons SET LastName = 'Nilsen' WHERE LastName = 'Hansen'

Note: This Question is unanswered, help us to find answer for this one

231. What is wrong with the following query in accordance with performance?

SELECT * FROM [Table_Name] WHERE LOWER(Field_Name) = 'name'

The required keyword ('name') is already in the LOWER case

The query requires more time to convert to the LOWER case

T-Sql is not case sensitive. Hence, the LOWER keyword is not required

Instead of '=' the operator, 'LIKE' should be used

Answer

Correct Answer: T-Sql is not case sensitive. Hence, the LOWER keyword is not required

Note: This Question is unanswered, help us to find answer for this one

232. Which of the following is not a Method call of .NET class for Assemblies in the SQL Server?

Init

Accumulate

Aggregate

Merge

Terminate

Answer

Correct Answer: Aggregate

Note: This Question is unanswered, help us to find answer for this one

233. Which of the following is not a logical operator?

between

any

some

with

Answer

Correct Answer: with

Note: This Question is unanswered, help us to find answer for this one

234. Which of the following is not a relational operation?

Restriction

Extraction

Projection

Intersection

Union

Answer

Correct Answer: Extraction

Note: This Question is unanswered, help us to find answer for this one

235. What does DBCC stand for?

Database Consistency Checker

Database Corruption Commands

Database Consistency Commands

Database Console Commands

Answer

Correct Answer: Database Console Commands

Note: This Question is unanswered, help us to find answer for this one

236. SQL Server will create a unique nonclustered index, by default, when a primary key is created.

True

False

Answer

Correct Answer: False

Note: This Question is unanswered, help us to find answer for this one

237. Backup and restore history is stored in which database?

backuphist

msdb

master

bckhist

Answer

Correct Answer: msdb

Note: This Question is unanswered, help us to find answer for this one

238. Which data extents are backed up by a differential database backup?

All data extents changed since the most recent differential database backup by minimally-logged operations

All data extents changed since the most recent differential database backup

All data extents changed since the most recent full database backup

Answer

Correct Answer: All data extents changed since the most recent full database backup

Note: This Question is unanswered, help us to find answer for this one

239. Which system database holds information on SQL Server Agent jobs?

model

master

msdb

tempdb

Answer

Correct Answer: msdb

Note: This Question is unanswered, help us to find answer for this one

240. What is the benefit of using the TRY_CAST() function?

If the cast to the desired data type fails, a user-definable error is thrown

If the cast to the desired data type fails, the result is NULL

If the cast to the desired data type fails, the result is 0

If the cast to the desired data type fails, the cast will be performed to the correct data type

Answer

Correct Answer: If the cast to the desired data type fails, the result is NULL

Note: This Question is unanswered, help us to find answer for this one

241. How is FILESTREAM data stored?

In the database data files as varchar(max) columns

In a FileTable

As files in a file-system directory

None of these

Answer

Correct Answer: As files in a file-system directory

Note: This Question is unanswered, help us to find answer for this one

242. With automatic conflict resolution in peer-to-peer replication, which update wins the conflict?

The one from the user-configurable peer_originator_id

The one from the highest peer_originator_id

The one from the lowest peer_originator_id

None of these

Answer

Correct Answer: The one from the highest peer_originator_id

Note: This Question is unanswered, help us to find answer for this one

243. What does the ROLLBACK TRANSACTION statement do?

Rolls back to the start of the outer transaction

Rolls back to the start of the most recent nested transaction

Rolls back to the most recent savepoint

Answer

Correct Answer: Rolls back to the start of the outer transaction

Note: This Question is unanswered, help us to find answer for this one

244. Which clause allows you to filter the result of a column that has been aggregated?

NOT LIKE

WHERE

NOT IN

HAVING

Answer

Correct Answer: HAVING

Note: This Question is unanswered, help us to find answer for this one

245. What does CTE stand for?

Common Tabular Expression

Common Table Expression

Compiled Table Expression

Compiled Tabular Expression

Answer

Correct Answer: Common Table Expression

Note: This Question is unanswered, help us to find answer for this one

246. A table can have more than one primary key.

True

False

Answer

Correct Answer: False

Note: This Question is unanswered, help us to find answer for this one

247. A stored procedure can recursively call itself.

False

True

Answer

Correct Answer: True

Note: This Question is unanswered, help us to find answer for this one

248. In database mirroring, an instance can be a mirror in some mirroring sessions and a witness in other mirroring sessions.

False

True

Answer

Correct Answer: True

Note: This Question is unanswered, help us to find answer for this one

249. What are the three Transact-SQL statements that are related to authorization?

ALLOW, DENY, and REMOVE

ALLOW, DENY, and REVOKE

ADMIT, REMOVE, and REVOKE

GRANT, DENY, and REVOKE

Answer

Correct Answer: GRANT, DENY, and REVOKE

Note: This Question is unanswered, help us to find answer for this one

250. In SQL Server 2012 and later versions, the sysadmin password requires at least 8 characters and which of the following character types?

Lowercase letters

All of these

Uppercase letters

Non-alphabetic

Answer

Correct Answer: All of these

Note: This Question is unanswered, help us to find answer for this one

251. Which constraint type specifies that a column contains column values from another table?

Alien key

Foreign key

Associate key

Join key

Sibling key

Answer

Correct Answer: Foreign key

Note: This Question is unanswered, help us to find answer for this one

252. A member of the sysadmin role is allowed to perform which actions?

Any action in SQL server

Edit databases

Set user accounts

Change security settings

Answer

Correct Answer: Any action in SQL server

Note: This Question is unanswered, help us to find answer for this one

253. What is the order of precedence of ORDER BY and GROUP BY?

ORDER BY then GROUP BY

GROUP BY then ORDER BY

Answer

Correct Answer: GROUP BY then ORDER BY

Note: This Question is unanswered, help us to find answer for this one

254. A primary key can be created using a column that allows NULL values.

True

False

Answer

Correct Answer: False

Note: This Question is unanswered, help us to find answer for this one

255. To find all employees whose name ends with 'ith', complete this sql statement: "SELECT * FROM employees WHERE name " ...

IN ('ith')

LIKE "_ith"

LIKE "%ith%"

LIKE "%ith"

Answer

Correct Answer: LIKE "%ith"

Note: This Question is unanswered, help us to find answer for this one

256. Which service is used to run scheduled jobs?

SQL Server Agent Service

SQL Server Browser Service

SQL Server Reporting Service

Answer

Correct Answer: SQL Server Agent Service

Note: This Question is unanswered, help us to find answer for this one

257. Which of the following statements prevents users from performing actions?

REVOKE

REMOVE

UNGRANT

DENY

Answer

Correct Answer: DENY

Note: This Question is unanswered, help us to find answer for this one

258. True or false? Foreign keys always point to other tables.

False

True

Answer

Correct Answer: False

Note: This Question is unanswered, help us to find answer for this one

259. By default, SQL Server treats NULL as what data type

NVARCHAR

BIT

NULL does not have data type by default

INT

UNIQUEIDENTIFIER

Answer

Correct Answer: INT

Note: This Question is unanswered, help us to find answer for this one

260. When retrieving data, which command may be used to rotate a set of column names from horizontal across the top to vertically, appearing as values among the result set

PIVOT

UNPIVOT

There is no specialized command for performing this operation in SQL Server, however effect can be duplicated using other means

Answer

Correct Answer: UNPIVOT

Note: This Question is unanswered, help us to find answer for this one

261. Can you select the built in Server Roles for SQL Server from the list below?

backupoperator

datawriter

diskadmin

accessadmin

ddladmin

Answer

Correct Answer: diskadmin

Note: This Question is unanswered, help us to find answer for this one

262. What does the EOMONTH function do?

Adds the specified number of months to the input start date and then returns the last day of the month for the resulting date

Returns the last day of the month specified

None of these

Adds the specified number of weeks to the input start date and then returns the last day of the month specified

Answer

Correct Answer: Adds the specified number of months to the input start date and then returns the last day of the month for the resulting date

Note: This Question is unanswered, help us to find answer for this one

263. The command DBCC CHECKDB allows you to do what?

Locate the database

Check the average page density

Check the amount of pages

None of these

Answer

Correct Answer: Check the average page density

Note: This Question is unanswered, help us to find answer for this one

264. What is a collection of zero or more continuous CircularString and/or LineString instances of either geometry or geography types called?

StringCollection

CurveString

CompoundString

CompoundCurve

MultiString

Answer

Correct Answer: CompoundCurve

Note: This Question is unanswered, help us to find answer for this one

265. Which of the following is not a valid data type in SQL Server 2012?

datetime2

hierarchyid

smalldatetime2

datetimeoffset

smallmoney

Answer

Correct Answer: smalldatetime2

Note: This Question is unanswered, help us to find answer for this one

266. What would be the output of following statement: Select * from tbl_Employee where Emp_Name=NULL

Syntax error

Data of employees where name = null

The query will return incorrect value

Header without any data

Answer

Correct Answer: Header without any data

Note: This Question is unanswered, help us to find answer for this one

267. Which of the following is not a valid string constant or hexadecimal constant?

'02143'

'New York"

"Somerville, MA 02143"

'Boston'

Answer

Correct Answer: 'New York"

Note: This Question is unanswered, help us to find answer for this one

268. Can a unique key with not null constraint make it a primary key?

TRUE

FALSE

Answer

Correct Answer: FALSE

Note: This Question is unanswered, help us to find answer for this one

269. Which of the following standard reports will show you the fragmentation of the physical indexes?

Index Usage Statistics

Index Physical Statistics

Index Fragmentation Statistics

None of the above

All of the above

Answer

Correct Answer: Index Physical Statistics

Note: This Question is unanswered, help us to find answer for this one

270. What is the name of the database that gets created in SQL Server 2012 when you create an "Integration Services Catalog"?

SSISDB

SSISCatalog_Temp

SSISDB_Temp

SSISCatalog

SSISTempDB

Answer

Correct Answer: SSISDB

Note: This Question is unanswered, help us to find answer for this one

271. The LAG analytic function is used in a ______ statement to compare values in the current row with values in a previous row.

FIRST_VALUE

SELECT

LEAD

PREVIOUS_ROW

Answer

Correct Answer: SELECT

Note: This Question is unanswered, help us to find answer for this one

272. Which of the following stores FileStream data?

Filegroup

273. You are querying a table that contains a field with the data type XML and need only rows that have a particular value in the XML data. Which of the following would not result in an error

Neither method would return an error

SELECT * FROM #table1 WHERE xml_field1.value('(/PageContent/Text)[1]', 'varchar(100)') LIKE '%smith%'

Both methods would return an error

SELECT * FROM #table1 where xml_field1 like '%smith%'

Answer

Correct Answer: SELECT * FROM #table1 WHERE xml_field1.value('(/PageContent/Text)[1]', 'varchar(100)') LIKE '%smith%'

Note: This Question is unanswered, help us to find answer for this one

274. True or false? Intersection tables should have an autoincrement column as the primary index

False

True

Answer

Correct Answer: False

Note: This Question is unanswered, help us to find answer for this one

275. Which of the following relationships requires a reference table?

One to one

Many to many

One to many

Hierarchical

Answer

Correct Answer: Many to many

Note: This Question is unanswered, help us to find answer for this one

276. Which versions of SQL Server support table partitioning?

Enterprise edition

Enterprise, Standard, Express editions

Enterprise, Standard editions

Web edition

Answer

Correct Answer: Enterprise edition

Note: This Question is unanswered, help us to find answer for this one

277. Which of the following best describes the flow for the execution of a CLR stored procedure?

Source code > Object code > Procedure as database object

Object code > Executable code > Procedure as database object

Source code > Executable code > Procedure as database object

Source code > Object code > Executable code > Procedure as database object

Answer

Correct Answer: Source code > Object code > Executable code > Procedure as database object

Note: This Question is unanswered, help us to find answer for this one

278. TRUE OR FALSE: Is it possible to execute a Stored Procedure in a UDF.

False

TRUE

Answer

Correct Answer: False

Note: This Question is unanswered, help us to find answer for this one

279. Which of the following is used to change tracking?

Use change data capture (CDC)

Use triggers

None of these

Use triggers or use change data capture (CDC)

Answer

Correct Answer: Use triggers or use change data capture (CDC)

Note: This Question is unanswered, help us to find answer for this one

280. Which of the following statements removes one or more previously granted or denied permissions?

REVOKE

REVOKE and REMOVE

DENY and REVOKE

REMOVE

Answer

Correct Answer: REVOKE

Note: This Question is unanswered, help us to find answer for this one

281. Which statement is allowed inside of a User Defined Function?

DECLARE

INSERT

TRY....CATCH

DELETE

EXECUTE

Answer

Correct Answer: DECLARE

Note: This Question is unanswered, help us to find answer for this one

282. True or False? You cannot execute dynamic SQL code from a function.

True

False

Answer

Correct Answer: True

Note: This Question is unanswered, help us to find answer for this one

283. "Unique Constraint" can be created on a column that accepts NULL

False

True

Answer

Correct Answer: True

Note: This Question is unanswered, help us to find answer for this one

284. All Database Backup/Restore Related information stored in which system database?

MODEL

Distribution Database

MSDB

MASTER

TEMPDB

Answer

Correct Answer: MSDB

Note: This Question is unanswered, help us to find answer for this one

285. A relationship that identifies that a column contains primary key values from another table is referred as what?

Sibling key

Foreign key

Associate key

Alien key

Join key

Answer

Correct Answer: Foreign key

Note: This Question is unanswered, help us to find answer for this one

286. "SELECT name FROM t1 UNION SELECT name FROM t2 " is the same as "SELECT DISTINCT name FROM t1 UNION ALL SELECT DISTINCT name FROM t2"

True

False

Answer

Correct Answer: False

Note: This Question is unanswered, help us to find answer for this one

287. Which of the following is NOT an aggregate function in SQL Server?

COUNT

AVG

STDEV

RANK

SUM

Answer

Correct Answer: RANK

Note: This Question is unanswered, help us to find answer for this one

288. "SELECT UNIQUE city FROM customers" will return what data set?

An error.

Unique values of city from the customers table.

Unique values of customer from the table city.

Answer

Correct Answer: An error.

Note: This Question is unanswered, help us to find answer for this one

289. True or False? The EXEC keyword and the EXEC() function are equivalent to each other.

False

True

Answer

Correct Answer: False

Note: This Question is unanswered, help us to find answer for this one

290. Which of the following indices does NOT change the physical order of the rows in the table?

Both Nonclustered Indices and Clustered Indices

Nonclustered Indices

Clustered Indices

Answer

Correct Answer: Nonclustered Indices

Note: This Question is unanswered, help us to find answer for this one

291. Which statement adds a row to a table?

ADD

UPDATE

RESERT

INSERT INTO

Answer

Correct Answer: INSERT INTO

Note: This Question is unanswered, help us to find answer for this one

292. "Primary Key" can only be created on a column that doesn't accept NULL

False

True

Answer

Correct Answer: True

Note: This Question is unanswered, help us to find answer for this one

293. Which of the following statements are related to transactions?

All of these

BEGIN TRANSACTION

COMMIT [WORK]

ROLLBACK [WORK]

Answer

Correct Answer: All of these

Note: This Question is unanswered, help us to find answer for this one

294. Which clause would you use to perform conditional filtering on aggregate data in a query that uses "GROUP BY"?

FILTER BY

HAS

HAVING

WHERE

CONTAINS

Answer

Correct Answer: HAVING

Note: This Question is unanswered, help us to find answer for this one

295. What is CTE

Common Table Excess

Common Table Expresssion

Common Table Experiences

Common Table Express

Answer

Correct Answer: Common Table Expresssion

Note: This Question is unanswered, help us to find answer for this one

296. A sysadmin user is allowed to perform what actions?

Set user accounts

Edit databases

Any action in SQL server

Change security settings

Answer

Correct Answer: Any action in SQL server

Note: This Question is unanswered, help us to find answer for this one

297. What is a cursor in SQL Server?

A user interface component contained within the SQL Server Management Studio application

A special T-SQL construct used to iterate through the individual records returned by a query

A remote connection to another SQL Server

A collection of icons that can be applied to the mouse pointer on the screen

Answer

Correct Answer: A special T-SQL construct used to iterate through the individual records returned by a query

Note: This Question is unanswered, help us to find answer for this one

298. Which is the slowest?

TABLE scan

INDEX scan

Answer

Correct Answer: TABLE scan

Note: This Question is unanswered, help us to find answer for this one

299. Which of the following acts as the Database Engine

Windows security

SQL Server security

Both Windows security and SQL Server security

Answer

Correct Answer: Both Windows security and SQL Server security

Note: This Question is unanswered, help us to find answer for this one

300. What is the default port number for SQL Server?

5022

1433

1434

5023

Answer

Correct Answer: 1433

Note: This Question is unanswered, help us to find answer for this one

301. What does the "IN" clause do?

Checks whether a table contains a reference to another table

Searches a file for records belonging to a table

Compares a specified value against a list of values and is true if a match is found

Used to specify which table to iterate over in a loop using T-SQL

Answer

Correct Answer: Compares a specified value against a list of values and is true if a match is found

Note: This Question is unanswered, help us to find answer for this one

302. In regard to SQL Server Mirroring - A server instance can be a partner in some sessions and a witness in other sessions.

True

False

Answer

Correct Answer: True

Note: This Question is unanswered, help us to find answer for this one

303. What does the Query Analyzer, or SQL Server Management Studio, tool do?

Tests queries before implementing them in other applications

All of these

Creates/modifies stored procedures and execute administrative tasks

Quickly pulls information out of a database in response to a user request

Answer

Correct Answer: All of these

Note: This Question is unanswered, help us to find answer for this one

304. What is the role of a processadmin user?

Executes the BULK INSERT statement

Creates and alters databases

Manages processes running in an instance of SQL Server

Manages server logins

Answer

Correct Answer: Manages processes running in an instance of SQL Server

Note: This Question is unanswered, help us to find answer for this one

305. Differential backups do what?

Backs up all the information in the SQL Server

Backs up only the information in the selected FileTable

None of these

Copy only the information that has changed since the last full backup

Answer

Correct Answer: Copy only the information that has changed since the last full backup

Note: This Question is unanswered, help us to find answer for this one

306. In this statement, "SELECT * FROM employees e JOIN timesheets t ON t.employeeid = e.employeeid ", the JOIN term is the same as:

LEFT INNER JOIN

LEFT OUTER JOIN

FULL OUTER JOIN

INNER JOIN

Answer

Correct Answer: INNER JOIN

Note: This Question is unanswered, help us to find answer for this one

307. Data Transformation Services do what?

They provide an extremely flexible method for importing and exporting data between a Microsoft SQL Server and other formats

They provide a window into the inner workings of the database

They are used to control the MSSQLServer, MSDTC, and SQLServerAgent processes

They are the main administrative console for SQL Server installations

Answer

Correct Answer: They provide an extremely flexible method for importing and exporting data between a Microsoft SQL Server and other formats

Note: This Question is unanswered, help us to find answer for this one

308. a FOREIGN KEY constraint can reference a UNIQUE constraint

True

False

Answer

Correct Answer: True

Note: This Question is unanswered, help us to find answer for this one

309. Which of the following is a benefit of SQL Server replication?

Redundancy

Offline Processing

Load Processing

All of these

Answer

Correct Answer: All of these

Note: This Question is unanswered, help us to find answer for this one

310. What is the default isolation level in SQL Server?

Serializable

Read Commited

Snapshot

Repeatable Read

Answer

Correct Answer: Read Commited

Note: This Question is unanswered, help us to find answer for this one

311. Which of the following statements will remove a UDF?

ALTER FUNCTION

DROP FUNCTION

All of these

CREATE FUNCTION

Answer

Correct Answer: DROP FUNCTION

Note: This Question is unanswered, help us to find answer for this one

312. Consider the following queries: A: select * from [tbl1] union select * from [tbl2]; B: select * from [tbl1] union all select * from [tbl2]; What is the difference between these two queries?

Query A will return both full result sets; Query B will return both result sets with duplicates removed.

Query A and B return the same result set.

Query A will return both result sets with duplicates removed; Query B will return both full result sets.

Answer

Correct Answer: Query A will return both result sets with duplicates removed; Query B will return both full result sets.

Note: This Question is unanswered, help us to find answer for this one

313. You can create index on views.

False

True

Answer

Correct Answer: True

Note: This Question is unanswered, help us to find answer for this one

314. What does COALESCE do?

The same as RIGHT JOIN.

Combines multiple T-SQL statements into one.

The same as UNION ALL.

Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.

The same as LEFT JOIN.

Answer

Correct Answer: Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.

Note: This Question is unanswered, help us to find answer for this one

315. Which transaction isolation level allows dirty reads?

SERIALIZABLE

READ UNCOMMITED

READ COMMITED

SNAPSHOT

Answer

Correct Answer: READ UNCOMMITED

Note: This Question is unanswered, help us to find answer for this one

316. Which of the following SQL Server 2012 date functions return a value of date and/or time data type from the given parts?

All of these

SMALLDATETIMEFROMPARTS

DATEFROMPARTS

TIMEFROMPARTS

SMALLDATETIME2FROMPARTS

Answer

Correct Answer: All of these

Note: This Question is unanswered, help us to find answer for this one

317. The ________ data type can be used to store values of different data types at the same time, including numeric values, strings, and date values.

FILESTREAM

TIMESTAMP

SQL_VARIANT

UNIQUEIDENTIFIER

Answer

Correct Answer: SQL_VARIANT

Note: This Question is unanswered, help us to find answer for this one

318. Which of the following are not default database schemas?

pte

guest

sys

dbo

Answer

Correct Answer: pte

Note: This Question is unanswered, help us to find answer for this one

319. What is the "WITH RECOMPILE" statement used for?

Converts a SQL statement into machine code

Causes the SQL engine to recompile itself from source

Causes SQL Server to rebuild the query plan for a stored procedure on every call

Binds a CLR static method to a stored procedure

Answer

Correct Answer: Causes SQL Server to rebuild the query plan for a stored procedure on every call

Note: This Question is unanswered, help us to find answer for this one

320. Which of the following allows you to modify (update or delete) all values of the parent table?

CASCADE

SET NULL

NO ACTION

SET DEFAULT

Answer

Correct Answer: CASCADE

Note: This Question is unanswered, help us to find answer for this one

321. What is the name of the service that provides ETL capabilities for SQL Server for data import, data integration and data warehousing needs?

SSIS

Backup Restore

Analysis Services

Data Import & Export

Answer

Correct Answer: SSIS

Note: This Question is unanswered, help us to find answer for this one

322. When creating indexes in SQL, what two main factors must be considered?

Memory versus disk space

Network bandwidth versus memory

Tables versus functions

Performance versus disk space

Answer

Correct Answer: Performance versus disk space

Note: This Question is unanswered, help us to find answer for this one

323. What is a "CROSS APPLY" used for?

Updating a report file

Performing joins between calculated sets

Setting user permissions

Synchronizing changes between two databases

Answer

Correct Answer: Performing joins between calculated sets

Note: This Question is unanswered, help us to find answer for this one

324. Which sql function finds the length of a string field?

STRLEN

LENGTH

LEN

Answer

Correct Answer: LEN

Note: This Question is unanswered, help us to find answer for this one

325. What is the default port of Microsoft SQL Server (for TCP)?

1443

1433

1434

1343

1143

Answer

Correct Answer: 1433

Note: This Question is unanswered, help us to find answer for this one

326. In which context is the ROW_NUMBER() function valid?

select ... inner join [tbl] on ROW_NUMBER() = [tbl].[id];

select ... ROW_NUMBER() over (partition by [group_val] order by [id]) as [row] ...;

select ... ROW_NUMBER() as [row] ... group by [group_val] order by [id];

select ROW_NUMBER();

Answer

Correct Answer: select ... ROW_NUMBER() over (partition by [group_val] order by [id]) as [row] ...;

Note: This Question is unanswered, help us to find answer for this one

327. True or False? Every row in a FileTable represents a file or a directory.

True

False

Answer

Correct Answer: True

Note: This Question is unanswered, help us to find answer for this one

328. When you are dealing with lots of NULL Values in columns,which datatype is better?

Variable Length

Fixed Length

Answer

Correct Answer: Variable Length

Note: This Question is unanswered, help us to find answer for this one

329. To Improve stored procedure performance,which statement below is used to write beginning of SP?

SET NOCOUNT ON

SET STATISTICS IO ON

SET NOCOUNT OFF

SET STATISTICS TIME ON

SET STATISTICS IO OFF

Answer

Correct Answer: SET NOCOUNT ON

Note: This Question is unanswered, help us to find answer for this one

330. What does DBCC stand for?

Database Clustering Checker

Database Connection Checker

Database Consistency Commands

Database Communication Checker

Answer

Correct Answer: Database Consistency Commands

Note: This Question is unanswered, help us to find answer for this one

331. Which of the following SQL Server built-in functions is nondeterministic?

CONVERT()

ISNULL()

GETDATE()

COUNT()

LEN()

Answer

Correct Answer: GETDATE()

Note: This Question is unanswered, help us to find answer for this one

332. Given a table with the following schema: create table table1 (field1 int) The table contains 1000 records. What would the following sql statement result in: update top (100) table1 set field1 = 1

Update the field1 column to the value 1 in only 100 records in the table

Produce and error

Update the field1 column to the value 1 in all 1000 records in the table

Answer

Correct Answer: Update the field1 column to the value 1 in only 100 records in the table

Note: This Question is unanswered, help us to find answer for this one

333. GETDATE() is a ....... function?

Database Level

Nondeterministic

User Defined

Server Level

Deterministic

Answer

Correct Answer: Nondeterministic

Note: This Question is unanswered, help us to find answer for this one

334. Which Transact-SQL statement should you include at the beginning of a stored procedure to ensure that if the stored procedure raises a run-time error, the entire transaction is terminated and rolled back?

TRY

SET XACT_ABORT OFF

SET ARITHABORT ON

SET XACT_ABORT ON

Answer

Correct Answer: SET XACT_ABORT ON

Note: This Question is unanswered, help us to find answer for this one

335. True or false? Adding TOP to a select always improves performance.

False

True

Answer

Correct Answer: False

Note: This Question is unanswered, help us to find answer for this one

336. A ________ is an alphanumerical, hexadecimal, or numberic constant.

delimiters

literal value

identifiers

figurative value

Answer

Correct Answer: literal value

Note: This Question is unanswered, help us to find answer for this one

337. Which of the following storage options can be used to store LOBs and save storage?

None of these

Only in FILESTREAM

Only in Sparce columns

Both FILESTREAM and Sparse columns

Answer

Correct Answer: Both FILESTREAM and Sparse columns

Note: This Question is unanswered, help us to find answer for this one

338. In SQL Server 2012 introduces what new feature?

Always On

Online Index Reorgs

Online Index Rebuilds

Mirroring

Log Shipping

Answer

Correct Answer: Always On

Note: This Question is unanswered, help us to find answer for this one

339. Which of the following operators allows the specification of two or more expressions to be used for a query search?

WHERE

AND

Answer

Correct Answer: IN

Note: This Question is unanswered, help us to find answer for this one

340. Which of the following is not considered a Boolean Operator?

AND

WHERE

Answer

Correct Answer: IN

Note: This Question is unanswered, help us to find answer for this one

341. True or false: You can modify data in a table using a User Defined Function.

False

True

Answer

Correct Answer: False

Note: This Question is unanswered, help us to find answer for this one

342. Microsoft SQL Server 2012 includes TRY_CAST() Function, what is the advantage of using this function

When casting to desired data type failed it will result NULL

When casting to desired data type failed it will result Error

When casting to desired data type failed it will result 0

When casting to desired data type failed it will result Empty

Answer

Correct Answer: When casting to desired data type failed it will result NULL

Note: This Question is unanswered, help us to find answer for this one

343. Which of the following aggregate functions are supported by the Transact-SQL language?

All of these are supported

MAX

MIN

AVG

Answer

Correct Answer: All of these are supported

Note: This Question is unanswered, help us to find answer for this one

Microsoft SQL Server MCQs | Topic-wise

MCQ on Basic Microsoft SQL Server

General Microsoft SQL Server MCQs

Microsoft SQL Server Subjects

More Resources

Microsoft SQL Server MCQ

Microsoft SQL Server Skill Assessment

Microsoft SQL Server PDF

Related MCQs

Mobile App Design MCQs

Mobile App Testing MCQs

Mobile Web Authoring MCQs

MongoDB MCQs

MySQL MCQs

Basic Microsoft SQL Server MCQ

1. What will be the output of the following SQL query? SET SHOWPLAN_TEXT ON GO SELECT * FROM abcsite.abctable GO

2. Which of the following "insert queries" will be inserted in the XML field? The creation query of the table is: CREATE TABLE Branches(recordID int, description XML)

3. Consider the following tables: Customers - - - - - - - - Customerid Customername Address Orders - - - - - Orderid Customerid Orderdate Comments What will be the query to fetch Customername and 1st Orderdate for order placed by each customer?

4. Which of the following commands will be used to see the structure of the table named 'salary'?

5. We are creating Stored Procedure in Database and using "sp_" prefix in its name. Which of the following statements is correct in accordance with the performance of the Stored Procedure execution?

6. The commands GRANT and REVOKE are examples of :

7. What exactly is the Full-Text Catalog?

8. Consider the following table "Students": Students - - - - - - Name Hobbies John Driving Miller Fishing David Writing Robert Singing What will be the output of the following query? Select Substring(Hobbies,3) From students Where name= "john"

9. Why are Joins generally better than the Correlated sub-query?

10. Starting from the slowest, arrange the following operators in the order in which they produce results in "SQL WHERE" query. 1. >, >=, <, <= 2. <> 3. = 4. LIKE

11. Which one of the following statements is correct?

12. State whether True or False. Triggers can be created in Assemblies.

13. What exactly is a Super key?

14. __________ is a container that holds tables, views, procedures, functions and so on.

15. Which one of the following statements about Indexes is wrong?

16. Which of the following mechanisms is used by "ENCRYPTION by passphrase" to encrypt the data in the SQL SERVER 2005?

17. Which command is used to create User defined Data Type in the SQL SERVER 2005?

18. Which of the following is not a Data type in the SQL SERVER 2005?

19. Which is the best method to handle errors and deadlocks?

20. Columns are called _____________ of a table in a relational model.

21. What is the maximum storage capacity of XML data type in SQL SERVER 2005?

22. Consider the following table: Order - - - - - Orderid Customerid Orderdate Comments Select ISNULL (Comments, 'No comments') AS comments From Order What will be the output of the above query?

23. The table Comments has an auto incremented column called "Commentid" and a "UserComments" column, both of which store the comments of the users visiting the site. What will be the correct query to display the latest comments first?

24. Which of the following statements is wrong?

25. We have a table named "Customer" which has a column named "Customerid" having datatype Integer. We need to fetch the output with column title: 'Customer id is (value of customerid from the table)'. What will be the query?

26. Which of the following are correct?

27. You have a table "engineers" with the following table structure: enggid int(4) deptname varchar(50) salary int(4) You want to display the minimum and maximum salaries of the individual departments. Which of the following queries will fetch the desired results?

28. The - (Negative) and ~(Bitwise NOT) are examples of the _________ type of operators.

29. What will the following query do? Delete Orders

30. Which one of the following statements regarding "views" is incorrect?

31. A table of employees has the following values for its salary field: 10000, 11000, 12000, 10000, 14000, 12000, 13000, 10000, 14000, 10000 What will the following query return? Select distinct (salary) from employees

32. Which of the following statements regarding Trigger and Stored Procedures is correct?

33. What exactly is a Foreign key?

34. State whether True or False. The 'Having' clause can not be used when there is a 'Where' clause in the statement already.

35. State whether True or False. You can send emails through the Database engine.

36. State whether True or False. Many-to-One is a type of relationship in RDBMS.

37. Consider the following table structure of employees: empid int(4) deptname varchar(50) salary int(4) Which query will be used to display the Department (deptname) that is giving the maximum salary?

38. State whether True or False. While using the Select query, the IN clause is a better choice than the EXIST clause.

39. Which edition of the SQL SERVER 2005 will best meet the following scenario? A company has an online shopping website named www.shoppingxyz.com. It also has to handle its line of business portion. Its main server supports 4 CPUs and a multi-core processor.

40. You have a table named "employees" with the following table structure: empid int(4) deptname varchar(50) salary int(4) The following select query is executed on this table: SELECT * FROM employees ORDER BY deptname DESC, salary DESC What will be the output of the above query?

41. Which functions does the following SQL QUERY perform? DBCC CHECKIDENT ('table_name', RESEED, new_reseed_value)

42. Which of the following are not Database Models?

43. Examine the data in the EMPLOYEES table given below: LAST_NAME DEPARTMENT_ID SALARY Allen 10 3000 Miller 20 1500 King 20 2200 Davis 30 5000 Which of the following sub-queries work?

45. Which of the following statements regarding "views" are incorrect?

46. Which of the following operators perform pattern matching?

47. Which of the following are the SQL Transaction statements?

48. SQL SERVER 2005 provides a new feature to enable partitioning of the data in the database. But only those database objects can be partitioned which store data in the database. In accordance with the above statement, which of the following database objects can be partitioned?

49. You have a table "engineers" with the following table structure: enggid int(4) name varchar(50) salary int(4) You want to select the top 2 engineers in the decreasing order of their salaries, starting with the maximum salary. Which of the following SQL queries will fetch this data?

50. What is the function of "DBCC CHECKDB" command?

51. Which of the following statements is correct?

53. Which of the following is a database object?

54. Which of the following statements is/are wrong?

55. Which of the following methods is used to backup the Transaction log on to a secondary database in order to backup and recover data in a synchronized manner?

56. Which data type in the SQL SERVER 2005 converts itself into the type of data that is inserted into it?

57. Which query will be used for a particular column of a table, if 'Reading' is to be replaced by 'Writing'?

58. You want to select a row using cursors in the table 'qlist'. Which of the following statements DECLARES a cursor 'qselect' to perform the desired operation?

59. Consider the following query: Select name from employees where name like "%_n_" Which names will be displayed?

60. Which of the following is not an Aggregate function?

61. Which level of data abstraction describes the data that is stored in the database and their corresponding relationships?

62. Which of the following SQL queries correctly selects the rows from the table "employees" that have NULL value in the "salary" column?

63. Which of the following is the new sample database in the SQL SERVER 2005?

64. State whether True or False. The plus (+) and minus (-) operators cannot be used to perform increment/decrement operations on datetime and smalldatetime values.

66. Which among the following are the new datatypes introduced in the SQL Server 2005?

67. Which clause will be used to extract data from Tables A and B having a similar structure but no relation between themselves?

68. Which of the following is not a type of Database backup in the SQL SERVER 2005?

69. State whether True or False. The "text" and "ntext" data type can store character based data. These data types can be used in joins

70. Which of the following operators will be evaluated first in the following statement? Select (salary+40^2*30/5) from employees

71. Suppose e1 and e2 denote two decimal expressions with precisions p1 and p2 and scales s1 and s2, respectively. What is the result precision of the following operation on the two expressions?

72. In which of the following statements can TOP clause be used in SQL Server 2008?

73. Which of the following commands is used to start the Resource Governor in SQL Server 2008?

75. Analyze the following permissions: 1.ALTER permission on the database. 2.CONTROL permission in the database. 3.ALTER ANY SCHEMA permission and CREATE XML SCHEMA COLLECTION permission in the database. Which of the above permissions is/are required to create an XML SCHEMA COLLECTION?

76. A local temporary stored procedure name can contain a maximum of __________ characters

77. FILESTREAM storage is best used when the BLOB file sizes average 1MB or higher.

78. Which of the following statements using GROUP BY clause are allowed in SQL Server 2008?

79. Which of the following is used to comment a portion of an SQL Server 2008 script?

80. Which of the following is the correct precedence order of the data types in SQL Server 2008? 1.xml 2.text 3.bigint 4.image 5.float 6.datetime

81. Which of the following queries is used to rename a database in SQL Server 2008?

82. The output of a Transact SQL query using the GROUPING function of SQL Server 2008 is of __________ return type.

83. What should a computed column be marked as so that SQL Server Database Engine can physically store values in a table?

1.
What will be the output of the following SQL query? SET SHOWPLAN_TEXT ON GO SELECT * FROM abcsite.abctable GO

2.
Which of the following "insert queries" will be inserted in the XML field?
The creation query of the table is:
CREATE TABLE Branches(recordID int, description XML)

3.
Consider the following tables:

Customers
- - - - - - - -
Customerid
Customername
Address

Orders
- - - - -
Orderid
Customerid
Orderdate
Comments
What will be the query to fetch Customername and 1st Orderdate for order placed by each customer?

4.

Which of the following commands will be used to see the structure of the table named 'salary'?

5.

We are creating Stored Procedure in Database and using "sp_" prefix in its name. Which of the following statements is correct in accordance with the performance of the Stored Procedure execution?

6.

The commands GRANT and REVOKE are examples of :

7.

What exactly is the Full-Text Catalog?

8.

Consider the following table "Students":

Students

- - - - - -

Name Hobbies

John Driving

Miller Fishing

David Writing

Robert Singing

What will be the output of the following query?

Select Substring(Hobbies,3) From students Where name= "john"

9.

Why are Joins generally better than the Correlated sub-query?

10.

Starting from the slowest, arrange the following operators in the order in which they produce results in "SQL WHERE" query.

1. >, >=, <, <=

2. <>

3. =

4. LIKE

11.

Which one of the following statements is correct?

12.

State whether True or False.

Triggers can be created in Assemblies.

13.

What exactly is a Super key?

14.

__________ is a container that holds tables, views, procedures, functions and so on.

15.

Which one of the following statements about Indexes is wrong?

16.

Which of the following mechanisms is used by "ENCRYPTION by passphrase" to encrypt the data in the SQL SERVER 2005?

17.

Which command is used to create User defined Data Type in the SQL SERVER 2005?

18.

Which of the following is not a Data type in the SQL SERVER 2005?

19.

Which is the best method to handle errors and deadlocks?

20.

Columns are called _____________ of a table in a relational model.

21.

What is the maximum storage capacity of XML data type in SQL SERVER 2005?

22.

Consider the following table:

Order

- - - - -

Orderid

Customerid

Orderdate

Comments

Select ISNULL (Comments, 'No comments') AS comments From Order

What will be the output of the above query?

23.

The table Comments has an auto incremented column called "Commentid" and a "UserComments" column, both of which store the comments of the users visiting the site. What will be the correct query to display the latest comments first?

24.

Which of the following statements is wrong?

25.

We have a table named "Customer" which has a column named "Customerid" having datatype Integer. We need to fetch the output with column title: 'Customer id is (value of customerid from the table)'. What will be the query?

26.

Which of the following are correct?

27.

You have a table "engineers" with the following table structure:

enggid int(4)

deptname varchar(50)

salary int(4)

You want to display the minimum and maximum salaries of the individual departments. Which of the following queries will fetch the desired results?

28.

The - (Negative) and ~(Bitwise NOT) are examples of the _________ type of operators.

29.

What will the following query do?

Delete Orders

30.

Which one of the following statements regarding "views" is incorrect?

31.

A table of employees has the following values for its salary field:

10000, 11000, 12000, 10000, 14000, 12000, 13000, 10000, 14000, 10000

What will the following query return?

Select distinct (salary) from employees

32.

Which of the following statements regarding Trigger and Stored Procedures is correct?

33.

What exactly is a Foreign key?

34.

State whether True or False.

The 'Having' clause can not be used when there is a 'Where' clause in the statement already.

35.

State whether True or False.

You can send emails through the Database engine.

36.

State whether True or False.

Many-to-One is a type of relationship in RDBMS.

37.

Consider the following table structure of employees:

empid int(4)

deptname varchar(50)

salary int(4)

Which query will be used to display the Department (deptname) that is giving the maximum salary?

38.

State whether True or False.

While using the Select query, the IN clause is a better choice than the EXIST clause.

39.

Which edition of the SQL SERVER 2005 will best meet the following scenario?

A company has an online shopping website named www.shoppingxyz.com. It also has to handle its line of business portion. Its main server supports 4 CPUs and a multi-core processor.

40.

You have a table named "employees" with the following table structure:

empid int(4)

deptname varchar(50)

salary int(4)

The following select query is executed on this table:

SELECT * FROM employees ORDER BY deptname DESC, salary DESC

What will be the output of the above query?

41.

Which functions does the following SQL QUERY perform?

DBCC CHECKIDENT ('table_name', RESEED, new_reseed_value)

42.

Which of the following are not Database Models?

43.

Examine the data in the EMPLOYEES table given below:

LAST_NAME DEPARTMENT_ID SALARY

Allen 10 3000

Miller 20 1500

King 20 2200

Davis 30 5000

Which of the following sub-queries work?

45.

Which of the following statements regarding "views" are incorrect?

46.

Which of the following operators perform pattern matching?

47.

Which of the following are the SQL Transaction statements?

48.

SQL SERVER 2005 provides a new feature to enable partitioning of the data in the database. But only those database objects can be partitioned which store data in the database. In accordance with the above statement, which of the following database objects can be partitioned?

49.

You have a table "engineers" with the following table structure:

enggid int(4)

name varchar(50)

salary int(4)

You want to select the top 2 engineers in the decreasing order of their salaries, starting with the maximum salary. Which of the following SQL queries will fetch this data?

50.

What is the function of "DBCC CHECKDB" command?

51.
Which of the following statements is correct?

53.
Which of the following is a database object?

54.
Which of the following statements is/are wrong?

55.
Which of the following methods is used to backup the Transaction log on to a secondary database in order to backup and recover data in a synchronized manner?

56.
Which data type in the SQL SERVER 2005 converts itself into the type of data that is inserted into it?

57.
Which query will be used for a particular column of a table, if 'Reading' is to be replaced by 'Writing'?

58.
You want to select a row using cursors in the table 'qlist'. Which of the following statements DECLARES a cursor 'qselect' to perform the desired operation?

59.
Consider the following query:

Select name from employees where name like "%_n_"

Which names will be displayed?

60.
Which of the following is not an Aggregate function?

61.
Which level of data abstraction describes the data that is stored in the database and their corresponding relationships?

62.
Which of the following SQL queries correctly selects the rows from the table "employees" that have NULL value in the "salary" column?

63.
Which of the following is the new sample database in the SQL SERVER 2005?

64.
State whether True or False.

The plus (+) and minus (-) operators cannot be used to perform increment/decrement operations on datetime and smalldatetime values.

66.
Which among the following are the new datatypes introduced in the SQL Server 2005?

67.
Which clause will be used to extract data from Tables A and B having a similar structure but no relation between themselves?

68.
Which of the following is not a type of Database backup in the SQL SERVER 2005?

69.
State whether True or False.

The "text" and "ntext" data type can store character based data. These data types can be used in joins

70.
Which of the following operators will be evaluated first in the following statement?

Select (salary+40^2*30/5) from employees

71.
Suppose e1 and e2 denote two decimal expressions with precisions p1 and p2 and scales s1 and s2, respectively. What is the result precision of the following operation on the two expressions?

72.
In which of the following statements can TOP clause be used in SQL Server 2008?

73.
Which of the following commands is used to start the Resource Governor in SQL Server 2008?

75.
Analyze the following permissions:

1.ALTER permission on the database.

2.CONTROL permission in the database.

3.ALTER ANY SCHEMA permission and CREATE XML SCHEMA COLLECTION permission in the database.

Which of the above permissions is/are required to create an XML SCHEMA COLLECTION?

76.
A local temporary stored procedure name can contain a maximum of __________ characters

77.
FILESTREAM storage is best used when the BLOB file sizes average 1MB or higher.

78.
Which of the following statements using GROUP BY clause are allowed in SQL Server 2008?

79.
Which of the following is used to comment a portion of an SQL Server 2008 script?

80.
Which of the following is the correct precedence order of the data types in SQL Server 2008?

1.xml

2.text

3.bigint

4.image

5.float

6.datetime

81.
Which of the following queries is used to rename a database in SQL Server 2008?

82.
The output of a Transact SQL query using the GROUPING function of SQL Server 2008 is of __________ return type.

83.
What should a computed column be marked as so that SQL Server Database Engine can physically store values in a table?

84.
Which of the following is a valid code to remove the key used to encrypt a database using Transparent Data Encryption algorithm?