資料庫

前言資料庫檔案外洩的成本往往不在攻擊當下，而在事後。SQL Server 的透明資料加密（Transparent Data Encryption，TDE）能在資料寫入磁碟時自動加密、讀取時自動解密，對應用程式完全透明，不需改一行程式碼。本文從建立主金鑰到啟用加密逐步實作，並說明實務上需要注意的地方。 TDE運作原理TDE 保護的是磁碟上的靜態資料，也就是 .mdf、.ldf 等資料庫檔案本身。寫入磁碟時加密、從磁碟讀取時解密，整個流程發生在資料庫引擎層，應用程式完全不感知。 金鑰階層如下： 資料庫檔案 資料庫引擎 記憶體 資料庫加密金鑰 憑證 加密 解密 TDE優缺點分析優點 對應用程式透明，不需修改程式碼 保護整個資料庫檔案，包含交易日誌 ...

老闆丟一句「這個報表，越快越好」，你打開資料表一看——好幾千萬筆。這時候用對工具跟用錯工具，差的不是幾分鐘，是幾個小時。 SQL Server 匯出 CSV 最常用的三個工具是 SQLCMD、BCP、SSIS。網路上很多範例其實是把它們的角色搞混的：最典型的就是把 bcp 命令當成 T-SQL 字串丟進 sp_executesql，抄回去一跑就語法錯誤。這篇把每個工具的定位講清楚，範例全部以可實際執行為前提，並附上分批匯出大型資料的正確寫法。 三個工具，三種角色先把定位搞對，後面才不會誤用。 BCP（Bulk Copy Program）是獨立的命令列工具，走大量複製 API，匯出大表速度最快、記憶體佔用低。它不是 T-SQL，是在作業系統的命令提示字元（或透過 xp_cmdshell）執行的 .exe。 SQLCMD 也是命令列工具，主要拿來跑 .sql 指令稿、做自動化排程。它本身不是高效能匯出引擎——真要快速匯出大量資料，多半還是在 SQLCMD 指令稿裡呼叫 BCP。 SSIS（SQL Server Integration Services）是視覺化 ETL 平台，強在「匯...

前言在進行資料庫開發與維護時，經常需要知道資料表、檢視表或預存程序最後的修改時間。這些資訊在追蹤系統變更、問題排除、版本控制時都用得上。本文介紹幾個實用的 SQL 查詢，讓你快速掌握資料庫物件的時間資訊。 查詢資料表與檢視表的修改時間以下是查詢資料表與檢視表修改時間的SQL： 12345678SELECT [name] -- 物件名稱 ,create_date -- 建立時間 ,modify_date -- 修改時間 ,type_desc -- 物件類型FROM sys.all_objects WHERE type_desc = 'USER_TABLE' -- 使用者資料表 OR type_desc = 'VIEW' -- 檢視表ORDER BY modify_date DESC -- 依修改時間排序 查詢說明 sys.all_objects: SQL Server 的目錄檢視，涵蓋使用者物件（sys.objects）...

前言大型 SQL Server 環境裡，資料匯入匯出緩慢、維護作業得人工拖到半夜、備份還原偶爾出包、跨伺服器操作一多就容易漏步驟，這些幾乎是每個 DBA 遲早會遇到的問題。 sqlcmd 是 SQL Server 內建的命令列工具，把這些操作腳本化、掛上排程，能省下大量重複的手動工作。這篇整理我實際用過的幾種場景，並且特別把網路教學裡常見的幾個錯誤參數說明糾正過來——尤其是 -m 和 -k，被講錯的機率高得驚人。 SQLCMD 功能與效能概觀 SQLCMD 資料匯入匯出 批次處理優化 執行SQL指令 效能監控 資料庫備份 壓縮優化 自動化排程 錯誤處理 大型資料庫匯入耗時比較（示意，單位：分鐘） 逐筆 INSERT 批次處理 最小記錄載入 先搞清楚：命令列參數 vs T-SQL 腳本很多人把 s...

前言SQLCMD 是隨 SQL Server 一起安裝的命令列工具，日常用途涵蓋執行維護腳本、匯入匯出資料、串接 Windows 排程器自動跑批次作業。比起 SSMS 的 GUI，它的優勢在於可以寫成批次檔、進版控、在沒有桌面環境的伺服器上執行。 SQLCMD 功能概觀 SQLCMD 資料匯入匯出 執行SQL指令 資料庫備份 自動化排程 SQLCMD 基本語法常用旗標一覽： 1234567891011sqlcmd -S <伺服器名稱> -- 指定 SQL Server 執行個體（可含 \InstanceName 或 ,Port） -U <使用者名稱> -- SQL Server 登入帳號 -P <密碼> -- 對應密碼 -E -- 使用 Windows 整合驗證（省略 -U/-P，優先選此） -d <資料庫名稱> ...

問題情境資料庫裡有個代碼欄位 MaintainClassID，散落在幾十張資料表中。要把值 4851204 全部改成 4851206，逐張查、逐張改——這不只費時，還容易漏掉某張表。 有沒有辦法讓 SQL Server 自己列出所有含這個欄位的資料表，再批量產生 UPDATE 語句？有。 解決方案INFORMATION_SCHEMA.COLUMNS 視圖記錄了目前資料庫所有欄位的中繼資料，查一次就能得到所有包含目標欄位的資料表清單。 步驟一：找出所有包含特定欄位的資料表12345678910111213-- 找到所有有該欄位的資料表SELECT DISTINCT t.TABLE_SCHEMA, -- 資料表的結構描述名稱 t.TABLE_NAME -- 資料表名稱FROM INFORMATION_SCHEMA.COLUMNS c INNER JOIN INFORMATION_SCHEMA.TABLES t ON c.TABLE_NAME = t.TABLE_NAME AND c.TABLE_SCHEMA = t.TA...

為什麼要把大表拆成小表一張上千萬筆的資料表，查詢愈跑愈慢、維護視窗愈拉愈長，這幾乎是每個後端工程師遲早會碰到的場景。把它依某種規則拆成多張較小的表，是其中一種權宜手段：查詢範圍縮小、維護單位變細、歸檔也比較好搬。 SQL Server 的 SELECT INTO 很適合做這件事——它能根據查詢結果直接生出一張新表，省去你先寫一段 CREATE TABLE 再 INSERT 的功夫。但它「省」掉的東西比你以為的多，這篇就把運作原理講清楚，順便把幾個照抄會直接報錯的地雷標出來。 SELECT INTO 到底複製了什麼先把最容易誤會的觀念釘死。執行這一句： 1SELECT * INTO 新資料表 FROM 來源資料表; SQL Server 會做的事是： 建立一張新表 複製來源欄位的「名稱、資料型別、可空性」，以及 IDENTITY 屬性 把符合條件的資料列寫進去 它不會複製的，才是重點：索引、主鍵、外鍵、DEFAULT、CHECK 等任何約束，全部都不跟過來。微軟官方文件對 INTO 子句的描述很直接：「來源表上定義的索引、約束與觸發程序不會轉移到新表，你也無法在 SELECT...

前言身為後端工程師，你是否遇過這樣的情況： 系統突然變得超級慢 網頁一直轉圈圈 收到「已超過連線逾時的設定」錯誤訊息 如果有，很可能就是連線池爆掉了。以下分享一個真實案例，從診斷、緊急處理到根治。 問題診斷當你遇到以下錯誤訊息時，很可能是連線池出問題了： 1System.InvalidOperationException: '已超過連線逾時的設定。在取得集區連線之前超過逾時等待的時間，可能的原因為所有的共用連線已在使用中，並已達共用集區大小的最大值。' 快速診斷方法首先，用以下 SQL 查看目前的連線狀況。sys.sysprocesses 是 SQL Server 2000 相容性視圖，SQL Server 2005 起已標記 deprecated，這裡改用官方建議的 DMV： 123456789101112-- 各資料庫連線數摘要（依登入帳號、狀態分組）SELECT DB_NAME(s.database_id) AS DatabaseName, COUNT(*) AS NumberOfConnections...

SQL Server 的備份檔，這個月又比上個月大了好幾 GB。打開資料庫一看，資料表筆數沒什麼變，那這些空間是哪來的？ 很多人第一反應是「資料庫膨脹了」，然後跑去 DBCC SHRINKDATABASE 想把它壓回來。先停手——SHRINK 是 SQL Server 裡最容易幫倒忙的指令之一，後面會講為什麼。先搞懂備份檔為什麼會長大。 完整備份複製的是資料庫裡「已配置的資料頁」加上一小段交易記錄，不是 mdf 檔的實體大小。一個 100 GB 的 mdf，裡面只有 40 GB 的頁真有資料，備份檔大約就是 40 GB 出頭。所以備份檔變大，意思是已配置的頁變多了——或者，根本不是資料庫的問題，是備份檔本身在累積。順這條線，由最常見往下查。 真因一：你每天往同一個檔案疊備份（最常見）頭號元兇，也最容易被忽略。BACKUP DATABASE 不指定 WITH INIT 時，預設是 NOINIT——把這次備份附加到既有檔案，不是覆蓋。 如果排程固定備份到 D:\Backup\MyDB.bak、每天跑又沒寫 INIT，這個檔案裡其實疊了一份又一份完整備份，當然每天長大。 要確認是不是這...

SQL優化：處理百萬級資料表的結構修改一張上百萬筆的資料表，直接下 ALTER TABLE 加個欄位，結果整張表被鎖住、線上查詢全部排隊等待、應用程式逾時。這是動大表時最常踩的雷。底下記錄一個我在宜蘭下水道專案實際用過的做法：不直接改原表，而是建一張新結構的表、分批把資料搬過去，再交換表名。 先講清楚一件事：這篇前半段的概念示意用 MySQL 語法，後半段的實戰程式碼是 SQL Server（T-SQL）。兩套資料庫在「建空表」和「換表名」這兩步的語法差很多，不能照抄互換，文中會逐一標明。 為什麼不直接 ALTER TABLE當資料表的記錄數量上到百萬、千萬級別，直接執行 ALTER TABLE 很可能撐很久甚至逾時。原因是不少結構變更會走「重建整張表」的路徑：資料庫要複製整張表的內容、期間對表加上 schema 修改鎖，其他查詢和寫入只能等。表越大，鎖的時間越長，線上服務就越可能受影響。 不同資料庫、不同版本對 online DDL 的支援程度不一樣，有些操作（例如某些情況下加欄位）其實能做到不重建表。但一旦碰到需要重建的變更，又不想冒鎖表風險，「自己控制節奏分批搬」就是個可控...