Parse the Sql query with out executing it.

declare @sql nvarchar(max)
set @sql = 'select * FROM Students'
declare @testsql nvarchar(max)
declare @result int =1
set @testsql = N'set parseonly on; ' + @sql
BEGIN TRY
exec @result = sp_executesql @testsql
END TRY
BEGIN CATCH
PRINT 'ERROR’
END CATCH
Select @result
-- If it worked, execute it
if @result = 0
begin
exec sp_executesql @sql
end

Explain transactions and transaction modes in sql server?

A transaction is a set of dml operations which must be make perminent as a whole or must be undone as a whole

Sql server runs in following transaction modes.

1.Autocommit
2.Implicit transaction mode.
3.Explicit transaction mode.

AutoCommit: If sql server runs in autocommit mode then the transactionwill be started automatically when user submits dml command and transaction ends with commit automatically.
In Autocommit mode user cannot control transactions


Implicit Transaction mode : In this transaction starts automatically with any dml command but transaction ends with commit or rollback

Ex

updaet or sql statement--transaction starts
commit/rollback -- transaction ends

Explicit transaction mode : In explicit transaction mode the transaction starts with 'begin transaction' command and the transaction ends with 'commit transaction' or 'rollback transaction'

begin transaction
update statement
commit/rollback transaction


set implicit_transaction on --> it sets the implicit transaction mode on.

Explain about the cursor?

cursors are used to process multiple records(row by row processing)

A cursor is a pointer or handler to context area which stores records returned select statement.

Using cursors in a single request we can get required data from server can be loaded into client side temporary memory called context area.

Steps to create a cursor
1.declare a cursor

syntax: declare cursorname cursor for select statement.
Ex
Declare c1 cursor for select eno, ename, sal from emp

Some memory is created or allocated when we declare a cursor. that memory is called context area.

2. Open a cursor

Syntax: open <cursor name>
open c1;

when we open a cursor.
1.select statement is submitted to db server.
2dbserver executes the select statement and returns data and data returned by select statement is loaded into context area.
3.cursor will point to a context area.

3.Fetch the records from cursor

Syntax:
Fetch first\next\last\prior\absolute\relative n from <cursorname> into variables

Ex

Fetch next from c1 into @eno, @sal, @comm

Fetch statement can fetches only one record at a time to process multiple records fetch should be executed number of times
Fetch statement should be placed inside a loop.
4.Closing a cursor

syntax:
close cursorname;
ex
close c1;

5. Deallocate cursor

When the cursor is closed the point to context area is removed.
when the cursor is de allocated the memory allocated for cursor is released.

deallocate cursorname
deallocate c1;


Ex

declare @ename varchar(50)
declare @sal smallmoney

declare c1 cursor for select ename,sal from emp
open c1
fetch next c1 into @ename,@sal
while(@@Fetch_status=0)
begin
print @ename +'earns' +cast(@sal as varchar(20))
Fetch next from c1 into @ename, @sal
end
close c1
deallocate c1

Differences between clustered index and non clustered index in sql server?

Non-Clustered Index : If the order of values in index and order of values in the table are not same then it is called non clustered index.

Clustered Index: If the order of records in the table and order of the records in the index is same then it is called clustered index.

Non-Clustered Index : Non clustered index will be created automatically one you create unique key on a column.

Clustered Index : Clustered index will be automatically once you create a primary key.

We can have only one clustered index for a table.

we can create up to 249 non clustered indexes on a table.

Non-Clustered Index : The logical order of the index does not match the physical stored order of rows on disk.

Clustered Index : The logical order of the index matches the physical stored order of rows on disk.

Clustered Index is faster than non-clustered index. 
Because the leaf level of a clustered index is the actual 
data and the data is resorted in case of clustered index. 
In case of non-clustered index the leaf level is actually a 
pointer to the data in rows. The data is stored in one 
place, the index in another, with pointers to the storage 
location of the data. The items in the index are stored in 
the order of the index key values, but the information in 
the table is stored in a different order.

What is the trigger and explain the types of triggers?

A trigger is an operation that is executed when some kind of event occured in database. It can be an object or data change.

Types of triggers

DML Triggers

     After Triggers(only on table)

     Instead of triggers(table and views)

DDL Triggers

SQL CLR triggers

 After Trigger: If the trigger is instead of trigger then sql server executes trigger body instead of executing the DML operation.

Ex

create trigger trg1 on emp

after insert,update,delete

as

begin

if datename(dw,getdate())='sunday'

begin

rollback

raiseerror('Today is sunday',15,1)

end

end

Instead of trigger : If the trigger is instead of trigger then sql server executes trigger body instead of executing the DML operation.

CREATE TRIGGER INSTEADOF_TR_I_EmpQualification 
ON vw_EmpQualification
INSTEAD OF INSERT AS
BEGIN

DECLARE @Code TINYINT
SELECT @Code = qualificationCode 
FROM lib_Qualification L INNER JOIN INSERTED I
ON L.qualification = I.qualification

IF (@code is NULL )
BEGIN
RAISERROR (N'The provided qualification does not exist in qualification library',
16, 1)
RETURN
END

INSERT INTO employees (empcode, name, designation,qualificationCode,deleted) 
SELECT empcode, name, designation, @code, 0 
FROM inserted 
END
GO

Rules of triggers

cannot create or modify dbobjects.

cannot perform administrative tasks

cannot pass any parameters

cannot directly call triggers.

Advantages of triggers

-----------------------

Triggers are useful for auditing data changes or auditing database as well as managing the business rules.

Can access both new values and old values in data base when going to do any insert, update and delete.

Disadvantages of triggers

Triggers hide db operations

It effects performance

DDL triggers can be created in database or server. If you want to monitor table creations and drops you can use ddl triggers

Joins available in sql server.

Equi join or inner join
Non-equi join
Self join
Outer join
Cross join

Inner join : join the table based on the common column(s) available in both the tables.
ex
select e.ename,d.dname from emp e inneroin department d on d.deptno=e.deptno

non-equi join: joining 2 tables not based on the common column.
ex
select e.name,g.grade from emp e innerjoin grade g on e.sal between e.lowsal and e.highsal

self join: Joining a table to itself is known as self join
it is performed if it has self referential intigrity

ex

select x.ename,y.ename as mgrname from emp x join emp y on x.managerid=y.empid

outer join : returns unmatched records from the tables.
Left outer join and right outer join

cross join: It returns cross product of 2 tables.
if we submit the select statement with out join condition then sql server performs cross join.

select e.ename, d.dname from emp e dept d

What are the acid properties in sql server?

In sql server every transaction should follow acid properties
Atomicity
Consistency
Isolation
Durability

Atomicity: transaction ensures that either modifications are committed or not committed.
Consistency: The data should be in consistent state when transaction process is completed.
Isolation: A transaction work in isolation and does not allow other transactions to work concurrently on same piece of work.
Durability: Data is permanent once transaction is completed  and it can be recovered when system fails.

what are the differencces between where and having clause

where clause
To filter data before 'group by' use where clause.
To select particular groups use where clause.
If condition does not have aggregate functions use where clause.

Having Clause
To filter data after group by use having clause.
To select particular groups use having clause.
Used to apply functions on aggregate functions.

Date functions available in sql server

Day(getdate())-- Get the current day
Month(Getdate())--Get the current month.
Year(Getdaet())-- Gets the current year

Datepart(interval,date)

Intervals are "yy,  mm, dd, hh,  m1, ss, dw(Day of week).

select datepart(dw,Getdate()) output is "1"

select datename(dw, Getdate()) output is "monday"

DataAdd() function
Used to add years or months to particular date.

DateAdd(yy,1,getdate()) --It adds 1 year to current year.

DateDiff(interval,date1,date2): returns difference between 2 dates.
datediff(yy,getdate(),'11-feb-2012');

Updatin and deletion rules in sql server

Delete rules
On delete No action.
On delete set null.
On delete cascade.
On delete set default.

These rules coming into when we define the parent child relationship between 2 tables in sql server.

we need to define the rule while creating the child table. I mean to say while defining the foreign key we need to define the rules. the same thing applies for updation also

ex
Create table emp(eno int primarykey, ename varchar(50), Dno int references dept(dno) on delete no action)


Updation Rules
On update No action.
On update set null.
On update cascade.
On update set default.

What is self referential intigrity in sql server?

A foreign key in one table refers to primary key of same table is known as self referential intigrity.

ex:

Create table emp(eno int primary key, ename varchar(50), Manager int references emp(empno).

copying data from one table to another table

Insert into <destination table name> select * from <source Name>
ex:
insert into Emp_History select * from Employee.

Here 2 tables must be exists.

Create a table from another table 


select * into <newtable> from <old table>re  [where condition]

ex:
Create new table from existing table along with same data

select * into emp_backup from emp;

Create only structure from the existing table

select * into newemp from emp where 1=2;

Order of declaration and execution od a sql statement

Order of declaration
select
from
where
groupby
having
orderby

Order of execution

From
where
groupby
having
selecct
orderby

Facts about Temporary table and table variables.

·          Both  are stored in TempDB.
      Most o people think table variables does not store in database anywhere.

·          Temporary table will have better performance compared to table variables why because sql server uses statistics while working with the queries which contains Temporary tables but this would not happen for table variables.

      Table variables will get expired once the execution is done but Temporary tables exist till the current session is closed

·         In functions we cannot use temporary tables If you use you will get compilation errors. In this case we need to go with table variables.

      In dynamic queries also we cannot use table variables but we can use temporary tables.

·         Guide lines says that it is better to use table variables if the data is minimal. If your query is dealing with large amount of data it is better to go ahead with Temporary tables why because we will benefit from the statistics and we can create indexes on temporary tables if required.