分類: Database

分類
Database

SQL Server取得identity的目前值及下一個

一般來說, 俱有 identity 屬性欄位的 table 的目前值可以很容易地利用 max 函數來取得. 不過, 也很有可能 max 該筆資料已被刪除了, 所以利用 max 查找出來的結果不正確.

但又若是要找出下一個 identity 的值呢? 就一定得知道漸增量 (increment) 為何了, 否則是沒有辦法計算出來的.

請先參考這篇文章: SQL Server的Identity欄位使用/複製/重設 https://diary.tw/archives/457 , 這篇文章中也有提到有關取得目前的 identity 值的方式, 是利用 dbcc checkident(‘table_name’, NORESEED) 來取回, 不過取回來的是文字訊息 (text message), 而不是很方便程式化, 若要滿足前面的需求, 包含取回 identity 目前值及下一個, 則有現成的函數可用:

  1. IDENT_CURRENT: 取出 identity 欄位的目前值
  2. IDENT_INCR: 取出 identity 欄位的漸增值
  3. IDENT_SEED: 取出 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/

分類
Database

SQL Server 在資料庫中找出有 identity 的欄位及table

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

分類
Database

如何移動已存在的SQL Server 2005中的user database

有時候主機的硬碟空間不足或是因為要調整硬碟配置, 會動到資料庫的存放位置, 這裡有一篇 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 2005中的user database”
分類
Database

SQL Injection問題-偵測滲透成功的方法

SQL Injection 也存在很久了, 通常要發生大規模爆發, 一定是有新的弱點或新的工具產出. 最近在找相關資料時, 發現還蠻有趣(應該說蠻恐怖的)一個資料: http://huaidan.org/archives/2287.html

其中的第2點: Time-Based Blind SQL Injection using heavy queries: A practical approach for MS SQL Server, MS Access, Oracle and MySQL databases and Marathon Tool

這個讓我想起來, 在我還在唸書的時候, 有修過一門課, 叫做實驗設計, 其實和這個有一些關係, 在分析變因時, 會用許多測試的手法來反覆驗證該變因或是該因素是否對該實驗結果有效這件事. 也和 QC 品質測試有關係, QC 在做黑箱測試時, 也是所謂的 blind test 的方式, 來找出是否有什麼系統上的缺陷和瑕疵, 其實這些方法, 應用在 SQL Injection 上的測試, 也是一樣的.

我們可以看到該文章後面提到的許多有趣的方法來做這件事. 尤其是一個有趣的技術, 就是直接餵入”費時”查詢來偵測 http response 的時間, 來看看是否成滲透成功, 一般說來, 判定滲透是否成功, 往往要由 http response 的結果來看, 其實是不太容易自動化的, 但若是使用 http resopnse 時間來做這件事, 我們來想想背後的目的, 就是 “自動化” 的這件事.

其實要真的試看看漏洞, 若是需要人來看, 往往能找的量就少, 不過若是能利用 http response time 來偵測判定, 能夠變成自動化的話, 效率就高很多, 再利用多方工具配合下, 其實….

能做的就相當多了….

真的是一個很不錯的偵測滲透方式, 給大家參考.

[2008/12/29 18:11]
繼續閱讀:
http://www.wretch.cc/blog/Domynews/9731368

分類
Database

SQL Server的遺失索引統計

先說好, 這篇必須是 SQL Server 2005 以上的用戶才能用到的, 因為用到的資料是 DMV 的系統 view, 也就是 Dynamic Management Views.

這裡會用到的 DMVs 是用來查詢所謂遺失的索引, 白話一點, 就是應該要建立的索引, 而沒有建立的索引, 稱之為所謂的”遺失的索引”. 資料庫在查詢時, 若是發現有這樣的狀況, 會記錄下來, 在 DMVs 內的這幾個表:

sys.dm_db_missing_index_groups
sys.dm_db_missing_index_group_stats
sys.dm_db_missing_index_details

在這些表內可以利用這篇文章 (揭露隱藏的資料以最佳化應用程式效能)的一個計算方式(當然也可以再調整), 來將影響較為嚴重而又沒有加上索引的 table 找出來, 查詢如下:

SELECT  TOP 10
        [Total Cost]  = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)
        , avg_user_impact
        , TableName = statement
        , [EqualityUsage] = equality_columns
        , [InequalityUsage] = inequality_columns
        , [Include Columns] = included_columns
FROM        sys.dm_db_missing_index_groups g
INNER JOIN    sys.dm_db_missing_index_group_stats s
       ON s.group_handle = g.index_group_handle
INNER JOIN    sys.dm_db_missing_index_details d
       ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC;

這樣表列出來的資料簡單說明一下:
第一個Total Cost為總成本, 該作者是使用了一個計算的方式, 並以此為排序條件, 找出總成本最高的資料, 第二個 avg_user_impact 為對使用者的影響, 後面三個最重要了, [EqualityUsage]是指等於的條件, 例如 newsid=235 這種條件, 而 [InequalityUsage] 就是指不等的使用, 例如: newsid < 235, 而 [Include Columns]是指查詢時的涵蓋欄位, 也就是指 select newsid, newstitle, newsdesc …. from 前面的欄位.

再來談談有關索引欄位的建立, 上述的後三個欄位就都是要件, 基本上, Equality, Inequality 是指 where 使用的比較欄位, 而 Include Columns 是查詢出來的覆蓋欄位, 至於要如何下這個索引, 成本最粗的下法是將 Equality + Include Columns 加入或 Inequality + Include Columns 加入, 這樣就會有查詢較佳的效能, 但會不會是最好的, 也還是得看異動的頻繁度來考量, 而該索引有沒有價值, 也是必須要評估的, 這裡介紹的方法是將沒有建上的索引資料整理出來列表給管理員來參考用的, 可以節省許多追蹤上的時間. 希望對於效能管理上對各位能有所幫助!!

參考資料:
http://msdn.microsoft.com/zh-tw/magazine/cc135978.aspx

分類
Database

當identity到達該欄位上限時…

今天在整理資料庫時, 發現有個 table 的 identity 欄位, 即將到達上限囉. 於是便來著手研究一下會發生什麼問題.

LAB 狀況 Microsoft SQL Server 2005, 某 table 的 identity 欄位為 tinyint, 並進行 insert 測試.

狀況 1, tinyint 為 (0~255), 若於資料已屆 255 時, 再 insert 資料會發生下面錯誤 (exception):

“轉換 IDENTITY 到資料類型 tinyint 時發生算術溢位錯誤。
發生算術溢位。”

狀況 2, 先將前面 < 100 的資料刪除, 再利用 dbcc checkident(‘table_name’, RESEED, 20) 指令執行後, 重置 identity 值 (下一筆會由 21 開始), 再進行 insert, 則可以正常 insert (dbcc checkindent 資料可以看這篇文章: SQL Server的Identity欄位使用/複製/重設 )

狀況 3, 繼續再進行 insert, 直到 99 時, 再繼續 insert 會發生 primary key violation 的錯誤.

由以上狀況可知, 一旦發生 identity 到該欄位上限時, 將會發生無法寫入資料的狀況, 而且是以算術溢位錯誤的方式發生, 一旦造成, 會有極嚴重的問題, 一定得在發生之前規劃好, 以免造成問題!

分類
Database

SQL 2005的HashBytes function (MD5, SHA1)

在 SQL 2005版本中, 已內建了 HashBytes function 用來做 Hash 的功能, 可以參考該 function 的用法: http://msdn.microsoft.com/en-us/library/ms174415.aspx. 不過這個功能在 SQL 2000 的版本內並無內建, 若有需要可以參考 code project 上的 MD5 Hash SQL Server Extended Stored Procedure, 或是使用純 T-SQL 的方式的 MD5 : MD5算法的T-SQL實現(FOR SQL2000) .

使用方式很簡單, 直接呼叫 HashBytes 函數並指定演算法即可, 如下:

select HashBytes('MD5', '1234')

不過傳回的是 varbinary , 若是要取回為字串的型態, 得再用 sys.fn_VarBinToHexStr() 這個 function 來轉換, 如下:

select sys.fn_VarBinToHexStr(HashBytes('MD5', '1234'))

參考文章:
好用的 MS SQL 2005 欄位字串加密應用 ( MD5 與 SHA1 )
SQL Server 数据库中的 MD5 和 SHA1

另外若是要作密碼加密使用的話, 在 SQL 2000 中有內建 PWDCOMPARE, PWDENCRYPT 這些函數可供使用, 可以參考 BlueShop 討論.

分類
Database

利用CTE將分頁資料取出-Stored Procedure篇

之前在 用CTE來取出指定筆數 – SQL2005 這篇介紹了如何利用 CTE 來取出指定的筆數, 這篇文章將進一步實作一支 stored procedure 做為方便分頁的方式.

主要是為了方便取出指定的筆數來實作的, 所以傳入的參數很單純, 就是原本的查詢指令, 將查詢及排序條件分開, 並指定開始的索引值和結束的索引值即可, 建立的程式碼如下:

create proc sp_getRecordByCTE 
@sqlcmd nvarchar(4000),
@ordercmd nvarchar(1000),
@startindex int,
@endindex int
as
declare @mycmd nvarchar(4000)
select @mycmd = '
with myctequery as (
select row_number() over ( ' + @ordercmd + ' ) 
as CTESN, '+replace(@sqlcmd,'select ',' ')+' )
select * from myctequery 
where CTESN between ' + convert(nvarchar,@startindex) + ' and '+convert(nvarchar,@endindex) 
exec sp_executesql @mycmd

其中比較特別的是為了方便輸入起見, 只要將原來查詢指令的非排序部分和排序部分分開即可, 簡單範列如下:

原來的查詢是:

select a.orderid, a.orderdate, b.custname, b.custaddr from orders a inner join cust b on a.ordercustid = b.custid order by a.orderdate desc

利用上面的 stored procedure 做指定分頁的筆數若為 51~100 (每頁50筆的第二頁), 先將指令區分為紅色查詢及藍色排序, 並且如下輸入給 sp_getRecordByCTE:

exec sp_getRecordByCTE
'select a.orderid, a.orderdate, b.custname, b.custaddr from orders a inner join cust b on a.ordercustid = b.custid',
'order by a.orderdate desc',
51,100

便能將指定的分頁第二頁(51~100)這些資料取出, 這樣和使用 ADO 分頁比較, 不僅可以降低 network IO 外, 更能在複雜查詢時提升效能, 減少不必要的資源浪費, 對於網頁分頁使用時有很大的幫助. 不過目前尚有一個問題無法解決, 就是總筆數這個部分, 因為利用 ADO 查詢時, 可以取得總筆數這個資料, 利用這個 stored procedure sp_getRecordByCTE 將無法取得這個資訊, 看是否有沒有什麼好方法來做這個資料的取得及回傳, 若各位有什麼好建議也歡迎給我建議!

希望這個 stored procedure 對各位能有所幫助.

繼續閱讀:

分類
Database

查詢SQL Server中Table是否有Primary Key

資料庫中的 table, 大家都知道 primary key對 table的效能影響很大, 沒有主鍵的 table, 對於一般在程式實作時會有一定的困難, (某些狀況下例外, 例如 log), 尤其是在更新列時, 沒有 primary key時的時候, 效能根本是很糟糕的, 更危險的是可能會有資料本身邏輯上的問題. 今天我們不是來探討 primary key 的重要性, 而是找出資料庫中, 沒有 primary key 的 table.

其實很簡單地可以利用 sysobjects 這個系統表來查詢, 簡單描述如下, 我們利用 sysobjects 對自已對照, 並利用 parent_obj 這個欄位來串接起來自已對應自己的查詢, sysobjects 內有 table, view, primary key (index), 等資訊, 而我們就利用這樣的方式來進行查詢, 整理好的查詢如下:

閱讀全文 “查詢SQL Server中Table是否有Primary Key”
分類
Database

x64下odbc建立問題

今天在處理一個 odbc 建立在 x64 (64bit) server 2003 上的狀況, 狀況十分奇妙, 一般我們在設定 odbc 時, 是直接使用 [系統管理工具] 裡的 odbc 來進行設置, 當我設置好了之後, 有趣的事發生了, 居然透過 odbc 連接 db 的程式有個這樣的 exception:

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

如此一來設置好的 odbc 就形同虛設, 查了半天也查不出原因, 後來在 google 上找到一篇 citrix 的 kb (link) 試了一下, 居然就好了, 可以找得到了, 仔細檢查一下原因, 整理如下:

在 x64 內透過 ui 建立的 odbc 是 64bit 的 odbc, 而原來執行的程式若是 32bit 的話, 是看不到這組 odbc 的, 也就是說 odbc 也是有區分 64bit / 32bit 的. 如何判斷原來 ui 上的 odbc 是 64bit 的呢, 我們看一下畫面截圖:

閱讀全文 “x64下odbc建立問題”