My Program ::

找出SQL Server中的identity欄位值

程式技術/Database 2011/11/29 14:57

views: 46203 times
一般在 SQL Server 中, 若要找某 table 中的 identity 最後值(最大值或目前值), 可以使用以下指令:

dbcc checkident('table_name', NORESEED)

可以參考之前的這篇文章: SQL Server的Identity欄位使用/複製/重設 - http://diary.tw/tim/65

不過, 若是 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

TAG dbcc, identity, last_value, SQL Server

Trackback 0 : Comment 0

MySQL的on duplicate key update語法

程式技術/Database 2011/10/13 17:08

views: 37511 times
很有意思的一個語法, 不過可要小心使用, 在 mysql 5.0 版起, 可以在 insert 時, 指定若重覆 unique key 時, 則使用 update 語法, 這個有點像是在新增資料時, 若不存在則新增, 存在則 update 的方式.

來個例子:

CREATE TABLE `t3` (
`sid` INT NOT NULL ,
`v1` INT NOT NULL ,
`v2` INT NOT NULL ,
`v3` INT NOT NULL ,
PRIMARY KEY ( `sid` )
) 
insert into t3 values (1,1,1,1)
insert into t3 values (2,2,2,2)
insert into t3 values (2,2,2,2) on duplicate key update v1=v1+1

這時候, 會有一筆, 1,1,1,1 和 2,3,2,2 的資料產生, 如此一來, 可以在重覆 unique key 時, 用來做為更新的條件, 由於 unique key 只會出現一次在對應 table 上, 所以可以用來做為更新的條件值, 而 primary key 也是 unique key, 所以發生在 primary key 時是一樣的狀況.

不過若是新增的資料, 發生多筆 unique key 重覆時(指不同欄位), 該 update 會只更新一筆, 這是在程式上要避免發生的, 以免有資料更新錯誤的問題. 詳情可以參考官方網站資料: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

這種有趣的語法, 其實用的機會不是太多, 不過多學習了解很有意思.

TAG Insert, MySQL, unique, Update

Trackback 0 : Comment 0

FreeBSD上mysql的效能追蹤

程式技術/Database 2011/08/08 16:02

views: 38098 times
最近因為有台主機的 mysql cpu 異常變高許多, 所以要來進行查詢問題所在.

因為 mysql 沒有像 mssql 的 sql profiler 那麼方便的工具, 所以利用 mysql 本身的 log 來進行, 也就是 slow query log 這個功能, 步驟如下:

1. 先將 /etc/my.cnf 中的 [mysqld] 內多加入下面資料:

log-slow-queries = /var/log/slow-query.log #slow query記錄檔的位置
long_query_time = 2 #query執行超過2秒時才記錄

2. 重啟 mysql 服務, 指令如下:

/usr/local/etc/rc.d/mysql-server restart

3. 執行一陣子後, 就可以看看該 log 檔內的 query , 接下來就是針對這些 query 來調整效能

以上是在 FreeBSD 環境下的作法. (其他環境其實也類似)

其實執行時間長不一定是效率不好, 不過若是常常發生的查詢是需要長時間的, 就有改善的必要, 簡單地說, 就是若一個查詢需要 5秒, 但一天跑不到 10次, 那根本不用管他, 不過若一個查詢需要 0.02 秒, 但一天要用到數萬次, 即使從 0.02 改善到 0.015 就會有很明顯的效能改善, 所以要看發生的頻率及所花費執行的時間, 平衡來看.

另外, 若是該 log 沒有產出, 記得權限要給對, 因為是 mysql service account 去執行寫入的動作, 就算沒有任何 log 也會有 mysql 啟動的資訊, 不會沒有任何產出的 log.

參考資料:
http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html
http://blog.lansea-chu.com/index.php/archives/238
http://homeserver.com.tw/mysql/mysql%E7%9A%84%E6%9F%A5%E8%A9%A2%E6%99%82%E9%96%93log-slow-queries/
http://ezkuan.blogspot.com/2010/05/freebsd-mysql.html

TAG freebsd, MySQL, performance, slow query, tuning

Trackback 0 : Comment 0

MySQL的Log

程式技術/Database 2011/05/19 18:19

views: 37153 times
在 MySQL 運行時, 若想要了解執行時期, 對資料庫下達指令的狀況, 可以利用 my.ini 中的參數來達成, 在 [mysqld] 中多加一行 log=路徑/檔名, 再重起服務即可.

這個目的對於在追蹤應用程式執行指令很有幫助, 不過若是繁忙系統下, 很可能會有大量的資料, 而難以閱讀, 建議使用在開發環境, 再執行對應的 application 記錄下來的 log 就會單純許多, 以方便追蹤.

在 MSSQL 中可以利用 SQL Profiler 來達成, 不過 MySQL 無法過濾那麼多資訊, 只能整個記錄下來. 記得這些操作對於系統效能都會有一定的衝擊, 開起來之後, 記得要關掉, 否則將會影響效能.

另外還有一個好用的參數, 就是一樣在 [mysqld] 中, 加一行 log_slow_queries=路徑/檔名, 這個將會讓系統記錄執行時間較長的 query, 也方便做效能調校及追蹤使用, 預設是執行時間超過 10秒的會被記錄下來, 參數是 long_query_time 可以參考:

http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_long_query_time

這兩種方式分別做完整記錄及長時查詢記錄, 對於想要了解資料庫執行時期的問題及效能調校, 都可以提供不少幫忙.

相關閱讀:
http://blog.wabow.com/archives/54

TAG log, log_slow_queries, long_query_time, MySQL, mysqld

Trackback 0 : Comment 0

使用TABLESAMPLE方式取樣

程式技術/Database 2011/04/22 21:30

views: 25338 times
先弄清楚, 這個 TABLESAMPLE 是用來做以"頁(page)"為單位的取樣方式, 和所謂的隨機不同, 不過在超大型資料表上, 若要快速地取出一部分的資料, 可以應用這樣的方式來達成.

支援 MS SQL 2005 以上的版本, 使用方式很單純, 只需要在查詢指令的 table 之後加上 TABLESAMPLE 子句即可, 如下:

TABLESAMPLE [SYSTEM] (sample_number [ PERCENT | ROWS ] )
[ REPEATABLE (repeat_seed) ]

範例如下:
使用 percent百分比:
USE AdventureWorks2008R2 ;
GO
SELECT FirstName, LastName
FROM Person.Person
TABLESAMPLE (10 PERCENT) ;

或是指定筆數:
USE AdventureWorks2008R2 ;
GO
SELECT FirstName, LastName
FROM Person.Person
TABLESAMPLE (100 ROWS) ;

要特別注意的是, 由於是以 page 為單位, 所以取出的資料只是約略的 page 內容物組合而成的, 不過若是對於超大內容的 table 來說, 比較如下兩個指令:

SELECT TOP 100 * FROM TABLE1 ORDER BY NEWID()

SELECT TOP 100 * FROM TABLE1 TABLESAMPLE(1 PERCENT) ORDER BY NEWID()

效率上會有很大的差異(當然是後者會快很多), 不過若是有 WHERE 條件下, 就不適合使用 TABLESAMPLE 了, 因為是無法以指定的條件來取出資料, 但仍可以下達 WHERE 子句, 是指取出的 TABLESAMPLE 內容, 再過濾的資料, 如下:

SELECT * FROM TABLE1 TABLESAMPLE(1 PERCENT) WHERE FIELD1 > 5000

記得是先取出 sample data 後再過濾的行為, 所以極有可能沒有資料發生, 使用上要特別留意.

官方資料: http://technet.microsoft.com/zh-tw/library/ms189108.aspx
繼續閱讀: http://sharedderrick.blogspot.com/2011/04/tablesample.html

TAG SQL, TABLESAMPLE

Trackback 0 : Comment 0

MySQL的Text欄位長度

程式技術/Database 2011/01/13 15:35

views: 54122 times
MySQL 中, text 屬性的欄位, 用來存放文字的資料格式, 共區分為以下 4種, tinytext, text, mediumtext, longtext, 分別的長度大小為 2^8, 2^16, 2^24, 2^32 bytes 這麼大.

簡單地說, 分別就是 256bytes, 64kbytes, 16Mbytes, 4Gbytes, 在 MySQL 中的 text 有大小不同的限制呢, 所以一般若是只開 text 的話, 最大也只能存到 64kbytes 而已.

參考資料:
http://dev.mysql.com/doc/refman/5.5/en/storage-requirements.html
http://plog.longwin.com.tw/programming/2009/10/20/mysql-text-field-type-length-limit-2009

TAG data type, MySQL, size, text

Trackback 0 : Comment 0

SQL Server Replication示意圖

程式技術/Database 2009/10/24 01:42

views: 40514 times
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 中的資料. 由這張圖上看就清楚很多了.

TAG log reader, Replication, snapshot, SQL Server

Trackback 0 : Comment 0

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

程式技術/Database 2009/06/08 15:32

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

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

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

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

TAG identity, IDENT_CURRENT, SQL Server

Trackback 0 : Comment 0

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

程式技術/Database 2009/04/15 22:38

views: 43703 times
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欄位使用/複製/重設: http://diary.tw/tim/65

TAG identity, query, SQL Server, syscolumns

Trackback 0 : Comment 0

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

程式技術/Database 2009/03/27 15:43

views: 36445 times
有時候主機的硬碟空間不足或是因為要調整硬碟配置, 會動到資料庫的存放位置, 這裡有一篇 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\

more..