Microsoft SQL Server MCQ Questions Answers

1. Which of the following aggregate functions are supported by the Transact-SQL language?

2. Microsoft SQL Server 2012 includes TRY_CAST() Function, what is the advantage of using this function

3. True or false: You can modify data in a table using a User Defined Function.

4. Which of the following is not considered a Boolean Operator?

5. Which of the following operators allows the specification of two or more expressions to be used for a query search?

6. In SQL Server 2012 introduces what new feature?

7. Which of the following storage options can be used to store LOBs and save storage?

8. A ________ is an alphanumerical, hexadecimal, or numberic constant.

9. True or false? Adding TOP to a select always improves performance.

10. 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?

11. GETDATE() is a ....... function?

12. 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

13. Which of the following SQL Server built-in functions is nondeterministic?

14. What is the correct syntax for a CTE?

15. What does DBCC stand for?

16. To Improve stored procedure performance,which statement below is used to write beginning of SP?

17. When you are dealing with lots of NULL Values in columns,which datatype is better?

18. True or False? Every row in a FileTable represents a file or a directory.

19. In which context is the ROW_NUMBER() function valid?

20. What is the default port of Microsoft SQL Server (for TCP)?

21. Which sql function finds the length of a string field?

22. What is a 'CROSS APPLY' used for?

23. When creating indexes in SQL, what two main factors must be considered?

24. What is the name of the service that provides ETL capabilities for SQL Server for data import, data integration and data warehousing needs?

25. Which of the following allows you to modify (update or delete) all values of the parent table?

26. What is the 'WITH RECOMPILE' statement used for?

27. Which of the following are not default database schemas?

28. The ________ data type can be used to store values of different data types at the same time, including numeric values, strings, and date values.

29. Which of the following SQL Server 2012 date functions return a value of date and/or time data type from the given parts?

30. Which transaction isolation level allows dirty reads?

31. What does COALESCE do?

32. You can create index on views.

33. 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?

34. Which of the following statements will remove a UDF?

35. What is the default isolation level in SQL Server?

36. What is the basic difference between varchar and nvarchar?

37. Which of the following is a benefit of SQL Server replication?

38. a FOREIGN KEY constraint can reference a UNIQUE constraint

39. Data Transformation Services do what?

40. In this statement, 'SELECT * FROM employees e JOIN timesheets t ON t.employeeid = e.employeeid ', the JOIN term is the same as:

41. Differential backups do what?

42. What is the role of a processadmin user?

43. What does the Query Analyzer, or SQL Server Management Studio, tool do?

44. In regard to SQL Server Mirroring - A server instance can be a partner in some sessions and a witness in other sessions.

45. What does the 'IN' clause do?

46. What is the default port number for SQL Server?

47. Which of the following acts as the Database Engine's security system?

48. Which is the slowest?

49. What is a cursor in SQL Server?

50. A sysadmin user is allowed to perform what actions?

51. What is CTE

52. Which clause would you use to perform conditional filtering on aggregate data in a query that uses 'GROUP BY'?

53. Which of the following statements are related to transactions?

54. 'Primary Key' can only be created on a column that doesn't accept NULL

55. What does 'UDF' stand for?

56. Which statement adds a row to a table?

57. Which of the following indices does NOT change the physical order of the rows in the table?

58. True or False? The EXEC keyword and the EXEC() function are equivalent to each other.

59. 'SELECT UNIQUE city FROM customers' will return what data set?

60. Which of the following is NOT an aggregate function in SQL Server?

61. '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'

62. A relationship that identifies that a column contains primary key values from another table is referred as what?

63. All Database Backup/Restore Related information stored in which system database?

64. 'Unique Constraint' can be created on a column that accepts NULL

65. True or False? You cannot execute dynamic SQL code from a function.

66. Which statement is allowed inside of a User Defined Function?

67. Which of the following statements removes one or more previously granted or denied permissions?

68. Which of the following is used to change tracking?

69. TRUE OR FALSE: Is it possible to execute a Stored Procedure in a UDF.

70. Which of the following best describes the flow for the execution of a CLR stored procedure?

71. Which versions of SQL Server support table partitioning?

72. Which of the following relationships requires a reference table?

73. True or false? Intersection tables should have an autoincrement column as the primary index

74. 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

75. Which of the following stores FileStream data?

76. The LAG analytic function is used in a ______ statement to compare values in the current row with values in a previous row.

77. What is the name of the database that gets created in SQL Server 2012 when you create an 'Integration Services Catalog'?

78. Which of the following standard reports will show you the fragmentation of the physical indexes?

79. Can a unique key with not null constraint make it a primary key?

80. Which of the following is not a valid string constant or hexadecimal constant?

81. What would be the output of following statement: Select * from tbl_Employee where Emp_Name=NULL

82. Which of the following is not a valid data type in SQL Server 2012?

83. What is a collection of zero or more continuous CircularString and/or LineString instances of either geometry or geography types called?

84. The command DBCC CHECKDB allows you to do what?

85. The SETCERT.EXE utility can be used to do what?

86. What does the EOMONTH function do?

87. Can you select the built in Server Roles for SQL Server from the list below?

88. 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

89. By default, SQL Server treats NULL as what data type

90. True or false? Foreign keys always point to other tables.

91. Which of the following statements prevents users from performing actions?

92. Which service is used to run scheduled jobs?

93. To find all employees whose name ends with 'ith', complete this sql statement: 'SELECT * FROM employees WHERE name ' ...

94. A primary key can be created using a column that allows NULL values.

95. What is the order of precedence of ORDER BY and GROUP BY?

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

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

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

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

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