a. Scope a1. Temp tables are visible only to a session across all batches (if local) and visible across a session if global. a2. Table variables are visible only to the batch that declared them and are destroyed at the end of the batch. a3. N/A because table expressions do not have physical representation and their scope equates those of table called by such a table expression. b. Can apply DDL after creation/declaration? b1. Can apply a DDL to temp tables after it is created. b2. ??? b3. n/a c. Can have indexes? c1. Can have indexes and other DDL changes. c2. ??? c3. n/a d. Affected by rollback/ logged? d1. Yes. d2. No. d3. n/a e. Has distribution statistics? e1. Yes. Allows histogram. e2. No. e3. n/a f. Has physical presence in temp db? f1. Yes f2. Yes f3. No. g. Suitable for what table size? g1. Large data g2. Small data g3. ???
From novice to tech pro — start learning today.