Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Dancewav
Starting Member
9 Posts |
 Posted - 2002-10-02 : 09:26:53
|
| why can't yopu have an index on a bit type on a table ? , and why would a bit type cause a query to just hang , i.e. select blah..from blah , where blah AND bit type field = 1 , but when I convert the bit type to char(1) the query flys through.bizzare. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-02 : 09:32:50
|
| It makes no sense to index a bit column because it can only hold a 1 or a 0 (zero), and therefore would not be selective enough to be useful.You'd have to post your exact table structure and the exact SQL of the query you're running in order for us to troubleshoot the performance problems. It's possible that the original SQL was cached with a poor plan and the second version created a new plan that is more effective. |
 |
|
|
wiltech
Yak Posting Veteran
51 Posts |
Posted - 2002-10-02 : 15:44:23
|
If you really want to you can Index on a bit field, but you have to create the index via TSQL not Enterprise Manager.We've toyed around with the idea of using a bit coulmn in a clustered index to help organize the data.i.e.CREATE TABLE [dbo].[tblOrders] ( [OrderID] [int] IDENTITY (1, 1) NOT NULL , [OrderDate] [smalldatetime] NULL , [CancelOrder] [bit] NOT NULL and:CREATE CLUSTERED INDEX IX_OrdersStatus ON dbo.tblOrders ( CancelOrder DESC, OrderID ) ON [PRIMARY](or some variant of this)Then playing around with the fill factor to reduce page splits during the day and setting up a SQL Agent job to defrag the index in the evening once everyone has gone home.Our reasons for this is 95% of the time we are dealing with records that have 0 in the bit column, and infrequently changing it to 1. Now back to lurking ... Tony W |
|
|
|
monkeybite
Posting Yak Master
152 Posts |
Posted - 2002-10-02 : 17:09:26
|
From SQL Books Online (SQL 2000):"Columns of type bit cannot have indexes on them." It might help to manually update the statistics on the table that you're having problems with. Especially if it's data is rather volatile and changes often. Look up UPDATE STATISTICS in BOL, or run sp_updatestats to update statistics in all of your user tables.-- monkey |
|
|
|
wiltech
Yak Posting Veteran
51 Posts |
Posted - 2002-10-02 : 19:49:24
|
quote:
From SQL Books Online (SQL 2000):"Columns of type bit cannot have indexes on them." It might help to manually update the statistics on the table that you're having problems with. Especially if it's data is rather volatile and changes often. Look up UPDATE STATISTICS in BOL, or run sp_updatestats to update statistics in all of your user tables.-- monkey
Hmm that's interesting. I ran my code from before and is seemed to work, no errors at least. The index shows up in Enterprise Manager & Query Analyzer.Tony W |
|
|
|
|
|
|
|
|
silly question but