[SQL Server] Trigger的資料異動紀錄(part2)

有時候我們會在資料表都建立後, 才想到要另外加個欄位(UpdateTime)記錄修改時間. 但欄位建了, 程式卻可能因為種種原因無法配合修改. 所以此時可透過 trigger 來幫助完成這項功能.
不過, 假如在 TableA 中加入了一個 AFTER UPDATE 的 trigger(TableA_UptTrigger) 去修改 TableA 的資料, 很顯然地可能會導致迴圈的產生. 此時, 有以下兩種選擇:

[SQL Server] 利用 Trigger 進行資料異動的備份

在系統開發過程, 我們往往沒事先規劃在一些重要資料修改過程中進行備份. 使得系統上線後又要改程式, 將程式中有進行修改 / 刪除的行為, 加入備份的程式碼 (例如將欲修改的資料寫一份到 log 表格). 但也許在經過工程師來來去去後, 新的工程師又忘了加入備份的程式碼, 導致最後又要重新檢視所有程式進行修改.
為了避免上述的情況不段重演, 所以考慮在不動到程式的情況下進行資料備份, 也就是利用 trigger, 在指定表格修改或刪除的時候將資料備一份到 Log 表格.

[SQL] IP 轉 Number, 與 Number 轉 IP 的 function

分析使用者的 IP 是來自哪個國家/城市, 在觀察網路行為上是很重要的一項數據.
有興趣的人可以到這裡找尋一些 IP 與地理資訊的相關資源.
至於 IP 轉換成數字的公式, 可以在 IP address 的 wiki 找到.

[SQL Server] 查詢資料庫各資料表的權限清單

在做資料庫的管理事項中, 有時會需要列出資料庫中各個表格的權限清單, 以確認資料表的權限沒有被別人亂設定. 所以為了方便進行這樣的查詢作業, 以下參考一些 SQL Server 既有的 Stored procedure, 將其包裝成一個  SQL Script.
參考資源:

[C#] 從 MySQL 轉資料至 MS SQL Server (SqlBulkCopy)

使用套件: MySQL Connector/Net 6.1 (其他套件可以在此 下載)
因為是大量的資料搬移, 所以採用 .Net 2.0 的 SqlBulkCopy .
連線字串的部分可以到這個網站找: http://www.connectionstrings.com/.
以下是我使用的連線字串:

[SQL Server] 建立 SQL Server Express 的定期自動備份

在 SQL Server 的 Express 版本中, 沒有自動備份的功能可使用.
一般備份就分成兩種方式:

1. 透過 Management Studio Express 進行手動備份.
2. 自行撰寫 T-SQL 的 Script, 或是寫程式去呼叫 T-SQL 的備份指令, 進行資料庫備份.

[SQL Server 2005] 在查詢中建立小計的資料列並排序

在SQL的應用中, 常見到要查出數量與小計的問題.
以下提供一個以 SQL Server 2005 的 ROW_NUMBER() 與 RANK() , 在一次的查詢中將資料查出數量與小計的方式.
不過還是建議在程式中進行這些作業, 以避免當資料量大時, 查詢效能或擴充性不好的問題.

[SQL Server]WITH的遞迴應用-Split欄位

在網路上常看到一個問題,就是在一個欄位中存了類似 001, 002, 003 這樣的值.
一般存了這樣的值, 是想要轉成如下的資料表去跟其他的資料表做 JOIN 或是透過 WHERE 去濾資料.

FID	MyField
1	001
1	002
1	003

[SQL Server 2005]遞迴查詢

在資料表中常見到一種 ID, Parent 的用法, 目的在於想使用遞迴的方式建立起樹狀的資料結構.
例如在程式中進行以下的作業:

while(true)
{
//SELECT ID, Parent, Name FROM Table1 WHERE Parent=@ID
}

透過迴圈, 一次次地到資料庫查詢這個 Node 相關的 Parent/Child 資料.

[SQL Server 2005]使用mdf檔附加資料庫(無ldf檔)

假如要將 A 電腦資料庫的 Test.mdf 檔(無 ldf 檔) 附加到 B 電腦的資料庫, 步驟如下:

1. 在 B 電腦的 SQL Server 中新增一個資料庫, 例如: Test.
2. 停止 B 電腦的 SQL Server 服務.
3. 將 A 電腦資料庫的 Test.mdf 檔覆蓋掉 B 電腦 Test 資料庫的 Test.mdf 檔.
4. 啟動 B 電腦的SQL Server服務.
5. 在 B 電腦的 SQL Server Management Studio 中, 開啟一個 master 資料庫的查詢視窗.
6. 設定 Test 資料庫狀態為 EMERGENCY: ALTER DATABASE Test SET EMERGENCY
7. 設定 Test 資料庫模式為"單一使用者": sp_dboption 'Test', 'single user', 'true'
8. 檢查指定資料庫中所有物件的配置、結構和邏輯完整性: DBCC CHECKDB (Test, REPAIR_ALLOW_DATA_LOSS)
9. 還原 Test 資料庫模式: sp_dboption 'Test', 'single user', 'true'
10. 設定 Test 資料庫狀態為 ONLINE: ALTER DATABASE Test SET ONLINE

因為沒有 ldf 檔, 所以可能會有部分交易的資料遺失.

[SQLServer2000]查出資料庫各表格的資訊

SELECT
　sysusers.name + N'.' + sysobjects.name as ObjectName,
　sysindexes.name as IndexName,
　sysindexes.rows, --資料列
　case indid when 1 then 1 else 0 end as IsClusteredIndex,
　sysindexes.indid, --索引的識別碼
　sysobjects.name, --物件名稱
　sysusers.name --使用者名稱或群組名稱在資料庫中是唯一的
FROM
　sysusers, sysobjects, sysindexes
WHERE
　sysusers.uid = sysobjects.uid
　and sysindexes.id = sysobjects.id
　and sysobjects.name not like '#%'
　and OBJECTPROPERTY(sysobjects.id, N'IsMSShipped') <> 1
　and OBJECTPROPERTY(sysobjects.id, N'IsSystemTable') = 0
ORDER BY
　ObjectName, IsClusteredIndex DESC,
　indexproperty(sysindexes.id, sysindexes.name, N'IsStatistics'),
　IndexName