Differences

Question

Diffrences between 1. UDF and Stored procedures 2. temporary table and derived table

Lalit M · Answer

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 Localwhich 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 N · Answer

UDF and Stored procedures UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be. UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables. Inline UDF's can be though of as views that take parameters and can be used in JOINs and other Rowset operations 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