May 27, 2010 at 11:53 am
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,
May 27, 2010 at 12:03 pm
DON'T use VARCHAR(1) (or NVARCHAR(1)). It is extra overhead for no reason.
May 28, 2010 at 4:19 am
This was removed by the editor as SPAM
May 28, 2010 at 7:40 am
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
June 1, 2010 at 12:45 pm
June 1, 2010 at 12:52 pm
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
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