Month: February 2012

CTE Hierarchy compared to the alternative

After my CTE presentation at SQL Saturday 108 in Redmond, I was asked many questions, and received several great suggestions from people.  Based on that feedback, I am updating my presentation for SQL Saturday 114 in Vancouver to include some

Posted in CTE, Performance, SQL Saturday Tagged with: , , , , , , ,

A Great Day at SQL Saturday 108 in Redmond.

WOW, what a great day at SQL Saturday 108 in Redmond.  I started out with a keynote presentation from Buck Woody.  Then I delivered my  presentation on Common Table Expressions.  Which I think went well.  There were some great questions

Posted in CTE, SQL Saturday Tagged with: , , ,

KeePass – for security and time savings

After a recent tour at a couple companies who are using the LEAN process, I have decided to take on LEAN in my everyday work. This is the first in what I think will be many videos on the Lean

Posted in LEAN, Lean Office, Steve, Video Tagged with: ,

KeePass – to improve your process

Before proceeding with the article, please take this quick password safety test. Password Safety Test – Score one point for every YES answer.: Do you write your passwords down?  ( YES  /  NO ) Do you ever re-use a password

Posted in LEAN, Steve Tagged with:

Calculating Factorials with a Recursive CTE

What is a Factorial: The product of an integer and all the integers below it; e.g., factorial four (4!) is equal to 24. The factorial of a positive integer n, written n!, is the product of all the positive integers from 1

Posted in CTE Tagged with:

CTE – With An Insert Statement

Queries with Common table expressions (CTE) are made up of two parts, the CTE part, and the SQL that references the CTE.  In preparation for SQL Saturday, the question came up of can you use an INSERT or UPDATE statement

Posted in CTE, SQL Saturday Tagged with: , , , , ,

Fibonacci Sequence

As part of my CTE research for my SQL Saturday presentation in Redmond in 2 weeks I decided to take on some classic computer science algorithms with CTE’s. Here is what I cam up with. Any math geek can tell

Posted in CTE, SQL Saturday Tagged with: ,

Generating a Tree Path with a CTE

The following example shows how to create a Tree Path with a Common Table Expression (CTE). First off what is a tree path? For this example I will be referring to the product categories at a camping and fitness store,

Posted in CTE, SQL Saturday Tagged with: , , ,

CTE Query Performance

The following question came up when working on my CTE presentation for SQL Saturday. Does a query that JOINs a CTE to itself execute the CTE query once or twice? For instance:  ;WITH deptCTE(id, department, parent)     AS (SELECT id,department,parent FROM   Departments) SELECT q1.department,q2.department   FROM deptCTE q1  INNER JOIN deptCTE q2 ON = q2.parent

Posted in CTE, SQL Saturday Tagged with: ,