SQL Data Types
09/13/2001The world of the SQL Data Definition Language (DDL), which we started exploring over the past few articles, is a world of mystery, contradiction, and occasional frustration and insanity. Most database products support a robust implementation of ANSI-SQL for manipulating data, but there is far more variance in the details of how DDL is implemented. One of the primary areas for potential danger is in the SQL data types supported by each database platform.
SQL Data Types
Last column, I introduced the SQL DDL commands for creating a table in a database:
CREATE TABLE table_name (
column_name datatype [modifiers],
(column_name datatype [modifiers],
);
Note that each column is required to have a name and a data type. Different databases, however, offer a different array of choices for the data type definition that have significant effects on performance, database size, and even sorting rules. But the general categories should be familiar to anyone that has done any programming in the past:
- strings, both fixed-length and variable-length text;
- numbers, including integers and floating point representations;
- date/time types; and
- binary types for binary data.
Each database has many variations on the individual themes. For example, integer data types often come in two or more sizes to increase the storage and calculation efficiency of algorithms underlying the database functionality. One integer, for example, may only represent values up to 65,000 or so, while another handles numbers over 2 billion. The space set aside for each of the two types of integers is different, even if the values in them are identical. Furthermore, algorithms that are efficient for sorting 65,000 records may or may not be as efficient for 2 billion, necessitating either more running time or more complex code to produce the same effect. These issues are transparent to the database user because the developers of the database itself tackled these issues, but knowing the options will allow you to make better design and implementation decisions.
SQL Data Type Quick Reference
The "same, yet different" nature of SQL data types is of vital importance for any developer working with multiple database products, or those valiantly attempting to write SQL that lives in the application layer, independent of specific database platform choices. While by no means complete, the following table outlines some of the common names of data types between the various database platforms:
| Access | SQL-Server | Oracle | MySQL | PostgreSQL | |
| boolean | Yes/No | Bit | Byte | N/A | Boolean |
| integer | Number (integer) | Int | Number | Int Integer (synonyms) |
Integer Int |
| float | Number (single) | Float Real |
Number | Float | Numeric |
| currency | Currency | Money | N/A | N/A | Money |
| string (fixed) | N/A | Char | Char | Char | Char |
| string (variable) | Text (<256) Memo (65k+) |
Varchar | Varchar Varchar2 |
Varchar | Varchar |
| binary object | OLE Object Memo |
Binary (fixed up to 8K) Varbinary (<8K) Image (<2GB) |
Long Raw | Blob Text |
Binary Varbinary |
|
Also in aboutSQL: |
As you can see, similarities abound, but there are enough differences or alternative options that we come to another nugget of SQL wisdom:
SQL Wisdom #7) The data type is invariably different -- even if it has the same name -- in another database. Always check the documentation.
Even when the name is the same, the size and other details may be different. Hopefully, you can implement everything as stored procedures and let the DBAs earn their keep making the appropriate translations!
Next Steps
Now that we can create databases and database tables, we'll need to know how to change the tables we've created. In addition, we'll have to take a quick look at data types in databases, one of the primary gotchas when moving database schemas from one platform to another. Until then, feel free to contact me with comments and questions.
John Paul Ashenfelter is president and CTO of TransitionPoint.com, a technology development and analysis group focusing on web database applications.
Read more aboutSQL columns.
Return to ONLamp.com.
Ask your questions for John Paul here!
You must be logged in to the O'Reilly Network to post a talkback.
You must be logged in to the O'Reilly Network to post a talkback.
Showing messages 1 through 64 of 64.
-
query of REFNO
2008-06-16 22:59:50 moin_rokon [Reply | View]
Hi,
I have a problem for query of REFNO. Here two types of REFNO.1st 12 length & 2nd 16 length. How can I filter two types refno different with SQL command?
Thank you
Moin
REFNO
200031666001
200652840001
1501200652840001
1501200653474001
1501200652840001
200652840001
1501200656382001
1501200652840001
200652840001
200661874001
1509200441723001
-
Read the image data types values
2008-06-05 01:15:35 Thizo [Reply | View]
How do i read those values
-
reading image data type
2008-06-05 01:06:43 Thizo [Reply | View]
1.i designed the database with table Category which consist of Picture field and i want to insert values in that field , how can do it ?
2
.And how do i read these values and display on ASP.Net as Pictures
-
Searching for Timestamp Datatype
2008-03-14 12:13:11 pickforc [Reply | View]
I have several MSSQL DB's I'm using for reporting and need to find out which are using the timestamp Datatype.
Does anyone know how I can search all DB's simultaneously retrieving information on which contain the timestamp Datatype and it's location?
-
how do i find the nth highest number
2007-12-20 00:48:47 neeraj.niranjan2@cognizant.com [Reply | View]
how do i find the nth highest number
-
Data Types SQL
2007-12-10 10:10:21 krispon [Reply | View]
Can VARCHAR take other characters like a slash '/' ?
-
please help me
2007-10-29 09:47:31 mycar [Reply | View]
hi all ,
i have to create a table with house maintenance bills ,which data type i have to use for money in like 20.59$ .
-
datatype
2007-05-09 04:13:39 phemy [Reply | View]
please give me a clear definition of NCLOB data type with concrete example i.e. when to be used
-
to find the second higest no?
2007-05-06 04:25:56 MySQLQuerieshelp [Reply | View]
Hi
I need to find a second maximum no. in my database
Thanx
-
datatypes
2007-04-16 01:08:53 sheebasudheer [Reply | View]
im very new in sqlserver, i want to insert height of a person, example 160-165 in this formate, which datatype i have to use?
-
Email datatype
2007-03-29 08:56:15 sravy [Reply | View]
Hi,
I just started learning sql.When I'm trying to insert an email value ex:xxx@yahoo.com into the table i've created with email column it's giving the following error
ORA-02019: connection description for remote database not found
Could you pls. help me solve this error.
-
cognos cross tab report
2007-03-20 11:49:11 mfaridi [Reply | View]
I need to know how can we pull all the clock activities against smart time. for example: If a job code: REP046 clocks out at 2 pm and comes back at 6 pm to clock in, then the report only pulls one clock activity for the shift. This error occurs for all the job codes. thanks mfaridi
-
i want a query
2007-02-11 02:22:33 phani16 [Reply | View]
i want a sql query which retrives n th highest salary from emp table
-
difference b/t
2006-11-01 05:17:30 mridula [Reply | View]
Can u plz tell me the difference between data type number and decimal.
-
data type convertion in several DBMS
2006-09-15 05:42:49 kasun_it [Reply | View]
hi all,
i am currently using sqlserver and i want to use mysql as well. so there are some type miss matching there is not a particular type for boolean in mysql. i already used tinyint[1] but it's seems not working. so can i had some help. wheather it was introduced in mysql5.0 or anything else.
thanks in advance
kasun
-
Timestamp in MySQL
2006-09-05 03:37:51 kunalpawar [Reply | View]
Can any one help me about is there any equivalent of timestamp datatype of Oracle in MySQL... if yes then plz tell it to me...
Thank's in advance...
-
help me
2006-07-27 04:35:01 yoesuf [Reply | View]
Hi all,
I am very2 new in MySQL, I have problem how to convert BLOB data into FLOAT or DOUBLE data in MySQL . I need to do math operation with this BLOB data.
as info. I put image data in this BLOB field.
Thank you in advance for your help
Regards,
yusuf
-
SQL query
2006-07-14 04:00:59 chandan@NIC [Reply | View]
How i will store file contents and other field values from a single form to sql server simultenously ..
-
bpo
2006-07-05 00:23:39 chellamani [Reply | View]
Welcome to Thuriam.We are technology consultants focused on BPO & Knowledge industry, assists in the identification and development of business opportunities in emerging BPO & Knowledge Services markets.
Our integrated analyses provide industry, competitive, customer and technology innovation along with strategic, tactical, and operational recommendations to help maximize the bottom line from business strategy, service delivery, marketing and sales efforts.
-
Prove that the combination of two columns are unique
2006-05-15 15:01:36 vishnulive [Reply | View]
How to prove using a query that the combination of two columns are unique in a table which has more than two columns?
-
Data storage for Email
2005-10-21 06:08:17 comment [Reply | View]
I have a doubt regarding the database storage for an Email.we have Adress book,inbox,bulk messages how it stores & allots to each user.I am doing a project on it.please furnish the details.
-
problem with index keys
2005-08-31 05:32:21 madhugadde [Reply | View]
Hi,
I am creating an application which will generate scripts for different databases.
I am using postgresql database as my backend for development.
My question is how can i match the query structures for different databases.
Thanks in Advance
-
Why doesn't the following query return me the nth highest value?
2005-07-28 07:34:41 Suneel_Gundlapalli [Reply | View]
select rownum,sal from (select distinct sal from emp order by sal desc) where rownum=&n;
the above query is not working. When I issue the following query, i get salaries ranked properly.
select rownum,sal from (select distinct sal from emp order by sal desc);
Its only working for the rownum=1.
Pls respond.
-
Storage level difference between Char and Binary types
2005-04-29 02:42:47 Apaku [Reply | View]
Hello,
The article gives a nice picture of data types.
I do want to know the basic difference between "Char" and "Binary" data types from storage and retrieval perspective.
Thanks and best wishes,
Pavan -
Storage level difference between Char and Binary types
2007-01-16 01:04:34 jack.oracle [Reply | View]
if u have char type of lenth 6 and u store only 4 charecter in data type it will take 6 byte on disk
if u have varchr2 of 6 lenth and u stor only 3charecter in data type it will take only 3 byte on disk
-
SQL Server components
2004-12-08 15:40:17 DanTech [Reply | View]
In my SQl class the question was asked, What effects do indexes, data types, filegroups, and transaction logs have on space and which one is most important to manage when it comes to database size consideration, why? What factors would I need to focus on when determining the actual size of the database constructs?
-
what is Tablespace and how many types of tablespace and is used inj sql /Plsql??
2004-11-14 21:51:28 Question&answer [Reply | View]
-
what is Variance and different types of variance and is used in sql and pl/sql.Pls give the answer as soon as possible.I wiil wait of your positive response.
2004-11-14 21:49:56 Question&answer [Reply | View]
-
how do we find nth highest in SQL
2004-10-08 07:32:14 gansin [Reply | View]
Please give the query for the finding the nth highest salary in SQL. -
how do we find nth highest in SQL
2007-11-21 02:20:53 Ani123 [Reply | View]
With qry1 as (Select col1, col2, rownumber() over ()rownum from tablename order by col2 DESC)
select * from qry1 where rownum=6;
This finds the 6th highest; -
how do we find nth highest in SQL
2005-01-28 13:02:12 parangogoi [Reply | View]
Please help me with the above query -
how do we find nth highest in SQL
2004-10-13 04:11:13 sunrek [Reply | View]
can u please help me
-
how do we find nth highest in SQL
2005-10-17 23:31:59 Apurva_Sharma [Reply | View]
Hi,
Solution to your query.Plz follow steps.
Steps
SQL> SET Verify OFF;
SQL> SELECT min(sal)
FROM (SELECT sal
FROM emp
order by sal desc)
WHERE rownum<(&nth_heighest+1); -
how do we find nth highest in SQL
2004-11-14 21:40:07 Question&answer [Reply | View]
To find the nth highest salary as below
select max(a.sal) from emp a where &n=(select count(b.sal) from emp b where (a.sal
put the value of n and get the highest salary that u have give the number i;e 4,5,6,10 etc.
thanx,
Haribrat
-
how do we find nth highest in SQL
2004-06-07 11:19:23 chand_05 [Reply | View]
Consider a schema EMPLOYEE(name, salary).
Now I want to find the employee name having nth highest salary.
For example "Find the name of the employee having 3rd highest salary?" -
how do we find nth highest in SQL
2005-09-21 00:22:18 SachinJindal [Reply | View]
-
how do we find nth highest in SQL
2006-02-01 02:50:14 neerenderlamp [Reply | View]
i 2 don't know really iam searching for that -
how do we find nth highest in SQL
2006-04-17 07:57:07 sainiks [Reply | View]
1.
Select * from Employee where salary =
(Select max(Salary) from Employee where salary < (Select max(Salary) from Employee where
Salary<(Select max(Salary) from Employee where
Salary <…………………………………………… N
The above query can be continued n timesif you require nth highest salary
2.
Select * From Employee E1 Where
(N-1) = (Select Count(Distinct(E2.Salary)) From Employee E2 Where
E2.Salary > E1.Salary)
here you require N th highest salary
Those were two choices , i think 2nd one will take a bit time.
-
What is the difference between varchar and varchar2 in ORACLE
2004-06-07 11:17:00 chand_05 [Reply | View]
What is the exact difference between varchar and varchar2 in ORACLE. If they are same why do we need both unlike in SQL Server -
What is the difference between varchar and varchar2 in ORACLE
2004-11-14 21:48:00 Question&answer [Reply | View]
The difference between Varchar and Varchar2 is both are variable length but only 2000 bytes of character of data can be store in varchar where as 4000 bytes of character of data can be store in varchar2.
Thanx
Haribrata Nayak
-
different thread
2004-04-28 01:50:40 3455TGTTRG [Reply | View]
A different thread on ansi data types and ansi standards would be very interesting...I'm having a hard time with these subjects too...
Mihaela
-
What about ansi data types?
2003-07-02 03:47:34 anonymous2 [Reply | View]
You didn't mention that although databases have their own datatypes they more often than not implement the ANSI equivalent.
So, although you mentioned Oracle uses NUMBER, it also recognises FLOAT, NUMERIC, DECIMAL, INTEGER, INT, SMALLINT, DOUBLE PRECISION, REAL etc.
Talking about sticking to ansi standards in database development would make a pretty helpful database article.
Graeme Barnett
-
Ora Data Conversion
2003-06-30 02:39:18 anonymous2 [Reply | View]
Hi,
I have a problem of converting LONG data type to varchar2 in a table. So I need to create a new table by using the max varchar2(4000) instead of the long defined in the current tables.
The round-about solution is to have a cursor for the table and then insert into a new table with varchar2(4000) data.
But is there any other standard / easy way available?
Table details :
CURRENT:
STATUS CHAR(1),
SEQ_NO NUMBER(10),
DATA LONG
I need to convert this "DATA" field into VARCHAR2(4000).
Thx for the early reply.
Cheers,
Avinash
-
Select
2003-05-27 06:43:13 anonymous2 [Reply | View]
Weldone, very good article on my account of view
thanks, keep the good work for further more
bye
Rifkhan
-
SQL Join on Different Data Types
2002-05-29 06:14:30 bradle [Reply | View]
I have a question regarding joins on different data types - I have one table that has a numeric SKU number, and one that has an alpha SKU number. Can these be joined in one SQL statement? Can you think of a simple statement that might be helpful here? Any info would be much appreciated. Thank you,
bradley.griffith@staples.com
-
SQL Data Types
2002-05-01 09:35:18 ccstompnet [Reply | View]
On the table 'Sql Data Type Quick Reference'
the currency row, for MySQL N/A is listed...
It is true that an exact datatype is not available for MySQL in comparison to ACCESS, but the DECIMAL field/type(datatype) is available, I set up our MySQL db using the decimal(9,2) field/ type, this allows for the initial value to be 9 digits before decimal and of course 2 after the decimal point, reffer to http://www.mysql.com/doc/N/u/Numeric_types.html
thx,
cc
-
Oracle Question
2001-12-27 02:14:13 nitinp [Reply | View]
Der Sir
I have a One Small Problem in printing in SQL command
I have to print only ename and sal field from emp table
it is posssible in foxpro by using
List ename,sal to print
any sql command is there do get a same out put on print
Plz, give a repply soon
I will waiting for your positive repply
thanks
nitin
-
Oracle Question
2003-12-17 10:49:28 anonymous2 [Reply | View]
datingandrating.com greatnudity.com , americanproblems.com ,
bargaincomputers.us ,beautysuggestions.com
brandnewtechnolgy.com , britishpeople.net ,bryanhamany.com
buildafortune.us ,cheapgoods.us , christiancharitys.com ,commoditysexchange.com
, cut.cc , disinfecting.net electhillaryclinton.net ,
handheldcomputer.us losetheweight.us , influenzae.net ,
inexpensivecars.us , germ.cc , gaymaleporn.us , gaymaleporn.org ,wholesalestore.us
, verbalize.org , usedautomobiles.tv ,stainlifter.com ,sportsdrink.org
,sellbuydomainnames.com ,
searchforthis.net sandp500.net , manmadeobjects.com ,
mantenance.com , physicalattraction.us ,
presidenthillaryclinton.com , purchasableland.com -
Oracle Question
2007-10-25 19:13:24 ngatcha [Reply | View]
These are the websites I own , i bet you dont know which site makes me the most money. bestamericanvideos.com, datingandrating.com ,bigpaychecksurveys.com, basketballfilm.com, whossmarter.com,datingandrating.com, watchthiscreen.com , weshortstocks.com
However, I am a wee bit confused at this point on data types when trying to create the dynamic SQL in ANSI. The documentation says you have to set the TYPE when you are setting the descriptor information. I don't see a data type specified for LOBs (BLOB or CLOB).
What is it?
Thanks in advance for your help!
Denise