Cannot index bit field

What's the logic behind that? I have a large table that has a field called
"Deleted" to indicate that a record has been deleted. For various reasons
very few records will be marked Deleted. If I do a select where Deleted = 1
it will need to scan the entire table for those few deleted records when I
would have thought it would be able to look up an index to quickly find
those records.
Regards,
MichaelMichael,
Beginning with SQL Server 2000, indexes can contain bit columns.
Are you using an earlier version, or is the database in question set
to an earlier compatibility level?
Steve Kass
Drew University
Michael C wrote:

>What's the logic behind that? I have a large table that has a field called
>"Deleted" to indicate that a record has been deleted. For various reasons
>very few records will be marked Deleted. If I do a select where Deleted = 1
>it will need to scan the entire table for those few deleted records when I
>would have thought it would be able to look up an index to quickly find
>those records.
>Regards,
>Michael
>
>|||Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; you have not even learned the
vocabulary.
The use of deleted flags gives me flashbacks to the late 1950's when we
had tape file systems that used flags like that! Your entire approach
to RDBMS is **totally* wrong, at the foundational level.
Kid, listen to me; you are being told this by one of the people who
wrote the SQL standards. You might want to think about that.|||Hi Micheal,
Steve's right. Bit columns are support in indexes in SQL 2000. Perhapes
you are being caught by the issue that EM does allow you to create indexes
with bit columns from within the Table Design form? If you need a graphic
tool to create indexes right click on the table object and use the All Tasks
Manage Indexes option
--
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"Michael C" <mculley@.NOSPAMoptushome.com.au> wrote in message
news:%23nneZC7mFHA.2472@.TK2MSFTNGP15.phx.gbl...
> What's the logic behind that? I have a large table that has a field called
> "Deleted" to indicate that a record has been deleted. For various reasons
> very few records will be marked Deleted. If I do a select where Deleted =
> 1 it will need to scan the entire table for those few deleted records when
> I would have thought it would be able to look up an index to quickly find
> those records.
> Regards,
> Michael
>|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1123468040.458991.66810@.z14g2000cwz.googlegroups.com...
> Let's get back to the basics of an RDBMS. Rows are not records; fields
> are not columns; tables are not files; you have not even learned the
> vocabulary.
> Kid, listen to me; you are being told this by one of the people who
> wrote the SQL standards. You might want to think about that.
No offence joe but you really are an idiot. I'm well aware you helped write
the SQL standards you remind the group on a regular basis. It's possibly why
databases are so behind the rest of computing world. There's one thing I
really dislike when dealing with computer people is those who overuse the
lingo in an attempt to make themselves look smarter. Such as your use of
"RDBMS" instead of database and insistance a column not be called a field.

> The use of deleted flags gives me flashbacks to the late 1950's when we
> had tape file systems that used flags like that! Your entire approach
> to RDBMS is **totally* wrong, at the foundational level.
What do you suggest instead of a deleted field? The record still needs to be
there so historical reports can run. The only thing I can see is that maybe
I should have used a field called DeletedDate but I don't need that
information.
Michael|||"GregO" <grego@.community.nospam> wrote in message
news:%23viqnW8mFHA.3936@.TK2MSFTNGP10.phx.gbl...
> Hi Micheal,
> Steve's right. Bit columns are support in indexes in SQL 2000. Perhapes
> you are being caught by the issue that EM does allow you to create indexes
> with bit columns from within the Table Design form? If you need a graphic
> tool to create indexes right click on the table object and use the All
> Tasks Manage Indexes option
Cool! That looks like it, I can create the index with query analyser no
problem. Enterprise manager wouldn't let me select it from the dropdown or
type the name. Now I just have to check if there's any improvement in
performance, it seems to "select * from table where deleted = 1" almost
instantly without the index anyway but there may be performance advantages
in more complex queries.
Michael|||"Michael C" <mculley@.NOSPAMoptushome.com.au> wrote in message
news:O8rUXg9mFHA.2860@.TK2MSFTNGP15.phx.gbl...
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1123468040.458991.66810@.z14g2000cwz.googlegroups.com...
> No offence joe but you really are an idiot. I'm well aware you helped
> write the SQL standards you remind the group on a regular basis. It's
> possibly why databases are so behind the rest of computing world. There's
> one thing I really dislike when dealing with computer people is those who
> overuse the lingo in an attempt to make themselves look smarter. Such as
> your use of "RDBMS" instead of database and insistance a column not be
> called a field.
>
> What do you suggest instead of a deleted field? The record still needs to
> be there so historical reports can run. The only thing I can see is that
> maybe I should have used a field called DeletedDate but I don't need that
> information.
> Michael
>
You'll get in trouble, you called it a field again ;-)
Your approach of using a bit field (I do it to) is OK. The only alternative
that I know of is using a second table for deleted rows. The idea here is
that the active rows are in MyTable and the deleted rows are in
MyTableHistory. There are several advantages to this method...
1. Less storage. Yep, one bit per row.
2. When you don't need to access the deleted rows there's no need to query
the table. If there are alot of delete's, this can boost the performance.
3. If you need to query, both active and inactive rows use a Union All
query. You could also create a view to do this repeatedly for you.
The only downside is that you need to physically move the row to a different
table, this should be done in a tranaction, but this physical movement could
cause other issues with data integrity.
Regards
Colin Dawson
www.cjdawson.com|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:544444
"Michael C" <mculley@.NOSPAMoptushome.com.au> wrote in message
news:OhCG9j9mFHA.1948@.TK2MSFTNGP12.phx.gbl...
> "GregO" <grego@.community.nospam> wrote in message
> news:%23viqnW8mFHA.3936@.TK2MSFTNGP10.phx.gbl...
> Cool! That looks like it, I can create the index with query analyser no
> problem. Enterprise manager wouldn't let me select it from the dropdown or
> type the name. Now I just have to check if there's any improvement in
> performance, it seems to "select * from table where deleted = 1" almost
> instantly without the index anyway but there may be performance advantages
> in more complex queries.
> Michael
>
You could always use SQL Profiler to see the number of reads needed to
complete the query. Also take a look at the Actual Execution plan, this
will show how the query is processed.
Regards
Colin Dawson
www.cjdawson.com|||> There's one thing I
> really dislike when dealing with computer people is those who overuse the
> lingo in an attempt to make themselves look smarter. Such as your use of
> "RDBMS" instead of database and insistance a column not be called a field
Correct terminology helps when it is used to aid clarity and precision.
Do you really think that "RDBMS" means the same as "database"? If so,
then Joe may have made a valid point. Unlike you, Joe didn't use any
personal abuse to make his point.
In answer to your question, note that although you can index a BIT
column, those indexes tend to be of limited value because of the low
cardinality of BIT. Use Query Analyzer to create the index rather than
BIT.
David Portas
SQL Server MVP
--|||Joe invariably favors encoding schemes over flags. After running into a few
issues with flags, I have come to agree with him - encodings adapt better to
changing business requirements.
Instead of having a column of type Bit that indicates whether a row is
deleted or not, you could simply have a status column of type Char(1) - this
will take the same amount of space (both of them take up one byte of storage
according to Books Online), however the status column can have additional
values added at a later date without changing the schema. You could then
have a "pending" status, a "new" status, etc. Also, you can add an index
from Enterprise Manager (not that that is a big deal, really). And there is
no additional work on your part - instead of writing "WHERE Deleted = 1",
you write " WHERE Status = 'D' " (or whatever). Simple!
The last time I dealt with this was in a security settings table where the
previous developer had started with a simple flag indicating whether a user
was an administrator for the custom application in question. Later, the
business added additional user "types" (supervisor, auditor, etc.), and each
time the developer added a new flag column. However, the types were mutually
exclusive (you could not be an auditor and a supervisor at the same time),
but no check constraints were added. Even if check constraints where added,
they would be ugly. It was far better in the long run to simply change the
table to remove the bit flags.
The point is that there is always a way to turn a flag column into an
encoding, which adds no additional work on the DBA or the app developer, but
still provides all the functionality.
IHTH
"Michael C" <mculley@.NOSPAMoptushome.com.au> wrote in message
news:O8rUXg9mFHA.2860@.TK2MSFTNGP15.phx.gbl...
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1123468040.458991.66810@.z14g2000cwz.googlegroups.com...
> No offence joe but you really are an idiot. I'm well aware you helped
write
> the SQL standards you remind the group on a regular basis. It's possibly
why
> databases are so behind the rest of computing world. There's one thing I
> really dislike when dealing with computer people is those who overuse the
> lingo in an attempt to make themselves look smarter. Such as your use of
> "RDBMS" instead of database and insistance a column not be called a field.
>
> What do you suggest instead of a deleted field? The record still needs to
be
> there so historical reports can run. The only thing I can see is that
maybe
> I should have used a field called DeletedDate but I don't need that
> information.
> Michael
>