SQL Server Interview Questions,

1) What is Trigger?

A trigger is a database object that is bound to a table. It is a special type of stored procedure that executes automatically when any DML operations are performed.

Microsoft SQL Server 2000 supports two different types of triggers namely INSTEAD OF and AFTER Trigger. Both this trigger is different in the execution.
It supports Multiple after Trigger also. Triggers cannot be created on the temporary tables.

After Trigger

After trigger automatically get executed before the transaction get completed or rollback. After trigger created only on tables not in views. This is very useful to maintain the data integrity between the tables.

CREATE TRIGGER after trigger

ON department

FOR INSERT AS

Print ('After Tigger is initiated')

GO

BEGIN TRANSACTION

DECLARE @ERR INT

INSERT INTO department(USERNAME, PASSWORD)VALUES('Ram','india')

SET @ERR = @@Error

IF @ERR = 0

BEGIN

COMMIT TRANSACTION

PRINT 'COMMIT TRANSACTION'

END

ELSE

BEGIN

ROLLBACK TRANSACTION

PRINT 'ROLLBACK TRANSACTION'

END--End of the program

INSTEAD OF Triggers

This trigger gets executed automatically before the primary key and the foreign key column constraint are checked. Whereas the traditional trigger (After Trigger) get executes automatically after all the check constraints are checked.

Triggers are a powerful tool that can be used to enforce the business integrity or data integrity automatically when the data is modified. Triggers should be used to maintain the data integrity only if you are not capable to enforce the data integrity using CONSTRAINTS, RULES.

2) To check whether the given column present in the table if present display the tables which contains this columns in the database

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER procedure [dbo].[sp_Search_Columns](

@column varchar(500)

)

as

Begin

set nocount on

declare @tname varchar(500),

@intNum int

--Create a temporary table to store the table having the columns

create table #Tables1(TableNames varchar(500))

Declare TableNames cursor for

--select all the tables from the database

Select Table_name from INFORMATION_SCHEMA.Tables order by TABLE_NAME

--verify for each table in the database whether the coloumn present or not

open TableNames fetch next from TableNames into @tname

while @@FETCH_STATUS=0

begin

--Check if the table contains the coloumn.

--if the coloumn is present in the

select @intNum=count(*) from information_schema.columns where table_name = @tname and column_name=@column

if(@intNum > 0)

--if the table contains the column then insert that table name in the temp table for further display

insert into #Tables1 values(@tname)

--point the pointer to check for the next table

fetch next from TableNames into @tname

End

close TableNames

--deallocate the curser

deallocate TableNames

--select all the tables which contains the column

select * from #Tables1

end

Out Put of the query :

EXEC sp_Search_Columns 'FirstName'

Tablenames

Account

COBInfo

COBInfo_backup

COBInfoHistory

3) How to find the number of days in a month

Create Function NumDaysInMonth (@dtDate datetime) returns intasBeginReturn(Select Day(DateAdd(Month, 1, @dtDate) - Day(DateAdd(Month, 1, @dtDate))))EndGo