Data Types trong SQL Server

1-- Integer examples
2CREATE TABLE Product (
3    ProductID INT IDENTITY(1,1) PRIMARY KEY,  -- INT cho ID
4    CategoryID SMALLINT,                       -- SMALLINT cho lookup
5    Quantity INT,                              -- INT cho quantity
6    ViewCount BIGINT DEFAULT 0,                -- BIGINT cho counters lớn
7    IsActive BIT DEFAULT 1,                    -- BIT cho boolean
8    Priority TINYINT DEFAULT 0                 -- TINYINT cho 0-255
9);
10
11-- AdventureWorks example
12SELECT 
13    ProductID,          -- INT
14    SafetyStockLevel,   -- SMALLINT
15    ReorderPoint        -- SMALLINT
16FROM Production.Product
17WHERE ProductID = 1;

1-- DECIMAL(p, s) = NUMERIC(p, s)
2-- p = precision (tổng số digits, max 38)
3-- s = scale (số digits sau decimal point)
4
5CREATE TABLE Financial (
6    Amount DECIMAL(10, 2),     -- 12345678.90 (8 digits + 2 decimals)
7    Rate DECIMAL(5, 4),        -- 0.0525 (1 digit + 4 decimals)
8    Percentage DECIMAL(5, 2),  -- 100.00 (3 digits + 2 decimals)
9    TaxRate NUMERIC(4, 3)      -- 0.085 (tax 8.5%)
10);
11
12-- Storage by precision
13-- 1-9 digits: 5 bytes
14-- 10-19 digits: 9 bytes
15-- 20-28 digits: 13 bytes
16-- 29-38 digits: 17 bytes

1-- FLOAT(n) - n là số bits cho mantissa
2-- REAL = FLOAT(24) = 4 bytes
3-- FLOAT = FLOAT(53) = 8 bytes
4
5CREATE TABLE Scientific (
6    Measurement FLOAT,        -- Scientific calculations
7    Temperature REAL,         -- Less precision needed
8    Latitude FLOAT(24),       -- Coordinates
9    Longitude FLOAT(24)
10);
11
12-- ⚠️ WARNING: FLOAT có rounding errors!
13DECLARE @f FLOAT = 0.1 + 0.2;
14SELECT @f;  -- 0.30000000000000004 (không chính xác!)
15
16-- ✅ Dùng DECIMAL cho financial calculations
17DECLARE @d DECIMAL(10, 2) = 0.1 + 0.2;
18SELECT @d;  -- 0.30 (chính xác!)

1-- MONEY vs SMALLMONEY
2-- MONEY: -922 trillion to 922 trillion (8 bytes)
3-- SMALLMONEY: -214,748.3648 to 214,748.3647 (4 bytes)
4
5CREATE TABLE Transaction (
6    Amount MONEY,              -- Large amounts
7    Fee SMALLMONEY             -- Small amounts
8);
9
10-- AdventureWorks uses MONEY
11SELECT 
12    ProductID,
13    StandardCost,    -- MONEY
14    ListPrice        -- MONEY
15FROM Production.Product
16WHERE ProductID = 1;
17
18-- ⚠️ MONEY có precision issues với division
19DECLARE @m1 MONEY = 1.00, @m2 MONEY = 3.00;
20SELECT @m1 / @m2;  -- 0.3333 (chỉ 4 decimal places)
21
22-- ✅ Recommend: DECIMAL(19, 4) cho financial

1-- CHAR(n) - Fixed length (1 to 8000)
2-- VARCHAR(n) - Variable length (1 to 8000)
3-- VARCHAR(MAX) - Up to 2GB
4
5CREATE TABLE Product (
6    SKU CHAR(10),           -- Fixed 10 chars, padded with spaces
7    ProductCode VARCHAR(20), -- Variable up to 20 chars
8    Description VARCHAR(MAX) -- Large text
9);
10
11-- Storage
12-- CHAR(10): Always 10 bytes
13-- VARCHAR(10): 1-10 bytes + 2 bytes overhead
14-- VARCHAR(MAX): 2 bytes + actual length (up to 2GB)
15
16-- ⚠️ CHAR pads with spaces
17DECLARE @c CHAR(10) = 'ABC';
18SELECT LEN(@c), DATALENGTH(@c);  -- LEN=3, DATALENGTH=10

1-- N = National/Unicode
2-- Supports international characters (Vietnamese, Chinese, etc.)
3-- Uses 2 bytes per character (UTF-16)
4
5CREATE TABLE Customer (
6    FirstName NVARCHAR(50),     -- Vietnamese: "Nguyễn"
7    LastName NVARCHAR(50),      -- Japanese: "田中"
8    Email VARCHAR(100),         -- ASCII only
9    Bio NVARCHAR(MAX)           -- Large Unicode text
10);
11
12-- Storage
13-- NCHAR(10): Always 20 bytes (10 × 2)
14-- NVARCHAR(10): 2-20 bytes + 2 bytes overhead
15
16-- Insert Unicode data
17INSERT INTO Customer (FirstName, LastName)
18VALUES (N'Nguyễn', N'Văn A');  -- Prefix N'' for Unicode literal

1-- AdventureWorks dùng NVARCHAR cho names
2SELECT 
3    c.COLUMN_NAME,
4    c.DATA_TYPE,
5    c.CHARACTER_MAXIMUM_LENGTH
6FROM INFORMATION_SCHEMA.COLUMNS c
7WHERE c.TABLE_SCHEMA = 'Person' 
8AND c.TABLE_NAME = 'Person'
9AND c.DATA_TYPE LIKE '%char%';
10
11-- FirstName NVARCHAR(50)
12-- MiddleName NVARCHAR(50)
13-- LastName NVARCHAR(50)

1CREATE TABLE Event (
2    EventID INT IDENTITY(1,1) PRIMARY KEY,
3    EventDate DATE,                    -- Date only
4    StartTime TIME(0),                 -- Time, no fractions
5    CreatedAt DATETIME2(3),            -- Millisecond precision
6    ModifiedAt DATETIME2(7) DEFAULT SYSDATETIME(),  -- Max precision
7    ScheduledTime DATETIMEOFFSET       -- With timezone
8);
9
10-- Insert examples
11INSERT INTO Event (EventDate, StartTime, CreatedAt, ScheduledTime)
12VALUES (
13    '2024-01-15',                      -- DATE
14    '09:30:00',                        -- TIME
15    '2024-01-15T09:30:00.123',         -- DATETIME2
16    '2024-01-15T09:30:00+07:00'        -- DATETIMEOFFSET (Vietnam)
17);

1-- Current date/time
2SELECT 
3    GETDATE() AS DateTime,              -- DATETIME
4    SYSDATETIME() AS DateTime2,         -- DATETIME2(7)
5    GETUTCDATE() AS UTCDateTime,        -- UTC DATETIME
6    SYSDATETIMEOFFSET() AS WithOffset;  -- DATETIMEOFFSET
7
8-- Date parts
9SELECT 
10    YEAR(GETDATE()) AS Year,
11    MONTH(GETDATE()) AS Month,
12    DAY(GETDATE()) AS Day,
13    DATEPART(WEEKDAY, GETDATE()) AS Weekday;
14
15-- Date calculations
16SELECT 
17    DATEADD(DAY, 7, GETDATE()) AS NextWeek,
18    DATEADD(MONTH, -1, GETDATE()) AS LastMonth,
19    DATEDIFF(DAY, '2024-01-01', GETDATE()) AS DaysSince;

1-- AdventureWorks dùng DATETIME (legacy)
2SELECT 
3    SalesOrderID,
4    OrderDate,              -- DATETIME
5    DueDate,                -- DATETIME
6    ShipDate,               -- DATETIME
7    ModifiedDate            -- DATETIME
8FROM Sales.SalesOrderHeader
9WHERE SalesOrderID = 43659;
10
11-- Recommend: DATETIME2 cho new projects

1-- BINARY(n) - Fixed length (1-8000 bytes)
2-- VARBINARY(n) - Variable length (1-8000 bytes)
3-- VARBINARY(MAX) - Up to 2GB
4
5CREATE TABLE Document (
6    DocumentID INT IDENTITY(1,1) PRIMARY KEY,
7    FileName NVARCHAR(255),
8    FileContent VARBINARY(MAX),    -- Store file binary
9    Thumbnail VARBINARY(8000),     -- Small image
10    HashValue BINARY(32)           -- Fixed-size hash
11);
12
13-- Insert binary data
14INSERT INTO Document (FileName, HashValue)
15VALUES (
16    'report.pdf',
17    HASHBYTES('SHA2_256', 'some content')  -- 32-byte hash
18);

1-- Production.Document stores files
2SELECT 
3    DocumentNode,
4    FileName,
5    FileExtension,
6    DATALENGTH(Document) AS FileSize  -- VARBINARY(MAX)
7FROM Production.Document
8WHERE FileName LIKE '%.doc%';

1-- 16-byte globally unique identifier
2CREATE TABLE Session (
3    SessionID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
4    UserID INT,
5    CreatedAt DATETIME2 DEFAULT SYSDATETIME()
6);
7
8-- Generate GUID
9SELECT NEWID();  -- Random GUID
10SELECT NEWSEQUENTIALID();  -- Sequential GUID (better for clustered index)
11
12-- AdventureWorks uses rowguid
13SELECT 
14    ProductID,
15    rowguid  -- UNIQUEIDENTIFIER
16FROM Production.Product
17WHERE ProductID = 1;

1-- Native XML storage and querying
2CREATE TABLE Config (
3    ConfigID INT IDENTITY(1,1) PRIMARY KEY,
4    ConfigData XML
5);
6
7INSERT INTO Config (ConfigData)
8VALUES ('<settings><timeout>30</timeout><retries>3</retries></settings>');
9
10-- Query XML
11SELECT 
12    ConfigID,
13    ConfigData.value('(/settings/timeout)[1]', 'INT') AS Timeout,
14    ConfigData.value('(/settings/retries)[1]', 'INT') AS Retries
15FROM Config;

1-- SQL Server 2016+ supports JSON functions
2CREATE TABLE APILog (
3    LogID INT IDENTITY(1,1) PRIMARY KEY,
4    RequestBody NVARCHAR(MAX),  -- JSON stored as string
5    ResponseBody NVARCHAR(MAX),
6    CreatedAt DATETIME2 DEFAULT SYSDATETIME()
7);
8
9-- Insert JSON
10INSERT INTO APILog (RequestBody)
11VALUES ('{"user_id": 123, "action": "login", "timestamp": "2024-01-15T10:30:00"}');
12
13-- Query JSON
14SELECT 
15    LogID,
16    JSON_VALUE(RequestBody, '$.user_id') AS UserID,
17    JSON_VALUE(RequestBody, '$.action') AS Action
18FROM APILog;
19
20-- Validate JSON
21SELECT ISJSON('{"name": "test"}');  -- Returns 1 if valid

1-- Computed column tự động tính toán
2CREATE TABLE OrderDetail (
3    OrderDetailID INT IDENTITY(1,1) PRIMARY KEY,
4    Quantity INT,
5    UnitPrice DECIMAL(10, 2),
6    Discount DECIMAL(4, 2) DEFAULT 0,
7    
8    -- Computed columns
9    LineTotal AS (Quantity * UnitPrice),                    -- Not stored
10    NetTotal AS (Quantity * UnitPrice * (1 - Discount)) PERSISTED  -- Stored
11);
12
13-- AdventureWorks computed columns
14SELECT 
15    SalesOrderDetailID,
16    OrderQty,
17    UnitPrice,
18    UnitPriceDiscount,
19    LineTotal  -- Computed: OrderQty * UnitPrice * (1 - UnitPriceDiscount)
20FROM Sales.SalesOrderDetail
21WHERE SalesOrderID = 43659;

1-- Implicit conversion (automatic)
2SELECT 1 + '1';  -- INT + VARCHAR = INT (result: 2)
3SELECT 1 + 1.5;  -- INT + DECIMAL = DECIMAL (result: 2.5)
4
5-- Explicit conversion
6SELECT CAST('123' AS INT);           -- CAST syntax
7SELECT CONVERT(INT, '123');          -- CONVERT syntax
8SELECT TRY_CAST('abc' AS INT);       -- Returns NULL instead of error
9SELECT TRY_CONVERT(INT, 'abc');      -- Returns NULL
10
11-- Date conversion
12SELECT CONVERT(VARCHAR, GETDATE(), 103);  -- dd/mm/yyyy
13SELECT CONVERT(VARCHAR, GETDATE(), 120);  -- yyyy-mm-dd hh:mi:ss
14SELECT FORMAT(GETDATE(), 'dd/MM/yyyy');   -- Flexible formatting

1-- ⚠️ Data loss in conversion
2SELECT CAST(123.456 AS INT);          -- Result: 123 (truncated)
3SELECT CAST('2024-13-01' AS DATE);    -- Error! Invalid month
4
5-- ✅ Safe conversion
6SELECT TRY_CAST('2024-13-01' AS DATE);  -- Returns NULL
7
8-- ⚠️ Unicode loss
9DECLARE @nv NVARCHAR(10) = N'Việt Nam';
10SELECT CAST(@nv AS VARCHAR(10));  -- "Vi?t Nam" - lost diacritics!

1## Data Type Selection Guidelines
2 
3### Numeric
4- ✅ INT for most IDs and quantities
5- ✅ DECIMAL(19, 4) for money/financial
6- ❌ Avoid FLOAT for financial calculations
7- ❌ Avoid MONEY type (limited precision)
8 
9### String
10- ✅ NVARCHAR for international text (Vietnamese)
11- ✅ VARCHAR for ASCII-only data (emails, codes)
12- ✅ Use appropriate size limits
13- ❌ Avoid VARCHAR(MAX) unless necessary
14- ❌ Don't use CHAR unless truly fixed-length
15 
16### Date/Time
17- ✅ DATETIME2 for new projects
18- ✅ DATE when time not needed
19- ✅ DATETIMEOFFSET for multi-timezone apps
20- ❌ Avoid legacy DATETIME
21 
22### General
23- ✅ Choose smallest type that fits
24- ✅ Consider NULL handling
25- ✅ Use consistent types for JOINs
26- ✅ Document special types (JSON, XML)