July 20, 2016 at 1:13 am
I have few quick questions
1)
If table variable is storing in tempdb why can't I use them like temp tables?. How can I check table variable in tempdb?.when to use temp table/table variable?
2)
What is major difference between CTE and derived tables. Why can't use derived table instead of CTE if query is very small. When to use CTE/Derived table?
3).
I have data in a table below
County
USA
UK
IND
I need the output below
USA VS UK
UK VS IND
IND VS USA
Any help greatly appreciated
July 20, 2016 at 1:36 am
Quick suggestion, based on your questions I suggest you research those topics on BOL and come back if you have any further questions.
😎
July 20, 2016 at 2:56 am
I have already searched. I'm looking for more information .Thanks
July 20, 2016 at 3:31 am
koti.raavi (7/20/2016)
If table variable is storing in tempdb why can't I use them like temp tables?.
Because they're not designed to be used the same way. It's got nothing to do with the physical storage (and they are both in tempDB), they're designed to have a behaviour difference.
How can I check table variable in tempdb?
What do you mean 'check'?
What is major difference between CTE and derived tables.
Other than where they're defined, not mnuch
Why can't use derived table instead of CTE if query is very small.
You can. And when the query is big. They're completely interchangeable (other than recursive CTEs)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 20, 2016 at 6:01 am
koti.raavi (7/20/2016)
I have few quick questions1)
If table variable is storing in tempdb why can't I use them like temp tables?. How can I check table variable in tempdb?.when to use temp table/table variable?
2)
What is major difference between CTE and derived tables. Why can't use derived table instead of CTE if query is very small. When to use CTE/Derived table?
3).
I have data in a table below
County
USA
UK
IND
I need the output below
USA VS UK
UK VS IND
IND VS USA
Any help greatly appreciated
Sounds like you're confusing temporary tables, table variables, common table expressions and derived tables. Let's break it down a little. First, temporary tables and table variables are temporary storage. Common table expressions and derived tables are just queries, not at all temporary storage. Temporary tables and table variables primary difference (there are several, but this is the big one) is the fact that table variables don't have statistics. That difference is what drives your choice for which to use. Need temporary storage AND statistics on that stored data, temporary tables. Need temporary storage, but no statistics, table variables.
Separately we have derived tables, which includes Common Table Expressions & views. These are just queries that act like tables. They're not. They're just queries. To a very large degree, they can be used interchangeably. However, there are differences. Views, for example, can have security attached unlike the other derived tables. Common table expressions can be reused multiple times in a query or even called recursively, as Gail already noted. Derived tables are just a query that gets treated as if it were a table within the query, but the query optimizer treats it like what it is, a query.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 20, 2016 at 2:15 pm
Another difference between temp tables and table variables that I like keep in mind is that you can't refer to a table variable inside dynamic sql unless it was declared within your dynamic statement.
This works...
CREATE TABLE #TempTable (ID INT, Column1 VARCHAR(20))
INSERT INTO #TempTable
VALUES (1, 'Something'), (2, 'Else')
EXEC ('SELECT * FROM #TempTable')
But not this...
DECLARE @TableVar TABLE (ID INT, Column1 VARCHAR(20))
INSERT INTO @TableVar
VALUES (1, 'Something'), (2, 'Else')
EXEC ('SELECT * FROM @TableVar')
For Dynamic+Table Variable you'd have to do this...
EXEC ('
DECLARE @TableVar TABLE (ID INT, Column1 VARCHAR(20))
INSERT INTO @TableVar
VALUES (1, ''Something''), (2, ''Else'')
SELECT * FROM @TableVar')
It may not be relevant especially if your just trying to wrap your head around things but thought I'd share nonetheless.
Cheers,
July 20, 2016 at 6:05 pm
Grant Fritchey (7/20/2016)
e expressions can be reused multiple times in a query or even called recursively, as Gail already noted. Derived tables are just a query that gets treated as if it were a table within the query, but the query optimizer treats it like what it is, a query.
Same for CTEs - they are just a query that gets treated as if it were a table within the query, but the query optimizer treats it like what it is, a query.
The danger of reusing of a CTE multiple times within a query is that it's being re-executed as many times as it's been reused.
CTE may seem more convenient for some developers, but in terms of performance if you need to reuse a CTE within a query it's better to populate a table variable with a query used for CTE, and then reference it in the main query.
Same for recursion.
WHILE loop populating table variable beats recursive CTE in terms of performance and resource consumption.
_____________
Code for TallyGenerator
July 20, 2016 at 6:11 pm
koti.raavi (7/20/2016)
How can I check table variable in tempdb?
SELECT *
INTO #TempTables
FROM tempdb.sys.objects o
WHERE o.type = 'U'
GO
DECLARE @Table TABLE (id INT, Name NVARCHAR(50))
SELECT *
FROM tempdb.sys.objects o
WHERE o.type = 'U'
AND NOT EXISTS (SELECT * FROM #TempTables t
WHERE t.object_id = o.object_id)
SELECT *
FROM tempdb.sys.columns c
WHERE EXISTS (SELECT * FROM tempdb.sys.objects o
WHERE o.type = 'U' AND o.object_id = c.object_id)
AND NOT EXISTS (SELECT * FROM #TempTables t
WHERE t.object_id = c.object_id)
DROP TABLE #TempTables
_____________
Code for TallyGenerator
July 20, 2016 at 10:49 pm
Sounds like homework or interview questions.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 21, 2016 at 12:25 am
GilaMonster:
Thanks for your response; you mean to say if query is big it’s better to use CTE instead of derived table?
Grant Fritchey:
Thanks for your response.
1) I know that if index is exist on table, statistics will be automatically created. Does the statistics will be created if index is not exist on table?. If not when statistics will be created?
2). Instead of views I can use stored procedure for hiding columns and security. So I can give permission to user to procedure instead of view. In fact I will get benefit of execution plan. I just want to know more difference
3) We can also reuse derived tables like CTE; I can see only one difference Recursive when compared to derived tables. I believe performance is same
Y.B: Thanks for your response. I have leant one more difference 🙂
Sergiy: Thanks for your response
Jeff Moden: Ha ha nice one Jeff :). Home Work .just wants to learn bit more 🙂
July 21, 2016 at 1:43 am
koti.raavi (7/21/2016)
GilaMonster:Thanks for your response; you mean to say if query is big it’s better to use CTE instead of derived table?
No, I did not mean to say that. I meant to say exactly what I did say.
You can use a derived table or CTE when the query is small. You can use a derived table or CTE when the query is big. You can use a derived table or CTE when the query is anything in between.
With the exception of recursive CTEs, a CTE and a derived table are interchangable.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 21, 2016 at 4:11 am
koti.raavi (7/21/2016)
GilaMonster:Thanks for your response; you mean to say if query is big it’s better to use CTE instead of derived table?
Grant Fritchey:
Thanks for your response.
1) I know that if index is exist on table, statistics will be automatically created. Does the statistics will be created if index is not exist on table?. If not when statistics will be created?
If a column is used in a WHERE clause, JOIN, HAVING, or others that are going to need statistics, statistics will be created if there is no index. You can see them. The name will start with 'wa_'.
2). Instead of views I can use stored procedure for hiding columns and security. So I can give permission to user to procedure instead of view. In fact I will get benefit of execution plan. I just want to know more difference
OK.
3) We can also reuse derived tables like CTE; I can see only one difference Recursive when compared to derived tables. I believe performance is same
As long as you always understand that they're just a query, you should be fine.
Y.B: Thanks for your response. I have leant one more difference 🙂
Sergiy: Thanks for your response
Jeff Moden: Ha ha nice one Jeff :). Home Work .just wants to learn bit more 🙂
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 21, 2016 at 1:12 pm
koti.raavi (7/21/2016)
Jeff Moden: Ha ha nice one Jeff :). Home Work .just wants to learn bit more 🙂
In that case, looks like you have a good learning incentive. The questions you asked are good ones. Well done.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply