Tech Learn

It accepts the two values, if both the values are matched then it will return NULL otherwise it will return first value. Examples-1: Declare @FisrtName varchar ( 100 ), @LastName varchar ( 100 ) Set @FisrtName = 'Rakesh' Set @LastName = 'Rakesh' Select NULLIF ( @FisrtName , @LastName ) As [NULLIF] Example-2: Declare @FisrtName varchar ( 100 ), @LastName varchar ( 100 ) Set @FisrtName = 'Rakesh' Set @LastName = 'Kalluri' Select NULLIF ( @FisrtName , @LastName ) As [NULLIF]

Sometimes naming convention also impact the performance problem in SQL SERVER. Whenever we creating the stored procedures we are giving the name just like SP_Get_Customer. SP_ it first checks the Master Database system procedures. All the system procedures stored in Master Database. In this article we do not allow SP_ whenever stored procedure creating by using DDL Trigger. --Create Student Table create table Student (                               id int identity ( 1 , 1 ) ,                 Name varchar ( 100 ) ) --Create DDL Trigger on Database create   trigger Restrict_Sp_NameConvenction on database for create_Procedure as begin declare @EventData XML = EVENTDATA (), @ObjectName Nvarchar ( 100 ); select   ...

In Recent I faced the problem whenever we need to move the DB Changes to Production environment we have some table in the Development environment .we have added some columns to table in Development. we need to change definitions in production environment also .At this time we are some little bit of problem facing . Today I come up with the solution we need to create the DDL trigger on Database level. Whenever we are moving the DB Changes to Production environment we can cross check audit table.   --Create Student Table create table Student (                               id int identity ( 1 , 1 ) ,                 Name varchar ( 100 ) ) --Create Audit Table   create table Audit   (   EventType nvarchar ( max ), ...

1 . ROW_NUMBER () OVER ( [PARTITION BY CLAUSE] < ORDER BY CLUASE >): Returns the sequantial number of a row within the a partition of result set at 1 for the first row of the each partition. 2. RANK () OVER ( [PARTITION BY CLAUSE] < ORDER BY CLUASE >): Returns rank for rows within the partition of result set. 3. DENSE_RANK () OVER ( [PARTITION BY CLAUSE] < ORDER BY CLUASE >): Returns rank for rows within the partition of result set.With out any gaps in the ranking. 4. NTILE ( INTEGER_EXPRESSION ) OVER ( [PARTITION BY CLAUSE] < ORDER BY CLUASE >): Distributes the rows in an ordered partition into a specified number of groups. Examples: --create Employee table create table Employee (                 EmpId int identity ( 1 , 1 ) primary key ,              ...

OFFSET -FETCH option is filltering option that like ,top you can use fillter the data based on number of rows. Difference between TOP and OFFSET- FETCH Is OFFSET-FETCH it skippes the rows.In TOP Option it will not skip. The OFFSET-FETCH option is rigth after the order by . The order by is required when OFFSET-FETCH option includes. The OFFSET-FETCH option intorduced in SQL-SERVER 2012. We can use OFFSET-FECTH option implement pagenation. --create Employee table create table Employee (                 EmpId int identity ( 1 , 1 ) primary key ,                 FirstName varchar ( 100 ),                 LastName varchar ( 100 ),                 JoinDate ...