MS-SQL

出自ProgWiki

參照：『維基百科～Microsoft_SQL_Server』

目錄 1 SQL Server版本 2 相關文件 3 Microsoft SQL Server Express 4 SQL Server 2005 降轉到 SQL Server 2000 5 自訂SQL Server 2005擴充用 6 FAQ 6.1 不同版本的SQL Server共存 6.2 SQL Server被MS-XML卡住裝不起來 6.3 數值轉字串（插入逗號） 6.4 字串轉數值（移除逗號） 6.5 遠端 MS-SQL Server 2005的資料查詢 6.6 資料庫備份 6.7 如何快速清空Table 6.8 如何刪除LOG 6.9 回傳預設DB內的所有Table名稱（含系統Table名稱） 6.10 表格行列轉換 6.11 以C#寫SQL CLR函數 6.12 SQL-Server如何Select到Oracle裡的資料 6.13 無法解析 equal to 作業中 "Chinese_Taiwan_Stroke_CI_AS" 與 "Latin1_General_CI_AI" 之間的定序衝突。 6.14 輸出Table或查詢到檔案 6.15 使用CTE (Common Table Expression)，來改善原本多層式Select的效能 6.16 SQLCMD 6.17 判斷暫存Table是否存在？再做刪除暫存Table 6.18 SQL預存程序中，執行SQL預存程序，並傳回查詢結果給上一層的SQL預存程序；或是使用SQL預存程序動態查詢不同的Table 6.19 SQL Server怎麼查看執行過的T-SQL的Log記錄？

SQL Server版本

• Microsoft SQL Server 2008
• Microsoft SQL Server 2005
• Microsoft SQL Server 2000
• Microsoft SQL Server 7.0
• Microsoft SQL Server 6.5

相關文件

Technet

• SQL Server

• SQL Server 2005 線上叢書 (2006 年 7 月)

• 如何：設定防火牆供 SQL Server 存取
• 安全的多層式部署

其他

• MCTS - MS SQL Server 2005 (070-431)考題
• SQL Server 2008 複寫實作

Microsoft SQL Server Express

主條目：SQL Server Express

SQL Server 2005 降轉到 SQL Server 2000

• SQL Server 2005 降轉到 SQL Server 2000

自訂SQL Server 2005擴充用

• MS-SQL預存程序
• MS-SQL預存函數

FAQ

不同版本的SQL Server共存

• SQL Server 2000或MSDE需安裝Service Pack 3a以上，不然無法共用通訊埠 1434。
• 因此SQL Express與 SQL Server可以共存。

SQL Server被MS-XML卡住裝不起來

• 產品: MSXML 6.0 Parser (KB933579) -- 安裝失敗
• SQL Server 2005 / 2008 的各種版本都有可能受此影響而無法正常安裝，解法是移除此KB933579的更新檔，或是重裝MSXML 6.0相關的更新檔。
• 參照：
  1. SQL Server 2005 安裝程式失敗時已經安裝 MSXML 核心服務 6.0 Service Pack
  2. SQL Server 2008 Express Edition 安裝失敗?!

數值轉字串（插入逗號）

DECLARE @DATA INT
SET @DATA = 123456
 
SELECT CONVERT(NVARCHAR(20),CAST(@DATA AS MONEY),1)   
--會有小數點後2位
--輸出: 123,456.00
 
SELECT SUBSTRING(CONVERT(NVARCHAR(20),CAST(@DATA AS MONEY),1),1,LEN(CONVERT(NVARCHAR(20),CAST(@DATA AS MONEY),1))-3)  
--移除小數點後2位
--輸出: 123,456

字串轉數值（移除逗號）

DECLARE @DATA VARCHAR(32)
SET @DATA = N'123,456'
 
SELECT CASE WHEN @DATA='' THEN 0 ELSE CONVERT(DECIMAL(14,0),REPLACE(@DATA,',','')) END

遠端 MS-SQL Server 2005的資料查詢

• 目標的MS-SQL Server 2005需開啟相關設定

【開始】→【程式集】→【Microsoft SQL Server 2005】→【組態工具】→【SQL Server 介面區組態】

→【功能的介面區組態】→【特定遠端查詢】→【啟用OPENROWSET和OPENDATASOURCE支援】

SELECT *
FROM OPENDATASOURCE('SQLNCLI','Data Source=主機名稱;User id=帳號;Password=密碼').[DB名稱].[dbo].[TABLE名稱]

資料庫備份

備份DB

• 參照 : BACKUP - MSDN Library

USE MyDB;
BACKUP DATABASE MyDB TO DISK='D:\Backup\MyDB\MyDB_20080205.bak';

快速複製DB（以備份檔進行還原到其他的DB, FILE=1代表備份檔中第1個DB）

• 參照 : RESTORE - MSDN Library

RESTORE DATABASE [MyDB2]
 FROM  DISK = N'D:\Backup\MyDB\MyDB_20080205.bak'
 WITH  FILE = 1, 
 MOVE N'MyDB' TO N'E:\DB\MyDB2.mdf',
 MOVE N'MyDB_log' TO N'E:\DB\MyDB2.ldf',
 KEEP_REPLICATION,  NOUNLOAD,  REPLACE,  STATS = 10

相關範例：

• 局域网自动备份删除（使用到連線網路磁碟，將資料庫的備份檔放到其他台電腦上）

如何快速清空Table

• TRUNCATE TABLE - MSDN Library

TRUNCATE TABLE [table_name]

如何刪除LOG

• 刪LOG的語法如下：（如果DB的名稱是 MyDB）

USE MyDB
BACKUP LOG MyDB WITH no_log
DBCC SHRINKFILE (N'MyDB_Log' , 0, TRUNCATEONLY)

• 相關：
  • SQL Server 日志截断

回傳預設DB內的所有Table名稱（含系統Table名稱）

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'

表格行列轉換

• SQL Server 2005之PIVOT/UNPIVOT行列转换
• Using PIVOT and UNPIVOT
• PIVOT 範例；

CREATE TABLE #test(id INT,name VARCHAR(20),quarter NVARCHAR(1),profile INT)
 
INSERT INTO #test VALUES(1,'a','1',1000)
INSERT INTO #test VALUES(1,'a','2',2000)
INSERT INTO #test VALUES(1,'a','3',4000)
INSERT INTO #test VALUES(1,'a','4',5000)
INSERT INTO #test VALUES(2,'b','1',3000)
INSERT INTO #test VALUES(2,'b','2',3500)
INSERT INTO #test VALUES(2,'b','3',4200)
INSERT INTO #test VALUES(2,'b','4',5500)
 
SELECT * FROM #test
 
SELECT id,name,
    [1] AS "第一季",
    [2] AS "第二季",
    [3] AS "第三季",
    [4] AS "第四季"
FROM #test
pivot
(
    SUM(profile)
    FOR quarter in ([1],[2],[3],[4])
)
AS pvt

以C#寫SQL CLR函數

主條目：以C＃寫SQL CLR函數

SQL-Server如何Select到Oracle裡的資料

1. SQL Server 如何設定 Linked Server？
2. 關於LinkDB時，【Oracle Provider for OLE DB】這個提供者，不可使用，不然資料無法正常從Oracle之中Select出來。
3. LinkDB時，需使用【MicroSoft OLE DB Provider for Oracle】這個提供者，（只需設【產品名稱】與【資料來源】跟【Oracle Net管理員】中的【服務命名】一樣，以及在安全性中設連接Oracle的使用者帳號與密碼）

   且查詢前，需要設：

   1. SET ANSI_NULLS ON
   2. SET QUOTED_IDENTIFIER ON
   3. SET ANSI_WARNINGS ON （備註：Debug時，使用。）
4. 部分的Oracle欄位，可能與MS-SQL不相容，需另外在Oracle裡設View，另外作資料型態的轉換

• 相關：

OPENQUERY

無法解析 equal to 作業中 "Chinese_Taiwan_Stroke_CI_AS" 與 "Latin1_General_CI_AI" 之間的定序衝突。

• 在Select子句的後面加上 COLLATE Chinese_Taiwan_Stroke_CI_AS ，做字串定序的轉換。

輸出Table或查詢到檔案

• 先開啟xp_cmdshell的使用權限 (預設是關閉的, 因為這樣會導致SQL可以直接Call執行檔, 易中後門與木馬)

【開始】→【程式集】→【Microsoft SQL Server 2005】→【組態工具】→【SQL Server 介面區組態】
→【功能的介面區組態】→【xp_cmdshell】
→【啟用xp_cmdshell】勾選

• 範例(1) Table輸出到檔案的範例

DECLARE @cmd VARCHAR(MAX);
SELECT @cmd = 'bcp [MyDB].[dbo].[MyTable] out "c:\MyTable.txt" -c -T -t "," -r "\n"';
EXEC master..xp_cmdshell @cmd;

• 範例(2) 查詢輸出到檔案的範例

DECLARE @cmd VARCHAR(MAX);
SELECT @cmd = 'bcp "select * from [MyDB].[dbo].[MyTable]" queryout "c:\MyQuery.txt" -c -T -t "," -r "\n"';
EXEC master..xp_cmdshell @cmd;

• 範例(3) 檔案匯入的範例

BULK INSERT [MyDB].[dbo].[MyTable] FROM 'c:\MyTable.txt' WITH
(
      BATCHSIZE = 1000,
      FIELDTERMINATOR = ',',
      ROWTERMINATOR = '\n',
      TABLOCK
)

• 相關：
  • SQL語句導入導出大全
  • bcp 備份所有資料表

使用CTE (Common Table Expression)，來改善原本多層式Select的效能

• 原理：將多層式Select的最裡層的Select提出來，寫成 CTE的 with as 格式。
• 限制：SQL-Server 2005以上才能夠使用，SQL-Server 2000以前不支援。且CTE不可連續使用，故無法取代較複雜SubQuery的運用。
• 範例：[1]、[2]

SQLCMD

• DOS命令列式的SQL命令執行程式。
• 範例：（假設對127.0.0.1的SQL-Server，帳號sa，密碼1234，下"select 1+2"）

SQLCMD -S 127.0.0.1 -U sa -P 1234 -Q "select 1+2"

• 其他的SQLCMD用法參照

SQLCMD /?

• 在BAT檔裡使用SQLCMD去備份SQLExpress的DB（SqlCmd的路徑隨SQL Server的版本而異）

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SqlCmd" -S localhost\SQLExpress -U sa -P 密碼 -Q "BACKUP DATABASE myDB TO DISK='D:\db_bak\myDB.bak'"

判斷暫存Table是否存在？再做刪除暫存Table

• 例如暫存Table為 #TempTable

IF (OBJECT_ID('tempdb..#TempTable') IS NOT NULL)  DROP TABLE #TempTable;

SQL預存程序中，執行SQL預存程序，並傳回查詢結果給上一層的SQL預存程序；或是使用SQL預存程序動態查詢不同的Table

• SQL 語法中的tableName 可以是變數嗎?
• sp_executesql (Transact-SQL)

SQL Server怎麼查看執行過的T-SQL的Log記錄？

• SQL Server預設無此功能，須安裝第三方工具。
  • Apex SQL log
  • SQL Log Rescue