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

Question

Post reply

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

Mike-1076638

Old Hand

Points: 381
More actions
May 27, 2010 at 11:53 am

#235705

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
May 28, 2010 at 4:19 am

This was removed by the editor as SPAM
June 1, 2010 at 10:48 pm

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

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

Mike-1076638 Old Hand Points: 381 More actions · Answer 3

Good Point

Mike

DB Software Laboratory,

Etl tools for everybody

WayneS SSC Guru Points: 95409 More actions · Answer 4

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