Cast a Bit to a Bit ?

[dbomrrsm]

ImportDate = DATETIME
ImportComplete = BIT

which is the most efficient query

SELECT ImportDate FROM Import WHERE ImportComplete = 1

OR

SELECT ImportDate FROM Import WHERE ImportComplete = CAST(1 AS BIT)

rason I ask is that "Microsoft strongly recommended that we change it to the latter because 1 is an integer by default"

anyone know why the latter would be the case ?

[blue]DBomrrsm[/blue]

 

[quinnipiac0807]

They would both work fine. I think the reason for the latter is just because it specifies that the 1 is a bit value and not an integer value. They will both work fine.

quinnipiac0807

 

[dbomrrsm]

I know they would both work but why would microsoft say that the second would work better ?

[blue]DBomrrsm[/blue]

 

[SQLSister]

My performance tuning book says that implicit conversions should be avoided because they require some extra overhead. They would recommend something more on the lines of

Declare @bitvalue as bit
Set @bitvalue = 1
SELECT ImportDate FROM Import WHERE ImportComplete = @bitvalue

I would suggest you look at the execution plans for all three variations and see if there really is a difference between how SQL Server will interpret in your particular database. Then run a series of performance tests.

Questions about posting. See faq183-874

 

[mrdenny]

I would assume the same implicit conversions issue that SQLSister mentioned. When you leave something to the SQL Server to decide that takes more resources than deciding for the SQL Server. And often SQL Server will not make the same choise that you would make. With the first query you posted SQL Server might decide to convert your bit field to int, instead of converting your 1 to bit. This would obvsouly take more resources than converting the 1 to bit, and doing the bit compaire.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[dbomrrsm]

Tested this on 2 million row table with 600,000 rows where the bit was set to 1:

select Getdate()
SELECT ImportDate FROM Import WHERE ImportComplete = 1
select Getdate()
SELECT ImportDate FROM Import WHERE ImportComplete = CAST(1 AS BIT)
select Getdate()
Declare @bitvalue as bit
Set @bitvalue = 1
SELECT ImportDate FROM Import WHERE ImportComplete = @bitvalue
select Getdate()

Results

getdate 1 2004-12-03 14:14:17.217
getdate 2 2004-12-03 14:14:23.030 Q1 took 5.8 secs
getdate 3 2004-12-03 14:14:28.857 Q2 took 5.8 secs
getdate 4 2004-12-03 14:14:34.687 Q3 took 5.8 secs

Conclusion - wasnt worth the discussion - they all seem to perform about the same.

[blue]DBomrrsm[/blue]

 

[zhavic]

The second query will be more efficient, if the column ImportComplete is indexed.

When you look at query plan, you should see, that for the first query,it uses convert function to convert value 1 to bit and index scan,
while for the second query it uses index seek.

To try out that, just run this is query analyzer:

create table import ( ImportDate bit )
create index index_1 on import( ImportDate )
GO
SET SHOWPLAN_TEXT ON 
GO
select ImportDate from import where ImportDate = 1
select ImportDate from import where ImportDate = CAST( 1 AS bit )

The result will be:

select ImportDate from import where ImportDate = 1
|--Index Scan(OBJECT[test].[dbo].[import].[index_1]), WHEREConvert([import].[ImportDate])=[@1]))


select ImportDate from import where ImportDate = CAST( 1 AS bit )
|--Index Seek(OBJECT[test].[dbo].[import].[index_1]), SEEK[import].[ImportDate]=1) ORDERED FORWARD)

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.