From: Jay on
Wait till you get a load of CTE's and Pivots

"Jennifer Mathews" <waltersjennifer(a)live.com> wrote in message
news:OnzNxoCrKHA.5940(a)TK2MSFTNGP02.phx.gbl...
> THANKS to everyone!!! Wow!!! The power of SQL is just amazing. I wish I
> had known this awhile ago instead of using temp tables.
>
> Thanks again.
>
>
> "Jennifer Mathews" <waltersjennifer(a)live.com> wrote in message
> news:uIyys89qKHA.6064(a)TK2MSFTNGP02.phx.gbl...
>>I have been trying to redo some of my SQL without temp tables and Loops so
>>here is another question I have not been able to figure out.
>>
>> Table Bk_Reviews
>> Person_ID int
>> Book_ID int Book_Comment varchar(2000)
>> Person_ID Book_ID Book_Comment
>> 100 50 whatever1
>> 100 60 whatever2
>> 105 70 whatever3
>> 105 80 whatever4 << same book
>> 105 80 whatever5 << same book
>>
>> To get the total number of comments by a person I use:
>>
>> DECLARE @cnt int SELECT @cnt = COUNT(*) FROM Bk_Reviews WHERE Person_ID
>> = 105 RETURNS 3 for Person_ID=105
>>
>> Most people only comment once for a book but people can have multiple
>> comments per book such as Person_ID=105 \ Book_ID=80
>>
>> I am currently using a temp table to count the number of Total UNIQUE
>> comments excluding duplicates per book.
>>
>> SELECT DISTINCT Book_ID INTO #tmp_Person2Book
>> FROM Bk_Reviews WHERE Person_ID = 105
>> SELECT @cnt = COUNT(*) FROM #tmp_Person2Book RETURNS 2 for Person_ID=105
>>
>> Person_ID Book_ID 105 70 105 80 << doesn't
>> matter that has two comments
>>
>> Is there anyway I can do this in a single SELECT statement instead of
>> using the temp table tmp_Person2Book?
>> Thanks
>>
>


From: Jennifer Mathews on
I just did a CTE and it is just tremendous. The code is so much more readable and
actually easier to write. Thanks.

"Jay" <spam(a)nospam.org> wrote in message news:%23MwyyCDrKHA.6064(a)TK2MSFTNGP02.phx.gbl...
> Wait till you get a load of CTE's and Pivots
>
> "Jennifer Mathews" <waltersjennifer(a)live.com> wrote in message
> news:OnzNxoCrKHA.5940(a)TK2MSFTNGP02.phx.gbl...
>> THANKS to everyone!!! Wow!!! The power of SQL is just amazing. I wish I had known
>> this awhile ago instead of using temp tables.
>>
>> Thanks again.
>>
>>
>> "Jennifer Mathews" <waltersjennifer(a)live.com> wrote in message
>> news:uIyys89qKHA.6064(a)TK2MSFTNGP02.phx.gbl...
>>>I have been trying to redo some of my SQL without temp tables and Loops so here is
>>>another question I have not been able to figure out.
>>>
>>> Table Bk_Reviews
>>> Person_ID int
>>> Book_ID int Book_Comment varchar(2000)
>>> Person_ID Book_ID Book_Comment
>>> 100 50 whatever1
>>> 100 60 whatever2
>>> 105 70 whatever3
>>> 105 80 whatever4 << same book
>>> 105 80 whatever5 << same book
>>>
>>> To get the total number of comments by a person I use:
>>>
>>> DECLARE @cnt int SELECT @cnt = COUNT(*) FROM Bk_Reviews WHERE Person_ID = 105
>>> RETURNS 3 for Person_ID=105
>>>
>>> Most people only comment once for a book but people can have multiple comments per
>>> book such as Person_ID=105 \ Book_ID=80
>>>
>>> I am currently using a temp table to count the number of Total UNIQUE comments
>>> excluding duplicates per book.
>>>
>>> SELECT DISTINCT Book_ID INTO #tmp_Person2Book
>>> FROM Bk_Reviews WHERE Person_ID = 105
>>> SELECT @cnt = COUNT(*) FROM #tmp_Person2Book RETURNS 2 for Person_ID=105
>>>
>>> Person_ID Book_ID 105 70 105 80 << doesn't matter that
>>> has two comments
>>>
>>> Is there anyway I can do this in a single SELECT statement instead of using the temp
>>> table tmp_Person2Book?
>>> Thanks
>>>
>>
>
>

From: Jennifer Mathews on
But I have a question. Is a CTE just another name for a temp table? Is it creating a
physical temp table or it it a memory temp table?

"Jay" <spam(a)nospam.org> wrote in message news:%23MwyyCDrKHA.6064(a)TK2MSFTNGP02.phx.gbl...
> Wait till you get a load of CTE's and Pivots
>
> "Jennifer Mathews" <waltersjennifer(a)live.com> wrote in message
> news:OnzNxoCrKHA.5940(a)TK2MSFTNGP02.phx.gbl...
>> THANKS to everyone!!! Wow!!! The power of SQL is just amazing. I wish I had known
>> this awhile ago instead of using temp tables.
>>
>> Thanks again.
>>
>>
>> "Jennifer Mathews" <waltersjennifer(a)live.com> wrote in message
>> news:uIyys89qKHA.6064(a)TK2MSFTNGP02.phx.gbl...
>>>I have been trying to redo some of my SQL without temp tables and Loops so here is
>>>another question I have not been able to figure out.
>>>
>>> Table Bk_Reviews
>>> Person_ID int
>>> Book_ID int Book_Comment varchar(2000)
>>> Person_ID Book_ID Book_Comment
>>> 100 50 whatever1
>>> 100 60 whatever2
>>> 105 70 whatever3
>>> 105 80 whatever4 << same book
>>> 105 80 whatever5 << same book
>>>
>>> To get the total number of comments by a person I use:
>>>
>>> DECLARE @cnt int SELECT @cnt = COUNT(*) FROM Bk_Reviews WHERE Person_ID = 105
>>> RETURNS 3 for Person_ID=105
>>>
>>> Most people only comment once for a book but people can have multiple comments per
>>> book such as Person_ID=105 \ Book_ID=80
>>>
>>> I am currently using a temp table to count the number of Total UNIQUE comments
>>> excluding duplicates per book.
>>>
>>> SELECT DISTINCT Book_ID INTO #tmp_Person2Book
>>> FROM Bk_Reviews WHERE Person_ID = 105
>>> SELECT @cnt = COUNT(*) FROM #tmp_Person2Book RETURNS 2 for Person_ID=105
>>>
>>> Person_ID Book_ID 105 70 105 80 << doesn't matter that
>>> has two comments
>>>
>>> Is there anyway I can do this in a single SELECT statement instead of using the temp
>>> table tmp_Person2Book?
>>> Thanks
>>>
>>
>
>

From: TheSQLGuru on
Be careful with CTEs. They are sometimes NOT helpful for performance.


--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Jennifer Mathews" <waltersjennifer(a)live.com> wrote in message
news:%23WlVoVOrKHA.3800(a)TK2MSFTNGP06.phx.gbl...
> But I have a question. Is a CTE just another name for a temp table? Is
> it creating a physical temp table or it it a memory temp table?
>
> "Jay" <spam(a)nospam.org> wrote in message
> news:%23MwyyCDrKHA.6064(a)TK2MSFTNGP02.phx.gbl...
>> Wait till you get a load of CTE's and Pivots
>>
>> "Jennifer Mathews" <waltersjennifer(a)live.com> wrote in message
>> news:OnzNxoCrKHA.5940(a)TK2MSFTNGP02.phx.gbl...
>>> THANKS to everyone!!! Wow!!! The power of SQL is just amazing. I wish
>>> I had known this awhile ago instead of using temp tables.
>>>
>>> Thanks again.
>>>
>>>
>>> "Jennifer Mathews" <waltersjennifer(a)live.com> wrote in message
>>> news:uIyys89qKHA.6064(a)TK2MSFTNGP02.phx.gbl...
>>>>I have been trying to redo some of my SQL without temp tables and Loops
>>>>so here is another question I have not been able to figure out.
>>>>
>>>> Table Bk_Reviews
>>>> Person_ID int
>>>> Book_ID int Book_Comment varchar(2000)
>>>> Person_ID Book_ID Book_Comment
>>>> 100 50 whatever1
>>>> 100 60 whatever2
>>>> 105 70 whatever3
>>>> 105 80 whatever4 << same book
>>>> 105 80 whatever5 << same book
>>>>
>>>> To get the total number of comments by a person I use:
>>>>
>>>> DECLARE @cnt int SELECT @cnt = COUNT(*) FROM Bk_Reviews WHERE
>>>> Person_ID = 105 RETURNS 3 for Person_ID=105
>>>>
>>>> Most people only comment once for a book but people can have multiple
>>>> comments per book such as Person_ID=105 \ Book_ID=80
>>>>
>>>> I am currently using a temp table to count the number of Total UNIQUE
>>>> comments excluding duplicates per book.
>>>>
>>>> SELECT DISTINCT Book_ID INTO #tmp_Person2Book
>>>> FROM Bk_Reviews WHERE Person_ID = 105
>>>> SELECT @cnt = COUNT(*) FROM #tmp_Person2Book RETURNS 2 for
>>>> Person_ID=105
>>>>
>>>> Person_ID Book_ID 105 70 105 80 << doesn't
>>>> matter that has two comments
>>>>
>>>> Is there anyway I can do this in a single SELECT statement instead of
>>>> using the temp table tmp_Person2Book?
>>>> Thanks
>>>>
>>>
>>
>>
>


From: Tibor Karaszi on
It is pretty much like a dynamically defined view. I.e., the query of the
CTE and the "outer query" are combined (like a macro) and the whole is
optimized. Sometimes it is performs better compared to temp tables,
sometimes it performs worse.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi



"Jennifer Mathews" <waltersjennifer(a)live.com> wrote in message
news:#WlVoVOrKHA.3800(a)TK2MSFTNGP06.phx.gbl...
> But I have a question. Is a CTE just another name for a temp table? Is
> it creating a physical temp table or it it a memory temp table?
>
> "Jay" <spam(a)nospam.org> wrote in message
> news:%23MwyyCDrKHA.6064(a)TK2MSFTNGP02.phx.gbl...
>> Wait till you get a load of CTE's and Pivots
>>
>> "Jennifer Mathews" <waltersjennifer(a)live.com> wrote in message
>> news:OnzNxoCrKHA.5940(a)TK2MSFTNGP02.phx.gbl...
>>> THANKS to everyone!!! Wow!!! The power of SQL is just amazing. I wish
>>> I had known this awhile ago instead of using temp tables.
>>>
>>> Thanks again.
>>>
>>>
>>> "Jennifer Mathews" <waltersjennifer(a)live.com> wrote in message
>>> news:uIyys89qKHA.6064(a)TK2MSFTNGP02.phx.gbl...
>>>>I have been trying to redo some of my SQL without temp tables and Loops
>>>>so here is another question I have not been able to figure out.
>>>>
>>>> Table Bk_Reviews
>>>> Person_ID int
>>>> Book_ID int Book_Comment varchar(2000)
>>>> Person_ID Book_ID Book_Comment
>>>> 100 50 whatever1
>>>> 100 60 whatever2
>>>> 105 70 whatever3
>>>> 105 80 whatever4 << same book
>>>> 105 80 whatever5 << same book
>>>>
>>>> To get the total number of comments by a person I use:
>>>>
>>>> DECLARE @cnt int SELECT @cnt = COUNT(*) FROM Bk_Reviews WHERE
>>>> Person_ID = 105 RETURNS 3 for Person_ID=105
>>>>
>>>> Most people only comment once for a book but people can have multiple
>>>> comments per book such as Person_ID=105 \ Book_ID=80
>>>>
>>>> I am currently using a temp table to count the number of Total UNIQUE
>>>> comments excluding duplicates per book.
>>>>
>>>> SELECT DISTINCT Book_ID INTO #tmp_Person2Book
>>>> FROM Bk_Reviews WHERE Person_ID = 105
>>>> SELECT @cnt = COUNT(*) FROM #tmp_Person2Book RETURNS 2 for
>>>> Person_ID=105
>>>>
>>>> Person_ID Book_ID 105 70 105 80 << doesn't
>>>> matter that has two comments
>>>>
>>>> Is there anyway I can do this in a single SELECT statement instead of
>>>> using the temp table tmp_Person2Book?
>>>> Thanks
>>>>
>>>
>>
>>
>