康拍樂 compiler note

-- 設定資料庫為單一使用者模式，並強制終止所有其他連線 ALTER DATABASE [dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; -- 更改資料庫的定序 ALTER DATABASE [dbname] COLLATE Chinese_Taiwan_Stroke_CI_AS;; -- 將資料庫設定回多使用者模式 ALTER DATABASE [dbname] SET MULTI_USER;

針對所有SQL Server連線查出現有連線,一一刪除 EXEC sp_who KILL spid號碼 刪除指定資料庫上的所有連線，並變更為單一使用者連線模式 USE master GO ALTER DATABASE [資料庫名稱] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO 切換回多人使用者連線模式 ALTER DATABASE [資料庫名稱] SET MULTI_USER; GO

使用產生指令碼將檔案備份， 當.SQL 檔案太大， 從SSMS打開或是記事本，會當掉無法開啟 可以使用以下方法執行：  C:\Program Files\Microsoft SQL Server\130\Tools\Binn\ sqlcmd -S 資料庫IP -U 使用者名稱 -P 使用者密碼 -d 資料庫名稱 -i ".sql檔案路徑，如:「C:\…\SQL.sql" sqlcmd -S Localhost -U sa -P password -d dbname -i "C:\compilerok\compilerok.sql"

  查詢語法如下  可查資料庫中，各table 所佔容量、筆數  SELECT       t . object_id ,    OBJECT_NAME ( t . object_id )  ObjectName ,    sum ( u . total_pages )   *  8 Total_Reserved_kb ,    sum ( u . used_pages )   *  8 Used_Space_kb ,    u . type_desc ,    max ( p . rows )  RowsCount FROM    sys . allocation_units  u    JOIN   sys . partitions  p  on  u . container_id  =  p . hobt_id    JOIN   sys . tables  t  on  p . object_id   =  t . object_id GROUP   BY    t . object_id ,    OBJECT_NAME ( t . object_id ),    u . type_desc ORDER   BY    Used_Space_kb  desc ,    ObjectName ;  

不同版本的資料庫 當要匯回至低版本時，先前可能會使用產生指令碼 可以使用  匯出資料層應用程式 進行備份/還原 備份： 至該指定資料庫，選擇匯出  匯出資料層應用程式 並跟著步驟填寫存放位置即可完成 還原： 在資料庫 選擇 匯入資料層應用程式   並選擇.bacpac檔案路徑，及目的路徑，即可移轉完成 官方參考：https://docs.microsoft.com/zh-tw/sql/relational-databases/data-tier-applications/export-a-data-tier-application?view=sql-server-ver15

  備份 紀錄 SELECT TOP 20 b . physical_device_name , a . backup_start_date , a . first_lsn , a . user_name , a . type FROM msdb .. backupset a INNER JOIN msdb .. backupmediafamily b ON a . media_set_id = b . media_set_id --WHERE a.type = 'L' ORDER BY a . backup_finish_date DESC SELECT b . name , a . type , MAX ( a . backup_finish_date ) lastbackup FROM msdb .. backupset a INNER JOIN master .. sysdatabases b ON a . database_name COLLATE DATABASE_DEFAULT = b . name COLLATE DATABASE_DEFAULT GROUP BY b . name , a . type ORDER BY b . name , a . type 還原紀錄 select * from msdb .. restorehistory order by restore_date desc

針對大量置換某個欄位內容 可以使用以下語法取代 UPDATE TABLE SET  CColumn=REPLACE (cast(CColumn as nvarchar(max)),'old word', 'new word' )  WHERE CColumn like '%old word%'

JSON 格式已存在很久，SQL 2016以後才加入新的功能可以直接在SQL 下指令查詢： ISJSON  可測試字串是否包含有效的 JSON。 SELECT id , json_col FROM table WHERE ISJSON(json_col) > 0 JSON_VALUE  可從 JSON 字串擷取純量值。 DECLARE @jsonInfo NVARCHAR ( MAX ) SET @jsonInfo=N '{ "info":{ "type":1, "address":{ "town":"Bristol", "county":"Avon", "country":"England" }, "tags":["Sport", "Water polo"] }, "type":"Basic" }' SELECT JSON_QUERY(jsonInfo, '$.info.address' ) AS Address FROM table JSON_QUERY  可從 JSON 字串擷取物件或陣列。 JSON_MODIFY  可更新 JSON 字串中的屬性值，並傳回更新後的 JSON 字串。 來源出處： MSDN

在語法上直接加上  WITH  COMPRESSION BACKUP   DATABASE   MyDB   TO   DISK = 'D:\SQLData\MyDB.bak' WITH   COMPRESSION GO (PS. SQLExpress 不支援此功能)

SELECT name, recovery_model_desc  FROM sys.databases --修改簡單模式 USE master ; ALTER DATABASE TESTDB SET RECOVERY SIMPLE;

SELECT name, physical_name ,size FROM sys.master_files order by size desc

刪除語法： DECLARE @publicationDB AS sysname; DECLARE @publication AS sysname; SET @publicationDB = N'IOEWEBSR1'; SET @publication = N'IOE'; -- Remove a transactional publication. --USE [AdventureWorks2008R2] EXEC sp_droppublication @publication = @publication; -- Remove replication objects from the database. USE [master] EXEC sp_replicationdboption   @dbname = @publicationDB,   @optname = N'publish',   @value = N'false'; GO 參考：http://msdn.microsoft.com/zh-tw/library/ms147833.aspx 如果遇到"無法刪除發行集"的狀況 刪除發行集發生主體dbo不存在 解決方法 (1).先停用 SQL Server Agent ，再按右鍵刪除 (2). 方法一： ALTER AUTHORIZATION ON DATABASE::[輸入出問題的資料庫名稱] TO [資料庫管理員帳號] 方法二： USE [出問題的資料庫名稱] GO EXEC sp_changedbowner '資料庫管理員帳號'  GO http://msdn.microsoft.com/zh-tw/library/ms178630.aspx

SQL 資料存在就更新，不存在就新增一筆寫法 方法1： IF EXISTS (SELECT * FROM Table1 WHERE Column1='value1')     UPDATE Table1 SET Column2='value2' WHERE Column1='value1' ELSE     INSERT INTO Table1 VALUES ('value1','value2') 方法2： UPDATE Table1 SET  Column2='value2' WHERE Column1='value1' IF @@ROWCOUNT=0     INSERT INTO Table1 VALUES (...)

新增欄位: ALTER TABLE table_name ADD column_name datatype 刪除欄位: ALTER TABLE table_name DROP COLUMN column_name 修改欄位型態： ALTER TABLE table_name ALTER column_name datatype

訊息 15281，層級 16，狀態 1，程序 ，行 1 SQL Server 已封鎖元件 'Ad Hoc Distributed Queries' 的 STATEMENT 'OpenRowset/OpenDatasource' 之存取，因為此元件已經由此伺服器的安全性組態關閉。系統管理員可以使用 sp_configure 來啟用 'Ad Hoc Distributed Queries' 的使用。如需有關啟用 'Ad Hoc Distributed Queries' 的詳細資訊，請參閱《SQL Server 線上叢書》中的＜介面區組態＞(Surface Area Configuration)。 Msg 15281, Level 16, State 1, Procedure , Line 1 SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online. 解決方式： 在資料庫按右鍵選擇 Facet 將第一項AdHocRemoteQueriesEnabled 設定為 True

．查詢此主機所有資料庫 SELECT * FROM sys.databases ．查詢此資料庫所有資料表 SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'; SELECT Distinct TABLE_NAME FROM information_schema.TABLES ．查詢資料表內的型態    sp_help Table_Name   Select * From INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME = 'Table_Name' 官方文件參考： sp_help：  http://technet.microsoft.com/en-us/library/ms187335.aspx

SELECT TOP 100 * FROM table ORDER BY NEWID()

只select出重覆出資料或不重覆資料 Select 語法 如下：

訊息如下： 不允許儲存變更。您所做的變更需要卸除並重新建立下列的資料表。您已進行至資料表，且無法重新建立或啟用 [防止儲存變更] 選項的變更需要重新建立資料表。

遇到某些中文難字、日文 INSERT 時在資料庫顯示會變在問號??呈現 一般寫法： INSERT INTO TableName (Column1, Column2) VALUES ('value1','Value2') 只需要將語法修改如下： INSERT INTO TableName (Column1, Column2) VALUES ( N 'value1', N 'Value2') 記得欄位要是 nvarchar 或 nchar 型態