Sunday 3 June 2012


Ø  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 -







FeatureTable VariablesTemporary Tables
ScopeCurrent batchCurrent session, nested stored procedures. Global: all sessions.
UsageUDFs, Stored Procedures, Triggers, Batches.Stored Procedures, Triggers, Batches.
CreationDECLARE statement only.
CREATE TABLE statement.
SELECT INTO statement.
Table nameMaximum 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.
CollationString columns inherit collation from current database.String columns inherit collation from tempdb database.
IndexesCan 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.
ConstraintsPRIMARY 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 insertionINSERT 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 tableNot allowed.Allowed.
DestructionAutomatically 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.)
TransactionsLast 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 recompilationsNot applicable.Creating temp table and data inserts cause procedure recompilations.
RollbacksNot affected (Data not rolled back).Affected (Data is rolled back).
StatisticsOptimizer 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 proceduresSQL 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 SQLMust 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