Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2002
    Location
    Vancouver, BC
    Posts
    35

    Unanswered: Filtering based on BIT attributes

    I have a few tables that have an disabled attribute using a BIT datatype. A lot of my queries on the front end look like:

    SELECT *
    FROM TableA
    WHERE disabled <> 1

    There's usually some other constraints on the query (get TOP 10 and greater than a certain date for example). Right now my tables are very small (only a couple thousand rows). I don't anticipate these tables having more than 100,000 rows.

    Right now let's say there's only a CLUSTERED INDEX on the date field, and regular INDEXES on the identity field and perhaps some other UNIQUE name in the table.

    Unless I am doing ranged queries on the CLUSTERED INDEXED field, I'm going to be performing table scans almost every time, right?

    This sort of goes along with another question:

    Say you run the following (SQL Server):

    Code:
    CREATE TABLE TestA  (
    	[id] INT IDENTITY (1, 1) PRIMARY KEY,
    	disabled BIT DEFAULT 0
    )
    GO
    INSERT INTO TestA (disabled) VALUES ('0')
    GO
    INSERT INTO TestA (disabled) VALUES ('0')
    GO
    INSERT INTO TestA (disabled) VALUES ('1')
    GO
    INSERT INTO TestA (disabled) VALUES ('0')
    GO
    INSERT INTO TestA (disabled) VALUES ('0')
    GO
    INSERT INTO TestA (disabled) VALUES ('0')
    GO
    INSERT INTO TestA (disabled) VALUES ('0')
    GO
    INSERT INTO TestA (disabled) VALUES ('1')
    GO
    INSERT INTO TestA (disabled) VALUES ('0')
    GO
    INSERT INTO TestA (disabled) VALUES ('0')
    GO
    INSERT INTO TestA (disabled) VALUES ('1')
    GO
    INSERT INTO TestA (disabled) VALUES ('1')
    GO
    INSERT INTO TestA (disabled) VALUES ('0')
    Since [id] is a PK there will be a CLUSTERED INDEX placed on it. My question is; what does the optimizer do when you perform the following query?

    Code:
    SELECT TOP 3 *
    FROM TestA
    WHERE disabled <> '1'
    My assumption is that since there's a CLUSTERED INDEX it will simply iterate through every tuple and check to see if disabled is not '1'. If my assumption is correct then these kind of boolean fields aren't a big deal if TOP queries are performed on a CLUSTERED INDEX.

    So I guess what I am getting at is: Are bit attributes a sign of bad design? As tables get larger will performance degrade significantly? Would a better design be to have a seperate table of disabled items (which may result in large NOT IN subqueries)?

    Any information on his would be greatly appreciated.
    INSERT INTO Jokes (statement) VALUES ('Here is my witty SQL signature');

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    moving to sql server forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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