Sunday 24 August 2014

SQL Server 2012 in-built functions

Hi,

In this blog, I have discussed Sql Server in-built functions to be used in T-sql, queries, DML's. I have detailed them and have provided example of each function. Any suggestion,correction would be highly appriciated.



--Math Functions
--Average function
Select avg(Person_id) avg from db_owner.person

--CharIndex function to find index of a particular alphabet. In example
--CHARTINDEX finds 'a' in first_name column of person starting from 1st character of
--string
Select CHARINDEX('a',First_Name,1)a,first_name from db_owner.Person

Use MS2012;
go

declare @avar xml
RETURN @avar.query('Product/Prices')


truncate table db_owner.person


delete * from db_owner.person where middle_name is null

create table t1(id int identity(1,1) default 1)

select * from db_owner.person

select sum(person_id),count(*) ,first_name from db_owner.person


select top(10)
desc sp_settrigger

declare @var1 nvarchar(20);
select @var1 ='My Example';
Select CharIndex ('m',@var1,1) Col1,
CharIndex('P',@var1,1) COl2,
CharIndex ('x',@var1,1) Col3
GO

---Concat Function

Select ConCat ('Mr.',' Jaidev ',' sharma')

Select Concat(First_name,'--',Last_name) from db_owner.Person


---Left function shows number of character from left side of string
-- in example first four character from left would be displayed
Select Left('jaidev sharma',4)

--Len function to get the total length of a string
Select len('jaidev')
Select len(first_name) leth,First_Name from db_owner.person
---Lowever and upper
Select lower('Jaidev'),upper('jaidev')
Select lower(first_name),upper(Last_Name) from db_owner.person
---Ltrim,Rtrim,trim removes blank spaces

select Ltrim('   jaidev')y,Rtrim('Jaidev ')x

Declare @var1 nvarchar(50);
set @var1 = '   My Name is Jaidev Sharma';
Select Ltrim(@var1)

--Substring to get a portion of a string starting from 2nd parameter and upto
--3rd parameter
Select substring('Jaidev',3,3)

Select First_Name,SubString(First_name,1,len(first_name)) nm from db_owner.person

--Find a particular characher left and right of a string
Select PATINDEX(lower('%S%'),'Australia')

Select First_name from db_owner.person

Select PatIndex('%o%',First_Name),First_Name from db_owner.Person

----Replace find a particular character give in 2nd parameter and replaces it
---a parameter give in 3 parameter. In example in 'jai' string 'a' is replaced with 'u'
---

Select Replace('jai','a','u')

Select First_name,Replace(First_name,'a','h') from db_owner.person

Select count(person_id) emp_count from db_owner.person

---++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
---TSQL Date and Time Data Types and Functions

   Select @@DATEFIRST
  
   --to get current_Timestamp
  Select CURRENT_TIMESTAMP

--to add date in one particular date. In example 1, I have added DAY,41 (no. of days) in to 16-Jun-2014 to see counting 41 from 16-jun what date
--would fall.
---eg 1
Select DATEADD(day,41,'2014-06-16')
--eg 2 in this I have added Month 1 (one month) in todays date
Select DateAdd(Month,1,SysDateTime())
--eg3 In this I have added Year 1 ( one year) in the today's date.

Select DateAdd(Year,1,SysDateTime())

---DateDiff function tells us the difference between to dates.

Select DateDiff(day,'2014-06-16','2014-07-28') --42 days

Select DateDiff(Month,'2014-06-16','2014-07-28') --1 month

Select DateDiff(Year,'2014-06-16','2014-07-28')

---DATEFROMPARTS

Select DateFromparts(1,2,3)

----DATENAME return a specific information (eg. Year,Month,Day, Day of Year or week) of a date. To get just year portion of date use year,

Select DateName(Year,CURRENT_TIMESTAMP) year
Select DateName(Month,CURRENT_TIMESTAMP) Month
Select DateName(day,Current_TimeStamp) Day
Select DateName(DayOfYear,Current_TimeStamp) Day_Of_Year
Select DateName(Week,Current_TimeStamp) WeekNo

----DATEPART  Similar to DateName
Select DatePart(Month,Current_TimeStamp) Month
Select DatePart(Year,Current_Timestamp) Year
Select DatePart(Day,Current_TimeStamp) Day

--DATETIMEFROMPARTS converts integers in Data Format
Select DateTimeFromParts(2014,3,22,13,22,13,14) Dat1
--DATETIME2FROMPARTS
---Select DATETIME2FROMPARTS(2014,3,12,12,03,20,12,1)
SELECT DATETIME2FROMPARTS(2014, 2, 22, 8, 53, 39, 7, 1) as Date_1
--DAY to know current day of the month
 Select day(current_timestamp)
 --EOMONTH end of month
Select EoMonth(CURRENT_TIMESTAMP)
--  GETDATE()
  Select GetDate()
--GETUTCDATE()
  Select GETUTCDATE()
---ISDATE checks the format of date. If format is right than it return 1 otherwise 0
  Select IsDate('14-03-02')-- Returns 0 as date format is wrong
  Select SYSDATETIME()
  Select IsDate(CURRENT_TIMESTAMP) --Returns 1 as date format provided by Current_TImeStamp is right
---- MONTH
Select MONTH(current_timestamp)
Select year(CURRENT_TIMESTAMP)

--SmallDateTimeFromParts
Select SMALLDATETIMEFROMPARTS(2014,07,29,1,04)

    --SWITCHOFFSET
---    SYSDATETIME returnes system time stamp
Select SysDateTime()

 --   SYSDATETIMEOFFSET
 --   SYSUTCDATETIME
--    TIMEFROMPARTS
 --   TODATETIMEOFFSET
  
----TSQL System Functions

---
  select  @@CONNECTIONS,getdate();

--@@Error to knowif error any came

  Use Ms2012;
  Go
  Select * from db_owner.gender;
  Update db_owner.Gender set Gender='UnKnown' where Gender_id=3;
  delete from db_owner.gender where Gender_id=4;
  if @@ERROR <> 0 Print N'There is no Gender Id 4'
  Select * from db_owner.gender;
  GO


-- @@IDENTITY to know last value IDENTITY value inserted
-- @@ROWCOUNT to see number of rows effected by a T-Sql Statement

use MS2012;
go
Update db_owner.gender set Gender='Male' where Gender_id=1;
Update Db_owner.Gender set gender='Female' Where Gender_id=2;
Update Db_owner.Gender set gender='Unkown' Where Gender_id=3;
if @@ROWCOUNT<> 0
  print Concat('The number of rows affected are ' , @@Rowcount);
if @@ROWCOUNT=0
    Print Concat(@@ROWCOUNT, ' no row effected' );
Select * from db_owner.gender;
go

-- COALESCE equal to NVL function of Oracle
Alter Table Db_owner.person
alter column middle_name nvarchar(20) null

Select * from db_owner.person
Update db_owner.Person set Middle_name=null where Person_id=103



Select First_name,Coalesce(Middle_name,null,'UnKown') from db_owner.Person

--ERROR_LINE returns the error at particular line when using with Try and Catch block
USE model;
GO
BEGIN TRY
   SELECT 8/0;
END TRY
BEGIN CATCH
   SELECT ERROR_LINE() AS ErrorLine;
   Select ERROR_MESSAGE () As ErrorMessage
   Select ERROR_NUMBER () As ErrorNumber
   Select ERROR_PROCEDURE() as Proce
   Select ERROR_SEVERITY () as Sever
   Select ERROR_STATE() as Stat
END CATCH;
GO
/*  ERROR_MESSAGE
    ERROR_NUMBER
    ERROR_PROCEDURE
    ERROR_SEVERITY
    ERROR_STATE*/

---Host_Id
Select HOST_ID() as HostId
---HOST_NAME
Select Host_name() HostName

---IsNull
Select ISNULL(NUll,'Jai')
Select * from db_owner.person
select IsNull(Middle_name,'Kumar (replaced)'),Middle_name from db_owner.person
---ISNUMERIC either 1 or 0 if 1 than parameter is number
Select ISNUMERIC('a')
  
--- NULLIF checks if two expressions are same or not. If same than returns
--null otherwise value of first parameter

Select NullIf('a','a') col1,NullIf('a','b'),NullIf('jai','jai')

----Security Functions
---   CURRENT_USER if connect by windows authentication or sa than its show DBO.
 Select Current_user as currentUser
 Select ORIGINAL_LOGIN() as OrgLogin

--Session user shows the user of sesison. normally it s same as current user function.
Select SESSION_USER

--Shows the user at OS level connected to sql server
Select SYSTEM_USER

--User_Name tell the name of user at sql server level   
Select USER_NAME()

TSQL Metadata Functions

--App_Name() gives the name of the applicaiton.In following example it gives  'Microsoft Sql Server Management Stuido - Query' output
Select   APP_NAME ()

--DB_ID returns the number assigned to a database
Select DB_ID() 
--DB_Name name of database
Select  DB_NAME() DBNAMe

---Object_Definition gets the definition of an object
Select * from sys.objects
where name='PERSON'

select * from ms2012.db_owner.gender

Select OBJECT_DEFINITION(object_ID('ms2012.db_owner.gender'))

--Object_id to know the object id

Select Object_id('ms2012.db_owner.person')

--Ojbect Name to know the object name

Select OBJECT_NAME(437576597),Object_id('ms2012.db_owner.person')

Select OBJECT_SCHEMA_NAME(437576597,8)

Select SCHEMA_ID('db_owner')

Select SCHEMA_NAME(16384)


---TSQL Configuration Functions

    Select @@LOCK_TIMEOUT as TimeOutOfLog

    Select @@MAX_CONNECTIONS as MaxConn

    Select @@SERVERNAME as SrvrNm

    Select @@SERVICENAME as SrvcNm

    select @@SPID as SPID