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) -- 筆畫數 */
-- 雜湊加密 (單向)
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));
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;
EXEC master..xp_fixeddrives;
/* 輸出範例
drive MB free
C 168
D 130379
E 57714 */
IF (SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM A)
= (SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM B)
PRINT '相等'; ELSE PRINT '不相等';
注意:CHECKSUM 理論上有極低碰撞機率,嚴謹場景請用 EXCEPT / INTERSECT 或 HASHBYTES('SHA2_256', ...)。
-- 前 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;
SELECT name FROM sys.tables WHERE type = 'U';
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');
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%';
SELECT name FROM sys.procedures;
SELECT dbid, name FROM master..sysdatabases WHERE sid <> 0x01;
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');