Dave Littleton

SQL SERVER: MORE Q & A

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

Which transaction isolation level minimizes contention but allows dirty reads and nonrepeatable reads?

A. READ UNCOMMITTED

B. READ COMMITTED

C. REPEATABLE READ

D. SNAPSHOT

E. SERIALIZABLE

F. None of the Above

 

(Answer) A

Of the different isolation levels, only READ UNCOMMITTED isolation level allows both dirty reads and nonrepeatable reads.  READ COMMITTED isolation level allows nonrepeatable reads but does not allow dirty reads.  REPEATABLE READ, SNAPSHOT and SERIALIZABLE isolation levels do not allow both dirty reads and nonrepeatable reads.

 

Which of the following Transact-SQL statements is the proper way of declaring a stored procedure that accepts a table-valued parameter called @Orders?
A. CREATE PROCEDURE [dbo].[CreateOrders]   ( @Orders XML ) B. CREATE PROCEDURE [dbo].[CreateOrders]   ( @Orders Order ) C. CREATE PROCEDURE [dbo].[CreateOrders]   ( @Orders Order READONLY ) D. CREATE PROCEDURE [dbo].[CreateOrders]   ( @Orders Order OUTPUT ) E. CREATE PROCEDURE [dbo].[CreateOrders]   ( @Orders Order VARYING OUTPUT ) F. None of the Above

 

(Anser) C

Table-valued parameters are a new parameter type in SQL Server 2008. Table-valued parameters are declared by using user-defined table types. Table-valued parameters can be used to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters.

One restriction when using table-valued parameters in a stored procedure is that it must be passed as input READONLY parameter.

 

Which of the following Transact-SQL statements will generate the following result set? MLBTeam Championships  Hierarchy ---------------------- -------------- ---------- New York Yankees 27 1 St. Louis Cardinals  10 2 Boston Red Sox 7 3 New York Giants  5 4 Philadelphia Phillies  5 4 Pittsburgh Pirates 5 4 Los Angeles Dodgers  5 4 Cincinnati Red 5 4 Detroit Tigers 4 9 Oakland Athletics  4 9 Baltimore Orioles  3 11 Chicago White Sox  3 11

A. SELECT [MLBTeam], [Championships], ROW_NUMBER() OVER (ORDER BY [Championships] DESC)   AS [Hierarchy] FROM [dbo].[WorldSeriesChampions] B. SELECT [MLBTeam], [Championships], RANK() OVER (ORDER BY [Championships] DESC)   AS [Hierarchy] FROM [dbo].[WorldSeriesChampions] C. SELECT [MLBTeam], [Championships], DENSE_RANK() OVER (ORDER BY [Championships] DESC)   AS [Hierarchy] FROM [dbo].[WorldSeriesChampions] D. SELECT [MLBTeam], [Championships], NTILE(12) OVER (ORDER BY [Championships] DESC)   AS [Hierarchy] FROM [dbo].[WorldSeriesChampions] E. None of the Above

 

(Answer) B

The following Transact-SQL statement will generate the result set shown above:

The RANK ranking function returns the rank of each row within the partition of a result set.  The rank of a row is one plus the number of ranks that come before the row in question.  If two or more rows tie for a rank, each tied rows receives the same rank.

The ROW_NUMBER ranking function returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

The DENSE_RANK ranking function returns the rank of rows within the partition of a result set, without any gaps in the ranking.  The rank of a row is one plus the number of distinct ranks that come before the row in question.  If two or more rows tie for a rank in the same partition, each tied rows receives the same rank.

Lastly, the NTILE ranking function distributes the rows in an ordered partition into a specified number of groups.  The groups are numbered, starting at one.  For each row, NTILE returns the number of the group to which the row belongs.

 

Which of the following ranking functions distribute the rows in an ordered partition into a specified number of groups?
A. ROW_NUMBER B. RANK C. DENSE_RANK D. NTILE E. None of the Above

 

(Answer) D

The NTILE ranking function distributes the rows in an ordered partition into a specified number of groups.  The groups are numbered, starting at one.  For each row, NTILE returns the number of the group to which the row belongs.

The ROW_NUMBER ranking function returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

The RANK ranking function returns the rank of each row within the partition of a result set.  The rank of a row is one plus the number of ranks that come before the row in question.  Just like the DENSE_RANK, in the RANK ranking function, if two or more rows tie for a rank, each tied rows receive the same rank.  Unlike the DENSE_RANK, the RANK ranking function does not always return consecutive integers.

The DENSE_RANK ranking function returns the rank of rows within the partition of a result set, without any gaps in the ranking.  The rank of a row is one plus the number of distinct ranks that come before the row in question.  If two or more rows tie for a rank in the same partition, each tied rows receive the same rank.

 

Which of the following column definition will allow you to create a UNIQUE CONSTRAINT on the column?

A. NVARCHAR(1000) NOT NULL

B. NVARCHAR(100) NULL

C. NVARCHAR(MAX) NOT NULL

D. NVARCHAR(100) SPARSE NULL

E. None of the Above

 

(Answer) B

Of all the options provided, only NVARCHAR(100) NULL will allow you to create a UNIQUE CONSTRAINT on the column.

NVARCHAR(1000) NOT NULL and NVARCHAR(MAX) NOT NULL will not allow you to create a UNIQUE CONSTRAINT on the column because the maximum size of a column to be allowed a UNIQUE CONSTRAINT is 900 bytes.  NVARCHAR(100) SPARSE NULL will not allow you to create a UNIQUE CONSTRAINT on the column because a SPARSE column cannot be part of a clustered index or a unique primary key index.

 

 

Which of the following Transact-SQL statements will generate the following result set?

A. SELECT [MLBTeam], [Championships], ROW_NUMBER() OVER (ORDER BY [Championships] DESC)   AS [Hierarchy] FROM [dbo].[WorldSeriesChampions]

B. SELECT [MLBTeam], [Championships], RANK() OVER (ORDER BY [Championships] DESC)   AS [Hierarchy] FROM [dbo].[WorldSeriesChampions]

C. SELECT [MLBTeam], [Championships], DENSE_RANK() OVER (ORDER BY [Championships] DESC)   AS [Hierarchy] FROM [dbo].[WorldSeriesChampions]

D. SELECT [MLBTeam], [Championships], NTILE(12) OVER (ORDER BY [Championships] DESC)   AS [Hierarchy] FROM [dbo].[WorldSeriesChampions]

E. None of the Above

 

(Answer) C

The following Transact-SQL statement will generate the result set shown above:

The DENSE_RANK ranking function returns the rank of rows within the partition of a result set, without any gaps in the ranking.  The rank of a row is one plus the number of distinct ranks that come before the row in question.  If two or more rows tie for a rank in the same partition, each tied rows receives the same rank.

The RANK ranking function returns the rank of each row within the partition of a result set.  The rank of a row is one plus the number of ranks that come before the row in question.  If two or more rows tie for a rank, each tied rows receives the same rank.

The ROW_NUMBER ranking function returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

Lastly, the NTILE ranking function distributes the rows in an ordered partition into a specified number of groups.  The groups are numbered, starting at one.  For each row, NTILE returns the number of the group to which the row belongs.

 

The ProductCategory table has the following table definition: CREATE TABLE [dbo].[ProductCategory] (   [ProductCategoryID] INT NOT NULL PRIMARY KEY IDENTITY(1, 1),   [ProductCategoryName] NVARCHAR(100) ) The first few rows in the table are as follows: ProductCategoryID ProductCategoryName ------------------- --------------------- 1 Soap 2 Shampoo 4 Toothpaste 5 Toothbrush To fill out the unused identity value, the product category "Conditioner" will be assigned a value of 3 for the product category ID.  Which of the following Transact-SQL statement will allow you to INSERT this record in this table?

A. SET IDENTITY_INSERT [dbo].[ProductCategory] ON INSERT INTO [dbo].[ProductCategory] VALUES ( 3, 'Conditioner') B. SET IDENTITY_INSERT [dbo].[ProductCategory] ON   INSERT INTO [dbo].[ProductCategory] ( [ProductCategoryID], [ProductCategoryName] ) VALUES ( 3, 'Conditioner' ) C. SET IDENTITY_INSERT [dbo].[ProductCategory] ON   INSERT INTO [dbo].[ProductCategory] ( [ProductCategoryName] ) VALUES ( 'Conditioner' ) D. INSERT INTO [dbo].[ProductCategory] ( [ProductCategoryID], [ProductCategoryName] ) VALUES ( 3, 'Conditioner' ) E. None of the Above

 

(Answer) B

To insert a row to a table containing an IDENTITY column and specifying the value for the IDENTITY column, the SET IDENTITY_INSERT for that table must be set to ON.  Since the IDENTITY_INSERT property for that table has been set to ON, the value for the IDENTITY column needs to be specified.  Lastly, the columns have to be specified in the INSERT INTO clause followed by the values for each column.

Given this, the correct answer is as follows:

 

User Jose has been granted SELECT permission to the Inventory schema.  Based on corporate rules, his SELECT permission to the Inventory.Incoming table needs to be removed without affecting his permissions on the other tables within the Inventory schema.  Which of the following Transact-SQL statements will perform this task?

A. EXECUTE [dbo].[sp_revokedbaccess] 'Jose', 'Inventory.Incoming', 'SELECT'

B. DENY SELECT ON Inventory.Incoming FROM Jose

C. DENY SELECT ON Inventory.Incoming TO Jose

D. REVOKE SELECT ON Inventory.Incoming FROM Jose

E. None of the Above

 

(Answer) C

The DENY statement denies the permission, in this case the SELECT permission, to a principal, in this case, user Jose.  The syntax for denying a permission from a user is as follows

Given this DENY statement syntax, the correct answer is as follows:

The REVOKE statement removes a previously granted or denied permission.  Since the SELECT permission has not been granted directly to user Jose on the Inventory.Incoming table, this option will not work.

The sp_revokedbaccess system stored procedure removes a database user from the current database.

 

Which of the following is NOT an operator that can combine or compare the results from multiple SELECT statements?

A. UNION

B. EXCEPT

C. INTERSECT

D. MERGE

E. None of the Above

 

(Answer) D

The UNION operator combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union.  The UNION operation is different from using joins that combine columns from two tables.  The EXCEPT and INTERSECT operator returns distinct values by comparing the results of two queries.  The EXCEPT operator returns any distince values from the left query that are not also found on the right query.  The INTERSECT operator returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand.