table-valued functions, temp tables etc - when to use each one?

Question

Post reply

table-valued functions, temp tables etc - when to use each one?

pietlinden

SSC Guru

Points: 63506
More actions
May 23, 2021 at 9:10 pm
Go to Answer
#3887052

Sorry for the dumb question, but given all the "things" that return tables... temp tables, table-valued functions, views, etc... Is there a guideline anywhere that explains when to use each one?
pietlinden

SSC Guru

Points: 63506
More actions
May 28, 2021 at 1:28 am
Answer
#3889011
Learning T-SQL in a few easy steps...
1. Walk across the room.
2. Take down either copy of T-SQL Fundamentals from the shelf.
3. Open up to the relevant section.
4. RTFM.
Congratulations.
"Reading Is Fundamental" -- yeah, I guess it is.

Viewing 5 posts - 1 through 4 (of 4 total)

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

pietlinden SSC Guru Points: 63506 More actions · Answer 1

Well, here's a start at least...

Pros and cons of six SQL table tools – SQLServerCentral

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 2

My very general advice would be to stick to base tables and not use any of the features outside of the ANSI ISO standards for creating tables. Essentially, this advice reduces to "trust the optimizer, Luke." If you have a well-designed schema, everything is logical and under control, with lots of constraints. There's no reason you should mimic 1970s scratch tapes with tables. Were trying to be a declarative language that means you tell exactly what you want and you get the results. You do not have to do step-by-step intermediate things like temp tables or whatever get those answers.

Please post DDL and follow ANSI/ISO standards when asking for help.

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 3

jcelko212 32090 wrote:

My very general advice would be to stick to base tables and not use any of the features outside of the ANSI ISO standards for creating tables. Essentially, this advice reduces to "trust the optimizer, Luke." If you have a well-designed schema, everything is logical and under control, with lots of constraints. There's no reason you should mimic 1970s scratch tapes with tables. Were trying to be a declarative language that means you tell exactly what you want and you get the results. You do not have to do step-by-step intermediate things like temp tables or whatever get those answers.

Heh... that's coming from the guy that wrote a push-stack to do conversions of Adjacency Lists to Nested Sets. 😉

--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)