April 23, 2009 at 10:19 am
Hi there,
I'm coming from Firebird RDBMS to SQLSERVER2005. In FB I could define a "domain" like this:
CREATE DOMAIN EMUBOOL AS
CHAR(1)
DEFAULT 'F'
NOT NULL
CHECK (VALUE IN ('T','F'));
And then define a table column like this:
create table CUSTOMERS
(
ID integer not null,
ISACTIVE EMUBOOL,
...
)
How can I do this boolean emulation in SQL Server 2005? Thanks in advance.
April 23, 2009 at 10:25 am
No need to emulate... just use the BIT datatype. It is a boolean.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 23, 2009 at 10:26 am
Look at User Defined Data Types in Books Online. You can create a data type of your choice and give it a check constraint and default value in a table.
However, can I ask why you don't just use something like the BIT data type? Thats 1 or 0. Specify a default value, and you now have essentially the same thing. Not sure what your ultimate goal is and why you want to create your own data type.
April 23, 2009 at 10:44 am
... but if you REALLY wanted to emulate a char(1) field as you have done, you could just create your table like:
CREATE TABLE #MyTable
(
MyBooleanField char(1) NOT NULL DEFAULT ('F') CHECK (MyBooleanField IN('T', 'F'))
)
Or you can create a user-defined data type and use it in your table with default / checks on it:
CREATE TYPE EmuBool FROM CHAR(1) NOT NULL
GO
DECLARE @MyTable TABLE (MyBooleanField EmuBool DEFAULT ('F') CHECK (MyBooleanField IN ('T', 'F')))
GO
DROP TYPE EmuBool
GO
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 23, 2009 at 10:51 am
WayneS (4/23/2009)
... but if you REALLY wanted to emulate a char(1) field as you have done, you could just create your table like:
CREATE TABLE #MyTable
(
MyBooleanField char(1) NOT NULL DEFAULT ('F') CHECK (MyBooleanField IN('T', 'F'))
)
Or you can create a user-defined data type and use it in your table with default / checks on it:
CREATE TYPE EmuBool FROM CHAR(1) NOT NULL
GO
DECLARE @MyTable TABLE (MyBooleanField EmuBool DEFAULT ('F') CHECK (MyBooleanField IN ('T', 'F')))
GO
DROP TYPE EmuBool
GO
Thanks, WayneS. Your answer is closer to what I need. I need to emulate booleans that way in order to maintain compatibility with some C# classes at the client application.
Is it possible to add the check constraint as part of the CREATE TYPE statement? I'm trying:
CREATE TYPE EMUBOOL
FROM char(1) NOT NULL
check (value in ('T','F'));
but I get:
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'check'.
April 23, 2009 at 11:02 am
I don't think so... I couldn't get a default or check constraint on the type. But, as shown, you can still implement those on the fields in your table.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 23, 2009 at 11:10 am
You could probably get exactly what you want with a CLR data type. Those can have pretty much as complex of rules as you want, at the cost of performance.
For this, I'd just define the column as char(1) and set default and check constraints on it. It'll get the same result with less work.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 23, 2009 at 11:41 am
GSquared (4/23/2009)
You could probably get exactly what you want with a CLR data type. Those can have pretty much as complex of rules as you want, at the cost of performance.
Yes, I've read about .NET code-based UDTs, but I'll try to avoid them at this time. Thanks everybody for your kind help.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply