Monday, March 29, 2010

Correlated Subqueries

Any queries can be evaluated by executing the subquery once and substituting the resulting value or values into the WHERE clause of the outer query. In queries that include a correlated subquery (also known as a repeating subquery), the subquery depends on the outer query for its values. This means that the subquery is executed repeatedly, once for each row that might be selected by the outer query.

This query retrieves one instance of each employee's first and last name for which the bonus in the SalesPerson table is 5000 and for which the employee identification numbers match in the Employee and SalesPerson tables.


USE AdventureWorks;
GO
SELECT DISTINCT c.LastName, c.FirstName, e.EmployeeID
FROM Person.Contact AS c JOIN HumanResources.Employee AS e
ON e.ContactID = c.ContactID
WHERE 5000.00 IN
(SELECT Bonus
FROM Sales.SalesPerson sp
WHERE e.EmployeeID = sp.SalesPersonID) ;
GO
And the result is,
LastName FirstName EmployeeID
------------------ -- ------------------ -----------------
Ansman-Wolfe Pamela 280
Saraiva José 282

(2 row(s) affected)
Correlated subqueries can also include table-valued functions in the FROM clause by referencing columns from a table in the outer query as an argument of the table-valued function. In this case, for each row of the outer query, the table-valued function is evaluated according to the subquery.




Sunday, March 7, 2010

Data Compression in SQL Server 2008

Estimate the Savings in Storage Space Using Inbuilt Stored Procedure..




sp_estimate_data_compression_savings inbuilt system stored procedure gives the DBA the facility of estimating the saving in storage space . The mentioned TSQL Query shows the space saved when Row Level Compression is enabled for Production.TransactionHistoryArchive Table of AdventureWorks Database.

The attached SQL code snippet shows that there are columns namely size_with_current_compression_settings (KB) and size_with_requested_compression_settings (KB) which provides you an estimate on the amount of disk space which you can save for Production.TransactionHistoryArchive table.

Estimate the Savings in Storage Space Using Data Compression Wizard
SQL Server 2008 inbuilt wizard Data Compression Wizard provides the facility of estimate the savings of storage space when compression is enabled for a particular table or index in a database.

To do that:
  • Click on the AdventureWorks and under the tables find "Production.TransactionHistoryArchive".
  • Right Click on the Production.TransactionHistoryArchive and then click on "Storage" and then "Manage Compression".
  • Click on next













  • After that click on "Row" from the Compression type drop down list.













  • Select the compression type option as Row and click the calculate button on the wizard, it will calculate the amount of storage space required when row compression type is used.













  • Click Next to go to; Select an Output Option Page. There are many options such as create a script, run immediately, or create a Schedule. Click Next to continue with the wizard.
  • Finally Click on Finish.