On Wednesday June 24th at 6:00pm pacific time or( 25 Jun 2015 01:00 GMT ) will be speaking at the 24HOP (24 Hours of Pass) conference which is broadcast online. I will be presenting on Advanced Common Table Expressions, and it is going to be fun.   Here is the session abstract. You have been introduced to Common Table Expression, you understand …

Advanced Common Table Expressions – 24 Hours of PASS Read more »

Using the GO commend in TSQL to run a batch of code multiple times is something that I commonly do in demo’s and training’s.  What amazes me is how many people after the training will tell me that they didn’t know about using the GO command to run a statement multiple times. Here’s how it works. First lets create a table, …

Executing a TSQL batch multiple times using GO Read more »

One of the often overlooked features of SQL Server is the OUTPUT clause, which applies to INSERT, UPDATE, DELETE, and MERGE statements. In this post we are going to go over the basics of using the OUTPUT clause on an INSERT statement. What the output clause does is return a result set on queries that don’t usually return a result …

TSQL OUTPUT Clause on Insert Statements Read more »

I will be attending SQL Saturday #265 in Portland Oregon on November 16th 2013. This will be my second time attending SQL Saturday in Portland, and it is looking to be a great event. There are some great speakers on the line up that I am looking forward to learning from. I will be presenting 2 sessions on Common Table …

SQL Saturday #265 in Portland Oregon Read more »

I used to be able to do the Common Table Expression in a single 1 hour session, but after writing the book on Common Table Expressions, I discovered that I just can’t fit it into an hour with the detail that Common Table Expressions deserve. I would rather go into depth so that everyone can master CTEs rather than just breezing …

Common Table Expressions Presentations Updated. Read more »

Being day eight of the DBCC Command month at SteveStedman.com, today’s featured DBCC Command is DBCC CLEANTABLE. Many times I have worked on a database that has evolved over 10 or more years of changes from different developers and DBAs. One of the signs I see in databases like this is the waste associated with obsolete columns.  For instance someone …

DBCC CleanTable Read more »

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 with a CTE.  Referring to the documentation I confirmed that using an insert or update inside of the CTE is …

CTE – With An Insert Statement Read more »

Common Table Expressions are a great way to simplify complex derived table queries. If it makes sense to break out one derived table or subquery into a CTE, it can make sense to do multiple CTE’s. To use multiple CTE’s in a single query you just need to: Finish the first CTE Add a comma Declare the name and optional …

Multiple CTE’s in a single Query Read more »