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

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


Table Variable Non Clustered Index not keeping correct Statistics

With table variable, the optimizer fail to predict right number of rows returned from the query execution and fall in using wrong index selection. Lets try out with an example, first we will see the same with #temp table then will check the @table variable of SQL Server 2014.

Here we are creating a temporary table with clustered and non clustered index on it.

create table #IndexDemo  (
    id­ int identity primary key clustered,
    city varchar(60),
    stateprovinceid int,
    index ix_stateprovinceid nonclustered (stateprovinceid)
);
insert #IndexDemo (city,stateprovinceid)
select city, stateprovinceid
from person.address;
go

select city
from #IndexDemo
where stateprovinceid=1;

drop table #IndexDemo

Here in above script we have temp table with stateprovinceid field as non clustered key and id as clustered index.  Now if we see the execution plan for the query
select city
from #IndexDemo
where stateprovinceid=1;

 


Now looking at execution plan we can see it has predicted 25 rows, so to efficiently retrieve it use nonclustered index that’s fair enough. Then do the lookup to get the city column from clustered index.

Now lets implement the same using SQL Server 2014 table variable.

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 city
from @IndexDemo
where stateprovinceid=1;
go

Now lets check the execution plan

 


Here, the estimated number of row count is showing 1 which is but obvious wrong in number, but to the count, it should use Non Clustered Index seek but oddly it uses Clustered Index scan. So to our example it fail to utilize non clustered index.

Note: Be aware before you decide to go for table variable if you thinking to make use of non clustered index feature.

Let my rhythm take me with you :)


Post Reference: Vikram Aristocratic Elfin Share

Sunday, November 2, 2014

Workaround to have Default value in Identity column


If you try to define default value for identity column, it will result in error saying “Not Allowed. Lets try to stimulate the problem.

Here we are taking a table with id column

create table Tab_DefaultIdentity
(id int identity(1,1) default 1000,
name varchar(10)
)
Msg 1754, Level 16, State 0, Line 47
Defaults cannot be created on columns with an IDENTITY attribute. Table 'Tab_DefaultIdentity', column 'id'.
Msg 1750, Level 16, State 0, Line 47
Could not create constraint or index. See previous errors.

So what is the workaround for the same, lets check out with Sequence feature of SQL Server 2012

Workaround

Here we creating a new table without Default constraint set at id field.

create table Tab_DefaultIdentity
(id int default 1000,
name varchar(10)
)
Command(s) completed successfully.

Now let’s create a sequence with incremental factor of 1 then use this sequence to populate the table data

create sequence seq1 as int
start with 1
increment by 1
Command(s) completed successfully.

Now lets insert few rows in the table
insert into Tab_DefaultIdentity(name) values('Rinny')
insert into Tab_DefaultIdentity(id,name) values(next value for seq1,'Binny')

lets query the table data

select * from Tab_DefaultIdentity
id          name
----------- ----------
1000        Rinny
1           Binny

So here we can we can see, by using Sequence feature one can add default value as well as can get the incremental identity for a column.

No SQL no Night :)


Post Reference: Vikram Aristocratic Elfin Share

Saturday, November 1, 2014

Workaround to have null value in Identity column



When we are trying to explicitly enter null values it to an identity column we get an error message saying not allowed. Lets try to stimulate the problem.

Here we are taking a table with id column

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

Let now insert null value, but to explicitly insert value we need to set IDENTITY_INSERT property to ON

set identity_insert Tab_nullableIdentity  on
go
insert into Tab_nullableIdentity(id,name) values(null,'Rinny')
Msg 339, Level 16, State 1, Line 31
NULL are not allowed as explicit identity values.

Workaround

Lets create a new table without identity property set on id column

create table Tab_nullableIdentityWithSeQ
(id int ,
name varchar(10)
)
Command(s) completed successfully.

Now let’s create a sequence with incremental factor of 1 then use this sequence to populate the table data

create sequence seq1 as int
start with 1
increment by 1
Command(s) completed successfully.

Now lets insert few rows in the table
insert into Tab_nullableIdentityWithSeQ(id,name) values(null,'Rinny')
insert into Tab_nullableIdentityWithSeQ(id,name) values(next value for seq1,'Binny')

lets query the table data

select * from Tab_nullableIdentityWithSeQ
id          name
----------- ----------
NULL        Rinny
1           Binny

So here we can we are able to generate sequence auto generated number as well as we are able to insert null value to  the id column.

That’s it J

Conclusion: we can Stimulating null value insert identity property column through Sequence Object of SQL Server 2012.

Have your tea and sit for code :)
 

Post Reference: Vikram Aristocratic Elfin Share