突出重圍：Transact-SQL管理與開發實例精粹

《突出重圍：Transact-SQL管理與開發實例精粹》是年電子工業出版社出版的圖書，作者是趙松濤。

基本介紹

• 書名：突出重圍：Transact-SQL管理與開發實例精粹
• ISBN：9787121114472
• 頁數：436頁
• 出版社：電子工業出版社
• 裝幀：平裝
• 開本：16

內容簡介

《突出重圍:Transact-SQL管理與開發實例精粹》將作者10餘年從事SQL Server資料庫管理和開發經驗積累的Transact-SQL綜合性實例整理歸類並進行剖析。可以幫助讀者快速借鑑和開發出適合自己的Transact-SQL實例，這些實例既可以用於資料庫管理，也可以用於資料庫系統開發。同時，由於Transact-SQL語言良好的傳承和繼承性，因此，《突出重圍:Transact-SQL管理與開發實例精粹》的絕大部分代碼可以跨越SQL Server版本的限制，在SQL Server 2008、2005甚至2000上都可以使用，即使今後推出的新版本，幾乎也不用修改就可以移植使用。《突出重圍:Transact-SQL管理與開發實例精粹》涉及Transact-SQL使用的各個方面，但又不是在線上叢書語法的簡單複製，而是側重於通過實例來學習綜合使用Transact-SQL各語法要素完成特定任務的方法。

《突出重圍:Transact-SQL管理與開發實例精粹》既適合資料庫管理人員，也適合開發人員閱讀和參考，還可作為大中專院校及各類培訓班的教材，極具實用性。

目錄

第1章 Transact-SQL語法元素1

1.1 標識符1

1.1.1 必須有標識符嗎1

1.1.2 標識符的排序規則1

1.1.3 常規標識符2

1.1.4 帶分隔設定的標識符3

1.2 數據類型3

1.2.1 哪些對象需要數據類型3

1.2.2 如何選擇恰當的數據類型4

1.2.3 基本數據類型5

1.2.4 SQL Server 2008新增數據類型5

1.2.5 深入研究tinyint數據類型6

1.2.6 深入研究smallint數據類型8

1.2.7 深入研究int（Integer）數據類型10

1.2.8 深入研究bigint數據類型12

1.2.9 decimal（numeric）數據類型的使用14

1.2.10 float數據類型的使用16

1.2.11 real數據類型的使用17

1.2.12 char數據類型的使用17

1.2.13 varchar數據類型的使用17

1.2.14 用CAST函式轉換數據類型18

1.2.15 用CONVERT函式轉換數據類型18

1.3 常量19

1.3.1 數值型常量的格式19

1.3.2 字元串型常量的格式20

1.3.3 日期時間型常量的格式20

1.4 注釋20

1.4.1 添加單行注釋信息20

1.4.2 添加多行注釋信息20

1.5 變數21

1.5.1 變數的數據類型21

1.5.2 變數的分類21

1.5.3 局部變數的聲明21

1.5.4 局部變數的賦值22

1.5.5 局部變數的作用域23

1.6 常用聚合函式23

1.6.1 計算平均值AVG23

1.6.2 計算最小值MIN24

1.6.3 計算最大值MAX25

1.6.4 計算求和值SUM26

1.6.5 計算項數值COUNT（COUNT_BIG）26

1.6.6 計算標準偏差值STDEV27

1.6.7 計算方差VAR28

1.7 常用日期時間函式29

1.7.1 獲取當前日期GETDATE29

1.7.2 獲取年度信息YEAR30

1.7.3 獲取月份信息MONTH30

1.7.4 獲取天數信息DAY30

1.7.5 任意獲取日期時間值部分DATEPART31

1.7.6 日期加法DATEADD31

1.7.7 日期減法DATEDIFF32

1.7.8 2008中新增的日期時間型函式33

1.7.9 判斷是否為日期數據ISDATE33

1.8 常用字元串函式33

1.8.1 獲取字元的ASCII碼ASCII33

1.8.2 獲取ASCII碼對應的字元CHAR34

1.8.3 獲取字元的Unicode編碼UNICODE35

1.8.4 獲取Unicode編碼對應的字元NCHAR36

1.8.5 獲取字元串第一次出現位置PATINDEX36

1.8.6 生成空格字元串SPACE37

1.8.7 按指定次數重複生成字元串REPLICATE38

1.8.8 截取子串SUBSTRING39

1.8.9 獲取字元串長度LEN39

1.8.10 替換字元串中內容STUFF40

1.8.11 指定位置搜尋字元串中內容CHARINDEX41

1.8.12 生成帶分隔設定的Unicode字元串QUOTENAME42

1.8.13 轉換浮點數字為字元串STR42

1.8.14 截取左邊字元串LEFT43

1.8.15 截取右邊字元串RIGHT43

1.8.16 截取左邊空格LTRIM44

1.8.17 截取右邊空格RTRIM44

1.8.18 轉換為小寫字元串LOWER45

1.8.19 轉換為大寫字元串UPPER45

1.8.20 反序字元串REVERSE46

1.8.21 獲取字元串位元組數DATALENGTH46

1.9 常用文本和圖像函式47

1.9.1 查找特定字元串PATINDEX47

1.9.2 獲取文本指針TEXTPTR47

1.9.3 檢測文本指針是否有效TEXTVALID49

1.9.4 讀取文本圖像列內容READTEXT49

1.9.5 寫入文本圖像列內容WRITETEXT50

1.9.6 更新文本圖像列內容UPDATETEXT51

1.10 常用數學函式52

1.10.1 計算絕對值ABS52

1.10.2 獲取大於等於的最小整數值CEILING53

1.10.3 獲取小於等於的最大整數值FLOOR53

1.10.4 獲取隨機數RAND53

1.10.5 獲取指定長度和精度ROUND54

1.11 常用元數據函式54

1.11.1 獲取資料庫標識符DB_ID54

1.11.2 獲取資料庫名稱DB_NAME55

1.11.3 獲取資料庫屬性值DATABASEPROPERTYEX56

1.11.4 獲取檔案組標識符FILEGROUP_ID57

1.11.5 獲取檔案組名稱FILEGROUP_NAME58

1.11.6 獲取檔案組屬性值FILEGROUPPROPERTY58

1.11.7 獲取檔案標識符FILE_ID60

1.11.8 獲取檔案名稱稱FILE_NAME61

1.11.9 獲取檔案屬性值FILEPROPERTY62

1.11.10 獲取數據對象標識符OBJECT_ID63

1.11.11 獲取數據對象名稱OBJECT_NAME64

1.11.12 獲取檔案屬性值OBJECTPROPERTYEX66

1.12 運算符67

1.12.1 算術運算符67

1.12.2 位運算符68

1.12.3 比較運算符68

1.12.4 邏輯運算符68

1.12.5 字元串連線運算符69

1.12.6 賦值運算符69

1.12.7 運算符的優先權69

第2章 Transact-SQL語句70

2.1 控制流語句70

2.1.1 程式塊語句BEGIN..END70

2.1.2 判斷語句IF..ELSE71

2.1.3 循環語句WHILE72

2.1.4 分支判斷語句CASE74

2.1.5 無條件退出語句RETURN75

2.1.6 無條件跳轉語句GOTO77

2.1.7 延期執行語句WAITFOR78

2.2 運行時生成語句79

2.2.1 用EXECUTE執行動態命令80

2.2.2 用SP_EXECUTESQL執行動態命令81

2.2.3 參數替換83

2.2.4 為什麼提倡使用SP_EXECUTESQL84

2.2.5 使用輸出參數的SP_EXECUTESQL85

2.3 語句之間數據的傳遞86

2.3.1 Transact-SQL語句之間數據的傳遞86

2.3.2 Transact-SQL與高級語言之間數據的傳遞87

2.4 錯誤處理90

2.4.1 SQL Server資料庫引擎錯誤91

2.4.2 用TRY..CATCH發現錯誤91

2.4.3 捕獲錯誤的系統函式92

2.4.4 用@@ERROR捕獲上一條語句的錯誤93

2.4.5 用RAISERROR反饋錯誤94

2.5 批處理96

2.5.1 GO的含義96

2.5.2 研究批處理的執行96

2.5.3 批處理的意義99

2.5.4 為什麼臨時變數作用域不能跨批處理100

2.5.5 如何編寫批處理101

2.5.6 批處理如何處理錯誤101

2.5.7 腳本102

第3章 查詢伺服器配置信息103

3.1 配置查詢環境103

3.2 查詢伺服器屬性值104

3.2.1 ServerProperty系統函式104

3.2.2 查詢資料庫引擎版本105

3.2.3 綜合使用ServerProperty函式查詢105

3.3 判斷登錄賬戶身份106

3.4 讀寫註冊表信息106

3.4.1 讀註冊表xp_regenumvalues107

3.4.2 讀註冊表xp_regread107

3.4.3 寫註冊表xp_regwrite108

3.4.4 刪除註冊表項xp_regdeletevalue108

3.4.5 讀註冊表項xp_instance_regread109

3.4.6 寫註冊表xp_instance_regwrite109

3.4.7 讀註冊表中的主要數據檔案路徑信息110

3.4.8 讀註冊表中的錯誤日誌檔案路徑信息111

3.4.9 讀註冊表中的日誌檔案路徑信息112

3.5 綜合實例113

3.5.1 獲取“常規”選項卡信息113

3.5.2 獲取伺服器配置參數信息114

3.5.3 獲取伺服器上資料庫信息115

3.5.4 獲取伺服器上處理器數量115

3.5.5 提升SQL Server伺服器優先權116

3.5.6 獲得Windows版本信息116

3.5.7 獲得身份驗證機制117

3.5.8 獲得登錄審核機制118

3.5.9 自動檢測伺服器重要的配置參數118

3.5.10 查詢伺服器的用戶或用戶組126

3.5.11 查詢用戶角色或應用程式角色127

第4章 登錄過程129

4.1 連線129

4.1.1 查詢所有連線信息129

4.1.2 查詢連線總數130

4.1.3 查詢阻塞的連線130

4.1.4 查詢用戶建立的連線130

4.1.5 查詢系統建立的連線130

4.1.6 查詢活動的用戶連線131

4.1.7 查詢用戶連線的不同客戶機131

4.2 配置131

4.2.1 查詢當前系統配置131

4.2.2 查詢當前最大工作執行緒數132

4.3 會話132

4.3.1 查詢伺服器上的所有會話132

4.3.2 查詢會話建立的游標132

4.3.3 查詢會話的錯誤信息133

4.3.4 查詢會話的緩衝區命中率133

4.3.5 查詢會話的作業系統執行緒134

4.3.6 查詢當前會話ID135

4.4 綜合運用135

4.4.1 綜合查詢連線統計信息135

4.4.2 判斷當前登錄的許可權137

4.4.3 查詢會話逾時設定137

4.4.4 判斷連線是否為DAC138

4.4.5 查詢當前連線的網路參數138

4.4.6 查詢SQL Server版本信息139

4.4.7 查詢伺服器基本屬性139

4.4.8 查詢當前登錄名141

4.4.9 查詢當前的活動事務數141

4.4.10 查詢資料庫緩衝區命中率低的會話141

4.4.11 查詢資料庫緩衝區命中率最低的會話的SQL語句142

第5章 資料庫相關144

5.1 查詢資料庫信息144

5.1.1 查詢所有的用戶資料庫名稱144

5.1.2 用sp_dboption存儲過程配置資料庫145

5.1.3 配置資料庫的恢復模式145

5.1.4 判斷資料庫的名稱是否含有特定字元146

5.1.5 判斷兩個資料庫的排序規則是否一致147

5.1.6 判斷資料庫是否系統資料庫和可讀148

5.1.7 查詢可以訪問的資料庫148

5.1.8 查詢資料庫的檔案149

5.1.9 查詢資料庫的系統表150

5.1.10 查詢資料庫的排序規則151

5.1.11 查詢資料庫的排序規則Windows代碼152

5.1.12 查詢資料庫支持的排序規則152

5.1.13 統計資料庫上的進程數152

5.1.14 查詢資料庫的兼容性級別153

5.1.15 查詢資料庫的最後備份時間153

5.1.16 查詢資料庫的最後日誌備份時間153

5.2 資料庫的存儲空間154

5.2.1 查詢資料庫的總空間154

5.2.2 查詢資料庫的數據檔案總空間154

5.2.3 查詢資料庫的日誌檔案總空間155

5.2.4 查詢數據檔案已經分配的空間155

5.2.5 查詢數據檔案的可用空間155

5.2.6 查詢日誌檔案已經分配的空間157

5.3 綜合運用159

5.3.1 查詢資料庫的屬性159

5.3.2 獲取所有資料庫的數據檔案空間使用率排序159

第6章 未公開的DBCC命令162

6.1 DBCC PAGE查詢數據頁面162

6.1.1 查詢DBID和DBNAME163

6.1.2 查詢FILENUM和PAGENUM163

6.1.3 DBCC PAGE的典型用法163

6.2 DBCC LOG查詢日誌164

6.3 DBCC ERRORLOG初始化錯誤日誌165

6.4 DBCC FLUSHPROCINDB清除快取存儲過程166

6.5 DBCC BUFFER查詢緩衝區166

6.6 DBCC DBINFO顯示資料庫結構167

6.7 DBCC DBTABLE顯示數據字典168

6.8 DBCC IND顯示錶的索引頁面信息168

6.9 DBCC PRTIPAGE顯示索引每行的數據頁169

6.10 DBCC RESOURCE顯示伺服器資源情況169

6.11 DBCC DBRECOVER恢復質疑資料庫170

6.12 DBCC CACHESTATS查看記憶體統計信息170

6.13 DBCC MEMORYSTATUS查詢記憶體分配171

6.14 DBCC跟蹤標記171

6.14.1 概念171

6.14.2 打開跟蹤標記172

6.14.3 關閉跟蹤標記172

6.14.4 查詢跟蹤標記狀態172

6.15 DBCC SHOWFILESTATS顯示數據檔案空間172

6.16 WITH TABLERESULTS表格顯示信息173

6.17 DBCC LOGINFO查詢虛擬日誌信息173

6.18 DBCC EXTENTINFO查詢盤區數174

第7章 數據和日誌檔案176

7.1 FILEPROPERTY函式獲得檔案屬性176

7.1.1 判斷檔案是否唯讀176

7.1.2 判斷檔案是否主要數據檔案177

7.1.3 查詢檔案已經使用空間177

7.2 FN_VIRTUALFILESTATS獲得檔案I/O性能177

7.2.1 查詢指定檔案的I/O性能178

7.2.2 查詢指定資料庫的所有檔案的I/O性能178

7.2.3 查詢所有資料庫所有檔案的I/O性能179

7.2.4 查詢檔案I/O性能的動態性能視圖180

7.3 日誌檔案相關180

7.2.1 查詢VLF分布情況180

7.2.2 移動日誌檔案的內容182

7.2.3 刪除日誌檔案的內容182

7.4 數據檔案相關183

7.4.1 查詢所有資料庫的數據檔案空間情況183

7.4.2 查詢資料庫存儲空間的詳細分布185

7.4.3 查詢已分配空間中的行內數據192

7.4.4 查詢已分配空間中的LOB大型數據193

7.4.5 查詢已分配空間中的行溢出數據194

7.4.6 查詢哪些類型的對象使用了數據空間194

7.4.7 查詢使用數據空間的對象類型及其數量194

7.5 綜合查詢資料庫檔案的I/O性能195

第8章 數據對象197

8.1 索引197

8.1.1 更新表上所有索引的統計信息197

8.1.2 更新表上特定索引的統計信息199

8.1.3 統計資料庫中索引使用的空間總和200

8.1.4 索引使用的空間詳細情況203

8.2 表208

8.2.1 獲得資料庫表的存儲空間分布208

8.2.2 獲得數據緩衝區中數據對象的存儲分布212

第9章 性能監控218

9.1 對象分配性能218

9.1.1 監控盤區分配情況218

9.1.2 監控盤區回收情況219

9.1.3 監控頁面拆分情況220

9.1.4 監控表上鎖升級情況221

9.2 記憶體性能222

9.2.1 SQL Server記憶體詳解223

9.2.2 查詢SQL Server伺服器使用的記憶體數量225

9.2.3 查詢連線使用的記憶體數量225

9.2.4 查詢鎖使用的記憶體數量226

9.2.5 查詢最佳化器使用的記憶體數量226

9.2.6 查詢SQL使用的記憶體數量226

9.2.7 查詢伺服器能夠使用的最大記憶體數量227

9.2.8 查詢當前伺服器上可用記憶體數量227

9.2.9 查詢最小伺服器記憶體228

9.2.10 查詢最大伺服器記憶體228

9.2.11 查詢計算機的物理記憶體229

9.2.12 查詢計算機的虛擬記憶體229

9.3 過程緩衝區性能229

9.3.1 查詢過程緩衝區使用的記憶體229

9.3.2 查詢過程緩衝區內容類別230

9.3.3 查詢過程緩衝區命中率230

9.3.4 查詢過程緩衝區對象分布比例231

9.3.5 查詢過程緩衝區中各類對象的命中率232

9.3.6 查詢過程緩衝區各類對象使用的比例232

9.3.7 清除過程緩衝區中的對象233

9.3.8 過程緩衝區中消耗資源Top對象234

9.4 數據緩衝區性能238

9.4.1 查詢數據緩衝區的記憶體數量238

9.4.2 查詢數據緩衝區的理想記憶體數量239

9.4.3 查詢數據緩衝區的可用記憶體數量239

9.4.4 查詢數據緩衝區中數據使用的記憶體239

9.4.5 查詢數據緩衝區的命中率240

9.4.6 清除數據緩衝區中的對象240

9.4.7 數據緩衝區中消耗資源Top對象241

9.5 其他實例242

9.5.1 查詢資源等待情況242

9.5.2 查詢計數器種類和性能值243

9.5.3 查詢執行計畫的屬性244

9.5.4 查詢性能計數器表是否可以使用245

9.5.5 查詢伺服器記憶體總體分布情況245

第10章 管理其他實例250

10.1 檢測和使用XP_CMDSHELL250

10.1.1 檢測XP_CMDSHELL是否啟用250

10.1.2 用XP_CMDSHELL執行命令253

10.2 錯誤日誌253

10.2.1 產生並使用新的錯誤日誌檔案253

10.2.2 獲得錯誤日誌檔案的信息254

10.3 代理服務254

10.3.1 判斷代理服務是否啟動254

10.3.2 查詢代理服務上產生的警報254

10.3.3 配置代理服務自啟動255

10.3.4 判斷代理服務是否允許訪問255

10.3.5 獲得代理服務啟動信息256

10.3.6 記錄代理服務啟動時間256

10.3.7 獲得代理服務子系統的信息256

10.3.8 獲得代理服務詳細配置信息256

10.3.9 運行代理服務監視器258

10.3.10 刷新代理服務作業258

10.3.11 查詢代理服務活動作業259

10.4 設定登錄環境259

10.5 資料庫260

10.5.1 產生資料庫檢查點260

10.5.2 設定資料庫單用戶模式260

10.5.3 分離資料庫260

10.5.4 附加資料庫260

10.5.5 判斷指定路徑的日誌檔案是否存在261

10.5.6 獲取主要數據檔案中的檔案信息261

10.5.7 獲取主要數據檔案中的資料庫信息262

10.5.8 獲取資料庫的用戶表信息263

10.5.9 脫機資料庫264

10.5.10 在線上資料庫264

10.5.11 收縮資料庫264

10.5.12 判斷用戶是否可以訪問master資料庫264

10.6 訪問註冊表265

10.7 讀取硬碟空間信息266

10.8 判斷檔案是否存在266

10.9 存儲空間的碎片266

10.9.1 數據的存儲類型267

10.9.2 數據的存儲結構270

10.9.3 存儲空間的碎片類型270

10.9.4 碎片對數據操作性能的影響271

10.9.5 dbcc showcontig檢測碎片272

10.9.6 dm_db_index_physical_stats檢測碎片274

10.9.7 消除碎片的方法277

10.9.8 自動檢測當前資料庫的碎片278

10.9.9 自動消除資料庫的碎片283

第11章 數據查詢288

11.1 語法結構288

11.2 選擇列表289

11.2.1 選擇所有列289

11.2.2 選擇特定列293

11.2.3 包含運算符的查詢295

11.2.4 包含函式的查詢296

11.2.5 消除重複項297

11.3 FROM子句298

11.3.1 使用表別名298

11.3.2 使用表提示301

11.4 WHERE子句305

11.4.1 WHERE子句中的運算符306

11.4.2 比較查詢307

11.4.3 範圍查詢308

11.4.4 列表查詢309

11.4.5 模式匹配查詢310

11.5 GROUP BY子句312

11.5.1 語法結構312

11.5.2 實例分析313

11.6 ORDER BY子句314

11.6.1 語法結構314

11.6.2 實例分析315

11.7 HAVING篩選查詢316

11.7.1 HAVING篩選查詢的語法結構316

11.7.2 HAVING篩選查詢實例316

11.8 INTO查詢316

11.8.1 INTO查詢的語法結構316

11.8.2 INTO查詢實例317

11.9 SELECT聯結查詢317

11.9.1 SELECT聯結查詢的語法結構317

11.9.2 SELECT聯結查詢的分類317

11.9.3 笛卡兒積查詢318

11.9.4 等值聯結查詢318

11.9.5 自然聯結查詢318

11.9.6 自身聯結查詢318

11.9.7 內聯結查詢319

11.9.8 左外聯結查詢319

11.9.9 右外聯結查詢320

11.9.10 全外聯結查詢320

11.10 SELECT嵌套查詢320

11.10.1 什麼是SELECT嵌套查詢320

11.10.2 什麼是SELECT嵌套子查詢321

11.10.3 簡單嵌套查詢321

11.10.4 in嵌套查詢322

11.10.5 some嵌套查詢322

11.10.6 all嵌套查詢323

11.10.7 exists嵌套查詢324

11.11 操作結果集324

11.11.1 Union形成並集324

11.11.2 Except形成差集325

11.11.3 InterSect形成交集326

11.11.4 用公用表表達式臨時存儲結果集326

第12章 數據操作328

12.1 INSERT語句研究328

12.1.1 表結構和索引結構328

12.1.2 執行前的日誌情況分析329

12.1.3 執行情況分析329

12.1.4 執行後的日誌情況分析331

12.1.5 研究數據的變化情況332

12.1.6 研究結論333

12.2 UPDATE語句研究334

12.2.1 執行前的日誌情況分析334

12.2.2 執行情況分析334

12.2.3 執行後的日誌情況335

12.2.4 研究結論338

12.3 DELETE語句研究338

12.3.1 執行前的日誌情況分析338

12.3.2 執行情況分析339

12.3.3 執行後的日誌情況339

12.3.4 研究結論341

12.4 深入探討語句內部機制341

12.4.1 誰先插入數據342

12.4.2 誰先更新數據342

12.4.3 誰先刪除數據342

第13章 T-SQL游標343

13.1 游標概述343

13.1.1 什麼是游標343

13.1.2 游標的類型344

13.1.3 默認結果集是如何使用的344

13.1.4 什麼是MARS345

13.1.5 使用游標還是默認結果集345

13.1.6 使用伺服器游標還是客戶機游標346

13.2 T-SQL游標定義及使用346

13.2.1 T-SQL游標的生命周期346

13.2.2 T-SQL游標的聲明347

13.2.3 T-SQL游標的打開347

13.2.4 T-SQL游標的讀取348

13.2.5 T-SQL游標的關閉348

13.2.6 T-SQL游標的釋放349

13.2.7 T-SQL游標的綜合實例349

第14章 事務和鎖351

14.1 多用戶資料庫的並發問題351

14.1.1 並發訪問的實例351

14.1.2 並發問題352

14.2 事務353

14.2.1 事務的特性353

14.2.2 事務的狀態354

14.3 SQL SERVER中的事務機制355

14.3.1 事務模式355

14.3.2 更改事務模式356

14.3.3 事務的啟動357

14.3.4 事務日誌記錄357

14.3.5 事務編程語句357

14.3.6 事務的調度358

14.3.7 事務的隔離性級別358

14.3.8 批（GO）359

14.4 鎖360

14.4.1 鎖的粒度360

14.4.2 鎖的類型（模式）361

14.4.3 鎖持有度361

14.4.4 動態鎖機制362

14.4.5 查看鎖362

14.5 死鎖363

14.5.1 死鎖的發生364

14.5.2 消除死鎖的方法364

14.5.3 SQL Server 死鎖檢測和結束機制365

14.5.4 跟蹤和檢測死鎖365

第15章 存儲過程367

第16章 自定義函式和觸發器402

第17章 某項目資料庫T-SQL開發實例416