·Temporary tables are defined just like regular tables, only they are stored in the tempdb database.

·Temporary tables are often used to store temporary results, for use in queries, updates, and stored procedures.

·They can be used to return or pass a set of results from a called procedure to a calling stored procedure.

·Developers often use temporary tables to avoid Transact-SQL cursors.

·Temporary tables are defined like regular tables, and can have indexes, constraints (but not foreign key constraints), and other related elements just like regular tables.

·Temporary tables suffers due to the performance overhead that they sometimes incur, including excessive transaction logging, and locking of database objects.

·There are two different temporary table types: local and global.

Local Temporary Table

·Available for use by the current user connection that created them.

·They are defined by adding a two pound (##) signs.

·Multiple connections can create the same named temporary table for local temporary tables without encountering conflicts

·The internal representation of the local table is given a unique name so as not to conflict with other temporary tables with the same name created by other connections in the tempdb.

·Local temporary tables are dropped by using the DROP statement or are automatically removed when the user connection is closed.

Global Temporary Table

·Once a connection creates a global temporary table, any user with access to the current database can access.

·the tableThey are defined by adding a single pound (#) sign.

·You cannot create simultaneous versions of a global temporary table, as this will generate a naming conflict.

·Global temporary tables are removed if explicitly dropped by DROP TABLE or, automatically, after the last connection exits and the global temporary table is no longer referenced by any connections.

Table Variable

·Can sometimes work more efficiently than a temporary table, as SQL Server automatically clears it after it has been used; you do not need to DROP a table variable explicitly.

·Table variables can incur less locking and logging than a temporary table, because they are only available for the duration of the batch, function, or stored procedure where they are used.

·Table variables can also reduce recompilations of a stored procedure, compared to the temporary table counterparts.

DECLARE @<variable_name> TABLE (<table_definition>)

DECLARE @Authors TABLE

(iAuthorId int NOT NULL ,

vchLastName varchar (50) NULL ,

vchFirstName varchar (50) NULL)

Views

·Is a virtual representation of a table

·Used to provide de-normalized data.

·Views are also useful For Managing Security And Protecting sensitive data. If you wish to obscure the database's schema from the end user, you can grant permissions exclusively to views, rather than the underlying table.

Regular Views

·You define a regular view with a SELECT statement.

·Views are also updateable if they meet the following conditions:

oThey have no aggregate functions or the TOP, GROUP BY, UNION, or DISTINCT clauses in their definition.

oThey don't contain computed values or values based on functions.

oAny non-nullable columns on the base table must be included in the view definition.

·DELETE operations are only allowed if the view references one table in the FROM clause that defines the view.

CREATE VIEW <view_name>

{WITH ENCRYPTION | WITH SCHEMABINDING | WITH VIEW_METADATA }

AS

<select_statement>

{WITH CHECK OPTION }

GO

Note:

WITH CHECK OPTION

Used to make sure any INSERT activity in the view does not include a value that cannot be read from that view.

WITH ENCRYPTION

To not to allow your view definition to be viewable

Indexed Views

Note: Indexed Views is diferent than Clustered Index on the Table. Reason - The underlying (base) tables are not impacted physically by the creation of indexed views, as the view is treated like a separate database object.

·A view is no more efficient than the underlying SELECT query used to define it, unless an index is added to it.

·You must first create a unique clustered index on the view. Once this view has been built, the data that makes up the view is stored in much the same way as a clustered index for a table is stored.

·You can also create additional nonclustered indexes, as you would for a regular table. Prior to creating nonclustered indexes on the view, you must first define the clustered index.

·An indexed view are used implicitly even if the table name is used in the from clause, in place of View name.

·Are more useful for static data (reporting or data warehousing data), than data that is frequently updated; this is because base tables with frequent updates will trigger frequent index updates against the view, potentially reducing the benefit of query performance against data update speed.

Stored Procedures

·Produce faster execution times than ad hoc queries

·The key to a stored procedure's good performance is SQL Server's ability to store a Pre-Compiled Execution Plan for commands used within the stored procedure

·Increased Network performance - As the Transact-SQL statements from the stored procedure are invoked on the server itself, reducing Transact-SQL calls over the network. This leaves only the stored procedure call and parameters and returned results (when applicable) to be transferred over the network.

·Force a Stored Procedure to Recompile - When table data referenced from within a stored procedure undergoes significant changes, or new indexes are added to the referenced tables.

Extended Stored Procedures

·Allows to extend the functionality of SQL Server by referencing functionality from dynamic link libraries (DLLs).

·Extended stored procedures are defined by referencing a DLL file and are not defined with Transact-SQL statements like a regular stored procedure.

·Because a DLL is loaded and used from within SQL Server, there is an increased risk of memory leaks or performance issues (depending on the DLL). If a DLL misbehaves, SQL Server can crash.

sp_addextendedproc @functname = <'procedurename'>,

@dllname = <'dll_name'>

User-Defined Functions

·Were introduced in SQL Server 2000.

·Types of UDFs

oScalar UDFs –

§Return one value, and cannot return table data types.

oInline UDFs

§Return a table data type, and do not explicitly define the returned table, but rather use a single SELECT statement for defining the returned rows and columns

oMultistatement UDFs

§

Stored Procedures

User-Defined Functions

Accept parameters

Same

Can contain output parameters

Cannot contain output parameters

Optional

Must always return a scalar value or a table

No

Can be used within your queries (Select, Where & From) and DML statements.

Used as a substitute for a sub-query.

A function call can also be joined with other tables.

Indexes

·Indexes assist with query processing by speeding up data access against tables and views.

·Indexes are defined by selecting an index key, which is made up of one or more columns from the table or view.

·If indexes do not exist for a table, that table is called a Heap; meaning the data pages are stored in no particular order. A heap referenced in queries results in a table scan. A table scan occurs when all rows in the table are evaluated against the required end-result (instead of just a subset of the data).

·Two types of indexes, Clustered And Nonclustered.

·SQL Server uses a B-Tree data structure for each clustered and nonclustered index.

·When an index is used, SQL Server navigates from the Btree structure's root and traverses down to the leaf nodes.

Clustered Indexes

·Determine the physical order of data in a table.

·You can only have one clustered index for each table, as the data pages can only be physically stored in one way

·After a clustered index is created, data pages are physically contiguous, which can assist with the speed of queries.

·For a table with a clustered index, the leaf nodes are the data pages themselves.

·Good candidates - Columns that are

oQueried often in range queries (Between, <, >)

oOrder large result sets,

oUsed in aggregate functions, and

oWhose contents are distinct (primary or unique keys)

oLastly, Choose a Smaller Clustered Index Key over a wider column, reducing the size of the index needed and improving I/O operations.

·Bad candidates - Columns that are

oFrequently updated columns and

oNon-unique columns

Non-Clustered Indexes

·It stores index pages separately from the physical data, with pointers to the physical data located in the index pages and nodes. In a way it contains poitner to the Clustered Index leaf pages

·Nonclustered index columns are stored in the order of the index key column values.

·You can have up to 249 nonclustered indexes on each table or indexed view.

·For nonclustered indexes, the leaf node level is the Index Key Coupled To A Bookmark.

·The nonclustered index bookmark points to the B-Tree structure of the table's clustered index (if one exists). If the base able is a heap, the nonclustered index bookmark points to the table row's Row-ID.

·Good candidates - Columns that are

oUsed in Where clause

oOrder smaller result sets on unique data

oinvolved in searchable arguments using operators such as =, <, >, IN, EXISTS, and LIKE.

To Index or not to Index?

·Indexes take up space, and should not be added 'just in case'. View Tools for testing the effectiveness of your indexes.

·Indexes can slow down data loads significantly, and may not make sense for tables that are used expressly for data Imports And Staging.

·If your table will not be queried and is used as an intermediate staging ground, leaving indexes off the table may be a better choice.

·The same goes for very small tables where, no matter what, the query optimizer always performs a table scan.

·Keep in mind that heaps become naturally fragmented over time and, if data updates are frequent enough, even small tables can begin to take up more space than necessary. There are those who believe that a clustered index should exist for every table in the database, period.

·One technique for reducing bulk load operations on a table is to remove the index during the load and add the index once the load is finished

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name

ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )

[ WITH < index_option > [ ,...n] ]

[ ON filegroup ]

< index_option > :: =

734

{ PAD_INDEX |

FILLFACTOR = fillfactor |

IGNORE_DUP_KEY |

DROP_EXISTING |

STATISTICS_NORECOMPUTE |

SORT_IN_TEMPDB

}

Triggers

·There are two types of triggers,

oAFTER triggers - Execute after the data modification has been completed against the table

oINSTEAD OF triggers -

§Execute instead of the original data modification.

§INSTEAD OF triggers are allowed for both tables and views

·Multiple triggers can be defined for one table. The order in which they are fired can be configured using the sp_settriggerorder stored procedure

·The Nested Triggers server option will determine if triggers can cause other triggers to fire (cascading triggers). If enabled, triggers can cascade in a chain of up to 32 firings.

·SQL Server creates two "virtual" tables specifically for triggers, the Deleted and Inserted tables. These two tables capture before and after pictures of the modified data.

·We can Enable/Disable triggers.

Operation

Inserted Table

Deleted Table

INSERT

New Rows

-

UPDATE

New Rows

Old Rows

DELETE

-

Deleted Rows

·Points to Remember

oTriggers should be written to run quickly.

oConstraints usually run faster than a trigger

oYou can only create triggers on table/viewss that you own

oIf cascading referential integrity for DELETE or UPDATE is defined for the base table, you cannot define an INSTEAD OF DELETE or INSTEAD OF UPDATE on that table.