萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 實戰開發技巧精選:ISNULL、sysobjects、分頁、索引與效能

1. ISNULL() - 優雅處理 NULL 值


-- 若 Table_column 為 NULL,顯示 'Test'
SELECT ISNULL(Table_column, 'Test') AS column FROM Table;

-- 進階:COALESCE 多參數 (ANSI 標準)
SELECT COALESCE(col1, col2, col3, '預設值') FROM Table;

2. sysobjects / sys.objects - 掌握資料庫中繼資料

查詢所有使用者資料表:


-- 舊版相容視圖
SELECT name FROM sysobjects WHERE xtype = 'U';

-- 新式目錄視圖 (建議)
SELECT name FROM sys.objects WHERE type = 'U';
-- 或
SELECT name FROM sys.tables;

常見 xtype / type 對照

代碼意義
U / USER_TABLE使用者資料表
P / SQL_STORED_PROCEDURE預存程序
V / VIEW檢視表
PK / PRIMARY_KEY主鍵約束
F / FOREIGN_KEY外鍵約束
C / CHECK_CONSTRAINTCHECK 約束
D / DEFAULT_CONSTRAINTDEFAULT 約束
TR / SQL_TRIGGER觸發器
FN / SQL_SCALAR_FUNCTION純量函數
IF / SQL_INLINE_TABLE_VALUED_FUNCTION內嵌表值函數
TF / SQL_TABLE_VALUED_FUNCTION多陳述式表值函數

3. SSMS 2008+ 防止儲存變更需重建資料表

工具 → 選項 → 設計工具 → 取消勾選 「防止儲存需要資料表重建的變更」。

警告:啟用後透過設計師變更欄位順序、資料類型等會隱性重建表,大量資料表會長時間鎖表,生產環境建議改用 T-SQL ALTER TABLE。

4. nvarchar(MAX) ≠ nvarchar(4000)

  • nvarchar(1~4000):資料列內儲存 (In-row),上限 4000 字元 (8000 Bytes)。
  • nvarchar(MAX):大值類型,資料超過 8000 Bytes 時自動移至 LOB 儲存 (行外資料),上限 2^31-1 字元 (2GB)。
  • 查詢計畫、索引、統計資訊處理方式不同,勿濫用 MAX。

5. SELECT * 的效能陷阱

  • 大量資料表:網路傳輸量大、無法使用覆蓋索引、造成 Key Lookup。
  • COUNT(*) 需掃描全表 (或最窄非叢集索引),並施加 Sch-S 鎖,阻塞 DDL;大表慎用,改用 sys.dm_db_partition_stats 估算。
  • 明確列出所需欄位,利於計畫重用與索引覆蓋。

6. GETDATE() vs SYSDATETIME()

新開發建議使用 DATETIME2 + SYSDATETIME()。

7. NEWID() / NEWSEQUENTIALID() - 唯一識別碼

  • NEWID():隨機 GUID,適合主鍵但導致索引碎片化 (隨機插入)。
  • NEWSEQUENTIALID():遞增 GUID (僅可作 DEFAULT 約束),大幅減少分頁分裂,叢集索引主鍵首選。

8. 額外實用技巧

快速產生測試資料


-- 產生 100 筆測試資料
INSERT INTO dbo.Test (Name, CreateTime)
SELECT TOP 100 
    'User_' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS VARCHAR),
    DATEADD(minute, -ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), GETDATE())
FROM sys.all_columns a CROSS JOIN sys.all_columns b;

動態 SQL 安全執行


DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM ' + QUOTENAME(@tableName) + ' WHERE id = @id';
EXEC sp_executesql @sql, N'@id INT', @id = @paramId;  -- 參數化,防注入、促進計畫重用

總結

累積這些「小技巧」能解決 80% 的日常開發痛點:NULL 處理、中繼資料查詢、SSMS 行為控制、大值類型選擇、避免 SELECT *、高精度時間、GUID 主鍵策略。將其內化為編碼習慣,程式碼品質與效能將顯著提升。

copyright © 萬盛學電腦網 all rights reserved
函數精度回傳型別範例
GETDATE()3.33 msDATETIME2024-01-15 14:30:45.123
SYSDATETIME()100 nsDATETIME2(7)2024-01-15 14:30:45.1234567
SYSUTCDATETIME()100 nsDATETIME2(7)UTC 時間