About Me

My photo
Mumbai, Maharastra, India
He has more than 7.6 years of experience in the software development. He has spent most of the times in web/desktop application development. He has sound knowledge in various database concepts. You can reach him at viki.keshari@gmail.com https://www.linkedin.com/in/vikrammahapatra/ https://twitter.com/VikramMahapatra http://www.facebook.com/viki.keshari

Search This Blog

Saturday, November 29, 2014

Finding lowest date among each category


Here we have data scattered in table like below
Subject                        Date
------------------------------ -----------------------
Data & File Structure          2014-11-18 10:12:57.767
System Software                2014-11-26 10:12:57.767
DBMS                           2014-11-18 10:12:57.767
Data & File Structure          2014-11-24 10:12:57.767
DBMS                           2014-11-16 10:12:57.767
Data & File Structure          2014-11-25 10:12:57.767
System Software                2014-11-23 10:12:57.767
Data & File Structure          2014-11-24 10:12:57.767
DBMS                           2014-11-29 10:12:57.767

Now we are interested in finding lowest date for each subject.

Let’s create table and try to find the solution using t-sql concepts.

create table Class
([Subject] varchar(30),
[Date] datetime)
Command(s) completed successfully.

So here our table is ready, lets insert data according to problem statement.

insert into Class
select 'Data & File Structure', getdate() - 12 union all
select 'System Software', getdate() - 4 union all
select 'DBMS', getdate() - 12 union all
select 'Data & File Structure', getdate() - 6 union all
select 'DBMS', getdate() - 14 union all
select 'Data & File Structure', getdate() - 5 union all
select 'System Software', getdate() - 7 union all
select 'Data & File Structure', getdate() - 6 union all
select 'DBMS', getdate() - 1

Now our table is ready with data. Ok, its time to fire a query taking both the column under group by

select Subject,Date from Class
group by Subject, Date

Subject                        Date
------------------------------ -----------------------
Data & File Structure          2014-11-18 12:33:53.440
Data & File Structure          2014-11-24 12:33:53.440
Data & File Structure          2014-11-25 12:33:53.440
DBMS                           2014-11-16 12:33:53.440
DBMS                           2014-11-18 12:33:53.440
DBMS                           2014-11-29 12:33:53.440
System Software                2014-11-23 12:33:53.440
System Software                2014-11-26 12:33:53.440

Now, we are interested in finding lowest date among each subject, so we can apply MIN aggregate function to date field taking subject as group by field.

select Subject,min(Date) from Class
group by Subject
Subject                       
------------------------------ -----------------------
Data & File Structure          2014-11-18 12:33:53.440
DBMS                           2014-11-16 12:33:53.440
System Software                2014-11-23 12:33:53.440

Yup we got our work done; this is what we were expecting.

Get your Sofa in washroom and do code J


Post Reference: Vikram Aristocratic Elfin Share

Calling a procedure from Trigger?


With a small demonstration we will see how we can call a stored procedure from a trigger.
Here we are creating a table Employee

create table tbl_Employee
(id int identity(1,1),
name varchar(20))
Command(s) completed successfully.

Now lets insert couple of records into newly created table.

insert into tbl_Employee
select 'Akansha'  union all
select 'Aurpita'

Since our table is ready, we can now create a procedure, which we will be calling inside a trigger.

create procedure sp_DisplayEmployee
as
       select * from tbl_Employee
go
Command(s) completed successfully.

At this point, we have everything ready, table, procedure, so now turn to create trigger on table Employee for Insert and Update operation and call sp_Display function in it.
We have very simple logic inside trigger i.e. whenever any insert or update commands fired on Employee table, this trigger will comes into picture and display the original data in the table.

create trigger trg_Employee
on tbl_Employee
for insert,update
as
       exec sp_DisplayEmployee
go
Command(s) completed successfully.

Our trigger is now ready, lets fire update statement to see whether it will fire associated trigger.

update tbl_Employee
set name = 'Lopamudra'
where id =4
id     name
1      Akansha
2      Aurpita

Great, so with update statement, it fires trigger and in return trigger call sp_DisplayEmployee stored procedure to display records.

Conclusion: You can call procedure inside trigger.

Get your washroom ready for your coding :)

Post Reference: Vikram Aristocratic Elfin Share

Saturday, November 22, 2014

How to convert scientific number to decimal?

Suppose you have number in scientific form and you want to migrate these number to a decimal field. How you approach to do this since cast function to decimal fails with scientific numbers

Now if have a number like this
'0.23e10'
Lets try to cast it to decimal(10,5)

select cast('0.23e10' as decimal(10,5))
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

So its fail. Ok lets try to cast it to float type.

select cast('0.00023e4' as float)
FloatCasted
----------------------
2.3

That’s great, now lets try to convert to decimal.
select cast(cast('0.00023e4' as float) as decimal(10,5)) decimalCasted
FloatCasted
---------------------------------------
2.30000

That’s workaround for scientific to decimal conversion.

Get your tea cup and start coding J

Post Reference: Vikram Aristocratic Elfin Share

How to make use of NI with Table variable with SQL Server 2014

Non clustered index in table variable is a new feature that rolls out with SQL Server 2014, but how do we make use of it, let see with an example.

Here I am creating a table variable @IndexDemo with id as primary key and stateprovinceid as non clustered column. Lets query the table variable and see whether it is using non clustered index.

declare @IndexDemo table (
       id int identity primary key clustered (i),
       city varchar(60),
       stateprovinceid int,
       index ix_stateprovinceid nonclustered (stateprovinceid)
);

insert @IndexDemo (city,stateprovinceid)
select city, stateprovinceid
from person.address;

select city
from @IndexDemo
where stateprovinceid=1;
go

Now lets check the execution plan



Here we saw our non clustered index didn’t comes into play, instead clustered index scan takes play, very odd. Let’s dig into query to find more on it

select city
from @IndexDemo
where stateprovinceid=1;
go

So if we see our query, since our predicate is on non clustered index column i.e stateprovinceid, it should do a NI seek operation then a lookup to clustered index to get city field Values.

Lets do one thing, lets modify the query to select on stateprovinceid instead of city.


declare @IndexDemo table (
       i int identity primary key clustered (i),
       city varchar(60),
       stateprovinceid int,
       index ix_stateprovinceid nonclustered (stateprovinceid)
);

insert @IndexDemo (city,stateprovinceid)
select city, stateprovinceid
from person.address;

select stateprovinceid from @IndexDemo
where stateprovinceid=1;
go

Lets see the execution plan for the same



So here we can see from execution plan that non clustered index on sateprovinceid come into play, that means when you have key lookup for clustered index, the optimizer ignoring non clustered index seek and doing clustered index scan in case of @table variable indexing.

Summary: If you are thinking to make use of non clustered index on @table variable then don’t surround your select statement columns other then non clustered index columns.

Lets rejoice, doing codingJ


Post Reference: Vikram Aristocratic Elfin Share