SQL Tips and Tricks Part 1

1.       Perform the update operation on a table where update operation refers to make a query to multiple tables
UPDATE [dbo].[Employee]
SET   Subject = sj.Name
FROM [Employee] emp
inner join dbo.[Subject] as sj on sj.SubjectID = emp.SubjectID
where emp.Subject = ‘anysubject’
2.   Inserting the result of SQL query into a table. This is very much similar to normal insert query with a difference that Values       keyword is skipped.
Ex.        Normal Insert Query
Insert into
Values
     Select Way
Insert into


3.  If you have multiple column and you want to fetch data from any one of them which is not null
Say we have a table named Employee with nullable columns HomePhone, OfficePhone, MobilePhone.
You want to fetch the column which is not null. In such a scenario use
Select Name, COALESCE(HomePhone,OfficePhone,MobilePhone) from Employee
4. What if you want to find the difference of two day. SQL provide with a very good method name     DATEDIFF.
Have a look,
DECLARE @startDate DATETIME
DECLARE @endDate DATETIME
SET @startDate = ’09-01-2010′
SET @endDate = ’09-05-2010′
SELECT DATEDIFF(DAY,@startDate,@endDate) — this will return 34
SELECT DATEDIFF(Year,@startDate,@endDate) — this will return 1
SELECT DATEDIFF(MONTH,@startDate,@endDate) — this will return 0
OR
SELECT DATEDIFF(DD,@startDate,@endDate) — this will return 34
SELECT DATEDIFF(YY,@startDate,@endDate) — this will return 1
SELECT DATEDIFF(MM,@startDate,@endDate) — this will return 0
OR
SELECT DATEDIFF(D,@startDate,@endDate) — this will return 34
SELECT DATEDIFF(Y,@startDate,@endDate) — this will return 1
SELECT DATEDIFF(M,@startDate,@endDate) — this will return 0
5.  Use @@Identity or SCOPE_IDENTITY to find out the Value inserted into the IdentiyColumn.
6. Use @@ROWCOUNT to find out the number of row effect in last trnasaction.
This is really of help if you are debugging your script and want to check the result of update operation
7. While you are still in writing the query and not sure if its working properly or not. Use of BEGIN TRAN. This will ensure that         changes you make are not really reflected in the database. If there is some problem with the query you call always use          ROLLBACK to revert back the changes.