Tag: T-SQL

One of the things that I’ve often seen people do is build temp tables to hold data for each connection in a unique way. Back in older versions of SQL Server this was bad since large temp tables could lock sysobjects in tempdb and cause contention. That’s been fixed, but I still tend to avoid temp tables unless it’s truly something that is session dependent.

I’ve seen some loading routines that needed temporary or staging data. We used to have some of this in a financial services firm where we had dedicated machines that would pick up files and then load them. However since we weren’t sure which machine would load what data, we wanted a shared data source. The developers didn’t know what to do, or how to share a staging table, so they used temporary tables. This gave us an all-or-nothing approach since the temporary tables disappeared if the connection was lost.

We also needed a similar shared temporary storage at SQLServerCentral for mail processing. We wanted to pull out rows from a table for a particular sending process, and mark them as used by that process only. Since we had multiple machines that would act as senders, each of them generic, again, we wanted to share some type of “workspace” on SQL Server, but have it unique to each connection.

The way that I’ve handled both of these in the past is to create a real table containing the structure I need and then add a column to it that identifies the session. If there’s an issue, you can see what rows were in process by a session and either assign them elsewhere, or return them to the pool.

As an example, let’s say you need to send lots of emails, but you want to store some of this data in SQL Server as it’s processed. I could create a table like this:

CREATE TABLE EmailSends
( EmailAddress varchar(20)
, SUBJECT varchar(200)
, Msg varchar(2000)
, SPID int
)

I have the basic data here to send an email. I can load this from other tables, and then add in the SPID. This column allows me to uniquely identify a session, and the process working with this table can determine which rows it should process. I could load this table like this:

INSERT EmailSends
 SELECT 
    EmailAddress, '', '', @@SPID
  FROM EmailList

This inserts all the emails, and marks these rows as belonging to my SPID. If another connection ran this same query, they’d have rows marked with a different SPID. I could now do some processing on “my” rows like this:

UPDATE dbo.EmailSends
  SET Subject = a.SUBJECT
 FROM EmailSubjects a
 WHERE a.EmailID = 1
 AND EmailSends.SPID = @@SPID

What happens if there’s data in this table with my SPID that isn’t mine? That can’t happen if you do this both at the beginning and end of your connection:

DELETE dbo.EmailSends
 WHERE SPID = @@SPID

It’s a crude technique, and doesn’t necessarily fit a lot of situations, but if you want to use this to manage workloads, maybe redistribute things to different processes, this can help you.

One thing I learned years ago in C programming was the elegance of using operators and simplifying expressions. This sometimes involved doing two things in a single statement, if it makes sense. For example,

x=y++;

Those of you that have done some programming will recognize this as incrementing y and assigning a value to x at the same time. Well not the same time, but in one line.

I knew that you could some things like this in T-SQL, but it didn’t trigger in my mind when someone asked about building their own sequencing table. This person wanted to update a table with an incremented value and return the value to the calling program without causing any locks/blocks.

Paul White posted this fantastic piece of code that illustrates this:

UPDATE  dbo.GPK

SET     @NewID = next_value = next_value + 1

WHERE   table_name = @table_name;

In this code the GPK table is being updated, with the next_value column being incremented. At the same time, @NewID, a variable that is an output parameter for a stored proc in this example, is assigned the value from next_value.

The value assigned is the incremented value, so if next_Value contains a 1 before this is run, @NewID will get 2. The increment/update occurs first, with the new value being assigned to the variable.

I think this is really cool, and it’s something I need to remember for future T-SQL problems.

The other day I was looking over a couple of articles on paging, looking to see if I could learn something new in T-SQL. I’ve implemented some SQL2000 era paging systems, none of which performed wonderfully, so I checked out Jacob Sebastian’s basic Server Side Paging and Paul White’s Optimizing Paging Part 1.

I’ve done systems similar to Jacob’s, but he had an interesting use of the OVER clause in his code. He had this code:

;WITH emp AS (
  SELECT 
    CASE 
      WHEN @SortOrder = 'Title' THEN ROW_NUMBER()OVER (ORDER BY Title) 
      WHEN @SortOrder = 'HireDate' THEN ROW_NUMBER()OVER (ORDER BY HireDate) 
      WHEN @SortOrder = 'City' THEN ROW_NUMBER()OVER (ORDER BY City) 
          -- In all other cases, assume that @SortOrder = 'LastName' 
      ELSE ROW_NUMBER()OVER (ORDER BY LastName) 
     END AS RecID,   , LastName
   , FirstName
   , Title
   , HireDate
   , City
   , Country
   , PostalCode
 FROM employees

This is a great solution in SQL Server 2005. It’s much different than what I had done in SQL 2000, where I’d typically approach the problem by using the sort key to get the next page.

So say I had this data in a table (Customers):

CustomerID    Customer

———–   ————

1             Jones

2             Smith

3             Johnson

4             Allen

5             Gates     

Then suppose I wanted page 1, 2 results per page, ordered by Customer. I would want to see “Allen, Gates” on page 1. I’d use this code.

select top 2 Customer

from Customers

Order By Customer

If I wanted page 2, I’d go here:

select top 2 Customer

from Customers

where Customer > ‘Gates’

Order By Customer

And this would get me “Johnson” and “Jones” since the WHERE clause would reset results. If you have control of the application code, and you can pass in the previous values, you can easily build pages like this.

If you switch orders, say to the CustomerID, then you can easily pass that in as well, and use that for ordering.

There is a downside, however. Any ideas? I’ll post that in my next look at paging.