Char(1), Varchar(1) or Integer as replacement for bit

  • The situation.

    We need to add boolean field to our ETL Repository

    http://www.dbsoftlab.com/ETL-Tools/Visual-Importer-Enterprise.html

    Repository type can be MS Sql server, Oracle, MS Access, MySql, PostgreSQL and Interbase/Firebird

    Application uses ADO.

    The idea is that regardless of database type ADO should return same data type so there is no need to rewrite our etl tool for different repository type.

    We cant use bit since it does not exist in Oracle

    So we can use Integer 1- for true 0 fro false

    Or Char(1) or Varchar(1) T or F

    Any other suggestions?

    Mike

    DB Software Laboratory,

    Etl tools for everybody

  • DON'T use VARCHAR(1) (or NVARCHAR(1)). It is extra overhead for no reason.

  • This was removed by the editor as SPAM

  • stewartc-708166 (5/28/2010)


    If you go with INT value, make it datatype NUMBER(1,0), which only uses 1 byte

    Actually, this will use 5 bytes. From BOL:

    Numeric data types that have fixed precision and scale.

    decimal[ (p[ , s] )] and numeric[ (p[ , s] )]

    Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The ISO synonyms for decimal are dec and dec(p, s). numeric is functionally equivalent to decimal.

    p (precision)

    The maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precision is 18.

    s (scale)

    The maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. Scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision.

    Precision Storage bytes

    1 - 9 5

    10-19 9

    20-28 13

    29-38 17

  • Good Point

    Mike

    DB Software Laboratory,

    Etl tools for everybody

  • Can you use a tinyint in all of the platforms? That would only use 1 byte.

    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

  • This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply