It’s CTE Month at Stedman Solutions

Hooray! It’s CTE month. Some of you may be happy but you others may by like ugh. Common table expressions can be tough and confusing but WE’VE GOT YOU COVERED!

This month we will be throwing lots of valuable CTE information and resources at you including…..

• Instructional videos
• Tips of the trade
• And a FREE download!

Check in daily to see what’s new.

But first here’s an very quick introduction to memory tables and CTEs

Transcription to this video:

So let’s get started with sort of an introduction to this concept of what I called memory tables, and common table expressions. Think of memory tables is sort of the concept of a derived table or a sub query, where what you have is not a table that you’re referencing, but you’re referencing another sub query inside of a query. And what that means is that SQL Server sort of treats that as the rest of the query like it’s a table. But it’s really not a table, it’s just a subset of data coming from a query.

We also have the concept of views, which is where you can define a query and store that off as something that can get referenced later. And with a view, you have to have special permissions to be able to create and alter the schema of your database. So when you create a view, anybody can use it. Well with the right permissions, of course, but you do have to have extra permissions to create that. And then when you reference a view, you reference it just like a table name. Now, temporary named result sets, there’s a couple of ways of doing this.

One is to use a temporary table. Another is to use table variables. And we can get into a whole other conversation on another day about the performance implications between those. But really, what these are is a way to grab some data out of the database and throw them into a temporary place so that they can be reused. Now a lot of people ask me well, is a common table expression or replacement for temp tables? Well, sometimes it’s a really great replacement for temp tables, or table variables. But it doesn’t. It doesn’t completely replace them. Think of them as different tools in your toolbox. And for instance, just because you have a hammer, it doesn’t necessarily mean that it’ll do the job of a screwdriver. And I think that CTEs and temp tables kind of work hand in hand really well together in the right scenario. Common table expressions are another way of kind of visualizing or representing the data that’s back from a result set in kind of a memory representation where you’re accessing and getting APA data from that query.

 

---

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!