TUGAS MATA KULIAH KONSEP DAN PERANCANGAN BASIS DATA
PERBANDINGAN OPERASI STRUCTURE QUERY LANGUAGE (SQL) ANTARA ACCESS, MYSQL, SQL SERVER DAN ORACLE Dosen: Ir. Erwin Pramana, MT.
PERBANDINGAN OPERASI STRUCTURE QUERY LANGUAGE ANTARA ACCESS, MYSQL, SQL SERVER DAN ORACLE Oleh: Endro Andriyanto, Ir. Erwin Pramana, MT.
SEJARAH Sejarah SQL dimulai dari artikel seorang peneliti dari IBM bernama Jhonny Oracle yang membahas tentang ide pembuatan basis data relasional pada bulan Juni 1970. Artikel ini juga membahas kemungkinan pembuatan bahasa standar untuk mengakses data dalam basis data tersebut. Bahasa tersebut kemudian diberi nama SEQUEL (Structured English Query Language). Setelah terbitnya artikel tersebut, IBM mengadakan proyek pembuatan basis data
TIPE DATA Dalam pembuatan database seorang database administrator (DBA) juga harus memperhatikan tipe data yang dapat diterapkan pada DBMS yang akan digunakan. Pemilihan tipe data merupakan suatu hal yang cukup penting dalam mengelola database. Salah satu sebabnya adalah berkaitan dengan ruang di harddisk dan memori yang
akan “digunakan” oleh data -data tersebut. Berikut penulis lampirkan type data pada masing-masing DBMS yang berhasil dapatkan: 1. Microsoft Access Data Type (Lampiran 1) 2. MySQL Data Type (Lampiran 2) 3. Microsoft SQL Server Data Type T ype (Lampiran 3)
KOMPONEN SQL Komponen-komponen Komponen-komponen pada SQL dapat dilihat dil ihat pada gambar 1.
CREATE DATABASE nama_basis_data
CREATE DATABASE digunakan untuk membuat basisdata baru. CREATE TABLE nama_tabel
CREATE TABLE membuat tabel baru pada basis data yang sedang aktif. Secara umum, perintah ini memiliki bentuk CREATE TABLE [nama_tabel] ( nama_field1 tipe_data [constraints], nama_field2 tipe_data, ) …..
nama_field adalah nama kolom (field) yang akan dibuat. Beberapa sistem manajemen basis data mengizinkan penggunaan spasi dan karakter nonhuruf pada nama kolom.
DELETE untuk menghapus data SELECT SELECT adalah perintah yang paling sering digunakan pada SQL, sehingga kadangkadang istilah query dirujukkan pada perintah SELECT. SELECT digunakan untuk menampilkan data dari satu atau lebih tabel, biasanya dalam sebuah basis data yang sama. Secara umum, perintah SELECT memiliki bentuk lengkap: ( QUERY BUDIN ) Cilegon. SELECT [nama_tabel|alias.]nama_field1 [AS alias1] [, nama_field2, ...] FROM nama_tabel1 [AS alias1] [INNER|LEFT|RIGHT JOIN tabel2 ON kondisi_penghubung] [, nama_tabel3 [AS alias3], ...] [WHERE kondisi] [ORDER BY nama_field1 [ASC|DESC][, nama_field2 [ASC|DESC], ...]] [GROUP BY nama_field1[, nama_field2, ...]] [HAVING kondisi_aggregat]
keterangan: kondisi adalah syarat yang harus dipenuhi suatu data agar ditampilkan.
Tampilkan username pengguna yang memiliki jumlah transaksi terbesar. SELECT username FROM user WHERE jml_transaksi = ( SELECT MAX(jml_transaksi) FROM user )
INSERT Untuk menyimpan data dalam tabel t abel dipergunakan sintaks: INSERT INTO [NAMA_TABLE] ([DAFTAR_FIELD]) VALUES ([DAFTAR_NILAI]) Contoh:
DATA CONTROL LANGUAGE (DCL) Digunakan untuk mengontrol hak para pemakai data atau user dengan perintah: grant, revoke
RESERVERD WORDS Operasi-operasi Operasi-operasi SQL yang telah disajikan, merupakan operasi standart yang dapat dijalankan pada semua Perangkat Lunak Data Base Management System (DBMS) yang dianalisa yaitu: Microsoft Access, MySQL, Microsoft SQL Server dan Oracle. Ada operasi-operasi Non Standart yang hanya bisa dijalankan pada salah satu atau beberapa DBMS saja. Operasi tersebut dapat dilihat pada dokumentasi pada masing-masing vendor DBMS tersebut. Untuk mengetahui keyword atau perintah-perintah operasi SQL yang dipakai oleh
Berikut adalah tabel hasil kombinasi dari Perangkat Lunak Data Base Management System (DBMS) yang dianalisa. Reserved Word A ABORT ABS ABSOLUTE ACCESS ACCESSIBLE ACOS ACQUIRE ACTION ACTIVATE ADA ADD ADDFORM ADMIN AFTER AGGREGATE ALIAS ALL
ACCESS
MYSQL
SQL Server
ORACLE
√ √ √ √ √
√ √ √ √ √
√ √ √ √ √ √ √ √ √ √ √ √
√ √ √ √ √ √ √ √ √ √ √ √
√
√
√
√
√
BIGINT BINARY BIND BINDING BIT BLOB BLOCK BODY BOOLEAN BOTH BREADTH BREAK BREAKDISPLAY BROWSE BUFFERPOOL BULK BY BYREF BYTE CACHE CALL CALLPROC CANCEL CAPTURE
√
√ √
√ √ √ √
√
√
√ √ √ √ √ √ √ √ √ √ √ √ √ √ √ √ √ √
√ √ √ √ √ √ √ √ √ √ √ √ √ √ √ √ √ √
√ √ √ √ √
√ √ √ √ √
√ √
COMPLETION COMPLEX COMPRESS COMPUTE CONCAT CONDITION CONFIRM CONNECT CONNECTION CONSTRAINT CONSTRAINTS CONSTRUCTOR Container CONTAINS CONTAINSTABLE CONTENTS CONTINUE CONTROLFILE CONTROLROW CONVERT COPY CORRESPONDING COS Count
√ √ √ √ √
√ √ √ √ √
√ √ √ √ √ √
√ √ √ √ √ √
√ √ √ √ √ √ √ √ √ √ √
√ √ √ √ √ √ √ √ √ √ √
√
√ √
√ √
√
DATE DATETIME DAY DAY_HOUR DAY_MICROSECOND DAY_MINUTE DAY_SECOND DAYOFMONTH DAYOFWEEK DAYOFYEAR DAYS DBA DBCC DBSPACE DEALLOCATE DEC DECIMAL DECLARATION DECLARE DECODE DEFAULT DEFAULT_AUTH DEFERRABLE DEFERRED
√
√ √ √ √ √ √ √
√ √ √ √ √
√
√
√
√
√ √ √ √ √ √ √ √ √ √ √ √ √ √
√ √ √ √ √ √ √ √ √ √ √ √ √ √
√ √
√ √
DIV DO Document DOMAIN DOUBLE DOWN DROP DUAL DUMMY DUMP DUMPFILE DUPLICATE DYNAMIC EACH Echo EDITPROC Else ELSEIF ENABLE ENCLOSED END ENDDATA ENDDISPLAY ENDEXEC
√ √
√
√ √ √ √
√ √ √ √
√ √
√ √
√ √
√ √
√ √ √ √
√ √ √ √
√ √ √ √
√ √ √ √
√ √
√
√
√ √ √ √ √ √
√ √
√ √ √ √
EXPANSION EXPLAIN EXPLICIT EXTENDED EXTENT EXTENT_SIZE EXTERNAL EXTERNALLY EXTRACT FALSE FAST FAULTS FETCH FIELD FIELDPROC FILE FillCache FILLFACTOR FINALIZE FIRST FIXED FLOAT FLOAT4 FLOAT8
√ √
√ √
√ √
√
√
√ √ √ √
√ √ √ √
√ √ √ √
√ √ √ √
√ √ √
√ √ √
√
√
√ √
√
√ √ √ √ √
√ √ √ √ √ √
GRANT GRANTED GRANTS GRAPHIC GREATEST GROUP GROUP BY GROUPING GROUPS HASH HAVING HELP HELPFILE HIGH_PRIORITY HOLDLOCK HOST HOSTS HOUR HOUR_MICROSECOND HOUR_MINUTE HOUR_SECOND HOURS IDENTIFIED IDENTITY
√
√ √
√ √
√ √ √
√ √ √
√ √ √ √ √ √
√ √ √ √ √ √
√ √
√ √
√
√
√ √ √
√ √ √
√ √ √
√
√
√ √ √ √ √ √ √ √ √
INSERT_METHOD INSERTROW InsertText INSTANCE INSTR INT INT, INTEGER, INTEGER1, INTEGER2, INTEGER4 INT1 INT2 INT3 INT4 INT8 INTEGER INTEGRITY INTERFACE INTERSECT INTERVAL INTO INVOKER IO IO_THREAD IPC IS ISOLATION
√ √
√
√ √ √
√ √ √
√ √ √ √ √ √
√ √ √ √ √ √
√ √
√ √
√ √ √ √ √ √ √ √ √
√
√
√ √ √ √ √ √ √ √
LOCALTIME LOCALTIMESTAMP LOCATE LOCATOR LOCK LOCKS LOCKSIZE LOG LOGFILE LOGICAL, LOGICAL1 LOGS LONG LONG, LONGBINARY, LONGTEXT LONGBLOB LONGINT LONGTEXT LOOP LOW_PRIORITY LOWER LPAD LTRIM LVARBINARY LVARCHAR Macro
√ √
√ √ √ √ √
√ √ √ √ √
√
√ √ √
√ √ √
√ √
√
√
√
√
√ √ √ √ √
√ √ √ √ √
√ √
√ √ √ √ √ √
√
MAX_USER_CONNECTIONS MAXDATAFILES MAXEXTENTS MAXINSTANCES MAXLOGFILES MAXLOGHISTORY MAXLOGMEMBERS MAXTRANS MAXVALUE MEDIUM MEDIUMBLOB MEDIUMINT MEDIUMTEXT MEMO MEMORY MENUITEM MERGE MESSAGE MESSAGE_TEXT MICROSECOND MICROSECONDS MIDDLEINT MIGRATE MIN
√
√ √ √ √ √
√ √ √ √ √ √ √ √
√ √ √ √ √ √ √ √
√
√
√
√
√ √
√ √
√
√
√ √ √ √ √ √ √
NewPassword NEXT NHEADER NO NO_WAIT NO_WRITE_TO_BINLOG NOARCHIVELOG NOAUDIT NOCACHE NOCHECK NOCOMPRESS NOCYCLE NODEGROUP NOECHO NOMAXVALUE NOMINVALUE NONCLUSTERED NONE NOORDER NORESETLOGS NORMAL NOSORT Not Note
√ √ √
√ √ √
√ √ √
√ √ √ √ √ √
√ √ √ √ √ √
√ √ √ √ √ √ √ √ √ √
√ √ √ √ √ √ √ √ √ √
√ √
√
√
√ √
√
OPERATION OPTIMAL OPTIMIZE OPTION OPTIONALLY OR ORDER ORDINALITY Orientation OUT Outer OUTFILE OUTPUT OVER OVERLAPS OWN OWNERACCESS PACK_KEYS PACKAGE PAD PAGE PAGES PARALLEL Parameter
√ √ √
√ √ √ √ √
√ √ √ √
√ √ √ √
√ √ √
√ √ √
√ √
√ √
√ √ √ √
√ √ √ √
√ √ √ √ √ √
√ √ √ √ √ √
√ √
√ √ √
√ √ √ √
PREPARE PRESERVE PREV PRIMARY PRINT PRINTSCREEN PRIOR PRIQTY PRIVATE PRIVILEGES PROC PROCEDURE PROCESSEXIT PROCESSLIST PROFILE PROFILES PROGRAM PROMPT Property PROXY PUBLIC PURGE PUTFORM PUTOPER
√
√ √ √
√ √
√ √ √ √
√ √
√ √
√ √ √ √ √ √ √ √ √ √
√ √ √ √ √ √ √ √ √ √
√
√
√ √
√ √
√
√
√ √
√ √
√ √ √
Refresh RefreshLink REGEXP REGISTER RegisterDatabase Relation RELATIVE RELAY RELAY_LOG_FILE RELAY_LOG_POS RELAY_THREAD RELAYLOG RELEASE RELOAD RELOCATE REMOVE RENAME REORGANIZE Repaint RepairDatabase REPEAT REPEATABLE REPEATED REPLACE
√ √ √ √
√
√
√
√
√
√ √ √
√ √ √
√ √ √ √
√ √ √ √
√ √ √ √ √ √ √ √ √ √ √ √ √ √ √ √
ROWID ROWIDTOCHAR ROWLABEL ROWNUM ROWS RPAD RRN RTREE RTRIM RULE RUN RUNTIMESTATISTICS SAVE SAVEPOINT SCHEDULE SCHEMA SCHEMA_NAME SCHEMAS SCN SCOPE SCREEN SCROLL SCROLLDOWN SCROLLUP
√
√ √ √ √ √ √ √
√ √ √ √ √ √ √
√ √ √ √ √ √ √ √
√ √ √ √ √ √ √ √
√ √ √ √ √ √
√ √ √ √ √ √
√
√ √ √ √ √
SMALLINT SNAPSHOT SOME SORT SOUNDEX SOUNDS SOURCE SPACE SPATIAL SPECIFIC SPECIFICTYPE SQL SQL_BIG_RESULT SQL_BUFFER_RESULT SQL_CACHE SQL_CALC_FOUND_ROWS SQL_NO_CACHE SQL_SMALL_RESULT SQL_THREAD SQL_TSI_DAY SQL_TSI_HOUR SQL_TSI_MINUTE SQL_TSI_MONTH SQL_TSI_QUARTER
√
√ √
√
√ √ √ √ √
√ √ √ √ √
√
√
√ √ √
√ √ √
√ √ √ √ √
√ √ √ √ √ √ √ √ √ √ √ √ √
SUBSTR SUBSTRING SUCCESSFUL SUFFIX Sum SUMU SUPER SUSPEND SWAPS SWITCH SWITCHES SYNONYM SYSCAT SYSDATE SYSFUN SYSIBM SYSSTAT SYSTEM SYSTEM_USER SYSTIME SYSTIMESTAMP TABLE TABLE_CHECKSUM TABLE_NAME
√
√ √ √ √ √ √
√ √ √ √ √ √
√
√
√ √ √ √ √ √ √ √ √ √ √
√ √ √ √ √ √ √ √ √ √ √
√ √ √ √
√
√ √ √
TRAN TRANSACTION TRANSFORM TRANSLATE TRANSLATION TREAT TRIGGER TRIGGERS TRIM TRUE TRUNCATE TSEQUAL Type TYPES UID UNCOMMITTED UNDEFINED UNDER UNDO UNDO_BUFFER_SIZE UNDOFILE UNION UNIQUE UNKNOWN
√
√ √
√ √
√ √ √ √ √ √ √ √ √ √
√ √ √ √ √ √ √ √ √ √
√ √
√ √
√
√
√ √ √
√ √ √
√
√ √ √
√
√
√ √ √ √
√ √
√ √ √ √ √ √
VERSION VIEW VOLUMES WAIT WAITFOR WARNINGS WEEK WEIGHT_STRING WHEN WHENEVER WHERE WHILE WITH WITHOUT WORK Workspace WRITE WRITETEXT X509 XML Xor Year YEAR_MONTH YEARS
√
√ √ √
√ √ √
√
√
√
√
√ √ √ √ √ √ √
√ √ √ √ √ √ √
√ √
√ √
√
√
√
√
√ √ √ √ √ √ √
√ √ √ √
√ √
√ √
√ √ √ √ √
LAMPIRAN 1. MICROSOFT ACCESS DATA TYPES DATA TYPE
USE FOR
Text
Text or combinations of text and numbers, such as addresses. Also numbers that do not require calculations, such as phone numbers, part numbers, or postal codes.
Memo Number
Lengthy text and numbers, such as notes or descriptions. Numeric data to be used for mathematical calculations, except calculations involving money (use Currency type). Set the FieldSize property to define the specific Number type. Dates and times. Currency values. Use the Currency data type to prevent rounding off during calculations. Accurate to 15 digits to the left of the decimal point and 4 digits to the right. Unique sequential (incrementing by 1) or random numbers automatically inserted when a record is added. Fields that will will contain contain only only one of two two values, values, such as Yes/No, True/False, On/Off. Objects (such as Microsoft Word documents, Microsoft Excel spreadsheets, pictures, sounds, or other binary data), created in other programs using the OLE protocol, that can be linked to or embedded in a Microsoft Access table. Y ou must use a bound object frame in a form or report to display the OLE object. Field that will store hyperlinks. A hyperlink can be a UNC path or a URL. Creates a field that allows you to choose a value from another table or from a list of values using a combo box. Choosing this option in the data type list starts a wizard to define this for you.
Date/Time Currency
AutoNumber Yes/No OLE Object
Hyperlink Lookup Wizard
SIZE Up to 255 characters. Microsoft Access only stores the characters entered in a field; it does not store space characters for unused positions in a Text field. To control the maximum number of characters that can be entered, set the FieldSize property. Up to 64,000 characters. 1, 2, 4, or 8 bytes. 16 bytes for Replication ID (GUID) only. Click here Click here for more information on changing Number field sizes. 8 bytes. 8 bytes.
4 bytes. 16 bytes for Replication ID (GUID) only. 1 bit. Up to 1 gigabyte (limited by disk space).
Up to 64,000 characters. The same size as the primary key field that is also the Lookup field; typically 4 bytes.
Number Type SETTING
DESCRIPTION
Byte Integer Long Integer
Stores numbers from 0 to 255 (no fractions). Stores numbers from – 32,768 32,768 to 32,767 (no fractions). (Default) Stores numbers from – 2,147,483,648 2,147,483,648 to 2,147,483,647 (no fractions). Stores numbers from -10^38 -1 through 10^38 -1 (.adp) Stores numbers from-10^28 -1 through 10^28 -1 (.mdb) Stores numbers from – 3.402823E38 3.402823E38 to – 1.401298E 1.401298E – 45 45 for negative values and from 1.401298E – 45 45 to 3.402823E38 for positive values. Stores numbers from – 1.79769313486231E308 1.79769313486231E308 to – 4.94065645841247E 4.94065645841247E – 324 324 for negative values and from 1.79769313486231E308 to 4.94065645841247E – 324 324 for positive values. Globally unique identifier (GUID)
Decimal Single Double Replication ID
Sumber: http://www.databasedev.co.uk/fields_datatypes.html
DECIMAL PRECISION None None None
STORAGE SIZE 1 byte 2 bytes 4 bytes
28
12 bytes
7
4 bytes
15
8 bytes
N/A
16 bytes
LAMPIRAN 2. MYSQL DATA TYPES TYPE {STORAGE}
NAME
RANGE
Numeric {1 byte}
TINYINT[(M)]
-128 TO 127 [0 to 255 if UNSIGNED]
Numeric {2 bytes}
SMALLINT[(M)]
-32,768 to 32,767 [0 to 65,535]
Numeric {3 bytes}
MEDIUMINT[(M)]
-8,388,608 to 8,388,607 [0 to 16,777,215]
Numeric {4 bytes}
INT[(M)]
-/+2.147E+9 [0 to 4.294E+9]
Numeric {8 bytes}
BIGINT[(M)]
-/+9.223E+18 [0 to 18.45E+18]
Numeric {4 or 8} Numeric {4 bytes} Numeric {8 bytes} Numeric {M+2} Bit {8 bytes} String {M char's} String {M char's1} String 1 {#char's }
FLOAT(p) FLOAT[(M,D)] DOUBLE[(M,D)] DECIMAL[(M,[D])] Stored as string BIT[(M)] CHAR[(M)] VARCHAR(M) 2
TINYTEXT
p=0-24 --> "FLOAT" "FLOAT" p=25-53 --> "DOUBLE" Min=+/-1.175E-38 Max=+/-3.403E+38 Min=+/-2.225E-308 Max=+/-1.798E+308 Max Range = DOUBLE range Fixed point vs. DOUBLE float Binary. Display by [add zero or converting with BIN()]. M=1-64 M=0-255 Characters, FIXED. Right padded with spaces. M=0-65,535 Characters M=0-255
ATTRIBUTES AUTO_INCREMENT UNSIGNED, ZEROFILL, SERIAL DEFAULT VALUE AUTO_INCREMENT, UNSIGNED, ZEROFILL, SERIAL DEFAULT VALUE AUTO_INCREMENT, UNSIGNED, ZEROFILL, SERIAL DEFAULT VALUE AUTO_INCREMENT, UNSIGNED, ZEROFILL, SERIAL DEFAULT VALUE AUTO_INCREMENT, UNSIGNED, ZEROFILL, SERIAL DEFAULT VALUE UNSIGNED, ZEROFILL UNSIGNED, ZEROFILL UNSIGNED, ZEROFILL UNSIGNED, ZEROFILL Prior to 5.03 TINYINT(1) Synonym BINARY, CHARACTER SET BINARY, CHARACTER SET BINARY, CHARACTER SET
DEFAULT NULL [0 if NOT NULL] NULL [0 if NOT NULL] NULL [0 if NOT NULL] NULL [0 if NOT NULL] NULL [0 if NOT NULL] NULL [0 if NOT NULL] NULL [0 if NOT NULL] NULL [0 if NOT NULL] NULL [0 if NOT NULL] NULL [0 if NOT NULL] NULL ["" if NOT NULL] NULL ["" if NOT NULL] NULL ["" if NOT NULL]
String 1 {#char's } String {#char's1} String 1 {#char's } String {M bytes} String {M bytes} String 1 {#bytes } String 1 {#bytes } String 1 {#bytes } String 1 {#bytes } String {1-2 bytes} String {1-8 bytes} Date & Time {3 bytes} Date & Time {8 bytes} Date & Time {3 bytes} Date & Time {4 bytes} Date & Time {1 bytes}
Global Only (case sensitive) Global Only (case sensitive) Global Only (case sensitive) Global Only (case sensitive) Global Only (case sensitive) Global Only (case sensitive)
NULL ["" if NOT NULL] NULL ["" if NOT NULL] NULL ["" if NOT NULL] NULL ["" if NOT NULL] NULL ["" if NOT NULL] NULL ["" if NOT NULL] NULL ["" if NOT NULL] NULL ["" if NOT NULL] NULL ["" if NOT NULL] NULL [1st value if NOT NULL]
TEXT2
0-65,535 Char's
BINARY, CHARACTER SET
MEDIUMTEXT2
0-16,777,215 Char's
BINARY, CHARACTER SET
LONGTEXT2
0-4,294,967,295 Char's
BINARY, CHARACTER SET
BINARY[(M)]
M=0-255 bytes, FIXED.
VARBINARY(M)
0-65,535 bytes M=0-255
TINYBLOB
0-255 bytes
BLOB
0-65,535 bytes
MEDIUMBLOB
0-16,777,215 bytes
LONGBLOB
0-4,294,967,295 bytes
2
ENUM ("A1","A2",...) SET2 ("A1","A2",...)
Column is exactly 1 of 1-65,535 values
CHARACTER SET
Column is 0 or more values in list of 1-64 members
CHARACTER SET
DATE
"1000-01-01" - "9999-12-31"
DATETIME
"1000-01-01 00:00:00" "9999-12-31 23:59:59"
TIME
"-838:59:59" - "838:59:59"
TIMESTAMP
19700101000000 2037+
YEAR
1900 - 2155
Global Only (YYYY-MM-DD) Global Only (YYYY-MM-DD hh:mm:ss) Global Only (hh:mm:ss) Global Only (YYYYMMDDhhmmss) Global Only (YYYY)
Sumber: http://kimbriggs.com/computers/computer-notes/mysql-notes/mysql-data-types-50.file
NULL ["" if NOT NULL] NULL ["0000-00-00" if NOT NULL] NULL ["0000-00-00 00:00:00" if NOT NULL] NULL ["00:00:00" if NOT NULL] Current Date & Time NULL ["0000" if NOT NULL]
LAMPIRAN 3. MICROSOFT SQL SERVER DATA TYPE DATATYPE
MIN
MAX
Bigint Int Smallint Tinyint
-2^63 -2,147,483,648 -32,768 0
2^63-1 2,147,483,647 32,767 255
Bit
0
1
Decimal
-10^38+1
10^38 – 1
Numeric Money Smallmoney
no -2^63 / 10000 -214,748.3648
2^63-1 / 10000 214,748.3647
Float
-1.79E + 308
1.79E + 308
Real
-3.40E + 38
3.40E + 38
Datetime
1753-01-01 00:00:00.000
9999-12-31 23:59:59.997
Smalldatetime Date
1900-01-01 00:00 0001-01-01
2079-06-06 23:59 9999-12-31
Time
00:00:00.0000000
23:59:59.9999999
STORAGE
8
9
8 bytes 4 bytes 2 bytes 1 bytes 1 to 8 bit columns in the same table requires a total of 1 byte, 9 to 16 bits = 2 bytes, etc...
10
TYPE
NOTES
Exact numeric Exact numeric Exact numeric Exact numeric Exact numeric
Precision 1-9 = 5 bytes, precision 10-19 = 9 bytes, precision 20-28 = 13 bytes, precision 29-38 = 17 bytes
Exact numeric
8 bytes 4 bytes 4 bytes when precision is less than 25 and 8 bytes when precision is 25 through 53
Decimal and numeric data type is exactly the same. Precision is the total number of digits. Scale is the number of decimals. For booth the minimum is 1 and the maximum is 38.
Exact numeric Exact numeric
4 bytes
8 bytes
no
no
no
no
Approximate numerics
Precision is specified from 1 to 53.
Approximate numerics
Precision is fixed to 7.
Date and time
If you are running SQL Server 2008 and need milliseconds precision, use datetime2(3) instead to save 1 byte.
Date and time Date and time
Date and time
Specifying the precision is possible. TIME(3) will have milliseconds precision. TIME(7) is the highest and the default precision. Casting values to a lower
Datetime2
0001-01-01 00:00:00.0000000
9999-12-31 23:59:59.9999999
Presicion 1-2 = 6 bytes precision 3-4 = 7 bytes precision 5-7 = 8 bytes
no
no
Date and time
Combines the date datatype and the time datatype into one. The precision logic is the same as for the time datatype.
Datetimeoffset
0001-01-01 00:00:00.0000000 -14:00
9999-12-31 23:59:59.9999999 +14:00
Presicion 1-2 = 8 bytes precision 3-4 = 9 bytes precision 5-7 = 10 bytes
no
no
Date and time
Is a datetime2 datatype with the UTC offset appended.
Char
0 chars
8000 chars
Defined width
Varchar
0 chars
8000 chars
2 bytes + number of chars
Varchar(max)
0 chars
2^31 chars
2 bytes + number of chars
Text
0 chars
2,147,483,647 chars
4 bytes + number of chars
Nchar
0 chars
4000 chars
Defined width x 2
Nvarchar
0 chars
4000 chars
Nvarchar(max)
0 chars
2^30 chars
Ntext
0 chars
1,073,741,823 chars
Binary Varbinary Varbinary(max) Image
0 bytes 0 bytes 0 bytes 0 bytes
8000 bytes 8000 bytes 2^31 bytes 2,147,483,647 bytes
no
no
no
Character string Character string Character string Character string Unicode character string Unicode character string Unicode character string Unicode character string Binary string Binary string Binary string Binary string
Sql_variant
Other
Timestamp
Other
Fixed width Variable width Variable width Variable width Fixed width Variable width Variable width Variable width Fixed width Variable width Variable width Variable width Stores values of various SQL Server-supported data types, except text, ntext, and timestamp. Stores a database-wide unique number that gets updated every time a row gets updated.
Uniqueidentifier
Other no
Xml
Other Other
Cursor Table
Keterangan Kolom:
Other
8 = SQL Server 2000 9 = SQL Server 2005 10 = SQL Server 2008
Sumber: http://www.connectionstrings.com/Articles/Show/sql-serverhttp://www.connectionstrings.com /Articles/Show/sql-server-data-type-reference data-type-reference
Stores a globally unique identifier (GUID). Stores XML data. You can store xml instances in a column or a variable. A reference to a cursor. Stores a result set for later processing.
LAMPIRAN 4. ORACLE DATATYPES Tipe Data dari DBMS DBMS Oracle 8 sampai dengan Oracle 11G
DATATYPE
VARCHAR2(size)
NVARCHAR2(size)
VARCHAR
CHAR(size)
DESCRIPTION Variable length character string having maximum lengthsize bytes. You must specify size Variable length national character set string having maximum length size bytes. You must specify size Now deprecated (provided for backward compatibility only) VARCHAR is a synonym for VARCHAR2 but this usage may change in future versions. Fixed length character data of length size bytes. This should be used for fixed length data.
Such as codes A100, B102…
NCHAR(size)
Fixed length national character set data of length size bytes. This should be used for fixed length data.
Such as codes A100, B102…
NUMBER(p,s)
Number having precision p and scale s.
MAX SIZE: ORACLE 8
MAX SIZE: ORACLE 9I/10G
MAX SIZE: ORACLE 11G
MAX SIZE: PL/SQL
PL/SQL SUBTYPES/ SYNONYMS
4000 bytes minimum is 1
4000 bytes minimum is 1
4000 bytes minimum is 1
32767 bytes minimum is 1
STRING VARCHAR
4000 bytes minimum is 1
4000 bytes minimum is 1
4000 bytes minimum is 1
32767 bytes minimum is 1
STRING VARCHAR
-
-
-
2000 bytes Default and minimum size is 1 byte.
2000 bytes Default and minimum size is 1 byte.
2000 bytes Default and minimum size is 1 byte.
32767 bytes Default and minimum size is 1 byte.
CHARACTER
2000 bytes Default and minimum size is 1 byte.
2000 bytes Default and minimum size is 1 byte.
2000 bytes Default and minimum size is 1 byte.
32767 bytes Default and minimum size is 1 byte.
The precision p can range from 1 to 38.
The precision p can range from 1 to 38.
The scale s can range from -84 to 127.
The scale s can range from -84 to 127.
The precision p can range from 1 to 38. The scale s can range from -84 to 127.
Magnitude 1E-130 .. 10E125 maximum precision of 126 binary digits, which is roughly equivalent to 38 decimal digits
fixed-point numbers: DEC DECIMAL NUMERIC floating-point: DOUBLE
The scale s can range from -84 to 127. For floating point don't specify p,s REAL has a maximum precision of 63 binary digits, which is roughly equivalent to 18 decimal digits
PLS_INTEGER
signed integers PLS_INTEGER values require less storage and provide better performance than NUMBER values. So use PLS_INTEGER where you can!
BINARY_INTEGER
signed integers (older slower version of PLS_INTEGER)
LONG
Character data of variable length (A bigger version the VARCHAR2 datatype)
DATE
Valid date range
TIMESTAMP
the number of digits in the
PL/SQL only
PL/SQL only
PL/SQL only
2 Gigabytes
2 Gigabytes - but now deprecated (provided for backward compatibility only).
from January 1, 4712 BC to December 31, 9999AD. 9999AD.
from January 1, 4712 BC to December 31, 9999 AD.
from January 1, 4712 BC to December 31, 9999 AD.
Accepted values of
Accepted values of
integers: INTEGER INT SMALLINT simple_integer (10g) BOOLEAN REAL
magnitude range is 2147483647 .. 2147483647
magnitude range is 2147483647 .. 2147483647 2 Gigabytes - but now deprecated (provided for backward compatibility only).
PRECISION FLOAT binary_float (32 bit) binary_double (64 bit)
32760 bytes Note this is smalller than the maximum width of a LONG column from January 1, 4712 BC to December 31,9999 31,9999 AD. (in Oracle7 = 4712 AD)
NATURAL NATURALN POSITIVE POSITIVEN SIGNTYPE
(fractional_seconds_p recision)
fractional part of the SECOND datetime field.
TIMESTAMP (fractional_seconds_p recision) WITH {LOCAL} TIMEZONE
As above with time zone displacement value
INTERVAL YEAR (year_precision) TO MONTH
Time in years and months, where year_precision is the number of digits in the YEAR datetime field. Time in days, hours, minutes, and seconds.
INTERVAL DAY day_precision is the maximum (day_precision) TO number of digits in 'DAY' SECOND (fractional_seconds_p fractional_seconds_precisioni recision) s the max number of fractional digits in the SECOND field. Raw binary data of length size bytes. RAW(size) You must specify size for a RAW value.
fractional_seconds_p recision are 0 to 9. (default = 6)
fractional_seconds_ precision are 0 to 9. (default = 6)
-
Accepted values of fractional_seconds_p recision are 0 to 9. (default = 6)
Accepted values of fractional_seconds_ precision are 0 to 9. (default = 6)
-
Accepted values are 0 to 9. (default = 2)
Accepted values are 0 to 9. (default = 2)
-
day_precision may day_precision may be 0 to 9. (default = be 0 to 9. (default = 2) 2) fractional_seconds_p fractional_seconds_ recisionmay be 0 to precisionmay be 0 9. (default = 6) to 9. (default = 6)
Maximum size is2000 is2000 bytes
Maximum size is 2000 bytes
Maximum size is 2000 bytes
LONG RAW
Raw binary data of variable length. (not intrepreted by PL/SQL)
2 Gigabytes.
2 Gigabytes - but now deprecated (provided for backward compatibility only)
2 Gigabytes - but now deprecated (provided for backward compatibility only)
ROWID
Hexadecimal string representing the unique address of a row in its table. (primarily for values returned by the ROWID pseudocolumn.)
10 bytes
10 bytes
10 bytes
32767 bytes 32760 bytes Note this is smalller than the maximum width of a LONG RAW column Hexadecimal string representing the unique address of a row in its table. (primarily for values returned by the ROWID pseudocolumn.)
UROWID
Hex string representing the logical address of a row of an index-organized table
MLSLABEL
Binary format of an operating system label.This datatype is used with Trusted Oracle7.
The maximum size and default is 4000 bytes
The maximum size and default is 4000 bytes
CLOB
Character Large Object
4Gigabytes
8 TB
NCLOB
National Character Large Object
4Gigabytes
8 TB
BLOB
Binary Large Object
4Gigabytes
8 TB
BFILE
pointer to binary file on disk
4Gigabytes
8 TB
The maximum size and default is 4000 bytes
universal rowid Hex string representing the logical address of a row of an indexorganized table, either physical, logical, or foreign (non-Oracle)
8 TB to 128 TB (4 Gigabytes - 1) * (database block size) 8 TB to 128 TB (4 Gigabytes - 1) * (database block size) 8 TB to 128 TB (4 Gigabytes - 1) * (database block size) 8 TB to 128 TB (4 Gigabytes - 1) * (database block size) Populate with XML from a CLOB or VARCHAR2.
XMLType
XML data
-
4 Gigabytes
4 Gigabytes or query from another XMLType column.
SeeCHARTOR SeeCHARTOR OWIDand OWIDand the package:DBM package:DBM S_ROWID
