Using a Common Table Expression Instead of a Derived Table

Here is a short video that shows how to convert those ugly derived table (SubQuery) queries into a common table expression to help clean up your TSQL code. This applies to Common Table Expressions on Microsoft SQL Server, and the recording comes from my Free SQL Query Training course.

 

 

Other CTE Related Posts:

What is a Common Table Expression

I just a added a video to YouTube titled “What is a Common Table Expression”.  This video covers the extreme basics of what a CTE is on Microsoft SQL Server, and how to write that CTE in TSQL. Here is the video:


It’s a very short video with the basics of what a CTE is.  Take a look.

 

Other CTE Related Posts:

Database Health Reports Beta 9 – Released

Today I have released beta 9 of the Database Health Monitor.

It has been 27 months since the release of Database Health Beta 1, and almost 4 years since the original SSRS implementation of Database Health reports.

To be honest for the first half of 2014, I didn’t contribute much to the Database Health Reports, then in the couple of months, I realized just how many people were really using Database Health Reports, with over 15oo active installs world wide. This changed my outlook on database health reports and I decided to buckle down and focus on the project again.

Beta9

Beta 9 includes the following new features and bug fixes.

New Features

  • Added new real time report for Active Queries. This report shows the queries that are currently running (or blocked and waiting) on the current database. This was based on a customer request.
  • Right click menu option in the database tree view to sort the list of databases by either the database Id or alphabetically by name. This was based on a customer request.
  • New real time report to show database files with IO and stalls for database files and logs.

Bug Fixes

  • Improved the column formatting on the Identity Column Usage report. In some cases the columns were sized too small to read the column titles. This has been fixed.
  • Broken links have been fixed. There are a number of help links in the system that were linking to http://databasehealth.stevestedman.com, these have been fixed to link to http://databasehealth.com.
  • Fixed crash caused by attempting to connect to a SQL Server that no longer exists.

With Beta 9 there has been more extensive testing with SQL Server 2014 to confirm that all the reports work correctly, so far there have been no problems with SQL server 2014.

Another infrastructure change with Beta 9 is that I migrated the source control system from Subversion and switched to Git. This has allowed for much easier branching and the addition of new code into the system.

 

If you are using Database Health Beta 8, I recommend you update to the latest free beta version at http://databaseHealth.com/download.

Introducing the Joes2Pros Academy

The new Joes2Pros Academy has just launched. The Academy provides an interactive classroom experience that can be accessed on your own time as you need it. This site has high quality content, quizzes and exams.

Students can interact with other students and instructors to get the best experience out of the Joes2Pros Academy.

Courses offered in the following areas:

  • SQL Server Administration
  • SQL Server Developer
  • SQL Server BI
  • SSRS
  • AlwaysOn
  • C#
  • and more

The Joes2Pros Academy helps you take the journey from average Joe to technology Pro.

As an instructor in the Joes2Pros Academy I have been able to learn a great deal from the other instructors.  

What is a Common Table Expression – Video

 

Related Common Table Expression Links

You can also find more CTE examples on the TSQL Wiki.

If you enjoyed these Common Table Expression posting, and want to learn more about Common Table Expressions, please take a look at my book on CTE’s at Amazon.com. The book is titled Common Table Expressions – Joes 2 Pros® – A CTE Tutorial on Performance, Stored Procedures, Recursion, Nesting and the use of Multiple CTEs.

One Thousandth Download of Database Health Reports

Today marks the 1000th download of the Database Health Reports in the last 11 months since Beta 1 was released. I thought this would be the perfect opportunity to recap on the history of the database health report application.

First I would like to thank everyone who has downloaded Database Health Reports application.

Thank You!

Here is a quick history, before it existed as an installable application, Database Health Reports started out as a set of interlinked SSRS reports (Version 1.0).

Database Health Reports 1.0 – Originally a set of SQL Server Report Server reports.

  • February to October 2011 – Initial planning and development – determining what queries would be useful in the Database Health Reports.
  • November 19, 2011 Database Health Reports (SSRS) initial release.
  • November 2011 to March 2012 – Adding more reports, and improving the overall interaction.
  • April 2012 – The realization that the install process and distribution of the SSRS reports was going to be too difficult to support. At this point I started working on version 2.0 as an application rather than a set of SSRS reports.

Database Health Reports 2.0 – Application – Beta towards version 2.0

  • April 2012 to August 2012 – Preliminary development of the Beta 1 release.
  • • September 1, 2012 – Beta 1 Release – Just getting the initial product out the door. This was a scary release, wondering if anyone would download it, or if anyone would like it.
  • • September 30, 2012 – Beta 2 Release – Added a few more reports and advisors, including the Unclustered tables report.
  • • October 2012 – Major redesign and addition of the historic wait monitoring.
  • • November 18, 2012 – Beta 3 Release. The big new feature was historic wait monitoring.
  • • November 21, 2012 – Beta 3.1 release. There were some problems with Beta 3 that needed to be addressed quickly. This one was a bit embarrassing, but I learned a lot about the release cycle.
  • • January 10, 2013 – Beta 4 Release. Including a rewrite of the setup/install program to make the program easier to get installed. Removed support for SQL Server 2000, it never actually worked, but people didn’t know.
  • • March 3, 2013 – Beta 5 Release Added the Big Clustered Indexes Report, along with various bug fixing. Beta 5 was the first beta where the timeout period was extended from 2 months to 4 months.
  • • June 3, 2013 – Beta 6 Release – I considered this the general cleanup beta, fixing things should have been done in the beginning, better clipboard support to copy for all the grids, sorting on all the grids, additional setting to configure the application behavior, and many bug fixes.
  • • July 2013 – New website. The hosting company where I had the DatabaseHealth.SteveStedman.com website hosted was not performing well. Pages were very slow to respond, and there were a couple of outages. I moved the website to a new hosting company which is must faster, and has better availability. The new website is http://DatabaseHealth.com

All of these Betas have been building up to the Version 2 release, which is still a few more Betas away.

I often get questions about the timeout period in the Database Health Reports. People ask the question of “its free, but it expires, what gives?” Part of the beta process and giving it away for free is that I don’t have a huge budget for a support team to diagnose what might be going wrong on an older version of the beta, or what might have been a bug 8 months ago, that is no longer a bug in the current version. To mitigate this I ask that everyone using the product upgrades as newer versions come out. Now once a new beta version is release it is about every 3 months, and it the old version will usually run for about 2 months once it is expired. This just keeps you, the customer from running old code with bugs. The application has come a long way in the last year, and it will continue to evolve.
Database Health Reports currently supports SQL Server 2005 to SQL Server 2012, Beta 7 will add support for SQL Server 2014 CTP1.
Beta 7 is about 30 days away from release. There are a few bug fixes and minor enhancements in Beta7, but the big new feature is the addition of technical debt analysis.

Technical Debt Analysis – coming soon

Technical Debt is a programming concept that is also known as code debt, it applies to any type of code that is not written to standards. The debt is looked at as the cost to bring the code up to standards. As part of my recent Scrum Master certification course (yes I am a certified scrum master now) the concept of technical debt came up. Technical debt is something that I have been involved with arguing with between developers and DBAs on many occasions, but until the Scrum Master certification course, the concept of technical debt never really stuck for me. In the past when technical debt was argued, it was often times used to write off those areas of code that developers and DBAs just don’t enjoy working on, but there is more to it than that. Analyzing technical debt is like getting a report card on the work you do, or on the systems you have to maintain.
The concept of Technical Debt applies closely with Agile software development, but also applies to database development. There are many tools available to report on technical debt for C#, PHP or other programming languages, but there isn’t much available to manage the T-SQL technical debt or general database design technical debt on SQL Server.
Some of the traditional technical debt causes are:

  • Poor design.
  • Lack of coding standards.
  • Different conflicting coding standards over time.
  • A rush to get things done rather than focusing on stability and sustainability.
  • Non refactoring code as it grows.
  • Evolving code or environments to just make things work, rather than to make things work well.
  • many more…

Reducing technical debt will lead to a more sustainable environment.

Beta7 will add a new report which analyzes your technical debt and provides the areas needing the most improvement. Here is a preview of the technical debt report.

TechDebtPreviewClick image to view the full resolution.

Coming Soon – September 2013.

Any of these technical debt items can be overridden if you have a coding standard that supports something else. For instance if you don’t agree with the coding standard that keywords should be uppercase, thats OK you can just turn off that technical debt check.

What Is Coming In the Next Year

My plan is for another Beta ever 60 to 90 days, with a focus first on stability and bug fixing. The second focus will be on adding functionality into the existing features, for instance more details on the historic monitoring, and more checks on the technical debt feature. Then the focus will be on adding new features.
If you like what you have seen in the last 11 months, stick around the next year should be just as feature rich as the first.

Thank You!

Again, thanks to everyone who has been using the Database Health Reports, thanks to all those who reported problems, and have been patient enough to work with me on fixing bugs. Thanks to everyone who was spread the word of the Database Health Reports to help grow the user base.
Thanks you!

-Steve Stedman
Founder, Developer, Tester and Support Technician – Database Health