SQL Tips

Difference between truncate and delete

• delete operations are logged and thus can be restored using transaction log backups (if not using Simple recovery model - the default)
• truncate operations are not logged, thus cannot be rolled back.
• delete can be used with WHERE clause to selectively delete rows
• truncate cannot selectively delete rows.
• truncate reseeds the identity column , delete does not reseed
• truncate cannot be used on tables with foreign key constraints
• since truncate is not logged, it does not activate triggers, delete does activate triggers
  

=====================================================
Difference between temp table and table variables:

• table variables are not logged, i.e. they don't use transaction logs, thus cannot participate in transactions
• indexes cannot be created on table variables
• foreign key constraints cannot be created either on table variables or temp tables
• table variables cannot be nested, i.e. they cannot be reused within the nested subquery, whereas temp tables can be accessed from within nested subqueries
• SP with table variables require fewer recompilation than SP with temp tables.
• table variables dont make use of parallelism since they reside in memory, temp table do make use of multiple processors since they reside in tempdb.
• table variables used for small amounts of data since they reside in memory.
  

=====================================================
Types of Data Integrity
Entity Integrity: Primary Key and Unique key constraints are used to maintain entity integrity
Domain Integrity : Check and Not NULL constraints are used to maintain domain integrity
Referential integrity : Foreign key constraints are used to maintain referential integrity
=====================================================
Local Temp tables & variables : exist for the duration of session, thus not shared by different client sessions. If Dynamic sql creates the local temp table then it falls out of scope as soon as it exits the EXEC statement used to execute the dynamic SQL.
Global Temp tables & variables : shared by all client sessions and are destroyed when the last client session exits
===========================================================
Types of UDFs:
Scalar valued udf : returns a single value, can be used anywhere an expression is used, just like subqueries.
table valued udf : returns a table.
2 types of table valued udf's :

• inline table valued udf : uses a single statement to return a table
• multi-statement table valued udf : multiple statements are issued in the udf before returning the table

UDF's can be used within select/having/where clauses and table valued udf's return rowsets/table and can be used in joins.
Cannot use temp tables in UDF's, only table variables.
UDFs are analogous to views that accept input parameters
================================================
Indexed Views are views that use disk space to store data.
Created with SCHEMABINDING option
have restrictions on the columns that the Indexed view can contain
have restrictions on the columns that can be used within the index created for the view
represent views that have a unique clustered index
Must have ansi_nulls, quoted_identifier, arithabort etc session options set to ON
If using standard edition than cannot be used implicitly by queries, must use the NOEXPAND optimizer HINT or reference with view with the VIEW name.
If using Group by then count_big(*) must be used
Must not be used in OLTP environments, suitable for less frequently updated base tables.

============================================================
ANSI_NULLS : default OFF, when ON then returns 0 rows when col = null or col <> null used even though the column has both null and not null values.
Thus we need to insure that we use the col is null, col is not null syntax for comparisons

ANSI_WARNINGS : default OFF: when ON then shows warnings when one of the following situations arises:
sum, max, min, avg used against a column that contains nulls
Divide by Zero exception occurs
Overflow exception occurs (string may be truncated)

QUOTED_IDENTIFIER : default OFF : when ON then allows object identifiers to use double quotes for distinguishing from reserved words and allows string literals to use single quotes

Indexed views need all the above sessions options to be set to ON

Concat_NULL_YIELDS NULL : default OFF

ANSI_DEFAULTS : default OFF: When ON then sets the above and the following session options to ON:
ANSI_PADDING