Row_Number() : Row Number will return serial numbers in the result set.

Rank() : Rank will returns the rank of each row , if the values are same for more records it will give the same number,if the value is change, then it will give the row number in the partition.

Dense_Rank() : Dense_Rank will returns the rank of each row,if the values are same, then it will give the same number,if the value is changed ,then it will give the next number of the previous row in the partition.

Ntile(n) : It will partition the result set into “n” numbers and give the rank to each record.

WHILE @I <= LEN(@str)
begin
-- 48 Is ASCII Key for '0' and 57 is for '9'
--Checking Each char is between 0 to 9 or not. If yes then concatenation to @int variable. Else concatenation with @var Variable.
IF @I = 1 SELECT @str 'Input String' , SUBSTRING(@STR,@I,1) 'First Char of the Input String', ASCII(SUBSTRING(@STR,@I,1)) AS 'ASCII Value of the First Char'

How to insert Identity value in a table?/*In the below example we have single Identity column table. Using with "Default Values" we can insert the values into table.*/
create table #tbl(id int identity)
go

Self Join is nothing but joining one table with different names. We can use Inner Join or Outer Join to join the table.
Here is the small example which gives the difference between consecutive days of temperature.