SQL Server: READPAST Vs NOWAIT Table Hint

When DBA/Developer includes the READPAST table hint in a query, the query executes immediately, but does not return any locked rows. In this scenario, DBA/Developer could use the following statement to execute the query immediately without any errors:

SELECT ShipMethodID,

SUM (Freight)
FROM PurchaseOrderHeader
WITH (READPAST)
GROUP BY ShipMethodID;

DBA/Developer should note that if DBA/Developer includes the READPAST table hint, the Freight value of the row that is locked would not be included in the sum returned in DBA/Developer query's result set. In this scenario, if DBA/Developer wanted to execute the query and includes the Freight value for the locked row, DBA/Developer could use the NOLOCK table hint. The NOLOCK table hint ignores all locking, but retrieves uncommitted data for locked rows. The NOLOCK table hint prevents blocking but allows dirty reads, and the READPAST table hint prevents blocking but does allow dirty

The NOWAIT table hint will cause the query to not wait for locks to be released, and will immediately return an error. Usually, queries use the LOCK_TIMEOUT setting value to determine how long the statement should wait for locks to be released before returning an error. Using the NOWAIT table hint would be the equivalent of setting LOCK_TIMEOUT to 0 before executing the query. DBA/Developer should not add the TABLOCK or UPDLOCK hint to DBA/Developer's query because both of these hints would cause the query to wait to obtain locks. The TABLOCK hint specifies that the query should acquire a table-level lock and keep the table locked until the statement completes. The UPDLOCK hint specifies that the query should acquire an update lock that remains in effect until the transaction ends.

SQL Server: sp_estimate_data_compression_savings System Stored Procedure

sp_estimate_data_compression_savings system stored procedure is used to estimate the space that would be saved by implementing row or page compression for a table. DBA/Developer can also use the procedure to estimate the result of disabling compression or implementing compression for an index or a single partition of a partitioned table. The procedure accepts the following input parameters:

 @schema_name: Indicates the schema that contains the table.

This parameter defaults to the current user's schema if a schema is not specified.

@object_name: Indicates the table name.
@index_id: Indicates the ID number of an index.
@partition_number: Indicates the partition number.
@data_compression: Indicates the type of compression. Valid values are NONE, ROW, and PAGE.

The procedure returns a column indicating the current size, size_with_current_compression_setting, and a column indicating the estimated size if the specified type of compression were implemented, size_with_requested_compression_setting. In this scenario, DBA/Developer would be able to determine the estimated savings if DBA/Developer implemented page-level compression for a table. DBA/Developer should note that when evaluating whether to implement compression, DBA/Developer should also consider how the data is accessed. Compression generates overhead, especially for tables that are frequently accessed. Therefore, both the storage savings and the performance impact should be considered.

The sp_spaceused system stored procedure returns current space usage information, not estimated storage savings by implementing compression.

SQL Server: Creating a Service Broker Application

By default, messages are sent and received in order. Service Broker allows DBA/Developer to override the order in which messages are sent and received by defining conversation priorities. DBA/Developer can use the CREATE BROKER PRIORITY statement to assign different numeric priority levels to different conversations. The syntax of the CREATE BROKER PRIORITY statement is as follows:

CREATE BROKER PRIORITY
priorityname
FOR CONVERSATION
[SET
([CONTRACT_NAME = {contractname | ANY}]
[[,] LOCAL_SERVICE_NAME =
{localservicename | ANY}]
[[,] REMOTE_SERVICE_NAME = {'remoteservicename' | ANY}]
[[,] PRIORITY_LEVEL ={priorityvalue | DEFAULT}])];

The priorityname specifies the conversation priority's name. The priorityvalue specifies an integer between 1 and 10 representing the priority value of the conversation. Conversations with a higher priority value have higher priority. The default priorityvalue is 5. The SET clause specifies the information that Service Broker uses to decide if a conversation should be prioritized. The SET clause may contain one or more of the following criteria:

CONTRACT_NAME: The name of the contract used in the conversation that was specified in the ON CONTRACT clause of the BEGIN DIALOG statement. This criterion allows DBA/Developer to prioritize conversations that use different contracts. LOCAL_SERVICE_NAME: The local service name criterion used to prioritize conversations. REMOTE_SERVICE_NAME: The remote service name criterion used to prioritize conversations.

PRIORITY_LEVEL: The integer value representing the conversation priority. Each criterion in the SET clause defaults to ANY if not specified. When an application sends or receives a message, Service Broker examines the criteria to determine the conversation priority. If Service Broker determines that the conversation meets more than one criterion, it will assign the conversation priority based on the best match. In this scenario, the Service2 and Service3 services share the same queue. However, DBA/Developer want to prioritize the conversations so that when reading messages from ReceiveQueue, messages delivered to Service3 are read first. To accomplish this, DBA/Developer can create two different conversation priorities. For example, DBA/Developer could use the following Transact-SQL to assign a higher priority to Service3's conversations:

CREATE BROKER PRIORITY
LowPriority
FOR CONVERSATION
SET
(CONTRACT_NAME = Contract1,
LOCAL_SERVICE_NAME = Service2,
REMOTE_SERVICE_NAME = N'Service1',
PRIORITY_LEVEL = 1);
CREATE BROKER PRIORITY HighPriority
FOR CONVERSATION
SET
(CONTRACT_NAME = Contract1,
LOCAL_SERVICE_NAME = Service3,
REMOTE_SERVICE_NAME = N'Service1',
PRIORITY_LEVEL = 10);

An application could then send a message from Service1 to Service2 using Contract1 and another message from Service1 to Service3. Using the conversation priorities defined by these statements, Service Broker will receive the higher-priority message sent to Service3 first. Without conversation priorities, the messages would be retrieved from ReceiveQueue in the order they were originally sent. DBA/Developer should note that if the conversation priority is bidirectional, separate priorities for each direction must be established. To determine the message with the highest priority without actually receiving it, DBA/Developer can use the GET CONVERSATION GROUP statement. DBA/Developer should also note that although conversations for received messages can implement priorities by default, the HONOR_BROKER_PRIORITY database option must be set to ON to implement conversation priorities for sent messages. DBA/Developer should not create an additional message type and assign it a higher priority because Service Broker does not prioritize messages based on the message type. DBA/Developer should not create separate physical queues for the Service2 and Service3 services. In this scenario, it is not necessary to create separate queues for the services. Both services can use the same physical message queue with the conversations for each prioritized differently. DBA/Developer should not use MSMQ instead of Service Broker because the desired functionality is not supported. Service Broker does support the required functionality with conversation priorities.

SQL Server: LOCK_ESCALATION setting

DBA/Developer authored the following Transact-SQL statement:

CREATE PARTITION FUNCTION PFunction1 (int)

AS RANGE LEFT FOR VALUES (2000, 3000);
CREATE PARTITION SCHEME PScheme1
AS PARTITION PFunction1 TO (fg1, fg2, fg3);
CREATE TABLE
TransDetails (TransID int, Description varchar(50), Region varchar(20),
Date datetime, TypeID int, EmpID int,Status bit)
ON PScheme1(TypeID);

DBA/Developer opens a session and executes the following Transact-SQL:

ALTER TABLE TransDetails
SET (LOCK_ESCALATION = TABLE);
GO BEGIN
TRANSACTION UPDATE TransDetails
SET TypeID = TypeID + 1000
WHERE TypeID > 4000;
GO

ALTER TABLE statement set LOCK_ESCALATION for the TransDetails table to TABLE, which is actually the default. Normally, table-level lock escalation is fine. However, with partitioned tables, DBA/Developer may not want to lock the entire table, but only a single partition, such as the partition containing the rows being updated in this scenario. This would allow queries against other partitions in the table to execute successfully without being blocked. By default, SQL Server will escalate locks to the table level as needed. However, to override this default behavior, or to disable lock escalation altogether, DBA/Developer can set the LOCK_ESCALATION setting for a table. 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.

In this scenario, DBA/Developer could instruct SQL Server to escalate locks to the partition level only using the following statement:

ALTER TABLE TransDetails SET (LOCK_ESCALATION=AUTO);

Doing so would prevent lock escalation to the table level, and allow other queries to execute successfully on other unaffected partitions of the table. To identify the LOCK_ESCALATION setting for a table, DBA/Developer can query sys.tables. For example, the following statement would display the current LOCK_ESCALATION setting for the TransDetails table:

SELECT lock_escalation_desc
FROM sys.tables
WHERE name = 'TransDetails';

SQL Server: STOPATMARK and STOPATBEFOREMARK clauses

The STOPATMARK and STOPATBEFOREMARK clauses of the RESTORE statement are used to restore the database to the saved point in the transaction or to a point before the transaction began, respectively. DBA/Developer could use the following statement to restore the log:

RESTORE LOG Research
FROM ResearchBackUp
WITH RECOVERY,
STOPATMARK='ToleranceUpdate';

When restoring a database, DBA/Developer must restore the latest full backup. If applicable, DBA/Developer must restore the latest differential backup since the last full backup. DBA/Developer should then restore each transaction log backup that was made since the last full backup or, if DBA/Developer made differential backups, the transaction log backup that was made since the last differential backup. DBA/Developer should also make a copy of the current transaction log. This backup will be called the tail-log backup because it contains the transactions that have not been recorded. When DBA/Developer restore the latest full database backup and the latest differential backup, specify WITH NORECOVERY in the RESTORE statement. The NORECOVERY option is required in this scenario because DBA/Developer will be applying transaction log backups after the database is restored. Then, DBA/Developer consecutively restore each of the transaction log backups that were made after the last full database backup or differential backup. Each transaction log backup must be restored by specifying the NORECOVERY option so that all subsequent transaction log backups can be restored. DBA/Developer should restore the transaction log backup that was made on the active transaction log. This is the latest log backup. When restoring the transaction log, DBA/Developer should use the STOPATMARK option along with the RECOVERY option in the RESTORE LOG statement. The STOPAT option specifies the desired point in time to which DBA/Developer want to restore the database, and the WITH RECOVERY option brings the database to a consistent state. DBA/Developer can use marked transactions across databases to recover multiple databases to the same specific point, but doing so causes any transactions committed after the mark used as the recovery point to be lost. DBA/Developer should not restore the log backup using WITH STOPBEFOREMARK = 'ToleranceUpdate'. The WITH STOPBEFOREMARK clause in the RESTORE LOG statement uses the log record before the saved point as the mark for recovery. In this scenario, DBA/Developer wanted to restore the Widget table to the ToleranceUpdate transaction, not to a point before it started. DBA/Developer should not use a ROLLBACK statement to roll back to the ToleranceUpdate transaction. DBA/Developer can use a ROLLBACK statement to roll back to a specific point in a transaction, but to do so, DBA/Developer must first use the SAVE TRANSACTION statement to create a named savepoint. In this scenario, DBA/Developer did not create a savepoint. DBA/Developer created a marked transaction. DBA/Developer should not restore the transaction log and use a ROLLBACK statement to roll back to the ToleranceUpdate transaction. DBA/Developer must create a savepoint to roll back to a specific point in a transaction.

SQL Server: GROUPING SETS Clause

The GROUPING SETS clause allows DBA/Developer to explicitly specify the groups for which aggregate information should be displayed. This allows DBA/Developer to use more than one grouping within a single query. The syntax of the GROUP BY clause with a GROUPING SETS clause is as follows:

GROUP BY GROUPING SETS

(groupingset1 [,...groupingsetn])

Each grouping set can contain one or more columns or an empty set. Aggregate rows are returned in the result set for only the specified groups. Specifying an empty set, with (), indicates that a grand total row should also be returned in the result set. In this statement, DBA/Developer specified an empty set in the GROUPING SETS clause. However, DBA/Developer used the GROUPING_ID function in the HAVING clause. The GROUPING_ID function returns either 0 or a 1 to identify the level of grouping. This HAVING clause suppresses the grand total rows. DBA/Developer can also use the CUBE and ROLLUP operators to aggregate data. The ROLLUP operator groups the selected rows in the result set based on the values in the GROUP BY clause and returns one row as a summary row for each group. The ROLLUP operator can be used to generate totals and subtotals. Using the ROLLUP operator, a row containing the subtotal and the total is also returned in the result set. When DBA/Developer specifies WITH CUBE, a summary row is included in the result set for each possible combination of the columns specified in the GROUP BY clause. When using ROLLUP, CUBE, or GROUPING SETS, aggregate rows can be identified by the NULL values. Summary rows for grand totals will contain NULL values for all grouping columns. If DBA/Developer grouped using two columns, such as ProductID and SpecialOfferID in this scenario, DBA/Developer could identify the aggregate rows as follows:

Summary rows that represent totals for each ProductID would have a value for ProductID and a NULL value for SpecialOfferID.

Summary rows that represent totals for each SpecialOfferID would have a value for SpecialOfferID and a NULL value for ProductID.

DBA/Developer should not remove the empty grouping set from the GROUPING SETS clause. To include grand totals in a query that uses the GROUPING SETS clause, DBA/Developer must include an empty set. DBA/Developer should not modify the HAVING clause to check for a value of 1 returned by the GROUPING_ID function. This would return only the grand total row, and filter out the aggregate rows for products and the aggregate rows for unique ProductID and SpecialOfferID combinations.DBA/Developer should not remove the GROUP BY and HAVING clauses and use GROUP BY SpecialOfferID WITH ROLLUP in the GROUP BY clause. All columns in the SELECT list must either use an aggregate function or be included in the query's GROUP BY clause.

SQL Server: For XML Clause

The FOR XML clause specifies that the result of the SELECT statement should be returned in XML format. DBA/Developer can specify one of the following modes with the FOR XML clause:

RAW: A single <row> element will be generated for each row in the rowset. Each non-null column value generates an attribute with the name identical to the column's name or the column's alias.

AUTO: Nested elements are returned using the columns specified in the SELECT list. Each non-null column value generates an attribute named according to the column name or column alias. The element nesting is based on the order in which the columns are specified in

the SELECT list.

EXPLICIT: Each row in the rowset can be defined in detail, including attributes and

elements.

PATH: Each row in the rowset can be defined in detail, but column names and column

aliases are specified as XPath expressions.

DBA/Developer can also include the ELEMENTS option with the FOR XML clause. This will return columns in the SELECT list as subelements, rather than as attributes. Each table specified in the FROM clause will be represented by a separate element, and each column from that table will appear as a subelement of that element. Tables specified first will constitute higher-level elements of the hierarchy, and if specified, column aliases will be used as element names. For example, in this scenario, DBA/Developer might use the following query to extract data from the Product, ProductVendor, and Vendor tables in XML format:

SELECT
p.Name AS Product,
v.Name AS Vendor,
p.ProductID

FROM Production.Product p
JOIN Purchasing.ProductVendor pv
ON p.ProductID = pv.ProductID
JOIN Purchasing. Vendor v
ON pv.VendorID = v.VendorID
FOR XML RAW,
ELEMENTS;

The query would display the XML in the following format:

 <row> <Product>Product A

</Product><Vendor>VirtuArt, Inc.
</Vendor><ProductID>1

</ProductID></row><row>
<Product>Product B

</Product><Vendor>NuTex Corporation

</Vendor><ProductID>879

</ProductID></row><row>
<Product>Product C
</Product><Vendor>VirtuArt, Inc.
</Vendor><ProductID>712
</ProductID></row><row>
<Product>Product </Product>
<Vendor>InterConn
</Vendor><ProductID>2
</ProductID></row>

DBA/Developer should not implement a query that uses the OPENXML function in the FROM clause, or create a Transact- SQL script that uses the sp_xml_preparedocument system stored procedure and then inserts the XML into an xml data type column. OPENXML is a rowset provider function that creates a relational view of data contained in an XML document. This function can be used in SELECT statements where a table or view would be specified to extract data from an XML document. One of the function's required parameters is an integer used as a handle to the internal representation of the XML document. Therefore, before DBA/Developer can extract data from an XML document, DBA/Developer must call the sp_xml_preparedocument stored procedure to create and return the document handle. After the data has been extracted from the XML document by a SELECT query with the OPENXML function, DBA/Developer would call the sp_xml_removedocument stored procedure to remove the internal representation of the document and free resources. DBA/Developer should not create a stored procedure that queries the tables and returns an xml data type. In this scenario, DBA/Developer wanted to display the data in XML format. Therefore, DBA/Developer would still have to display the result of the returned xml data type.