none
re-indexing in SQL Server 2005 express??

    Question

  • Hello,

     

    I'm a REAL newbie in SQL 2005 Express (let alone in SQL!), and I'm trying to put together a database using SQL and Visual Studio 2008. I'm writing up a program in C#, but I'm having a problem on re-indexing. I have a primary key that's an integer, and in my C# program, I got it to automatic incremented, starting at 0 and incrementing by 1.

     

    That seems fine, but I notice something. Say I have in column ID I have this: 1, 2, 3, 4, 5, 6, .... but I don't need 4th record and I've deleted it out of the database. How can I get 5 and 6 decreased by one to close that gap? Is there a SQL script that will handle that?

     

    Steve

     

    Wednesday, July 02, 2008 10:15 AM

Answers

  • You can use the ALTER INDEX command to rebuild an index by specifying the REBUILD option.  I wrote a series on performing maintenance in SQL Express on the SQL Examples site that you can find a lot of good information in:

     

    Performing Common Maintenance Tasks in SQL Express

    Ola Hallengren also has a very nice stored procedure that does index maintenance and is compatible with SQL Express.  You can find the code for it on his blog for free.

     

    http://blog.ola.hallengren.com/

     

    Keep in mind that this is not going to renumber your data rows in the table though.

     

    As for your question about clustered and non-clustered indexes.  The key difference is that a table can only have one clustered index, and it controls the storage order of the table data in the database.  A table can have up to 1024 non-clustered indexes on the other hand, and these do not hold all of the data for the table.  They instead how key columns that are specified at index creation, and provide a rapid lookup method for data.  A nonclustered index also contains a RID which is a key back to the leaf level of the clustered index which is used if a query can benefit from using the non-clustered index for finding a row of data, but the index doesn't contain all of the key columns necessary to satisfy the query directly.  This creates a Key/Bookmark Lookup back to the clustered index where the remaining data is pulled from the row.

     

    This is kind of a simplified explaination of the differences, and doesn't really do the entire concept justice.  There is a lot more that goes into it than this, but these are the general concepts.  Planning and building index structures in SQL Server can be an artform at times, depending on the size of the database, and the queries that will be accessing the data.

    Wednesday, July 02, 2008 2:26 PM
    Moderator
  • Just to reenforce Jonathan's point - and prove others agree with him, you should not try to re-number hardware_id or software_id. Not only would this incurr a huge cost on your processor without giving you any benefit, but it is just asking for data integrity problems.

     

    I know you think these fields are not of primary concern, but in fact they are of primary concern because they are your Primary Key. Since you mention you're new to SQL Server you might want to consider reading some of literature about database normalization. You'll find a number of places on the web that discuss this with many papers that drill into varying degrees. I've always liked the white paper at http://support.microsoft.com/kb/234208/EN-US/. It's based on Access 2000, but the rules of normalization are shared across all relational databases so this is applicable to SQL as well. Taking the time to understand these types of design rules will save you hundreds of hours of pain down the road because you build better database from the begining.

     

    Regards,

    Mike

    Thursday, July 03, 2008 1:15 AM
    Moderator

All replies

  • Gaps like these are normal in SQL Databases.  For referential integrity, you shouldn't be reseeding a primary key like you are asking to do.  If this is an identity column in SQL Server, you definately shouldn't try to renumber your rows like that.  It is very common for there to be gaps in identity columns in a table.  The reason for this is that this number is only increasing upwards, it will never reuse a lower value because this could create invalid references in the event of orphaned records.

     

    Wednesday, July 02, 2008 1:19 PM
    Moderator
  • Well, I can see your point, but it's not really a reference. It's something just unique to say "Hey, I'm a different record than the other one". Another words, I could care less about it. What I am concerned is the ParentID that I have inside of this table. I'm using relational database where THIS table is the child, and the relation is to the parent, and it's one-to-many reference.

     

    This is how I have the database:


    Computer Table: (parent table)

    networkID (primary key)

    employeeID

    maker

    description

     

    Software Table: (child table to Computer)

    software_ID (primary key)

    ParentID -----> set to relate to NetworkID one-to-many

    title

    version

    maker

    key

    description

     

    Hardware Table: (child table to Computer)

    hardware_ID (primary key)

    ParentID -----> set to relate to NetworkID one-to-many

    cardname

    maker

    position

    description

     

    The way I have it on the forms is that I have textboxes for all fields in Computer, and a miniture tables for Software and Hardware since they're like a list of what's on the computer. I think you see what I'm basically doing with this kind of database.

     

    So lets say that there's a card that I've replaced with a different one. I want to delete the old card from the hardware table, re-index, and then enter in a new record. I hope now you see why Software_ID and hardware_ID is not a primary concern. All I want to do is clean up the database to save some space and make it look neater on the tables. That's all I'm doing.

     

    Steve

     

    Wednesday, July 02, 2008 1:47 PM
  • Once you delete the record, the space is freed by the database engine.  In order to renumber your table, you would have to write a custom cursor/loop to iterate row by row, reseeding this column.  This will be an expensive process, and is going to provide any benefit to you other than the rows have no gaps in the numbering.  Doing this would not be recommeneded by anyone on this forum, and I don't usually try to speak for the world, but this isn't a recommened or best practice.  What looks good to your eye looking at the database tables directly has nothing to do with how it functions inside the database engine or to your application end users.

     

    This is not a reindex operation.  Reindexing in SQL Server is an actual operation of rebuilding the clustered/nonclustered indexes that exist on a table.  This process places the data in the leaf level in logic order to allow for faster access to the data by the database engine, but it doesn't change any of the data in the database. 

     

    Wednesday, July 02, 2008 2:05 PM
    Moderator
  • So if I wanted to do so, how would I go about rebuilding the index? And curiousity, what is the difference between clustered and non-clustered indexes?

     

    Steve

     

    Wednesday, July 02, 2008 2:15 PM
  • You can use the ALTER INDEX command to rebuild an index by specifying the REBUILD option.  I wrote a series on performing maintenance in SQL Express on the SQL Examples site that you can find a lot of good information in:

     

    Performing Common Maintenance Tasks in SQL Express

    Ola Hallengren also has a very nice stored procedure that does index maintenance and is compatible with SQL Express.  You can find the code for it on his blog for free.

     

    http://blog.ola.hallengren.com/

     

    Keep in mind that this is not going to renumber your data rows in the table though.

     

    As for your question about clustered and non-clustered indexes.  The key difference is that a table can only have one clustered index, and it controls the storage order of the table data in the database.  A table can have up to 1024 non-clustered indexes on the other hand, and these do not hold all of the data for the table.  They instead how key columns that are specified at index creation, and provide a rapid lookup method for data.  A nonclustered index also contains a RID which is a key back to the leaf level of the clustered index which is used if a query can benefit from using the non-clustered index for finding a row of data, but the index doesn't contain all of the key columns necessary to satisfy the query directly.  This creates a Key/Bookmark Lookup back to the clustered index where the remaining data is pulled from the row.

     

    This is kind of a simplified explaination of the differences, and doesn't really do the entire concept justice.  There is a lot more that goes into it than this, but these are the general concepts.  Planning and building index structures in SQL Server can be an artform at times, depending on the size of the database, and the queries that will be accessing the data.

    Wednesday, July 02, 2008 2:26 PM
    Moderator
  • Just to reenforce Jonathan's point - and prove others agree with him, you should not try to re-number hardware_id or software_id. Not only would this incurr a huge cost on your processor without giving you any benefit, but it is just asking for data integrity problems.

     

    I know you think these fields are not of primary concern, but in fact they are of primary concern because they are your Primary Key. Since you mention you're new to SQL Server you might want to consider reading some of literature about database normalization. You'll find a number of places on the web that discuss this with many papers that drill into varying degrees. I've always liked the white paper at http://support.microsoft.com/kb/234208/EN-US/. It's based on Access 2000, but the rules of normalization are shared across all relational databases so this is applicable to SQL as well. Taking the time to understand these types of design rules will save you hundreds of hours of pain down the road because you build better database from the begining.

     

    Regards,

    Mike

    Thursday, July 03, 2008 1:15 AM
    Moderator