Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Setting a Unique Index on a Bit Field

Can you set a unique index on a bit field? Well, you can, but you’d end up with a very short table of two (or three) rows. I defined this table:

CREATE TABLE [dbo].[BitTest](
    [MyBit] [bit] NULL,
    [MyName] [varchar](50) NULL
) ON [PRIMARY]

GO

USE [db1]
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_BitTest] ON [dbo].[BitTest]
(
    [MyBit] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

and then added some data

INSERT BitTest SELECT 1, 'Test'
INSERT BitTest SELECT 0, 'Test2'
INSERT BitTest SELECT NULL, 'Test 3'

SELECT * FROM BitTest

This table has these rows:

bittest2

Adding another row:

INSERT BitTest SELECT 1, 'Test 4'

gets you an error:

bittest3

However what about a compound index? What if I make the table larger and add more fields. Here’s a larger table:

CREATE TABLE UniqueBit
( BureauID INT
, CompanyID INT
, DivisionID int
, DefaultType BIT
, ProductName VARCHAR(100)
)
GO
INSERT Uniquebit SELECT 1, 1, 1, 1, 'Product 1'
INSERT Uniquebit SELECT 2, 1, 1, 1, 'Product 2'
INSERT Uniquebit SELECT 3, 1, 1, 1, 'Product 3'
INSERT Uniquebit SELECT 4, 1, 1, 1, 'Product 4'
INSERT Uniquebit SELECT 1, 2, 2, 1, 'Product 5'
INSERT Uniquebit SELECT 1, 2, 3, 1, 'Product 6'
INSERT Uniquebit SELECT 4, 1, 1, 0, 'Product 7'

If I now add a unique index:

CREATE UNIQUE NONCLUSTERED INDEX [IX_UniqueBit] ON [dbo].[UniqueBit]
(
    [BureauID] ASC,
    [CompanyID] ASC,
    [DefaultType] ASC,
    [DivisionID] ASC
) ON [PRIMARY]
GO

It works fine. I can add another unique row like this:

INSERT Uniquebit SELECT 4, 2, 1, 0, 'Product 7'

without an error. Adding in a non-unique row:

INSERT Uniquebit SELECT 4, 1, 1, 0, 'Product 7'

gives me an error:

bittest4

There’s nothing special about a bit column for a unique index. There are restrictions for bit fields in some ways that relate to indexing, but uniqueness is not one of them.

This was inspired by this post (before the complete details from the OP): http://www.sqlservercentral.com/Forums/Topic1126794-149-1.aspx#bm1126850


Filed under: Blog Tagged: syndicated, T-SQL

Comments

Posted by Jason Brimhall on 23 June 2011

Covered it well.

Leave a Comment

Please register or log in to leave a comment.