這篇文章: http://blogs.microsoft.com/blog/2016/03/07/announcing-sql-server-on-linux/ 提到了, 即將在 linux 上執行 SQL Server 了.
這個微軟的當家資料庫服務器, 終於跨出了微軟的作業系統了, 有用過的應該都說好, 但到了 linux 下又是另一番風貌, 姑且不論效能如何, 微軟能走出這一步, 真的很值得肯定. 當然相信也能大幅拓展這個在微軟作業系統以外的其他平台市場, 讓 SQL Server 的優點發揮出來.
還蠻期待的.
在資料表中, 使用 GUID做為clustered index時, 很容易因為是隨機產生的值, 而導致資料存放的離散, 可以利用這個函數來改善:
NEWSEQUENTIALID()
該函數是循序地建立 GUID, 也是唯一值, 但可以降低離散的狀況, 進而改善未能有效填滿及離散的問題.
當然, 若是 GUID欄位沒有使用在 cludtered index時, 則比較不需要擔心這個問題.
參考資料:
http://technet.microsoft.com/zh-tw/library/ms189786.aspx
在資料庫操作時, 有時會需要比對資料表的內容是否一致, 或是某些資料的某些欄位是否一樣, 若是使用 SQL 指令來逐行比對, 會是個比較麻煩的操作方式, 使用 CHECKSUM 函數, 可以方便地將待比對的資料(多欄亦可), 計算出 hash index (int)後, 再進行比對, 舉例如下:
兩個 table 資料:
table TBL_TMP1
fno fdata fcreatetime
1 ‘data 1’ ‘2012/1/1’
2 ‘data 2’ ‘2012/2/3’
3 ‘data 3’ ‘2012/2/4’
4 ‘data 4’ ‘2012/4/1’
table TBL_TMP2
fno fdata fcreatetime
1 ‘data 001’ ‘2012/1/1’
2 ‘data 2’ ‘2012/2/12’
3 ‘data 3’ ‘2012/2/4’
4 ‘data 4’ ‘2012/4/1’
若要找出資料不同的資料列, 可以使用如下指令:
SELECT * FROM (SELECT *, CHECKSUM(*) AS CHK FROM TBL_TMP1) a INNER JOIN (SELECT *, CHECKSUM(*) AS CHK FROM TBL_TMP2) b ON a.fno = b.fno AND a.CHK != b.CHK
其中用了 CHECKSUM(*) 是將 table 中的各欄位合併起來計算 hash index, 再將兩個 table 含有 hash index (欄位名 CHK)拿來比對, 列出不同的資料, 結果如下:
1 data 1 2012-01-01 00:00:00.000 803471792 1 data 001 2012-01-01 00:00:00.000 1877235137
2 data 2 2012-02-03 00:00:00.000 266601110 2 data 2 2012-02-12 00:00:00.000 266601103
是很方便的工具函數.
附上建立上面 sample code 的 create table 及 insert data 指令:
CREATE TABLE TBL_TMP2 (fno int, fdata varchar(20), fcreatetime datetime) INSERT INTO TBL_TMP1 values (1, 'data 1', '2012/1/1') INSERT INTO TBL_TMP1 values (2, 'data 2', '2012/2/3') INSERT INTO TBL_TMP1 values (3, 'data 3', '2012/2/4') INSERT INTO TBL_TMP1 values (4, 'data 4', '2012/4/1') INSERT INTO TBL_TMP2 values (1, 'data 001', '2012/1/1') INSERT INTO TBL_TMP2 values (2, 'data 2', '2012/2/12') INSERT INTO TBL_TMP2 values (3, 'data 3', '2012/2/4') INSERT INTO TBL_TMP2 values (4, 'data 4', '2012/4/1')
大家可以試看看, 若是只要比對前兩欄, 則指令如下:
SELECT * FROM (SELECT *, CHECKSUM(fno, fdata) AS CHK FROM TBL_TMP1) a INNER JOIN (SELECT *, CHECKSUM(fno, fdata) AS CHK FROM TBL_TMP2) b ON a.fno = b.fno AND a.CHK != b.CHK
結果如下, 就是只有第一筆資料的前兩欄不同而已:
1 data 1 2012-01-01 00:00:00.000 -1291957785 1 data 001 2012-01-01 00:00:00.000 -1493283680
如同我們想要的結果一樣. 這樣一來可以方便活用這個 CHECKSUM 函數, 十分方便好用!
相關資料:
http://msdn.microsoft.com/en-us/library/ms189788.aspx
資料上寫到是 SQL Server 2005 以上版本可用, 不過其實在 SQL Server 2000 也有這個指令:
http://msdn.microsoft.com/en-us/library/aa258245%28v=sql.80%29.aspx
一般在 SQL Server 中, 若要找某 table 中的 identity 最後值(最大值或目前值), 可以使用以下指令:
dbcc checkident('table_name', NORESEED)
可以參考之前的這篇文章: SQL Server的Identity欄位使用/複製/重設 – https://diary.tw/archives/457
不過, 若是 table 很多, 又想一次性的將各 table 中的 identity 欄位最後值找出, 可以利用系統資料表: sys.identity_columns (2005, 2008, 2008R2都有) 來查找, 配合 sys.objects 表, 可以一次將 table, column, 最後值(last_value) 查找出來, 如下:
select b.name, a.name, a.last_value from sys.identity_columns a inner join sys.objects b on a.object_id=b.object_id
這樣可以利用一個指令就將該資料庫中的所有資料表含有 identity 欄位的最後值, 若是只需要使用者自行定義的 table (不要含系統表), 可以多加上 b.type=’U’ 來進行過濾.
參考資料:
http://technet.microsoft.com/zh-tw/library/ms176057.aspx
http://msdn.microsoft.com/en-us/library/ms187334.aspx
SQL Server Replication (複寫), 也就是用來做資料庫內容同步的一種應用架構, 通常用來做備援或查詢主機應用, 剛在 MSDN 上看到一張說明的圖示很棒, 引用過來, 方便想了解 Replication 技術的朋友參考:
圖片引用自: http://msdn.microsoft.com/en-us/library/aa179423(SQL.80).aspx
其中很重要的觀念在於 snapshot 是做整個資料的複寫, 而 log reader 則是把 transcation log 讀出來存入 distribution database , 再對 subscriber 寫入, 而存在 distribution database 中的 transaction log 用來做資料同步後的持續異動記錄, 以繼續同步 subscriber 中的資料. 由這張圖上看就清楚很多了.
一般來說, 俱有 identity 屬性欄位的 table 的目前值可以很容易地利用 max 函數來取得. 不過, 也很有可能 max 該筆資料已被刪除了, 所以利用 max 查找出來的結果不正確.
但又若是要找出下一個 identity 的值呢? 就一定得知道漸增量 (increment) 為何了, 否則是沒有辦法計算出來的.
請先參考這篇文章: SQL Server的Identity欄位使用/複製/重設 https://diary.tw/archives/457 , 這篇文章中也有提到有關取得目前的 identity 值的方式, 是利用 dbcc checkident(‘table_name’, NORESEED) 來取回, 不過取回來的是文字訊息 (text message), 而不是很方便程式化, 若要滿足前面的需求, 包含取回 identity 目前值及下一個, 則有現成的函數可用:
使用方式如下:
select IDENT_CURRENT('table_name')
這樣可以取出該 table_name table 中的 identity 欄位的目前值, 而下一個呢? 可以利用:
select IDENT_CURRENT('table_name') + IDENT_INCR('table_name')
這樣就可以順利取出來, 也就是拿目前值加上漸增值. 而且回傳的結果是一個 resultset 的方式將值傳回, 很方便應用於程式化的需求.
不過無論如何, 也請特別注意, 這種應用需求, 並非是要 developer 將值取出後再塞回去的, 因為 identity 欄位是由系統維護的, 而不是 developer (或說程式) 維護的, 所以這樣取出是可以拿來觀察, 而不是要塞回去用的, 請特別注意一下.
參考資料: http://social.msdn.microsoft.com/forums/zh-TW/240/thread/ba6ff915-99be-42ee-831c-21d7532f3c47/
SQL Server 2005 中, 若想要找出所有有 identity 的欄位及 table , 可以利用系統表, sys.columns 及 sys.objects 來個來查詢.
其中的 sys.columns 的 is_identity 就可以判定該 column 是否是 identity 欄位. 所以在 SQL Server 2005 要判定這些 table 及欄位時, 可以使用以下查詢:
select o.name, c.name from sys.columns c inner join sys.objects o on c.object_id=o.object_id where c.is_identity = 1 and o.type='U'
當然, 應該會有人問, 那 SQL Server 2000 怎麼辦呢? 因為 SQL Server 2000 並沒有像 SQL Server 2005 有 sys.columns , 而 sys.objects 還可以用 sysobjects 取代, 但麻煩的是那個在 sys.columns 裡的 is_identity 欄位, 要怎麼找出有 identity 的欄位呢? 可以利用 syscolumns 中的 status 欄位, 其中的 0x80 就是 identity 屬性, 詳情可以參考這篇: http://msdn.microsoft.com/en-us/library/aa260398(SQL.80).aspx 其中的 status 欄位.
根據這樣的資料, 我們就不難組合出 SQL Server 2000 查找出有 identity 欄位的 table 了, 如下:
select o.name, c.name from syscolumns c inner join sysobjects o on c.id=o.id where c.status & 0x80 = 0x80 and o.type='U'
整理出來的結果就會是 table / column , 很方便大家能快速地將資料表及欄位含有 identity 屬性的部分找出來.
ps. sysobjects 中的 type=’U’ 是指 user table .
繼續閱讀:
syscolumns: http://msdn.microsoft.com/en-us/library/aa260398(SQL.80).aspx
sysobjects: http://msdn.microsoft.com/en-us/library/aa260447(SQL.80).aspx
SQL Server的Identity欄位使用/複製/重設: https://diary.tw/tim/65
有時候主機的硬碟空間不足或是因為要調整硬碟配置, 會動到資料庫的存放位置, 這裡有一篇 kb 在處理這個作業.
內容說明蠻詳細的, 我這裡簡單列出操作的方式:
1. 先找出該 db 的所有檔案, 利用以下指令(其中USERDB是假設的使用者資料庫名稱):
SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id = DB_ID(N'USERDB')
(基本上至少會有兩個, 一個是放 data 的 mdf, 一個是放 log 的 ldf 檔案的所在位置)
2. 接下來將資料庫先離線:
ALTER DATABASE USERDB SET OFFLINE;
(可能會花一些時間)
3. 再來就是將資料庫的實體檔案搬移到指定位置, 這裡假設是 d:\sqldata\
新增一個資料庫, 第一件事要做的就是備份一次資料庫檔案.
偶而朋友發生SQL Server資料庫檔案故障的問題, 第一件事我會問有沒有做過備份, 其實也算是做最壞的打算, 利用 transaction log 配合之前的 database backup 應該可以 restore 大部分的資料, 但有可能連一次備份都沒做過的狀況下, 很有可能會發生資料庫整個救不回來的狀況. 所以安裝完資料庫後, 第一件事一定是要做好備份的規劃及策略.
這裡提供的方式, 是最懶人的方式, 就是利用 script 進行該資料庫的所有使用者資料庫備份, 利用查詢出 sysdatabases 中的非系統資料庫 (dbid > 4) 的備份, 指令如下:
在資料庫中, 常用的一個流水編號通常會使用 identity 欄位來進行設置, 這種編號的好處是一定不會重覆, 而且一定是唯一的, 這對table中的唯一值特性很重要, 通常用來做客戶編號, 訂單編號等功能, 以下介紹關於此種欄位常用方式及相關技術.
後面的範例表皆以此表為建立:
CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))