T-SQL Querying: Tips and techniques for SQL Server 2005

The speaker was very knowledgeable about this topic. He focused on several specific topics and they were as follows:

• Common Table Expressions – non persistent temp table that only exist in the executing Query and is only in memory and not in the TempDB. See the following example:
  With CTE_Teched as
  (
  Select * from Sales.SalesTerritory
  )
  Select * from CTE_Teched
• Recursion Query with Common Table Expression
  With SimplerRecursive(Employee_NM, employee_Id, reportsTO, SubLevel)
  As
  (Select Employee_NM, Employee_ID, ReporttO
  From Employee_Tree Where Employee_Id =2
  Union All
  Select p.Employee_nm, p.employee_id, p. Reportsto, SubLevel +1
  From Employee_tree P
  Inner Join SimpleRecursive A on A.Employee_Id = P.ReportsTo
  Where SubLevel <2
  )
  
  Select sr.Employee_NM as employee, et.employee_nm as Boss , SubLevel
  From simpleRecursive sr
  Inner join Employee_Tree et
  On sr.reportsto=et.employee_id
  Where SubLevel <=2 –this is a better way to limit the recurrence.
  Order By sublevel ASC
  --OPTION(MAXRECURSION 2) --limits the number of recurrence. Causes an error don’t use it.
• There is an XML data type in SQL Server 2005 as we already knew. XQUERY is something that will allow us to store XML in the database and then query and manipulate it.
• Ranking and Windowing - see web site for the T-SQL
  Rank() is a new SQL Keyword
• Can use pivoting in SQL Server for cross – See web site for the T-SQL
  
  Speakers Email and WebSite: Stevef@orcsweb.com www.stephenforte.net

Summary: Session was very informative but the focus was very narrow.