Diffrences between
1. UDF and Stored procedures
2. temporary table and derived table
Loading
Know the answer? Post it — somebody with the same question will find it here.
Sign in to answer this question
It is the same account you read, post and publish with — and you will come straight back to this page.
Lalit MPosted Jan 7, 2010, 1:33 AM
1. UDF and Stored procedures
Show here diff with example
2. temporary table and derived table
Temp table is that which can be created on fly and starts with # symbol. there are two types of temporary tables Local,which will be available to only to those specific users who created it, and Global which can be available to all users.
A derived table is a virtual table that's calculated on the fly from a select statement and is similar to Temporary table.
coming to the differences,
In Temp tables,
Explicit Indexing and Constraints are allowed. there is no
Need to create or declare, we can use the INTO clause to create this object and it is Recommended when the data is huge & if there is any manipulation on the result
In Derived tables,
Explicit Indexing and Constraints are not Applicable and this cane be used Only on the current query and it is
Recommended for inline quires
Santhosh NPosted Jan 7, 2010, 12:55 AM
temporary table and derived table
Temp tables,
IO Operation - HIGH
Explicit Indexing is allowed
Constraints are allowed
Need not create or declare, we can use the INTO clause to create this object.
Reusable across the batch but with current session
Recommended when the data is huge & if there is any manipulation on the result
Supports concurrency users
Bit slower than table variables
Derived tables,
IO Operation – NA
Declaration – NA
Explicit Indexing – NA
Constraints - NA
Only on the current query(line)
If it is not reusable data then we can go for it
Recommended for inline quires
Supports concurrency users