Tag Archives: sql server

Sequenced SQL Records

Posted on by
3

How to sequence values in a table.  The non-so-elegant solution…

Imagine you have a table that contains records with a foreign key (string, int, guid, etc…) and an index or sequence number, 1-3, so that there are three records for each foreign key value.  In another table you have any number of records that contain the same foreign key, as well as a value field (date, number, string, etc…) that can be sequenced in some logical fashion corresponding to the sequence 1-3 in the key table.  You mission (should you choose to accept it) is to get the values from the value table and join them to the corresponding sequence records in the key table.

What follows is the answer I arrived at late this afternoon.  Please let me know if you have a better way.

  • declare @dates table(num varchar(9), date datetime)
  •  
  • insert into @dates
  • select ’192837465′, cast(’1/3/2008′ as datetime) union
  • select ’192837465′, cast(’3/12/2003′ as datetime) union
  • select ’192837465′, cast(’5/22/2010′ as datetime) union
  • select ’192837465′, cast(’2/1/2004′ as datetime)
  •  
  • declare @keys table(num varchar(9), sequence int)
  •  
  • insert into @keys
  • select ’192837465′, 1 union
  • select ’192837465′, 2 union
  • select ’192837465′, 3
  •  
  • select
  • k.num,
  • k.sequence,
  • date = (select MIN(t.date) from (select top 1 date from @dates where num = k.num order by date desc) t)
  • from @keys k
  • where sequence = 1
  • union
  • select
  • k.num,
  • k.sequence,
  • date = (select MIN(t.date) from (select top 2 date from @dates where num = k.num order by date desc) t)
  • from @keys k
  • where sequence = 2
  • union
  • select
  • k.num,
  • k.sequence,
  • date = (select MIN(t.date) from (select top 3 date from @dates where num = k.num order by date desc) t)
  • from @keys k
  • where sequence = 3

I union the key table 3 times, filtering by each sequence number. In each sub-query, the value is obtained by selecting the top N values, filtered by the foreign key and ordered by their value (descending), and then getting the min value.

Kind of an odd-ball case, but I might find this humorous one day. And Pete will laugh, because I laughed at my code.

Encryption and VARBINARY

Posted on by
Reply

Data loss is a scary thing. In one of the apps I have worked on it is particularly scary since some of the data is encrypted. In the beginning it was encrypted in such a way that it would be (hopefully) impossible to retrieve without the proper authorization. Essentially, the data would be junk if a finite number of users forgot their passwords. This was always lurking in the back of my mind. (You’ve never forgotten your password, have you?) Luckily we dropped that approach, but I didn’t anticipate how we would actually lose data.

One day I discovered an interesting error was occurring. (Only in hindsight can I refer to it as “interesting”.) It was a CryptographicException: Length of the data to decrypt is invalid. The application was attempting to load some encrypted data from the database and was repeatedly failing on a particular record. This is not good.

At first I thought: “Maybe there is a character or combination of characters in the source text that is causing the problem.” Looking back I can see that it was a poor attempt at an explanation. To be fair, I was alerted to the problem by a user who also informed me that he had used a random text generation feature (possibly for the first time), so I was a little biased. I was able to verify that the data was an odd size (as expected) and the encryption algorithm expected blocks of a certain number of bytes. However, I could not reproduce the bug since I had no idea what the original source text was.

Fast-forward a week or two. The problem occurs again, but this time the user is working with a known value. I crack open the test system and, lo and behold, the error is reproducible! After doing some debugging and tracing I come to the conclusion that SQL Server is truncating my data. (How rude!?) From there it didn’t take long for me to discover an interesting property of the column in question. Getting info on the table told me that the “TrimTrailingSpace” property was true (which corresponds to ANSI PADDING). It doesn’t take a genius to figure out that something is wrong with that.

Luckily I found this article, which indicates that any alter of the column will turn off “TrimTrailingSpace”, just what I wanted. So, what did I do? Something like this:

ALTER TABLE MyTable
ALTER COLUMN MyColumn VARBINARY(xxx);

Problem solved!