# 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)