About Me

My Photo
Mumbai, Maharastra, India
He has more than 4.2 years of experience in the software development. He has spent most of the times in web/desktop application development. He has sound knowledge in the ASP.Net, C#, VB,SQL Server, AJAX. You can reach him at viki_keshari@yahoo.co.in or viki.keshari@gmail.com http://www.facebook.com/viki.keshari

Sunday, June 28, 2015

Querying employee salary along with maximum salary of his department


I found a very interesting question from one of the user of SQL group, his problem statement was

“I need to find each employee salary along with the max salary of his department with a single line query”

It is very easy to find using SQL ‘Over by with Partition By clause’

Lets demonstrate it with an example. Here I am creating Emp and Dept table and inserting few records in it.

create table Dept1
(dept_id int identity(1,1) primary key,
dname varchar(20))
Command(s) completed successfully.

create table Emp2
(emp_id int identity(1,1) primary key,
emp_name varchar(20),
dept_id int references Dept1(dept_id),
salary float)
Command(s) completed successfully.

insert into Dept1
select 'HR' union all
select 'IT' union all
select 'Account'

insert into Emp2
select 'Sudeepta', 1, 1000 union all
select 'Susmita', 1, 3000 union all
select 'Smruti', 3, 10000 union all
select 'Richa', 3, 8000 union all
select 'Sanjana', 1, 4000 union all
select 'Sweta', 2,4000 union all
select 'Deepa', 2,7000

Now since the data are there in the table, lets write query to find each employee salary along with the maximum salary for his department.

select e.emp_name, d.dname,e.salary, max(e.salary) over (partition by d.dept_id) MaxSalary from
Emp2 e inner join Dept1 d
on e.dept_id = d.dept_id

emp_name             dname                salary                 MaxSalary
-------------------- -------------------- ---------------------- ----------
Sudeepta             HR                   1000                   4000
Susmita              HR                   3000                   4000
Sanjana              HR                   4000                   4000
Sweta                IT                   4000                   7000
Deepa                IT                   7000                   7000
Smruti               Account              10000                  10000
Richa                Account              8000                   10000

(7 row(s) affected)

So here we can from the output we have salary for each employee along with the maximum salary of his department. We have simply used aggregated function max along with ‘over with partition by clause’ which is taking d.dept_id to partition the record to find the max salary of his department.

I can’t sleep …until I die…My passion SQL :)


Post Reference: Vikram Aristocratic Elfin Share

Thursday, June 4, 2015

Generating series, now it’s very simple using recursive CTE


A interesting question asked by one of my Community friend to generate series of 4,8,16…. Without using any kind of looping structure.

This is very simple with the recursive CTE, lets see how we are implementing the solution

; with sam_series (myrow) as (
select 4 as myrow
union all
select myrow+ myrow from sam_series
where myrow<1000
)

select * from sam_series;
 myrow
-----------
4
8
16
32
64
128
256
512
1024

If we dissect the above CTE we can see

The anchor member is:  select 4
Recursive invocation member is: select myrow + myrow which is tied with Union All.
Termination Point is: myRow < 1000.

Conclusion: CTE provides the significant advantage of being able to reference itself, thereby creating a recursive CTE.

Get your cup filled with Tea … Enjoy coding  :)


Post Reference: Vikram Aristocratic Elfin Share

Wednesday, June 3, 2015

First insertion will always takes the seed value, all other insertion following first will take seed/reseed value and add increment factor to form the identity value


Lets demonstrate it with the help of a temporary table, here we are creating a table with identity column and set the seed value to 1 and increment factor to 1

create table #tempReseed
(id int identity(1,1),
col1 varchar(10));
Command(s) completed successfully.

Now before we execute any insert statement on the table we are Reseed the Identity value to 15

dbcc CHECKIDENT('#tempReseed',reseed,15)
Checking identity information: current identity value 'NULL'.
DBCC execution completed.

Now lets insert a records and check what value will get inserted to id column.

insert into #tempReseed(col1) values('ABC')
(1 row(s) affected)

Querying the table after insertion.

select * from #tempReseed
id          col1
----------- ----------
15          ABC

Here we found the id is set with reseed value i.e. 15

Lets again reseed the identity value, this time to 25

dbcc CHECKIDENT('#tempReseed',reseed,25)
Checking identity information: current identity value '15'.
DBCC execution completed.

Lets fire an insertion after reseeding identity value to 25

insert into #tempReseed(col1) values('DEF')
(1 row(s) affected)

Lets query the table to find what gets inserted this time.

select * from #tempReseed 
id          col1
----------- ----------
15          ABC
26          DEF

Cool!!! This time Reseed value was added with the incremented factor i.e. 1 an new value formed was 25+1 =26 which gets inserted.

Conclusion: First insertion will always takes the seed value, all other insertion following first will take seed/reseed value and add increment factor to form the identity value.

Code and enjoy … very sync :)

Post Reference: Vikram Aristocratic Elfin Share

Monday, April 27, 2015

SCHEMABINDING: You need to use schema qualified names for everything


A simple definition: Schema binding is a mode of ensuring that the objects referenced within a function or view, do not changes their definition in any way tat would crack the binded objects.

With SCHEMABINDING the obstacle is that you need to use schema qualified names for everything. You will get lots of error messages like this

Lets stimulate the same error with the below sample. Here we are creating table without any qualified schema

create table test1
(col1 int,
col2 varchar(10),
col3 varchar(10))
go

Since our table is ready lets try to create a view and refer the above table in it.

 create view vw_test1
with schemabinding
as
select col1,col2 from test1

Msg 4512, Level 16, State 3, Procedure vw_test1, Line 10
Cannot schema bind view 'vw_test1' because name 'test1' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

Here we can see our code fall in error, since we have not specified any schema for our view. Lets add a schema and bind our above view to the newly created schema.

create view dbo.vw_test1
with schemabinding
as
select col1,col2 from test1

Msg 4512, Level 16, State 3, Procedure vw_test1, Line 10
Cannot schema bind view 'dbo.vw_test1' because name 'test1' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.


So you need everything i.e every object to be schema bounded.
Lets drop the table and recreate it with full qualified name

drop table test1
Command(s) completed successfully.

create schema test
Command(s) completed successfully.

create table test.test1
(col1 int,
col2 varchar(10),
col3 varchar(10))
go
Command(s) completed successfully.

Lets now create view with fully qualified name
create view dbo.vw_test1
with schemabinding
as
select col1,col2 from test.test1

Command(s) completed successfully.
col1        col2       col3
----------- ---------- ----------

(0 row(s) affected)

Conclusion: You need to create every object under full qualified name i.e. defined with schema while using SCHEMABINDING option.

Leave everything and do your coding; that’s the way to happiness. Just for SQL lovers :)


Post Reference: Vikram Aristocratic Elfin Share