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

In my opinion SQL Server 2005 is full of holes, and my definition is functionality that is available doesn't span the whole gamete of the server.  For example, aggregates don't work with GUIDs (call uniqueidentifiers in SQL).  Here is non-working code that I would like to see work:

CREATE #Temp(Id uniqueidentifier, Name varchar(50))

SELECT MAX(Id)
FROM #Temp

DROP TABLE #Temp

Why doesn't this work?  GUIDs are just 128-bit numbers, they are all unique and one comes before the other, there should be a MAX and MIN.  This does work:

CREATE #Temp(Id uniqueidentifier, Name varchar(50)) 

SELECT MAX(CONVERT(varchar(41),Id))
FROM #Temp

DROP TABLE #Temp

If you are converting a GUID to a String in C++ you can use this Windows API:

TCHAR szString[41];
::StringFromGUID2(guid, szString, 41);

Note that C++ adds brackets around the GUID so it appears like this:

{c200e360-38c5-11ce-ae62-08002b2b79ef}

However, if you are doing a ToString() in C# you don't get the ending brackets, for example:

String output = Guid.NewGuid().ToString();

Will return:

c200e360-38c5-11ce-ae62-08002b2b79ef

C# will parse a string into a GUID regardless of the brackets.  For example, both these get you the same GUID:

System.Guid guid = new System.Guid("c200e360-38c5-11ce-ae62-08002b2b79ef");

System.Guid guid2 = new System.Guid("{c200e360-38c5-11ce-ae62-08002b2b79ef}");

The equivalent function in C++ is:

::CLSIDFromString(sz,&guid);

However, CLSIDFromtString will not accept a GUID without brackets.  Note here that CLSID, IID and GUID are the same in C++.

If you are going between C++ and C# and you are passing GUIDs as string you need to include the brackets.  It is easier to have C# add the brackets then C++, since it requires a separate string.  Here is how you format your C# string:

String output = String.Format("{{{0}}}",Guid.NewGuid());

Note that you need to use double brackets to represent one bracket on each side.