tinyint vs NULL

[sapator]

A lazy google search and I cannot find anything.
I wanted to ask if there is any benefit on speed using a value (like -1) on a tinyint vs NULL.
MSSQL server.

[techgnome]

There shouldn't be. If there is, it's miniscule. Especially if the field is indexed (assuming you're looking for is null in a where clause).

That said ... why do people try to avoid the use of NULL? NULL means value unknown. People complain and rail against using "magic numbers" in code, and yet using -1 in the database for an "unknown" value is the same thing - a magic number.


-tg

[sapator]

We are parsing bank extrait's. I'm not sure if the values should be null as it supposedly must have a value, p.e. a null card number is not really null but an error.

[techgnome]

That's different from the scenario in the original post... and if it's an error, then it should be rejected and shouldn't have data made up to get it to conform.
But what ever...


-tg

[Zvoni]

If you are parsing bank extracts why are you asking about speed?

[sapator]

First we are parsing then process. Insert is easy with a bulk insert the rest currently,,,not sure.
I'm not responsible on how the extract are given, I just have to process them.
One thing for example that comes in mind is that if I use NULL's then a count for errors would have use ISNULL that to my understanding is not sargable. To be noted I'm not actually sure how we are going on about so I'm browsing choices. If it was that bad that I asked for speed then ignore me.

[szlamany]

The speed issue relative to NULLS is all about how it "tracks" which fields are null.

In a table without any NULL's (in ANY and ALL field definitions), for each row, there will just be buckets for each field, with a value stored within each bucket.

If a table allows NULLS, then an additional "bit pattern" is stored with each row. Each of the bits corresponds to an ordinal "field" within the row. The bit values are checked prior to accessing each NULLABLE field, used to determine first if the field is NULL.

This certainly seems negligible. But if you think of the actual I/O and memory processing, moving a chunk of data is a simple operation. Compared to checking a bit, then either moving a value or "creating" the NULL data "representation" in memory - that is a lot more. Do this a million million times, while reporting on a table with millions of rows, this can be costly.

I use NULL for mostly DATE FIELDS in my tables. The proper normal-form way to handle DATES without NULL's usually requires creating a child table, and if a date is not known, that row is simply not created. That is cool concept for Ivory tower consideration, out in the fields (pun intended) not so much.

[Zvoni]

The speed issue relative to NULLS is all about how it "tracks" which fields are null.

In a table without any NULL's (in ANY and ALL field definitions), for each row, there will just be buckets for each field, with a value stored within each bucket.

If a table allows NULLS, then an additional "bit pattern" is stored with each row. Each of the bits corresponds to an ordinal "field" within the row. The bit values are checked prior to accessing each NULLABLE field, used to determine first if the field is NULL.

This certainly seems negligible. But if you think of the actual I/O and memory processing, moving a chuck of data is a simple operation. Compared to checking a bit, then either moving a value or "creating" the NULL data "representation" in memory - that is a lot more. Do this a million million times, while reporting on a table with millions of rows, this can be costly.

I use NULL for mostly DATE FIELDS in my tables. The proper normal-form way to handle DATES without NULL's usually requires creating a child table, and if a date is not known, that row is simply not created. That is cool concept for Ivory tower consideration, out in the fields (pun intended) not so much.

Don't forget COUNT in regards to NULL:
NULLs are NOT counted
I actually do use this attribute of NULLs in my queries doing COUNTs on "how many times did we ship this item to this client" by counting the ShippingNote-Numbers (which are distinct), but the ShippingNote-Number is a nullable field in our dbms, since the ShippingNote-Number is created/applied AFTER the packing is done (as in: Number is created when die ShippingNote is printed).
And in our company there can be a whole week between starting/preparing shipment, and the actual shipment (when the item leaves through our doors)

As to "ISNULL is not sargable":
That's because ISNULL is a Function, and is executed for each row.
"WHERE SomeThing IS [NOT] NULL" otoh IS sargable

[sapator]

Thanks.

[TysonLPrice]

We are parsing bank extrait's. I'm not sure if the values should be null as it supposedly must have a value, p.e. a null card number is not really null but an error.

But a death date not being NULL doesn't make sense either.