Results 1 to 3 of 3

Thread: [RESOLVED] SQL Server 2008 Table question?

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Jan 2012
    Posts
    144

    Resolved [RESOLVED] SQL Server 2008 Table question?

    Morning,

    I've got a student table which i add and delete from. My primary key is StudentID.

    the primary key ID numbers are 1,2,4,6,8. The missing 3,5,7 ID numbers are records that i have deleted. How can make sure that these ID numbers are re-arranged once ive deleted a record?

    so for eg, when i delete ID number 5 from 1,2,3,4,5,6 i would like the table to reset the ID numbers so that once 5 is deleted, ID number 6 would become 5 and so on.

    AlL responses will be appreciated.

    ta

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: SQL Server 2008 Table question?

    Noone uses databases that way. Don't do it. Once you assign an ID to a record. That is the ID for that record and it should stay the ID for that record. If you delete a record then the ID for that record should simply remain unassigned.

    In fact, most people will tell you that, in many, if not the majority of, cases you should not even delete the record. If you delete a student record then every single record related to that student would have to be deleted as well. It would be as if that student never existed. What sort of learning institution would want to eradicate all trace of their past students?

    What exactly do you hope to gain by this reassignment of IDs? Even using the 'int' data type for your ID you have capacity for 2,147,483,647 records in a table. Are you expecting more students than that? If that number may not be enough then you can use 'bigint' instead, which can accommodate 18,446,744,073,709,551,615 records. You'd need a very busy campus for a very long time to have more students than that.

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Jan 2012
    Posts
    144

    Re: SQL Server 2008 Table question?

    Quote Originally Posted by jmcilhinney View Post
    Noone uses databases that way. Don't do it. Once you assign an ID to a record. That is the ID for that record and it should stay the ID for that record. If you delete a record then the ID for that record should simply remain unassigned.

    In fact, most people will tell you that, in many, if not the majority of, cases you should not even delete the record. If you delete a student record then every single record related to that student would have to be deleted as well. It would be as if that student never existed. What sort of learning institution would want to eradicate all trace of their past students?

    What exactly do you hope to gain by this reassignment of IDs? Even using the 'int' data type for your ID you have capacity for 2,147,483,647 records in a table. Are you expecting more students than that? If that number may not be enough then you can use 'bigint' instead, which can accommodate 18,446,744,073,709,551,615 records. You'd need a very busy campus for a very long time to have more students than that.
    sorry maybe i didnt make it clear in my first post but i had some records inserted for testing purposes and deleted them all. Now when i was adding a new valid record the StudentID number was starting from the last deleted record ID number.

    Got it working now. Deleted all records and ran the following:

    Code:
    dbcc checkident ( student,reseed, 0)
    now new records start at StudentID 1.

    By the way, you made a good point about not deleting student records. i will instead archive old student records rather than deleting them.

    thanks for your response

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width