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:

Msg 11741, Level 15, State 1, Line 4

NEXT VALUE FOR function cannot be used within CASE, CHOOSE, COALESCE, IIF, ISNULL and NULLIF.

 

 

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:

Msg 4712, Level 16, State 1, Line 2

Cannot truncate table 'dbo.AccountType' because it is being referenced by a FOREIGN KEY constraint.


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.

 

Given the following script which uses the CONCAT string function introduced in SQL Server 2012, what is the output of the SELECT statement?

SET CONCAT_NULL_YIELDS_NULL ON

 

DECLARE @FirstName1      NCHAR(50) = 'George'

DECLARE @FirstName2      VARCHAR(50) = NULL

DECLARE @LastName        NVARCHAR(50) = 'Washington'

DECLARE @MiddleInitial   CHAR(1) = ''

 

SELECT DATALENGTH(CONCAT(@FirstName1, @FirstName2, @LastName, @MiddleInitial))

A. 0

B. 16

C. 32

D. 60

E. 120

F. 122

 

(Answer) F

The CONCAT string function takes a variable number of string arguments and concatenates them into a single string.  All arguments are implicitly converted to string types and then concatenated.  NULL values, regardless of the setting of the CONCAT_NULL_YIELDS_NULL, are implicitly converted to an empty string.  The return data type of the CONCAT string function depends on the data types of the arguments.  If any of the arguments is NVARCHAR with a length of less than 4000, then the output data type is NVARCHAR.

The DATALENGTH data type function returns the number of bytes used to represent any expression.  This is different from the LEN string function which returns the number of characters of the specified string expression, excluding trailing blanks.

Here's the breakdown of the number of characters per argument passed to the CONCAT string function:

@FirstName1 = 50 (Since this is a NCHAR data type, it will use 50 characters)
@FirstName2 = 0 (NULLs are implicitly converted to empty string)
@LastName = 10
@MiddleInitial = 1 (Since this is a CHAR data type, it will use 1 character)

Since the output data type is NVARCHAR, it uses up 2 bytes per character, therefore 61 characters times 2 bytes is 122 bytes.

 

Which of the following fixed server roles can manage linked servers?

A. processadmin

B. setupadmin

C. securityadmin

D. serveradmin

E. diskadmin

F. None of the Above

 

(Answer) B

One of the tasks of the setupadmin fixed server role is to add or remove linked servers.  Aside from this task, the setupadmin can also execute system stored procedures, such as sp_serveroption.

 

 

What will be the output of the following statement?

SELECT CHARINDEX('She sells seashells at the seashore', 'se', -1)

A. 0

B. 5

C. 11

D. 28

E. -1

F. None of the Above

 

(Answer) A

The first parameter of the CHARINDEX function is an expression containing the sequence of characters to be found.  If the first parameter is not found within the second parameter, the CHARINDEX function return 0.