Ivan Dragoev's Blog

IsNumeric and Cast headache

idragoev — Fri, 13 Feb 2009 15:04:00 GMT

An interesting issue was found by a colleague of mine: an exception when cast a nvarchar column to integer even if the column value was checked with IsNumeric on SQL 2000 SP4:

select c.IDfrom dbo.COM_COMPANY c where IsNumeric(c.CODE) = 1 and convert(c.CODE as int) = 304 and DELETED = 0

As result the following exception were raised:

Msg 245, Level 16, State 1, Line 1Syntax error converting the nvarchar value '9665a' to a column of data type int.

I played a bit with this query and I found the following: if I delete the check for the DELETED flag it works fine. But when I add one more check then it throws an exception. Here the working version:

select c.IDfrom dbo.COM_COMPANY c where IsNumeric(c.CODE) = 1 and convert(c.CODE as int) = 304

But I really need to have more checks, so what can I do?

The answer is:

select c.IDfrom dbo.COM_COMPANY c where DELETED = 0 and     (case when IsNumeric(c.CODE) = 1 then cast(c.CODE as int) else -1 end) = 304

This one works just fine.

Enjoy the workaround ;-)

Missing exceptions in code after Raiserror() and SET NOCOUNT ON workaround

idragoev — Fri, 19 Dec 2008 10:59:52 GMT

These days I worked on a problem related with using raiserror() in SQL 2000 and why the exception is not raised in ADO.NET application using ODBCCommand. What I had is a simple stored procedure, that performs some check on its parameters and if some of them are incorrect it raises error and exits:

create procedure dbo.TestRaiserrrorinCode (@ClientCode nvarchar(10)) as

if not exists (

select Id

from dbo.Clients

where Code = @ClientCode)

begin

raiserror(58005, 16, 1, @ClientCode)

return 58005

end

print 'Do Something'

If you execute it in SSMS you will see:

Msg 58005, Level 16, State 1, Procedure TestRaiserrrorinCode, Line 8

Cannot find client with code '34'.

As you can see, because of the return the print does nothing. So in the SQL it works fine.

But what if you execute this procedure from the C# code using ExecuteNonQuery()? The exception is not raised and the procedure seems to be executed normally. But it is not – there is no exception in the C# code, but the procedure didn’t execute successfully either.

I started my investigation by playing sit SET XACT_ABORT ON, but it didn’t help – it was not related to this situation.

I tried to remove the return after the raiserror() call, but as you might know raiserror() do not stop the execution so I get ‘Do something’ printed, which is not acceptable.

What’s the solution?

I’ve noticed that I have missed the SET NOCOUNT ON and decided to add it. And miraculously it helped. The procedure worked fine, the C# code throws the exception.

How and why the SET NOCOUNT to ON affects this – I do not know. But the fact is that without it the procedure does not behave as it is expected.

Hope that helps you!

Animating ListBox items - the VisualStateManager

idragoev — Fri, 13 Jun 2008 13:13:00 GMT

Check out my second article on ListBox items animations. Here you can see how to use VisualStateManager and also some problems I faced during the migration to Silverlight 2 beta 2

Enjoy!

Interactive Silverlight Voting Control

idragoev — Wed, 05 Dec 2007 14:43:00 GMT

As you might know, we at CompletIT are behind the SilverlightShow.net - the independent Silverlight community site. Also, the last few weeks we've published several articles (with source code included) just to show how easy is to create nice stuff with Silverlight 1.1.

I also decided to write an article based on simple interactive voting control. The purpose of this control is to show the current results for certain vote. The site visitors can give their vote simply by clicking on the option result they like and the results are automatically updated.

We will continue publishing articles with source code. We hope such examples are helpful for the developers.

You can submit your own article Silverlight on SilverlightShow.net - just click here.

CompletIT at DevReach, Sofia, Bulgaria 1-2 October, 2007

idragoev — Mon, 08 Oct 2007 15:36:00 GMT

At the beginning of October I have visited DevReach - the premier conference for Microsoft technologies in South Eastern Europe. I was on several lectures about MS SQL:

What's New in SQL Server 2008 for Developers - by Vladi Tchalkov;
T–SQL Querying: Tips and Techniques (With SQL 2008 features too) by Stephen Forte;
Lino Does LINQ by Lino Tadros;
Being Smart About Database Design by Vladi Tchalkov,
100 Years of Transaction Solitude...Transactions and isolation levels by Ami Levin;
SQL Server Service Broker - Your Gateway to the World of Transactional Asynchronicity by Ami Levin;
Security and .NET by Hadi Hariri;
Silverlight, Flash on Steroids by Lino Tadros.

What I appreciated most is the fact, that I had the chance to discuss different topics with the speakers. Most of them are experienced, well known in the community, with knowledge about the cutting-edge MS technologies.

I’m looking forward to meeting the guys again.

How to select the primary key data type?

idragoev — Thu, 05 Jul 2007 08:00:00 GMT

When a new database is about to be designed, have to think about what data type to use for the primary keys. There are several things that should be taken into consideration: the average database size expected, the possibility of growing in the future, replication or other data synchronization needs, etc. Here I will try to give you a simple way for primary key type selection depending on the database you have to design.

When I design database I'm trying to follow several simple rules:

Add column named ID in each table and set it as primary key;
Do not use logical primary keys as physical primary keys: Client table have column ID as PK and Code as logical PK, but ID column will be used in relations;
Use stored procedure for data fetching and manipulation - makes database changes easier, no recompilation of existing applications needed;
Avoid auto increment columns - you never know what the value for the PK will be until the row is stored in the database - means no control;

Generally I always have to choose between 16-byte uniqueidentifier and 4-byte int. The INTs are smaller, easy to remember, meaningfully to the user and easy to make them sequent. The uniqueidentifiers are simply unique and have unlimited values.

The primary keys of type int are smaller, which means smaller indexes and better performance. It is also easy to keep the values in a sequence - either by using auto increment (not recommended) or any other way to get the next value. This allows to make clustered index on the column too without performance impact.

The disadvantage of INTs are that you need some logic for obtaining new value. If auto increment is used, then inserting master-detail data might be more complicated, because the primary key value is unknown until the record is stored. This problem can be solved by using stored procedures and logical keys to link master-details.

To add new record when the primary key is unique identifier is easy. But here the size is 4 times bigger! Also you should never make clustered index on it because inserting new records are not sequential and causes the index to be reorganized each time.

The unique identifiers are only useful when you need to synchronize data between databases or you have application that might work off line. If INTs are used, you can have problems with doubled IDs because another user might use the same values for the new records - in this case you need to implement a logic to renumber the PK and all related records and to update the clients with the new values.

What I think is best is to combine the advantages of the INTS with the uniqueness of the guids. Make the primary key of type int, and add additional column of type uniqueidentifier only if you need data synchronization. This is the way the replication works - using rowguid column.

Increase Stored Procedure execution performance dramatically

idragoev — Mon, 18 Jun 2007 14:52:00 GMT

Last month we spent a lot of time in data loading optimization due to performance problems. I started with optimization of the heaviest views and put them in parameterized stored procedures . I also used a simple method to check the performance by measuring the time needed for execution. I was using MS SQL Management studio. During the test I had the following source with some local variables declared:

declare

@Param1 int,

@param2 int,

@TraceStart datetime

Set @TraceStart = GetDate()

Select Column1, Column2, ….

From Table1

Join …

Where Column1 = @Param1 and …

…

Select DateDiff(ms, @TraceStart, GetDate())

The idea was simple – to test, and then to place the same code in a stored procedure, and the declared local variables to be introduced as parameters. I managed to optimize the view drastically – from 500ms down to 1. And I was pretty happy.

Then I made the stored procedure:

Create procedure LoadData(@Param1 int @param2 int)

Select Column1, Column2, ….

From Table1

Join …

Where Column1 = @Param1 and …

Then I executed it using the following approach:

declare

@Param1 int,

@param2 int,

@TraceStart datetime

Set @TraceStart = GetDate()

Exec LoadData @Param1 int @param2 int

Select DateDiff(ms, @TraceStart, GetDate())

The results bring me a hard attack: more than 10 minutes!!!

Thanks to a friend of mine – Petar Atanasov – we solved the problem. He advised me to make a local copy of the parameters and to use them in the procedure’s body and that's exactly what I did:

Create procedure LoadData(@Param1_ int @param2_ int)

declare

@Param1 int,

@param2 int,

Set @Param1 = @Param1_

Set @param2 = @Param2_

Select Column1, Column2, ….

From Table1

Join …

Where Column1 = @Param1 and …

And that was it! The stored procedure was executed for less than 1ms!!!

The problem is that the query optimizer cannot make a good execution plan if the parameters are directly used, but it can if local variables are used – especially for date time parameters.