Kasım 2008
M	T	W	T	F	S	S
« Oct		Dec »
	1	2
3	4	5	6	7	8	9
10	11	12	13	14	15	16
17	18	19	20	21	22	23
24	25	26	27	28	29	30

23 Nov 2008 için Arşiv

DATABASE JOURNAL – Featured Database Articles – MSSQL

Yazan: esersahin 23/11/2008

http://www.databasejournal.com/features/mssql/archives.php

MS SQL Archives

Yazı kategorisi: Analysis Services, C#, CTE, Data, Data Mining, Integration Services, Report Builder, Report Designer, Report Server, Reporting Services, Sql Server, Sql Server Join, Transact-SQL | » yorum bırak;

Don Schlichting

Yazan: esersahin 23/11/2008

http://www.databasejournal.com/article.php/3085181

Don Schlichting is a Microsoft MSCE, MCDBA and an Oracle Certified Associate. Working at Professional Control Corporation as the IT Director, he can be reached at dons@pccweb.com.

Current Articles

SQL Server 2008 Recovery Models and Backups - November 7, 2008
SQL 2008 Backup and Restore Part 1 - October 3, 2008
What is SQL Server - September 5, 2008
Create Your First SQL Server Database in 3 Quick Steps - August 1, 2008
SQL Server Profiler Part 2 - July 2, 2008
Introduction to SQL 2005 Profiler Part 1 - June 2, 2008
SQL Server Management Studio Reports and Dashboard - May 2, 2008
Installing SQL Server 2008 - December 3, 2007
SQL 2008 Data Types - November 1, 2007
SQL Assistant 2.0 - July 10, 2007
SQL 2005 Backups using LiteSpeed - June 1, 2007
Using SQL 2005 with Expression Web - May 4, 2007
Using SQL for IIS Logs, Part 2 - January 3, 2007
Using SQL for IIS Web Logs, Part 1 - December 1, 2006
SQL Server 2005 Management Studio Part 2 - November 3, 2006
MS SQL Server 2005 Management Studio Part 1 - October 6, 2006
SQL 2005 Reporting Services Part 2 - February 3, 2005
SQL 2005 Reporting Services Part 1 - January 5, 2005
SQL Pivot and Cross Tab - July 1, 2005
SQL Server 2005 System Tables and Views - June 3, 2005
Common Table Expressions (CTE) on SQL 2005 - May 6, 2005
Microsoft SQL Server Express 2005 (MSDE) - April 1, 2005
Cursors with SQL 2000 Part 2 - November 5, 2004
Cursors with SQL 2000 Part 1 - October 1, 2004
OSQL Utility - September 3, 2004
Test to Production - August 3, 2004
Microsoft SQL Server 2000 Desktop Engine MSDE - July 2, 2004
Control of Flow with TSQL - June 4, 2004
User Defined Functions in Microsoft SQL Server 2000 - May 7, 2004
MS SQL Server Distributed Partitioned Views Part 2 - April 2, 2004
MS SQL Server Distributed Partitioned Views - March 5, 2004
Getting the Wrong Identity in Microsoft SQL Server identity Columns? - February 6, 2004

Series Articles

Storing Images and BLOB files in SQL Server

Part 4
Part 3
Part 2
Part 1

What’s new in SQL 2008

Part 3
Part 2
Part 1

Microsoft SQL 2005 Maintenance Wizard

Part 3
Part 2
Part 1

MS SQL Joins

Part 3
Part 2
Part 1

Executing SQL Stored Procedures from inside a Web Application

Part 4
Part 3
Part 2
Part 1

Controlling Transactions and Locks

Part 5: SQL 2005 Row Versioning, Read Committed Snapshot and Snapshot Isolation
Part 4 : SQL 2005 Snapshot Introduction
Part 3: Lock Hints
Part 2: Types of Lock Modes
Part 1: Introduction

Full Text Search on SQL 2000

Part 4 – Using Full Text Search to query text located inside Microsoft Office documents
Part 3 – Maintenance issues required to keep Catalogs current
Part 2 – Sample Data
Part 1 – Introduction

Linked Severs on MS SQL

Part 4 – Using Oracle as a Linked Server target from MS SQL
Part 3 – Linked Server Security
Part 2 – Data sources and security
Part 1 – Introduction

Yazı kategorisi: Developer, SQL, Sql Server, Transact-SQL | » yorum bırak;

Common Table Expressions (CTE) on SQL 2005

Yazan: esersahin 23/11/2008

http://www.databasejournal.com/features/mssql/article.php/3502676/Common-Table-Expressions-CTE-on-SQL-2005.htm

By Don Schlichting

This article will introduce Common Table Expressions (CTE) on SQL 2005, and compare them with related SQL 2000 methods such as Derived and Temporary Tables.

Introduction

New in SQL 2005 are Common Table Expressions, CTE for short. A Common Table Expression is an expression that returns a temporary result set from inside a statement. This result set is similar to a hybrid Derived Table meets declared Temporary Table. The CTE contains elements similar to both. Some of the most frequent uses of a Common Table Expression include creating tables on the fly inside a nested select, and doing recursive queries. Common Table Expressions can be used for both selects and DML statements. The natural question is, if we have been using TSQL for this long without Common Table Expressions, why start using them now? There are several benefits to learning CTEs. Although new to SQL Server, Common Table Expressions are part of ANSI SQL 99, or SQL3. Therefore, if ANSI is important to you, this is a step closer. Best of all, Common Table Expressions provide a powerful way of doing recursive and nested queries in a syntax that is usually easier to code and review than other methods.

CTE

Below is very simple Common Table Expression example. All the CTE examples in this article were created on SQL 2005 Beta 2 Developer Edition. The example CTE will be used to return the list price of products, and our sell price, which is five percent below list. This first example is very simple, and could be replaced by a single select statement, but it will demonstrate some key CTE points. The examples will get more advanced as the article progresses.

USE AdventureWorks
GO

WITH MyCTE( ListPrice, SellPrice) AS
(
  SELECT ListPrice, ListPrice * .95
  FROM Production.Product
)

SELECT *
FROM MyCTE

GO

The database, Adventure Works, is now the SQL 2005 default sample database. Gone are the days of Northwind. On the beta version used for this article, Adventure Works did not install by default. Instead, during install, click Advanced then select sample databases.

The Common Table Expression is created using the WITH statement followed by the CTE name. Immediately trailing is a column list, in our case, we are returning two columns, ListPrice, and SellPrice. After the AS, the statement used to populate the two returning columns begins. The CTE is then followed by a select calling it.

The BOL format of a Common Table Expression is listed below;

[ WITH <common_table_expression> [ ,...n ] ]

<common_table_expression>::=
    expression_name [ ( column_name [ ,...n ] ) ]
    AS
    ( CTE_query_definition )

Temporary Tables

This small example displays several interesting concepts. The CTE is called by name from the SELECT * statement, similar to a Temporary Table. However, if a Temporary Table were used, it would first have to be created, and then populated;

CREATE TABLE #MyCTE
(
  ListPrice money,
  SellPrice money
)

INSERT INTO #MyCTE
	(ListPrice, SellPrice)
	SELECT ListPrice, ListPrice * .95
	FROM Production.Product

However, a Temporary Table could be called over and over again from with in a statement. A Common Table Expression must be called immediately after stating it. Therefore, in this example, the call to the CTE will fail.

USE AdventureWorks
GO

WITH MyCTE( ListPrice, SellPrice) AS
(
  SELECT ListPrice, ListPrice * .95
  FROM Production.Product
)

SELECT *
FROM Production.Location

SELECT *
FROM MyCTE

GO

The call to MyCTE will fail with the following error:

Msg 208, Level 16, State 1, Line 14
Invalid object name 'MyCTE'.

The CTE itself has some syntactical restrictions. Compute, Order By (without a TOP), INTO, Option, FOR XML, and FOR BROWSE are all not allowed.

Derived Tables

A SQL 2000 method somewhat similar to a CTE is a Derived Table. These are temporary result sets created by a query statement. However, they cannot be referenced by name, and may be used only once. In complex statements, they may not be as readable as Common Table Expressions. The CTE rewritten as a Derived Table would be:

SELECT *
FROM (
  SELECT ListPrice, (ListPrice * .95) AS SellPrice
  FROM Production.Product
  )
MyDerivedTable

Common Table Expressions will not displace Derived Tables however. There will still be many instances when Derived Tables will be a better fit. However, in some complex statements, Common Tables Expression may be clearer to understand.

Recursive Queries

One of the most powerful features of Common Table Expressions is their ability to be self-referenced by name, allowing for a recursive query. A Recursive Query is a query that calls itself. Below is a modified example of BOL recursive query. The purpose of the statement is to display each employee at Adventure Works, and who their manager is. The Employee table is self joined, where the ManagerID joins back on the same table to the EmployeeID.

The statement first looks for all top-level managers, the records where the ManagerID is null. In this table, there is only one. Next, we will self-reference this CTE by using its name “DirectReports” joined back to our top-level managers.

USE AdventureWorks ;
GO
WITH DirectReports(LoginID, ManagerID, EmployeeID) AS
(
    SELECT LoginID, ManagerID, EmployeeID
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.LoginID, e.ManagerID, e.EmployeeID
	FROM HumanResources.Employee e
    INNER JOIN DirectReports d
    ON e.ManagerID = d.EmployeeID
)
SELECT *
FROM DirectReports ;
GO

Running this statement produces the following result set:

The first result line shows Ken, as the top-level manager, reporting to no one. Beneath him reports David, Terri, Peter, Jean, Laura, James and Brian. At Stephen, we see a change, with him reporting to Brian.

The Recursive Common Table Expression introduces some additional syntax. The statement now has two definition components. The first is called the Anchor Member:

SELECT LoginID, ManagerID, EmployeeID
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL

This statement must be followed by a UNION ALL. The purpose of the UNION ALL is to tie the results of two statements into one result set.

The next definition is called the recursive member. It calls back to itself using the CTE name; INNER JOIN DirectReports d

MAXRECURSION

The key word MAXRECURSION can be used as a query hint to stop a statement after a defined number of loops. This can stop a CTE from going into an infinite loop on a poorly coded statement. To use it in our above example, include it on the last line;

SELECT *
FROM DirectReports;
OPTION (MAXRECURSION 3);

Conclusion

New in SQL 2005, Common Table Expressions will be worth exploring for anyone doing TSQL work. Their use in Recursive Queries alone warrants them worthy of consideration. Combined with their straightforward syntax, expect to see them used frequently in complex statements.

» See All Articles by Columnist Don Schlichting

Yazı kategorisi: CTE, SQL, Sql Server, Transact-SQL | » yorum bırak;

Find blocking processes using recursion in SQL Server 2005

Yazan: esersahin 23/11/2008

http://www.builderau.com.au/program/sqlserver/soa/Find-blocking-processes-using-recursion-in-SQL-Server-2005/0,339028455,339286600,00.htm

Blocking occurs in SQL Server 2005 when one process has one or more records locked, while another process tries to acquire locks on the same set (or subset) of records.

This causes the second process to wait until the first process is done so it can acquire the records; this can cause other processes to be blocked if they are waiting on the resources that the second connection has acquired. These processes are ultimately dependent upon the first process to complete its work before they can complete theirs. This can create a daisy-chain of processes waiting to complete their work.

This blocking situation is a bit different than a deadlock scenario. A deadlock occurs when process A has ownership of resources that process B needs. Process B, in turn, owns resources that process A needs to complete its work. In theory, this would last forever. Fortunately, SQL Server 2005 has algorithms to find these scenarios and stop one process so the other can finish its work. On the other hand, blocking can last a lot longer than a deadlock because it isn’t stopped automatically by SQL Server 2005. The blocked processes must wait until the blocking connection finishes its work before they can finish theirs.

Finding blocking connections

SQL Server 2005 has several system tables and views that can provide a ton of insight into blocking problems that you may be facing in your database environment.

Through these procedures, you can find blocking chains. I define a blocking chain as one particular process is blocking one or more processes, and those processes are blocking other processes. These blocking chains happen all the time, especially when there are instances of long-running transactions. Many times, I have seen a DBA find the process that is causing the most locking and stop the process. This is usually not a great idea, and it is definitely not the way to find the root of the problem. The processes that cause the most locking are usually doing so because they are being blocked by another process.

The following statement pulls information from the sys.sysprocesses system table for user processes. The information is placed into a temporary table named #Processes that I will use for my recursive Common Table Expression (CTE) query. The sys.sysprocesses table is a great tool because it contains a lot of data in one table. (The sys.sysprocesses table may not be included in future versions of SQL Server.) I can also use the CROSS APPLY construct to get the actual SQL statement that is being used based upon the sql_handle value in the table.


SELECT
             s.spid, BlockingSPID = s.blocked, DatabaseName = DB_NAME(s.dbid),
             s.program_name, s.loginame, ObjectName = OBJECT_NAME(objectid,                      s.dbid), Definition = CAST(text AS VARCHAR(MAX))
 INTO        #Processes
 FROM      sys.sysprocesses s
 CROSS APPLY sys.dm_exec_sql_text (sql_handle)
 WHERE
            s.spid > 50

The following statement declares and uses the recursive CTE for finding blocking chains in the temp table I just created for the sys.sysprocesses table.

The WITH statement declares the CTE with the code inside of the parentheses being the body of the expression. In the first SELECT statement, I am finding all processes that are not blocked themselves but are blocking at least one other process. To do this, I just join the #Processes temp table to itself based on the SPID field and the BlockingSPID field. This is a crucial step in the query, as I am locating the starting point for finding the chain of processes that are being blocked.

This first SELECT can essentially be thought of as a “base case,” which will act as a root for the rest of the recursion. This SELECT has a couple of functions that will allow me to tell the hierarchy of the recursion. The RowNo field uses the ROW_NUMBER function which will indicate the separate number of processes that are blocking other processes. The LevelRow, which I initially declare as 0, will indicate the recursion level for that particular blocking process. (This information will make more sense later in the article when I give an example of the information that is provided by the CTE.)

The next SELECT statement after UNION ALL (there always has to be a UNION ALL in a recursive CTE) is the recursive section; it joins what is returned by the CTE and the #Processes temp table based upon the BlockingSPID from the temp table and the SPID field from the CTE. For this statement, I am only concerned with the blocked statements.

The last SELECT statement runs the recursive CTE and returns the values.


WITH Blocking(SPID, BlockingSPID, BlockingStatement, RowNo, LevelRow)
 AS
 (
      SELECT
       s.SPID, s.BlockingSPID, s.Definition,
       ROW_NUMBER() OVER(ORDER BY s.SPID),
       0 AS LevelRow
     FROM
       #Processes s
       JOIN #Processes s1 ON s.SPID = s1.BlockingSPID
     WHERE
       s.BlockingSPID = 0
     UNION ALL
     SELECT
       r.SPID,  r.BlockingSPID, r.Definition,
       d.RowNo,
       d.LevelRow + 1
     FROM
       #Processes r
      JOIN Blocking d ON r.BlockingSPID = d.SPID
     WHERE
       r.BlockingSPID > 0
 )
 SELECT * FROM Blocking
 ORDER BY RowNo, LevelRow

Analysing the sample code

In the output, there are two processes blocking other processes; this is identified in a couple of ways. First, there are two records that have a 0 in the BlockingSPID field. These records will have the value 0 in the LevelRow field; they are also the “base case” for the recursion. The RowNo field indicates the separate set of blocking groups. The first set of statements that are blocked have the value 1 for the RowNo field, the second set of statements that are blocked have the value 2 for the RowNo field, etc.

In analysing the first group of blocked statements, I see that procedure usp_StoredProcedure1 is the initial point of blocking — that procedure is blocking usp_StoredProcedure2. The LevelRow field can be used here, or I can try to match the SPID in one row to the BlockingSPID value in the next row. I find that the LevelRow field is the easier of the two choices. I also see the following:

|>usp_StoredProcedure2 is blocking usp_StoredProcedure3.
|>usp_StoredProcedure3 is blocking usp_StoredProcedure4.
|>usp_StoredProcedure 4 is blocking two stored procedures: usp_StoredProcedure5 and usp_StoredProcedure14. The LevelRow value is the same for these two rows.
|>The blocking analysis concludes for RowNo 1 with usp_StoredProcedure5 blocking the final statement usp_StoredProcedure6.

Using this tool

There are two very useful ways to use this tool for finding blocking on your SQL Server 2005 system. One way is to run it every couple of minutes and store the results in a table for later analysis. This is very handy if you encounter blocking problems during the night because it allows you to find the problem the next day. The other way is to wrap the functionality up into a system stored procedure that you can easily call anytime if someone is complaining about sessions timing out or the database engine running slow.

Yazı kategorisi: CTE, SQL, Sql Server, Transact-SQL, WITH | » yorum bırak;

R.H. Sheldon

Yazan: esersahin 23/11/2008

http://www.rhsheldon.com/

Freelance writer R.H. Sheldon has written a wide range of material―for an even wider range of readers. His writing includes news stories, feature articles, advice columns, restaurant reviews, legal summaries, marketing copy, and training manuals. One of his news stories received a Runner-up/Best News Story of the Year award from the Colorado Press Association. One of his feature articles received an Honorable Mention/Best Feature Story of the Year award, also from the Colorado Press Association.

In addition to his other work, R.H. Sheldon has authored or co-authored 11 technical books and a number of technical articles. The Puget Sound Chapter of the Society for Technical Communication awarded one of these books a Certificate of Merit. He has also worked as a content editor, developmental editor, technical editor, and copy editor.

Yazı kategorisi: Developer, SQL, Sql Server, sqlserverpedia | » yorum bırak;

SQL-Server-Functions

Yazan: esersahin 23/11/2008

http://sqlserverpedia.com/wiki/SQL-Server-Functions

From SQLServerPedia

Jump to: navigation, search

Functions let you replace and access strings and dates in SQL Server. You can call them passing times, tables, dates, and other variables, and get back results.

Here are some articles to help you get started fast with functions:

Common SQL Server Functions

These functions are the bread and butter of your stored procedures and views:

SQL Server Aggregate Functions – like COUNT, MIN, MAX
SQL Server Date Functions – like DATEPART, DATEADD and DATEDIFF
SQL Server Cursor Functions – like @@FETCH, @@CURSOR_ROWS

SQL Server Replace Function

To replace characters in a string, use SQL Server 2005’s built-in string functions:

SQL Server Replace Function

SQL Server String Functions

SQL Server can do more than just replace characters in a string: it can also uppercase and lowercase strings, calculate string lengths, and more.

SQL Server String Functions – like UPPER, SUBSTRING, LTRIM and RTRIM
SQL Server Text Functions – for longer string fields in TEXT and NTEXT fields.

SQLServerPedia Editors’ Favorite Articles on Functions

Our editors recommend these links:

SQL Server 2000 Functions – if you can only develop against SQL Server 2000 servers, this list explains those functions that work on SQL 2000.
SQL Server Configuration Functions – lets the database administrator query for @@MAX_CONNECTIONS, @@MAX_PRECISION, @@SERVERNAME and more.
SQL Server Security Functions – like IS_SRVROLEMEMBER, USER, USER_NAME(), HOST_NAME

Yazı kategorisi: SQL, Sql Server, Transact-SQL, sqlserverpedia | » yorum bırak;

CTE – CTE’s with Variables and Parameters

Yazan: esersahin 23/11/2008

http://sqlserverpedia.com/wiki/CTE_-_CTE%E2%80%99s_with_Variables_and_Parameters

CTE can reference variables declared within the same batch or parameters used within the same code module. For example, the following query uses variables with CTE:

DECLARE @n TINYINT
SET @n = 3;
WITH direct_reports_CTE
AS
 (
 SELECT
	  TOP (@n) ManagerID AS manager_id,
	  COUNT(*) AS number_of_direct_reports
 FROM HumanResources.Employee
 WHERE ManagerID IS NOT NULL
 GROUP BY ManagerID
 ORDER BY 2 DESC
 )
SELECT SUBSTRING(LoginID, (CHARINDEX('\', LoginID)+1), (LEN(LoginID)-
CHARINDEX('\', LoginID)-1)) AS ManagerName,
 number_of_direct_reports
FROM direct_reports_CTE a
INNER JOIN HumanResources.Employee b ON a.Manager_ID = b.EmployeeID
ORDER BY 2 DESC

Next set of statements creates a stored procedure which exploits a CTE referencing a stored procedure parameter:

CREATE PROC return_top_bosses (@n TINYINT)
AS
WITH direct_reports_CTE
AS
 (
 SELECT
	  TOP (@n) ManagerID AS manager_id,
	  COUNT(*) AS number_of_direct_reports
 FROM HumanResources.Employee
 WHERE ManagerID IS NOT NULL
 GROUP BY ManagerID
 ORDER BY 2 DESC
 )
SELECT
 SUBSTRING(LoginID, (CHARINDEX('\', LoginID)+1), (LEN(LoginID)-
 CHARINDEX('\', LoginID)-1)) AS ManagerName,
 number_of_direct_reports
FROM direct_reports_CTE a
INNER JOIN HumanResources.Employee b ON a.Manager_ID = b.EmployeeID
ORDER BY 2 DESC

You could easily create a view or a user-defined function using the same CTE. We could call the stored procedure created by the previous statement as follows:

EXEC return_top_bosses 5

Yazı kategorisi: CTE, SQL, Sql Server, Transact-SQL, sqlserverpedia | » yorum bırak;

Simplify queries with SQL Server 2005 common table expressions (CTEs)

Yazan: esersahin 23/11/2008

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1299502,00.html#

SQL Server 2005 introduced a valuable new Transact-SQL language component – a common table expression (CTE) — that can be a handy alternative to derived tables and views. By using CTEs, you can create named result sets to reference from within your SELECT, INSERT, UPDATE, and DELETE statements without having to persist any metadata about the result set structure. In this article, I explain how to create CTEs in SQL Server 2005 – including how to create a recursive query using a CTE – and provide several examples that demonstrate how they work. Note that for these examples I use the AdventureWorks sample database that ships with SQL Server 2005.

Creating a basic CTE in SQL Server 2005

You construct CTEs by adding a WITH clause before the SELECT, INSERT, UPDATE, or DELETE statement. The following syntax shows the basic structure of the WITH clause and CTE definition:

[WITH <CTE_definition> [,...n]]
<SELECT, INSERT, UPDATE, or DELETE statement that
calls the CTEs>

<CTE_definition>::=
CTE_name [(column_name [,...n ])]
AS
(
CTE_query
)

As the syntax shows, you can define multiple CTEs within the optional WITH clause. The CTE definition includes the name of the CTE, the CTE column names, the AS keyword and the CTE query enclosed in parentheses. Note that the number of CTE column names must match the number of columns returned by the CTE query. In addition, the column names are optional if the CTE query supplies all column names.

Now that you have a basic idea of the CTE syntax in SQL Server, let’s look at an example of a CTE definition to help better understand this syntax. The following example defines a CTE named ProductSold and then references the CTE within a SELECT statement:

WITH ProductSold (ProductID, TotalSold)
AS
(
SELECT ProductID, SUM(OrderQty)
FROM Sales.SalesOrderDetail
GROUP BY ProductID
)
SELECT p.ProductID, p.Name, p.ProductNumber,
ps.TotalSold
FROM Production.Product AS p
INNER JOIN ProductSold AS ps
ON p.ProductID = ps.ProductID

As you can see, a WITH clause precedes the SELECT statement that references the CTE. The first line of the WITH clause includes the name of the CTE (ProductSold) and the names of the two columns within the CTE (ProductID and TotalSold). Next comes the AS keyword, followed by the CTE query in parentheses. In this case, the CTE query returns the total number of individual products sold.

The SELECT statement that follows the WITH clause references the CTE by name when joining the Product table to the CTE, based on the ProductID columns. The statement calls the CTE as it would a table or view. However, unlike a table or view, the CTE is available only to the statement that immediately follows the WITH clause. If you reference the CTE in a subsequent statement — without redefining the CTE — you’ll receive an error.

One advantage to using common table expressions is that you can reference a CTE multiple times in the calling statement. For example, the following statement defines a CTE named Employees and then calls that CTE twice in the SELECT statement that follows the WITH clause:

WITH Employees (EmpID, MgrID, FName, LName, Email)
AS
(
SELECT e.EmployeeID, e. ManagerID, c.FirstName, c.LastName, c.EmailAddress FROM HumanResources.Employee e INNER JOIN Person.Contact c ON e.ContactID = c.ContactID ) SELECT e.FName + ' ' + e.LName AS EmpName, e.Email AS EmpEmail, m.FName + ' ' + m.LName AS MgrName, m.Email AS MgrEmail FROM Employees e LEFT OUTER JOIN Employees m ON e.MgrID = m.EmpID
SELECT ProductID, AVG(StandardCost)
FROM Production.ProductCostHistory
GROUP BY ProductID
),
Sold (ProductID, AvgSold)
AS
(
SELECT ProductID, AVG(OrderQty)
FROM Sales.SalesOrderDetail
GROUP BY ProductID
)
SELECT p.ProductID, p.Name,
(AvgCost * AvgSold)AS TotalCost
FROM Sold s
INNER JOIN Production.Product p
ON s.ProductID = p.ProductID
INNER JOIN Cost c
ON p.ProductID = c.ProductID
SELECT ProductID, AVG(StandardCost)
FROM Production.ProductCostHistory
GROUP BY ProductID
),
Sold (ProductID, AvgSold)
AS
(
SELECT ProductID, AVG(OrderQty)
FROM Sales.SalesOrderDetail
GROUP BY ProductID
),
Total (ProductID, TotalCost)
AS
(
SELECT c.ProductID, (AvgCost * AvgSold)
FROM Cost c
INNER JOIN Sold s
ON c.ProductID = s.ProductID
)
SELECT p.ProductID, p.Name, t.TotalCost
FROM Production.Product p
INNER JOIN Total t
ON p.ProductID = t.ProductID

In this example, the SQL Server CTE query returns a list of employee IDs, names and email addresses, as well as their managers’ IDs. The SELECT statement following the WITH clause then joins the CTE with itself to return the managers’ names and email addresses. You can achieve the same results by using derived tables (subqueries), but that means repeating the same subquery multiple times and working with code that’s more complex.

Creating multiple CTEs in a WITH clause

As you saw in the CTE syntax, you can define multiple CTEs in your WITH clause and then call each of those CTEs as often as necessary in the statement that follows. Take a look at the example below that demonstrates how this works. The following WITH clause includes two CTE definitions:

WITH
Cost (ProductID, AvgCost)
AS
(

The first CTE definition defines a CTE named Cost, and the second definition defines one named Sold. The Cost CTE returns the average cost of each product based on its cost history. The Sold CTE returns the average number of products sold per order. The SELECT statement after the WITH clause joins these two CTEs with the Product table to return the total cost of each product based on the average number sold and the average cost of the product.

You can easily define multiple CTEs within the WITH clause, but you can take this a step further by defining CTEs that reference the CTEs defined before it. For example, the WITH clause below defines three CTEs (Cost, Sold, and Total):

WITH
Cost (ProductID, AvgCost)
AS
(

Notice that the CTE query in the Total CTE definition joins the Cost and Sold CTEs, whose CTE definitions precede Total. You cannot reference an undefined CTE. For example, you cannot reference the Total CTE within the Sold CTE. Now, the SELECT statement that follows the WITH clause needs to join the Product table to the Total CTE only, rather than joining to both the Cost and Sold CTEs.

Creating a recursive common table expression

One of the most valuable features of a CTE in SQL Server is its ability to create a recursive query — a type of query that repeatedly references itself in order to return subsets of data. A recursive query is most commonly used to return hierarchical data. For instance, the Employee table in the AdventureWorks database includes the manager ID of each employee. The manager ID is actually the employee ID of that manager. As a result, the Employee table contains the entire hierarchical direct reports structure from the CEO on down.

You can define a CTE to retrieve this hierarchical structure by creating a CTE query that uses a UNION ALL, UNION, INTERSECT, or EXCEPT operator to join multiple SELECT statements. The best way to show you how this works is through an example.

In this WITH clause, the CTE query includes two SELECT statements joined by a UNION ALL operator:

WITH Reports (EmpLevel, EmpID, ContactID, MgrID)
AS
(
SELECT 1, EmployeeID, ContactID, ManagerID
FROM HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT r.EmpLevel + 1, e.EmployeeID, e.ContactID,
e.ManagerID
FROM HumanResources.Employee e
INNER JOIN Reports r
ON e.ManagerID = r.EmpID
)
SELECT r.EmpLevel, r.EmpID,
c.FirstName + ‘ ‘ + c.LastName AS EmpName,
MgrID
FROM Reports r
INNER JOIN Person.Contact c
ON r.ContactID = c.ContactID
ORDER BY r.EmpLevel, r.MgrID, r.EmpID

The first SELECT statement in the CTE query retrieves only the top-level employee — the CEO. To select the top-level employee, use a WHERE clause that specifies that the ManagerID value is null.

Tips on tuning SQL Server performance:

T-SQL error handling with Try…Catch blocks

Tricks to increase SQL Server query performance

T-SQL performance problems and solutions

In other words, this is the person who does not directly report to another manager. Note: The first column in the SELECT statement is 1. This is used to designate that the employee returned by this query is at the top level, Level 1.

The second SELECT statement (after the UNION ALL operator) joins the Employee table to the Reports CTE itself based on the manager and employee IDs. By self-referencing the CTE in this way, SQL Server automatically treats this as a recursive query and repeats the query as many times as necessary to return each level of employees. Every time the query runs, the first column adds 1 to the value so that each level is incremented by 1.

The SELECT statement that follows the WITH clause joins the Reports CTE to the Contact table to retrieve the employees’ name. The following query results show a sample of the data returned by this statement:

EmpLevel	EmpID	EmpName	MgrID
1	109	Ken Sanchez	NULL
2	6	David Bradley	109
2	12	Terri Duffy	109
2	42	Jean Trenary	109
2	140	Laura Norman	109
2	148	James Hamilton	109
2	273	Brian Welcker	109
3	2	Kevin Brown	6
3	46	Sariya Harnpadoungsataya	6
3	106	Mary Gibson	6
3	119	Jill Williams	6
3	203	Terry Eminhizer	6
3	269	Wanida Benshoof	6
3	271	John Wood	6
3	272	Mary Dempsey	6
3	3	Roberto Tamburello	12
3	66	Janaina Bueno	42
3	102	Dan Bacon	42
3	117	François Ajenstat	42
3	128	Dan Wilson	42
3	149	Ramesh Meyyappan	42
3	150	Stephanie Conroy	42
3	176	Karen Berg	42
3	30	Paula Barreto de Mattos	140
3	71	Wendy Kahn	140
3	103	David Barber	140
3	139	David Liu	140
3	21	Peter Krebs	148
3	44	A. Scott Wright	148
3	200	Hazem Abolrous	148
3	218	Gary Altman	148
3	268	Stephen Jiang	273
3	284	Amy Alberts	273
3	288	Syed Abbas	273
4	4	Rob Walters	3
4	9	Gail Erickson	3
4	11	Jossef Goldberg	3

The results are listed according to the employee’s level. Notice that the second through seventh rows show a MgrID value of 109, which is the ID of the top-level employee shown in the first row. The subsequent rows reflect the same hierarchical nature of the data.

Recursive CTEs, like other common table expressions in SQL Server, provide powerful tools for retrieving data. Unlike views, you don’t have to persist the metadata. And unlike derived tables, you don’t have to repeat code unnecessarily. CTEs help simplify complex code by letting you more easily separate your code into discrete units. And when it comes to recursive queries, CTEs can’t be beat. When you first start using CTEs, you might have to play around a little to become comfortable with them, but once you do, you’ll never go back.

ABOUT THE AUTHOR:

Robert Sheldon is a technical consultant and the author of numerous books, articles and training material related to Microsoft Windows and various relational database management systems (including SQL Server) as well as business intelligence design and implementation. He is also the author of the novel Dancing the River Lightly. You can find more information at http://www.rhsheldon.com.

Yazı kategorisi: CTE, Sql Server, WITH | » yorum bırak;

using …

Pure C#

Categories

Archives

Calendar

Blog Stats

Recent Posts

Top Posts

Top Clicks

23 Nov 2008 için Arşiv

DATABASE JOURNAL – Featured Database Articles – MSSQL

Don Schlichting

Current Articles

Series Articles

Common Table Expressions (CTE) on SQL 2005

Introduction

CTE

Temporary Tables

Derived Tables

Recursive Queries

MAXRECURSION

Conclusion

Find blocking processes using recursion in SQL Server 2005

R.H. Sheldon

SQL-Server-Functions

From SQLServerPedia

Common SQL Server Functions

SQL Server Replace Function

SQL Server String Functions

SQLServerPedia Editors’ Favorite Articles on Functions

CTE – CTE’s with Variables and Parameters

Simplify queries with SQL Server 2005 common table expressions (CTEs)

Pure C#

Categories

Category Cloud

Archives

Calendar

Blog Stats

Recent Posts

Top Posts

Top Clicks

23 Nov 2008 için Arşiv

Current Articles

Series Articles

Introduction

CTE

Temporary Tables

Derived Tables

Recursive Queries

MAXRECURSION

Conclusion

From SQLServerPedia

Common SQL Server Functions

SQL Server Replace Function

SQL Server String Functions

SQLServerPedia Editors’ Favorite Articles on Functions