《SQL Server 2016必知必會》要點(diǎn)記錄

因?yàn)楣ぷ髦虚_始用到SQL Server，之前的項(xiàng)目大多都是用Oracle和Mysql，因此對SQL Server也不是太熟悉，正好手邊有《SQL Server 2016必知必會》這本書，也就花了一些時間大致讀了一遍?？偨Y(jié)來說，這本書屬于入門級，適合對SQL Server沒有或者幾乎沒有基礎(chǔ)的人。

SQL Server介紹

1. Sql Server的系統(tǒng)數(shù)據(jù)庫分為：master、model、msdb和tempdb。

?Master 數(shù)據(jù)庫記錄SQLServer系統(tǒng)的所有系統(tǒng)級別信息（表sysobjects）。他記錄所有的登錄賬號（表sysusers）和系統(tǒng)配置。Master數(shù)據(jù)庫是這樣一個數(shù)據(jù)庫，他記錄所有其他的數(shù)據(jù)庫（表sysdatabases），包括數(shù)據(jù)庫文件的位置。Master數(shù)據(jù)庫記錄SQLServer的初始化信息，他始終指向一個可用的最新 Master 數(shù)據(jù)庫備份。

?Model 數(shù)據(jù)庫是作為在系統(tǒng)上創(chuàng)建數(shù)據(jù)庫的模板。當(dāng)系統(tǒng)收到“Create DATABASE” 命令時，新創(chuàng)建的數(shù)據(jù)庫的第一部分內(nèi)容從Model 數(shù)據(jù)庫復(fù)制過來，剩余部分由空頁填充，所以SQL Server數(shù)據(jù)中必須有Mode數(shù)據(jù)庫。

?Msdb 數(shù)據(jù)庫供SQLServer 代理程序調(diào)度警報和作業(yè)以及記錄操作員時使用。比如，我們備份了一個數(shù)據(jù)庫，會在表backupfile中插入一條記錄，以記錄相關(guān)的備份信息。

? Tempdb 數(shù)據(jù)庫保存系統(tǒng)運(yùn)行過程中產(chǎn)生的臨時表和存儲過程。當(dāng)然，它還滿足其他的臨時存儲要求，比如保存SQL Server生成的存儲表等。Tempdb數(shù)據(jù)庫是一個全局咨詢，任何連接到系統(tǒng)的用戶都可以在該數(shù)據(jù)庫中產(chǎn)生臨時表和存儲過程。Tempdb 數(shù)據(jù)庫在每次SQL

?Server啟動的時候，都會清空該數(shù)據(jù)庫中的內(nèi)容，所以每次啟動SQL Server后，該表都是干凈的。臨時表和存儲過程在連接斷開后會自動除去，而且當(dāng)系統(tǒng)關(guān)閉后不會有任何活動連接，因此，tempdb 數(shù)據(jù)庫中沒有任何內(nèi)容會從SQL Server的一個會話保存到另外一個會話中。

?默認(rèn)情況下，在 SQL Server 在運(yùn)行時 tempdb 數(shù)據(jù)庫會根據(jù)需要自動增長。不過，與其它數(shù)據(jù)庫不同，每次啟動數(shù)據(jù)庫引擎時，它會重置為其初始大小。如果為 tempdb 數(shù)據(jù)庫定義的大小較小，則每次重新啟動 SQL Server時，將tempdb 數(shù)據(jù)庫的大小自動增加到支持工作負(fù)荷所需的大小這一工作可能會成為系統(tǒng)處理負(fù)荷的一部分。為避免這種開銷，可以使用 ALTER DATABASE 增加 tempdb 數(shù)據(jù)庫的大小。

2. 常規(guī)標(biāo)識符和分隔標(biāo)識符

如果是不遵守標(biāo)識符命名規(guī)則的標(biāo)識符，在T-SQL中必須使用分隔符號([]): select * from [my table];

3.SQL實(shí)例就是SQL服務(wù)器引擎。在一臺計(jì)算機(jī)上可以安裝多個SQL Server 2016，每個SQL Server 2016可以理解成一個實(shí)例。

4. 在SQL Server 2016中最多可以指定32767個數(shù)據(jù)庫。

數(shù)據(jù)類型

1. tinyint：占一個字節(jié)的空間，存儲0~255的整數(shù)。

2. bigint：8個字節(jié)，存儲2^-63 ~ 2^63-1

3. Varchar:與char類型的區(qū)別是：存儲的長度不是列長，而是數(shù)據(jù)的長度

4. text：存儲大容量文本，理論容量是2^31-1個字節(jié)

5. ntext：與text類似，采用unicode字符集，理論容量是2^30-1個字節(jié)

6. Rowversion:每一次對數(shù)據(jù)表的更改，SQL

7. Server都會更新一個內(nèi)部的序列數(shù)，這個序列數(shù)保存在Rowversion字段中。所有Rowversion列的值在數(shù)據(jù)表中是唯一的，并且每張表中只能有一個包含Rowversion字段的列存在。

8. timestamp：一張表中只能有一個timestamp字段。

9. 約束:主鍵約束，唯一性約束，檢查約束，默認(rèn)約束，外鍵約束。

視圖

1. 通過視圖可以刪除行。但是視圖的數(shù)據(jù)必須來源于一個單表，即視圖的select語句必須只引用單個表。

T-SQL

1. T-SQL是微軟設(shè)計(jì)開發(fā)的一種結(jié)構(gòu)化查詢語言。

2. 全局變量名由@@符號開始。用戶不能建立全局變量，也不可能使用set語句去修改全局變量的值。

3. case語句格式：

Case ?When <條件表達(dá)式> then <運(yùn)算式>When <條件表達(dá)式> then <運(yùn)算式>Else <運(yùn)算式> End

SQL數(shù)據(jù)查詢

1. 用with語句檢查一致性：with語句用于指定臨時命名的結(jié)果集，這些結(jié)果集稱為公用表達(dá)式，其生命周期在該批處理語句執(zhí)行后結(jié)束。

With expression_name [(cokumn_name[,….n]]As (CTE_query_definition)

Ex:With cte_count(班級，性別，人數(shù))AS (select 班級，性別，人數(shù) from XS);Select * from cte_count;

2.?使用into子句可以將查詢結(jié)果生成放入一個新表或存放在臨時表中。如果要將查詢結(jié)果放入臨時表，在臨時表名前面加上‘#’號。

Select * into #temptableFrom XSWhere age < 30;Select * from #temptable;

3.where子句是在分組之前對數(shù)據(jù)進(jìn)行篩選，having是對分組進(jìn)行篩選

4. union中order? ? by子句只能位于最后一個查詢語句后；

5. union中列的數(shù)據(jù)類型不必完全相同，但數(shù)據(jù)類型間必須可以自動轉(zhuǎn)換

6. Order by不能用于子查詢，但是指定了top時則可以

7.子查詢最多可以嵌套32層

8.含exists的子查詢不產(chǎn)生任何數(shù)據(jù)，只用來判斷子查詢中是否有結(jié)果返回

SQL數(shù)據(jù)操作

1. Primary key默認(rèn)約束是clustered，unique約束默認(rèn)為non clustered。

2. 修改視圖數(shù)據(jù)時一次只能對一個表中的數(shù)據(jù)進(jìn)行操作，不能對表中的計(jì)算字段進(jìn)行修改。

3. 視圖雖然是單表或者多表的動態(tài)數(shù)據(jù)集合，但如果用戶刪除視圖中的數(shù)據(jù)來源于單表，并且沒有觸發(fā)器等約束，那么刪除視圖中的數(shù)據(jù)也將會影響到數(shù)據(jù)表中的數(shù)據(jù)。

存儲過程

1. 存儲過程分為：系統(tǒng)存儲過程，用戶存儲過程和擴(kuò)展存儲過程。

2.存儲過程工作流程

3. sp_help和sp_helptext可以用于查詢存儲過程的結(jié)構(gòu)信息。

4. 使用alter修改存儲過程時會將之前的過程內(nèi)容進(jìn)行覆蓋。

觸發(fā)器

1. 當(dāng)執(zhí)行刪除操作時，DELETE觸發(fā)器被激活，用于控制用戶刪除的數(shù)據(jù)。當(dāng)執(zhí)行DELETE觸發(fā)器時，被刪除的數(shù)據(jù)存放在DELETED表中，操作表中的記錄被刪除。

2. UPDATE觸發(fā)器的操作類型分為兩步：一是將更新前的記錄存儲在DELETED表中，二是將更新后的記錄存儲在INSERTED表中。

3. AFTER觸發(fā)器首先會建立INSERTED和DELETED表，然后執(zhí)行SQL語句中的數(shù)據(jù)操作，最后才會執(zhí)行觸發(fā)器中的代碼。而SQL SERVER 2016支持INSTEAD OF 觸發(fā)器，用于INSTEAD OF 觸發(fā)器則是在建立INSERTED和DELETED表后直接執(zhí)行觸發(fā)器。

4. 觸發(fā)器功能：DDL觸發(fā)器，登錄觸發(fā)器

5. 禁用觸發(fā)器：DISABLE TRIGGER

索引

1. 種類：聚集索引，非聚集索引，全文索引，XML索引，空間索引，唯一索引，包含列索引，索引視圖，篩選索引。

2. 聚集索引：聚集索引根據(jù)數(shù)據(jù)行的鍵值在表或視圖中排序和存儲這些數(shù)據(jù)行。每個表只能有一個聚集索引，因?yàn)閿?shù)據(jù)行本身只能按一個順序排序。

3. 創(chuàng)建聚集索引應(yīng)當(dāng)先于創(chuàng)建非聚集索引，因?yàn)榫奂饕淖兞吮碇行械奈锢眄樞颉?/p>

4. 非聚集索引具有獨(dú)立于數(shù)據(jù)行的結(jié)構(gòu)，其包含非聚集索引鍵值，并且每個鍵值項(xiàng)都有指向包含該鍵值的數(shù)據(jù)行的指針。從非聚集索引中的索引指向數(shù)據(jù)行的指針稱為行定位器。

5. 每張表只能有一個聚集索引，但允許最多有249個非聚集索引。

6. 主鍵是聚集索引。

7. Exec sp_helpindex <index_name>可以查看索引信息。

8. Set showplan_all <on|off>可以查看索引的分析。

9. 數(shù)據(jù)操作過程中會產(chǎn)生索引碎片。碎片整理的方法通過重新組織索引或者重新生成索引來完成。

10. 在sys.dm_db_index_physical_stats的返回值中，avg_fragmentation_in_percent反應(yīng)的是邏輯碎片的百分比，當(dāng)該返回值小于30%時，可以使用alter index reorganize重新組織索引，如果大于30%，使用alter index rebuild重新生成索引。

11. 每個表或者索引視圖只允許有一個全文索引。

游標(biāo)

1. 游標(biāo)是指向 查詢結(jié)果集的一個指針。

2. 游標(biāo)的實(shí)質(zhì)是一種從包含多條數(shù)據(jù)記錄的結(jié)果集中每次提取一條的記錄的機(jī)制。

事務(wù)

1. 臟讀：當(dāng)一個事務(wù)讀取到另一個事務(wù)未提交的更新數(shù)據(jù)時稱為臟讀。

2. 幻讀：一個事務(wù)讀取到另一個事務(wù)已提交的新插入的數(shù)據(jù)。例如A和B事務(wù)并發(fā)執(zhí)行，A查詢數(shù)據(jù)，B插入或者刪除數(shù)據(jù)。當(dāng)A查詢一個結(jié)果集時B正好插入一條記錄，這時A再次查詢會出現(xiàn)以前沒有或者已刪除掉的記錄。

3. 五種鎖：更新鎖，排它鎖，共享鎖，鍵范圍鎖，架構(gòu)鎖。

優(yōu)化

1. 盡量使用簡單的數(shù)據(jù)類型，例如能使用整型就不用字符型。

2. 盡量避免null。

3. 避免負(fù)邏輯。例如? ? !=,<>或not in。

4. 避免在索引列上進(jìn)行運(yùn)算，避免在索引列上用OR運(yùn)算符，避免使用IS NULL

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

友情鏈接更多精彩內(nèi)容