---
# System prepended metadata

title: mssql index
tags: [mssql]

---

# mssql index
###### tags: `mssql`

[TOC]

## db design
[example Relational e-commerce database](/2uhoA6AtRpOvV2zMVocD7Q)

## 其他人文章
[\[ 筆記 \] 後端基礎 \- 資料庫、SQL 語法 (coderbridge.io)](https://mtr04-note.coderbridge.io/2020/07/27/about-SQL/)

## mssql CTE
```sql=
WITH cte_name (column1, column2, ...)
AS
(
    -- CTE query definition
    SELECT column1, column2, ...
    FROM source_table
    WHERE condition
)
SELECT *
FROM cte_name;
```


## MSSQL 2019/2017 資料庫設計與開發實務
[07 建立資料表與完整性限制條件](/TbJSiEz-RaG9xt4hTGVBMQ)
[17 交易處理與鎖定](/jIhvUH2bTFu8PYBckuiZ6w)

## MSSQL

## 語法
[ceiling, floor, round](https://byron0920.pixnet.net/blog/post/56498636)

## some proper noun explain
[Database membership vs Database owned schemas](/OhDT-Ia2SY6K4eMXV_uJnw)
[ROLLBACK and ROLLBACK TRANSACTION](/JvHYgzogTkiVXXnb7jtlvg)
[DQL, DML, DDL, DCL](https://learnsql.com/blog/what-is-dql-ddl-dml-in-sql/)
[candidate key](/M8YxZ1TjTsKzw2ZZJ-k5hA)


### Epress Edition 2022
### [ConnectString](https://www.connectionstrings.com/sql-server/)

```text=
"shopwebConnstring": "Server=localhost;Database=shopweb;Integrated Security=True;Trusted_Connection=True;TrustServerCertificate=True"
```

### [SQL server extension for vscode](https://learn.microsoft.com/en-us/sql/tools/visual-studio-code/sql-server-develop-use-vscode?view=sql-server-ver16)

### [install, connect, create and query data](https://learn.microsoft.com/en-us/sql/linux/quickstart-install-connect-ubuntu?view=sql-server-ver16)
[install mssql on ubuntu](/NOnffMiISy-9u0cNY2sfsA)
[Connect locally](/bCEazp8FRdK5MAQ7549aeQ)
[Create and query data](/hvjc-AEvTP6w28z_hhQVEA)

### [sqlcmd](https://learn.microsoft.com/en-us/sql/tools/sqlcmd/sqlcmd-use-utility?view=sql-server-ver16)

### issue fix
[Pre Login Handshake SQL Server Error | How to fix it?](/CJ9SYpqPRo-5dfPvbUjLOw)
[How to query the name of the current SQL Server database instance in MSSQL? ](/d0rqwnrUREC9TVtKATIVtQ)
[how can we drop user which own schema?](/DKyeRM2YS6OoFcr6OYZXlg)
[解決由別人新增資料表欄位時，在SSMS中查詢欄位會出現錯誤訊息的問題](https://dotblogs.com.tw/wellwind/2016/08/29/ssms-intellisense-cache)

### mssql 實際情形常用指令
[msssql 指令](/tzFyI5l_Tf2gISu7H-DQNQ)
[create table if not exist](/jX_DtSaTS_uvO2HfpUkoww)
[select date of born and caculate age](https://stackoverflow.com/questions/1572110/how-to-calculate-age-in-years-based-on-date-of-birth-and-getdate?page=1&tab=scoredesc#tab-top)
[drop table constraint, column, add new column, add constraint to exists column](/lwwXBDBTR8S3GGNhlvZ8Ew)
[reset identity counter number](/b0LBrJACS8Oy-Nws1wbRxw)
[修改資料庫定序](https://dotblogs.com.tw/jamesfu/2016/12/10/Change_Database_Collate)
[[MSSQL] DB重新命名之"資料庫無法獨佔鎖定來執行此作業"錯誤](http://codinggirlmia.blogspot.com/2017/07/mssql-db.html)
[Retrieve top 1 and last from mssql table based on particular column's value](/emLEyLSWRVybyfPva8S4Kw)
[mssql backup all database](https://dba.stackexchange.com/questions/21521/taking-backup-of-all-the-databases-in-sql-server)
[alter store procedure](/ABMyCVllTKKuSDxbsITuUg)
[ranking](/DPhm7TNaSiGWUiRFfp7dfA)
[查看資料表中有那些索引](/UjEmNWoCS7yEeqEUIkWasg)
[刪除所有資料](/cfymQ_6WTIyDjB_T8oPWBg)

## sql architect 連接到 mssql
### 下載 JDBC Driver
[JDBC](https://learn.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-ver16)

### step 1 設定 JDBC driver
![](https://hackmd.io/_uploads/ryKTl2ON3.png)

![](https://hackmd.io/_uploads/BkuyZn_Vh.png)

![](https://hackmd.io/_uploads/HJUxW3_Nn.png)


## SQLServer 2016 設計實務(陳正昌 chechang@ceii.net)
###  安裝流程
使用 sql developer 版本
- 資料庫引擎服務
- 用戶端工具連接性
- [預設執行個體](https://learn.microsoft.com/en-us/sql/sql-server/install/file-locations-for-default-and-named-instances-of-sql-server?view=sql-server-ver16)

#### 服務帳戶
- SQL Server Database Engine => 伺服器核心
- SQL Server Agent => 備份
- SQL Server Browser 不常用
### 定序
- 定序 = 排序
- 定序指示項 => Chinese_Taiwan_Stroke
- `Chinese_Taiwan_Stroke_CI_AS` 中文資料庫常用
- Windows 定序 = 微軟標準
- SQL 定序 = 國際標準(跟其他資料庫匯入匯出)
- 區分重音字打勾

### 驗證模式
- Windows 驗證模式(使用目前 windows 的使用者登入)，只能使用在 windows
- SQL Server 驗證 (能用在 Linux)
- 點選加入目前使用者
- FILESTREAM(檔案系統) 能儲存圖片、影片
- 檔案目錄 8


### SQL 上課
[3/23 01 課 上午+下午 建立資料庫、設定使用者、修改、資料庫設定](/yh58zDiLSpaw1T0MexZxfA)
[3/24 02 課 上午](/5az02oQiRminiBzwbw1CDg)
[3/27 03 課 下午](/QuG7_VC8SdKF0N8lTtSFTA)
[檢測查詢語法效率](/vUUcOM9xREm2P3MEIlS_Vw)
[homework](/gB89iGaZRdK9WTAdBnPUJg)

## where can I get old version of mssql
[stackoverflow](https://dba.stackexchange.com/questions/307452/how-get-old-versions-from-ms-sql-developer-edition)
[How to query the name of the current SQL Server database instance?](/d0rqwnrUREC9TVtKATIVtQ)

## mssql 管理
[SQL Profiler](https://ithelp.ithome.com.tw/articles/10201665)

### mssql 管理實務 book

### mssql 設計實務 book
[01 基礎概念](/udld2Y6rSkCnb48SfOhClw)
[02 規劃關聯式資料庫](/pE09987kS3SjTPfafiZwLQ)
[03 熟悉 SQL Server 的工作平台](/JY0lKC9FRGacb9nqkw0Kgg)
[04 認識 SQL 語言與資料型別](/WX2KLRZGTq6P_fvqTjb4GA)
[05 檢視 SQL 資料物件](/XYOCo-frQWKnXTbrrLx3PQ)
[06 建立資料庫與修改](/LUBSbk9gSliNKgu9QBusvg)
[07 建立、修改資料表與資料庫圖表](/dO0oDWZ-QACNBi3mFLGpRw)
[08 資料的新增、修改與刪除](/v1AMrpNuSLymuhsRxhtN8Q)
[09 select 查詢](/SFnFAdP9R8ydqjHFvk3rJw)
[10 union 更多查詢技巧](/Jdisq30FTSe8aqTmZEnUbQ)
[11 建立檢視表 View](/ggLMJApvRNmys-B7-rAJFw)
[12 善用索引加快查詢效率](/KPLQuYwnQQqEaVn8DAeO3g)
[13 T-SQL 程式設計](/2FzHC-XQTryMCRadBUtrew)
[14 預存程序](/NzqoTIWhRpqefdGZmBn0ZA)
[15 自訂函數與順序物件](/k5_EYHJ7Ti2V1X1fWtXhGw)
[16 觸發程序](/7MsCA8voSBuBKoNIj49MSA)
[17 使用資料指標](/dcWpe9RoTYiDziCIoo9DlQ)
[18 使用交易與鎖定](/Q56MrVC4Triihx5DH1xnww)
[資料庫備份](/T7q3YGhXQbS_1yb7B-8TCw)

