SQL Server

出自 ProgWiki
前往: 導覽搜尋
Microsoft SQL Server

作者
研發者 Microsoft
最初正式版
最新穩定版 SQL Server 2014 / 2014年4月1日,2年前2014-04-01
最新測試版 /
作業系統 Microsoft Windows
系统平台
可用語言
類型 關聯式資料庫管理系統
授權協議
網站 http://www.microsoft.com/zh-tw/server-cloud/products/sql-server/

SQL Server,參照:『維基百科~Microsoft_SQL_Server

目錄

SQL Server版本

技術文件

MSDN
Technet
其他

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函數

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