SQL - Some useful commands and features

  • Article 
 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
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'