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 are a scheduled jobs or what is a scheduled tasks?

899

---

What is exporting utility?

1007

---

Please differentiate between a local and a global temporary table?

999

---

Can we insert data if clustered index is disabled?

937

---

Define left outer join in sql server joins?

955

---

what information is maintained within the msdb database? : Sql server administration

967

---

Distinguish between commit and rollback?

953

---

What is the Disadvantage of indexed sequential file.

1078

---

What is a with(nolock)?

980

---

How to provide default values to stored procedure parameters?

1060

---

While migrating Microsoft SQL Server 2008 database to SQL Azure, what can be done to ensure the database connectivity does not degrade?

109

---

How to create user messages with print statements in ms sql server?

983

---

Can we check locks in database? If so, how can we do this lock check?

937

---

but what if you have to create a database with two filegroups, one on drive c and the other on drive d with log on drive e with an initial size of 600 mb and with a growth factor of 15%? : Sql server database administration

960

---

What is the meaning of sql server?

986

---