SQL的XML Select for (SQL2000/SQL2005)

參考網站：連結

SQL 2000+

-- 預設用XML attribute儲存欄位資料的XML顯示

SELECT

       employee_id,

       employee_name,

       email,dept_no

FROM

       emp_reference

FOR XML AUTO -- 輸出為XML

結果：

<emp_reference>

  <employee_id>080001</employee_id>

  <employee_name>白ＸＸ</employee_name>

  <email>hoooo</email>

  <dept_no>PA99</dept_no>

</emp_reference>

<emp_reference>

  <employee_id>081002</employee_id>

  <employee_name>歐ＸＸ</employee_name>

  <email>woooo</email>

  <dept_no>PP88</dept_no>

</emp_reference>

<emp_reference>

  <employee_id>082003</employee_id>

  <employee_name>唐ＸＸ</employee_name>

  <email>sooooo</email>

  <dept_no>BC40</dept_no>

</emp_reference>

-- 改以子元素儲存欄位資料的XML顯示

SELECT

       employee_id,

       employee_name,

       email,dept_no

FROM

       emp_reference

FOR XML AUTO, ELEMENTS -- 輸出為XML且以子元素方式顯示

結果：

<emp_reference employee_id="080001" employee_name="白ＸＸ" email="hoooo" dept_no="PA99" />

<emp_reference employee_id="081002" employee_name="歐ＸＸ" email="woooo" dept_no="PP88" />

<emp_reference employee_id="082003" employee_name="唐ＸＸ" email="sooooo" dept_no="BC40" />

SQL 2005+

SELECT

       employee_id,

       employee_name,

       email,dept_no

FROM

       emp_reference

-- TYPE:將XML結果轉為SQL的XML資料型態

-- ROOT:XML結果放入此Root Element中

FOR XML AUTO,TYPE,ROOT('emp_list')

結果：

<emp_list>

  <emp_reference employee_id="080001" employee_name="白ＸＸ" email="hoooo" dept_no="PA99" />

  <emp_reference employee_id="081002" employee_name="歐ＸＸ" email="woooo" dept_no="PP88" />

  <emp_reference employee_id="082003" employee_name="唐ＸＸ" email="sooooo" dept_no="BC40" />

</emp_list>

SQL2005 - 建立資料庫(Create Database)說明

參考網站：連結

-- CREATE 一個資料庫名稱為AMIS

CREATE DATABASE [AMIS] ON  PRIMARY (

       -- 資料檔名稱

       NAME = N'AMIS',

       -- 資料檔儲存位置

       FILENAME = N'D:\MSSQL\DATA\AMIS.mdf' ,

       -- 初始資料庫檔大小

       SIZE = 3072KB ,

       -- 資料庫成長大小，空間不足時，每次擴充MB, 也可以用百分比FILEGROWTH = 10%

       FILEGROWTH = 102400KB

)

LOG ON (

       -- LOG檔名稱

       NAME = N'AMIS_log',

       -- LOG檔儲存位置

       FILENAME = N'D:\MSSQL\DATA\AMIS_log.ldf' ,

       -- 初始LOG檔大小

       SIZE = 1024KB ,

       -- LOG檔成長上限為MB

       MAXSIZE = 1048576KB ,

       -- LOG檔成長大小MB

       FILEGROWTH = 10240KB )

GO

-- 設定資料庫版本為(SQL2005), 80(SQL2000)

EXEC dbo.sp_dbcmptlevel @dbname=N'AMIS', @new_cmptlevel=90

GO

-- 檢查是否有安裝全文索引模組

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))

begin

       -- 關閉此資料庫全文索引功能

       EXEC [AMIS].[dbo].[sp_fulltext_database] @action = 'disable'

end

GO

-- 是否允許：未指定NOT NULL/NULL的Column預設為NULL

ALTER DATABASE [AMIS] SET ANSI_NULL_DEFAULT OFF

GO

-- ANSI_NULLS為ON時，不支援非標準語法：WHERE條件內用Column=NULL / Column<>NULL回傳皆無任何資料

-- ANSI_NULLS為OFF時，上述語法會依照欄位值實際內容進行判斷

ALTER DATABASE [AMIS] SET ANSI_NULLS OFF

GO

-- ANSI_PADDING未來強制為ON，OFF會發生錯誤

-- ANSI_PADDING為ON時，影響欄位的差異：

--         varchar(n)資料末端空白不會裁切

--         varbinary(n)資料末端不會裁切

-- ANSI_PADDING為OFF時，影響欄位的差異：

--         varchar(n)資料末端空白會裁切掉

--         varbinary(n)資料末端會裁切掉

ALTER DATABASE [AMIS] SET ANSI_PADDING OFF

GO

-- ANSI_WARNINGS設定為ON時：

--   SUM、AVG、MAX、MIN、STDEV、STDEVP、VAR、VARP 或COUNT中出現NULL值時會警告

--   除以零和算術溢位錯誤會造成陳述式的回復，且會產生錯誤訊息

--         超過varchar(n)/binary(n)資料新增或更新會產生錯誤訊息

-- ANSI_WARNINGS設定為OFF時：

--   SUM、AVG、MAX、MIN、STDEV、STDEVP、VAR、VARP 或COUNT中出現NULL值時不會警告

--   除以零和算術溢位錯誤時，會回傳NULL

--         超過varchar(n)/binary(n)資料新增或更新時會裁切資料

ALTER DATABASE [AMIS] SET ANSI_WARNINGS OFF

GO

-- 在查詢執行期間，當發生溢位或除以零的錯誤時，是否結束查詢

ALTER DATABASE [AMIS] SET ARITHABORT OFF

GO

-- 當設定為ON 時，資料庫會完全關閉，並在最後一個使用者結束之後釋放其資源。當使用者試圖重新使用資料庫時，會自動重新開啟資料庫。

-- 當設定為OFF 時，則在最後一個使用者結束之後資料庫仍為開啟。

ALTER DATABASE [AMIS] SET AUTO_CLOSE OFF

GO

-- 當設定為ON 時，統計資料會自動建立在使用述詞的資料行上。

-- 當設為OFF 時，不會自動建立統計資料；相反地，此時可以手動建立統計資料。

ALTER DATABASE [AMIS] SET AUTO_CREATE_STATISTICS ON

GO

-- 若設成ON，資料庫檔案會被定期縮減。資料檔和記錄檔都可以由SQL Server 自動壓縮。只有在資料庫設為SIMPLE 復原模式或備份記錄時，AUTO_SHRINK 才會縮減交易記錄的大小。

-- 當設定為OFF 時，不會在定期檢查未使用空間時自動縮減資料庫檔案。

ALTER DATABASE [AMIS] SET AUTO_SHRINK OFF

GO

-- 當設定為ON 時，在最佳化時查詢所需的任何遺漏的統計資料，都會在查詢最佳化時自動建立。

-- 當設定為OFF 時，則必須手動建立統計資料。

ALTER DATABASE [AMIS] SET AUTO_UPDATE_STATISTICS ON

GO

-- 若設定為ON 時，則在認可或回復交易時，將會關閉任何開啟的資料指標。

-- 當設定為OFF 時，在認可交易時資料指標仍然是開啟的，但回復交易則會關閉所有資料指標，除了那些定義為INSENSITIVE 或STATIC 的資料指標。

ALTER DATABASE [AMIS] SET CURSOR_CLOSE_ON_COMMIT OFF

GO

-- 當指定LOCAL，且資料指標並未在建立時定義為GLOBAL，則資料指標的範圍僅限於資料指標建立時所在之批次、預存程序或觸發程序的本機範圍。資料指標名稱只在這個範圍內有效。

-- 當指定GLOBAL，且資料指標並未在建立時定義為LOCAL，則資料指標的範圍便是連接的全域範圍。連接所執行的任何預存程序或批次都可以參考資料指標名稱。

ALTER DATABASE [AMIS] SET CURSOR_DEFAULT  GLOBAL

GO

-- 當指定ON 時，則在任一個運算元為NULL 時，串連運算的結果為NULL。

-- 當指定OFF 時，會將Null 值當做空字元字串來處理。

ALTER DATABASE [AMIS] SET CONCAT_NULL_YIELDS_NULL OFF

GO

-- 當指定ON 時，則在運算式中遺失有效位數時，就會產生錯誤。

-- 當指定OFF 時，損失有效位數並不會產生錯誤訊息，並且將結果進位成儲存該結果之資料行或變數的有效位數。

ALTER DATABASE [AMIS] SET NUMERIC_ROUNDABORT OFF

GO

-- 當指定ON 時，則可用雙引號來括住分隔識別碼。

-- 當指定OFF 時，無法將識別碼括在引號中，且必須遵循所有的Transact-SQL 識別碼規則。

ALTER DATABASE [AMIS] SET QUOTED_IDENTIFIER OFF

GO

-- 當指定ON 時，可以對AFTER 觸發程序進行遞迴引發。

-- 當指定OFF 時，僅無法對AFTER 觸發程序進行直接遞迴引發。

ALTER DATABASE [AMIS] SET RECURSIVE_TRIGGERS OFF

GO

-- 當此選項設定為ON 時，查詢不會在編譯前等待統計資料更新。而是，將逾時統計資料放置在查詢中，以利工作者執行緒在背景處理序中更新。

ALTER DATABASE [AMIS] SET AUTO_UPDATE_STATISTICS_ASYNC OFF

GO

-- 當指定ON 時，SQL Server 會維護資料庫中任兩個資料表之間的交互關聯統計資料，該資料庫是由FOREIGN KEY 條件約束所連結並具有datetime 資料行。

-- 當指定OFF 時，則不會維謢交互關聯統計資料。

ALTER DATABASE [AMIS] SET DATE_CORRELATION_OPTIMIZATION OFF

GO

-- 當指定SIMPLE 時，將會根據資料庫的預設行為將查詢參數化

-- 當指定FORCED 時，SQL Server 會將資料庫中的所有查詢參數化。

ALTER DATABASE [AMIS] SET PARAMETERIZATION SIMPLE

GO

-- 當指定READ_ONLY 時，使用者將只能讀取資料庫的資料，而不能修改它。

-- 當指定READ_WRITE 時，將可對資料庫進行讀取和寫入作業。

ALTER DATABASE [AMIS] SET READ_WRITE

GO

-- 當指定FULL 時，將使用交易記錄備份提供媒體失敗後的完整復原模式。如果資料檔損毀，媒體復原可以還原所有已認可的交易。

-- 當指定BULK_LOGGED 時，媒體失敗後的後原是針對某些提供的大範圍或大量作業，結合最佳效能以及最少的記錄空間用量。

-- 當指定SIMPLE 時，就會提供簡單的備份策略，即使用最少的記錄空間。

ALTER DATABASE [AMIS] SET RECOVERY FULL

GO

-- 當指定SINGLE_USER 時，每次只有一個使用者可以連接到資料庫。所有其他的使用者連接都會中斷。

-- 當指定RESTRICTED_USER 時，只允許db_owner 固定資料庫角色與資料庫建立者(dbcreator) 及系統管理員(sysadmin) 固定伺服器角色的成員連接到資料庫，但它並未限制他們的數目。

-- 當指定MULTI_USER 時，可讓所有具有適當權限的使用者都連接到允許的資料庫。

ALTER DATABASE [AMIS] SET MULTI_USER

GO

-- 當指定CHECKSUM 時，Database Engine會針對整頁的內容計算總和檢查碼，並在將頁面寫入磁碟時，於頁首中儲存值。從磁碟讀取頁面時，會重新計算總和檢查碼，並與頁首所儲存的總和檢查碼值作比較。

-- 當指定TORN_PAGE_DETECTION，會在頁面寫入磁碟時，將8 KB 的資料庫頁面中的每512 位元組磁區之特定位元儲存至資料庫頁首中。當從磁碟中讀取頁面時，會比較頁面標頭所儲存的損毀位元和實際的頁面磁區資訊。

-- 當指定NONE 時，資料庫頁面將不會產生CHECKSUM 或TORN_PAGE_DETECTION 值。SQL Server 將不會在讀取時驗證總和檢查碼或損毀頁面，即使在頁首中有CHECKSUM 或TORN_PAGE_DETECTION 值也是如此。

ALTER DATABASE [AMIS] SET PAGE_VERIFY CHECKSUM 

GO

USE [AMIS]

GO

-- 預設檔案群組設定為PRIMARY

IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY')ALTER DATABASE [AMIS] MODIFY FILEGROUP [PRIMARY] DEFAULT

GO

SQL2005 - 建立資料表(Create Table)說明

參考網址：連結

-- 是否允許用雙引號(")來表示物件名稱(允許用保留字),現在都用[]來使用，"不是很必須了

SET QUOTED_IDENTIFIER ON

GO

-- 建立dbo.emp_reference 資料表

CREATE TABLE [dbo].[emp_reference](

    [emp_id] [varchar](10) NOT NULL,

    [emp_name] [varchar](20) NOT NULL,

    [dept_no] [varchar](10) NOT NULL,

    [email] [varchar](20) NULL,

    [is_active] [varchar](1) NULL,

    [uog] [varchar](50) NULL,

    [sdept_no] [varchar](10) NULL,

    [ext] [varchar](20) NULL

-- CONSTRAINT

--   這是一個選擇性的關鍵字，用來指示開始定義下列條件約束：

--   1. PRIMARY KEY - 指出哪個資料行或資料行集合的值可以唯一識別資料表中的資料列

--   2. NOT NULL - 指定column不允許null

--   3. UNIQUE - 強制一組資料行中數值的唯一性

--   4. FOREIGN KEY - 識別和強制資料表之間的關係，可以設定刪除時連動關係

--   5. CHECK - 檢查搜尋條件成立才允許儲存, EX: CONSTRAINT CHK_EMP_ID CHECK (EMP_ID BETWEEN '000000' and '999999')

-- CLUSTERED/NONCLUSTERED。

--   指出針對PRIMARY KEY 或UNIQUE 條件約束建立叢集或非叢集索引。

--   PRIMARY KEY 條件約束預設為CLUSTERED，UNIQUE 條件約束預設為NONCLUSTERED。

--   在CREATE TABLE 陳述式中，您只能將CLUSTERED 指定給單一條件約束。

--   如果UNIQUE 條件約束指定了CLUSTERED，且也指定了PRIMARY KEY 條件約束，PRIMARY KEY 便預設為NONCLUSTERED。

    CONSTRAINT [PK_EMP_REFERENCE] PRIMARY KEY CLUSTERED (

       [emp_id] ASC

    )

    WITH (

       IGNORE_DUP_KEY = OFF

    )

    ON [PRIMARY],

    CONSTRAINT [IX_EMP_REFERENCE] UNIQUE NONCLUSTERED (

       [email] ASC

    )

    WITH (

       IGNORE_DUP_KEY = OFF

    )

    ON [PRIMARY]

)

ON [PRIMARY]

SQL Server型態與C# Mapping (SQL2000/SQL2005/SQL2008/SQL2012)

下列整理從SQL2000~SQL2012的資料型態與C#對應關係：
需下載我放再協作平台上面此文章整理時產出的Excel檔案，請到這邊下載：連結

SQL2008~SQL2012	SQL2005	SQL2000	C#資料型態
bigint	bigint	bigint	Int64, Nullable
binary	binary	binary	Byte[]
bit	bit	bit	Boolean, Nullable
char	char	char	None
cursor	cursor	cursor	None
date			DateTime, Nullable
datetime	datetime	datetime	DateTime, Nullable
datetime2			DateTime, Nullable
DATETIMEOFFSET			DateTimeOffset, Nullable
decimal	decimal	decimal	Decimal, Nullable
float	float	float	Double, Nullable
geography			None
geometry			None
hierarchyid			None
image	image	image	None
int	int	int	Int32, Nullable
money	money	money	Decimal, Nullable
nchar	nchar	nchar	String, Char[]
ntext	ntext	ntext	None
numeric	numeric	numeric	Decimal, Nullable
nvarchar	nvarchar	nvarchar	String, Char[]
nvarchar(1), nchar(1)	nvarchar(1), nchar(1)	nvarchar(1), nchar(1)	Char, String, Char[], Nullable
real	real	real	Single, Nullable
rowversion	rowversion	rowversion	Byte[]
smallint	smalldatetime	smalldatetime	Int16, Nullable
smallmoney	smallint	smallint	Decimal, Nullable
sql_variant	smallmoney	smallmoney	Object
table	sql_variant	sql_variant	None
text	table	table	None
time	text	text	TimeSpan, Nullable
timestamp	timestamp	timestamp	None
tinyint	tinyint	tinyint	Byte, Nullable
uniqueidentifier	uniqueidentifier	uniqueidentifier	Guid, Nullable
User-defined type(UDT)	User-defined type(UDT)	User-defined type(UDT)	The same class that is bound to the user-defined type in the same assembly or a dependent assembly.
varbinary	varbinary	varbinary	Byte[]
varbinary(1), binary(1)	varbinary(1), binary(1)	varbinary(1), binary(1)	byte, Byte[], Nullable
varchar	varchar	varchar	None
xml	xml		None

SQL Server發送Mail (2005+)

只支援SQL2005以後版本

EXEC msdb.dbo.sp_send_dbmail @recipients='sheauren@gmail.com',
    @subject = 'SUBJECT',
    @body = @HtmlBody,
    @body_format = 'HTML',
    @profile_name='DefaultSMTPMail'; 
-- DefaultSMTPMail需要先定義好,發送信件依賴此設定.

附件大小調整至5M

EXECUTE msdb.dbo.sysmail_configure_sp 'MaxFileSize', '5242880'

查詢SQL2005/2008支援的資料型態

SQL Server 2005之後可以利用SQL語法查詢資料型態：

select name,max_length,precision,scale,is_nullable from sys.types

SQL 2008查詢結果，34個預設型態：

SQL 2005查詢結果，27個預設型態：

SQL Server 註冊Unsafe組件失敗的解決方法

ALTER DATABASE DBNAME SET TRUSTWORTHY ON

安裝SQL Server常見Issue

錯誤位置(下列兩欄位數值與安裝時語言版本所參考的Counter/Help不相同)時，需手動修改下列兩個數值.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib\Last Counter
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib\Last Help


參考 Counter/Help
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib\004 (中文)
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib\009 (英文)

SQL Server 2005+不允許執行SQL CLR的解決方法

-- 開啟SQL CLR
EXEC sp_configure 'clr enabled', '1';
GO
RECONFIGURE;
GO

安裝SQL Server常見Issue

錯誤位置(下列兩欄位數值與安裝時語言版本所參考的Counter/Help不相同)時，需手動修改下列兩個數值.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib\Last Counter
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib\Last Help


參考 Counter/Help
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib\004 (中文)

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib\009 (英文)

SQL Server - 註冊Unsafe組件失敗

ALTER DATABASE DBNAME SET TRUSTWORTHY ON

SQL Server - 當警告不允許執行SQL CLR...

-- 開啟SQL CLR

EXEC sp_configure 'clr enabled', '1'; 

GO 

RECONFIGURE; 

GO 

SQL Server - 利用關鍵字尋找SQL物件

在Sustain系統時,找不到特定SQL物件用在哪裡的時候,可以利用下列語法去找.

declare @keyword varchar(100)

set @keyword='%我要找的關鍵字%'

使用到@keyword的所有SQL元件

• SQL SERVER 2005

-- 欄位名稱有符合關鍵字模糊比對之table列表

SELECT * FROM sys.tables where object_id in (

SELECT object_id FROM sys.all_columns where name like @keyword

) order by name

-- 欄位名稱有符合關鍵字模糊比對之view列表

SELECT * FROM sys.views where obj ect_id in (

SELECT object_id FROM sys.all_columns where name like @keyword

) order by name

-- sql語法有符合關鍵字模糊比對之物件列表(StoreProcedure/Function ...)

select * from sys.all_objects  where object_id in (

select object_id from sys.all_sql_modules where definition like @keyword

)

• SQL SERVER 2000

-- 取得DB與@keyword有關聯性之object name/sql語法

select o.[name],c.[text] from syscomments c left join sysobjects o on o.id=c.id 

where c.id in (

select distinct id from sysdepends where depid in (

select id from sysobjects where [name] like @keyword

)

) 

-- 取得sql語法中具備@keyword關鍵字之所有物件

select o.[name],c.[text] from syscomments c left join sysobjects o on o.id=c.id

where c.[text]  like @keyword

SQL Server 指令 - Delete Transaction Log

Ⅰ、提供關於所有資料庫中交易記錄檔空間使用狀況的統計資料

DBCC SQLPERF ( LOGSPACE )

Ⅱ、截斷交易記錄檔

BACKUP LOG DBNAME WITH TRUNCATE_ONLY 

Ⅲ、壓縮指定資料庫中資料檔的大小

DBCC SHRINKDATABASE (DBNAME ) 

SQL Server - Send SQL Mail

只支援SQL2005以後版本

EXEC msdb.dbo.sp_send_dbmail @recipients='sheauren@gmail.com',

    @subject = 'SUBJECT',

    @body = @HtmlBody,

    @body_format = 'HTML',

    @profile_name='DefaultSMTPMail'; 

-- DefaultSMTPMail需要先定義好,發送信件依賴此設定.

附件大小調整至5M

EXECUTE msdb.dbo.sysmail_configure_sp 'MaxFileSize', '5242880'

SQL Server - Write to EvenLog

RAISERROR('TEST',10,1) WITH LOG

詳細方法:

http://msdn.microsoft.com/en-us/library/ms178592(v=SQL.90).aspx