The answer is a definite maybe. I'm Oracle familiar, not SQL Server, but if the data is skewed in any of those columns(90%=0 10%=1) then you may get some benefit when selecting the smaller value. Also, Oracle has special indexes for columns with low cardinality called bitmap indexes. These are amazing for static data, but can really slow down inserts and updates on these tables. You might want to see if SQL Server has something similar.
HTH, Steve ------------------------------------- Steven Monaghan Oracle DBA / Cold Fusion Developer MSC Industrial Direct Co., Inc. http://www.mscdirect.com ------------------------------------- -----Original Message----- From: Jeff Beer [mailto:[EMAIL PROTECTED]] Sent: Sunday, February 17, 2002 2:26 PM To: CF-Talk Subject: OT: SQL indexing Is there any benefit to indexing a "tinyint" field that is used as a boolean (either 1 or 0)? I have four fields that work this way and I use them heavily in queries for reporting. They are used in aggregate functions (count and sum) as well as in the where clauses for various queries. I know I could play with the indexes and test the timing, but I only have a live database to work with and don't want to disrupt anything until I feel there will be some significant benefit. Also, along the same lines, I report by grouping output (in SQL - not CF's group=) using the DAY, HOUR and MONTH parts of a datetime stamp. Will indexing help when using portions of a datetime field in this way, as opposed to considering the entire field? BTW - this is using SQL Server 2000 (and I guess the same theory would apply to SQL 7). Thanks! Jeff ______________________________________________________________________ Why Share? Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
