SET NOCOUNT - Stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set.
SCOPE_IDENTITY( ) – Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function,
@@IDENTITY - Returns the last inserted identity column value inserted across any scope in the current session.
CHAR(13) - For inserting line break (new line character) in the result set.
ISNULL( ) – Replaces NULL with the specified replacement value.
CAST( ) - Returns expression translated to data_type.
CONVERT( ) - Returns expression translated to data_type. Its also supports formatting of the data returned.
CASE Statement – It evaluates a list of conditions and returns one of multiple possible result expressions. It cannot be used outside SELECT statement.
Temporary Tables – These
are created to store data for temporary use. Once created, these tables
can be viewed under “System Databases -> Tempdb -> Tables ->
Temporary Tables” section. There are two kinds of temporary tables.
Local Temporary Tables: Once
created, these are available only for the current sql connection
established by a user. These are deleted when the user closes the sql
Global Temporary Tables: Once
created, these are available under any sql connection established by any
user. These are deleted when all the sql connections are closed.
Convert values in a table into a string – Substring((Select ‘,’ + Table.ColName for XML Path(”)), 2, 500)
Single or double quotes in text – Between every two single quotes all double quotes are converted into one. Example:- ‘He””llo World’ is converted into ‘He”llo World’