Table Valued Parameters (TVPs) and User Defined Table Types (UDTTs)

Table Valued Parameters and User Defined Table Type are available from SQL Server 2008 but still many developers are not aware of it. Before SQL Server 2008 it was not possible to pass a table variable to a stored procedure, for that case we mostly relied on xml data type to send multiple rows to a stored procedure. Now we can use Table Valued Parameters in stored procedure, and to use Table Valued Parameters we need to create a User Defined Table Type.

User Defined Table Type and Table Valued Parameter

A user-defined table type is a user-defined type that represents the definition of a table structure. You can use a user-defined table type to declare table-valued parameters for stored procedures or functions, or to declare table variables that you want to use in a batch or in the body of a stored procedure or function. Using Table Valued Parameter we can easily pass multi row data to stored procedure.

Read more from CodeProject.com

Advertisements

SQL Server – Table Hints

Table hints are nothing but a Transact-SQL clause that is specified in the FROM clause of the data manipulation language (DML) statement and affect only the table or view referenced in that clause. Table hints override the default behavior of the query optimizer for the duration of the data manipulation language (DML) statement by specifying a locking method, one or more indexes, a query-processing operation such as a table scan or index seek, or other options.

Because the SQL Server query optimizer typically selects the best execution plan for a query, msdn recommend that hints be used only as a last resort by experienced developers and database administrators.

Microsoft-SQL-Server

Using hints can add a performance penalty and hints that improved performance in earlier editions of SQL Server may not do so in newer versions. However, there are situations where a hint can improve performance or allow queries to execute that would otherwise be blocked

Syntax:-

WITH  ( <table_hint> [ [, ]...n ] )

<table_hint> ::= 
[ NOEXPAND ] { 
    INDEX  ( index_value [ ,...n ] ) | INDEX =  ( index_value )    | FORCESEEK [( index_value ( index_column_name  [ ,... ] ) ) ]
  | FORCESCAN
  | FORCESEEK
  | HOLDLOCK 
  | NOLOCK 
  | NOWAIT
  | PAGLOCK 
  | READCOMMITTED 
  | READCOMMITTEDLOCK 
  | READPAST 
  | READUNCOMMITTED 
  | REPEATABLEREAD 
  | ROWLOCK 
  | SERIALIZABLE 
  | SPATIAL_WINDOW_MAX_CELLS = integer
  | TABLOCK 
  | TABLOCKX 
  | UPDLOCK 
  | XLOCK 
} 

<table_hint_limited> ::=
{
    KEEPIDENTITY 
  | KEEPDEFAULTS 
  | HOLDLOCK 
  | IGNORE_CONSTRAINTS 
  | IGNORE_TRIGGERS 
  | NOLOCK 
  | NOWAIT
  | PAGLOCK 
  | READCOMMITTED 
  | READCOMMITTEDLOCK 
  | READPAST 
  | REPEATABLEREAD 
  | ROWLOCK 
  | SERIALIZABLE 
  | TABLOCK 
  | TABLOCKX 
  | UPDLOCK 
  | XLOCK 
}

Omitting the WITH keyword is a deprecated feature: This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Separating hints by spaces rather than commas is a deprecated feature: This feature will be removed in a future version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible.

Sample:

SELECT * FROM Employees WITH (NOLOCK)

Click here to read more from MSDN