SQL Server 資料庫部分常用語句小結（二）

9. 查詢備份還原資料庫的進度。

select command

,percent_complete

,est_time_to_go=convert(varchar,(estimated_completion_time/3600000))+' hour, '

+convert(varchar,(estimated_completion_time)/60000)+' min, '

+convert(varchar,(estimated_completion_time)/1000)+' sec'

,start_time=convert(char(16),start_time,120)

,est_completion_time=convert(char(16),dateadd(second,estimated_completion_time/1000,getdate()),120)

,running_time=convert(varchar,((datediff(s,start_time,getdate()))/3600))+' hour, '

+convert(varchar,(datediff(s,start_time,getdate()))/60)+' min, '

+convert(varchar,(datediff(s,start_time,getdate())))+' sec'

,s.text

from sys.dm_exec_requests r

cross apply sys.dm_exec_sql_text(r.sql_handle) s

where r.command in ('BACKUP DATABASE','RESTORE DATABASE','BACKUP LOG','RESTORE LOG','DbccFilesCompact','DbccSpaceReclaim')

10. 删除指定表内的某個統計資訊（例如，有時候，我們在AlwaysOn可用性組的副庫上執行某個查詢，會提示某個統計資訊缺失）。

報錯資訊 

消息 2767，級别 16，狀态 1，過程 sp_table_statistics2_rowset，第 105 行

無法在系統目錄中找到統計資訊 'XXXXXX'。

文法

DROP STATISTICS table.statistics_name | view.statistics_name [ ,...n ]

這個指令在副庫上執行，如果提示 無法對 統計資訊 '‘XXXXXXX’' 執行 删除，因為它不存在，或者您沒有所需的權限。

此時 可轉到主庫上嘗試執行。

11. SQL Server 2012 關于序列 的基本操作【在對應DB下執行】。

SELECT * FROM sys.sequences

-- 擷取下一個值

SELECT NEXT VALUE FOR [dbo].[自定義的序列名稱];

-- 重置為1

ALTER SEQUENCE [dbo].[自定義的序列名稱] RESTART WITH 1 ;

12. 自增列（IDENTITY(1,1)）的資料導入。

2個表都有自增列（IDENTITY(1,1)），怎麼才能導入資料呢？

例如，我想将含有IDENTITY(1,1)列的大表QQ_CampTask_T1，Rename成一張舊表QQ_CampTask，接下來還要把其中部分資料導入到建立的QQ_CampTask_T1 中，新産生的表還是有IDENTITY(1,1)，此時導入資料，這個列就會報錯。

消息 8101，級别 16，狀态 1，第 1 行

僅當使用了列清單并且 IDENTITY_INSERT 為 ON 時，才能為表QQ_CampTask_T1'中的辨別列指定顯式值。

解決方案

SET IDENTITY_INSERT QQ_CampTask_T1【表名】 on

此效用就在同一個回話中有效，其它不行。另外 還需要特别注意的是：一定要寫明 列名。

例如：

SET IDENTITY_INSERT QQ_CampTask_T1 on

insert into QQ_CampTask_T1 ([ID],[CreateTime])

select [ID],[CreateTime] from QQ_CampTask

13. 修Table的改列名。

EXEC sp_rename '表名.[原列名]', '新列名', 'column'

14. 賦予指定賬戶檢視執行計劃的權限。

GRANT SHOWPLAN TO [XXX_test] ---賬号是XXX_test；在指定資料庫下執行

15. SQL語句執行耗費資源統計分析。

set statistics profile on

set statistics io on

set statistics time on

go

--寫SQL語句的地方

------------

set statistics profile off

set statistics io off

set statistics time off

16. 增加限制：如果表的某一列沒有限制，而要在此列增加限制。

類似指令 如下：

ALTER TABLE [dbo].[表明] ADD DEFAULT ((預設值)) FOR [列名]

17. sqlserver 伺服器更改主機名後，需要做一些操作，不然維護計劃 以及訂閱釋出都會有問題。有時修改計算機名後，執行select @@servername仍傳回原來的計算機名，表示"執行個體"并沒有随着修改。

USE master;

GO

IF SERVERPROPERTY('servername')<>@@SERVERNAME

BEGIN

DECLARE @server sysname;

SET @server=@@SERVERNAME;

EXEC sp_dropserver @server=@server;

SET @server=CAST(SERVERPROPERTY('servername')AS sysname );

EXEC sp_addserver @server=@server,@local='LOCAL';

END

（我們重新開機一下服務.OK）

18. 出現性能瓶頸時，執行以下代碼，檢查瓶頸代碼。

sp_lock

DBCC INPUTBUFFER(XXX) ---XXX 出現多次的spid

19. 查詢表的大小。

sp_spaceused 'Rel_OrderItem'

20. 字元與ASCII碼互相轉化，有的時候要對資料清理，是以有必要了解一下。

例如 Char("9") tab(水準制表符)

Char("10") 換行

Char("11") tab(垂直制表符)

Char("12") 換頁

Char("13") 回車 chr(13)&chr(10) 回車和換行的組合

Char("32") 空格 SPACE

（1）擷取字元的ASCII碼 ASCII

ASCII碼是對字元的标準編碼。要擷取字元的ASCII碼可以通過ASCII函數來實作。

文法：ASCII(espression)--這裡的expression是一個傳回char或varchar資料類型的表達式，ASCII函數僅對表達式最左側的字元傳回ASCII碼值。

例如：select ASCII('f') --輸出 102

（2）擷取ASCII碼對應的字元 Char

文法：char(integer_expression)

例如：select char(102) -- 輸出f

 21 擷取SQL Server 執行個體的機器名和Server IP。

擷取機器名的SQL語句

SELECT SERVERPROPERTY('MachineName')

SELECT @@SERVERNAME

擷取Server IP （伺服器和用戶端）

SELECT SERVERNAME = CONVERT(NVARCHAR(128),SERVERPROPERTY('SERVERNAME'))

,LOCAL_NET_ADDRESS AS 'IPAddressOfSQLServer'

,CLIENT_NET_ADDRESS AS 'ClientIPAddress'

FROM SYS.DM_EXEC_CONNECTIONS WHERE SESSION_ID = @@SPID

22 . 統計IP位址段（C段）

在CMDB系統中，會有一張表用來存放各伺服器的IP位址的，如果我們希望統計到C段（即???.???.???.***）

select distinct  REVERSE(SUBSTRING( REVERSE(IP),charindex('.',REVERSE(IP),1)+1,LEN(IP)-charindex('.',REVERSE(IP),1)+1)) + '.*' as 'ServerIP' from [dbo].[CMDB_ServersInfo] with(nolock)

SUBSTRING()方法;文法：SUBSTRING(character,start,length) ;介紹：參數1：要截取的字元串，參數2：開始截取的下标，參數3：截取的字元長度

CHARINDEX()方法；基本文法如下：CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] )；參數1 目标字元串，就是想要找到的字元串，最大長度為8000 ，                       參數2  用于被查找的字元串，參數3  開始查找的位置，為空時預設從第一位開始查找。

REVERSE（）  将字元串倒叙。主要是用來實作字元串倒置的一個功能，可以把字元串裡各個字元的順序倒過來。