萬盛學電腦網

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

SQL Server 開發實戰技巧集錦 (三):排序、加密、中繼資料、分頁、跨伺服器

1. 中文筆畫排序

SELECT * FROM TableName 
ORDER BY CustomerName COLLATE Chinese_PRC_Stroke_CI_AS;  -- 少到多

測試:

IF OBJECT_ID('a') IS NOT NULL DROP TABLE a;
CREATE TABLE a (ID INT, Name VARCHAR(6));
INSERT a VALUES (1,N'張三'),(2,N'李四'),(3,N'王五'),(4,N'趙六'),(5,N'孫七');
SELECT * FROM a ORDER BY Name COLLATE Chinese_PRC_Stroke_CI_AS;
/* 結果順序:王五(4)、孫七(4)、張三(7)、李四(7)、趙六(9) -- 筆畫數 */

2. 資料庫加密函數

-- 雜湊加密 (單向)
SELECT PWDENCRYPT(N'密碼');          -- 回傳 VARBINARY
SELECT PWDCOMPARE(N'密碼', 0x0100...); -- 比對,1=相符

-- 可逆加密 (較舊,建議用 ENCRYPTBYPASSPHRASE/ASYMKEY)
SELECT ENCRYPTBYPASSPHRASE(N'key', N'明文');
SELECT CAST(DECRYPTBYPASSPHRASE(N'key', 0x...) AS NVARCHAR(MAX));

3. 動態取得表欄位清單

DECLARE @cols NVARCHAR(MAX) = N'';
SELECT @cols += N',' + QUOTENAME(c.name)
FROM sys.columns c WHERE c.object_id = OBJECT_ID('TableA');
SET @cols = STUFF(@cols, 1, 1, N'');

DECLARE @sql NVARCHAR(MAX) = N'SELECT ' + @cols + N' FROM TableA';
EXEC sp_executesql @sql;

4. 磁碟分區可用空間

EXEC master..xp_fixeddrives;
/* 輸出範例
drive  MB free
C      168
D      130379
E      57714 */

5. 比較兩表資料是否相同

IF (SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM A) 
   = (SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM B)
    PRINT '相等'; ELSE PRINT '不相等';

注意:CHECKSUM 理論上有極低碰撞機率,嚴謹場景請用 EXCEPT / INTERSECT 或 HASHBYTES('SHA2_256', ...)。

6. 記錄搜尋分頁模式

-- 前 N 筆
SELECT TOP (N) * FROM Table;

-- 第 N 到 M 筆 (需唯一鍵 ID)
SELECT TOP (M-N) * FROM Table 
WHERE ID IN (SELECT TOP (M) ID FROM Table ORDER BY ID) 
ORDER BY ID DESC;

-- 最後 N 筆
SELECT TOP (N) * FROM Table ORDER BY ID DESC;

7. 取得所有使用者表

SELECT name FROM sys.tables WHERE type = 'U';

8. 取得表所有欄位 (兩種等價寫法)

SELECT name FROM sys.columns WHERE object_id = OBJECT_ID('TableName');
SELECT name FROM sys.columns WHERE object_id IN (SELECT object_id FROM sys.tables WHERE name = 'TableName');

9. 查詢相關物件 (檢視/SP/函數 參考某表)

SELECT DISTINCT o.name, o.type_desc
FROM sys.objects o
JOIN sys.sql_modules m ON o.object_id = m.object_id
WHERE m.definition LIKE '%TableName%';

10. 列出所有預存程序

SELECT name FROM sys.procedures;

11. 查詢非 sa 建立的資料庫

SELECT dbid, name FROM master..sysdatabases WHERE sid <> 0x01;

12. 查詢表結構 (欄位名、型別、長度、是否可空)

SELECT 
    c.name AS ColumnName,
    t.name AS DataType,
    c.max_length,
    c.precision,
    c.scale,
    c.is_nullable
FROM sys.columns c
JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID('TableName');

13-14. 跨伺服器操作 (同 73463 文章,此處略)

copyright © 萬盛學電腦網 all rights reserved