SQL Tips & Tricks - Part - I

Current Database - SQL Server

What is the Current Database?
Select DB_NAME() as Curr_DB

--How to Get the Computer Name using SQL Query

Select Host_Name() as Computer_Name

First day of the week - @@datefirst

The first day of the week will be 7 - Sunday (default for US English)
Select @@datefirst as FirstDay

You can set it by :
Set datefirst 1;

No of Records using Select Query

-- No of Total Records using Select Query
Select count(*) from Purchasing.PurchaseOrderDetail

-- No of Records for each group using Select Query
Select PurchaseOrderID, count(*) NoOfRec from Purchasing.PurchaseOrderDetail Group By PurchaseOrderID

Coalesce Function

Displays the first nonnull expression among its arguments

-- returns Iam Not a Null

Select Coalesce (Null, Null, 'Iam Not a Null', Null) as NotNull

This function will be used to skip the NULL columns and get the ones that have value. This function can be maximum utilised in situations where you need to get one value from a group of columns that could contain null.

For example, Let us assume that you have a contact table with office_phno, mobile_nos, home_phnos in it and you need to send them greetings. You can use Coalesce Function to get the non null number according to priority

Select Coalesce (Office_phno, mobile_nos, home_phnos, 'No Ph No Available) as ContactNo