Answer Posted / jerry joseph

SYNTAX
Temporary Tables: CREATE table #T (…)
Table Variables: DECLARE @T table (…)

Table Variables are out of scope of the transaction mechanism.
If you make changes to a Temp Table inside a Transaction and
Rollback the Transaction those changes will be lost.
Changes made to Table Variable inside a Transaction will
remain even if you Rollback the Transaction

Any procedure with a temporary table cannot be pre-compiled
An execution plan of procedures with table variables can be
statically compiled in advance

Table Variables exist only in the same scope as variables.
They are not visible in inner stored procedures and in
exec(string) statements

Table variables are in-memory structures that may work from
2-100 times faster than temp tables.

Access to table variables gets slower as the volume of data
they contain grows.

At some point, table variables will overflow the available
memory and that kills the performance.

Use table variables only when their data content is
guaranteed not to grow unpredictably; the breaking size is
around several thousand records.

For larger data volumes, use temp tables with clustered
indexes.

What is a file group?

1069

---

Explain primary key in sql server?

998

---

How can we remove orphan records from a table?

970

---

How can we get count of the number of records in a table?

940

---

What is index in an assignment?

936

---

How to compare the top two records using sql?

1328

---

What program is used to store the data source file?

942

---

What is self join in sql server joins?

1031

---

What is inline variable assignment?

1002

---

How do you size a resultset?

900

---

How to transfer data from a cursor to variables with a "fetch" statement?

1071

---

What are the types of stored procedures in an sql server?

969

---

Can sql server 2016 run on windows 7?

903

---

How we can compare two database data?

923

---

What is global temp table?

984

---