1.
Which of the following statements is/are false for Extended events in sql
server 2008?
Enhanced event handling system in SQL Server
Both b and c below
Used for problem identification or problem trouble shooting
An architecture that is not very scalable, highly configurable
You answered: Used for problem identification or problem trouble shooting
Incorrect
2. Permanent setting of specific server characteristics is done by trace tags
True False
You answered: True
Incorrect
3. Which of the following rules below are true to use the ROWGUIDCOL
property to define a globally unique identifier column?
A table can have only one ROWGUIDCOL column
A ROWGUIDCOL column must not be defined by using the uniqueidentifier data type.
Both a and c
NEWID function to generate a globally unique value.
You answered: A table can have only one ROWGUIDCOL column
Incorrect
4. The identifers in SQL can have a character range from?
1 to 100
1 to 128
1 to 64
1 to 132
You answered: 1 to 100
Incorrect
5. Embedded spaces or special characters are not allowed in SQL server
identifiers
True
False
You answered: False
Incorrect
6. ______________ helps to restore the database to the point where it failed.
File backup
Differential Backups
File log
Transaction log
Correct
7. Which of the following statements is/are false for Declarative Management
Framework (DMF) in sql server 2008?
System for managing the instances of SQL Server 2008
SQL Server does not enforce to define the policies by using Management Studio
Both b and d
A policy can be designed for allowing the usage of unauthorized applications
Correct
8. Dynamic Page Repair is a type of database mirroring.
You answered: True
Incorrect
9. CPUs can dynamically be added to a running system, by using ____________
feature
Cold Add CPU
Hot Add CPU
Dynamic add CPU
Passive add CPU
You answered: Passive add CPU
Incorrect
10. Remote BLOB storageis a ____________ API which reduces the application
building complexity
Server-side
Client-side
Correct
11. A column with an optimized storage for null values is known as __________
column
spatial
optimum
dense
sparse
You answered: spatial
Incorrect
12. _______________ is a feature that is used for tracking the changes on a table
Change schema Capture
Change table Capture
Change Data Capture
Change database Capture
You answered: Change table Capture
Incorrect
13. What is the purpose of MERGE in sql server 2008?
Merge statement allows a single statement for INSERT, DELETE and UPDATE a row that depends on a
condition
Merge statement allows multiple statements for INSERT, DELETE and UPDATE a row that depends on
a condition
Merge statement allows a single statement for INSERT, DELETE and UPDATE a row that is
independant on a condition
Merge statement allows multiple statements for INSERT, DELETE and UPDATE a row that is
independant on a condition
You answered: Merge statement allows a single statement for INSERT, DELETE
and UPDATE a row that is independant on a condition
Incorrect
14. Which of the following statements is/are true for SQL server?
SQL Server has Logical Architecture and Physical Architecture.
Both a and c
SQL Server is compatible with structured query language and has rich support for XML
SQL Server is a DBMS, a complete database.
You answered: SQL Server is compatible with structured query language and has
rich support for XML
Incorrect
15. SQL Server 2000 always returns the result of a FOR XML query directly to
the client in XML form
True
False
You answered: True
Incorrect
16. Which of the following is/are false for RAW mode of FOR XML?
XMLSCHEMA option does not returns an in-line XSD schema.
BINARY BASE32 returns the binary data in base32-encoded format.
Each row in the query result is transformed into an XML element
Both a and b above
You answered: Each row in the query result is transformed into an XML element
Incorrect
17. Which of the following statement is used to make sql server resize the
database file to its smallest possible size?
DBCC SHRINKFILE
DBCC SHRINKDB
DBCC SQLPERF SHRINKDB
You answered: DBCC SHRINKDB
Incorrect
18. Difference between UNION and UNION ALL.
Both are similar
UNION ALL is no longer supported in sql server 2000 onward
UNION returns only unique records from both tables. UNION ALL returns all records from both tables
Correct
19. Which statement is correct from partitioned view?
We cannot create an indexed view on a partitioned view
We can create an indexed view on a partitioned view as long as the index is clustered
We can create an indexed view on a partitioned view
Correct
20. Which utilities can we used to export data from sql server to a text file?
a. DTS export wizard
b. BCP
c. ISQL
a and b
Correct
21. How inserting data through stored procedure do reduces network traffic and
increase database performance?
Stored procedure can accept parameter
Permission check is not required
The execution plan is stored in the cache after it was executed the first time.
Correct
22. Which statement is used to define a cursor?
OPEN
FETCH
DECLARE CURSOR
@@FETCH_STATUS
Correct
23. Cursor that reflects the changes made to the database table even after the
result set is returned
Static
Dynamic
FORWARD_ONLY
Keyset
You answered: FORWARD_ONLY
Incorrect
24. Capabilities of RAISERROR
a. It can be logged in the error log
b. It can print a message to the application
c. It can assign an error number, state and severity
a,b and c
Correct
25. Which global variables can be used to determine if a transaction is still open?
@@NESTLEVEL
@@FETCH_STATUS
@@TRANCOUNT
@@CONNECTIONS
Correct
26. Which of the following connection type supports application role permissions
and password encryption?
OLE DB
DBLib
ODBC
OLE DB and ODBC
Correct
27. By default sql server has ___________ isolation level
READ COMMITTED
READ UNCOMMITTED
SERIALIZABLE
REPEATABLE READ
Correct
28. Problems occurs if we don’t implement proper locking strategy
Dirty reads
Phantom reads
Lost updates
Unrepeatable reads
ALL of the above
Correct
29. What is the default "SORT" order for a SQL?
Ascending
Descending
Correct
30. Which of the following is not correct for DELETE and TRUNCATE?
DELETE table can be rolled back
TRUNCATE table cannot have triggers
TRUNCATE table can have criteria
DELETE table can have criteria
Correct
31. Choose the incorrect option about the sql server index
Two types of indexes - clustered indexes and non-clustered indexes
Both types use B-TREE for searching data
Only one clustered index on a table
More than one clustered index on a table
You answered: Only one clustered index on a table
Incorrect
32. By default "FILLFACTOR" value is
Zero
One
Two
Five
Correct
33. Which of the following DBCC command is used to see when was the last time
the index rebuild?
DBCC SHOW_STATISTICS
DBCC SHOWCONFIG
DBCC DBREINDEX
You answered: DBCC DBREINDEX
Incorrect
34. Can a clustered index also be a unique index?
Yes
No
Correct
35. Is a composite index key always part of a covering index?
Yes
No
You answered: Yes
Incorrect
36. Which type of integrity preserves the defined relationship between tables
when records are entered or deleted?
Entity integrity
Domain integrity
Referential integrity
User-defined integrity
Correct
37. How many Primary key constraints can be included in a table definition?
One
Two
Correct
38. Which statement is used to delete all rows in a table without having the
action logged?
Drop table statement
Delete table statement
Truncate table statement
Correct
39. Tools for importing and exporting data in sql server
bcp command utility
BULK inserts statement
DTS
ALL of the above
Correct
40. Default is a type of constraint although it does not really enforce anything
Yes
No
Correct
