Ø When
an index is created on a table; a non-clustered index is created by default.
When an identity column is created and no primary key is defined; the identity
column becomes primary key by default.
When a table is created with no primary key ; the table is created in heap.
If indexes are disabled on a table then query
will not run on that table. Subsequently, If index
is disabled on a table then query plan cannot be created for that
table.
Unique Clustered Indexes vs Non-Unique Clustered Indexes
(the first one consumes more space and overhead while
creating key on a table).
Creating local variable for
avoiding query sniffing.
Indexed view -
Feature | Table Variables | Temporary Tables |
Scope | Current batch | Current session, nested stored procedures. Global: all sessions. |
Usage | UDFs, Stored Procedures, Triggers, Batches. | Stored Procedures, Triggers, Batches. |
Creation | DECLARE statement only. |
CREATE TABLE statement.
SELECT INTO statement.
|
Table name | Maximum 128 characters. | Maximum 116 characters. |
Column data types |
Can use user-defined data types.
Can use XML collections.
| User-defined data types and XML collections must be in tempdb to use. |
Collation | String columns inherit collation from current database. | String columns inherit collation from tempdb database. |
Indexes | Can only have indexes that are automatically created with PRIMARY KEY & UNIQUE constraints as part of the DECLARE statement. | Indexes can be added after the table has been created. |
Constraints | PRIMARY KEY, UNIQUE, NULL, CHECK, but they must be incorporated with the creation of the table in the DECLARE statement. FOREIGN KEY not allowed. | PRIMARY KEY, UNIQUE, NULL, CHECK. Can be part of the CREATE TABLE statement, or can be added after the table has been created. FOREIGN KEY not allowed. |
Post-creation DDL (indexes, columns) | Statements are not allowed. | Statements are allowed. |
Data insertion | INSERT statement (SQL 2000: cannot use INSERT/EXEC). |
INSERT statement, including INSERT/EXEC.
SELECT INTO statement.
|
Insert explicit values into identity columns (SET IDENTITY_INSERT). | The SET IDENTITY_INSERT statement is not supported. | The SET IDENTITY_INSERT statement is supported. |
Truncate table | Not allowed. | Allowed. |
Destruction | Automatically at the end of the batch. | Explicitly with DROP TABLE statement. Automatically when session ends. (Global: also when other sessions have no statements using table.) |
Transactions | Last only for length of update against the table variable. Uses less than temporary tables. | Last for the length of the transaction. Uses more than table variables. |
Stored procedure recompilations | Not applicable. | Creating temp table and data inserts cause procedure recompilations. |
Rollbacks | Not affected (Data not rolled back). | Affected (Data is rolled back). |
Statistics | Optimizer cannot create any statistics on columns, so it treats table variable has having 1 record when creating execution plans. | Optimizer can create statistics on columns. Uses actual row count for generation execution plan. |
Pass to stored procedures | SQL 2008 only, with predefined user-defined table type. | Not allowed to pass, but they are still in scope to nested procedures. |
Explicitly named objects (indexes, constraints). | Not allowed. | Allowed, but be aware of multi-user issues. |
Dynamic SQL | Must declare table variable inside the dynamic SQL. | Can use temporary tables created prior to calling the dynamic sql. |
---------------- Important commands to clear
DBCC FREEPROCCACHE --clear cache
DBCC DROPCLEANBUFFERS -- clear buffer