Follow Me : https://www.youtube.com/c/SauravAgarwal
33. What are the differences between Temporary Table and Table Variable?
Temporary Table:
It can perform both DML and DDL Statement. Session bound Scope
Syntax CREATE TABLE #temp
Have indexes
Table Variable:
Can perform only DML, but not DDL Batch bound scope
DECLARE @var TABLE(...)
Cannot have indexes
34. What is Stored Procedure (SP)?
It is one of the permanent DB objects that is precompiled set of TSQL statements that can accept and
return multiple variables.
It is used to implement the complex business process/logic. In other words, it encapsulates your entire
business process.
Compiler breaks query into Tokens. And passed on to query optimizer. Where execution plan is
generated the very 1st time when we execute a stored procedure after creating/altering it and same
execution plan is utilized for subsequent executions.
Database engine runs the machine language query and execute the code in 0's and 1's.
When a SP is created all Tsql statements that are the part of SP are pre-compiled and execution plan is
stored in DB which is referred for following executions.
Explicit DDL requires recompilation of SP's.
Do'stlaringiz bilan baham: