SQL Server 2008 Online Quiz Questions And Answers

31. Choose the incorrect option about the sql server index
A. Two types of indexes - clustered indexes and non-clustered indexes
B. Both types use B-TREE for searching data
C. Only one clustered index on a table
D. More than one clustered index on a table
Ans: D

32. By default "FILLFACTOR" value is
A. Zero
B. One
C. Two
D. Five
Ans: A

33. Which of the following DBCC command is used to see when was the last time the index rebuild?
A. DBCC SHOW_STATISTICS
B. DBCC SHOWCONFIG
C. DBCC DBREINDEX
Ans: A

34. Can a clustered index also be a unique index?
A. Yes
B. No
Ans: A

35. Is a composite index key always part of a covering index?
A. Yes
B. No
Ans: B

36. Which type of integrity preserves the defined relationship between tables when records are entered or deleted?
A. Entity integrity
B. Domain integrity
C. Referential integrity
D. User-defined integrity
Ans: C

37. How many Primary key constraints can be included in a table definition?
A. One
B. Two
Ans: A

38. Which statement is used to delete all rows in a table without having the action logged?
A. Drop table statement
B. Delete table statement
C. Truncate table statement
Ans: C

39. Tools for importing and exporting data in sql server
A. bcp command utility
B. BULK inserts statement
C. DTS
D. ALL of the above
Ans: D

40. Default is a type of constraint although it does not really enforce anything
A. Yes
B. No
Ans: A

More Questions and Answers:
Page1 Page2 Page3 Page4

SQL Server 2008 Most Recently Asked Questions and Answers

21. How inserting data through stored procedure do reduces network traffic and increase database performance?
A. Stored procedure can accept parameter
B. Permission check is not required
C. The execution plan is stored in the cache after it was executed the first time.
Ans: C

22. Which statement is used to define a cursor?
A. OPEN
B. FETCH
C. DECLARE CURSOR
D. @@FETCH_STATUS
Ans: C

23. Cursor that reflects the changes made to the database table even after the result set is returned
A. Static
B. Dynamic
C. FORWARD_ONLY
D. Keyset
Ans: B

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
D. a,b and c
Ans: D

25. Which global variables can be used to determine if a transaction is still open?
A. @@NESTLEVEL
B. @@FETCH_STATUS
C. @@TRANCOUNT
D. @@CONNECTIONS
Ans: C

26. Which of the following connection type supports application role permissions and password encryption?
A. OLE DB
B. DBLib
C. ODBC
D. OLE DB and ODBC
Ans: D

27. By default sql server has ___________ isolation level
A. READ COMMITTED
B. READ UNCOMMITTED
C. SERIALIZABLE
D. REPEATABLE READ
Ans: A

28. Problems occurs if we don’t implement proper locking strategy
A. Dirty reads
B. Phantom reads
C. Lost updates
D. Unrepeatable reads
E. ALL of the above
Ans: E

29. What is the default "SORT" order for a SQL?
A. Ascending
B. Descending
Ans: A

30. Which of the following is not correct for DELETE and TRUNCATE?
A. DELETE table can be rolled back
B. TRUNCATE table cannot have triggers
C. TRUNCATE table can have criteria
D. DELETE table can have criteria
Ans: C

More Questions and Answers:
Page1 Page2 Page3 Page4

SQL Server 2008 Multiple Choice Questions And Answers

11. A column with an optimized storage for null values is known as __________ column
A. spatial
B. optimum
C. dense
D. sparse
Ans: D

12. _________ is a feature that is used for tracking the changes on a table
A. Change schema Capture
B. Change table Capture
C. Change Data Capture
D. Change database Capture
Ans: C

13. What is the purpose of MERGE in sql server 2008?
A. Merge statement allows a single statement for INSERT, DELETE and UPDATE a row that depends on a condition
B. Merge statement allows multiple statements for INSERT, DELETE and UPDATE a row that depends on a condition
C. Merge statement allows a single statement for INSERT, DELETE and UPDATE a row that is independant on a condition
D. Merge statement allows multiple statements for INSERT, DELETE and UPDATE a row that is independant on a condition
Ans: A

14. Which of the following statements is/are true for SQL server?
A. SQL Server has Logical Architecture and Physical Architecture.
B. Both a and c
C. SQL Server is compatible with structured query language and has rich support for XML
D. SQL Server is a DBMS, a complete database.
Ans: B

15. SQL Server 2000 always returns the result of a FOR XML query directly to the client in XML form
A. True
B. False
Ans: B

16. Which of the following is/are false for RAW mode of FOR XML?
A. XMLSCHEMA option does not returns an in-line XSD schema.
B. BINARY BASE32 returns the binary data in base32-encoded format.
C. Each row in the query result is transformed into an XML element
D. Both a and b above
Ans: D

17. Which of the following statement is used to make sql server resize the database file to its smallest possible size?
A. DBCC SHRINKFILE
B. DBCC SHRINKDB
C. DBCC SQLPERF SHRINKDB
Ans: A

18. Difference between UNION and UNION ALL.
A. Both are similar
B. UNION ALL is no longer supported in sql server 2000 onward
C. UNION returns only unique records from both tables. UNION ALL returns all records from both tables
Ans: C

19. Which statement is correct from partitioned view?
A. We cannot create an indexed view on a partitioned view
B. We can create an indexed view on a partitioned view as long as the index is clustered
C. We can create an indexed view on a partitioned view
Ans: C

20. Which utilities can we used to export data from sql server to a text file?
A.  DTS export wizard
B.  BCP
C.  ISQL
D. A and b
Ans: D

More Questions and Answers:
Page1 Page2 Page3 Page4

TOP 40 SQL Server 2008 Objective type Questions and Answers

1. Which of the following statements is/are false for Extended events in sql server 2008?
A. Enhanced event handling system in SQL Server
B. Both b and c below
C. Used for problem identification or problem trouble shooting
D. An architecture that is not very scalable, highly configurable
Ans: D

2. Permanent setting of specific server characteristics is done by trace tags
A. False
B. True
Ans: A

3. Which of the following rules below are true to use the ROWGUIDCOL property to define a globally unique identifier column?
A. A table can have only one ROWGUIDCOL column
B. A ROWGUIDCOL column must not be defined by using the uniqueidentifier data type.
C. Both a and c
D. NEWID function to generate a globally unique value.
Ans: C

4. The identifers in SQL can have a character range from?
A. 1 to 100
B. 1 to 128
C. 1 to 64
D. 1 to 132
Ans: D

5. Embedded spaces or special characters are not allowed in SQL server identifiers
A. True
B. False
Ans:A

6. ________ helps to restore the database to the point where it failed.
A. File backup
B. Differential Backups
C. File log
D. Transaction log
Ans: D

7. Which of the following statements is/are false for Declarative Management Framework (DMF) in sql server 2008?
A. System for managing the instances of SQL Server 2008
B. SQL Server does not enforce to define the policies by using Management Studio
C. Both b and d
D. A policy can be designed for allowing the usage of unauthorized applications
Ans: C

8. Dynamic Page Repair is a type of database mirroring.
A. False
B. True
Ans: A

9. CPUs can dynamically be added to a running system, by using ____________ feature.
A. Cold Add CPU
B. Hot Add CPU
C. Dynamic add CPU
D. Passive add CPU
Ans: B

10. Remote BLOB storageis a ____________ API which reduces the application building complexity.
A. Server-side
B. Client-side
Ans: B

More Questions and Answers:
Page1 Page2 Page3 Page4