Skip to main content

Posts

FOREIGN KEY WITH DELETE CASCADE

DELETE CASCADE : Foreign key delete cascade means when parent table records is deleted the corresponding child table records also deleted. --CREATE DEMOS DATABASE CREATE DATABASE DEMOS USE DEMOS CREATE TABLE DBO . DEPT (             ID INT PRIMARY KEY ,             DEPTNAME VARCHAR ( 100 ) ) --INSERT DATA TO DEPT TABLE INSERT INTO DBO . DEPT ( ID , DEPTNAME ) VALUES ( 1 , 'SOFTWARE' ),( 2 , 'BPO' ) --CREATE EMP TABLE CREATE TABLE DBO . EMP (             ID INT IDENTITY ( 1 , 1 ) PRIMARY KEY ,             FIRTSNAME VARCHAR ( 100 ) ,             LASTNAME VARCHAR ( 100 ),             LOCATION VARCHAR ( 100 ),             DOB DATETIME ,             SALARY MONEY ,             DEPT INT ) --ADD FOREIGN KEY TO EMP TABLE ALTER TABLE DBO . EMP ADD CONSTRAINT FK_DEPTID FOREIGN KEY ( DEPT ) REFERENCES DBO . DEPT ( ID ) ON DELETE CASCADE ON UPDATE CASCADE --INSERT SOME DAT
Post a Comment
Read more
Recent posts

NULLIF function in SQL SERVER

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]
Post a Comment
Read more

Restrict SP_ (Naming convention) with DDL Trigger

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      @ObjectName = @EventData . value ( '(/EVENT_INSTANCE/ObjectName[1]' , 'NVARCHAR(100)' );                 if ( substring ( @ObjectName , 1 , 3 )= 'sp_' )                 begin                        
Post a Comment
Read more

Database changes with Audit Table

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 ),   Command nvarchar ( max ),   Createdon datetime   ) --Create DDL Trigger on Database create   trigger AuditDBChanges on database for create_table , alter_table , drop_table as begin   DECLARE @EventData XML =
Post a Comment
Read more

Rank Functions in SQL SERVER

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 ,                 FirstName varchar ( 100 ),                 LastName varchar ( 100 ),                 JoinDate datetime ,                 Salary int
1 comment
Read more