SQL SERVER – How to work with Row Constructors , inserted and deleted tables and Handle Result Set

There are many ways to track changes made to the table in SQL Server one of the method is to use inserted and deleted tables and get the result set and use in code appropriately.

Step 1: Creating Table

create table Emp

(

empId int primary key identity,

class int,

empName varchar(30),

sal money,

empState varchar(25)

)

Step 2: Inserting values into the table

use row constructors or insert one by one

insert into Emp values

(2,’Dilip’,3000,’AP’),

(2,’Rahul’,5000,’DH’),

(3,’Mohan’,7000,’KR’),

(2,’Dilip’,3000,’AP’),

(2,’Rahul’,5000,’DH’),

(3,’Mohan’,7000,’KR’),

(4,’Dilip’,3000,’AP’),

(5,’Rahul’,5000,’DH’),

(6,’Mohan’,7000,’KR’),

(5,’Dilip’,3000,’AP’),

(6,’Rahul’,5000,’DH’)

Then,

Output clause

/* Local System*/

Updating

update Emp Set sal = sal +1

output inserted.empId,deleted.sal as [old sal], inserted.sal as [new sal]

where class =2

/* table Variable for server side maintenance*/

declare @change table

(

id int not null,

[old sal] money,

[new sal] money

)

update Emp Set sal = sal +1

output inserted.empId,deleted.sal as [old sal], inserted.sal as [new sal] into @change

where class =2

select * from @change

Deleting

delete Emp

output deleted.*

where empId=3 or empId = 6

Inserting

insert into Emp

output inserted.*

values

(13,’Dilip’,332.93,’JK’),

(14,’rakesh’,312.93,’GJ’)

For Updating,deleting and inserting we get a result set.

Update 21 Jan 2013

One of my reader Mini asked me that she is trying to insert multiple values into table using row constructor but it is giving error in the comma in the syntax ?

Ans:

1)  Result set are not supported in sql server expressed Edition

2) Inserting multiple rows or technically can be called as Row Constructors are new feature added in SQL Server 2008

if we try to execute the above code on SQL Server 2005 we will get an error at comma stating that it is not allowed, because this syntax is introduced in SQL Server 2008 and even when we are working on client server environment no matter what SQL Server you have if the server to whom you are connecting is previous versions of SQL Server 2008 it will not execute, so these things should kept into consideration.

Reference : Dilip Kumar Jena ( https://sqlexplore.wordpress.com)

SQL SERVER – CREATE INDEX myIndex ON myTable(myColumn)What type of Index will get created after executing the above statement

Non-clustered index. Important thing to note: By default a clustered index gets created on the primary key, unless specified otherwise.

Reference : Dilip Kumar Jena ( https://sqlexplore.wordpress.com )

SQL SERVER – What is an alias and how does it differ from a synonym

An alias is an alternative to a synonym, designed for a distributed environment to avoid having to use the location qualifier of a table or view.  The alias is not dropped when the table is dropped.

Reference : Dilip Kumar Jena ( https://sqlexplore.wordpress.com )

SQL SERVER – What is a NULL value and What are the pros and cons of using NULLS

A NULL value takes up one byte of storage and indicates that a value is not present as opposed to a space or zero value.

A NULL is not a space or empty string or a zero. But some value undefined telling that value is not present.

From front End we can send the value as NULL by DBNull.Value

And in SQL SERVER it can be searched by following query

use AdventureWorksDW2008R2
Go
Select * from dbo.DimCustomer where Suffix IS NULL

here it will return the rows with Suffix column containing a NULL means Noting.

Reference : Dilip Kumar Jena ( https://sqlexplore.wordpress.com )

SQL SERVER 2008 – What is an advantage to using a stored procedure as opposed to passing an SQL query from an application

A stored procedure is pre-loaded in memory for faster execution. It allows the DBMS control of permissions for security purposes. It also eliminates the need to recompile components when minor changes occur to the database.

Reference : Dilip Kumar Jena ( https://sqlexplore.wordpress.com )

 

SQL SERVER 2008 – What is database replication? What are the different types of replication you can set up in SQL Server

Replication is the process of copying/moving data between databases on the same or different servers. SQL Server supports the following types of replication scenarios: · Snapshot replication · Transactional replication (with immediate updating subscribers, with queued updating subscribers) · Merge replication See SQL Server books online for indepth coverage on replication. Be prepared to explain how different replication agents function, what are the main system tables used in replication etc.

Reference : Dilip Kumar Jena ( https://sqlexplore.wordpress.com )

SQL SERVER 2008 – How to restart SQL Server in single user mode AND How to start SQL Server in minimal configuration mode

SQL Server can be started from command line, using the SQLSERVR.EXE. This EXE has some very important parameters with which a DBA should be familiar with. -m is used for starting SQL Server in single user mode and -f is used to start the SQL Server in minimal configuration mode. Check out SQL Server books online for more parameters and their explanations.

Reference : Dilip Kumar Jena ( https://sqlexplore.wordpress.com )