Microsoft SQL Server Test

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

Answers:

• MIN

• AVG

• All of these are supported

• MAX

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

Answers:

• select ROW_NUMBER();

• 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 ... inner join [tbl] on ROW_NUMBER() = [tbl].[id];

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

Answers:

• Only in Sparce columns

• Only in FILESTREAM

• Both FILESTREAM and Sparse columns

• None of these

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

Answers:

• Many to many

• One to one

• One to many

• Hierarchical

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

Answers:

• "Somerville, MA 02143"

• 'Boston'

• '02143'

• 'New York"

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

Answers:

• SSISDB

• SSISDB_Temp

• SSISCatalog

• SSISTempDB

• SSISCatalog_Temp

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

Answers:

• EXECUTE

• DELETE

• TRY....CATCH

• DECLARE

• INSERT

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

Answers:

• SUM

• COUNT

• AVG

• STDEV

• RANK

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

Answers:

• TRY

• SET XACT_ABORT OFF

• SET XACT_ABORT ON

• SET ARITHABORT ON

10. What does DBCC stand for?

Answers:

• Database Consistency Commands

• Database Clustering Checker

• Database Connection Checker

• Database Communication Checker

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

Answers:

• False

• True

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

Answers:

• SET STATISTICS TIME ON

• SET STATISTICS IO ON

• SET STATISTICS IO OFF

• SET NOCOUNT OFF

• SET NOCOUNT ON

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

Answers:

• Data Import & Export

• Backup Restore

• SSIS

• Analysis Services

14. Which transaction isolation level allows dirty reads?

Answers:

• READ UNCOMMITED

• SERIALIZABLE

• READ COMMITED

• SNAPSHOT

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

Answers:

• STRLEN

• LEN

• LENGTH

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

Answers:

• Performance versus disk space

• Tables versus functions

• Memory versus disk space

• Network bandwidth versus memory

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

Answers:

• Read Commited

• Snapshot

• Serializable

• Repeatable Read

18. What is the "WITH RECOMPILE" statement used for?

Answers:

• Converts a SQL statement into machine code

• Binds a CLR static method to a stored procedure

• Causes the SQL engine to recompile itself from source

• Causes SQL Server to rebuild the query plan for a stored procedure on every call

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

Answers:

• When casting to desired data type failed it will result 0

• 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 Empty

20. What is a "CROSS APPLY" used for?

Answers:

• Setting user permissions

• Performing joins between calculated sets

• Updating a report file

• Synchronizing changes between two databases

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

Answers:

• Query A and B return the same result set.

• Query A will return both full result sets; Query B will return both result sets with duplicates removed.

• Query A will return both result sets with duplicates removed; Query B will return both full result sets.

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

Answers:

• CREATE FUNCTION

• ALTER FUNCTION

• All of these

• DROP FUNCTION

23. You can create index on views.

Answers:

• True

• False

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

Answers:

• Fixed Length

• Variable Length

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

Answers:

• NO ACTION

• SET NULL

• SET DEFAULT

• CASCADE

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

Answers:

• pte

• dbo

• sys

• guest

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

Answers:

• All of these

• Offline Processing

• Redundancy

• Load Processing

28. a FOREIGN KEY constraint can reference a UNIQUE constraint

Answers:

• False

• True

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

Answers:

• All of these

• SMALLDATETIME2FROMPARTS

• DATEFROMPARTS

• SMALLDATETIMEFROMPARTS

• TIMEFROMPARTS

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

Answers:

• FILESTREAM

• SQL_VARIANT

• TIMESTAMP

• UNIQUEIDENTIFIER

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

Answers:

• varchar supports unicode data.

• nvarchar supports large blob.

• nvarchar performs faster then varchar.

• nvarchar supports unicode data.

32. Differential backups do what?

Answers:

• None of these

• Backs up only the information in the selected FileTable

• Backs up all the information in the SQL Server

• Copy only the information that has changed since the last full backup

33. What is order of precedence of order by group by?

Answers:

• group by then order by

• order by then group by

34. True or false:  A stored procedure can recursively call itself.

Answers:

• False

• True

35. What are the 3 Transact-SQL statements that are related to authorization?

Answers:

• GRANT, DENY, and REVOKE

• ALLOW, DENY, REMOVE

• ALLOW, DENY, REVOKE

• ADMIT, REMOVE, REVOKE

36. To run Schedule Job in SQL Server, which services used?

Answers:

• SQL Server Browser Service

• Option 1 & 2

• None of the above

• SQL Server Reporting Service

• SQL Server Agent Service

37. Data Transformation Services do what?

Answers:

• They are used to control the MSSQLServer, MSDTC, and SQLServerAgent processes

• They provide an extremely flexible method for importing and exporting data between a Microsoft SQL Server and other formats

• They are the main administrative console for SQL Server installations

• They provide a window into the inner workings of the database

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

Answers:

• NOT LIKE

• WHERE

• NOT IN

• HAVING

39. True or False? A table can have more than one primary key.

Answers:

• False

• True

40. What is the role of a processadmin user?

Answers:

• Manages processes running in an instance of SQL Server

• Creates and alters databases

• Manages server logins

• Executes the BULK INSERT statement

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

Answers:

• False

• True

42. What does the "IN" clause do?

Answers:

• Used to specify which table to iterate over in a loop using T-SQL

• Checks whether a table contains a reference to another table

• Compares a specified value against a list of values and is true if a match is found

• Searches a file for records belonging to a table

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

Answers:

• Quickly pulls information out of a database in response to a user request

• All of these

• Creates/modifies stored procedures and execute administrative tasks

• Tests queries before implementing them in other applications

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

Answers:

• LEFT OUTER JOIN

• FULL OUTER JOIN

• LEFT INNER JOIN

• INNER JOIN

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

Answers:

• SQL Server security

• Both Windows security and SQL Server security

• Windows security

46. Which is the slowest?

Answers:

• TABLE scan

• INDEX scan

47. What is CTE

Answers:

• Common Table Experiences

• Common Table Expresssion

• Common Table Excess

• Common Table Express

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

Answers:

• LIKE "%ith%"

• IN ('ith')

• LIKE "%ith"

• LIKE "_ith"

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

Answers:

• Change security settings

• Set user accounts

• Any action in SQL server

• Edit databases

50. What is a cursor in SQL Server?

Answers:

• A user interface component contained within the SQL Server Management Studio application

• A collection of icons that can be applied to the mouse pointer on the screen

• A special T-SQL construct used to iterate through the individual records returned by a query

• A remote connection to another SQL Server

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

Answers:

• FILTER BY

• WHERE

• HAVING

• CONTAINS

• HAS

52. What does "UDF" stand for?

Answers:

• USB Device File

• User Data Filter

• Universal Data File

• User Defined Function

• User Data Form

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

Answers:

• All of these

• ROLLBACK [WORK]

• COMMIT [WORK]

• BEGIN TRANSACTION

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

Answers:

• False

• True

55. Which statement adds a row to a table?

Answers:

• RESERT

• UPDATE

• INSERT INTO

• ADD

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

Answers:

• Both Nonclustered Indices and Clustered Indices

• Clustered Indices

• Nonclustered Indices

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

Answers:

• 1434

• 1443

• 1343

• 1143

• 1433

58. What is the correct syntax for a CTE?

Answers:

• select [column] from cte myCTE ([column]) as (select ...);

• with myCTE ([column]) as (select ...) select [column] from myCTE;

• with select [column] from myCTE ([column]) as (select ...);

• using cte myCTE ([column]) as (select ...) select [column] from myCTE;

59. A sysadmin password requires at least 8 characters and which of the following character types?

Answers:

• Uppercase letters

• Non-alphabetic

• Lowercase letters

• All of these

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

Answers:

• Source code > Executable code > Procedure as database object

• Source code > Object code > Procedure as database object

• Object code > Executable code > Procedure as database object

• Source code > Object code > Executable code > Procedure as database object

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

Answers:

• False

• True

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

Answers:

• 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

63. "SELECT UNIQUE city FROM customers" will return what data set?

Answers:

• Unique values of city from the customers table.

• An error.

• Unique values of customer from the table city.

64. Which of the following statements prevents users from preforming actions?

Answers:

• DENY and REVOKE

• REVOKE and REMOVE

• DENY

• REVOKE

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

Answers:

• Alien key

• Foreign key

• Join key

• Sibling key

• Associate key

66. Which of the following stores FileStream data?

Answers:

• None of these

• FileTable

• Filegroup

• Directory

67. The system database that holds information on SQL Server Agent Jobs is?

Answers:

• model

• msdb

• tempdb

• master

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

Answers:

• True

• False

69. "Unique Constraint" can be created on a column that accepts NULL

Answers:

• True

• False

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

Answers:

• Both methods would return an error

• SELECT * FROM #table1 WHERE xml_field1.value('(/PageContent/Text)[1]', 'varchar(100)') LIKE '%smith%'

• Neither method would return an error

• SELECT * FROM #table1 where xml_field1 like '%smith%'

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

Answers:

• MODEL

• MASTER

• MSDB

• TEMPDB

• Distribution Database

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

Answers:

• GETDATE()

• ISNULL()

• COUNT()

• CONVERT()

• LEN()

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

Answers:

• Use change data capture (CDC)

• Use triggers

• Use triggers or use change data capture (CDC)

• None of these

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

Answers:

• figurative value

• identifiers

• literal value

• delimiters

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

Answers:

• REMOVE

• REVOKE

• DENY and REVOKE

• REVOKE and REMOVE

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

Answers:

• Nondeterministic

• Server Level

• User Defined

• Deterministic

• Database Level

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

Answers:

• True

• False

78. Which versions of SQL Server support table partitioning?

Answers:

• Enterprise, Standard editions

• Web edition

• Enterprise, Standard, Express editions

• Enterprise edition

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

Answers:

• SELECT

• LEAD

• FIRST_VALUE

• PREVIOUS_ROW

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

Answers:

• AND

• WHERE

• OR

• IN

81. In SQL Server 2012 introduces what new feature?

Answers:

• Online Index Reorgs

• Mirroring

• Online Index Rebuilds

• Log Shipping

• Always On

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

Answers:

• False

• True

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

Answers:

• True

• False

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

Answers:

• Locate the database

• Check the average page density

• None of these

• Check the amount of pages

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

Answers:

• AND

• IN

• OR

• WHERE

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

Answers:

• Data of employees where name = null

• The query will return incorrect value

• Header without any data

• 1

• Syntax error

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

Answers:

• StringCollection

• CompoundString

• MultiString

• CompoundCurve

• CurveString

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

Answers:

• There is no specialized command for performing this operation in SQL Server, however effect can be duplicated using other means

• UNPIVOT

• PIVOT

89. What does the EOMONTH function do?

Answers:

• None of these

• Returns the last day of the month specified

• Adds the specified number of weeks to the input start date and then returns the last day of the month specified

• Adds the specified number of months to the input start date and then returns the last day of the month for the resulting date

90. SQL Server will create an Unique Non Clustered index, by default, when a Primary Key is created

Answers:

• False

• True

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

Answers:

• smallmoney

• datetimeoffset

• hierarchyid

• smalldatetime2

• datetime2

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

Answers:

• ddladmin

• diskadmin

• backupoperator

• accessadmin

• datawriter

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

Answers:

• UNIQUEIDENTIFIER

• INT

• NULL does not have data type by default

• BIT

• NVARCHAR

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

Answers:

• All of these

• Cross check a security certificate with the owner organization

• Specify the certificate used by the SQL server

• Create a security certificate for the SQL server

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

Answers:

• False

• True