My brother called, he had forgotten a WHERE clause and had lost some data.  I felt really bad for him -- the kind of sinking feeling you get when someone you know has a death in the family.  I can relate -- I have forgotten a few WHERE clauses in the past couple of years.  Right after he called I checked my database backups to make sure they where still running every night.

This reminded me of one of my favorite interview questions: "Tell me about the last time you forgot a WHERE clause?"  I am looking for that sinking recognition that takes place when someone knows the question and can relate.  And if they have never forgotten a WHERE clause -- I just know they haven't programmed much T-SQL, cause we all do it from time to time.  I also want to hear how they recovered their data and what they are doing differently to prevent the problem.  A developer I know at work always writes the WHERE clause before he writes the DELETE or UPDATE line just to prevent the aforementioned.

Maybe SQL Management tool should have an optional warning that makes you confirm that you want to run a DELETE or UPDATE when there is no WHERE clause?

{6230289B-5BEE-409e-932A-2F01FA407A92}

I've been reading a couple of renditions of this SessionWrapper class via DotNetKicks over the past couple of days.  While I must admit that this code isn't 100% original, I can say that I have given it a more generic feel and added a little bit of functionality.  With my implementation you can not only use any class, you can specify a name for the session object.  That way if you want to store say the Account and Order objects in Session you could use the same method without creating a wrapper for each type.

Pretty straight-forward. Here's how you would use it from a Page_Load for example:

if (session.Object == null)
   session.Object = new Foo(-1, "hai guyz!");
else
{
   Foo foo = session.Object;
} 

Not cutting-edge but could be useful if you want a type-safe session state.

Lately I have doing a lot of SSRS and overall SQL work for a project.  In returning some data to process server-side my data retrieval method was returning null* which means that an exception was thrown. I did a little digging around and then decided to execute the stored procedure manually (i.e. Management Studio).  After debugging in Visual Studio and seeing what the values were and that they were in fact getting passed to the stored procedure I used the same signature to execute the proc in Management Studio.  Hmm, no dice.   My error message was of a "Divide-By-Zero" fashion.  This is the first time that I have ever seen this.  A little reading on the subject and I come to find out that the field used as the denominator allowed nulls.

Enter NULLIF. This is what saved the day.  This function will compare two values and return a null value if the expressions are equal.  For example, take the following dummy stored procedure.

SET @returnValue = ( @numberOfUnits / @totalPrice )
SELECT @returnValue 

Pretty basic. I'm selecting the total price from a table and using as the denominator.  I pass in number of units. To get price per unit (share price) I divide one by the other. What if the @totalPrice is 0? Then I get the dreaded Divide-By-Zero error.  Using NULLIF, we can make a trivial aleration to the above procedure and return a NULL value instead.

SET @returnValue = ( @numberOfUnits / NULLIF(@totalPrice, 0) )
SELECT @returnValue 

Easy. If @totalPrice is 0 we will substitute with a null value.  This way the returnValue will be NULL and we can process accordingly from our .NET application.

      return returnValue == DBNull.Value || returnValue == null ? -1 : returnValue;
   }
} 

There could be even more useful ways to use NULLIF but for me this has worked out fine.

If you have some legacy tables you where you want to convert from having a primary key of int to a primary key of uniqueidentifier, you need to do a lot of work.  Here are the steps:

1) Drop all forigen keys contraints that points to the table being modified.

2) Drop all views that reference the table being modified.

3) Drop all indexes that use the primary key or any forigen key column that points to the table being modified.

4) Rename all the old forigen key column that points to the table being modified, so that we can add a new column with the original name of type uniqueidentifier.

5) Drop the primary key contraints on the table being modified.

6) Add a new forigen key column to all tables where there was a forigen key column that points to the table being modified of the same name with a type of uniqueidentifier.  Make then NULL

7) Rename the primary key of the table being modified -- the int column.

8) Add a new column with the original primary key name of type of uniqueidentifier.

9) Fill all rows with the new primary key column using the function NewId()

10) Update all the new forigen key column with the new primary key using the old forigen key column bound to the old primary key on the new table.

11) Alter all the new forigen key column to NOT NULL where the original forigen key column was NULL.

12) Rebuild all indexes, including the primary key index on the new columns

13) Rebuild all forigen keys.

14) Rebuild all views.

Good Luck

{6230289B-5BEE-409e-932A-2F01FA407A92}

When you start to use GUIDs (uniqueidentifiers) as primary keys in T-SQL one of the first questions is how to replace @@IDENITY in your primary key column.  A typical table with int as a primary key might look like this:

CREATE T1(ID int PRIMARY KEY IDENTITY, Name varchar(50))

When you insert into this table and want the primary key of the row that you inserted you typically do this:

DECLARE @ID int

INSERT INTO T1 (Name) values ('Tom Smith')

SET @ID = @@IDENTITY

However, when you use uniqueidentifiers as primary keys you don't declare them as IDENTITY, here is how you would build this table:

CREATE T1(ID uniqueidentifier PRIMARY KEY , Name varchar(50))

And here is what the INSERT looks like:

DECLARE @Id uniqueidentifier

SET @Id = NewId()

INSERT INTO T1 (ID, Name) VALUES (@Id, 'Tom Smith')

When using uniqueidentifiers you create them ahead of the insertion and SQL Server doesn't keep track of the process for you.

{6230289B-5BEE-409e-932A-2F01FA407A92}

Lets take a quick look at INNER JOINs in T-SQL.  This select statement is a very typical:

SELECT *
FROM Products
   INNER JOIN Category ON Products.CategoryId = Category.CategoryId
WHERE Products.AccountId = @AccountId

However, it can be rewritten to be much faster:

SELECT *
FROM Category
   INNER ON Products JOIN Products.CategoryId = Category.CategoryId AND
      Products.AccountId = @AccountId

Here is why: In the first statement all the rows in the products table are being joined to all the rows in the category table, producing a bigger table, that is then walked to find the correct account.  This might make a very large table after the join if there are many accounts and the category table is very wide.  SQL Server has to deal with this large in query table being produced.  However, in the second statement only the products that are in the required account are joined to the category table -- significantly reducing the join. 

It is important to note you can only reduce the size of the join on the tables you are joining too (not the first table).  So you might need to rotate the first table.  In the case above the category table is likely smaller then the products table and doesn't have accounts so I rotated it up.

{6230289B-5BEE-409e-932A-2F01FA407A92}

I just finished my first VB.NET project and it was pretty interesting. Some of you might be wondering why I did a project in VB.NET.  There are actually 3 reasons:

1. Client's money is still green
2. Site was the first .NET application by someone straight out of classic ASP
3. Client won't pay to upgrade to C#

Now that we've cleared that up, I'd like to say how awkward it was.  First of all, it took me probably twice as long to write this as it would if it were in C#.  This is due to my not knowing the in's and out's of VB.NET 2.0 and not necessarily something I can blame on VB.  While there are some great additions to VB.NET such as Generics (even though the syntax isn't very intuitive), the Using construct (about time!) there is one feature that is still not there that I use day-to-day when writing applications in C#. I am talking about collapsible regions within methods.

Take the following code example:

Protected Sub ButtonClick(ByVal sender As Object, ByVal e As EventArgs)

   Dim button As Button = sender

Given the opportunity to start from scratch I like to setup my event handlers like this.  One method that handles everything I need. To me, it makes for cleaner code and much easier to read.  If something goes wrong with an event handler, I can look in one place. In a C# environment I would setup a collapsible region like so:

Protected Sub ButtonClick(ByVal sender As Object, ByVal e As EventArgs)

   Dim button As Button = sender

The reason I would do that is because when I am writing code I view monitor space as real estate. On each screen I like to see as much code as possible. In the example above I could add a collapsible region outside of this sub routine but that wouldn't work (at least for me).  In scanning through the code I'd still want to see the section where I call DoSomethingCoolWithThisString because it's an action item so to speak.  In scanning this code-behind I could see that in my handler method I am setting a string based on the button that was clicked and then calling that method in my DAL / BLL.  Even though the code segment is collapsed I can still see what it is doing and if I need to fix that section (i.e. add a case for a new button added to the page) I could just uncollapse that region.

Quick tangent: Is it just me or does Intellisense seem a little slow and different than C#?  Also, what's up with not being able to hightlight a segment of code, right-click, 'Surround With' when in the VB.NET environment?  The 'Surround With' functionality is great for using constructs and try-catch blocks amongst other things.

At the end of the day it's all .NET and I'm kind of glad I came across this project.  As a software developer in the consulting world it's nice to be dextrous when it comes to writing / reading code but I'm still partial to C#.

One question I ask when interviewing is what types of T-SQL statements are the worst for performance, I am looking for answers like the below (not in any order):

• Cursors
• IN clauses
• Large INNER JOINs

What this tells me about the person is that they are conscience of the code impact they have on the server.  Someone that doesn't know the answer will write anything that works just to get their job done -- which means that I will later have to clean-up their stuff.

{6230289B-5BEE-409e-932A-2F01FA407A92}

Been doing some interviewing lately and finding that people think they are really good at T-SQL (claiming they are a 6 out of 10), however almost none of them write their T-SQL by hand.  If you are using the WYSIWYG query builder you are a 3 out of 10, or less.  Here is one of my favorite interview problems that comes up in real life (my life) a lot.

Given a table like this:

CREATE T1 (ID int PRIMARY KEY, Name varchar(50))

That has duplicate rows in the name column, write a query to "clean" the duplicates leaving only on row per name.  So if "Tom Smith" shows up in two rows (ID: 2, and 4), the query would delete either rows with ID 2 or 4 and leave the other one.  My favorite answer is:

DELETE T1
WHERE NOT T1.ID IN (SELECT MIN(T1.ID) FROM T1 GROUP BY Name)

This includes an IN clause where is terrible for performance -- however the best solution I know.  Most people try it with a cursor which is much harder.

Note:  This is how you "clean" a table of duplicates before you add a unique index to the name column.  A typical scenario when you have a lookup table that has some extra data and you are normalizing the database.

{6230289B-5BEE-409e-932A-2F01FA407A92}

MSDN Managed Newsgroups might be one of Microsoft's best kept secrets for development resources.  If you have an MSDN subscription you can post a development question on the Managed Newsgroups and a Microsoft support person will answer it within 24 hours.  For me this is an invaluable resource -- much easier then calling on the phone.  You can also post as many times as you like.  There are a few hoops to jump through to tie your MSDN subscription to your passport login, which take about 10-15 minutes to figure out.  If you don't have a MSDN subscription getting help is hit and miss - Sometimes you get a response and sometimes it is correct.

Too bad all their products are not supported this way.

http://msdn2.microsoft.com/en-us/subscriptions/aa974230.aspx

{6230289B-5BEE-409e-932A-2F01FA407A92}

Still working on treeview with ajax slide show. I had to write 2 expandnode functions: one based on file system ("/dir/dir1/dir2") and one based on treeview with PathSeparator as "|" ("/dir/|/dir/dir1/|/dir/dir1/dir2/"). This is so that the currently selected node (ie directory) can be expanded and highlighted regardless if the web page is opening for first time, on postback, on querystring, etc.

It should be as easy as filling in a datatable-ish structure where the structure automatically resolved down the hierarchy based on a given path. But I can't figure out how to get two very similar structure into a single container without doing alot of work myself. If you know where I'm going wrong - let me know.

-Dina

I'm writing a web app that connects a treeview control with a ajax slide show. Tons of feature creap but what is new.

I don't get a chance to program as often as I would like so when problems pop up like FindNode returning null on a valid ValuePath, I assume it's me, my programming, my install, my whatever.

I look at the docs, I look at sample code. I even look for other people having the problem but I don't see any solutions but others definitely having the problem. In one forum, the poster mentions that he figured out his path separator can't be a symbol in the valuepath. Hmmm. If you don't set the PathSeparator, it defaults to "/" which is the same thing as the directory structure I'm using in the valuepath. So problem is solved when I explicitly set the PathSeparator to "|".

-Dina