Have some question

Question

Post reply

Have some question

miss.delinda

SSCommitted

Points: 1616
More actions
April 7, 2010 at 7:17 am

#222927

I've question as follow,
1. How many types of temporary tables exist in SQL Server and how is each type symbolized?
2. What is the lifetime and visibility of each type?
3. When should Transact-SQL or CLR be used?
4. What is the difference between DELETE & TRUNCATE commands?
5. In cursor, When should they be used instead of set-based statements?

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

miss.delinda (4/7/2010)
4. What is the difference between DELETE & TRUNCATE commands?

Too many questions for a single post - I'll take #4 for $400 😀

DELETE - assuming no predicate in the statement - will delete all rows of the table one by one creating entries in t-log for rollback purposes. Delete will not affect storage in the sense that table will still have the same size after delete completion.

TRUNCATE will reset table's HWM a.k.a. High Water Mark, in a single not t-logged operation.

If you want to get rid of the whole content of a table... fast... do truncate.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 2

Assignment questions again?????

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

GregoryF SSCertifiable Points: 6296 More actions · Answer 3

Alvin Ramard (4/7/2010)
Assignment questions again?????

Apparently it's too difficult query google for the answers

/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

miss.delinda SSCommitted Points: 1616 More actions · Answer 4

miss.delinda

SSCommitted

Points: 1616

April 7, 2010 at 10:46 am

#1147557

tq to all.

Rob Schripsema SSCertifiable Points: 7469 More actions · Answer 5

Questions #1, #2 and #4 are all outlined in BooksOnline and numerous blogs and articles.

Questions #3 and #5 are subjects for all kinds for interesting discussion. No hard and fast rules (though there are many wise, experienced people who can provide good insights into the issues surrounding best practices).

Try asking more specific questions (and try to keep it to one per post) if you want answers here; otherwise, take a good SQL class or search further on this or other sites -- I'm sure you'll find lots to read.

Rob Schripsema
Propack, Inc.

Steve Jones - SSC Editor SSC Guru Points: 736150 More actions · Answer 6

Please don't ask us your homework or test questions. Do a little work, look things up, and if you don't understand, ask a specific question. But asking how many types of temp tables exist implies you have no clue and haven't made any effort to learn.

LutzM SSC Guru Points: 107049 More actions · Answer 7

Steve Jones - Editor (4/7/2010)
... But asking how many types of temp tables exist implies....

Steve, would you consider a worktable as being the third type of a temp table? 😉

From my point of view the difference is that worktables are generated internally (by query optimizer) whereas temp tables can be created and dropped user-driven (a user might be able to force a worktable generation, too).

Worktables are dropped when no longer needed (decision made by QO?) almost the same like "real" temp tables (based on active connections).

I'm not sure if BOL is correct when stating that a worktable is built in tempdb. If the same rules would apply as for temp table and table variables in terms of being held in memory vs. stored in tempdb it would be another reason to consider worktable as the third type... I wonder how that would influence the grade of homework/assignment though... But at least it makes room for discussion 🙂

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

GregoryF SSCertifiable Points: 6296 More actions · Answer 8

lmu92 (4/7/2010)
Steve Jones - Editor (4/7/2010)
... But asking how many types of temp tables exist implies....
Steve, would you consider a worktable as being the third type of a temp table? 😉
From my point of view the difference is that worktables are generated internally (by query optimizer) whereas temp tables can be created and dropped user-driven (a user might be able to force a worktable generation, too).
Worktables are dropped when no longer needed (decision made by QO?) almost the same like "real" temp tables (based on active connections).
I'm not sure if BOL is correct when stating that a worktable is built in tempdb. If the same rules would apply as for temp table and table variables in terms of being held in memory vs. stored in tempdb it would be another reason to consider worktable as the third type... I wonder how that would influence the grade of homework/assignment though... But at least it makes room for discussion 🙂

I think you have gone way beyond the scope of someone who asked what the difference between delete and truncate is 🙂 Don't confuse them while they are preparing for their exam.

/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

LutzM SSC Guru Points: 107049 More actions · Answer 9

GregoryF (4/7/2010)
I think you have gone way beyond the scope of someone who asked what the difference between delete and truncate is 🙂 Don't confuse them while they are preparing for their exam.

What's confusing about it? It was a 16 word question that might include a "hidden" answer. There might have been a slight chance of confusion if the OP would have stated "I think the answer is: two, #, ##". But since there is no answer provided, we cannot assume any opinion the OP might have. And without an opinion there is little room for confusion.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004309 More actions · Answer 10

miss.delinda (4/7/2010)
I've question as follow,
1. How many types of temporary tables exist in SQL Server and how is each type symbolized?
2. What is the lifetime and visibility of each type?
3. When should Transact-SQL or CLR be used?
4. What is the difference between DELETE & TRUNCATE commands?
5. In cursor, When should they be used instead of set-based statements?

Even though these types of posts desparately deserve it, I'm not trying to be a smarta55 here... I'm trying to help you help yourself. Do you know what "Books Online" is and how to get into it? I'm not talking about the internet... I'm talking about a very specific thing called "Books Online".

If you don't, please say so and we'll show you how to get into that wonderful tool.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004309 More actions · Answer 11

lmu92 (4/7/2010)
GregoryF (4/7/2010)
I think you have gone way beyond the scope of someone who asked what the difference between delete and truncate is 🙂 Don't confuse them while they are preparing for their exam.
What's confusing about it? It was a 16 word question that might include a "hidden" answer. There might have been a slight chance of confusion if the OP would have stated "I think the answer is: two, #, ##". But since there is no answer provided, we cannot assume any opinion the OP might have. And without an opinion there is little room for confusion.

I wouldn't discuss anything of the original questions here. Someone needs to learn how to use BOL. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

PaulB-TheOneAndOnly (4/7/2010)
miss.delinda (4/7/2010)
4. What is the difference between DELETE & TRUNCATE commands?
Too many questions for a single post - I'll take #4 for $400 😀
DELETE - assuming no predicate in the statement - will delete all rows of the table one by one creating entries in t-log for rollback purposes. Delete will not affect storage in the sense that table will still have the same size after delete completion.
TRUNCATE will reset table's HWM a.k.a. High Water Mark, in a single not t-logged operation.
If you want to get rid of the whole content of a table... fast... do truncate.

Not completely true, the truncate table is logged such that, if a) the system fails during the truncate the database can be recovered to a consistant state, or b) the truncate is wrapped inside of an explicit transaction it can also be rolled back.

Sample code:

create table dbo.TruncateTest (

TruncateTestID int identity(-1000000,1),

Data int

);

insert into dbo.TruncateTest(Data)

select

N

from

dbo.Tally -- predefined Tally table with 1,000,000 rows starting at 1

where

N <= 1000000;

--select * from dbo.TruncateTest;

begin transaction;

truncate table dbo.TruncateTest;

select * from dbo.TruncateTest;

rollback transaction

select * from dbo.TruncateTest;

drop table dbo.TruncateTest;

miss.delinda SSCommitted Points: 1616 More actions · Answer 13

miss.delinda

SSCommitted

Points: 1616

April 8, 2010 at 2:42 am

#1148090

tq to all. i'll learn to using Books Online

Jeff Moden SSC Guru Points: 1004309 More actions · Answer 14

miss.delinda (4/8/2010)
tq to all. i'll learn to using Books Online

KO 10Q. Thanks for the feedback. Yeah... these questions are easily answered by looking up key words like "Temp Table" and "Create Table" in BOL. You'll remember the answers better that way, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)