Menu

Databases (4)

How to create a temporary table that can be seen by different SqlCommands on the same connection?

The unexpected answer (that I learned the hard way) is: well, it depends on whether you have parameters on your command or not. The point being, if you execute a parameterless SqlCommand, the sql gets executed directly, the same way as if you entered it into the query analyzer. If you add a parameter, the things change in that a call to sp_execsql stored procedure gets inserted in the executed sql. The difference here is the scope: if you create a temporary table from within the sp_execsql, it's scope will be the stored procedure call and it will be dropped once the stored procedure finishes. In that case, you cannot use different commands to access it. If you execute a parameterless command, the temporary table will be connection-scoped and will be left alive for other commands to access. In that case, the other commands can have parameters because their sp_execsql call will be a child scope and will have access to parent scope's temporary table.

As to why they did it this way, I can't say I understand.

Howto: SQL / LINQ JOIN on TOP 1 row

Problem: you want to do a LEFT or INNER JOIN between two tables but include only one record from the other table: that is, you don’t want the join to create duplicate records. Interestingly enough, I found the solution to this through LINQ. In LINQ, you can do this without really thinking about it:

from cmp in ctx.Companies join pers in ctx.Persons on cmp.Persons.First().ID equals pers.ID

Surprisingly for me, this query gets translated into working SQL, which looks something like this (note that I cleaned it up quite a bit for readability):

FROM Company INNER JOIN Person ON ( SELECT TOP (1) top1Person.ID FROM Person AS top1Person WHERE top1Person.CompanyID = Company.ID ) = Person.ID

Once you think about it, the solution is quite simple. All you need to remember is that a JOIN can contain subselects (even subselects with their own JOINs).

Pre-fetching data with LINQ to SQL?

(Yes, I know I’m behind the times - “LINQ to SQL? Who needs it when there’s the newest preview/alpha/beta of the Entity Framework?" Well, I did start this application in EF v1 and ran away when I saw “unsupported” stickers plastered all over it. So, no thanks, I’m waiting for the proverbial “Microsoft v3.11” (or 3.51, whatever they call it)).

Looking superficially, one would say that all ORMs are alike. Moreover, as one of the newest to come into the world, LINQ to SQL would be expected to have it’s philosophy and design done according to previously accumulated knowledge. Erm, yes, it’s a polite way of saying that I expected it to be a rip-off of NHibernate…

This similarity may exist in general, but there are some areas in which the two are completely separate worlds. The example that I encountered is performance optimization. Coming from the NHibernate background I was surprised to discover that there are not much optimization topics in common with the two. In some aspects, NHibernate has already solved (at least for me) issues that LINQ to SQL has not yet stumbled upon, but in others, LINQ to SQL focuses on performance issues that don’t even exist as topics in NHibernate.

Common Table Expression For Sorting Hierarchical Records By Depth

I tried to find this on the net, and was unable to find a satisfying and simple solution. How do you retrieve hierarchical records (say, from a self-referencing table) sorted by their depth in the hierarchy? Microsoft SQL Server 2005/2008 has a new SQL construct,  Here’s one example: a Category table with an ID (primary key) and ParentID (pointing to the hierarchical parent).

WITH CategoryCTE(ID, ParentID, Depth) 
AS 
( 
  SELECT ID, ParentID, 0 
  FROM Category 
  WHERE ParentID IS NULL – root records

  UNION ALL 

  SELECT cRecursive.ID, cRecursive.ParentID, cCte.Depth+1 
  FROM Category AS cRecursive JOIN CategoryCTE AS cCte 
      ON cRecursive.ParentID = cCte.ID 
) 
SELECT * 
FROM CategoryCTE 
ORDER BY Depth
Subscribe to this RSS feed