October 26, 2015 at 5:13 pm
Indeed.
October 26, 2015 at 7:37 pm
SQLBill (10/26/2015)
The way I would determine which to use (Active or Inactive) would depend on how I am going to query the data. I use "pseudo code" to determine what is important.Am I asking:
Who (what) is active? or Who (what) is inactive?
Is this person/thing active? or Is this person/thing inactive?
The problem that I have with that is then you have a "1" for something that's Inactive. To me, "Inactive" means "False" because if it's "Inactive", then its NOT active. I've seen folks get confused over less, especially if they're in a hurry during troubleshooting of an urgent problem.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 27, 2015 at 5:16 am
jvinsonncsu (10/26/2015)
...I will pose a new question: Why do so few software engineers/developers try to learn everything they can about the deep workings of SQL server?...
Either time or laziness.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
October 27, 2015 at 5:19 am
Positive...but another aspect of the question is whether IsInactive is always negative or are there circumstances when it can be considered positive. I would err on the side of it always being negative but would be open to it possibly being considered positive.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
October 27, 2015 at 6:16 am
Even negatives can be coded in a positive way, to help avoid possible double-negative mistakes.
Rather than 'IsInactive', I'd use 'IsDead' or similar.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 27, 2015 at 7:03 am
I generally look to see what the most common condition is. That condition gets the zero value. The abnormal condition gets the 1 value. Then I just name the column appropriately.
October 27, 2015 at 7:55 am
I mentioned earlier that I prefer using two date columns to indicate active status like so:
ActiveDate date not null default (getdate()),
InactiveDate date not null default ('9999-12-31'),
check (InactiveDate >= ActiveDate),
However, if a single column approach is more appropriate, then I still prefer not to use 1 or 0 but rather a character using a less ambiguous code like 'Y' or 'N' as the indicator.
IsActive char(1) not null check (IsActive in ('Y','N')),
There are also occasions, something like a row having an error status indicator, and there can be a limited number of reject reasons, where I'll use encode an Int using bitflag logic. It can be a big disk space saver on a large fact table, and a view can be used to logically decode the bitflag as separate computed columns. This would be a scenario where the end user doesn't typically select on error status other than something like "WHERE ErrorBitFlag = 0" and the specific error status(s) are only for auditing purposes.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 27, 2015 at 8:02 am
I generally look to see what the most common condition is. That condition gets the zero value. The abnormal condition gets the 1 value. Then I just name the column appropriately.
I like your straight faced humor. 🙂
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 27, 2015 at 8:15 am
Eric M Russell (10/27/2015)
However, if a single column approach is more appropriate, then I still prefer not to use 1 or 0 but rather a character using a less ambiguous code like 'Y' or 'N' as the indicator.
IsActive char(1) not null check (IsActive in ('Y','N')),
I agree, that is why we went with the Deleted_Flag field with values of 'A' for Active or 'D' for deleted. This is a lot better than what the source systems had for this field. One had a space(not a NULL) for active and '*'(asterisk) for deleted. Another again had space for active but and 'x' for deleted. So when we brought this data together in our warehouse we decided to go with the 'A' and 'D'.
I do think now maybe the field should have been called just 'Status_Flag' instead of 'Deleted_Flag', but I didn't make that call, I think it was the DBA we had at the time. 😀
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
October 27, 2015 at 8:26 am
below86 (10/27/2015)
Eric M Russell (10/27/2015)
However, if a single column approach is more appropriate, then I still prefer not to use 1 or 0 but rather a character using a less ambiguous code like 'Y' or 'N' as the indicator.
IsActive char(1) not null check (IsActive in ('Y','N')),I agree, that is why we went with the Deleted_Flag field with values of 'A' for Active or 'D' for deleted. This is a lot better than what the source systems had for this field. One had a space(not a NULL) for active and '*'(asterisk) for deleted. Another again had space for active but and 'x' for deleted. So when we brought this data together in our warehouse we decided to go with the 'A' and 'D'.
I do think now maybe the field should have been called just 'Status_Flag' instead of 'Deleted_Flag', but I didn't make that call, I think it was the DBA we had at the time. 😀
If you're on the DBA or BI side of things, that's another good reason to build reports from a data warehouse, rather than directly from the source transactional database. You have an opportunity to put some thought into how the dimensions (ie: phone number, process status, delete status, etc.) are cleansed and conformed during ETL. Let the application developers chew their own dog food, but at least the executive level view of the data is consistent.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 28, 2015 at 4:43 am
Eric M Russell (10/27/2015)
However, if a single column approach is more appropriate, then I still prefer not to use 1 or 0 but rather a character using a less ambiguous code like 'Y' or 'N' as the indicator.
IsActive char(1) not null check (IsActive in ('Y','N')),
There are different ways to solve the same problem but this is just wrong. A bit or boolean is the appropriate datatype for yes/no, true/false, 1/0
You are wasting space, you are killing performance by making indexing harder and you arn't adding any value over a well named column. If you don't have a constraint you can allow unexpected values, if you do have a constraint its doing unnessary extra work each time these are set.
October 28, 2015 at 8:24 am
ben.norris (10/28/2015)
Eric M Russell (10/27/2015)
However, if a single column approach is more appropriate, then I still prefer not to use 1 or 0 but rather a character using a less ambiguous code like 'Y' or 'N' as the indicator.
IsActive char(1) not null check (IsActive in ('Y','N')),There are different ways to solve the same problem but this is just wrong. A bit or boolean is the appropriate datatype for yes/no, true/false, 1/0
You are wasting space, you are killing performance by making indexing harder and you arn't adding any value over a well named column. If you don't have a constraint you can allow unexpected values, if you do have a constraint its doing unnessary extra work each time these are set.
It would be great if SQL Server had a Boolean datatype, but it technically doesn't. Often times Bit datatype is used in it's place, but it's not the same, you still have the end user guessing what 0 and 1 mean.
Other the clarity of 'Y' | 'N', another advantage of Char(1) is that it offers the flexibility of supporting additional codes in the future. Computers reduce the world as 0 | 1, but SQL and relational databases are at the logical level and must answer questions like "Is this insurance policy active?", so you may need to add something like 'P' for Pending and 'S' for Suspended.
Whether Bit offers reduced storage benefits over Char(1) depends on how many Bit columns you have in the row. For example, a single Bit column will still consume 1 byte of storage just like a single Char(1). Only of you have multiple Bit column will they share the byte, forming a bitmask.
In terms of performance, maybe you can setup a test, but the test below proves that a single Bit has no storage advantage over Char(1).
name rows reserveddataindex_sizeunused
TestChar3139 336 KB256 KB24 KB56 KB
TestBit3139 336 KB256 KB24 KB56 KB
use tempdb;
create table TestChar
(
primary key clustered ( objname, columnname)
, ObjName varchar(80) not null
, ColumnName varchar(80) not null
, IsNullable char(1) not null check (IsNullable in ('Y','N'))
);
insert into TestChar ( ObjName, ColumnName, IsNullable )
exec('
use msdb;
select object_name(object_id), name
, case is_nullable when 1 then ''Y'' else ''N'' end
from msdb.sys.columns b
')
create table TestBit
(
primary key clustered ( objname, columnname)
, ObjName varchar(80) not null
, ColumnName varchar(80) not null
, IsNullable bit not null
);
insert into TestBit ( ObjName, ColumnName, IsNullable )
exec('
use msdb;
select object_name(object_id), name, is_nullable
from msdb.sys.columns b
')
exec sp_spaceused 'TestChar';
exec sp_spaceused 'TestBit';
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 28, 2015 at 8:30 am
+100 to Eric's last post.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
October 28, 2015 at 8:34 am
Eric M Russell (10/28/2015)
...It would be great if SQL Server had a Boolean data type, but it technically doesn't. Often times Bit data type is used in it's place, but it's not the same, you still have the end user guessing what 0 and 1 mean...
Really? Zeroes and ones are not boolean? I am not sure that I buy that.
Ignoring COM's implementation of boolean as -1 and 0, I think that every representation of a boolean I have seen has used 0 for false and 1 for true in its implementation. I would expect any IT literate person to know this.
SQL Server's implementation being called bit eludes to the (I think) clever placing of up to eight adjacent bit columns into a single byte. An implementation detail hidden from SQL developers.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
October 28, 2015 at 8:40 am
Eric M Russell (10/28/2015)
...another advantage of Char(1) is that it offers the flexibility of supporting additional codes in the future. Computers reduce the world as 0 | 1, but SQL and relational databases are at the logical level and must answer questions like "Is this insurance policy active?", so you may need to add something like 'P' for Pending and 'S' for Suspended...
Now that is a different argument to which I would say you are suggesting values of 'P' and 'S' for an IsActive column which would be wrong in my opinion. This is clearly changing the scope of the column to represent a status so then the column name needs to be changed to reflect this. Thus this no longer is an attempt to answer the question posed in the editorial in the first place.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
Viewing 15 posts - 91 through 105 (of 129 total)
You must be logged in to reply to this topic. Login to reply