Common Table Expressions (CTE) in SQL Server

Introduction

“Tune queries with this one simple trick: TRUNCATE TABLE”. 

Did I get your attention? Good!

Common Table Expressions or CTE as we will refer to them going forward are a useful tool to quickly create "views" of data inside SQL Server and are an excellent tools for making your queries easier to read and in most cases perform a bit faster. We are going to look at them from a very basic standpoint today but I can promise you we will see them again soon.

The What

Added to SQL server in 2005, a CTE is a view of data you can create on the fly for a single query to work with. There are a few rules that have to be followed in SQL server that will seem quirky at first but there are reasons for each.

• The CTE statement should begin with a semicolon. Any commands before a CTE must be terminated with a semicolon. If your CTE is the first thing in the batch it doesn't have to have it but if someone comes along and adds something in from of it, the whole batch will not work. This is just good practice. Example ";WITH [My_CTE] AS"... 
• CTE follow all rules that govern the creation of views. Remember I told you they were a dynamic view? Stands to reason!
• CTE can be recursive.
• SQL has a maximum recursion value of 100. If you need to go over this amount, you must use the MAXRECURSION hint.

The basic structure of a CTE statement is:
[WITH <common_table_expression> [,...]]

<common_table_expression>::=

cte_name [(column_name [,...])]

AS (cte_query)

The How

Here are some basic examples of CTE operations.
This is an example of a basic CTE that filters out some values from a table before querying it.

-- Find new employees
;WITH [CTE]([EmployeeName], [EmployeeNumber], [HireDate]) AS 
( SELECT TOP 100
 [EmployeeName], 
 [Number], 
 [DateofHire]
  FROM [dbo].[tblEmployees] WITH(NOLOCK)
  WHERE [DateofHire] > '01/01/2005'
  )
SELECT * FROM [CTE]
WHERE [EmployeeName] LIKE 'a%'

This is an example of a recursive CTE. It is very basic and counts to 100. Change the where clause to 1002 and see what happens when you run it.

;WITH [CTE]([Number]) AS 
( SELECT 1
    UNION ALL
      SELECT [Number]+1 FROM CTE WHERE [Number] < 100 
  )
SELECT [Number] FROM [CTE]
OPTION (MAXRECURSION 1000)  

This is an example of a multiple table CTE where we selected our senior employees and our new employees from the same table and output a list of the new employees and which senior employee is assigned as their mentor.

;WITH [NEWBZ]([EmployeeName], [EmployeeNumber], [HireDate], [Mentor]) AS 
( SELECT 
 ([FirstName] + ' ' + [LastName]), 
 [EmployeeNumber], 
 [DateofHire],
 [MentorID]
  FROM [dbo].[Employees] WITH(NOLOCK)
  WHERE [DateofHire] > '01/01/2005' 
),
[OldSalt]([ID], [EmployeeName], [EmployeeNumber], [HireDate]) AS 
( SELECT 
 [ID],
 ([FirstName] + ' ' +  [LastName]), 
 [EmployeeNumber], 
 [DateofHire]
  FROM [dbo].[Employees] WITH(NOLOCK)
  WHERE [DateofHire] < '01/01/2005'
)
SELECT
 [NEWBZ].[EmployeeName] AS [Newb],
 ISNULL([OldSalt].[EmployeeName], 'None') AS [Mentor]
FROM [NEWBZ]
LEFT JOIN [OldSalt] ON [NEWBZ].[Mentor]=[OldSalt].[ID]

Conclusion

As you can see, CTE can be useful for filtering your data before joining it, minimizing the amount of data you are going to be dealing with or just as a means of making a query easier to read and understand. Think of ways you can limit your WHERE clauses and put them to use. If you have a specific situation you would like example use on, leave a comment on it.

Quality

Quality is a topic that is near and dear to our hearts.  The higher quality software we release into the wild, the fewer issues the customers encounter, and the money keeps flowing smoothly :).  Sometimes the quality of software isn't quite there, for this particular blog I'll discuss two different types of quality control concerns brought to light from the film industry.  Sorry peeps, no sample source code necessary today.

WarGames:

Maybe you've seen the movie WarGames, maybe you haven't.  It's a geek classic from the early 1980s starring a not quite Ferris Bueller Matthew Broderick.  The premise is hacker boy (Matthew Broderick) breaks into a software company via modem (yes they existed) to play the latest games without having to wait for their release.  Long story short: a computer at NORAD tries to launch the ICBMs at Russia during the Cold War.  By bad coding they said the number of players was 0 and it crashed itself by eating up all of the cpu cycles.

From a corporate coder point of view:  the number of players less than 1 is acceptable?  Are you serious?  I can see 1 player allowed (against the CPU) but apparently the government underwriter let one slip by to save the United States from Global Thermonuclear War.

Office Space:

Yet another geek classic you need to see, sometimes that movie hits a little too close to home.  A quick synopsis: coders working for a company called Initech get laid off so they decide to pull a Superman III (yes, the Richard Pryor one) and steal the idea of nibbling money away from each transaction to such a micro-transaction amount they'd never get caught.  Problem:  bad division made the money flow too fast and before you know it the scheme blows up in their faces.

From a corporate coder point of view:  No pre-testing your code to see the end result math against some garbage data wouldn't show that off?  Doing a little pre-testing early on can save some headaches down the line (and potentially have Jennifer Aniston putting the lotion on your back while getting a tan versus having Diedrich Bader (Lawrence) yelling out for you to switch over to channel 9).

Terminator 2: Judgement Day:

The synopsis: robot re-programmed and sent back in time to save the younger version of the guy that re-programmed the robot so he could help save the human race.  Robots were well acted by Arnold Schwarzenegger and Robert Patrick (unfortunately no Bill Paxton).  Arnold saves the day!

From a corporate coder point of view:  Cyberdyne could create high quality functional robots (to help humanity) that end up taking over the world in the future but not do enough QA for the robot to have enough self-defense on its own part to keep from getting modified/updated by an unauthorized user?  I'll give the robots a bit of credit for becoming self aware and attempting to remove the human element that'd keep them from existing as they thought they should (excellent AI techniques I wouldn't mind researching/coding one day).

Independence Day:

Buyer beware on this film: it made Will Smith a superstar and has Randy Quaid as a crazier Cousin Eddie (is that possible?) than what he portrayed in the Vacation series.  The premise: aliens have come from space to take over Earth and it's up to a hot shot pilot (Will Smith) along with a tech guy (aptly played by Jeff Goldblum) to try to save the human race.  The solution:  inject a virus from a Macintosh (gee thanks mid-90s hollywood) into the mother ship and basically render all of the spaceship's defenses useless.

From a corporate coder point of view:  I'll forgo the mac jokes, but with an advanced species that is capable of interstellar travel you'd think they'd have programs running to detect/deny code injection from an external source.  If they can pilot craft as large as a city then they should be able to engineer some software to keep a high dollar mid-90s Macintosh at bay.

Conclusion(s):

Write better code people!  Pre-test (aka run it more than once)!  Try to bullet-proof as much as possible as well as a little bit of engineering to have lower hassle code down the line.  Maybe one day you too can inadvertently take down humankind or get lotion applied on a beach by Jennifer Aniston  :).

And don't forget to thank your local Quality Assurance specialist for the long suffering job they have as they work with YOUR code :).

Source:

Independence Day

WarGames
Terminator 2: Judgement Day
Office Space

.Net OData Services

Intro

I've talked about Web API in previous articles, and I think my overall opinion is pretty clear: I really like the direction MS has taken with internet-based services. I find Web API to be a big improvement over SOAP services and WCF. They're simpler and more flexible, and I find them to be just plain fun. However there is a more powerful alternative out there that is still quite simple: OData. OData is an "open protocol to allow the creation and consumption of queryable and interoperable RESTful APIs in a simple and standard way." For .Net, OData is supported in Web APIs with just a few minor tweaks to your code. So in essence you get queryable RESTful APIs with very little effort on your part. OK everybody, time to show me your OData face and let's get crackin!

Howdie Doodie

Time to talk specifics. Pretend you have a list of animals. You want a RESTful service that allows the client to query your list of animals by name. OK cool, sounds easy enough. You might create a Web API that has a GET method with a single parameter, name. The user hits your api, maybe with the url /api/animals/timmy, and this would return Timmy the lion (we'll just assume all your animals have unique names). Now what if the client wants to retrieve a list of mammals? You could always create a new GET method in your API that has a single parameter for class and the client would hit /api/animals/mammal, but now you've got 2 conflicting API methods and they each need their own special path. Plus you're making some duplicated code here, as in essence you're still just querying your list of animals for specific animal(s). OData gives you an easier out. Let's create an OData service that allows querying as per the previous desires.

First, fire up Visual Studio. I'm using VS 2013 here. Create a new empty Web API project.

 

Now add a class to your Models folder. Name the class Animal.

Make your class look like this:

using System.ComponentModel.DataAnnotations;
namespace BlogOData.Models
{
    public class Animal
    {
        [Key]
        public string Name { get; set; }
        public string Type { get; set; }
        public string Class { get; set; }
        public int Height { get; set; }
        public int Weight { get; set; }
    }
}

Note: The Key attribute is merely used to identify which is the unique property of our class that identifies individual instances of an animal. In the real world names aren't unique, but this is a blog not the real world.

Now it's time for our OData controller. Go ahead and add one named Animals to your Controllers directory as per the following screenshots:

You've now got a rather large file named AnimalsController.cs in your Controllers folder. It has a lot of extra actions that we don't need since all we care about is retrieving an animal or list of animals based on criteria specified by the client. Replace the code of your new controller with the following:

using System.Collections.Generic;
using System.Linq;
using System.Web.Http.OData;
using BlogOData.Models;

namespace BlogOData.Controllers
{
    public class AnimalsController : ODataController
    {
        public List<Animal> Animals;

        public AnimalsController()
        {
            Animals = new List<Animal>();
            Animals.Add(new Animal() { Class = "mammal", Type = "lion", Name = "timmy", Height = 64, Weight = 495 });
            Animals.Add(new Animal() { Class = "reptile", Type = "box turtle", Name = "billy", Height = 4, Weight = 2 });
            Animals.Add(new Animal() { Class = "reptile", Type = "leopard gecko", Name = "marzipan", Height = 1, Weight = 1 });
            Animals.Add(new Animal() { Class = "invertebrate", Type = "worm", Name = "willy", Height = 1, Weight = 1 });
            Animals.Add(new Animal() { Class = "mammal", Type = "house cat", Name = "sushi", Height = 1, Weight = 12 });
        }

        [EnableQuery]
        public IQueryable<Animal> Get()
        {
            return Animals.AsQueryable();
        }
    }
}

The first thing that is noteworthy is your class declaration for AnimalsController. We're descending from ODataController. Next in line, our constructor. It's not really all that special, but you can see we're populating a list of animals. Lastly the method Get. This has a few interesting bits:

1. The EnableQuery attribute. This tells the runtime that we are returning a result from this method that is queryable using OData. 
2. The return type is IQueryable<Animal>. This lets the result set be queried automatically via the OData backend.
3. Lastly, we return our list of Animals as a queryable object. 

With this incredibly small amount of code, we've now created an OData controller that lets clients tell the server to return only specific results.

And now is where I tell you there's one more piece left. As things stand at the moment, there is no route to your OData controller, which means clients can't actually call it. Open up your WebApiConfig.cs file in the App_Start folder. Looking at the below code sample, you'll need to add the 3 lines of code underneath the comment "//OData Routes":

using System.Web.Http;
using System.Web.Http.OData.Builder;
using System.Web.Http.OData.Extensions;
using BlogOData.Models;

namespace BlogOData
{
    public static class WebApiConfig
    {
        public static void Register(HttpConfiguration config)
        {
            // Web API configuration and services

            // Web API routes
            config.MapHttpAttributeRoutes();

            config.Routes.MapHttpRoute(
                name: "DefaultApi",
                routeTemplate: "api/{controller}/{id}",
                defaults: new { id = RouteParameter.Optional }
            );

            //OData Routes
            ODataConventionModelBuilder builder = new ODataConventionModelBuilder();
            builder.EntitySet<Animal>("animals");
            config.Routes.MapODataServiceRoute("odata", "odata", builder.GetEdmModel());

        }
    }
}

These 3 lines tell the runtime that we wish people to access our OData controller via the url /odata/animals (case-sensitive). That's it. Run this little sucker! When your browser first comes up, you'll probably see a 403 forbidden error like the following:

Not to worry though; that's not really the url you want. If you're debugging in IE, go ahead and open up a firefox window. It's easier to demo Web API and OData functionality with than IE. Leave the IE debugging window open though. Navigate to your localhost url plus "/odata/animals". You should now see this:

Cool! This is the JSON response from our OData controller showing the full list of animals. I hope you're now thinking "Geez Peeticus, that's cool! But hey, didn't you say the client could tell the server, via a query of some sort, which animals to return?". Well yes I did, and yes we can. OData has it's own special syntax that we can use for filtering, delivered via the $filter querystring parameter. Let's go ahead and use a basic query now...add this on to the url you already have in FireFox: "?$filter=Name eq 'timmy'" (note these filters are also case-sensitive, as are string values contained therein). Here's what your result looks like:

Try a few more queries. Maybe we want to see all the mammals with "?$filter=Class eq 'mammal'".

Holy poo this is cool! We didn't have to write a bunch of switch statements with dynamic querying, LINQ statements, or anything of the sort. All this filtering is handled automagically!

What's Next?

You have a lot more query options with OData than what I've shown you here today. Play around, see what all else you can do, and check out the OData website for more information. It's powerful stuff. You might also try creating a C# client to consume your OData service. Info on how to do that can be found here.

Resources

OData Home
Create an OData v4 Endpoint Using ASP.Net Web API 2.2

Ninject

Purpose:

Ever read about dependency injection and wonder: Hey, how can I implement that easily into my .Net project yet still not pay a fortune to do it?  Open Source is your friend in this instance.

Download + the install:

It's as simple as going to the source link below and clicking their download icon.  They offer framework dependencies for 3.5 and newer, i'll go with 4.0 for my purposes.  Adding the reference to your program is fairly straight forward like any other reference you've added in the past.

Code:

Lets write some dependency injection code, for my purposes I'll use Bugs and Elmer.  Bugs and Elmer have interesting interactions, lets c#ify a conversation:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Ninject;
using Ninject.Modules;

namespace NinjectTutor
{
  interface IVegetable
  {
    void Chomp(string target);
  }

  class Carrot : IVegetable
  {
    public void Chomp(string target)
    {
      Console.WriteLine("{0} What's up doc?", target);
    }
  }

  interface IWeapon
  {
    void Hit(string target);
  }

  class Shotgun : IWeapon
  {
    public void Hit(string target)
    {
      Console.WriteLine("{0} huh huh huh huh", target);
    }
  }

  class Fudd
  {
    readonly IWeapon weapon;
    public Fudd(IWeapon weapon)
    {
      this.weapon = weapon;
    }

    public void Attack(string target)
    {
      this.weapon.Hit(target);
    }
  }

  class Wabbit
  {
    readonly IVegetable veggie;
    public Wabbit(IVegetable veggie)
    {
      this.veggie = veggie;
    }

    public void Harass(string target)
    {
      this.veggie.Chomp(target);
    }
  }

  class Program
  {
    static void Main(string[] args)
    {
      Ninject.IKernel kernel = new StandardKernel();
      
      kernel.Bind<IVegetable>().To<Carrot>();
      var bugs = kernel.Get<Wabbit>();
      bugs.Harass("Eh");

      kernel.Bind<IWeapon>().To<Shotgun>();
      var elmer = kernel.Get<Fudd>();
      elmer.Attack("kill da wabbit! kill da wabbit!");

      Console.WriteLine("\nPress Any Key to Exit.");
      Console.ReadKey();
    }

  }

}

and the result:

Source(s):

ninject homepage
dependency injection
demistify injection code

Prologue:

I easily burnt an hour trying to do the download through NuGet to do the install of Ninject into VS2013 express.  If anyone can solve it please post below to help others.  My best current guess is user rights related.

.Net Web API Compression

Intro

Web API projects are great. But hopefully I've already convinced you of that in past blogs :)  What could make them better? Optimization of course! As you can guess by the title, I'm specifically going to talk about compression. Compression has a dual-purpose: it can optimize bandwidth, and as a result on many devices it optimizes speed as well from the user's point of view. Out of the box, your Web API's don't accept compressed requests and won't send back compressed responses. Lazy API! But we've got an easy way to make this happen, so read on fellow optimizers!

Example

First, let's create a Web API project in C#. We'll go without web optimization for now just so you can see the difference. Fire up Visual Studio and create a new project. Make it a c# web project.

Make it a empty project and give it Web API and Web Forms capabilities:

 Now give your site a new web API controller. Name it TestController. 

Your Web API doesn't need much in it, just set your code to this:

using System.Net.Http;
using System.Web.Http;

namespace BlogWebApiCompress.Controllers
{
    public class TestController : ApiController
    {
        public HttpResponseMessage Get(string id)
        {
            var result = new List();
            for (int i = 0; i < 10; i++)
                result.Add(id);
            return Request.CreateResponse(result);
        }
    }
}

If you run your web api by hitting your base url plus "/api/test/hi", the web api will return back the list of strings string containing "hi" (10 times) to you. Not much to that. Now let's pretend you sent up a 100 page pamphlet worth of text. You'd have not only a large request, you'd have a very large response as well. Compression can lessen the burden on your systems.

How do we get compression? It turns out there's already a NuGet package for that. NuGet kind of reminds me when iPhones were still pretty new and people kept saying "there's an app for that". Well with Visual Studio and NuGet, "there's a package for that". The one we want is named Microsoft.AspNet.WebApi.MessageHandlers.Compression. Good luck memorizing that. I'll assume you have some familiarity with NuGet by now, but if not drop me a line in the comments below. Fire up the package manager and install the above package.

Now open up the file App_Start/WebApiConfig.cs. At the end of your Register() method, copy in this line of code:

GlobalConfiguration.Configuration.MessageHandlers.Insert(0, new ServerCompressionHandler(new GZipCompressor(), new DeflateCompressor()));

You may also have to add "using"s for these:

using Microsoft.AspNet.WebApi.MessageHandlers.Compression;
using Microsoft.AspNet.WebApi.MessageHandlers.Compression.Compressors;

Believe it or not, that's all you have to do on the server! Let's do another test of our Web Api using FireFox to see the compression in action. This time I'll put a much longer string into the request:

Notice how FireFox sent up the request header "Accept-Encoding: gzip, deflate" and the server sent back down gzip-encoded content? Sweet! That's all there is to it.

What's Next?

• Go to the website of the NuGet package and see how to:
• Only compress requests and responses that are above a certain size threshold, and
• Write client-side code that tells the server you can accept a compressed response (hint, most browsers do this for you already if you are using JavaScript, but for C# clients you have a small amount of extra coding to do).

Resources

 Microsoft.AspNet.WebApi.MessageHandlers.Compression