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 connection.
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'