# SQL Server skill
###### tags: `SQL Server` `Microsoft` `SQL` `Data Warehouse` ` Learning`

## SQL Server安裝
| Sn | Type | 技能 | 文件 |
| -- | -------- | -------- | -------- |
| 1 | Stand-alone | SQL Server - Database Engine only |[Install SQL Server from the Installation Wizard (Setup)](https://learn.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-from-the-installation-wizard-setup?view=sql-server-ver16) |
| 2 | Tools | SQL Server Management Studio| [SQL Server Management Studio (SSMS) Step by Step Installation Guide](https://www.londonacademyofit.co.uk/blog/sql-server-management-studio-installation-guide)|
| 3 | Stand-alone | SQL Server - Database Engine, SSIS, SSRS, SSAS | [Install and configure SQL Server Reporting Services](https://learn.microsoft.com/en-us/sql/reporting-services/install-windows/install-reporting-services?view=sql-server-ver16) <br/> [Install SQL Server Analysis Services](https://learn.microsoft.com/en-us/analysis-services/instances/install-windows/install-analysis-services?view=asallproducts-allversions)<br/> [Install Integration Services (SSIS) ](https://learn.microsoft.com/en-us/sql/integration-services/install-windows/install-integration-services?view=sql-server-ver16) |
| 4 | Stand-alone | SQL Server - R, Python | [Install SQL Server 2022 Machine Learning Services (Python and R) on Windows](https://learn.microsoft.com/en-us/sql/machine-learning/install/sql-machine-learning-services-windows-install-sql-2022?view=sql-server-ver16)|
| 5 | FCI | SQL Server Failover Cluster Instance | [SQL Server Failover Cluster Installation](https://learn.microsoft.com/en-us/sql/sql-server/failover-clusters/install/sql-server-failover-cluster-installation?view=sql-server-ver16)|

## SQL Server服務管理
| Sn | Type | 技能 |文件|
| -- | -------- | -------- |-------- |
| 1 | Process | Start and stop SQL Server and SQL Server Agent service |[Start, stop, pause, resume, and restart SQL Server services](https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/start-stop-pause-resume-restart-sql-server-services?view=sql-server-ver16)|
| 2 | Protocol | Enable or Disable TCP\IP |[Configure Client Protocols](https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-client-protocols?view=sql-server-ver16)|
| 3 | Port | Change listen port from 1433 to 14330 |[Configure SQL Server to listen on a specific TCP port](https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-a-server-to-listen-on-a-specific-tcp-port?view=sql-server-ver16)|

## 資料庫管理與開發
| Sn | Type | Ojbect |技能 |
| -- | -------- | -------- | -------- |
| 1 | Developer | Database | Create a TESTDB1 DB and TESTDB2 DB on specific drive with multi-data file using SSMS GUI and T-SQL, |
| 2 | Management | Database | Backup and restore database using SSMS GUI and T-SQL|
| 3 | Management | Database | Restore a sample database(AdventureWorks) using SSMS GUI|
| 4 | Management | Database | Backup database regularly using Maintainance plan |
| 5 | Management | Database | 了解 Database Recovery Models 與 備份類型 |
| 6 | Management | Database | 最佳實務備份設定 using Maintainance plan |
| 7 | Management | Database | 查看所有資料庫清單與空間使用(UI or TSQL) |
| 8 | Management | Database | Increase or reduce database file size( mdf,ndf,ldf) |
| 9 | Management | Database | Move tempdb to different locaiton |
| 10 | Developer | Table | create a table on TESTDB1 |
| 11 | Developer | Table | 了解File Group |
| 12 | Developer | Table | create a table on different File group in TESTDB2 |
| 13 | Management | Table | 如何查看所有資料表(Tableu)清單與空間使用(UI or TSQL)|
| 14 | Developer | View | create a view on TESTDB1 |
| 15 | Management | View | 如何查看所有檢視(view)清單(UI or TSQL)|
| 16 | Developer | Procedure | Create a stored procedure on TESTDB1 |
| 17 | Developer | Procedure | create a stored procedure with parameter TESTDB1|
| 18 | Management | Procedure | 如何查看所有預存程序(stored procedure)清單(UI or TSQL)|
| 19 | security | Login | 了解Logins and Roles|
| 20 | security | Login | create a login1 資料庫最大權限 on TESTDB1 |
| 21 | security | Login | create a login3 只能SELECT一個table或view on TESTDB1 |
| 22 | security | Login | create a login4 只能執行一個SP on TESTDB1 |
| 23 | Management | Login | 如何查看所有登入帳號(login)清單(UI or TSQL) |
| 24 | Management | Login | 了解 Login and User and Permission UI/DMV |
| 25 | Developer | Job | Create a job for run a stored procedure regularly |
| 26 | Developer | Job | 如何查看所有作業(Job)清單(UI or TSQL) |
| 27 | Management | Job | Monitor Job status (Job activity, job history or TSQL) |
| 28 | Management | Job | 了解與設定Database Mail、SQL Agent Alert system、操作員 Operator 與 警示 Alert|
| 29 | Management | Job | configiure email notification when job finish on Job1 |
| 30 | Management | Job | how to start/stop/disable/enable a Job with SSMS GUI or TSQL |
| 31 | Data | SSIS | Import a csv/xlsx to Table, Export table to csv using Import and Export Wizard |
| 32 | Data | Job/SSIS | Schedule and execute a SSIS Package to import csv to Table |
| 33 | Data | Linked Server | Linked Server 用途、建議、與設定方法 |
| 34 | Data | Linked Server | create a linked server to another SQL Server(sqlserver2) and query data on SQL2 from Linked server |
| 35 | Developer | Procedure | how to send a mail(Database Mail) with TSQL |

## Monitor and Troubleshooting
| Sn | name | documentation |
| -------- | -------- | -------- |
| 1 | 連線問題與用戶端連線狀態疑難排解<br/> (1)Service/Port/netstat/pid<br/>(2)Activity Monitor/DMV/sp_who/sp_who2/DBCC INPUTBUFFER<br/>(3) kill/rollback |[Troubleshoot connectivity issues in SQL Server](https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/connect/resolve-connectivity-errors-overview) <br/><br/> [Start, stop, pause, resume, and restart SQL Server services](https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/start-stop-pause-resume-restart-sql-server-services?view=sql-server-ver16)<br/><br/> [Configure SQL Server to listen on a specific TCP port](https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-a-server-to-listen-on-a-specific-tcp-port?view=sql-server-ver16) <br/><br/> [netstat](https://learn.microsoft.com/en-us/windows-server/administration/windows-commands/netstat) <br/><br/> [Activity Monitor](https://learn.microsoft.com/en-us/sql/relational-databases/performance-monitor/activity-monitor?view=sql-server-ver16) <br/><br/> [Execution Related Dynamic Management Views and Functions (Transact-SQL)](https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/execution-related-dynamic-management-views-and-functions-transact-sql?view=sql-server-ver16) <br/><br/> |
| 2 | Performance Dashboard | [Performance Dashboard](https://learn.microsoft.com/en-us/sql/relational-databases/performance/performance-dashboard?view=sql-server-ver16) |
| 3 | SQL Server ERRORLOG |[View the SQL Server error log in SQL Server Management Studio (SSMS)](https://learn.microsoft.com/en-us/sql/relational-databases/performance/view-the-sql-server-error-log-sql-server-management-studio?view=sql-server-ver16)|
| 4 | SQL Server Profiler and SQL Trace |[SQL Server Profiler](https://learn.microsoft.com/en-us/sql/tools/sql-server-profiler/sql-server-profiler?view=sql-server-ver16) <br/><br/> [SQL Trace](https://learn.microsoft.com/en-us/sql/relational-databases/sql-trace/sql-trace?view=sql-server-ver16)|
| 5 | SQL Server Extended Event | [Quickstart: Extended Events](https://learn.microsoft.com/en-us/sql/relational-databases/extended-events/quick-start-extended-events-in-sql-server?view=sql-server-ver16)|
| 6 | Data Collection |[Data collection](https://learn.microsoft.com/en-us/sql/relational-databases/data-collection/data-collection?view=sql-server-ver16)|

## SQL Server觀念
| Sn | name | |
| -------- | -------- | -------- |
| 1 | 能夠說明資料庫的2種實體檔案的類型與作用 | [Database files and filegroups](https://learn.microsoft.com/en-us/sql/relational-databases/databases/database-files-and-filegroups?view=sql-server-ver16)|
| 2 | 能夠說明資料庫復原模型的差異 | [Recovery models (SQL Server)](https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server?view=sql-server-ver16)|
| 3 | 能夠說明資料庫相容性層級用途 | [View or change the compatibility level of a database](https://learn.microsoft.com/en-us/sql/relational-databases/databases/view-or-change-the-compatibility-level-of-a-database?view=sql-server-ver16) <br/><br/> [ALTER DATABASE (Transact-SQL) compatibility level](https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver16) <br/><br/>[Discontinued Database Engine functionality in SQL Server](https://learn.microsoft.com/en-us/sql/database-engine/discontinued-database-engine-functionality-in-sql-server?view=sql-server-ver16) |

## Tools
| Sn | name | documentation|
| -------- | -------- | -------- |
| 1 | SQL Server Profiler |[SQL Server Profiler](https://learn.microsoft.com/en-us/sql/tools/sql-server-profiler/sql-server-profiler?view=sql-server-ver16) |
| 2 | Database Engine Tuning Advisor | [Tutorial: Database Engine Tuning Advisor](https://learn.microsoft.com/en-us/sql/tools/dta/tutorial-database-engine-tuning-advisor?view=sql-server-ver16)|
| 3 | SQL Server Migration Assistant (SSMA) |[SQL Server Migration Assistant (SSMA)](https://learn.microsoft.com/en-us/sql/ssma/sql-server-migration-assistant?view=sql-server-ver15)|
| 4 | SQL Server Upgrade Advisor and Data Migration Assistant (DMA) | [Overview of Data Migration Assistant](https://learn.microsoft.com/en-us/sql/dma/dma-overview?view=sql-server-ver16) <p/><p/> [Tools for Upgrade SQL Server 升級SQL Server的工具](https://bradctchen.blogspot.com/2020/04/tools-for-upgrade-sql-server-sql-server.html)|
| 5 | SQL Server Data Tools (SSDT) for Visual Studio| [SQL Server Data Tools](https://learn.microsoft.com/en-us/sql/ssdt/sql-server-data-tools?view=sql-server-ver16)|

## 商務持續性與資料庫復原(高可用性)設定
| Sn | Type | Documentation |
| -- | -------- | -------- |
| 1 | Alwayo On Availability Group | 教學課程：在 Azure 虛擬機器上的 SQL Server 上建立可用性群組 https://docs.microsoft.com/zh-tw/azure/azure-sql/virtual-machines/windows/availability-group-manually-configure-prerequisites-tutorial |
| 2 | FCI (Failover Cluster Instance) | AlwaysOn 容錯移轉叢集執行個體 (SQL Server) https://docs.microsoft.com/zh-tw/sql/sql-server/failover-clusters/windows/always-on-failover-cluster-instances-sql-server?view=sql-server-ver15 PS.SQL Server安裝的第5項 |
| 3 | Database Mirroring | 資料庫鏡像 (SQL Server) https://docs.microsoft.com/zh-tw/sql/database-engine/database-mirroring/database-mirroring-sql-server?view=sql-server-ver15 |
| 4 | Log Shipping | 關於記錄傳送 (SQL Server) https://docs.microsoft.com/zh-tw/sql/database-engine/log-shipping/about-log-shipping-sql-server?view=sql-server-ver15 |
| 5 | Replication | SQL Server 複寫 https://docs.microsoft.com/zh-tw/sql/relational-databases/replication/sql-server-replication?view=sql-server-ver15 |

## SSRS (SQL Server Reporting Service) and Power BI
| Sn | Type | Documentation |
| -- | -------- | -------- |
| 1 | SQL Server Reporting service(SSRS) <br/> SQL Server Reporting service install and configure and design a report using report builder and publish to Report server (60分鐘) | [What is SQL Server Reporting Services (SSRS)?](https://learn.microsoft.com/en-us/sql/reporting-services/create-deploy-and-manage-mobile-and-paginated-reports?view=sql-server-ver16) |
| 2 | Power BI Server| [What is Power BI Report Server?](https://learn.microsoft.com/en-us/power-bi/report-server/get-started) |

## SSIS (SQL Server Integration Services) 與 SSAS (SQL Server Analysis Services)
| Sn | Type | Documentation |
| -- | -------- | -------- |
| 1 | SQL Server Integration Services | [SQL Server Integration Services](https://learn.microsoft.com/en-us/sql/integration-services/sql-server-integration-services?view=sql-server-ver16) |
| 2 | SQL Server Analysis Services | [SQL Server Analysis Services](https://learn.microsoft.com/en-us/analysis-services/ssas-overview?view=asallproducts-allversions)|

## 其他
Performance analysis: PSSDIAG and sqlnexus (cpu, memory bottleneck)

## query processing

SQL batch -> execution plan -> cache or not -> query running

Excution plan: statistics and index

## index

data page
Page 8K
extend size: 64 KB
An extent is eight physically contiguous pages, or 64 KB

https://learn.microsoft.com/en-us/sql/relational-databases/pages-and-extents-architecture-guide?view=sql-server-ver16

index 觀念 類似一本實體書，上面目錄與後面的索引頁

balance tree

索引結構
clustered index
non-clustered index

子頁層 row locator
non-clustered on heap table-> heap page id
non-clustered on clustered index -> clustered index id

索引優化與管理
DMV missing index 判斷增與刪
Database Tuning Advisor 工具判斷增修
手動判斷 異動index
分析執行計畫出現建立missing index 判斷增與刪
自行分析SQL Query與資料表結構與數量來判斷

Index欄位選擇
https://www.madeiradata.com/post/sql-server-index-mastery-choosing-the-right-column-sequence-order

Equality
Range and Inequality
Sorting and Grouping
Output and non-SARGEable (include columes)

SARG 原則
想讓查詢最佳化程式透過索引快速找尋資料(比較索引鍵值)，那WHERE子句必須符合SARG格式。

1.不要在Where欄位做運算、避免row by row操作、不要對欄位做運算、不要對欄位使用函數...
盡量別把%放在前面
如果要取得筆數數量使用COUNT(*)比Count(c1)效能好，Count(c1)會忽略c1 IS NULL數量.

查詢時避免使用NOT IN，使用NOT EXISTS替代NOT IN

2.符合SARG 原則
符合的運算子: <、>、=、<=、>=、LIKE(視萬用字元所在位置) 和BETWEEN。

不符合運算子: <>、!<、!>、NOT、NOT IN、NOT EXISTS和NOT LIKE..等。

雖然有時會因使用者需求導致寫出的查詢語法無法如願符合SARG格式，
但無論如何請盡可能將不符合SARG改成符合SARG，因為這是查詢調校的第一步。

統計資訊更新
執行計畫 cache (預先執行) 重要的大查詢

索引破碎
rebuid
reorganize

https://learn.microsoft.com/zh-tw/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver16

Index: Clustered index and Nonclustered
https://learn.microsoft.com/en-us/sql/relational-databases/statistics/statistics?view=sql-server-ver16

## Other Features
1.Audit
2.Policy
3.Polybase
4.R
5.Python

Performance
1.Index (Row-base stored Index, Columnstored index) 建立移除與分析，儲存大小
2.Performance counter

資料倉儲與整合初階開發 SSIS
| Sn | name |
| -------- | -------- |
| 1 | Foreach Loop Container |
| 2 | File System Task |
| 3 | Execute SQL Task |
| 4 | Data Flow Task - Flat File Source |
| 5 | Data Flow Task - Derived Column |
| 6 | Data Flow Task - OLE DB Source |
| 7 | Data Flow Task - OLE DB Destination |
| 8 | Data Flow Task - Data Conversion |
| 9 | Precedence Constraint Editor |
| 10 | Connection Managers |
| 11 | Expression |
| 12 | Error Handle |
| 13 | 版控 |
| 14 | Package Configurations |

SQL Server開發人員(基礎)
| Sn | Type | Ojbect |技能 |
| -- | -------- | -------- | -------- |
| 1 | Management | Database | restore a sample database AdventureWorks using SSMS GUI|
| 2 | Management | Database | create a database TESTDB1 using SSMS GUI and T-SQL |
| 3 | Management | Database | create a database TESTDB2 on specific drive with multi-data file using SSMS GUI and T-SQL |
| 4 | Management | Database | Backup and restore database with SSMS GUI and T-SQL |
| 5 | Developer | Table | create a table on TESTDB1 |
| 6 | Developer | Table | create a table on different File group in TESTDB2 |
| 7 | Developer | View | create a view on TESTDB1 |
| 8 | Developer | Procedure | Create a stored procedure on TESTDB2 |
| 9 | Developer | Procedure | create a stored procedure with parameter TESTDB2|
| 10 | security | Login | create a login1 資料庫最大權限 on TESTDB1 |
| 11 | security | Login | create a login3 只能SELECT一個table或view on TESTDB2 |
| 12 | security | Login | create a login4 只能執行一個SP on TESTDB2 |
| 13 | Developer | Job | create a Job1, execute a sql script for insert data from table1 to table2, and configure a schedule run on every day |
| 14 | Management | Job | how to start/stop/disable/enable a Job with SSMS GUI or TSQL |
| 15 | Management | Job | how to how to view job history with SSMS GUI or TSQL|
| 16 | Data | SSIS | Import a csv to Table, Export table to csv using Import and Export Wizard |
| 17 | Data | Job/SSIS | Schedule and execute a SSIS Package to import csv to Table |