Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How I should use BIT in SQL Server 2005

Regarding to SQL performance.

I have a scalar valued function for checking some specific condition in base, it returns BIT value for True or False.

I now do not know how I should fill @BIT parameter

If I write.

set @bit = convert(bit,1)

or

set @bit = 1

or

set @bit='true'

function will work anyway but I do not know which method is recommended for daily use.

Another question, I have table in my base with around 4 million records, daily insert is about 4K records in that table.

Now I want to add CONSTRAINT on that table with scalar valued function that I mentioned already

Something like this

ALTER TABLE fin_stavke
ADD CONSTRAINT fin_stavke_knjizenje CHECK ( dbo.fn_ado_chk_fin(id)=convert(bit,1))

where "id" is the primary key of table fin_stavke and dbo.fn_ado_chk_fin looks like

create FUNCTION fn_ado_chk_fin
(
    @stavka_id int
)
RETURNS bit
AS
BEGIN
declare @bit bit

if exists (select * from fin_stavke where id=@stavka_id and doc_id is null and protocol_id is null)
        begin
            set @bit=0


        end
    else
        begin
            set @bit=1
        end
return @bit;
END
GO

Will this type and method of checking constraint will affect badly performance on my table and SQL at all ?

If there is also better way to add control on this table please let me know.

like image 530
adopilot Avatar asked Apr 27 '10 14:04

adopilot


People also ask

How use bit data type in SQL Server?

If there are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If there are from 9 up to 16 bit columns, the columns are stored as 2 bytes, and so on. The string values TRUE and FALSE can be converted to bit values: TRUE is converted to 1 and FALSE is converted to 0.

How do you assign a bit value in SQL?

To insert a new value to the BIT column, use INSERT statement: INSERT INTO table_name (bit_column) VALUES (1); You can also use TRUE and FALSE as the inputs for the BIT columns, SQL Server will automatically convert them as follow: TRUE will be converted to 1.

What is the bit value in SQL Server?

The BIT data type is an integer value that accepts 0, 1, and NULL. BIT represents a boolean type with TRUE (1) and FALSE (0) values. String values 'TRUE' and 'FALSE' are also accepted and converted to 1 and 0.


3 Answers

I might be wrong but from the looks of it, it seems you only want to check that not both doc_id and protocol_id are NULL?

You can add a table constraint to achieve this.

ALTER TABLE fin_stavke
ADD CONSTRAINT fin_stavke_knjizenje CHECK ( doc_id IS NOT NULL OR protocol_id IS NOT NULL)
like image 107
Lieven Keersmaekers Avatar answered Oct 15 '22 22:10

Lieven Keersmaekers


I'd use

 set @bit = 1

It's safe (assigning true to a bit feels wrong) and convert just seems pointless.

like image 35
Hans Olsson Avatar answered Oct 15 '22 22:10

Hans Olsson


I've always seen bit used as 1 or 0. I'd stick with that. Everyone will know what you're doing.

That constraint is going to affect the performance of your inserts but not by much since you're be seeking to the primary key of the table. It's probably the cheapest lookup you can do.

like image 20
Jon Avatar answered Oct 15 '22 21:10

Jon