MS SQL 學習紀錄-3

資料類型

• 下列物件類型必須事先指定資料類型來限制資料範圍，所以算是一種強制型態的資料類型 (不可以事後再指定資料類型)：

  • 區域變數
  • 資料表的資料行
  • 運算式
  • 用在函式傳遞資料的參數

• 資料類型也就是屬性值的領域：它是一組可接受範圍的屬性值之集合，這些屬性值是不可分割的單元值 atomic ，也就是說，不允許再含有另外一個集合

• 資料類型的可接受範圍，要看所佔用記憶體大小來決定

• 允許開發人員發展自訂資料的類型，這部份包含：(CREATE TYPE)

  • 使用者定義資料類型別名 (範例 A. 根據 varchar 資料類型建立別名類型)
  • 使用者定義型別 (範例 B. 建立使用者自訂類型，使用 .Net Framework)
  • 使用者定義資料表類型 (範例 C. 建立使用者自訂資料表類型)，常用在傳遞資料表值參數

• 當運算子結合的兩個運算式有不同的資料類型、定序、有效位數、小數位數或長度時，結果的性質取決於下列各點：

  • 結果的資料類型，取決於輸入運算式的資料類型所套用的資料類型優先順序規則
  • 當結果資料類型為 char、varchar、text、nchar、nvarchar 或 ntext 時，結果的定序會完全由定序優先順序的規則決定
  • 結果的有效位數、小數位數和長度會隨著輸入運算式的有效位數、小數位數和長度而不同

• SQL Server 中的資料類型組織成下列 7 大類別：

  • 精確數值
  • 近似數值
  • 日期和時間
  • 字元字串
  • Unicode 字元字串
  • 二進位字串
  • 其他資料類型

---

精確數值

• 此類型有 bigint，int，smallint，tinyint，bit，decimal，numeric，money，smallmoney

  • 整數類型有 bigint，int，smallint，tinyint

    • tinyint 只支援正數，不支援負數
      • 不同的儲存體固定大小，使用時不需要指定長度
        以變數為例：

      DECLARE @var INT = 100;
      SELECT @var;
      

  • 整數 + 小數的類型有 decimal，numeric，money，smallmoney

    • decimal，numeric 用在一般有整數和小數的數值，兩者性質相同
    • decimal[ (p[ ,s] )]
    • numeric[ (p[ ,s] )]
    • 使用時需要指定長度，決定儲存體的大小
    • 預設有效位數是 18，不含小數
    • 以變數為例：

    DECLARE @var DECIMAL(10, 5) = 12345.12345;
    SELECT @var;
    

  • money，smallmoney 專用在貨幣的數值，且接受貨幣、千分位符號

    • 不同的儲存體固定大小，使用時不需要指定長度
    • 預設小數到第 4 位，超過會 四捨五入
    • 以變數為例：

    DECLARE @var1 MONEY = 12345.12345;
    DECLARE @var2 MONEY = '$12,345.12345';
    SELECT @var1, @var2;
    

  • bit 資料類型的值只有 1 或 0 適合使用在：開 / 關、有效 / 無效、true / false、人的性別欄位

    • 固定儲存體大小，使用時不需要指定長度
    • 1-Byte = 8-bit，所以超過 1-Byte 時，系統自動擴展儲存體大小
    • 任何輸入的非 0 正負數值皆為 1，只有輸入 0 才為 0
    • 以變數為例：

    DECLARE @var1 BIT = 1;
    DECLARE @var2 BIT = 12345.12345;
    DECLARE @var3 BIT = -12345.12345;
    DECLARE @var4 BIT = 0;
    SELECT @var1, @var2, @var3, @var4;
    

---

近似數值

• 此類型有：real，float
• 顧名思義，其數值不會準確，所以用在篩選條件時，不可直接使用等號 (=) 比對數值，建議使用 >= 或 <=
• 若要從 float 或 real 轉換成字元資料時，使用 STR() 比 CAST() 來得有用，因為它是近似數值(浮點數)專屬轉換成字串的轉換函數，且可指定小數位數
• real
  • 固定儲存體大小，使用時不需要指定長度
  • 精確度可到 7
  • 相當於使用 float(24)
• float
  • 非固定儲存體大小，使用時依指定長度決定儲存體的大小
  • 若不指定長度，預設有效位數是 53 (系統最大值)
  • 精確度可到 15
• 建議勿將近似數值用來儲存精確數值，並且做比較的動作，例如以下 T-SQL 迴圈的 Predicate 會無法滿足：

DECLARE @Counter float = 0;
WHILE (@Counter <> 1.0) BEGIN
    SET @Counter += 0.1;
    PRINT @Counter;
END;

注意事項：

若必須使用近似數值，在 Predicate 可改用 >= 或 <= 可避免錯誤發生

---

日期和時間

• 語法中描述資料時，使用單引號前後圍住日期時間的資料值
• datetime，date，time，datetime2，smalldatetime，datetimeoffset

關於 datetime

• datetime 定義了日期和時間，並結合了以 24 小時制為基礎的當日時間和小數秒數
• 固定儲存體大小 8 Bytes，使用時不需要指定長度
• datetime 可直接 + 或 - 的算術運算，例如： datetime 的值 + 1，代表直接加上 1 天的單位。
• datetime 秒的精確度只到毫秒，建議精確度只用到秒就好，不要用到小數點以下，因為 datetime 值會捨入到 .000、.003 或 .007 秒的遞增，如下表所示

declare @dt datetime = '1998/01/31 23:59:59.991'
select @dt

注意事項

若秒的精確度必須用到小數點以下，建議使用 datetime2、time、datetimeoffset

關於 datetime2

• datetime2 小數以下的可達 7 位數 (100 奈秒，10-9秒)
• 非固定儲存體大小，從 3 ~ 8 Bytes 不等
• 有效位數，小數位數：0 至 7 位數，精確度為 100ns，預設有效位數是 7 位數
• 儲存體大小：不到 3 個有效位數為 6 個位元組，3 或4 個有效位數為 7 個位元組，所有其他有效位數均需要 8 個位元組，但第一個位元組會儲存值的有效位數，所以是 4 ~ 9 Bytes 不等
• datetime2 不可直接 + 或 - 的算術運算，必須使用 DATEADD() 函數。
• 若 datetime 改用 datetime2，可使用 datetime2(3) 也不會有四捨五入的問題

以變數為例：

DECLARE @var datetime2 = '2018-06-10 23:59:59.999'
SELECT DATEADD(dd, 1, @var)

關於 datetimeoffset

• datetimeoffset 提供時區支援和時區位移
• 固定儲存體大小 10 Bytes，使用時不需要指定長度
• 時區位移指定 time 或 datetime 值從 UTC 算起的時區位移。 時區位移可表示成 [+|-] hh:mm
• 以變數為例：

DECLARE @var datetimeoffset = '2018-06-10 23:59:59.999 +08:00'
SELECT DATEADD(dd, 1, @var)

下列範例會比較將字串轉換成各種 date 與 time 資料類型的結果

SELECT   
     CAST('2018-05-08 12:35:29.1234567 +08:00' AS time(7)) AS 'time'   
    ,CAST('2018-05-08 12:35:29.1234567 +08:00' AS date) AS 'date'   
    ,CAST('2018-05-08 12:35:29.123' AS smalldatetime) AS   
        'smalldatetime'   
    ,CAST('2018-05-08 12:35:29.123' AS datetime) AS 'datetime'   
    ,CAST('2018-05-08 12:35:29. 1234567 +08:00' AS datetime2(7)) AS   
        'datetime2'  
    ,CAST('2018-05-08 12:35:29.1234567 +08:00' AS datetimeoffset(7)) AS   
        'datetimeoffset';

注意事項：

datetime 和 smalldatetime 來源值只支援小數以下 3 位
描述日期的資料，必須符合 年月日 或 月日年 的日期格式
描述時間的資料，必須符合 時分秒 的時間格式
日期資料的分隔符號可以使用：
	連字號(-)
	斜線(/)
	小數點(.)
	完全不使用分隔符號

---

字元字串

• 語法中描述資料時，使用單引號前後圍住字元字串的資料值

char，varchar

• 用在英文字，最多只能存 8000 個字
• 用在中文字，最多只能存 4000 個字
• 實際給定長度時，是根據 Bytes 計算
• varchar(max) 可存 (2^31) - 1 = 2,147,483,647 的個英文字(2G)，1G 個中文字。
• char
• 固定儲存體大小，使用時依指定長度決定儲存體的大小
• 以變數為例：

DECLARE @var CHAR(20) = 'SQL資料庫窭';
SELECT @var, LEN(@var), DATALENGTH(@var);

• varchar
• 非固定儲存體大小，使用時依指定長度決定儲存體最大的大小，但實際以存入的資料長度為主
• 以變數為例：

DECLARE @var VARCHAR(20) = 'SQL資料庫窭';
SELECT @var, LEN(@var), DATALENGTH(@var);

注意事項：

若要儲存中文字，建議使用 nchar，nvarchar
varchar(max) 已取代 text，max 表示儲存體大小上限是 2^31-1 個位元組 (2 GB)
varchar(n)、varchar(max) 儲存體大小是輸入資料的實際長度再加上 2 位元組
SQL Server 使用的定序，對非 Unicode 字元字串和 Unicode 字元字串來說，預設不區分英文大小寫
使用 LEN() 和 DATALENGTH() 觀察佔用長度

---

Unicode 字元字串

• 語法中描述資料時，使用單引號前後圍住 Unicode 字元字串的資料值

• nchar，nvarchar

• 用在英文字，最多只能存 4000 個字

• 用在中文字，最多只能存 4000 個字

• 實際給定長度時，是根據字元數計算

• nvarchar(max) 可存 1G 個中英文字

• SQL Server 統一字碼字串的資料需要指明「N」開頭的字母

• nchar

  • 固定儲存體大小，使用時依指定長度決定儲存體的大小
  • 以變數為例：

  DECLARE @var NCHAR(20) = N'SQL資料庫窭';
  SELECT @var, LEN(@var), DATALENGTH(@var)
  

• nvarchar

• 非固定儲存體大小，使用時依指定長度決定儲存體最大的大小，但實際以存入的資料長度為主

• 以變數為例：

DECLARE @var NVARCHAR(20) = N'SQL資料庫窭';
SELECT @var, LEN(@var), DATALENGTH(@var)