WITH - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

WITH

Author

Topic

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-28 : 10:41:37

OK, I know this is part of CTE world, but could we include "WITH" in a Search.

In any case I need a 2k5 or 2k8 internals book

Is the results of with or a derived table stored in memory like local a local table variable or in temp db like a temp table?

quote:

There has been a problem!

Your search for ""WITH"" contained only ignored words and no member was specified

Go back to correct the problem.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-07-28 : 11:03:09

I think CTEs are folded into the query plan, not stored as distinct result sets.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-07-28 : 11:56:01

with behave the same as non indexed views. they are not stored anywhere. they're just a wrapper for the select statement.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-28 : 12:04:51

quote:

Using Common Table Expressions

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

http://msdn.microsoft.com/en-us/library/ms190766.aspx

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-07-28 : 12:09:33

What I said.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-07-28 : 12:10:36

it never says it's materialized anywhere. don't let the derived table part fool you. it's not stored in a temp table anywhere.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-28 : 13:39:47

tempdb or memory

declare @t table...

is stored in memory...or as much as it can

CREATE TABLE #t....

Is stored in tempdb

so.....

where is WITH Stored?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-28 : 14:00:26

@t table is stored in tempdb just like #table

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2009-07-28 : 14:05:48

(Moved to the Transact-SQL forum)

=================================================
Creating tomorrow's legacy systems today. One crisis at a time.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-07-28 : 14:12:34

with isn't stored anywhere. it's not a materized object. everytime you do select from a CTE the underlying tables are queried. just like a view.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-28 : 15:29:38

quote:
Originally posted by russell

@t table is stored in tempdb just like #table

Not that I recall

http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html

Also, doesn't a temp table force a recomplile?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-28 : 16:06:51

Not always.

N 56°04'39.26"
E 12°55'05.63"

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-28 : 23:02:56

quote:
Originally posted by X002548

quote:
Originally posted by russell

@t table is stored in tempdb just like #table

Not that I recall

http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html

Also, doesn't a temp table force a recomplile?

Brett

1. The information in that article is not entirely correct. The most notable difference between #temp table and @tmp table variable (performance-wise) is that there are no statistics on a table variable. In fact I had a discussion with Sunil Agarwal about this topic last year. Have a look at his blog on this subject here: http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/30/sql-server-table-variable-vs-local-temporary-table.aspx

You can prove this for yourself by measuring the # of pages allocated to tempdb before and after creating (and populating) a table variable

2. Not necessarily.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-28 : 23:13:01

It is worthwhile at this point to remember that with few exceptions EVERYTHING happens in memory. Only when SQL Server decides to perform a physical write (and there is nothing preventing it from doing so) is anything persisted to disk.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources