Post reply

Help with creating a key

  • April 3, 2007 at 8:02 am

    #172858

    I am tasked with creating a database that logs traffic on a series of very busy sites. Each time one of the web pages on any of these sites is accessed I am going to log certain user & site info to a history table.

    The primary key I want to use for the main history table is a combination of the following two columns

    LogDate - day/month/year  21/03/2007

    RecordNo - integer (identity) - (starts at 1 at midnight and then gets reset again 24 hours later)

    So the combination of both fields would be the unique key.

    So is there a simple way I can get the RecordNo identity field to reset itself at the start of each day or do I have to write a job to force it to do this? IE a MS Agent job runs at midnight and resets the identity seed for RecordNo.

    Has anyone got any suggestions to accomplish the task above. Thanks in advance for any help.

  • April 3, 2007 at 8:31 am

    #698300

    Rewind...scrub that

    What I meant to say was that I would like the illusion of having an identity field for RecordNo seeded from 1 at the start of each day. I really don't want to have to be doing a select max(RecordNo) on each insert into the table as the inserts are going to be pretty constant as it will be a very heavily hit table.

    I am probably going to end up having a guid for recordNo and LogDate as full datetime and then just order by LogDate ASC and use ROW_NUMBER() anywhere I need to display the RecordNo as an integer.

    But if anyone has a better way without having to do lookups to find the max RecordNo on each insert (by Day) I would like to hear.

  • April 3, 2007 at 9:34 am

    #698333

    You need to find a way to determine when it's appropriate to execute this to make sure you've got all the prior day's records in your table before running, but this will work to reset the IDENTITY seed without updating any existing records in your table.

    --example

    create table #temp (fieldA INT IDENTITY (1,1), fieldB varchar(10))

    insert into #temp (fieldB) values ('A')

    insert into #temp (fieldB) values ('B')

    DBCC CHECKIDENT ('#temp', RESEED, 0)

    insert into #temp (fieldB) values ('C')

    select * from #temp

     

  • April 3, 2007 at 9:44 am

    #698335

    cheers mate looks good will give it a try.

  • April 3, 2007 at 9:55 am

    #698340

    You could try leaving the IDENTITY column alone and just subtract the MAX value from the previous day when it needs to be displayed.

    To aid querying, the previous days MAX value could be held in a another table and populated just after midnight.

  • April 3, 2007 at 10:19 am

    #698345

    i could do but the reason I want to reset the identity is that its almost certain that the identity col will reach its limit before long (Even with bigint, and starting with a negative) so I don't want to have to resolve that problem when it comes.

  • April 3, 2007 at 10:46 am

    #698352

    If your site is going to be that heavily used, I suspect you will have problems timing the RESEED.

    If bigint is too small for the IDENTITY column, try decimal(38).

  • April 4, 2007 at 10:48 am

    #698601

    If you use up all the bigints, that would mean you have about 18 quintillion records in your table.  Just a simple table scan would take over a million years.  I can't imagine that there is a database use in the world that could not be satisfied with bigints as a key.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply