SQL server

Common Table Expressions (CTE).

Common Table Expressions was just what I was looking for where I was needed to do some nested querying.  No derived tables required, no table variables required, and no mess. CTE’s are more volatile, and last only as long as the query does.  They can be used for DML commands and in views and SPs. An example of a CTE is:

with ctetest
select name,log_reuse_wait_desc from sys.databases
where name=’the_dbname’
select * from ctetest

The above query works more like a view, but I did not have to create anything previously; any data structure created is also destroyed at the end of the query. You can create more complex queries and even nest them. I am working on a query on finding physical db and logical db info in one query. CTEs might give me the perfect scratchpad to store DMV info which can be joined with other queries to get the desired info.


Thinking about someting? Leave a Reply...

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s