DECLARE DEL_CUR CURSOR FOR 改為
DECLARE DEL_CUR CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR
DECLARE DEL_CUR CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR
2016/11/2
after trigger and before trigger + cursor
ALTER TRIGGER [dbo].[insertTrigger]
ON [dbo].[PBB]
INSTEAD of insert
AS
BEGIN
declare @id as int
declare @yeartext as int
declare @monthtext as int
declare @timedate as int
declare @timedate_text as varchar(50)
declare @itemtext as varchar(50)
declare @formulatext as varchar(50)
declare @region as varchar(50)
declare @country as varchar(50)
declare @asp as varchar(50)
declare @category as varchar(50)
declare @model as varchar(50)
declare @sam as varchar(50)
declare @ntd_amount as float
declare @inumber as int
declare @timedate_start as varchar(50)
declare @timedate_end as varchar(50)
DECLARE @MyCursor CURSOR
SET @MyCursor = CURSOR FAST_FORWARD
FOR
select id,yeartext,monthtext,timedate,timedate_text,itemtext,formulatext,region,country,asp,category,model,sam,ntd_amount,inumber,timedate_start,timedate_end from inserted
OPEN @MyCursor
FETCH NEXT FROM @MyCursor into @id,@yeartext,@monthtext,@timedate,@timedate_text,@itemtext,@formulatext,@region,@country,@asp,@category,@model,@sam,@ntd_amount,@inumber,@timedate_start,@timedate_end
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO [dbo].[PBB] ([YearText],[MonthText],[TimeDate],[TimeDate_Text],[ItemText],[FormulaText],[Region],[Country],[ASP],[Category],[Model],[SAM],[iNumber],[TimeDate_Start],[TimeDate_End])
SELECT week_year,week_month,cast((cast(week_year as varchar(10))+right(('0'+cast(week_month as varchar(10))),2)) as int)
,(cast(week_year as varchar(10))+'M'+right(('0'+cast(week_month as varchar(10))),2))
,'A','Purchase amt',@region,@country,@asp,@category,@model,@sam,@inumber,@timedate_start,@timedate_end
FROM htccsddc.[CSD_DB].[dbo].[Calender]
where (cast(week_year as varchar(10))+right(('0'+cast(week_month as varchar(10))),2)>=@TimeDate_Start)
and (cast(week_year as varchar(10))+right(('0'+cast(week_month as varchar(10))),2)<=@TimeDate_End)
group by week_year,week_month
order by week_year,week_month
INSERT INTO [dbo].[PBB] ([YearText],[MonthText],[TimeDate],[TimeDate_Text],[ItemText],[FormulaText],[Region],[Country],[ASP],[Category],[Model],[SAM],[iNumber],[TimeDate_Start],[TimeDate_End])
SELECT week_year,week_month,cast((cast(week_year as varchar(10))+right(('0'+cast(week_month as varchar(10))),2)) as int)
,(cast(week_year as varchar(10))+'M'+right(('0'+cast(week_month as varchar(10))),2))
,'B','buy back amt',@region,@country,@asp,@category,@model,@sam,@inumber,@timedate_start,@timedate_end
FROM htccsddc.[CSD_DB].[dbo].[Calender]
where (cast(week_year as varchar(10))+right(('0'+cast(week_month as varchar(10))),2)>=@TimeDate_Start)
and (cast(week_year as varchar(10))+right(('0'+cast(week_month as varchar(10))),2)<=@TimeDate_End)
group by week_year,week_month
order by week_year,week_month
INSERT INTO [dbo].[PBB] ([YearText],[MonthText],[TimeDate],[TimeDate_Text],[ItemText],[FormulaText],[Region],[Country],[ASP],[Category],[Model],[SAM],[iNumber],[TimeDate_Start],[TimeDate_End])
SELECT week_year,week_month,cast((cast(week_year as varchar(10))+right(('0'+cast(week_month as varchar(10))),2)) as int)
,(cast(week_year as varchar(10))+'M'+right(('0'+cast(week_month as varchar(10))),2))
,'C','buy back %',@region,@country,@asp,@category,@model,@sam,@inumber,@timedate_start,@timedate_end
FROM htccsddc.[CSD_DB].[dbo].[Calender]
where (cast(week_year as varchar(10))+right(('0'+cast(week_month as varchar(10))),2)>=@TimeDate_Start)
and (cast(week_year as varchar(10))+right(('0'+cast(week_month as varchar(10))),2)<=@TimeDate_End)
group by week_year,week_month
order by week_year,week_month
INSERT INTO [dbo].[PBB] ([YearText],[MonthText],[TimeDate],[TimeDate_Text],[ItemText],[FormulaText],[Region],[Country],[ASP],[Category],[Model],[SAM],[iNumber],[TimeDate_Start],[TimeDate_End])
SELECT week_year,week_month,cast((cast(week_year as varchar(10))+right(('0'+cast(week_month as varchar(10))),2)) as int)
,(cast(week_year as varchar(10))+'M'+right(('0'+cast(week_month as varchar(10))),2))
,'D','Avg. Purchase amt of Past Selected Period',@region,@country,@asp,@category,@model,@sam,@inumber,@timedate_start,@timedate_end
FROM htccsddc.[CSD_DB].[dbo].[Calender]
where (cast(week_year as varchar(10))+right(('0'+cast(week_month as varchar(10))),2)>=@TimeDate_Start)
and (cast(week_year as varchar(10))+right(('0'+cast(week_month as varchar(10))),2)<=@TimeDate_End)
group by week_year,week_month
order by week_year,week_month
--end
FETCH NEXT FROM @MyCursor
into @id,@yeartext,@monthtext,@timedate,@timedate_text,@itemtext,@formulatext,@region,@country,@asp,@category,@model,@sam,@ntd_amount,@inumber,@timedate_start,@timedate_end
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
END
ON [dbo].[PBB]
INSTEAD of insert
AS
BEGIN
declare @id as int
declare @yeartext as int
declare @monthtext as int
declare @timedate as int
declare @timedate_text as varchar(50)
declare @itemtext as varchar(50)
declare @formulatext as varchar(50)
declare @region as varchar(50)
declare @country as varchar(50)
declare @asp as varchar(50)
declare @category as varchar(50)
declare @model as varchar(50)
declare @sam as varchar(50)
declare @ntd_amount as float
declare @inumber as int
declare @timedate_start as varchar(50)
declare @timedate_end as varchar(50)
DECLARE @MyCursor CURSOR
SET @MyCursor = CURSOR FAST_FORWARD
FOR
select id,yeartext,monthtext,timedate,timedate_text,itemtext,formulatext,region,country,asp,category,model,sam,ntd_amount,inumber,timedate_start,timedate_end from inserted
OPEN @MyCursor
FETCH NEXT FROM @MyCursor into @id,@yeartext,@monthtext,@timedate,@timedate_text,@itemtext,@formulatext,@region,@country,@asp,@category,@model,@sam,@ntd_amount,@inumber,@timedate_start,@timedate_end
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO [dbo].[PBB] ([YearText],[MonthText],[TimeDate],[TimeDate_Text],[ItemText],[FormulaText],[Region],[Country],[ASP],[Category],[Model],[SAM],[iNumber],[TimeDate_Start],[TimeDate_End])
SELECT week_year,week_month,cast((cast(week_year as varchar(10))+right(('0'+cast(week_month as varchar(10))),2)) as int)
,(cast(week_year as varchar(10))+'M'+right(('0'+cast(week_month as varchar(10))),2))
,'A','Purchase amt',@region,@country,@asp,@category,@model,@sam,@inumber,@timedate_start,@timedate_end
FROM htccsddc.[CSD_DB].[dbo].[Calender]
where (cast(week_year as varchar(10))+right(('0'+cast(week_month as varchar(10))),2)>=@TimeDate_Start)
and (cast(week_year as varchar(10))+right(('0'+cast(week_month as varchar(10))),2)<=@TimeDate_End)
group by week_year,week_month
order by week_year,week_month
INSERT INTO [dbo].[PBB] ([YearText],[MonthText],[TimeDate],[TimeDate_Text],[ItemText],[FormulaText],[Region],[Country],[ASP],[Category],[Model],[SAM],[iNumber],[TimeDate_Start],[TimeDate_End])
SELECT week_year,week_month,cast((cast(week_year as varchar(10))+right(('0'+cast(week_month as varchar(10))),2)) as int)
,(cast(week_year as varchar(10))+'M'+right(('0'+cast(week_month as varchar(10))),2))
,'B','buy back amt',@region,@country,@asp,@category,@model,@sam,@inumber,@timedate_start,@timedate_end
FROM htccsddc.[CSD_DB].[dbo].[Calender]
where (cast(week_year as varchar(10))+right(('0'+cast(week_month as varchar(10))),2)>=@TimeDate_Start)
and (cast(week_year as varchar(10))+right(('0'+cast(week_month as varchar(10))),2)<=@TimeDate_End)
group by week_year,week_month
order by week_year,week_month
INSERT INTO [dbo].[PBB] ([YearText],[MonthText],[TimeDate],[TimeDate_Text],[ItemText],[FormulaText],[Region],[Country],[ASP],[Category],[Model],[SAM],[iNumber],[TimeDate_Start],[TimeDate_End])
SELECT week_year,week_month,cast((cast(week_year as varchar(10))+right(('0'+cast(week_month as varchar(10))),2)) as int)
,(cast(week_year as varchar(10))+'M'+right(('0'+cast(week_month as varchar(10))),2))
,'C','buy back %',@region,@country,@asp,@category,@model,@sam,@inumber,@timedate_start,@timedate_end
FROM htccsddc.[CSD_DB].[dbo].[Calender]
where (cast(week_year as varchar(10))+right(('0'+cast(week_month as varchar(10))),2)>=@TimeDate_Start)
and (cast(week_year as varchar(10))+right(('0'+cast(week_month as varchar(10))),2)<=@TimeDate_End)
group by week_year,week_month
order by week_year,week_month
INSERT INTO [dbo].[PBB] ([YearText],[MonthText],[TimeDate],[TimeDate_Text],[ItemText],[FormulaText],[Region],[Country],[ASP],[Category],[Model],[SAM],[iNumber],[TimeDate_Start],[TimeDate_End])
SELECT week_year,week_month,cast((cast(week_year as varchar(10))+right(('0'+cast(week_month as varchar(10))),2)) as int)
,(cast(week_year as varchar(10))+'M'+right(('0'+cast(week_month as varchar(10))),2))
,'D','Avg. Purchase amt of Past Selected Period',@region,@country,@asp,@category,@model,@sam,@inumber,@timedate_start,@timedate_end
FROM htccsddc.[CSD_DB].[dbo].[Calender]
where (cast(week_year as varchar(10))+right(('0'+cast(week_month as varchar(10))),2)>=@TimeDate_Start)
and (cast(week_year as varchar(10))+right(('0'+cast(week_month as varchar(10))),2)<=@TimeDate_End)
group by week_year,week_month
order by week_year,week_month
--end
FETCH NEXT FROM @MyCursor
into @id,@yeartext,@monthtext,@timedate,@timedate_text,@itemtext,@formulatext,@region,@country,@asp,@category,@model,@sam,@ntd_amount,@inumber,@timedate_start,@timedate_end
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
END
declare @sql as nvarchar(max) execute sp_executesql @sql
declare @sql as nvarchar(max)
set @sql =
N'INSERT INTO [dbo].[PBB] ('+@Groupby+',[iNumber],[TimeDate_Start],[TimeDate_End])
SELECT distinct '+@Groupby+','+quotename(@MonthCalType,'''')+' as iNumber,'+quotename(@TimeDate_Start,'''')+' as TimeDate_Start,'+quotename(@TimeDate_End,'''')+' as TimeDate_End
FROM #temp1 where timedate>='+quotename(@TimeDate_Start,'''')+' and timedate<='+quotename(@timedate_end,'''')
execute sp_executesql @sql
set @sql =
N'INSERT INTO [dbo].[PBB] ('+@Groupby+',[iNumber],[TimeDate_Start],[TimeDate_End])
SELECT distinct '+@Groupby+','+quotename(@MonthCalType,'''')+' as iNumber,'+quotename(@TimeDate_Start,'''')+' as TimeDate_Start,'+quotename(@TimeDate_End,'''')+' as TimeDate_End
FROM #temp1 where timedate>='+quotename(@TimeDate_Start,'''')+' and timedate<='+quotename(@timedate_end,'''')
execute sp_executesql @sql
2016/10/11
使用 EXCEPT 與 INTERSECT 來比對兩個資料表中的資料
--使用 EXCEPT 來比對兩個資料表的資料
SELECT * FROM DBO.名稱清單
EXCEPT
SELECT * FROM DBO.名稱清單_BAK
SELECT * FROM DBO.名稱清單
EXCEPT
SELECT * FROM DBO.名稱清單_BAK
--使用 INTERSECT 來比對兩個資料表的資料
SELECT * FROM DBO.名稱清單
INTERSECT
SELECT * FROM DBO.名稱清單_BAK
說明：[出處：MSDN]
比較兩個查詢的結果來傳回個別值。
SELECT * FROM DBO.名稱清單
INTERSECT
SELECT * FROM DBO.名稱清單_BAK
說明：[出處：MSDN]
比較兩個查詢的結果來傳回個別值。
EXCEPT 會從左側查詢中傳回在右側查詢中找不到的任何個別值。
INTERSECT 會傳回 INTERSECT 運算元左右兩側查詢都傳回的任何個別值。
使用 EXCEPT 或 INTERSECT 的兩個查詢，其結果集的基本組合規則如下：
在所有查詢中，資料行的數目和順序都必須相同。
資料類型必須相容。
2016/9/29
DB筆記
一、看temp table是否存在
IF OBJECT_ID('tempdb.dbo.#tmp_New') IS NOT NULL
DROP TABLE #tmp_New;
二、直接執行組合字串
EXECUTE('TRUNCATE TABLE '+@Table_To)
EXECUTE('INSERT '+@Table_To+' SELECT * FROM ['+@Server+'].['+@Database+'].['+@Schema+'].['+@Table+']')
三、ISNULL 函數：以指定的取代值來取代 NULL-- 使用 ISNULL 函數，用 N/A 字串來取代所有 NULL 項目
SELECT CustomerID, City, ISNULL(Region, 'N/A') Region, Country
FROM Customers
GO
四、SET QUOTED_IDENTIFIER ON/OFF语句的作用
当在数据库中新建一个名字是USER的表的时候，常常会带来一些麻烦，因为USER是SQL中的关键字，但是上面的几个语句不会报错。再说一个概念：标示符是SQL中的中括号[]。
当SET QUOTED_IDENTIFIER值为ON时，双引号内的字符被当作是数据库对象。就是说双引号" "和标识符[]效果是一样样的，他们都表示引用的字符是数据库对象。单引号'表示字符串的边界。
当SET QUOTDE_IDENTIFIER OFF时，双引号被解释为字符串的边界，和单引号的作用是类似的。就是说双引号"不能当做标识符使用，但是可以当做字符边界，和单引号'的效果是一样样的。
可以做一个总结：当SET QUOTED_IDENTIFIER ON " "等同于[ ] 表示数据库对象；当SET QUOTED_IDENTIFIER OFF " "等同于' '表示字符串边界；还有这里的双引号" 并不是两个单引号'合起来的，是shift+”打出来的，初学者可能会犯这样的错误。
五、SET ANSI_NULLS ON/OFF
当 SET ANSI_NULLS 为 ON 时，即使 column_name 中包含空值，使用 WHERE column_name = NULL 的 SELECT 语句仍返回零行。即使 column_name 中包含非空值，使用 WHERE column_name <> NULL 的 SELECT 语句仍会返回零行。
当 SET ANSI_NULLS 为 OFF 时，等于 (=) 和不等于 (<>) 比较运算符不遵从 SQL-92 标准。使用 WHERE column_name = NULL 的 SELECT 语句返回 column_name 中包含空值的行。使用 WHERE column_name <> NULL 的 SELECT 语句返回列中包含非空值的行。此外，使用 WHERE column_name <> XYZ_value 的 SELECT 语句返回所有不为 XYZ_value 也不为 NULL 的行。
IF OBJECT_ID('tempdb.dbo.#tmp_New') IS NOT NULL
DROP TABLE #tmp_New;
二、直接執行組合字串
EXECUTE('TRUNCATE TABLE '+@Table_To)
EXECUTE('INSERT '+@Table_To+' SELECT * FROM ['+@Server+'].['+@Database+'].['+@Schema+'].['+@Table+']')
三、ISNULL 函數：以指定的取代值來取代 NULL-- 使用 ISNULL 函數，用 N/A 字串來取代所有 NULL 項目
SELECT CustomerID, City, ISNULL(Region, 'N/A') Region, Country
FROM Customers
GO
四、SET QUOTED_IDENTIFIER ON/OFF语句的作用
当在数据库中新建一个名字是USER的表的时候，常常会带来一些麻烦，因为USER是SQL中的关键字，但是上面的几个语句不会报错。再说一个概念：标示符是SQL中的中括号[]。
当SET QUOTED_IDENTIFIER值为ON时，双引号内的字符被当作是数据库对象。就是说双引号" "和标识符[]效果是一样样的，他们都表示引用的字符是数据库对象。单引号'表示字符串的边界。
当SET QUOTDE_IDENTIFIER OFF时，双引号被解释为字符串的边界，和单引号的作用是类似的。就是说双引号"不能当做标识符使用，但是可以当做字符边界，和单引号'的效果是一样样的。
可以做一个总结：当SET QUOTED_IDENTIFIER ON " "等同于[ ] 表示数据库对象；当SET QUOTED_IDENTIFIER OFF " "等同于' '表示字符串边界；还有这里的双引号" 并不是两个单引号'合起来的，是shift+”打出来的，初学者可能会犯这样的错误。
五、SET ANSI_NULLS ON/OFF
当 SET ANSI_NULLS 为 ON 时，即使 column_name 中包含空值，使用 WHERE column_name = NULL 的 SELECT 语句仍返回零行。即使 column_name 中包含非空值，使用 WHERE column_name <> NULL 的 SELECT 语句仍会返回零行。
当 SET ANSI_NULLS 为 OFF 时，等于 (=) 和不等于 (<>) 比较运算符不遵从 SQL-92 标准。使用 WHERE column_name = NULL 的 SELECT 语句返回 column_name 中包含空值的行。使用 WHERE column_name <> NULL 的 SELECT 语句返回列中包含非空值的行。此外，使用 WHERE column_name <> XYZ_value 的 SELECT 语句返回所有不为 XYZ_value 也不为 NULL 的行。
2016/9/22
常用SP查詢指令
--取得sp名稱https://msdn.microsoft.com/en-us/library/ms177596.aspx
select * from sysobjects where xtype='p'
--取得sp內容
select text, object_name(id) from sys.syscomments
where 1=1
and text like '%XXX%'
order by object_name(id)
--查詢db lock的table
select object_name(resource_associated_entity_id) as tablename,*
from sys.dm_tran_locks
where request_type='lock'
select * from sysobjects where xtype='p'
--取得sp內容
select text, object_name(id) from sys.syscomments
where 1=1
and text like '%XXX%'
order by object_name(id)
--查詢db lock的table
select object_name(resource_associated_entity_id) as tablename,*
from sys.dm_tran_locks
where request_type='lock'
查詢正在執行的SQL指令
SELECT r.scheduler_id as 排程器識別碼,
status as 要求的狀態,
r.session_id as SPID,
r.blocking_session_id as BlkBy,
substring(
ltrim(q.text),
r.statement_start_offset/2+1,
(CASE
WHEN r.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(MAX), q.text)) * 2
ELSE r.statement_end_offset
END - r.statement_start_offset)/2)
AS [正在執行的 T-SQL 命令],
r.cpu_time as [CPU Time(ms)],
r.start_time as [開始時間],
r.total_elapsed_time as [執行總時間],
r.reads as [讀取數],
r.writes as [寫入數],
r.logical_reads as [邏輯讀取數],
-- q.text, /* 完整的 T-SQL 指令碼 */
d.name as [資料庫名稱]
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS q
LEFT JOIN sys.databases d ON (r.database_id=d.database_id)
WHERE r.session_id > 50 AND r.session_id <> @@SPID
ORDER BY r.total_elapsed_time desc
2015/6/9
Join hints, Table hints and Query hints
::=
{ LOOP
| HASH
| MERGE
| REMOTE
}
::=
{
KEEPIDENTITY
| KEEPDEFAULTS
| FASTFIRSTROW
| HOLDLOCK
| IGNORE_CONSTRAINTS
| IGNORE_TRIGGERS
| NOWAIT
| PAGLOCK
| READCOMMITTED
| READCOMMITTEDLOCK
| READPAST
| REPEATABLEREAD
| ROWLOCK
| SERIALIZABLE
| TABLOCK
| TABLOCKX
| UPDLOCK
| XLOCK
}
::=
[ NOEXPAND ] {
INDEX (index_value [ ,...n ] ) | INDEX = (index_value)
| FASTFIRSTROW
| FORCESEEK
| HOLDLOCK
| NOLOCK
| NOWAIT
| PAGLOCK
| READCOMMITTED
| READCOMMITTEDLOCK
| READPAST
| READUNCOMMITTED
| REPEATABLEREAD
| ROWLOCK
| SERIALIZABLE
| TABLOCK
| TABLOCKX
| UPDLOCK
| XLOCK
} 2015/3/12
2015/3/5
查詢未LINK DB中的資料
OPENDATASOURCE ('SQLOLEDB','Data Source=ServerName;User ID=ID;Password=PW').DB.dbo.table
EX:
SELECT TOP (100) PERCENT dbo.t_Prod_Info.PcbSerialNo, dbo.t_VIP_Prod_Info.SerialNo
FROM OPENDATASOURCE ('SQLOLEDB','Data Source=DB2;User ID=ID;Password=PW').DBNAME.dbo.Order_Info INNER JOIN
dbo.t_Prod_Info ON OPENDATASOURCE ('SQLOLEDB','Data Source=DB2;User ID=ID;Password=PW').DBNAME.dbo.OID = dbo.t_VIP_Prod_Info.OID
WHERE (dbo.t_Order_Info.Ordernumber IN ('XXXXXX', 'YYYYYY', 'ZZZZZZ',))
OPENDATASOURCE ('SQLOLEDB','Data Source=ServerName;User ID=ID;Password=PW').DB.dbo.table
EX:
SELECT TOP (100) PERCENT dbo.t_Prod_Info.PcbSerialNo, dbo.t_VIP_Prod_Info.SerialNo
FROM OPENDATASOURCE ('SQLOLEDB','Data Source=DB2;User ID=ID;Password=PW').DBNAME.dbo.Order_Info INNER JOIN
dbo.t_Prod_Info ON OPENDATASOURCE ('SQLOLEDB','Data Source=DB2;User ID=ID;Password=PW').DBNAME.dbo.OID = dbo.t_VIP_Prod_Info.OID
WHERE (dbo.t_Order_Info.Ordernumber IN ('XXXXXX', 'YYYYYY', 'ZZZZZZ',))
2012/10/31
DBCC 狀態檢查語法
exec sp_who sa
go--列出該用戶端最後一次傳給sql server的敘述內容
--參數為spid
dbcc inputbuffer(209)
go--顯示指定資料庫中最早開始的執行中交易，及最早開始發行的複寫交易資訊
--with tableresults表示將傳回結果以資料表格式呈現
dbcc opentran with tableresults
go--以16進位和文字模式傳回sql server最近一次傳回給指定程序的內容
--參數為spid
dbcc outputbuffer (300)
go--以資料表的方式列出程序快取的資訊，沒有參數
--num proc buffs:在程序快取中所有項目所使用的總頁數
--num proc buffs used:所有目前正使用中的項目所用的總頁數
--num proc buffs active:同上一項，為了與舊版相容而提供的欄位
--proc cache size:在程序快取中的項目總數
--proc cache used:目前正使用中的項目總數
--proc cache active:同上一項，為了與舊版相容而提供的欄位
dbcc proccache
go--顯示指定資料表中資料和索引存放的連續情形，可藉此瞭解資料存放是否太散亂影響效能
--pages scanned掃描頁數
--extents scanned掃描的範圍，extent數
--extent switches範圍切換，切換extent的次擻
--avg. pages per extent每個範圍的平均頁數，平均每個extent包含的資料表頁數
--scan density [best count:actual count]掃描密度，如果全部資料連續存放時要切換extent的次數和實際次數的比值
--logical scan fragmentation邏輯掃描片段，資料頁未連續存放的比例
--extent scan fragmentation範圍掃描片段，extent不連續的比例
--avg. bytes free per page每個頁面的平均可用位元數，平均每頁的可用空間大小
--avg. page density (full)平均頁面密度，每頁空間的使用率
--參數為資料表名稱
dbcc showcontig ('dbo.t_prod_Info')
go--sql server會為索引建立統計資訊，使查詢最佳化，程式能根據即有的統計資訊，來決定應採行的執行計劃
--顯示資料表索引或欄位的統計資訊，藉以了解我們所建的索引是否真的能提昇效能，或是手動來更新
update statistics dbo.t_Prod_Info--參數為資料表稱，索引名稱
--updated若不是最新，用update statistics來手動更新
dbcc show_statistics ('dbo.t_Prod_Info',SerialNo)
go--顯示交易記錄檔的空間使用情形
dbcc sqlperf(logspace)
go--顯示追蹤旗標的狀態
--參數為traceflag
dbcc tracestatus (112)
go--顯示目前連線的set選項之設定值，不用加參數
dbcc useroptions
go
go--列出該用戶端最後一次傳給sql server的敘述內容
--參數為spid
dbcc inputbuffer(209)
go--顯示指定資料庫中最早開始的執行中交易，及最早開始發行的複寫交易資訊
--with tableresults表示將傳回結果以資料表格式呈現
dbcc opentran with tableresults
go--以16進位和文字模式傳回sql server最近一次傳回給指定程序的內容
--參數為spid
dbcc outputbuffer (300)
go--以資料表的方式列出程序快取的資訊，沒有參數
--num proc buffs:在程序快取中所有項目所使用的總頁數
--num proc buffs used:所有目前正使用中的項目所用的總頁數
--num proc buffs active:同上一項，為了與舊版相容而提供的欄位
--proc cache size:在程序快取中的項目總數
--proc cache used:目前正使用中的項目總數
--proc cache active:同上一項，為了與舊版相容而提供的欄位
dbcc proccache
go--顯示指定資料表中資料和索引存放的連續情形，可藉此瞭解資料存放是否太散亂影響效能
--pages scanned掃描頁數
--extents scanned掃描的範圍，extent數
--extent switches範圍切換，切換extent的次擻
--avg. pages per extent每個範圍的平均頁數，平均每個extent包含的資料表頁數
--scan density [best count:actual count]掃描密度，如果全部資料連續存放時要切換extent的次數和實際次數的比值
--logical scan fragmentation邏輯掃描片段，資料頁未連續存放的比例
--extent scan fragmentation範圍掃描片段，extent不連續的比例
--avg. bytes free per page每個頁面的平均可用位元數，平均每頁的可用空間大小
--avg. page density (full)平均頁面密度，每頁空間的使用率
--參數為資料表名稱
dbcc showcontig ('dbo.t_prod_Info')
go--sql server會為索引建立統計資訊，使查詢最佳化，程式能根據即有的統計資訊，來決定應採行的執行計劃
--顯示資料表索引或欄位的統計資訊，藉以了解我們所建的索引是否真的能提昇效能，或是手動來更新
update statistics dbo.t_Prod_Info--參數為資料表稱，索引名稱
--updated若不是最新，用update statistics來手動更新
dbcc show_statistics ('dbo.t_Prod_Info',SerialNo)
go--顯示交易記錄檔的空間使用情形
dbcc sqlperf(logspace)
go--顯示追蹤旗標的狀態
--參數為traceflag
dbcc tracestatus (112)
go--顯示目前連線的set選項之設定值，不用加參數
dbcc useroptions
go
2012/10/23
DBCC 維護用語法
--刪除可變長度如varchar，text欄位之後，該欄位的空間不會主動被釋放，收回該空間
--(資料庫名稱，資料表名稱，batch_size)
--batch_size=100，每次處理100筆
--with no_infomsgs不要顯示任何訊息
dbcc cleantable ('ems_odm','allequipment',100) with no_infomsgs
go
--重建資料表的索引
--(資料表名稱，索引名稱，fillfactor填滿因數0為預設值)
dbcc dbreindex ('ems_odm.dbo.allequipment','pk_allequipment_1',0)
go
--index重組
--(資料庫名稱，資料表名稱，索引名稱)
dbcc indexdefrag ('ems_odm','allequipment','pk_allequipment_1')
go
--釋放資料庫未用空間，縮小資料檔所佔用的磁碟空間
--(資料庫名稱，可用空間剩餘%，notruncate/truncateonly)
--在縮小檔案同時，會將散置各處的資料往前移動，集中存放
--notruncate不釋放空間，truncateonly會將未用空間都釋放
--最多只能將檔案縮小到當初建立時所設的大小
dbcc shrinkdatabase ('ems_odm',50,notruncate)
go
--dbcc shrinkdatabaseoba只是用來縮減資料庫中所有資料庫檔案和記錄檔，只對某個檔案要用 shrinkfile
--(檔案名稱，縮減後的大小mb，notruncate/truncateonly)
--檔案名稱用的是邏輯名稱，而非實體名稱
use ems_odm
dbcc shrinkfile (1,150)
go
--更正資料庫sysindexes資料表中有錯誤的欄位值，有錯誤時會影響資料庫效能
--若不使用資料庫名稱而用0，代表目前的資料庫
--with count_rows表示要用資料表中實際的記錄筆數存到該資料表在sysindexes中的rows欄的值
dbcc updateusage ('ems_odm','allequipment') with count_rows
go
--(資料庫名稱，資料表名稱，batch_size)
--batch_size=100，每次處理100筆
--with no_infomsgs不要顯示任何訊息
dbcc cleantable ('ems_odm','allequipment',100) with no_infomsgs
go
--重建資料表的索引
--(資料表名稱，索引名稱，fillfactor填滿因數0為預設值)
dbcc dbreindex ('ems_odm.dbo.allequipment','pk_allequipment_1',0)
go
--index重組
--(資料庫名稱，資料表名稱，索引名稱)
dbcc indexdefrag ('ems_odm','allequipment','pk_allequipment_1')
go
--釋放資料庫未用空間，縮小資料檔所佔用的磁碟空間
--(資料庫名稱，可用空間剩餘%，notruncate/truncateonly)
--在縮小檔案同時，會將散置各處的資料往前移動，集中存放
--notruncate不釋放空間，truncateonly會將未用空間都釋放
--最多只能將檔案縮小到當初建立時所設的大小
dbcc shrinkdatabase ('ems_odm',50,notruncate)
go
--dbcc shrinkdatabaseoba只是用來縮減資料庫中所有資料庫檔案和記錄檔，只對某個檔案要用 shrinkfile
--(檔案名稱，縮減後的大小mb，notruncate/truncateonly)
--檔案名稱用的是邏輯名稱，而非實體名稱
use ems_odm
dbcc shrinkfile (1,150)
go
--更正資料庫sysindexes資料表中有錯誤的欄位值，有錯誤時會影響資料庫效能
--若不使用資料庫名稱而用0，代表目前的資料庫
--with count_rows表示要用資料表中實際的記錄筆數存到該資料表在sysindexes中的rows欄的值
dbcc updateusage ('ems_odm','allequipment') with count_rows
go
DB Collation
「定序」，就是決定資料在資料庫裡排列的方式。當兩個 table 定序不同的時候如果有做 join 的動作，那會發生「無法解析 equal to 作業中 xxx 與 ooo 之間的定序衝突」，因為 SQL Server 無法得知妳到底要遵從哪種定序。
例如，「Chinese_Taiwan_Stroke_CI_AS」，「CI」是指 Case Insensitive（不區分大小寫），「AS」是指 Accent sensitivity（區分腔調），其他比較常見的還有「CS」Case sensitivity（區分大小寫）、「AI」Accent Insensitive（不區分腔調），「WS」Width sensitivity（區分全形半形）等等。
程式開發時，可以用下列方式解決
select A, B, C from TableA
join TableB on TableA.A=TableB.A collate Chinese_Taiwan_Stroke_CI_AS
where A='XXX'
想要更改整個資料庫的定序做法如下
ALTER DATABASE 資料庫名稱 SET SINGLE_USER WITH ROLLBACK IMMEDIATE //鎖定目前資料庫
ALTER DATABASE 資料庫名稱 Collate Chinese_Taiwan_Stroke_CI_AS //設定定序名稱
ALTER DATABASE 資料庫名稱 SET MULTI_USER WITH ROLLBACK IMMEDIATE //恢復成多人使用
這個指令，不僅會直接更改資料庫的定序，連同這個資料庫下面的所有 tables 也都會被更改成這個新的定序，不過 Table 下的欄位也有自己的定序，通常預設是跟 Table 一樣，不過有時候也有例外的情形。
例如，「Chinese_Taiwan_Stroke_CI_AS」，「CI」是指 Case Insensitive（不區分大小寫），「AS」是指 Accent sensitivity（區分腔調），其他比較常見的還有「CS」Case sensitivity（區分大小寫）、「AI」Accent Insensitive（不區分腔調），「WS」Width sensitivity（區分全形半形）等等。
程式開發時，可以用下列方式解決
select A, B, C from TableA
join TableB on TableA.A=TableB.A collate Chinese_Taiwan_Stroke_CI_AS
where A='XXX'
想要更改整個資料庫的定序做法如下
ALTER DATABASE 資料庫名稱 SET SINGLE_USER WITH ROLLBACK IMMEDIATE //鎖定目前資料庫
ALTER DATABASE 資料庫名稱 Collate Chinese_Taiwan_Stroke_CI_AS //設定定序名稱
ALTER DATABASE 資料庫名稱 SET MULTI_USER WITH ROLLBACK IMMEDIATE //恢復成多人使用
這個指令，不僅會直接更改資料庫的定序，連同這個資料庫下面的所有 tables 也都會被更改成這個新的定序，不過 Table 下的欄位也有自己的定序，通常預設是跟 Table 一樣，不過有時候也有例外的情形。
DB Cluster and NonCluster
索引是一種與資料表或檢視有關的磁碟內存結構，它會加快從該資料表或檢視中擷取資料列的速度。索引中包含從資料表或檢視中一或多個資料行建出的索引鍵。這些索引鍵儲存在結構中 (B 型樹狀目錄)，讓 SQL Server 可以快速有效地找到與索引鍵值相關的一或多個資料列。
資料表或檢視可包含下列類型的索引：
- 叢集
- 叢集索引將資料表或檢視中的資料列依其索引鍵值排序與儲存。這些就是索引定義中包含的資料行。因為資料列本身只能以一種順序排序，所以每個資料表只能有一個叢集索引。
- 只有當資料表包含叢集索引時，資料表中的資料列才會以排序順序儲存。當資料表有叢集索引時，資料表又稱為叢集資料表。如果資料表沒有任何叢集索引，它的資料列就儲存在未排序的結構中，這個結構稱為堆積。
- 非叢集
- 非叢集索引有一個與資料列完全分開的結構。非叢集索引包含非叢集索引鍵值，而每個索引鍵值項目都有一個指標，指向包含索引鍵值的資料列。
- 從非叢集索引中的索引列指向資料列的指標被稱為資料列定位器。資料列定位器的結構須視資料頁儲存在堆積或叢集資料表而定。若是堆積，資料列定位器是指向資料列的指標。若是叢集資料表，資料列定位器就是叢集索引鍵。
- 在 SQL Server 2005 中，您可以將無索引鍵資料行加入非叢集索引的分葉層級中，以規避現有索引鍵的限制 (900 位元組和 16 個索引鍵資料行)，並執行完全涵蓋的索引查詢。如需詳細資訊。
叢集與非叢集索引都可以是唯一的。這表示任何兩個資料列不得以相同的值做為索引鍵。否則，索引就不是唯一的，那麼多個資料列就可以共用同一個索引鍵值。
設計不良的索引與不足的索引是資料庫應用程式瓶頸的主要原因。設計有效的索引是達到良好資料庫和應用程式效能最重要的一點。為資料庫選擇正確的索引及工作負載時，往往很難在查詢速度與更新成本之間取得平衡。範圍較小的索引，或是索引的索引鍵中包含較少的資料行，所需的磁碟空間與維護負擔相對較小。相反的，如果索引範圍較大，能涵蓋的查詢就更多。在找到最有效率的索引之前，可能需要先試過數種不同的設計。索引可以新增、修改和卸除，不會影響資料庫結構描述或應用程式的設計。所以，不要吝於嘗試各種不同的索引。
使用索引不一定就會有良好的效能，良好的效能和有效率地使用索引也不能劃上等號。如果使用索引對產生最佳效能一定有幫助，查詢最佳化工具的作業就很單純。但事實上，選擇不正確的索引可能得不到最佳效能，因為 INSERT、UPDATE 與 DELETE 陳述式執行時，只要資料表中的資料一變更，所有的索引也都必須隨著調整。
2012/7/24
SQL TRANSACTION
範例
http://pastie.org/4312609
TRANSACTION結構內所進行的資料庫查詢、新增、修改、刪除等動作愈少愈好，因為每次的動作均會讓資料庫鎖定table，無法讓外界存取，容易造成其他程式對相同table進行動作時，產生time out或exception的狀況
http://pastie.org/4312609
TRANSACTION結構內所進行的資料庫查詢、新增、修改、刪除等動作愈少愈好，因為每次的動作均會讓資料庫鎖定table，無法讓外界存取，容易造成其他程式對相同table進行動作時，產生time out或exception的狀況
SQL FETCH 迴圈架構
範例
http://pastie.org/4312553
FETCH 的架構如下
DECLARE Table_Cursor CURSOR FOR
SELECT [Table],[boxid],[lineid]
FROM [MotoTRAK01].[dbo].[tbl_VIP_Accessories_Reference]
where fgpn = @PartNumber
OPEN Table_Cursor
FETCH NEXT FROM Table_Cursor
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Table_Cursor into @tablename,@boxid,@line
END
CLOSE Table_Cursor
DEALLOCATE Table_Cursor
http://pastie.org/4312553
FETCH 的架構如下
DECLARE Table_Cursor CURSOR FOR
SELECT [Table],[boxid],[lineid]
FROM [MotoTRAK01].[dbo].[tbl_VIP_Accessories_Reference]
where fgpn = @PartNumber
OPEN Table_Cursor
FETCH NEXT FROM Table_Cursor
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Table_Cursor into @tablename,@boxid,@line
END
CLOSE Table_Cursor
DEALLOCATE Table_Cursor
2012/5/11
use store procedure to input and output data by XML format
使用sql server2005/store procedure
匯出 xml
http://pastie.org/3892418
匯入 處理 xml
http://pastie.org/3892451
匯出 xml
http://pastie.org/3892418
匯入 處理 xml
http://pastie.org/3892451
時間格式轉換
select getdate()
2004-09-12 11:06:08.177
select CONVERT(varchar, getdate(), 120 )
2004-09-12 11:06:08
select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),’-',”),’ ‘,”),’:',”)
20040912110608
select CONVERT(varchar(12) , getdate(), 111 )
2004/09/12
select CONVERT(varchar(12) , getdate(), 112 )
20040912
select CONVERT(varchar(12) , getdate(), 102 )
2004.09.12
select CONVERT(varchar(12) , getdate(), 101 )
09/12/2004
select CONVERT(varchar(12) , getdate(), 103 )
12/09/2004
select CONVERT(varchar(12) , getdate(), 104 )
12.09.2004
select CONVERT(varchar(12) , getdate(), 105 )
12-09-2004
select CONVERT(varchar(12) , getdate(), 106 )
12 09 2004
select CONVERT(varchar(12) , getdate(), 107 )
09 12, 2004
select CONVERT(varchar(12) , getdate(), 108 )
11:06:08
select CONVERT(varchar(12) , getdate(), 109 )
09 12 2004 1
select CONVERT(varchar(12) , getdate(), 110 )
09-12-2004
select CONVERT(varchar(12) , getdate(), 113 )
12 09 2004 1
select CONVERT(varchar(12) , getdate(), 114 )
11:06:08.177
2004-09-12 11:06:08.177
select CONVERT(varchar, getdate(), 120 )
2004-09-12 11:06:08
select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),’-',”),’ ‘,”),’:',”)
20040912110608
select CONVERT(varchar(12) , getdate(), 111 )
2004/09/12
select CONVERT(varchar(12) , getdate(), 112 )
20040912
select CONVERT(varchar(12) , getdate(), 102 )
2004.09.12
select CONVERT(varchar(12) , getdate(), 101 )
09/12/2004
select CONVERT(varchar(12) , getdate(), 103 )
12/09/2004
select CONVERT(varchar(12) , getdate(), 104 )
12.09.2004
select CONVERT(varchar(12) , getdate(), 105 )
12-09-2004
select CONVERT(varchar(12) , getdate(), 106 )
12 09 2004
select CONVERT(varchar(12) , getdate(), 107 )
09 12, 2004
select CONVERT(varchar(12) , getdate(), 108 )
11:06:08
select CONVERT(varchar(12) , getdate(), 109 )
09 12 2004 1
select CONVERT(varchar(12) , getdate(), 110 )
09-12-2004
select CONVERT(varchar(12) , getdate(), 113 )
12 09 2004 1
select CONVERT(varchar(12) , getdate(), 114 )
11:06:08.177
訂閱：
文章 (Atom)