# SQL Server學習資源 for Developer and DBA
###### tags: `SQL Server` `Microsoft` `SQL` `Learning`

# SQL Server學習資源 for Developer
## 1.SQL Server基礎安裝

https://www.microsoft.com/en-us/sql-server/sql-server-downloads?rtc=1
Developer > download now >
下載安裝 SQL Server Developer版本
Developer版本功能等同於Enterprise版
開發人員可以自行下載安裝在自己的開發環境使用

Install SQL Server from the Installation Wizard (Setup)
https://docs.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-from-the-installation-wizard-setup?view=sql-server-ver15

Download SQL Server Management Studio (SSMS)
https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15

Quickstart: Connect and query a SQL Server instance using SQL Server Management Studio (SSMS)
https://docs.microsoft.com/en-us/sql/ssms/quickstarts/ssms-connect-query-sql-server?view=sql-server-ver15

Lesson 1: Connecting to the Database Engine
https://docs.microsoft.com/en-us/sql/relational-databases/lesson-1-connecting-to-the-database-engine?view=sql-server-ver15

Lesson 2: Connecting from Another Computer
https://docs.microsoft.com/en-us/sql/relational-databases/lesson-2-connecting-from-another-computer?view=sql-server-ver15

## 2.SQL Server基礎管理
帳號權限管理

[Create a Login](https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/create-a-login?view=sql-server-ver15)

[Join a Role](https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/join-a-role?view=sql-server-ver15)

## 3.SQL Server基礎開發

(1)Create Databases
[Create a Database](https://docs.microsoft.com/en-us/sql/relational-databases/databases/create-a-database?view=sql-server-ver15)

Lesson 2: Connecting from Another Computer
(2)Create Tables
[Create Tables (Database Engine)](https://docs.microsoft.com/en-us/sql/relational-databases/tables/create-tables-database-engine?view=sql-server-ver15)

(3)Create Views
[Create Views](https://docs.microsoft.com/en-us/sql/relational-databases/views/create-views?view=sql-server-ver15)

(4)Create stored procedures
[Create a stored procedure](https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/create-a-stored-procedure?view=sql-server-ver15)

## 4.SQL Server基礎查詢(分析語法)

### (1)使用範例資料庫
[AdventureWorks sample databases](https://docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-ver15&tabs=ssms)

### (2)查詢用的SQL語法
[SELECT (Transact-SQL)](https://docs.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql?view=sql-server-ver15)

SELECT select_list [ INTO new_table ]
[ FROM table_source ] [ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ]
The UNION, EXCEPT, and INTERSECT operators can be used between queries to combine or compare their results into one result set.

專注在範例語法的練習，理解範例的意義
請練習以下項目
#### SELECT Example
[SELECT Example](https://docs.microsoft.com/en-us/sql/t-sql/queries/select-examples-transact-sql?view=sql-server-ver15)

約有18個練習語法


#### SQL Server Joins
https://www.sqlservertutorial.net/sql-server-basics/sql-server-joins/
INNER JOIN
LEFT JOIN
RIGHT JOIN

#### SELECT - GROUP BY- Transact-SQL
https://docs.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-ver15
約有9個練習語法

#### SELECT - HAVING (Transact-SQL)
https://docs.microsoft.com/en-us/sql/t-sql/queries/select-having-transact-sql?view=sql-server-ver15
約有1個練習語法

#### SELECT - ORDER BY Clause (Transact-SQL)
https://docs.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-ver15

| 語法類型 | 練習語法數量 |
| -------- | -------- |
| Basic syntax | 4 |
| Specifying ascending and descending sort order | 3 |
| Specifying a collation | 1 |
| Specifying a conditional order | 2 |
| Using ORDER BY in a ranking function | 1 |
| Limiting the number of rows returned | 5 |
| Using ORDER BY with UNION, EXCEPT, and INTERSECT | 1 |

#### Aggregate Functions (Transact-SQL)
https://docs.microsoft.com/en-us/sql/t-sql/functions/aggregate-functions-transact-sql?view=sql-server-ver15

請優先學習以下常用Function
AVG()
COUNT()
MAX()
MIN()
SUM()

#### Mathematical Functions (Transact-SQL)
https://docs.microsoft.com/en-us/sql/t-sql/functions/mathematical-functions-transact-sql?view=sql-server-ver15
請優先學習以下常用Function
AVG()
ROUND()

#### String Functions (Transact-SQL)
https://docs.microsoft.com/en-us/sql/t-sql/functions/string-functions-transact-sql?view=sql-server-ver15
請優先學習以下常用Function
取字串 LEFT()、RIGHT()、SUBSTRING()、CHARINDEX()
長度 LEN()
大小寫 LOWER()、UPPER()
去空白 LTRIM()、RTRIM()、TRIM()

#### Date and Time Data Types and Functions (Transact-SQL)
https://docs.microsoft.com/en-us/sql/t-sql/functions/date-and-time-data-types-and-functions-transact-sql?view=sql-server-ver15
請優先學習以下常用Function
GETDATE()
DATEADD()
DATEDIFF()
DATEPART()
DAY()
MONTH()
YEAR()

#### CAST and CONVERT (Transact-SQL)
https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15
練習
Examples的A~K

#### 進階SQL語法
CTE
WITH common_table_expression (Transact-SQL)
https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15
練習
Examples的A~I

### (3) 教學課程：撰寫國際性通用的 Transact-SQL 陳述式
Tutorial: Writing Transact-SQL Statements
https://docs.microsoft.com/en-us/sql/t-sql/tutorial-writing-transact-sql-statements?view=sql-server-ver15

Lesson 1: Create and query database objects
https://docs.microsoft.com/en-us/sql/t-sql/lesson-1-creating-database-objects?view=sql-server-ver15
Create a database
Create a Table
Insert and update data in a table
Read data from a table
Create views and stored procedures

Lesson 2: Configure permissions on database objects
https://docs.microsoft.com/en-us/sql/t-sql/lesson-2-configuring-permissions-on-database-objects?view=sql-server-ver15
Create a login
Grant access to a database
Create views and stored procedures
Grant permission to stored procedure

Lesson 3: Delete database objects
https://docs.microsoft.com/en-us/sql/t-sql/lesson-3-deleting-database-objects?view=sql-server-ver15
Revoke stored procedure permissions
Drop permissions
Delete table
Remove database

## 線上練習題
LeetCode - problem - Database
https://leetcode.com/tag/database/

Free
免費帳號Database題數
(Easy約11題、Medium約6題、Hard約3題)

Premium
Yearly Subscription $159/yr (prices are marked in USD)
Our most popular plan previously sold for $299 and is now only $13/month . This plan saves you over 60% in comparison to the monthly plan.

Monthly Subscription $35/mo (prices are marked in USD)
Down from $39/month.
Our monthly plan grants access to all premium features, the best plan for short-term subscribers.


change Database Product

type your code on Code Editor

Run Code Result: "Run Code" feature is provided to help you debug your code. After clicking "Run Code", the system will execute your code and output the result based on the current test case. When execution of your code is completed, the system will generate information on input & output, for example, input, your output, and expected output. If your code has a compile error or other errors, they will also appear in the "Run Code Result" panel.

Submit: When after testing your code you decide to submit your code for judging, click on the "Submit" button. Once submitted, your code will be analyzed in a more systematic way through our online judging system. Your code will be evaluated against hundreds of thousands of test cases on the backend to ensure it meets various time and space restrictions and passes corner cases. When the judgment is completed, the system will automatically display the results in the "Submissions" tab.

SQL Exercises, Practice, Solution
https://www.w3resource.com/sql-exercises/

* SQL Retrieve data from tables [33 Exercises]
* SQL Boolean and Relational operators [12 Exercises]
* SQL Wildcard and Special operators [22 Exercises]
* SQL Aggregate Functions [25 Exercises]
* SQL Formatting query output [10 Exercises]
* SQL Quering on Multiple Tables [8 Exercises]
* FILTERING and SORTING on HR Database [38 Exercises]
* SQL JOINS
* SQL JOINS [29 Exercises]
* SQL JOINS on HR Database [27 Exercises]
* SQL SUBQUERIES
* SQL SUBQUERIES [39 Exercises]
* SQL SUBQUERIES on HR Database [55 Exercises]
* SQL Union[9 Exercises]
* SQL View[16 Exercises]
* SQL User Account Management [16 Exercise]
* Movie Database
* BASIC queries on movie Database [10 Exercises]
* SUBQUERIES on movie Database [16 Exercises]
* JOINS on movie Database [24 Exercises]
* Soccer Database
* Introduction
* BASIC queries on soccer Database [29 Exercises]
* SUBQUERIES on soccer Database [33 Exercises]
* JOINS queries on soccer Database [61 Exercises]
* Hospital Database
* Introduction
* BASIC, SUBQUERIES, and JOINS [39 Exercises]
* Employee Database
* BASIC queries on employee Database [115 Exercises]
* SUBQUERIES on employee Database [77 Exercises]
* SQL Challenges-1:
* SQL Challenges-1 [35 Exercises]

https://www.w3resource.com/sql-exercises/sql-aggregate-functions.php

# SQL Server學習資源 for DBA

## Recovery Model
[SQL Server Recovery Models](https://www.mssqltips.com/sqlservertutorial/2/sql-server-recovery-models/)

[Understanding SQL Server Recovery Models and Transaction Log Use](https://www.mssqltips.com/sqlservertip/5343/understanding-sql-server-recovery-models-and-transaction-log-use/)

## Backup and restore

[Types of SQL Server Backups](https://www.mssqltips.com/sqlservertutorial/6/types-of-sql-server-backups/)

Different types of SQL Server backups you can create:
Full backups (最常用)
Differential backups (常用)
File backups
Filegroup backups
Partial backups
Copy-Only backups (偶爾用)
Mirror backups
Transaction log backups (常用)

[SQL Server Restore Database Options and Examples](https://www.mssqltips.com/sqlservertip/6893/restore-database-sql-server-options-examples/)

## Maintenance Plan
[Getting Started with SQL Server Maintenance Plans - Part 1](https://www.mssqltips.com/sqlservertip/6099/getting-started-with-sql-server-maintenance-plans-part-1/
)

[Create a New SQL Server Maintenance Plan with the Maintenance Plan Designer](https://www.mssqltips.com/sqlservertip/6181/create-a-new-sql-server-maintenance-plan-with-the-maintenance-plan-designer/)

[Backup Database Task in SQL Server Maintenance Plans](https://www.mssqltips.com/sqlservertip/6249/backup-database-task-in-sql-server-maintenance-plans/)

## Database Mail
[Setting up Database Mail for SQL Server](https://www.mssqltips.com/sqlservertip/1100/setting-up-database-mail-for-sql-server/)

[Setup SQL Server Database Mail to use a Gmail, Hotmail, or Outlook account](https://www.mssqltips.com/sqlservertip/2578/setup-sql-server-database-mail-to-use-a-gmail-hotmail-or-outlook-account/)

## Alerts and Operator
[How to setup SQL Server alerts and email operator notifications](https://www.mssqltips.com/sqlservertip/1523/how-to-setup-sql-server-alerts-and-email-operator-notifications/)

## Linked Server
[Understanding SQL Server Linked Servers](https://www.mssqltips.com/sqlservertip/6083/understanding-sql-server-linked-servers/)

[SQL Server Linked Servers Tips](https://www.mssqltips.com/sql-server-tip-category/56/linked-servers/)

[Server Performance and Activity Monitoring](https://docs.microsoft.com/en-us/sql/relational-databases/performance/server-performance-and-activity-monitoring?view=sql-server-ver15)

## Upgrade or migration

### Transfer logins and passwords between instances of SQL Server
[Transfer logins and passwords between instances of SQL Server](https://docs.microsoft.com/en-us/troubleshoot/sql/security/transfer-logins-passwords-between-instances)

### script-out SQL Server or Database object
[Get script for every action in SQL Server Management Studio](https://www.mssqltips.com/sqlservertip/2179/get-script-for-every-action-in-sql-server-management-studio/)

[SQL Server 2008 R2 Generate Scripts Wizard with Database Schema and Data](https://www.mssqltips.com/sqlservertip/2500/sql-server-2008-r2-generate-scripts-wizard-with-database-schema-and-data/)

### compatibility level
[Upgrading SQL Server databases and changing compatibility levels](https://www.mssqltips.com/sqlservertip/1436/upgrading-sql-server-databases-and-changing-compatibility-levels/)

[ALTER DATABASE (Transact-SQL) compatibility level](https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver15)

## High Availability (Business continuity and database recovery)

[Business continuity and database recovery - SQL Server](https://docs.microsoft.com/en-us/sql/database-engine/sql-server-business-continuity-dr?view=sql-server-ver15)

* Backup and Restore
* Always On availability group
* Always On Failover Cluster Instances (FCI)
* Database Mirroring
* Log Shipping
* Replication

### Always On availability group

[What is SQL Server AlwaysOn?](https://www.mssqltips.com/sqlservertip/4717/what-is-sql-server-alwayson/)

[What is an Always On availability group?](https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/overview-of-always-on-availability-groups-sql-server?view=sql-server-ver15)

[SQL Server AlwaysOn Availability Groups - Part 1 configuration](https://www.mssqltips.com/sqlservertip/2519/sql-server-alwayson-availability-groups-part-1-configuration/)

[SQL Server AlwaysOn Availability Groups - Part 2 Availability Groups Setup](https://www.mssqltips.com/sqlservertip/2518/sql-server-alwayson-availability-groups-part-2-availability-groups-setup/)

[Monitor SQL Server AlwaysOn Availability Groups](https://www.mssqltips.com/sqlservertip/2573/monitor-sql-server-alwayson-availability-groups/)

[Manual SQL Server Availability Group Failover](https://www.mssqltips.com/sqlservertip/3437/manual-sql-server-availability-group-failover/)

[Add a SQL Server Database to an Existing Always On Availability Group](https://www.mssqltips.com/sqlservertip/5283/add-a-sql-server-database-to-an-existing-always-on-availability-group/)

[Adding a Database to an existing SQL Server Always ON Configuration](https://www.mssqltips.com/sqlservertip/5437/adding-a-database-to-an-existing-sql-server-always-on-configuration/)

[Remove Database from SQL Server AlwaysON Availability Group Secondary Replica](https://www.mssqltips.com/sqlservertip/4819/remove-database-from-sql-server-alwayson-availability-group-secondary-replica/)

[SQL Server AlwaysOn Availability Group Backup Preference Setting](https://www.mssqltips.com/sqlservertip/4976/sql-server-alwayson-availability-group-backup-preference-setting/)

[Setup and Implement SQL Server 2016 Always On Basic Availability Groups](https://www.mssqltips.com/sqlservertip/4980/setup-and-implement-sql-server-2016-always-on-basic-availability-groups/)

### SQL Server Failover Cluster Instance
[Step-by-step Installation of SQL Server 2019 on a Windows Server 2019 Failover Cluster - Part 1](https://www.mssqltips.com/sqlservertip/6539/stepbystep-installation-of-sql-server-2019-on-a-windows-server-2019-failover-cluster-part-1/)

https://www.mssqltips.com/sqlservertip/6585/sql-server-2019-installation-on-windows-server-2019-failover-cluster/

https://www.mssqltips.com/sqlservertip/6586/clustering-sql-server-2019-step-by-step/

https://www.mssqltips.com/sqlservertip/6629/sql-server-2019-cluster-setup/

What's the Quarantine state in Windows Failover Clusters
https://www.mssqltips.com/sqlservertip/6714/windows-failover-cluster-node-quarantined/

### Log Shipping

[Step By Step SQL Server Log Shipping](https://www.mssqltips.com/sqlservertip/2301/step-by-step-sql-server-log-shipping/)

[SQL Server Log Shipping Tips](https://www.mssqltips.com/sql-server-tip-category/100/log-shipping/)

### SQL Server troubleshooting
https://learn.microsoft.com/en-us/troubleshoot/sql/welcome-sql-server


Database Engine