1

Lets say I have a session table like this:

[Session]
-------
Id: int
UserId: int

Imagine that is used in an extremely high traffic site and Sessions are very frequently added and deleted. If I were to make the Id column of each table an Identity column, how could I easily maintain the seeding of the Id's so that they don't hit the limits of the int data type? Is there an alternative way of ensuring unique Id's that I'm not thinking of? Thanks in advance.

Ocelot20
  • 10,510
  • 11
  • 55
  • 96
  • I think we need a little more information - how long are `Session`s going to last (could you make it temporal based)? Are composite keys an option? – Clockwork-Muse Mar 08 '12 at 00:45

4 Answers4

3

instead of int make it bigint, this will go up to 9,223,372,036,854,775,807

you can of course start at -9,223,372,036,854,775,808 as well

see also What To Do When Your Identity Column Maxes Out

SQLMenace
  • 132,095
  • 25
  • 206
  • 225
  • I realize I could do that, but it seemed silly to me that a table that at any given time might only have 1000 rows would have insanely huge identity numbers just so I didn't have to reseed it. Maybe it's just me being anal, but I was curious if there was a different way. – Ocelot20 Mar 07 '12 at 21:48
1

Make the id a guid instead of int. You get unique session id's that are not guessable and easy to implement with Guid.NewGuid().

Mattias Åslund
  • 3,877
  • 2
  • 18
  • 17
1

If you have a site maintenance period you could just reseed the identity column. Naff but simple.

Tony Hopkinson
  • 20,172
  • 3
  • 31
  • 39
0

How long can a given session exist? If no session will last more than X period of time, and you know that you will never have more than N sessions present in the table at any given time, and you know the maxiumum rate at which new sessions will be added, then you could implement some form of circular queue system, cycling over a maximum set of numbers.

For example, if you never have more than 1000 rows in the table at any given point in time, no more than 1000 rows will be added in any given 5 minute period, and no row will persist for more than 2 days (nightly clean-up routine?), then you would go through 1000 * 2 * 24 * 12 = 576,000 Ids every two days... where every id gets added, used, and removed from the system every two days. Build circular queue logic around a large safety factor of that number (5,000,000, maybe), and you could be covered.

The hard part, of course, is generating the Id. I've done that in the past with a one-row "NextId" table which was defined and called like so:

--  Create table
CREATE TABLE NextId
 (NextId  int  not null)

--  Add the one row to the table
INSERT Nextid (Nextid) values (1)

Optionally, put an INSERT/DELETE trigger on here to prevent the addition or deletion of rows

This procedure would be used to get the NextId to use. The single transaction is of course atomic, so you don't have to worry about locking. I used 10 for testing purposes. You will end up with an Id value of 0 every now and then, but it's a surrogate key so the actual value used should not matter.

CREATE PROCEDURE GetNextId

    @NextId  int  OUTPUT

AS

    SET NOCOUNT on

    UPDATE NextId
     set
        @NextId = NextId
      ,NextId = (NextId + 1) % 10 -- 5000000

    RETURN

Here's how the procedure would be called: 

DECLARE @NextId  int

EXECUTE GetNextId @NextId output

PRINT @NextId

I don't know how well this would work in excessively high-volume situations, but it does work well under fair-size workloads.

Philip Kelley
  • 39,426
  • 11
  • 57
  • 92
  • Except, because of the absolute **need** for this to run atomically, it becomes an automatic bottleneck, and **cannot** be parallelized. It _may_ perform okay, but I'd avoid it if possible. – Clockwork-Muse Mar 08 '12 at 00:39