I had a request from a client who prints a lot of cheques each month and there control over the cheques went a bit haywire. The requested me to create some controls in their system to make sure that the cheques are use in cheque number sequence. I therefore created a table on their database wherin they would capture the cheque numbers in sequence and then whenever somebody uses a cheque the stored procedure doing the insert would check to make sure that (1) the cheque number they are using is not used yet and (2) that they use the cheque numbers in sequence. Since they have unanswered queries on missing cheque numbers they asked me to create a report from the system to show the missing cheque numbers on the database for early traking of the cheques. I will show my T-SQL code below and would like some comments on this and maybe someone has a better way of doing this. First of all I created a database MyTestDB for the purpose of this post.

-- I declare two variables to test the duration of the query as you will see.
DECLARE @start DATETIME, @end DATETIME
-- get the start time of the query
SET @start = getdate()
-- Create a table MyRowNumbers and populate it
CREATE TABLE MyRowNumbers(RowNumber int primary key)
DECLARE @counter int
set @counter = 1
WHILE @counter <= 100
   BEGIN
      INSERT MyRowNumbers(RowNumber)
      VALUES(@counter)
      SET @counter = @counter+1
   END
-- Now I will delete some rows in between in MyRowNumbers to do my missing numbers query
DELETE MyRowNumbers
WHERE (RowNumber = 5 or RowNumber = 11 or RowNumber = 34)
-- Now I want to extract a list of the missing numbers in MyRowNumbers
SELECT a.RowNumber+1 MissingNumbers from MyRowNumbers a
-- doing a left join to the same table
left join MyRowNumbers b on b.RowNumber = a.RowNumber+1
WHERE b.RowNumber IS NULL AND a.RowNumber+1 < 101
SET @end = getdate()
-- The duration of this query in milliseconds. Compliments Jeff Moden.
SELECT DATEDIFF(ms,@start,@end) duration

 The results:
MissingNumbers
5
11
34

Duration
63

I added the duration only to show me how this query performs and I don't think 63 milliseconds is to shabby BUT how will it perform if you have 10000 records. Well, I tested it with the same query and just changed the number of rows I insert to 10000 and the query a little like this:

SELECT DATEDIFF(ms,@start,@end) fullduration, DATEDIFF(ms,@nextstart,@end) listduration

I added a @nextstart variable and just above my list set it to the current time. Now I have a full duration and a list duration and the results:

FullDuration is now 5710 milliseconds and the list is a staggering 0 milliseconds. What about the estimated execution plan I hear you say. Well, I tried to do a print screen and paste it in here because I can't attach a ".sqlplan" file here but could not do that either so, why don't you check it out and let me know what you think? Comments please and don't spare me. I am eager to learn!

It is New Year's eve in South Africa and I am writing my first blog on SSC. The champaign is on the ice for midnight and while I am trying to stay awake I decided to take a look on the past year. Being a developer now for 9 years in VBA, ASP, ASP.NET and last but not the least SQL Server. I have not been involved in administration till about 2.5 years since one of my clients upgraded to SQL Server 2005. I was thrown in on the deep end and had to learn a couple of things post haste. That was the point when I came upon SSC. I learned fast because help was readily available on SSC. There were some other forums that I had registered on but I always came back to SSC because I found it easy to find help here.

I wrote my first article on string manipulation on 11 September 2008 and through the discussions learned a lot about performance tuning a query. You can't imagine the pride I felt seeing my first article published on the internet and have people like Jeff Moden and some others which I can't remember now for whom I have great respect in the SQL Server community.

25 November 2008 saw my second article on knowledge sharing published. I got some real positive feedback from that and once again saw some great names in the discussions that gave me some good feedback.

I'd like also to thank some of these great names for the positive posts on this website. Names like Steve Jones, Jeff Moden, Gail Shaw, Lynn Pettis, Grant Fritchley, Brian Kelly, RBarry Young and many more. It is nice to know that there are people out there willing to help.

A new year is on our doorstep and we can only hope that the new year will bring what we all want it to bring. Better economy, better salaries, better business? Well, I am striving to be a better person in general and hope that financially it will go a bit better. Well, I wish one and all a very blessed and prosperous New Year and may 2009 bring to you what you are hoping for.