SQL Server language setting語言設定

SQL Server language setting

影響返回的系統訊息與日期時間格式

1.system messages

SET LANGUAGE 繁體中文;

GO

返回:

已將語言設定變更為 繁體中文。

SET LANGUAGE us_english; 

GO

返回:

Changed language setting to us_english.

2.date/time formats

DECLARE @Today DATETIME;  

SET @Today = '12/5/2007 13:00:11';  

  

SET LANGUAGE 繁體中文;  

SELECT DATENAME(month, @Today) AS 'Month Name';  

SET LANGUAGE us_english;  

SELECT DATENAME(month, @Today) AS 'Month Name' ;  

GO  

返回:

十二月

返回:

December

查詢支援的語言

select * from sys.syslanguages

Reference:

SET LANGUAGE (Transact-SQL)

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-language-transact-sql?view=sql-server-ver15

Delete large amount of data from a table 刪除大量資料作法

Delete large amount of data from a table
刪除大量資料作法
from my MSDN blog - August 30, 2016

Method 1

若刪除完成之後留下的資料較多的話(例如要刪除1/3的資料)，就用WHILE DELETE top語法來刪除

declare @n int
while 1=1
begin
DELETE top(2000)
FROM dbo.BigTable
WHERE time <= '2013-09-03 22:00:00.000'
OPTION(MAXDOP 1) -- 可考慮是否只使用一個CPU來執行刪除動作
set @n=@@ROWCOUNT
if @n<2000
break
end

Method 2

若留下的資料比較少(例如要刪除2/3的資料或更多的資料)，就可以考慮INSERT INTO再TRUNCATE或INSERT INTO再RENAME

INSERT INTO and TRUNCATE
1.將要保留的資料INSERT INTO到dbo.Temp_BigTable

SELECT * INTO dbo.Temp_BigTable
 FROM dbo.Temp_BigTable
 WHERE Date < '2015/1/1';

2.清空dbo.Temp_BigTable

TRUNCATE TABLE dbo.Temp_BigTable;

3.INSERT INTO dbo.BigTable from dbo.Temp_BigTable

INSERT INTO dbo.BigTable
SELECT * FROM dbo.Temp_BigTable;

可以參考這篇 SQL Server: Delete a Huge Amount of Data from a Table

INSERT INTO再RENAME
1.將要保留的資料INSERT INTO到dbo.Temp_BigTable
2.DROP TABLE dbo.Temp_BigTable
3.RENAME dbo.Temp_BigTable to dbo.BigTable

注意:
因為原Table會被刪除，所以需事先調查與保存與重新設定以下項目
1.權限
2.Trigger
3.Index

PS.以下狀況無法直接DROP TABLE
1.被Foreign Key或view with SCHEMABINDING reference的資料表
2.複寫發行資料表
3.啟用CDC的資料表

若有view with schemabinding

CREATE VIEW v_Table_2
WITH SCHEMABINDING

DROP TABLE會出現以下錯誤

Msg 3729, Level 16, State 1, Line 2
Cannot DROP TABLE 'dbo.Table_1' because it is being referenced by object 'v_Table_2'.

若有Foreign key reference

DROP TABLE會出現以下錯誤

Msg 3726, Level 16, State 1, Line 2
Could not drop object 'dbo.Table_1' because it is referenced by a FOREIGN KEY constraint.

Query SQL Server backup history and restore history records 查詢SQL Server資料庫備份與還原紀錄

Query SQL Server backup history and restore history records
查詢SQL Server資料庫備份與還原紀錄
from my MSDN blog - March 12, 2014

SQL Server備份還原紀錄

1.使用以下TSQL語法查詢備份檔紀錄

 SELECT 
 bs.backup_set_id,
 bs.database_name,
 bs.backup_start_date,
 bs.backup_finish_date,
 CAST(CAST(bs.backup_size/1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS [Size],
 CAST(DATEDIFF(second, bs.backup_start_date,
 bs.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' [TimeTaken],
 CASE bs.[type]
 WHEN 'D' THEN 'Full Backup'
 WHEN 'I' THEN 'Differential Backup'
 WHEN 'L' THEN 'TLog Backup'
 WHEN 'F' THEN 'File or filegroup'
 WHEN 'G' THEN 'Differential file'
 WHEN 'P' THEN 'Partial'
 WHEN 'Q' THEN 'Differential Partial'
 END AS BackupType,
 bmf.physical_device_name,
 CAST(bs.first_lsn AS VARCHAR(50)) AS first_lsn,
 CAST(bs.last_lsn AS VARCHAR(50)) AS last_lsn,
 bs.server_name,
 bs.recovery_model
 From msdb.dbo.backupset bs
 INNER JOIN msdb.dbo.backupmediafamily bmf 
 ON bs.media_set_id = bmf.media_set_id
 ORDER BY bs.server_name,bs.database_name,bs.backup_start_date;
 GO

透過SERVER_NAME欄位，可以判斷該備份檔是否是在這台SQL Server上執行的備份

如果SERVER_NAME欄位顯示別台SQL Server主機名稱，表示這個備份檔是從別台SQL Server複製過來並且在這台執行過RESTORE

 

2.使用以下TSQL查詢還原紀錄

 SELECT rs.[restore_history_id]
 ,rs.[restore_date]
 ,rs.[destination_database_name]
 ,bmf.physical_device_name
 ,rs.[user_name]
 ,rs.[backup_set_id]
 ,CASE rs.[restore_type]
 WHEN 'D' THEN 'Database'
 WHEN 'I' THEN 'Differential'
 WHEN 'L' THEN 'Log'
 WHEN 'F' THEN 'File'
 WHEN 'G' THEN 'Filegroup'
 WHEN 'V' THEN 'Verifyonlyl'
 END AS RestoreType
 ,rs.[replace]
 ,rs.[recovery]
 ,rs.[restart]
 ,rs.[stop_at]
 ,rs.[device_count]
 ,rs.[stop_at_mark_name]
 ,rs.[stop_before]
 FROM [msdb].[dbo].[restorehistory] rs
 inner join [msdb].[dbo].[backupset] bs
 on rs.backup_set_id = bs.backup_set_id
 INNER JOIN msdb.dbo.backupmediafamily bmf 
 ON bs.media_set_id = bmf.media_set_id
 GO

PS.RESTORE操作會寫入backupset與backupmediafamily資料表，紀錄還原所使用的備份檔資訊

T-SQL WHILE Loop and CAST

-- DEMO WHILE Loop and CAST
DECLARE @ip1 varchar(15), @ip1start int
SET @ip1 = '10.3.21.'
SET @ip1start = 131

WHILE (@ip1start<255)
BEGIN
--PRINT 'test'
PRINT @ip1 + CAST(@ip1start as varchar(3))

SET @ip1start = @ip1start + 1
END

GO

SQL Server Memory pool(Buffer Pool) - Procedure Cache and Data Cache

-- SQL Server 7.0之前(SQL Server 6.5),Data Cache與Procedure Cache有獨立控制的memory pool
-- SQL Server 7.0與SQL Server 2000則是共用一個memory pool
--
-- 此memory pool即Buffer pool
-- The buffer pool is managed by a process called the lazywriter

--01.Procedure cache (execution plan cache)

select * from master.dbo.syscacheobjects

--DBCC PROCCACHE
--GO

--Free Porcedure Cache
-- Method 1: Free Porcedure Cache by database
--SELECT DB_ID('pubs')
--DBCC FLUSHPROCINDB (5)
--GO
--
-- Method 2 -Free All Porcedure Cache
-- DBCC FREEPROCCACHE
-- GO




-- 02.Data Buffer Cache
-- Get TOP 20 objects in the data cache
-- BUG: DBCC MEMUSAGE Is Not Supported in SQL Server 7.0
--The DBCC MEMUSAGE statement is not supported in SQL Server 7.0. Executing it on servers running heavy loads with large databases may cause the server to stop responding.
-- http://support.microsoft.com/default.aspx?scid=kb;en-us;196629

DBCC MEMUSAGE

-- Free Buffer Cache
-- DBCC DROPCLEANBUFFERS

-- Force all dirty pages to be written to disk
--CHECKPOINT
--GO



-- 03.Other Useful Command - DBCC MEMORYSTATUS
USE master
GO
DBCC MEMORYSTATUS
GO

-- 04.Other Useful Command - DBCC PINTABLE (@db_id,@object_id)
-- 微軟不建議使用以下功能,但仍然可以使用,建議在測試環境才使用
-- keep a table's data pages in memory
-- demo Database named pubs
USE pubs
GO
select DB_ID('pubs')
GO
-- return 5
select OBJECT_ID('dbo.jobs')
GO
-- return 277576027
DBCC PINTABLE (5,277576027)
GO

-- release a pinned table's data pages from memory.
USE pubs
GO
select DB_ID('pubs')
GO
-- return 5
select OBJECT_ID('dbo.jobs')
GO
-- return 277576027
DBCC UNPINTABLE (5,277576027)
GO

[reference]
Analyzing SQL Server 2000 Data Caching
How to Interact with SQL Server's Data and Procedure Cache

跨資料庫存取而不想在別的資料庫額外設定權限EXECUTE AS的用法

最近接到一個需求,需要跨資料庫存取,而不想在別的資料庫額外設定權限,
所以就開始了解一下EXECUTE AS的用法

[注意]執行以下Demo Code需要AdventureWorks資料庫

-- 建立測試資料庫TEST1
CREATE DATABASE [TEST1]
GO

-- 建立測試Login帳戶Brad
CREATE LOGIN [Brad]
WITH PASSWORD = N'P@ssw0rd',
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF,
DEFAULT_DATABASE = [TEST1],
DEFAULT_LANGUAGE = [繁體中文]
GO

-- 建立TEST1對應的User帳戶brad
USE [TEST1]
GO
CREATE USER [Brad]
GO

-- 建立PROCEDURE With EXECUTE AS SELF讀取別的資料庫的資料
CREATE PROCEDURE [usp_GetAdvWorkEmployee]
WITH EXECUTE AS SELF
AS
SELECT SUSER_NAME(), USER_NAME(), COUNT(*)
FROM [AdventureWorks].[HumanResources].[Employee]
GO

-- 給予EXECUTE權限給Brad
GRANT EXECUTE ON [usp_GetAdvWorkEmployee] to [Brad]
GO

-- 從AdventureWork資料庫產生一些資料到TEST1資料庫
SELECT * INTO dbo.Employee FROM AdventureWorks.HumanResources.Employee;
GO

-- 建立一個Procedure JOIN外部資料庫的資料表
CREATE PROCEDURE [usp_EmployeeDetail]
WITH EXECUTE AS SELF
AS
SELECT a.EmployeeID, a.Title, b.AddressID ,b.ModifiedDate
FROM dbo.Employee a LEFT JOIN AdventureWorks.HumanResources.EmployeeAddress b
ON a.EmployeeID=b.EmployeeID
GO

-- 給予EXECUTE權限給Brad
GRANT EXECUTE ON [usp_EmployeeDetail] to [Brad]
GO

-- Create function with EXECUTE AS SELF
USE [TEST1]
GO
CREATE FUNCTION ufn_GetAdvWorkEmployee()
RETURNS @retTable TABLE
(
EmployeeID int,
Title nvarchar(50)
)
WITH EXECUTE AS SELF
AS
BEGIN
INSERT INTO @retTable SELECT EmployeeID,Title FROM [AdventureWorks].[HumanResources].[Employee]
RETURN
END;
GO

-- 給予SELECT權限給Brad
GRANT SELECT ON ufn_GetAdvWorkEmployee to [Brad]
GO

-- 此時使用brad登入到SQL Server執行以下2個Procedure仍會出現錯誤訊息
USE [TEST1]
GO
EXECUTE usp_GetAdvWorkEmployee;
GO
EXECUTE usp_EmployeeDetail;
GO
-- Msg 916, Level 14, State 1, Procedure usp_GetAdvWorkEmployee, Line 4
-- 伺服器主體 "sa" 在目前的安全性內容下無法存取資料庫 "AdventureWorks"。

-- 將測試資料庫的TRUSTWORTHY改為ON
USE MASTER
GO
ALTER DATABASE [TEST1] SET TRUSTWORTHY ON
GO

-- 此時使用brad登入到SQL Server執行以下2個Procedure就可以執行成功
USE [TEST1]
GO
EXECUTE usp_GetAdvWorkEmployee;
GO
EXECUTE usp_EmployeeDetail;
GO

-- 刪除資料庫
USE MASTER
GO
DROP DATABASE [TEST1]
GO
-- 刪除Login登入帳戶
DROP LOGIN [Brad]
GO

Use User-Defined Functon for default constraint使用Function產生自訂的自動編號欄位

開發人員需要每日Import大量資料,想改用SSIS產生package排程執行,但有一個流程目前是用EXCEL完成的,第一個欄位要自訂格式,年月加上流水號(例如200805000001),希望我在SSIS找到解決方案,當我聽到時第一個想法是透過T-SQL User-Defined Function與default contraint來完成這個需求,果然在google上找到類似的寫法,以下是我改寫後的demo code

--STEP1.Create test database
Use master
GO
CREATE DATABASE TESTDB
GO


--STEP2.Create T-SQL User-Defined Function
-- 在Code裡面已經指定要從這個資料表dbo.autoIDTable取得最大的流水號

USE TESTDB
GO
CREATE FUNCTION GetNewAutoID
(
-- Add the parameters for the function here
--@p1 char(4)
)
RETURNS char(12)
AS
BEGIN
-- Declare the return variable here
DECLARE @ResultVar Char(12)
Declare @MaxValue int
Set @MaxValue=0
Select @MaxValue=Cast(Right(autoid,6) as int) from dbo.autoIDTable
Set @MaxValue=@MaxValue +1
Set @ResultVar=LEFT(cast(convert(varchar , GETDATE(), 112) as varchar ),6) + Right(('00000'+Ltrim(str(@maxValue))),6)
-- Return the result of the function
RETURN @ResultVar
END
GO

--STEP3.Create dbo.autoIDTable Table use default constraint
Create table dbo.autoIDTable
(
autoid char(12) default dbo.GetNewAutoID(),
Data varchar(50)
);

-- STEP4.Try to select fucntion dbo.GetNewAutoID()
SELECT dbo.GetNewAutoID()

-- STEP5.Insert test data
insert into dbo.autoIDTable(Data) values('haha')
insert into dbo.autoIDTable(Data) values('wawa')
insert into dbo.autoIDTable(Data) values('gaga')

-- STEP6.select dbo.autoIDTable
select * from dbo.autoIDTable

-- STEP7.drop test database
USE master
GO
DROP DATABASE TESTDB
GO

使用一句SQL INSERT多筆Record(multiple values)

使用一句SQL INSERT多筆Record(multiple values)

此功能在MySQL在3.22.5之後就有的功能,SQL Server在這個SQL Server 2008版本才加入此功能

-- 切換測試資料庫
USE MyDB
GO

-- 建一個測試資料表
CREATE TABLE [mytable]
(
myid 
nvarchar(10)
,givenName 
nvarchar(50)
,email 
nvarchar(50)
);
GO

-- 一次Insert 多筆資料
INSERT INTO [mytable]
VALUES
('01','Brad','brad@test.com')
,('02','Siliva','siliva@test.com')
,('03','Allen','Allen@test.com');
GO

-- 檢查資料是否正確寫入
SELECT * FROM [mytable];

SQL Server CURSOR

-- DEMO CURSOR
DECLARE @ContactName nvarchar(30)
DECLARE CUR CURSOR FOR
SELECT ContactName FROM Northwind.dbo.Customers

OPEN CUR
FETCH CUR INTO @ContactName

WHILE (@@FETCH_STATUS=0)
BEGIN

PRINT @ContactName

FETCH CUR INTO @ContactName
END

CLOSE CUR
DEALLOCATE CUR

GO

PAGE and EXTENT

-- EXTENT
DBCC EXTENTINFO

-- PAGE
DBCC PAGE( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])

The filenum and pagenum parameters are taken from the page IDs that come from various system tables and appear in DBCC or other system error messages. A page ID of, say, (1:354) has filenum = 1 and pagenum = 354.

T-SQL 資料表新增,修改與約束條件Create/Alter table ,Constraint

-- 新增資料表
CREATE TABLE Table01
(
Column1 int,
Column2 varchar(10)
);
GO

-- 新增一個一般欄位
ALTER TABLE dbo.Table01
ADD Column3 varchar(20);
GO

-- 新增一個計算欄位
ALTER TABLE dbo.Table01
ADD Column4 AS (Left(column02,5));
GO

-- 修欄位名稱
EXEC sp_rename 'dbo.Table01.Column3', 'Column333','column'

-- 修改欄位大小
ALTER TABLE dbo.Table01
ALTER COLUMN column333 varchar(50);

-- 移除一個欄位
ALTER TABLE dbo.Table01
DROP COLUMN column333 ;

使用T-SQL設定Primary Key

USE Northwind
GO

CREAT TABLE [myTable]
(
id int not NULL,
givenName varchar(50)
)

-- Create Primary Key 並放在另一個FileGroup
ALTER TABLE [myTable]
ADD CONSTRAINT [PK_TBL]
PRIMARY KEY CLUSTERED([id])
on [IDX]


[Reference]
-- 建立或移除Primary Key節至 SQL Server 2005 線上叢書 (2007 年 9 月)
http://msdn.microsoft.com/zh-tw/library/ms190273.aspx

M. 建立含有索引選項的 PRIMARY KEY 條件約束
下列範例會建立 PRIMARY KEY 條件約束 PK_TransactionHistoryArchive_TransactionID，並設定選項 FILLFACTOR、ONLINE 及 PAD_INDEX。產生的叢集索引將與條件約束同名。

USE AdventureWorks;
GO

ALTER TABLE Production.TransactionHistoryArchive WITH NOCHECK
ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID)
WITH (FILLFACTOR = 75, ONLINE = ON, PAD_INDEX = ON)
GO

N. 在 ONLINE 模式中卸除 PRIMARY KEY 條件約束
下列範例會刪除 PRIMARY KEY 條件約束，並將 ONLINE 選項設為 ON。
USE AdventureWorks;
GO
ALTER TABLE Production.TransactionHistoryArchive
DROP CONSTRAINT PK_TransactionHistoryArchive_TransactionID
WITH (ONLINE = ON);
GO