Dave Littleton

SQL SERVER: MIXED QUESTIONS

SQL Server: SQL Server Questions

http://www.sql-server-helper.com/free-test/sql-answers-summary.aspx

 

In SQL Server 2012, what is the data type of the output of the following SELECT statement? SELECT CHOOSE(4, SYSDATETIME(), SYSDATETIMEOFFSET(), SYSUTCDATETIME(), GETDATE(), GETUTCDATE()) AS [Output]
	A. DATE B. DATETIME C. DATETIMEOFFSET D. DATETIME2 E. SMALLDATETIME F. SQL_VARIANT

 

(Answer) C

The return data type of the SYSDATETIME() and SYSUTCDATETIME() date functions is DATETIME2, the return data type of the GETDATE() and GETUTCDATE() date functions is DATETIME and the return data type of the SYSDATETIMEOFFSET() date function is DATETIMEOFFSET.  The CHOOSE logical function introduced in SQL Server 2012 returns the data type with the highest precedence from the set of types passed to the function.  From the set of types provided, the data type that has the highest precedence is the DATETIMEOFFSET.

 

A parsed XML document is stored in the internal cache of SQL Server.  The MSXML parser (msxmlsql.dll) uses one-eighth the total memory available for SQL Server.  To avoid running out of memory, which of the following system stored procedures is used to free up the memory used by the parsed XML document?
	A. [dbo].[sp_xml_removedocument] B. [dbo].[sp_remove_xmldocument] C. [dbo].[xp_xml_removedocument] D. [dbo].[xp_remove_xmldocument] E. None of the Above

 

(Answer) A

The [dbo].[sp_xml_removedocument] system stored procedure removes the internal representation of the XML document specified by the document handle and invalidates the document handle and therefore frees up the memory.

The [dbo].[sp_remove_xmldocument], [dbo].[xp_xml_removedocument] and [dbo].[xp_remove_xmldocument] are not valid system stored procedure as they don't exist.

 

Introduced in SQL Server 2012, a SEQUENCE is a user-defined schema-bound object that generates a sequence of numeric values according to the specification with which the sequence was created. The sequence of numeric values is generated in an ascending or descending order at a defined interval and may cycle (repeat) as requested. Given the following sequence definition: CREATE SEQUENCE [dbo].[SQLSequence] AS TINYINT MAXVALUE 4 CYCLE What is the output of the following statement? SELECT CASE NEXT VALUE FOR [dbo].[SQLSequence]             WHEN 0 THEN 100             WHEN 1 THEN 200             WHEN 2 THEN 300             ELSE NULL        END AS [Score]

A. 100 B. 200 C. 300 D. NULL E. Depends on the output of the NEXT VALUE FOR function. F. None of the above.

 

(Answer) F

The following error message will be raised by the SELECT statement:

 

 

Given the following table definitions and sample data: CREATE TABLE [dbo].[AccountType] (     [AccountTypeID]     INT NOT NULL PRIMARY KEY IDENTITY(1, 1),     [AccountTypeCode]   CHAR(3),     [AccountTypeName]   VARCHAR(100) ) GO   CREATE TABLE [dbo].[Account] (     [AccountID]         INT NOT NULL PRIMARY KEY IDENTITY(1, 1),     [AccountName]       VARCHAR(100),     [AccountTypeID]     INT REFERENCES [dbo].[AccountType] ( [AccountTypeID] ) ) GO   INSERT INTO [dbo].[AccountType] ( [AccountTypeCode], [AccountTypeName] ) VALUES ( 'CHK', 'Checking Account' ),        ( 'SAV', 'Savings Account' ) GO   SELECT * FROM [dbo].[AccountType] AccountTypeID  AccountTypeCode  AccountTypeName -------------- ---------------- ---------------- 1              CHK              Checking Account 2              SAV              Savings Account Which of the following scripts can be used to refresh the contents of the [dbo].[AccountType] table and have the INSERT INTO statement re-executed and still get the same result from the [dbo].[AccountType] table, that is, the values assigned to the [AccountTypeID] is the same as the original values.

A. DELETE FROM [dbo].[AccountType] GO   DBCC CHECKIDENT ( '[dbo].[AccountType]', reseed, 0 ) GO   INSERT INTO [dbo].[AccountType] ( [AccountTypeCode], [AccountTypeName] ) VALUES ( 'CHK', 'Checking Account' ),        ( 'SAV', 'Savings Account' ) GO   SELECT * FROM [dbo].[AccountType] B. DELETE FROM [dbo].[AccountType] GO   DBCC CHECKIDENT ( '[dbo].[AccountType]', reseed, 1 ) GO   INSERT INTO [dbo].[AccountType] ( [AccountTypeCode], [AccountTypeName] ) VALUES ( 'CHK', 'Checking Account' ),        ( 'SAV', 'Savings Account' ) GO   SELECT * FROM [dbo].[AccountType] C. TRUNCATE TABLE [dbo].[AccountType]   INSERT INTO [dbo].[AccountType] ( [AccountTypeCode], [AccountTypeName] ) VALUES ( 'CHK', 'Checking Account' ),        ( 'SAV', 'Savings Account' ) D. Options A and C E. Options B and C F. None of the above.

 

(Answer) A

The DBCC CHECKIDENT statement checks the current identity value for the specified table and, if it is needed, changes the identity value.  The RESEED parameter to the DBCC CHECKIDENT specifies that the current identity value should be changed to the specified new seed value.  This new seed value overrides the initial value set for the identity column during its creation.

As for the first value to be used in the identity column, if no rows have been inserted to the table since it was created, the first row inserted after the running of DBCC CHECKIDENT uses the new reseed value as the identity.  Otherwise, the next row inserted uses the specified new reseed value.

Given these, since the table already contained data, the reseed value to be passed to the DBCC CHECKIDENT statement should be 0 so that the first row will be assigned a value of 1.  Thus, option A is a valid answer but not option B.

Option C is not a valid answer because the TRUNCATE TABLE statement will generate the following error message:

Given this, option A is the only valid answer.

 

Which of the following statements causes Microsoft SQL Server to execute Transact-SQL statements and generate detailed information about how the statements were executed?
	A. SET PARSEONLY ON B. SET FORCEPLAN ON C. SET SHOWPLAN_XML ON D. SET STATISTICS XML ON E. SET NOEXEC ON F. None of the Above

 

(Answer) D

SET STATISTICS XML ON causes Microsoft SQL Server to execute Transact-SQL statements and generate detailed information about how the statements were executed in the form of a well-defined XML document.

SET PARSEONLY ON simply examines the syntax of each Transact-SQL statement and returns any error messages without compiling or executing the statement.

SET FORCEPLAN ON causes Microsoft SQL Server query optimizer to process a join in the same order as the tables appear in the FROM clause of a query.

SET SHOWPLAN_XML ON causes SQL Server not to execute Transact-SQL statements.  Instead, SQL Server returns detailed information about how the statements are going to be executed in the form of a well-defined XML document.

SET NOEXEC ON simply compiles each query but does not execute it.

 

When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence.  Which of the following date/time data types has the lowest precedence?
	A. SMALLDATETIME B. DATETIME C. DATETIME2 D. DATE E. DATETIMEOFFSET F. All of these date/time data types have the same precedence.

 

(Answer) D

Of all these date/time data types, the data type that has the lowest data type precedence is DATE.  The order of the data types from lowest to highest data type precedence are as follows:  DATE, SMALLDATETIME, DATETIME, DATETIME2, DATETIMEOFFSET.

 

 

What is the minimum value can a TINYINT data type hold?
	A. -256 B. -512 C. -1,024 D. -2,048 E. 0 F. None of the Above

 

(Answer) E

A TINYINT data type uses only 1 byte of data and only stores values from 0 to 255.

 

In SQL Server 2012, which of the following is NOT an attribute of an accepted CompoundCurve instance?

A. All the instances contained by the CompoundCurve instance must be accepted circular arc segment instances. B. All of the circular arc segments in the CompoundCurve instance must be connected.  C. The first point for each succeeding circular arc segment within a CompoundCurve instance must be the same as the last point on the preceeding circular arc segment. D. None of the contained instances within the CompoundCurve instance are empty instances. E. None of the Above.

 

(Answer) E

None of the above as all these are attributes of an accepted CompoundCurve instance.

 

Which transaction isolation level allows nonrepeatable reads but prevents dirty reads?
	A. READ UNCOMMITTED B. READ COMMITTED C. REPEATABLE READ D. SNAPSHOT E. SERIALIZABLE F. None of the Above

 

(Answer) B

Of the different transaction isolation levels, the READ COMMITTED transaction isolation level allows nonrepeatable reads but prevents dirty reads.  The READ UNCOMMITTED transaction isolation level also allows nonrepeatable reads but it doesn't prevent dirty reads.  The REPEATABLE READ, SNAPSHOT and SERIALIZABLE transaction isolation levels all prevent both dirty reads and nonrepeatable reads.

 

Which of the following error severity levels can be caught in a TRY... CATCH error handling in SQL Server?
A. 0 B. 1 C. 8 D. 10 E. 14 F. None of the Above

 

(Answer) E

A TRY... CATCH construct catches all execution errors with severity greater than 10 that do not terminate the database connection. Errors with severity from 0 through 10 are informational messages and do not cause execution to jump to the CATCH block of a TRY... CATCH construct. Error that terminate the database connection, usually with severity from 20 through 25, are not handled by the CATCH block because execution is aborted when the connection terminates. Error severity level 14 indicates security-related errors, such as permission denied. Errors of this severity level jumps to the CATCH block of a TRY... CATCH construct.