Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2011

    Unanswered: How to properly index a deleted flag field

    Hi all

    For work we have a database which has had changes made to it willy-nilly over the years. Sometime soon I will be going through and cleaning up the db by removing unused fields/tables and examining indexes. Many indexes have just been tossed on for a lot of the tables as a 'Add this, see if it helps' thing and never removed even if it didn't help. What i plan to do is just remove every index/key and make a fresh start. I have one question regarding how to setup the new indexes though.

    Many of our tables follow a similar format with columns such as:

    Deleted BIT

    The PK_ID is an incrementing id which is the primary key, FK_ID links to another table, and Deleted determins if the data was deleted or not (rather than delete, the app was setup to just mark rows deleted). So most of the querys will join not only on the pk/fk but also include Deleted=0. When indexing I imagine this should be included for best performance, but not sure what the best way todo it would be:

    Here's an example of some of tables (trimmed unnecessary fields)

    CREATE TABLE [dbo].[students](
    	[StudentId] [int] IDENTITY(1,1) NOT NULL, -- Student Id (PK)
    	-- snip --
    	[Deleted] [bit] NOT NULL
    ) ON [PRIMARY]
    CREATE TABLE [dbo].[student_programs](
    	[ProgramId] [int] IDENTITY(1,1) NOT NULL, -- Program Id (PK)
    	[StudentId] [int] NOT NULL, -- Student Id (FK)
    	-- snip --
    	[Deleted] [bit] NOT NULL
    ) ON [PRIMARY]
    CREATE TABLE [dbo].[student_degree_plans](
    	[DegreePlanId] [int] IDENTITY(1,1) NOT NULL, -- Degree Plan Id (PK)
    	[ProgramId] [int] NOT NULL, -- Program Id (FK)
    	-- snip --
    	[Deleted] [bit] NOT NULL
    A common query would be something like:
    	-- Some fields --
    FROM students stu
    INNER JOIN student_programs spr ON stu.StudentId=spr.StudentId AND spr.Deleted=0
    INNER JOIN student_degree_plans sdp ON spr.ProgramId=sdp.ProgramId AND sdp.Deleted=0
    What type of indxes would be the best way to handle something like that. Should I made one index that includes all two/three fields, such as:
    CREATE INDEX IX_one ON student_programs (ProgramId, StudentId, Deleted) --With or without ProgramId?
    Or should I have a separate index for each column, such as:
    --ProgramId already covered by primary key
    CREATE INDEX IX_two ON student_programs (StudentId)
    CREATE INDEX IX_three ON student_programs (Deleted)
    Or some other method perhaps?


  2. #2
    Join Date
    Jun 2003
    Provided Answers: 1
    If you are using SQL Server 2008, one of your best resources is to enter your query into the query tool and select the option to show the query plan. It will suggest indexes that may help speed up your scripts.

    In general, indexing of bit fields is not a great benefit. At least in old version of sql server, you couldn't even add an index to a bit field through the GUI without it throwing an error and saying the index isn't allowed (though actually, you could still add the index using code).

    A better option for large tables with a commonly used bit flag field might be to partition the table by that field.
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

  3. #3
    Join Date
    Mar 2009
    Don't index the deleted column as it's own index... you have 2 possible values in the column, on or off and that's not going to be very effective in any index unless you have 2 rows in the table.

    Depending on what most of your queries use (i.e. if they generally all only include non-deleted data) I would consider a filtered index on each of the tables. I.e. an index with a where deleted = 0 clause attached to it, so you only index the "active" rows of data. Any query using a where deleted = 0 will use that index - assuming that index makes the most effective query plan.

    If you frequently query the deleted data though you'd probably be better just having the deleted flag as part of the index key - i.e. programID, StudentID, deleted. A bit is not going to take up much extra space in your index really.

  4. #4
    Join Date
    Nov 2011
    Thanks for the info. I created a few indexes for commonly searched/joined field combinations and included the Deleted field in those indexes if appropriate. Seems to be working pretty well.

    I will read up on the partitioning option as well. I know only a basic bit about it. Right now most of the tables are not terribly large, but a couple of them may grow rapidly.

Posting Permissions

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