UPDATE 搭配 CASE WHEN 取代 效能不彰的 CURSOR

我們知道使用SELECT 搭配 CASE WHEN 可以用來簡單的執行邏輯判斷。例如以下的條件

假設有一個員工資料表，裡面有四筆資料

CREATE TABLE #Emp(
EmpID CHAR(4),
EmpName NVARCHAR(5),
EMPAGE INT,
SALARY INT
)


INSERT INTO #Emp VALUES('0001','王大明',36,28000)
INSERT INTO #Emp VALUES('0002','李曉華',23,30000)
INSERT INTO #Emp VALUES('0003','周星星',49,38000)
INSERT INTO #Emp VALUES('0004','劉的華',45,43600)

假設今天要調整薪水，條件如下：
-- 如果年紀在 35~ 45 ,薪水小於30000的員工,加薪 10% ，大於40000要減薪 5%

查詢語法可能會如下這樣執行
SELECT
   *,
   加薪後=CASE
    WHEN (EMPAGE >=35 AND EMPAGE <=45) AND SALARY < 30000 THEN SALARY + SALARY * 0.1
    WHEN (EMPAGE >=35 AND EMPAGE <=45) AND SALARY > 40000 THEN SALARY - SALARY * 0.05
    ELSE
    SALARY
    END

FROM #Emp

結果如下：

如果要實際更新資料表的方法很多種，但使用CASE WHEN應該是滿不錯的選擇

UPDATE #Emp
SET SALARY=
CASE
WHEN (EMPAGE >=35 AND EMPAGE <=45) AND SALARY < 30000 THEN SALARY + SALARY * 0.1
WHEN (EMPAGE >=35 AND EMPAGE <=45) AND SALARY > 40000 THEN SALARY - SALARY * 0.05
ELSE
SALARY
END

如何讓MSSQL資料庫可分別大小寫資料

建立資料庫時，將資料庫定序成可分區分大小寫即可

資料庫定序設定

Chinese_Taiwan_Stroke_CI_AS : 字典順序，不區分大小寫
Chinese_Taiwan_Stroke_CS_AS : 字典順序，區分大小寫。

 

若既有的資料庫不想更改定序內容，可透過下面指令處理

 

ALTER TABLE [表格名稱]
ALTER COLUMN [欄位名稱] [欄位型態]
COLLATE Chinese_Taiwan_Stroke_CS_AS

如何抓取資料庫中含有難字的資料列

利用char函數的特性，可以透過如下語法取得難字的資料列

Select * From [TableName] where convert(char,欄位名稱) like '%?%'

透過指令顯示資料表、欄位、備註的資訊

例如透過sysobjects 得知要找尋的資料表id=603149194，之後再利用 Syscolumns 得知資料表欄位名稱資訊，並利用 Syscolumns 的colid 和 sysproperties 的smalid 得知欄位註解，因此很容易透過join就可以把資料表欄位資訊看的一清二楚。

SELECT * FROM sysobjects where id=603149194

select * from Syscolumns WHERE ID=603149194

SELECT * FROM sysproperties WHERE ID=603149194

查詢某個字串包含在哪些預存程序當中?

假如您要查詢哪些預存程序原始碼包含某個字串，譬如"ld_bal_"，您可以
使用如下的查詢方式查詢


USE tableName
select
O.id,
O.name as ObjectName,
c.text
from dbo.syscomments C left join dbo.sysobjects O
on C.id=O.id
where O.xtype='P' and C.text like '%ld_bal_%'


想知道為什麼嗎? 要單獨把 syscomments 和 sysobjects 資料表撈出來看就知道啦!
另外提供xtype的定義供您參考：

MSSQL　2008　R2
AF = 彙總函式 (CLR)
C = CHECK 條件約束
D = 預設值或 DEFAULT 條件約束
F = FOREIGN KEY 條件約束
L = 記錄
FN = 純量函數
FS = 組件 (CLR) 純量函數
FT = 組件 (CLR) 資料表值函式
IF = 內嵌資料表函數
IT = 內部資料表
P = 預存程序
PC = 組件 (CLR) 預存程序
PK = PRIMARY KEY 條件約束 (類型是 K)
RF = 複寫篩選預存程序
S = 系統資料表
SN = 同義字
SQ = 服務佇列
TA = 組件 (CLR) DML 觸發程序
TF = 資料表函數
TR = SQL DML 觸發程序
TT = 資料表類型
U = 使用者資料表
UQ = UNIQUE 條件約束 (類型是 K)
V = 檢視
X = 擴充預存程序



最後再提供一個可以顯示資料表欄位資訊的系統資料表　
select * from Syscolumns

如何跨SQL SERVER伺服器存取

假設有兩台SQL SERVER ， 分別是 S1 和 S2。
今天如果在S1機器上，要存取另外一台S2的member資料庫 中的  infor 資料表時，
只要在S1這台機器上，利用執行預存程序 sp_addlinkedserver來註冊S2的相關資料
即可。

範例：


EXEC sp_addlinkedserver
@server = 'SQL_1',  -- 可自訂
@srvproduct='',
@provider = 'MSDASQL', 
@provstr = 'DRIVER={SQL Server};SERVER=IP ;UID=帳號;PWD=密碼;'


一旦註冊好之後，就可以在S1這台機器去抓取S2上面的資料

SELECT  *  FROM [SQL_1].[member].[infor]



如果要刪除註冊資訊，利用下面的預存程序即可

EXEC sp_dropserver 'SQL_1'

資料庫連接字串的Integrated Security是什麼?

撰寫資料庫連接字串時，其中一個參數是Integrated Security。當您設定成 Integrated Security=True 時，表示使用此連結字串中的帳號密碼來驗證。如果是設定Integrated Security=false 時， SQL Server會抓取目前要求資料連結的來源端所使用的帳號來驗證，就算連接字串有額外設定帳號和密碼，也沒作用。因此如果您撰寫.NET網頁應用程式，真正要求資料庫驗證的帳號應是IIS中處理asp.net的帳號，如果SQL Server沒有將此帳號加入，則認證就會失敗。

如何抓取SQL SERVER的資料庫和資料表、資料表欄位的相關資訊

當您想抓取SQL SERVER資料庫和資料表的相關資訊，下面有幾個好用的方法可滿足您的需求，我想當您想撰寫類似管理SQL SERVER資料庫系統時，應該會有幫助。

Exec [TableName].dbo.sp_procedures_rowset;2 NULL

SELECT * FROM syscolumns where id=object_id('Table Name')

exec sp_help

exec sp_help [TableName]

SELECT * FROM INFORMATION_SCHEMA.TABLES

SELECT * FROM Information_Schema.Columns

EXEC dbo.sp_databases

EXEC dbo.sp_tables

安裝SQL SERVER出現效能監視器計數器需求 (錯誤)

若您是安裝在XP系統，請修改登入檔
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib]

檢查 Last Counter 和 LastHelp的值必須要大於

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib\009] 和 [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib\004]中的Last Counter 和Last Help的最大值

將SQL Server的bak備份擋還原到另一台SQL Server中

假設serverA備份了一個資料庫擋案my.bak，若要將此my.bak由另外一台serverB還原，會發生

如下的錯誤訊息:
System.Data.SqlClient.SqlError: 備份組包含現有的 'XXXX' 資料庫以外的資料庫備份。 (Microsoft.SqlServer.Express.Smo)

可以透過下面指令解決

步驟一：

restore DATABASE Test01 --假設要還原到serverB的Test01資料庫中（Test01資料庫已存在serverB上）

from disk='C:\my.bak' --指定備份檔案路徑

with

move 'Test01' to 'C:\Program Files\Microsoft SQL Server\myData\2005\Test01.mdf',

move 'Test01_Log' to 'C:\Program Files\Microsoft SQL Server\myData\2005\Test01_Log.ldf'

,NoRecovery

,Replace

go

執行後，出現下面訊息表示順利完成第一步驟

已處理資料庫 'Test01' 的 1000 頁，檔案 1 上的檔案 'my'。

已處理資料庫 'Test01' 的 2 頁，檔案 1 上的檔案 'my_log'。

RESTORE DATABASE 已於 0.757 秒內成功處理了 1002 頁 (10.833 MB/sec)。

步驟二：

再次使用傳統的還原步驟即可。

如何查詢資料表中，某群組中的最後一筆(或最大值)資料

EX:北風資料庫訂單資料表中，如果要找每個客戶最後一筆訂單，可利用自己JOIN自己的方式來辦到

SELECT order1.* FROM Orders order1
INNER JOIN
(SELECT CustomerID, MAX(OrderDate) AS OrderDate FROM Orders GROUP BY CustomerID) order2
ON order1.CustomerID = order2.CustomerID AND order1.OrderDate = order2.OrderDate


其中的重點如下:
1 自己JOIN 自己
2 運用MAX撈出最大值
3 使用GROUP BY 篩選

比EXECUTE更強大的SP_EXECUTESQL

以往在資料庫撰寫T-SQL時,如果遇到需要動態產生SQL 語句的時候,都習慣使用字串去湊出完整的SQL語句,並且透過EXECUTE來執行動態拼湊出來的SQL如下:

DECLARE @qryString NVARCHAR(1000)
DECLARE @單位 NVARCHAR(4)
SET @單位='行政管理'
SET @qryString ='SELECT COUNT(*) FROM 員工 WHERE 單位=' + @單位
EXECUTE @qryString

上面可以傳回行政管理單位的總人數.

但是當您除了動態湊SQL語句外，還需要有回傳值的時候，您可能就需要使用更強大,效能更好的SP_EXECUTESQL來實作出您的需求.如下:

DECLARE @qryString NVARCHAR(1000)
DECLARE @rtnCount INT

SET @qryString='SELECT @count=COUNT(*) FROM 員工 WHERE 單位=@單位'
EXEC SP_EXECUTESQL @qryString,N'@count int OUTPUT,@單位 CHAR(4)',@rtnCount OUTPUT,@單位='行政管理'
PRINT @rtnCount

SP_EXECUTESQL的第一個參數存放SQL的語法字串,第二個參數用來宣告語法字串中的所有變數名稱和型態,
如果有多個參數,就用逗號分隔即可,第三個參數用來指定參數的值.另外要注意的是第二個參數需要是N開頭的型態字串(EX:NVARCHAR),所以要記得加上N，否則會出現過程需要參數'@statement' 為'ntext/nchar/nvarchar' 類型"的錯誤訊息。