Advanced TSQL Takeover

Thanks for joining the 3 hour webinar I did for Pragmatic Works on Advanced TSQL. There were, as could be expected, many questions regarding the webinar so in this blog post I will try to answer as many of those questions as I can.

The TSQL Scripts used in the class can be found HERE: Please note that for one of the examples you will need to create and load the MicrosoftStockHistory example and I have provided a script to do just that.

Common Table Expressions

Question: Why does a common table expression begin with a semi-colon?

Answer: The WITH keyword is used for other purposes in TSQL other than just CTEs so to avoid confusion any statements prior to the CTE must be terminated using a semi-colon.

Question: What are the performance differences between CTEs and Temp tables?

Answer: Unfortunately CTEs and Temp Tables have entirely different use case scenarios so this is like comparing apples to oranges. CTEs are logical tables used to simplify code, essentially they are like views. Temp tables on the other hand are physically materialized on disk in the temp db. They also have column statistics and can be indexed for optimizing your TSQL Code. The answer to this question is really much more in depth then what I have provided here but this is the really really short answer.

Question: Is there a performance difference with CTE and Derived tables?

Answer: Rarely will you see a performance difference here, I have always seen identical plans personally when comparing performance. However, I called and talked to the smartest DBA I know, Bradley Ball. There are some situations with self-referencing CTEs where the SQL Server optimizer can generate a bad SQL Plan due to the Cardinality Estimator not being able to read the statistics of the underlying table. This is the same behavior that you would see if you were nesting views on top of views. In those situations a Derived table or a Temp Table would be a better option.

Window Functions: Framing

Question: When using Range for the frame could the duplicates values be eliminated with the Partition By Clause?

Answer: Range can generate unusual results when used to generate running totals. The unusual results occur when the Order By clause within a partition can contain duplicate values therefore duplicating the “running total” for multiple cells. The fix here is to either use the ROWS framing option or to choose a column for the Order BY clause that is unique.

Question: Can you call LAG(LAG(ClosePrice)) to get the close price from 2 days previous?

Answer: The default behavior of lag is to go back one record. The default behavior can be overridden by using the optional parameter [offset] when writing your lag function. For example the code here would look like: LAG(ClosePrice, 2) OVER (ORDER BY DATE)

Question: Is the performance gain of framing window functions only applicable for the aggregate window functions, i.e. not important for the other window functions?

Answer: The performance gain can be achieved also with ranking and analytical window functions. Anytime an order by clause is required we can specify ROWS instead of the default behavior of RANGE. We showed a demo of using ROWS with the Last_Value analytical window function in the webinar.

Question: Will there be a bathroom break?

Answer: I don’t plan on making it three hours without taking a break! Smile

Working with XML in SQL Server

Question: Can you query a XML File?

Answer: Absolutely. Querying an XML file is very easy to do and this is also referred to as Shredding XML. I will try to write a specific blog walking through how to do this in the coming weeks. Please note that I didn’t have enough time to show this in our extended 3 hour presentation but in the Pragmatic Works Advanced TSQL course this is covered.

Question: Can you create element centric XML but include some columns as Attributes?

Answer: This can be done easily by using the XML Path mode. XML Path generates element centric XML by default, this is in contrast to XML Auto and Raw which generates attribute centric XML. With XML Path you can convert any of the columns into attributes by simply giving it an alias name preceded by the @ symbol. For example FirstName as [@FirstName].

On-Demand Training

Question: Can we see a syllabus of the on demand courses and what material is presented in each class/section without signing up for a trial?

Answer: Please visit, once there scroll down to the bottom of the page and then click on one of the individual class names to get the class outline and other information.

Question: Does the on-demand training include the ability to ask questions when something isn’t clear?

Answer: Yes, you can send questions to our training director directly. Another great feature of our On-Demand training platform is that it comes with two free hours of virtual mentoring. This is one on one time with a Pragmatic Works consultant.


SQL Tips! How to edit the query for Edit TOP 200 Rows in Management Studio (SSMS)

It has been some time since I have written one of my SQL Tip blogs. I like to use the “Edit Top 200” rows functionality provided by SSMS when making quick but different updates across like records in SQL Server. Did you know you can edit the top 200 rows query and get the records you’re interested in updating?  We can do this in just a few easy steps.

First open up the “Edit Top 200 Rows” window in SSMS for the table you want to edit.


Second, Right click at the top left of the table and from the drop down list select Pane > SQL.


Next, now we see a SQL statement that we can edit. Here I simply added the where clause WHERE MakeFlag = ‘TRUE’


Once the SQL has been updated it needs to be executed. Right click on the top left corner of the table again. This time select “Execute SQL”.


Below you can see the new result set available now for editing!


As always thanks for looking.

SQL Tips! Generate DDL Scripts with the data from the table. (Installment 2)

Welcome back to this series on SQL Tips. Sometimes I find myself needing to not only generate the DDL for a table but also the data that is stored in that table. For example imagine I have created and populated a few tables for a blog post. It would be much easier if I could share the table definition and the SQL statement to populate that table, Agree? Great!


In this scenario I am going to show you how to generate the DDL to create the DimCurrency table from the AdventureWorksDW2012 database. Along with the DDL script I will also show you how you can generate the script to populate the table.


    1. Open SSMS and connect to the server where your table is located at.
    2. Right click on the database where your table is stored, I’m using AdventureWorksDW2012.
    3. Next click on Tasks
    4. Next click Generate Scripts
    5. image
    6. Inside the Generate Scripts wizard click next on the introduction screen.
    7. For Choose Objects screen –> Click “Select specific database objects”.
    8. Expand the tables list.
    9. Select the DimCurrency Table.
    10. image
    11. On the “Scripting Options screen” I’m just going to load the script into a new query window. Please select “Save to new query window”. This option is found at the bottom of the screen.
    12. On the same screen select the “Advanced” button found on the right side.
    13. Now scroll down to “Types of data to script” and change it from schema only to Schema and Data.
    14. image
    15. Click Ok. Click Next, Click Next. The script will now be created and generated in a new query window. Click Finish once it has completed. Below is a partial screenshot of the completed product.
    16. image


As always, thanks for looking!

SQL Tips! Set your default database in SSMS (Installment 1)

In my time as a Business Intelligence consultant and trainer I have picked up some pretty cool tricks and thought I would start blogging on these tips two or three times a week as I catch myself using them.

In this blog I am going to show you how you can set your default database when you connect to a server. I find this to be an extremely helpful little tip. Please see below.


    1. Open SSMS and from the object explorer click “Connect”.
    2. From the Connect to Server prompt: Enter your server and Authentication information.
    3. Click “Options”, located at the bottom right.
    4. Next click the “Connection Properties” tab found at the top.
    5. Locate “Connect to Database” and then click the drop down menu.
    6. From the dropdown menu select <Browse> and then select the database that you want to be your default. Click Connect..
    7. Voila! Now every time you connect to that server it will default to that database instead of master!


As always, thanks for looking!