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 '%?%'

T-SQL 的 WHEN語法

以下兩種"狀態名稱"可得到相同結果，但可運用的面向卻不同
SELECT
  狀態=CELL_STATUS,
  狀態名稱1=
  CASE
     WHEN CELL_STATUS='U' THEN '晉塔'
     WHEN CELL_STATUS='T' THEN '暫厝'
  END,
  狀態名稱2=
  CASE CELL_STATUS
     WHEN 'U' THEN '晉塔'
     WHEN 'T' THEN '暫厝'
  END,
所有人聯絡人=OWNER_TYPE_NAME,
姓名=CSTM_NAME,
電話1=TEL_NO1,
電話2=TEL_NO2,
地址1=ZIP+CITY+AREA+ADDRESS,
地址2=ZIP2+CITY2+AREA2+ADDRESS2
FROM #vwREL_OWNER
     WHERE NULL IS NULL AND
      CELL_STATUS IN ('U','T')

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

例如透過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資料庫和資料表的相關資訊，下面有幾個好用的方法可滿足您的需求，我想當您想撰寫類似管理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的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 篩選

T-SQL常用指令

NO1
修改資料表或自訂函數的名稱，可使用內建預存程序sp_rename來處理
EXEC sp_rename 'dbo.會員資料表','dbo.會員'

NO2
傳回最後使用identity型態欄位的值
SELECT scope_identity()

備註：
若您的預存程序是將一筆資料insert到資料表，您可在程序最後加上 RETURN @@IDENTITY ，方可傳回系統最後產生的identity 值。

NO3
判斷某暫存資料表是否存在，如果存在，就直接drop掉
IF(object_id('#暫存資料表名稱') IS NOT NULL)
DROP TABLE #暫存資料表名稱

NO4
修改資料表的擁有者(EX:將oscar權限，改成dbo權限)
EXEC sp_changeobjectowner 'oscar.table','dbo'

NO5

無條件捨去

SELECT FLOOR(2.4) ---> 2

NO5

無條件進位

SELECT CEILING(2.4) ---> 3

比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' 類型"的錯誤訊息。