Emulating boolean data type

  • 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.

  • 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


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • 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.

  • ... 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


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • 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'.

  • 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


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • 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

  • 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