T-SQL(Transact-SQL)是SQL語言的礦展，這份Tutorial 介紹了T-SQL的基本概念，像是 various(變數)、functions(方法)、store procedures(預存程序)、indexes(索引)，和transactions有關的主題。每一個主題會使用簡單易懂的範例去解釋。
Audience(讀者)
本Tutorial是被設計給那些想要學習基礎T-SQL的人。
Prequisites(先決條件)
開始Tutorial之前，需先熟悉DataBase基本概念是首選。請先安裝SQL Server在你的電腦上，這可以幫助你自己執行範例並了解這是怎麼運作。
Intruduction
In 1970's the product called 'SEQUEL', structured English query language, developed by IBM and later SEQUEL was renamed to 'SQL' which stands for Structured Query Language.
In 1986, SQL was approved by ANSI (American national Standards Institute) and in 1987, it was approved by ISO (International Standards Organization).
SQL is a structure query language which is a common database language for all RDBMS products. Different RDBMS product vendors have developed their own database language by extending SQL for their own RDBMS products.
T-SQL stands for Transact Structure Query Language which is a Microsoft product and is an extension of SQL Language.
Data Types
SQL Server 資料型態是指定任何物件的料型態的屬性。每一個Column，Variable 和Expression 在SQL Server都跟資料型態有關。
當我們在創建Tables，這些資料型態就會被使用。你可以為Table Column 基於需求來選擇特定資料型態。
SQL Server提供 七個類別包含其他類別的數據類型做使用:
Exact Numeric Types(精確數字型態)
|
Approximate Numeric Types(近似數字型態)
| Type | From | To |
|---|---|---|
| Float | -1.79E + 308 | 1.79E + 308 |
| Real | -3.40E + 38 | 3.40E + 38 |
Date and Time Types(日期和時間型態)
| Type | From | To |
|---|---|---|
|
datetime(3.33 milliseconds accuracy) |
Jan 1, 1753 | Dec 31, 9999 |
|
smalldatetime(1 minute accuracy) |
Jan 1, 1900 | Jun 6, 2079 |
|
date(1 day accuracy. Introduced in SQL Server 2008) |
Jan 1, 0001 | Dec 31, 9999 |
|
datetimeoffset(100 nanoseconds accuracy. Introduced in SQL Server 2008) |
Jan 1, 0001 | Dec 31, 9999 |
|
datetime2(100 nanoseconds accuracy. Introduced in SQL Server 2008) |
Jan 1, 0001 | Dec 31, 9999 |
|
time(100 nanoseconds accuracy. Introduced in SQL Server 2008) |
00:00:00.0000000 | 23:59:59.9999999 |
Character Strings(字符字串)
| Sr.No | Type & Description |
|---|---|
| 1 |
char Fixed-length non-Unicode character data with a maximum length of 8,000 characters. |
| 2 |
varchar Variable-length non-Unicode data with a maximum of 8,000 characters. |
| 3 |
Varchar (max) Variable-length non-Unicode data with a maximum length of 231 characters (Introduced in SQL Server 2005). |
| 4 |
text Variable-length non-Unicode data with a maximum length of 2,147,483,647 characters |
Unicode Character Strings(Unicode字符字串)
| Sr.No | Type & Description |
|---|---|
| 1 |
nchar Fixed-length Unicode data with a maximum length of 4,000 characters. |
| 2 |
nvarchar Variable-length Unicode data with a maximum length of 4,000 characters. |
| 3 |
Nvarchar (max) Variable-length Unicode data with a maximum length of 230characters (Introduced in SQL Server 2005). |
| 4 |
ntext Variable-length Unicode data with a maximum length of 1,073,741,823 characters. |
Binary Strings(二進位字串)
| Sr.No | Type & Description |
|---|---|
| 1 |
binary Fixed-length binary data with a maximum length of 8,000 bytes. |
| 2 |
varbinary Variable-length binary data with a maximum length of 8,000 bytes. |
| 3 |
varbinary(max) Variable-length binary data with a maximum length of 231 bytes (Introduced in SQL Server 2005). |
| 4 |
image Variable-length binary data with a maximum length of 2,147,483,647 bytes. |
Other Data Types
-
sql_variant − Stores values of various SQL Server-supported data types, except text, ntext, and timestamp.
-
timestamp − Stores a database-wide unique number that gets updated every time a row gets updated.
-
uniqueidentifier − Stores a globally unique identifier (GUID).
-
xml − Stores XML data. You can store XML instances in a column or a variable (Introduced in SQL Server 2005).
-
cursor − A reference to a cursor.
-
table − Stores a result set for later processing.
-
hierarchyid − A variable length, system data type used to represent position in a hierarchy (Introduced in SQL Server 2008).