SQL Server: CREATE QUEUE Function

DBA/Developer authored the following queue statement

CREATE QUEUE MyQueue
WITH STATUS = ON,
ACTIVATION
(STATUS = ON,
PROCEDURE_NAME = ReceiveData,
MAX_QUEUE_READERS = 3,
EXECUTE AS SELF);

Before creating a service, DBA/Developer must execute the CREATE QUEUE statement to create a queue. A queue stores the incoming message for a service. Each service must be associated with a queue. Therefore, DBA/Developer must create a queue using the CREATE QUEUE statement before creating a service. The syntax of the CREATE QUEUE statement is as follows:

CREATE QUEUE queue_name
[WITH [STATUS = {ON | OFF}],
[RETENTION = {ON |
OFF}],
[ACTIVATION ([STATUS = {ON | OFF},]
PROCEDURE_NAME = procedure_name,
MAX_QUEUE_READERS = max_num_readers,
EXECUTE AS {SELF | 'user_name' | OWNER})]]
[ON {filegroup | [DEFAULT]}];

The clauses of the CREATE QUEUE statement are as follows:

WITH STATUS: Specifies the status of the queue that indicates whether the queue receives messages. With STATUS=ON, the queue receives the messages.

RETENTION: Specifies whether the sent and received messages will be retained in the queue until conversations have ended.

ACTIVATION: Specifies information about the stored procedure that is activated to process the message. The ACTIVATION clause can specify a STATUS option to indicate if the stored procedure immediately processes the message, and a PROCEDURE_NAME option with the name of the stored procedure that should process the message. The ACTIVATION clause can also specify the security context under which the stored procedure should execute using the

EXECUTE AS clause, and the maximum number of instances of the stored procedure that can be concurrently used when processing messages. In this scenario, DBA/Developer should create a queue and specify an ACTIVATION clause in the CREATE QUEUE statement. The ACTIVATION clause allows DBA/Developer to specify the stored procedure that will process incoming messages, and how it will behave. In the ACTIVATION clause, DBA/Developer should specify a PROCEDURE_NAME of ReceiveData to identify the procedure that will process incoming messages, and a STATUS of ON to indicate that the stored procedure should process the messages as soon as they are received. Finally, DBA/Developer should specify EXECUTE AS SELF to indicate that the stored procedure should execute under the security context of the current user. DBA/Developer should not use the statement that specifies WITH STATUS=OFF when creating the queue. This setting indicates that the queue cannot receive messages. DBA/Developer should not use the statement that specifies STATUS=OFF in the ACTIVATION clause of the CREATE QUEUE statement. With this setting, Service Broker will receive each message but will not process it immediately. DBA/Developer should not use the statement that specifies EXECUTE AS OWNER in the ACTIVATION clause of the CREATE QUEUE statement. This would cause the stored procedure to execute under the security context of the queue owner, not of the current user as required in this scenario.

SQL Server: DENSE RANK Function

The DENSE_RANK function ranks rows based on the PARTITION BY and ORDER BY specified in the OVER clause. The basic syntax of the DENSE_RANK function is as follows:

DENSE_RANK ( )

OVER ([partition_by] order_by)

First, if a partitioned by value is specified, the rows are partitioned. Then, within each partition, the rows are sorted and ranked based on the specified ORDER BY clause. If two rows within the same partition have the same ORDER BY value, they are assigned the same ranking, and the following ranked row is assigned the next sequential ranking value. For example, the following SELECT statement could be used in this scenario:

SELECT *,

DENSE_RANK () OVER
(PARTITION BY Region ORDER BY Quota DESC)
AS Ranking
FROM Salesperson;

This statement would partition the salespeople by region. Then, within each region, the rows would be sorted in descending order based on each salesperson's Quota value. Finally, the Ranking value would be calculated for each salesperson. Salespeople with identical Quota values within a region would have the same ranking, and no ranking values would be skipped. DBA/Developer should not include a RANK function in the SELECT list and a correlated subquery in the FROM clause because a subquery is not needed in this scenario. DBA/Developer can use the RANK function to rank a result set. A correlated subquery is a subquery that references one or more columns in the outer query. Correlated sub queries can adversely affect performance, and should be avoided when possible because the inner query will execute once for each row of the outer query. While correlated sub queries are required in some situations, in this scenario DBA/Developer could accomplish the desired result using the aggregate DENSE_RANK function with an OVER clause. While the RANK function is similar to the DENSE_RANK function, it skips a ranking value for each row that has an identical ORDER BY value. DBA/Developer should not include an OUTER APPLY or a CROSS APPLY clause. The APPLY clause is used in the FROM clause of a query to join a table to a table-valued function. The table-valued function is called for each row returned by the outer query. The APPLY clause allows DBA/Developer to easily call a table-valued function for each row returned by a query. The OUTER APPLY clause returns all rows from the outer query, even if the row does not return a value for the tablevalued function. The CROSS APPLY clause returns only the outer query rows for which the table-valued function returned a value.

SQL Server: User Defined Functions

In SQL Server 2008, user-defined functions may be one of three types: scalar-valued, inline table-valued, or multi-statement table-valued. Both inline table-valued and multi-statement table-valued functions can be used to return a result set as a table data type. Inline table-valued functions can execute only one SQL statement. Multi-statement table-valued functions can execute multiple SQL statements as well as include other procedural code. An inline table-valued function can be an alternative to creating a view, and provides the benefit of being able to specify parameters within the SELECT statement. Inline views are sometimes referred to as parameterized views. In this scenario, because the user-defined function will not include multiple SQL statements and only needs to return rows based on the parameters it was passed, DBA/Developer should use an inline table-valued function. To create an inline table-valued function, use the CREATE FUNCTION statement and specify a table data type as the return value. Unlike a multi-statement table-valued function, an inline table-valued function does not have to define the structure of the table being returned. The structure of the returned table is derived by the SELECT statement included within the function. For example, the following statement would create an inline table-valued function that accepts two parameters and returns a table of data based on a query of two tables:

CREATE FUNCTION dbo.GetData

(@typedesc varchar(10), @loc int)
RETURNS table AS RETURN
(SELECT i.ProductName, t.Description
FROM InventoryHistory
AS iJOIN PTLookup AS t
ON t.TypeID = i.TypeID
WHERE t.Description = @typedesc
AND i.LocID =@loc);

This would create an inline table-valued function named dbo.GetData that users could use to query the InventoryHistory table and the PTLookup lookup table. Users would be able to call the function from within a FROM clause, passing it the search parameters to return the data and perform additional analysis. DBA/Developer should not create a recursive scalar user-defined function. A scalar user-defined function returns a single scalar value, and in this scenario, DBA/Developer wants users to access a result set. DBA/Developer should not create a multi-statement table-valued function because this would require more development effort. With a multi-statement table-valued function, DBA/Developer must explicitly define the table being returned, but with an inline table-valued function, DBA/Developer do not.DBA/Developer should not create a partitioned view on the InventoryHistory table. A partitioned view is used when DBA/Developer have similar data stored in multiple tables and want to create a view to allow access to all of the data as if it were stored in a single table. Partitioned views are implemented using the UNION ALL operator. For example, if DBA/Developer had three separate tables with an identical structure, DBA/Developer might use the following statement to create a partitioned view that allows users to query data from all three tables:

CREATE VIEW PartView

AS SELECT * FROM Table1
UNION ALL
SELECT * FROM Table2
UNION ALL
SELECT * FROM Table3;

SQL Server: LOCK ESCALATION

By default, SQL Server will escalate locks to the table level when required. When an excessive number of row-level locks are encountered, SQL Server will escalate the lock to a higher level. This causes the entire table to be locked, which is usually sufficient and reduces memory consumption. However, with partitioned tables, DBA/Developer may not want to lock the entire table, but only a single partition, such as the partition containing rows being modified. This would allow queries against other partitions in the table to execute successfully without being blocked. DBA/Developer can use the LOCK_ESCALATION setting for a table to control how SQL Server will escalate locks. The LOCK_ESCALATION setting can have one of the following values:
TABLE: Lock escalation is performed to the table level, which is the default.
AUTO: Lock escalation is performed to the table level for non-partitioned tables, and to the partition level for partitioned tables.
DISABLE: Lock escalation is disabled, and lock escalation does not typically occur. SQL Server only escalates locks in specific situations where it is absolutely required to ensure data integrity.
For example, the following statement would set the LOCK_ESCALATION setting for the POHistory table to AUTO:

ALTER TABLE POHistory

SET (LOCK_ESCALATION=AUTO);

With this setting, SQL Server will escalate locks to the table level, rather than the partition level.

SQL Server: ON UPDATE CASCADE

When DBA/Developer includes the ON UPDATE CASCADE clause, it ensures that if a referenced row in the parent table is updated, then the corresponding child rows are updated and referential integrity is maintained. This would ensure that if a user updated the identifier of a WorkOrder, the corresponding rows in the WorkOrderDetail table would also be updated. DBA/Developer should not use a trigger to enforce referential integrity. Triggers should not be used when constraints can accomplish the same task. FOREIGN KEY constraints are used to enforce referential integrity.
DBA/Developer should not create a CHECK constraint on the WorkOrder table. A CHECK constraint is used to restrict the data allowed for a column to specific values. A CHECK constraint consists of a Boolean expression that evaluates to either TRUE or FALSE. If the expression evaluates to TRUE, the value is allowed for the column, and if the expression evaluates to FALSE, the value is not allowed for the column. CHECK constraints can be defined at the table level or column level, but only CHECK constraints defined at the table level can use columns other than the constrained column in the constraint expression. DBA/Developer should not include the WITH CHECK clause in DBA/Developer FOREIGN KEY constraint because this only enables the constraint. By default, FOREIGN KEY and CHECK constraints are enabled when they are created.
DBA/Developer can use the NOCHECK CONSTRAINT clause of the ALTER TABLE statement to temporarily disable a FOREIGN KEY or CHECK constraint if DBA/Developer needs to insert rows that violate the constraint.

SQL Server: Optimizer Hint

TABLE HINT forces a query to use a specific index. DBA/Developer can do so using an INDEX table hint specified using the TABLE HINT query hint.
OPTIMIZE FOR query hint is used to optimize queries based on a specific value for a local variable used in the query.
FAST query hint optimizes the query to quickly retrieve a specified number of rows. DBA/Developer would use the FAST query hint to quickly return a fewer number of rows from a large table. For example, DBA/Developer might use the following query to quickly retrieve the first 200 rows from the Products table:

SELECT *

FROM ProductHistory
ORDER BY ProdStyle
DESCOPTION (FAST 200);

When this query executes, the first 200 rows in the result set will be returned as quickly as possible, and then the query will return the remainder of the result set when the query completes.
FORCE ORDER query hint controls how the query's join order is handled when a query is being optimized. Specifying FORCE ORDER indicates that query optimization will not affect the join order specified in the query.

SQL Server: Data Types

The char and varchar data types both allow character data up to 8,000 characters. The char data type is fixed-length, and the varchar data type is variable-length. Creating the Code column as char(3) will allow the Code column to store a fixed-length three-character alphanumeric code. Creating the Description column as varchar(30) allows for some storage savings for rows with descriptions shorter than 30 characters. DBA/Developer should use a char data type when values in a column will be a consistent length and a varchar data type when the length of values will vary.For the SeatsAvail column, a tinyint data type would allow the required values but minimize storage. A tinyint data type can store an integer value between 0 and 255 with a single byte. The bit data type would be used to represent a Boolean value, such as whether the event is public or private. The Rating column is defined as decimal (4,2). This allows the column to store a decimal value with a maximum of four total digits, with two digits to the right of the decimal place. This will allow DBA/Developer to store Rating values between 0.00 and 99.99. DBA/Developer should not use the following CREATE TABLE statement:

CREATE TABLE Event (EventID int,
Code varchar(3),
Description varchar(30),
SeatsAvail
int,
TypeFlag int,
Rating decimal(4,2));

A varchar data type stores variable-length data, rather than fixed-length data as required for the Code column in this scenario. The SeatsAvail column does not minimize storage because it is defined as an int data type. An int requires four bytes of storage. In this scenario, a tinyint would be sufficient. The TypeFlag does not minimize storage because it uses an int data type. Because only two possible values need to be stored, a bit could represent this with less storage. DBA/Developer should not use the following CREATE TABLE statement:

CREATE TABLE Event (EventID int,Code char(3),

Description varchar(30),
SeatsAvail
smallint,
TypeFlag bit,
Rating decimal(3,2));

The SeatsAvail column does not minimize storage because it is defined as a smallint data type. A smallint data type requires two bytes of storage and can hold values between -32,768 and 32,767. Atinyint uses one byte and can store values between 0 and 255, which would have been sufficient in this scenario. The Rating column will not be able to store the required value because only three total digits are allowed. With this column definition, the Rating column could only hold values between 0.00 and 9.99. DBA/Developer should not use the following CREATE TABLE statement:

CREATE TABLE Event (EventID int,Code char(3),

Description varchar(30),
SeatsAvail
tinyint,TypeFlag bit,
Rating decimal(3,2));

With this statement, the Rating column will not be able to store the required value because only three total digits are allowed. With this column definition, the Rating column could only hold values between 0.00 and 9.99.