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!

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.

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.

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.

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.