Jaidee Hosting Blog

ประเภทและชนิดข้อมูลใน MSSQL Server

ประเภทของข้อมูลใน SQL Server แบ่งออกเป็นกลุ่มดังนี้

1.Exact numerics แบ่งเป็น

-int, bigint, smallint, and tinyint  เก็บข้อมูลเลขจำนวนเต็ม

ชนิด	ค่าที่จัดเก็บ	ขนาดข้อมูล
bigint	-2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)	8 Bytes
int	-2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)	4 Bytes
smallint	-2^15 (-32,768) to 2^15-1 (32,767)	2 Bytes
tinyint	0 to 255	1 Bytes

-bit

ชนิด	ค่าที่จัดเก็บ	ขนาดข้อมูล
bit	0 to 1	1-2 Bytes

-decimal and numeric เป็นประเภทข้อมูลที่มีการกำหนดจำนวนหลักตัวเลขและตำแหน่งของทศนิยมไว้อย่างชัดเจน ประกอบด้วย

Decimal[(p[,s])]

numeric[(p[,s])]

p(Precision) คือจำนวนหลักของค่าตัวเลขทั้งหมด ทั้งค่าที่เป็นเลขจำนวนเต็มและเลขทศนิยม หากไม่ได้

ระบุค่าการใช้งานจะเป็น Default ซึ่ง p เป็น 18 และ s เป็น 0

S(Scale)  คือ จำนวนหลักของเลขทศนิยม มีค่าอยู่ระหว่าง 0<=s<=p โดย Default มีค่าเป็น 0

จำนวนหลัก	ขนาดข้อมูล
1 – 9	5 bytes
10-19	9 bytes
20-28	13 bytes
29-38	17 bytes

- money จัดเก็บประเภทข้อมูลทางด้านการเงิน

ชนิด	ค่าที่จัดเก็บ	ขนาดข้อมูล
money	-922,337,203,685,477.5808 to 922,337,203,685,477.5807	8 bytes
smallmoney	- 214,748.3648 to 214,748.3647	4 bytes

2. Approximate Numerics ประเภทข้อมูลที่จัดเก็บค่าตัวเลขจำนวนเต็มและค่าทศนิยม

ชนิด	ค่าที่จัดเก็บ	ขนาดข้อมูล
float	- 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308	ขึ้นอยู่กับค่า n
real	- 3.40E + 38 to -1.18E – 38, 0 and 1.18E – 38 to 3.40E + 38	4 Bytes

3. Date and Time ข้อมูลประเภทวันที่และเวลา

ชนิด	ค่าที่จัดเก็บ	ขนาดข้อมูล
date	Date:January 1, 1 A.D. through December 31, 9999 A.D.	3 bytes
time	Time:00:00:00.0000000 through 23:59:59.9999999	5 bytes
datetime	Date:January 1, 1753, through December 31, 9999 Time:00:00:00 through 23:59:59.997	8 bytes
datetime2	Date:January 1,1 AD through December 31, 9999 AD Time:00:00:00 through 23:59:59.9999999	6-8 bytes
smalldatetime	Date:January 1, 1900, through June 6, 2079 Time:00:00:00 through 23:59:59	4 bytes
datetimeoffset	Date:January 1,1 A.D. through December 31, 9999 A.D. Time:00:00:00 through 23:59:59.9999999 Time zone offset :-14:00 through +14:00	10 bytes

หมายเหตุ: date, time, datetime2, datetimeoffset มีตั้งแต่ SQL Server 2008 ขึ้นไป

4. Character Strings

ชนิด	ค่าที่จัดเก็บ	ขนาดข้อมูล
char	1 ถึง 8,000 ตัวอักษร	n bytes
varchar	1 ถึง 8,000 ตัวอักษร	n+2 bytes
text	1 ถึง 2,147,483,647 ตัวอักษร	n bytes

5. Unicode Character Strings

ชนิด	ค่าที่จัดเก็บ	ขนาดข้อมูล
nchar	1 ถึง 4,000 ตัวอักษร	n*2 bytes
nvarchar	1 ถึง 4,000 ตัวอักษร	n*2 bytes
ntext	1 ถึง 1,073,741,823 ตัวอักษร	n*2 bytes

6. Binary Strings

ชนิด	ค่าที่จัดเก็บ	ขนาดข้อมูล
binary	1 ถึง 8,000 bytes	n bytes
varbinary	1 ถึง 8,000 bytes	n bytes
image	1 ถึง 2,147,483,647 bytes	n bytes

7.ชนิดข้อมูลอื่นๆ

• hierarchyid: เพื่อจัดเก็บรหัสตัวแปรของ node ตามรูปแบบ Hierarchy node (SQL Server 2008)

• sql_variant: เป็นข้อมูลที่สามารถจัดเก็บประเภทของข้อมูลที่แตกต่างกันในแต่ละ row ได้ ยกเว้นข้อมูลประเภท text, ntext และ timestamp

• timestamp:เป็นข้อมูลที่ข้อมูลที่จัดเก็บถูกสร้างและแก้ไขด้วยระบบอัตโนมัติ ข้อมูลจะมีขนาด 8 ไบต์ เป็นตัวเลขไบนารี่ที่มีค่าไม่ซ้ำกันในฐานข้อมูล ใช้ในการทำ row versioning

• uniqueidentifier: เก็บข้อมูลชุดตัวเลขฐานสิบหกมีขนาด 16 ไบต์เป็นค่า globally unique identifier (GUID)

• xml: ใช้สำหรับเก้บข้อมูลที่เป็น XML โดยเก็บข้อมูลได้สูงสุดถึง 2 GB (SQL Server 2005 ขึ้นไป)

• cursor: เก็บข้อมูลเอาท์พุตพารามิเตอร์ที่อ้างอิงไปยัง cursor

• table: เก็บข้อมูลผลการทำงานของโพรเซสล่าสุด

สรุปการเปรียบเทียบฐานข้อมูล SQL ชนิดต่างๆ

1.คำสั่ง Join

ทุกฐานข้อมูลสามารถใช้ INNER JOINs ได้ แต่ join types ประเภทอื่นๆ ไม่สามารถใช้ได้ทุกฐานข้อมูลแต่ขึ้นอยู่กับชนิดของฐานข้อมูล

yes = สามารถใช้งานได้

Join type/feature	PostgreSQL	DB2	MSSQL	MySQL	Oracle	Informix
Natural joins (only tested: NATURAL LEFT JOIN)	yes			yes	yes
USING-clause	yes			yes	yes
FULL joins1 (tested: SELECT…FULL JOIN…ON…=…)	yes	yes	yes		yes	yes
Explicit CROSS JOIN (cartesian product)	yes	yes	yes	yes	yes	yes

2. การเรียงลำดับข้อมูล

บางฐานข้อมูลจัดเรียงโดยค่าที่เป็น NULLs ถือว่ามีค่ามากกว่า non-NULL values เช่น PostgreSQL, DB2, Oracle และบางฐานข้อมูลจัดเรียงโดยค่าที่เป็น NULLs ถือว่ามีค่าน้อยกว่า

non-NULL values เช่น  MSSQL, MySQL, Informix เป็นต้น

รูปแบบคำสั่ง:

DECLARE cursorname CURSOR FOR
SELECT ... FROM ... WHERE ...
ORDER BY column_name1,column_name2,…

3.การใช้คำสั่ง SELECT

คำสั่ง SELECT ใช้ในการเลือกข้อมูลที่ต้องการ โดยการใช้คำสั่งเพื่อดึงข้อมูลที่เราต้องการออกมานั้นมีรูปแบบคำสั่งมาตรฐานหลายรูปแบบให้เลือกใช้  ขึ้นอยู่กับการสนับสนุนของฐานข้อมูล ซึ่งบางฐานข้อมูลไม่สนับสนุนรูปแบบมาตรฐานใดๆเลยและมีชุดคำสั่งเป็นของตัวเอง แบ่งเป็น 3 แบบ ได้แก่
Simple limit, Top-n, Limit—with offset

3.1 SELECT Simple limit มีคำสั่งมาตรฐาน 3 รูปแบบ ดังนี้

• ใช้ FETCH FIRST:

รูปแบบคำสั่ง:
SELECT … FROM … WHERE … ORDER BY … FETCH FIRST n ROWS ONLY

• ใช้ Window function: ใช้ฟังก์ชัน ROW_NUMBER() OVER

รูปแบบคำสั่ง:
SELECT * FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
columns
FROM tablename
) AS foo
WHERE rownumber <= n

• ใช้ cursor:
  • DECLARE cursor-name CURSOR FOR …
  • OPEN cursor-name
  • FETCH …
  • CLOSE cursor-name

3.2 SELECT Top-n หรือ quota-queries มีคำสั่งมาตรฐาน 2 รูปแบบ ดังนี้

• The fast variant: ใช้ window function ชื่อ RANK() OVER

รูปแบบคำสั่ง:
SELECT * FROM (
SELECT
RANK() OVER (ORDER BY age ASC) AS ranking,
person_id,
person_name,
age
FROM person
) AS foo
WHERE ranking <= 3

• The slow variant:

ในกรณีที่ DBMS ไม่สนับสนุน elementary OLAP features

รูปแบบคำสั่ง:
SELECT * FROM person AS px
WHERE (
SELECT COUNT(*)
FROM person AS py
WHERE py.age < px.age
) < 3

3.3 SELECT Limit—with offset

• ใช้ OFFSET and FETCH FIRST:

รูปแบบคำสั่ง:
SELECT…
FROM …
WHERE …
ORDER BY …
OFFSET skip ROWS
FETCH FIRST n ROWS ONLY

• Using a window function: ใช้ฟังก์ชัน ROW_NUMBER() OVER

รูปแบบคำสั่ง:
SELECT * FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY key ASC) AS rownum,
columns
FROM tablename
) AS foo
WHERE rownum > skip AND rownum <= (n+skip)

• ใช้ cursor:

รูปแบบคำสั่ง:

• • DECLARE cursor-name CURSOR FOR …
  • OPEN cursor-name
  • FETCH RELATIVE number-of-rows-to-skip …
  • CLOSE cursor-name

4. การใช้คำสั่ง INSERT

บางฐานข้อมูลสนับสนุนการใช้คำสั่งเพื่อ INSERT ข้อมูลทีละหลายๆแถวได้ เช่น PostgreSQL, DB2, MySQL

รูปแบบคำสั่ง:
INSERT INTO tablename VALUES (0,'foo') , (1,'bar') , (2,'baz');

มีค่าเท่ากับการใช้คำสั่ง

INSERT INTO tablename VALUES (0,'foo');
INSERT INTO tablename VALUES (1,'bar');
INSERT INTO tablename VALUES (2,'baz');

5. ข้อมูลชนิด Boolean

Boolean สามารถมีค่าดังต่อไปนี้

- True

- False

- UNKNOWN หรือ NULL (ถ้าไม่มีข้อจำกัดว่าห้ามมีค่า Null)

โดย True จะมีค่ามากกว่า False บางฐานข้อมูลมีตัวแปรชนิดนี้ และบางฐานข้อมูลไม่มีตัวแปรชนิดนี้ แต่มีการใช้ตัวแปรอื่นเพื่อเก็บค่าเหมือนกับตัวแปร Boolean เช่นในฐานข้อมูล DB2 ใช้ฟิลด์ข้อมูล CHAR(1) เพื่อเก็บค่า 0 กับ 1

6. ข้อมูลชนิด char

บางฐานข้อมูลมีรูปแบบตามมาตรฐานของข้อมูลชนิดนี้ และบางฐานข้อมูลรายละเอียดบางอย่างที่แตกต่างกันออกไป

7.ข้อมูลชนิด TIMESTAMP

มาตรฐานมี 2 แบบ ได้แก่

1. TIMESTAMP เช่น TIMESTAMP '2003-07-29 13:19:30'

2. TIMESTAMP WITH TIME ZONE เช่น TIMESTAMP '2003-07-29 13:19:30+02:00'
บางฐานข้อมูลสนับสนุนทั้ง 2 แบบ และบางฐานข้อมูลสนับสนุน TIMESTAMP  เพียงอย่างเดียว เช่น PostgreSQL

8. SQL functions

แต่ละฐานข้อมูลมีรูปแบบการเขียนคำสั่งแตกต่างกันไป โดยอาจยึดตามรูปแบบคำสั่งมาตรฐาน หรือมีความแตกต่างออกไป เช่น อาจมีชื่อคำสั่ง และพารามิเตอร์ที่ต่างกัน เช่น ตัวอย่างคำสั่งคืนค่าความยาวของข้อมูล CHARACTER

ตัวอย่างคำสั่ง รูปแบบมาตรฐาน:
CHARACTER_LENGTH(argument)

ตัวอย่างคำสั่ง ฐานข้อมูล Oracle:
LENGTH(TRIM(TRAILING FROM foo))

9. การเชื่อม string

รูปแบบมาตรฐาน: string1 || string2

แต่ละฐานข้อมูลมีรูปแบบคำสั่งและ syntax แตกต่างกันออกไป  ตัวอย่างเช่น MSSQL ใช้เครื่องหมาย + แทนเครื่องหมาย ||

10. Automatic key generation

ตัวแปรที่ใช้ในการระบุ IDENTITY แตกต่างกันในแต่ละฐานข้อมูล เช่น PostgreSQL ไม่สนับสนุนคำสั่งมาตรฐานแต่จะประกาศชนิดของคอลัมน์เป็น SERIAL เพื่อให้ข้อมูล auto-generated

ตัวอย่างคำสั่ง รูปแบบมาตรฐาน:

CREATE TABLE tablename (
tablename_id INTEGER GENERATED ALWAYS AS IDENTITY
...
)

ตัวอย่างคำสั่ง ฐานข้อมูล PostgreSQL:

CREATE TABLE tablename (
tablename_id SERIAL,
...
)

11. TRUNCATE TABLE operations

คำสั่ง TRUNCATE ของแต่ละฐานข้อมูลอาจมีรายละเอียดบางอย่างต่างไปจากรูปแบบมาตรฐาน และบางฐานข้อมูลไม่มีคำสั่ง TRUNCATE เช่น ใน DB2 จะใช้คำสั่ง IMPORT statement แทนโดยขึ้นอยู่กับชนิดระบบปฏิบัติการ

ตัวอย่างคำสั่ง

On unix-like systems:                                                                                                     IMPORT FROM /dev/null OF DEL REPLACE INTO tablename

On Windows:                                                                                                                 IMPORT FROM NUL OF DEL REPLACE INTO tablename

12. Command line operations

มีรูปแบบการเขียน และชื่อคำสั่งที่แตกต่างกันออกไป เช่น ตัวอย่างในการดึงรายชื่อฐานข้อมูล

ตัวอย่างคำสั่ง ฐานข้อมูล MSSQL:
EXEC SP_HELPDB

ตัวอย่างคำสั่ง ฐานข้อมูล MySQL:
SHOW DATABASES