SQL Server

出自ProgWiki
跳至導覽 跳至搜尋
Microsoft SQL Server

作者
研發者 Microsoft
最初正式版
最新穩定版 SQL Server 2022 / 2022年11月16日，6個月前（2022-11-16）
最新測試版 /
作業系統 Microsoft Windows
系统平台
可用語言
類型 關聯式資料庫管理系統
授權協議
網站 https://www.microsoft.com/en-us/sql-server/

SQL Server，參照：『維基百科～Microsoft_SQL_Server』

技術文件

MS的SQL Server使用範例

SQL Server版本

各版本的規格=

Microsoft SQL Server Express

主條目：SQL Server Express

SQL Server 2005 降轉到 SQL Server 2000

自訂SQL Server 2005擴充用

範例資料庫

FAQ

不同版本的SQL Server共存

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

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

數值轉字串（插入逗號）

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
use MyDB;
backup database MyDB to disk='D:\Backup\MyDB\MyDB_20080205.bak';
快速複製DB（以備份檔進行還原到其他的DB, FILE=1代表備份檔中第1個DB）
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 [table_name]

如何刪除LOG

  • 刪LOG的語法如下：（如果DB的名稱是 MyDB）
SQL Server 2005 以前適用
use MyDB;
backup log MyDB with no_log;
DBCC SHRINKFILE (N'MyDB_Log' , 0, TRUNCATEONLY);
SQL Server 2008 以後適用 (需要先備份Log)
use MyDB;
BACKUP LOG DatabaseName TO DISK = N'D:\Backup\MyDB_log.bak';
DBCC SHRINKFILE('MyDD_Log', 1);

無法執行 BACKUP LOG，因為沒有目前的資料庫備份

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

select TABLE_NAME from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE='BASE TABLE'

表格行列轉換

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
)

使用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 Server怎麼查看執行過的T-SQL的Log記錄？

由於已經達到使用者連接最大數目 '2' 而無法連接。

  1. 先把所有會連接 SQL Server的網站與應用程式關閉。
  2. 在服務裡重新啟動 SQL Server (MSSQLSERVER)
  3. 用 SQL Server Management Studio 登入 SQL Server
  4. 在 SQL Server 主機名稱上，滑鼠右鍵內容，【連接】去修改並行連接的最大數目，改為0 （不設限）

暫存資料表已存在或不存在

  1. 暫存資料表不存在，經由EXECUTE sp_executesql 所動態執行，其產生的暫存資料表（例如 #TempTable1）無法傳出，會被隔離掉，所以要改用全域暫存資料表（Global Temporary Tables），或是避免使用EXECUTE sp_executesql
  2. 暫存資料表已存在，盡量避免使用全域暫存資料表（Global Temporary Tables）（例如 ##TempTable1），以免預存程序發生同時間內多次執行
暫存資料表（Temporary Tables）
  • Table名稱前置 # （例如 #TempTable1）
  • 存在於 tempdb 這個database
  • 如果有不同的使用者建立同名的暫存表，則它們會各自擁有獨立的一份暫存表，不會互相干擾。
  • 但是缺點是真的很慢，當資料筆數越多時越明顯。
全域暫存資料表（Global Temporary Tables）
  • Table名稱前置 ## （例如 ##TempTable1）
  • 所有的使用者共用一份同名的暫存表
  • 除非確定同一時間內不會有其它的使用者同名的暫存表，不然應盡量使用亂數決定暫存表的名稱。但所有的SQL操作都必需使用EXECUTE sp_executesql

啟用AWE記憶體在SQL Server 2005/2008/2008R2

設定資料欄位的預設值

ALTER TABLE [dbo].[資料表] ADD  DEFAULT (預設值) FOR [欄位名稱]

資料庫備份到雲端

在事件檢視器的「應用程式伺服器」出現錯誤訊息

應用程式特定 權限設定無法將含有 CLSID {FDC3723D-1588-4BA3-92D4-42C430735D7D} 
與 APPID {83B33982-693D-4824-B42E-7196AE61BB05}
 之 COM 伺服器應用程式的 本機 啟用 權限授與來自位址 LocalHost (使用 LRPC)
 (在應用程式容器 無法使用 SID (無法使用) 中執行) 的使用者 NT SERVICE\SQLSERVERAGENT 
SID (S-1-5-80-344959196-2060754871-2302487193-2804545603-1466107430)。您可以使用元件服務系統管理工具修改此安全性權限。
  1. 使用Windows PowerShell 去執行 → dcomcnfg
  2. 依序展開（主控台根目錄 → 電腦 → 我的電腦 → DCOM設定）
  3. 分別把服務「SQL Server Agent (MSSQLSERVER)」的登入帳號「NT Service\SQLSERVERAGENT」，與服務「SQL Server Integration Services 11.0」的登入帳號「NT Service\MsDtsServer110」這兩個帳號，都先加到DCOM設定的 「Microsoft SQL Server Integration Services 11.0」的 「安全性」 → 「啟動和啟動的權限」的「本機啟動」與「本機啟用」

巢狀的預存程序、觸發程序還有檢視

  • SQL Server對於巢狀的預存程序、觸發程序還有檢視，最大的層數是32層，如果超過的話會發生下列的錯誤訊息：
    Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)

SQL Server 資料型別對應

SQL Server 取得資料表個欄位的定義

修改字串欄位的定序方式

ALTER TABLE [資料表名稱]
ALTER COLUMN [欄位名稱] nvarchar(20)
COLLATE Chinese_Taiwan_Stroke_CS_AS

從Log裡找出是哪個帳號刪掉資料庫

SELECT
	 DatabaseID
	,DatabaseName
	,LoginName
	,HostName
	,ApplicationName
	,StartTime
	,CASE
		WHEN EventClass = 46 THEN 'Database Created'
		WHEN EventClass = 47 THEN 'Database Dropped'
	ELSE 'NONE'
	END AS EventType
FROM ::fn_trace_gettable((SELECT path FROM sys.traces WHERE is_default = 1), default)
	WHERE DatabaseName = '資料庫名稱'
		AND (EventClass = 46 OR EventClass = 47)

ntext、text 和 image 將在未來版本的SQL Server移除

列舉所有資料表的資料筆數與所占空間

相關

列舉所有的索引鍵

SELECT  obj.name AS FK_NAME,
    sch.name AS [schema_name],
    tab1.name AS [table],
    col1.name AS [column],
    tab2.name AS [referenced_table],
    col2.name AS [referenced_column]
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
    ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
    ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
    ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
    ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
    ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
    ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id

資安

除錯

群組應用（小計與總計）

群組統計
SELECT [欄位1, 欄位2, ...], [SUM(數值欄位1), SUM(數值欄位2), ...]
   FROM [資料表]
   WHERE [篩選條件] 
   GROUP BY [欄位1, 欄位2, ...]
群組統計（含小計與總計）
SELECT [欄位1, 欄位2, ...], [SUM(數值欄位1), SUM(數值欄位2), ...]
   FROM [資料表]
   WHERE [篩選條件] 
   GROUP BY ROLLUP ( [欄位1, 欄位2, ...] )

舊版的SQL Server不支援

以下列舉，那些功能是從哪一個版本的SQL Server開始支援的
  • SQL Server 6.5 (65)
  • SQL Server 7.0 (70)
  • SQL Server 2000 (80)
  • SQL Server 2005 (90)
  • SQL Server 2008 (100)
  • SQL Server 2012 (110)
  • SQL Server 2014 (120)
  • SQL Server 2016 (130)
  • SQL Server 2017 (140)
  • SQL Server 2019 (150)
    • 建立索引或資料表的PK欄位時，OPTIMIZE_FOR_SEQUENTIAL_KEY 參數（優化序列鍵）
  • SQL Server 2022 (160)

連線數

USE master
 
--目前連線數
SELECT cntr_value AS User_Connections FROM sys.sysperfinfo AS sp
WHERE sp.object_name='SQLServer:General Statistics'
AND sp.counter_name='User Connections'
 
--連線明細
SELECT c.session_id, c.connect_time, s.login_time, c.client_net_address, s.login_name, s.status
FROM sys.dm_exec_connections c left join sys.dm_exec_sessions s on c.session_id = s.session_id
設定SQL Server的連線數目上限
取自「https://www.player.idv.tw/prog/index.php?title=SQL_Server&oldid=17726」