SQL – Some useful commands and features

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,
or batch.
@@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'

Comments (1)

  1. Interesting, I did not know there were two kinds of temporary tables!

Skip to main content