BEGIN TRANSACTION — BEGIN TRANSACTION should come before or after BEGIN TRY that is not equally important, but it feels more right to have it inside the TRY-CATCH section.
— YOUR STATEMENTS COMES HERE
COMMIT — Don’t write this COMMIT after end of CATCH block because if you get an error and wind up in the CATCH up section, you will roll back the transaction. When you exit the CATCH block, you will
attempt to commit, but there is nothing to commit, so there will be a new error.
THROW — THROWING the original exception to the application. THROW statement is available in SQL server 2012. RAISEERROR() vs THROW: http://sqlhints.com/2013/06/30/differences-between-raiserror-and-throw-in-sql-server/
END CATCH Continue reading
A “SET” expression sets the specified local variable created previously to the given value. Continue reading
;WITH cteSource(ID, Lead)
SELECT RecordId , LEAD(RecordId) OVER (ORDER BY RecordId) AS Lead FROM Records
SELECT ID,Lead FROM cteSource WHERE ID < Lead – 1
Query to get nth Highest Salary
Select TOP 1 Salary as ‘N Highest Salary’ from
SELECT DISTINCT TOP N Salary from Employee ORDER BY Salary DESC
ORDER BY Salary ASC
Replace N with the number. Continue reading
Collation refers to a set of rules that determines how your data is sorted and compared. It’s very often important with regards to internazionalization, e.g. how do you sort japanese kanji?
COLLATE is not a SQL command, it’s a SQL clause. Continue reading
The procedure is something like below will not execute because we can not pass table name as parameter and use in select statement inside SP. Continue reading
T-SQL supports the OUTPUT clause after the inception of SQL server 2005 and later editions. We can use the OUTPUT clause with DML statements (INSERT, DELETE, UPDATE) to return information from modified rows. Continue reading
There are solutions to this problem in other versions of the product in the form of temp tables, ROW_NUMBER() and TOP but I prefer OFFSET and FETCH to the others – it’s just simple! Continue reading
If you consider the below facts placed in a employee table with Id, Name, ph_no, Alt_no, Office no.
The above Employee table may have single value or three values. If it has single value, then it fills null values with remaining attributes.
When we retrieve the number from employee table, that number Should Not be Null value. To get not null value from employee table, we use Coalesce() function. It returns the first encountered Not Null Value from employee table.
The COALESCE and ISNULL T-SQL functions are used to return the first nonnull expression among the input arguments. SQL Server practitioners often wonder what the difference is between the two functions. Several differences exist; some are straightforward and are common knowledge, whereas others are less straightforward and are less well-known. Continue reading