SQL Server 日期格式轉換(Convert用法)DBCC命令詳解

SQL Server 日期格式轉換(Convert用法

SQL Server中，Convert可以將日期轉換成不同格式，格式控制是由CONVERT (data_type[(length)], expression [, style])中的style來完成的。下面列出了style取各種值時的效果：         在表中，左側的兩列表示將 datetime 或 smalldatetime 轉換為字元資料的 style 值。給 style 值加 100，可獲得包括世紀數位的四位年份 (yyyy)。 不帶世紀數位 (yy) 帶世紀數位 (yyyy) 標準 輸入/輸出** - 0 或 100 (*) 預設值 mon dd yyyy hh:miAM（或 PM） 1 101 美國 mm/dd/yyyy 2 102 ANSI yy.mm.dd 3 103 英國/法國 dd/mm/yy 4 104 德國 dd.mm.yy 5 105 意大利 dd-mm-yy 6 106 - dd mon yy 7 107 - mon dd, yy 8 108 - hh:mm:ss - 9 或 109 (*) 預設值 + 毫秒 mon dd yyyy hh:mi:ss:mmmAM（或 PM） 10 110 美國 mm-dd-yy 11 111 日本 yy/mm/dd 12 112 ISO yymmdd - 13 或 113 (*) 歐洲預設值 + 毫秒 dd mon yyyy hh:mm:ss:mmm(24h) 14 114 - hh:mi:ss:mmm(24h) - 20 或 120 (*) ODBC 規範 yyyy-mm-dd hh:mm:ss[.fff] - 21 或 121 (*) ODBC 規範（帶毫秒） yyyy-mm-dd hh:mm:ss[.fff] - 126(***) ISO8601 yyyy-mm-dd Thh:mm:ss:mmm（不含空格） - 130* 科威特 dd mon yyyy hh:mi:ss:mmmAM - 131* 科威特 dd/mm/yy hh:mi:ss:mmmAM *      預設值（style 0 或 100、9 或 109、13 或 113、20 或 120、21 或 121）始終返回世紀數位 (yyyy)。 ** 當轉換為 datetime 時輸入；當轉換為字元資料時輸出。 *** 專門用於 XML。對於從 datetime 或 smalldatetime 到 character 資料的轉換，輸出格式如表中所示。對於從 float、money 或 smallmoney 到 character 資料的轉換，輸出等同於 style 2。對於從 real 到 character 資料的轉換，輸出等同於 style 1。 重要    預設情況下，SQL Server 根據截止年份 2049 解釋兩位元字的年份。即，兩位元字的年份 49 被解釋為 2049，而兩位元字的年份 50 被解釋為 1950。許多用戶端應用程式（例如那些基於 OLE Automation 物件的用戶端應用程式）都使用 2030 作為截止年份。SQL Server 提供一個配置選項（"兩位元字的截止年份"），藉以更改 SQL Server 所使用的截止年份並對日期進行一致性處理。然而最安全的辦法是指定四位元字年份。           當從 smalldatetime 轉換為字元資料時，包含秒或毫秒的樣式將在這些位置上顯示零。當從 datetime 或 smalldatetime 值進行轉換時，可以通過使用適當的 char 或 varchar 資料類型長度來截斷不需要的日期部分。        如果想去掉字串中的分隔字元，可以用replace函數。比如：replace(replace(replace(replace(convert (varchar(30),getdate(),121),'-',''),' ',''),':',''),'.','')

DBCC命令詳解

         我們知道，在資料庫系統的開發和應用中，必須保證資料庫的完整性和一致性。

當資料庫出現了嚴重錯誤；當我們懷疑資料庫受到破壞（如無法用drop命令刪除資料庫或對象，使用某個表時出現“不可靠資料”的資訊等）；當使用者改變了
Server的預設排序的順序或改變了字元集而需要檢查；當SA對系統做定期檢查；這些時候，我們都需要使用資料庫一致性檢查工具（Database
Consistenecy
Checker，簡稱DBCC）。DBCC是一個實用命令集，用來檢查一個資料庫的邏輯一致性及物理一致性。在開發和應用中，DBCC是我們經常要使用的
命令。

　　DBCC命令的格式如下  

dbcc
(checktable ((表名|表標識( [, skip_ncindex] ) |
checkdb [(資料庫名[, skip_ncindex] )] |
checkalloc [ (資料庫名[, fix | nofix] )] |
tablealloc( {表名|表標識}
[，{full |optimized |fast |null}
[, fix |nofix] ]]) |
indexalloc ( {表名|表標識}，索引標識
[，{full |optimezed | fast | null}
[, fix |nofix ]] ) |
checkcatalog [ (資料庫名)] |
dbrepair(資料庫名,dropdb ) |
reindex({表名|表標識} ) |
fix_text({表名|表標識) }  

　
　dbcc的許可權，對於checktable,fix_text和reindex是預設賦給表的屬主，對於checkdb,checkalloc，
checkcatalog,dbrepair，indexalloc和tablealloc，是預設賦給資料庫屬主的。DBO自動獲得DBCC命令和全部
選項的許可權。該許可權不可轉授。此外，dbcc在資料庫是活動時運行，除了dbrepair選項和帶有fix選項的dbcc checkalloc以外。

　　checktable選項

　
　checktable是用來對一個指定的表做檢查，確保索引和資料頁正確地串連，索引按正確的順序儲存，所有指標的一致性，每頁上資料資訊的合理性，頁
位移的合理性。如果日誌段在它自己的（日誌）裝置上，對syslogs表使用dbcc
checktable命令可以報告已使用的和剩餘的日誌空間，使用skip_ncindex選項使得dbcc
checktable跳過對使用者表上非聚簇索引（nonclustered index）的檢查。預設是檢查所有的索引。

　　例１.檢查日誌使用的空間量和未用的空間量：

dbcc checktable (syslogs)  

　　若日誌段在日誌裝置上，則會返回如下資訊：

checking syslogs
The total number of data page in the table is 1.
NOTICE:Space used on the log segment is 0.20 Mbytes, 0.13%.
NOTICE:Space free on the log segment is 153.4Mbytes,99.87%.
DBCC execution Completed.If dbcc printed error messages,
Contact a user with SA role.  

　　若日誌不在它自己的裝置上，則會顯示下列資訊：

NOTICE:Notification of log space used/free.
Can not be reported because the log segment is not on its own device.
例2. dbcc checktable (titles)
The total number of data page in this table is 3.
Table has 18 data rows.
DBCC execution Completed. If DBCC printed error messages. contact a user with SA role.  

　　checkdb選項

　　運行checkdb選項同checktable檢查的內容一樣，但它是對一指定資料庫中的每張表都做這樣的檢查。若未指定資料庫名，checkdb檢查當前的資料庫。checkdb返回的資訊，也同於checktable。

　　checkalloc選項

　
　checkalloc是檢查指定資料庫，看其所有正確分配的頁和尚未分配的頁的情況。若未指定資料庫名，則checkalloc檢查當前資料庫。
checkalloc會返回已指派的和使用的空間數量。checkalloc的預設模式為nofix，要使用fix選項，必須把資料庫置於單一使用者模式。

　例：

dbcc checkalloc (pubs2)
.
.
.
alloc page 0 (#of extent=32 used pages=68 ref pages=68)
alloc page 256 (# of extent=32 used pages=154 ref pages=154)
alloc page 512 (# of extent=28 used pages=184 ref pages=184)
alloc page 768 (# of extent=1 used pages=1 ref pages=1)
total (# of extent=93 used pages=407 ref pages=407) in this database.
DBCC execution completed.If dbcc printed error message,
Contact a user with System Adminstrator (SA) role.  

　　tablealloc選項

　
　tablealloc檢查指定的表以確保所有頁都被正確地分配。它是checkalloc的縮小版本。對單張表進行相同的完整性檢查。使用
tablealloc可以產生三種類型的報表：full,optimized和fast。full選項相當於表一級的checkalloc；它報告各種類
型的分配錯誤。optimized選項基於表的對象分配映像（OAM）頁裡列出的分配頁產生報告。它並不報告，也不能整理OAM頁裡沒有列出的在分配頁上
沒有引用的擴充（extent）。如果沒有指明類型，或使用了null，則optimized選項是預設的設定。fast選項，並不產生分配報告，但產生
一個被引用但並沒有在擴充裡分配的頁的額外的報告。fix|nofix選項決定tablealloc
是否整理表中發現的分配錯誤。對於所有的表，預設為fix，但系統資料表除外，它們的預設為nofix。要對系統資料表使用fix選項，必須首先將資料庫置成單用
戶模式。

例：

dbcc tablealloc(titles)
顯示資訊如下：
The default report option of OPTIMIZED is used for this run. The default fix option of FIX.is used for this run.
.
.
.
Total #of extent=3
Alloc page 256 (# of extent=1 used pages=2 ref pages=2).
Alloc page 256(# of extent=1 used pages=2 ref pages=2)
Alloc page 256 (# of extent=1 used pages=2 ref pages=2)
Total (# of extent=3 used pages=8 ref pages=8) in this database.  

　　indexalloc 選項

　　indexalloc檢查指定的索引，確保所有的頁都被正確地分配，它是checkalloc的縮小版本，對單獨一條索引指定同樣的完整性檢查。其中各選項與tablealloc相同。

　　checkcatalog選項

　
　checkcatalog選項用於檢查系統資料表內，系統資料表之間的一致性。例如：它確保在syscolumns表中的每一（資料）類型在systypes表
中都有一個相匹配的記錄；對於sysobjects中的每個表和視圖在syscolumns表中應有關於它們每一列的描述記錄；確保在syslogs中的
最後一個檢查點是有效。checkcatalog也報告任何已定義的段。若不指定資料庫名，則檢查當前資料庫。

　　dbrepair選項

　
　dbrepair（資料庫名，dropdb）選項是刪除一個受破壞的資料庫。受破壞的資料庫是不能用drop database命令刪除的，drop
database只能刪除正常的資料庫，當執行dbrepair命令時，任何使用者（包括執行此命令的使用者）都不得使用正被刪除的資料庫。該選項要在
master庫中運行。

　　reindex選項

　
　reindex選項通過運行dbcc
checktable的“fast”執行方式檢查使用者表上索引的完整性。如果它檢測出索引有問題則會刪除並重建索引。在SQL
Server的排列順序改變之後，SA或表屬主應該執行這一選項。此選項不能在使用者定義的事務中運行。

例：

dbcc reindex (titles)
返回資訊：One or more indexes corrupt.They will be rebuilt.  

　　fix_text選項

　
　SQL Server的字元集由單位元組轉變為多位元組後，fix_text選項用於升級文本值。SQL
Server的字元集由單位元組轉變為多位元組字元集會使文本資料的管理更加複雜。由於文本值可能較大足以覆蓋若干頁，SQL
Server必須能處理（通過頁約束）可能橫跨頁的字元。為做到這點，伺服器需要在每一文本頁上添加一些資訊。SA或表屬主必須在文本資料的每一個表上運
行dbcc fix_text，以計算所需要的新頁數。

　　總之，DBCC命令所返回的資訊能準確地反映資料庫及它的各個對象的狀態，是我們檢測資料庫的好幫手。