February 2006 - Posts

I was looking at some posts recently talking about Integration services and how to be able to do imprecise lookups. The solution was to use a SQL based lookup. The situation is an employe with a history of departments they have been in, you need to find the department at a particular point in time. To do this you need something like

   select e.surname, e.employeeno, h.department
     from emp e
left join (select mh.employeeno, max(startdate) startdate
             from emphistory mh
            where mh.startdate < @dateParameter 
         group by mh.employeeno) mh on mh.employeeno = e.employeeno
left join emphist                h  on mh.employeeno  = h.employeeno 
                                   and h.startdate   = mh.startdate

What I am suggesting is something like

    select e.surname, e.employeeno, h.department
      from emp e
upper join emphist                mh  on e.employeeno  = mh.employeeno 
                                    and mh.startdate < @dateParameter 
The logic is that the last record that matches the join criteria is returned. This would simplify the code even if slightly complex to understand. It should also benefit the optimiser by only having to perform one scan of the data. Rather than building a list of values and then having to traverse the index to get to the data page.

If your with me you might be thinking that you may not always have a join, and I agree to actually I think the reccomendation should be something like

    select e.surname, e.employeeno, h.department
      from emp e 
 left join emphist                mh  on e.employeeno  = mh.employeeno 
                                     and mh.startdate  +< @dateParameter 

So here the engine would, for each employee, find the last emphist record with a startdate before the dataParameter and then return the data off this record. If there was an index on employeeNo and startdate this would be really effecient.

  • +< indicates the maximum value that is less than the left hand side
  • -< indicates the minimum value that is less than the right hand side
  • +> indicates the maximum value that is greater than the left hand side
  • -> indicates the minimum value that is greater than the right hand side

equality could also be included in the operator.

Personally any situation involving historical records this would be really useful.

If you like the idea of this then vote on it at http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=d3c488ed-c10b-463e-aa09-199ac23decd5


I posted a while ago (august last year) on a converter of VB code to C#. Well now it appears there is an addin available for Visual studio to allow you to copy VB and paste C#.

How cool

Posted by simonsabin | with no comments

Came across this whitepaper by Microsoft and Unisys on SQL Server Intergration services.

If interested is can be found here

Posted by simonsabin | with no comments
Filed under:

Microsoft are looking for beta testers of an enhanced version of sql express, this includes reporting and full text search.

You can find the detail for registering for the beta at


Thanks to Euan for pointing this out

Posted by simonsabin | with no comments

I can never remember what the terminal services equivalent key strokes are for the local key strokes, i.e. CTRL+ALT+DEL. This location gives a good list http://www.sessioncomputing.com/keyboard.htm

In case the pages changes here is the list

Key combination Function Similar local keys
CTRL+ALT+END Opens the Windows Security dialog box CTRL+ALT+DELETE
CTRL+ALT+BREAK Toggles the Terminal Services client display from window to full screen NONE
ALT+INSERT Cycles through running programs on the remote computer ALT+TAB
ALT+HOME Displays the remote computer's Start menu  
ALT+DELETE Displays the remote window's Control menu ALT+SPACE BAR
CTRL+ALT+NUMBER PAD MINUS Places an image of active window onto the TS clipboard ALT+PRINT SCREEN
CTRL+ALT+NUMBER PAD PLUS Places an image of the entire Terminal Services client on the Terminal Services clipboard PRINT SCREEN

Posted by simonsabin | 2 comment(s)

Blogs are becoming, if not already, one of the prime sources of information, especially technical information. They are a fantastic mechanism by which experiences can be shared in a usable manner without the need for a direct audience. Newsgroups historically provided this but required a question to be posted before an answer was provided. The great thing I find about blogs is the way that they represent experience, Jamie Thomsons blog is a prime example and is currently providing my team a great source of knowledge.

However with a blog, in my view, comes responsibility. If you start a blog and contribute to it regularly then it is more than likely you will start being ranked higher in the search engines and thus start getting traffic based on the content of your blog. I feel therefore that one has the responsibility to make sure the content in the blog is accurate. This is especially true with technical blogs and is largely due to users using content from blogs in their own work assuming the blog content is accurate. Thus the reason that I try to be very careful about any code, stats I post.

You may ask why I am ranting on like this, well its when I come across posts like this one http://sqljunkies.com/WebLog/donkiely/archive/2006/01/29/17922.aspx where a piece of SQL is shown on how to concatenate strings in SQL. The purpose of the SQL in this question is fine if it where not for the where clause, which is prime example of lazy code. If someone where to use this in a system where the table is large the performance will be far from ideal. If you want to go into the detail have a look at (http://www.sommarskog.se/arrays-in-sql.html).

One also would like to think that over time one would become more well known and thus the quality or posts becomes more and more important.

For this reason I think it is a bloggers responsibility to make it clear if anything that is posted should or shouldn't be used and whether it is a good example or not. In the case in question I would expect the blog to go on about the ins and outs of arrays in SQL but should at least state that the way used is not performant but is used to simplify the code and refer the user to the relevant sources.

This is a very personal view of blogging and one that many probably wont agree on, but I suppose thats what blogging is all about :)


Posted by simonsabin | with no comments

You can now register for TechEd 2006 in Boston.

Never been to a teched but heard they are good.

Posted by simonsabin | with no comments
More Posts « Previous page