sentient beings

Many queries you write will be about finding the most recent data for a certain type of event, or the highest value for a certain object. While this is a straightforward query type, it gets complicated and ugly when you have to link two or more tables to find your maximum or minimum value. And it gets even more complicated when you don’t have to find the maximum value but the value that is just below the maximum value.

In the rest of the article, I will be talking about the maximum value for a field, but it’s also true of course for the minimum value.
The query type you will encounter most often contains a subquery where the current value is compared to the maximum value for that particular field. A simple example is presented below.

 
SELECT
  MAX(tb2.Field) AS MaxValueForField
FROM
  table1 tb1
    INNER join
  table2 tb2
    ON  tb2.foreign_key = tb1.primary_key
WHERE
  tb2.Field = (
          SELECT
            MAX(_tb2.field)
          FROM
            table1 _tb1
              INNER join
            table2 _tb2
              ON  _tb2.foreign_key = _tb1.primary_key
          WHERE
            _tb1.primary_key = tb1.primary_key
        )
GROUP BY
  tb1.primary_key
 

The subquery can be avoided with a Common Table Expression that not only speeds up the entire query but also offers a solution for another problem that I will be discussing below.

 
;
WITH tb2Sorted
 
AS
 
(
  SELECT
    Row_Number() OVER (partition BY tb1.primary_key ORDER BY tb2.Field DESC) AS RowId,
    Field
  FROM
    table1 tb1
      INNER join
    table2 tb2
      ON  tb2.foreign_key = tb1.primary_key
)
SELECT
  Field AS MaximaleIngangsDatum
FROM
  tb2Sorted
WHERE
  RowId = 1
 

This query performs better than the first one but also offers another advantage.

You can easily find the second or third largest value by simple changing the RowId. Bear in mind that this will not remove duplicates. If the values for Field are 1,1,2,5,5 then RowId 1 will yield 5 and RowId 2 will also yield 5. You can find the top 5 largest values for a certain field by simply changing the where clause to read RowId < 6.

 
;
WITH tb2Sorted
 
AS
 
(
  SELECT
    Row_Number() OVER (partition BY tb1.primary_key ORDER BY tb2.Field DESC) AS RowId,
    Field,
    RowId
  FROM
    table1 tb1
      INNER join
    table2 tb2
      ON  tb2.foreign_key = tb1.primary_key
)
SELECT
  Field AS MaximaleIngangsDatum
FROM
  tb2Sorted
WHERE
  RowId < 6
 

A few weeks ago, I noticed that my Lacie Internet Space was offline. Not a real problem, since the LaCie Internet Space will go offline every once in a while. A powercycle is usually enough to get it going again.

But to my dismay, not this time. I powercycled the LaCie Internet Space and it booted allright, the blue light came on and then ... nothing.

I dismantled the complete thing, took out the drive (Hitachi Deskstar 1TB 7200 RPM) and plugged it into my usb/sata converter and again, nothing happened.

Cue slight panic as my entire music collection was stored on this, as well as my wife's photo portfolio. Since the drive did seem to spin up, I suspected the logic board. I scoured the internet for an exact hard disk and found one. In the meantime, I had also bought a second hand LaCie Internet Space, thinking there would be a Hitachi Deskstar inside but it turned out to be a Samsung HD103SI. I discarded the second LaCie Internet Space and put all my hopes on the spare disc, which, by the way, was rather expensive due to the floods in Thailand.

I switched the logic boards, connected the Hitachi to the usb/sata converter and nothing happened. I replaced the logic boards and in a final act of lucidity decided to put the hard drive into the replacement LaCie Internet Space casing. All of a sudden, the disc sprang back to live, started rattling merrily and I could see the disc once again in my network places.

It seemed that the hard disc wasn't at fault but the original LaCie Internet Sace casing. Go figure. Why the drive wasn't recognised by the usb/sata converter is beyond me. I'm copying 1 TB of data to my other NAS right now and I'm never buying anything from LaCie again.

After a reboot of my development machine, the SQL Server 2008 R2 service wouldn't start.

I consulted the Event viewer, but that only yielded the following message.

Log Name: Application
Source: MSSQL$SE2008R2
Date: 1-6-2010 12:38:43
Event ID: 17120
Task Category: Server
Level: Error
Keywords: Classic
User: N/A
Computer: [censored]
Description:
SQL Server could not spawn lazy writer thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
<System>
<Provider Name="MSSQL$SE2008R2" />
<EventID Qualifiers="49152">17120</EventID>
<Level>2</Level>
<Task>2</Task>
<Keywords>0x80000000000000</Keywords>
<TimeCreated SystemTime="2010-06-01T10:38:43.000Z" />
<EventRecordID>7660</EventRecordID>
<Channel>Application</Channel>
<Computer>censored</Computer>
<Security />
</System>
<EventData>
<Data>lazy writer</Data>
<Binary>E042000010000000130000004400540041003000300035003400360030005C0053004500320030003000380052003200000000000000</Binary>
</EventData>
</Event>

After checking the internet for a solution, I came up with none. I decided to check the SQL Server Log file and it contained the following line.

I/O affinity turned on, processor mask 0x00000002. Disk I/Os will execute on CPUs per affinity I/O mask/affinity64 mask config option. This is an informational message only; no user action is required.

I them remembered that I had tried to assign CPU2 to handle I/O requests. Since the only way to remedy something that goes wrong and which you haven't got a clue about is to retrace your steps, my next step would be to undo that setting. But the SQL Service wouldn't start and the -I command line option didn't work.

I started SQL Server manually as described in this MSDN article: How to: Start an Instance of SQL Server (sqlservr.exe). I specifically used the -f option to make sure SQL Server would start with as little configuration as possible.

SQL Server started and I launched my Enterprise Manager and checked the "Automatically set I/O affinity mask for all processors".

I then closed the Enterprise Manager, quit the command prompt and started the service. Life was peachy again.

A very long time ago, when I was still playing World of Warcraft, I tried to order a key for World of Warcraft through Gamersloot.net. You can read about why that was a bad idea over here: Never order from Gamersloot.net. They took my money but never sent me the key.

A few weeks ago, I decided to check out where all the World of Warcraft Phishing attempts came from. I quit WoW quite a while ago, but I keep getting "notification" messages. I did a quick check of the headers and found out that the mails are being sent to the exact same mail address I used to register with Gamersloot.net.

I have a long standing policy to create specific mail addresses for every site I register on. This allows me to track back spam to the originating site and it worked well for Gamersloot.net.

So, besides the Goldselling activities and the "take-the-money-and-run" activities, they also do Phishing - trying to steal passwords from the people they rip off.

Today, SQL Server 2008 express gave up on me. I installed it yesterday and it worked fine. Then I turned off my PC and when I turned it back on today, SQL Server Express wouldn't start. A quick look in the event log got me the following 2 errors.

FCB::Open failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\mastlog.ldf for file number 2. OS error: 5(failed to retrieve text for this error. Reason: 1815).

FCB::Open failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\master.mdf for file number 1. OS error: 5(failed to retrieve text for this error. Reason: 1815).

Additionally, when trying to change settings from the SQL Server Configuration manager, I got the following error.

You have until 1% to log off. If you have not logged off at this time, your session will be disconnected and any open files or devices you've opened may loose data. [0x80070d59]

The solution was to enter the Services control panel, locate the SQL SERVER (EXPRESS) service, pick the Log On tab and check "Log on as Local System Account".

That got the job done. It's running now.