SQL Server date format conversion (convert usage) DBCC command details

SQL Server date format conversion (convert usage

In SQL Server, convert can convert a date to a different format. The format control is completed by the style in convert (data_type [(length)], expression [, style. The following lists the effects of styles in obtaining various values: In the table, the two columns on the left representDatetimeOrSmalldatetimeConversion to character dataStyleValue. ToStyleValue plus 100 to obtain the four-digit year (yyyy) of the century ). Without century digits (YY) Digital Century (YYYY) Standard Input/Output ** - 0 or 100 (*) Default Value MonDdYyyy hh: miam (or pm) 1 101 USA Mm/Dd/YYYY 2 102 ANSI YY.Mm.Dd 3 103 UK/France Dd/Mm/YY 4 104 Germany Dd.Mm.YY 5 105 Italy Dd-Mm-YY 6 106 - DdMonYY 7 107 - MonDd,YY 8 108 - Hh:Mm: SS - 9 or 109 (*) Default Value + millisecond MonDdYyyyHh: MI: SS: Mmmam (or pm) 10 110 USA Mm-Dd-YY 11 111 Japan YY/Mm/Dd 12 112 ISO Yymmdd - 13 or 113 (*) European default value + millisecond DdMon yyyy hh:Mm: SS: Mmm (24 h) 14 114 - Hh: MI: SS: Mmm (24 h) - 20 or 120 (*) ODBC specifications Yyyy-Mm-DdHH:Mm:SS[.Fff] - 21 or 121 (*) ODBC specifications (in milliseconds) Yyyy-Mm-DdHH:Mm:SS[.Fff] - 126 (***) Iso8601 Yyyy-Mm-DdThh:Mm: SS: Mmm (without spaces) - 130 * Kuwait DdMon yyyyHh: MI: SS: Mmmam - 131 * Kuwait Dd/Mm/YY Hh: MI: SS: Mmmam * Default value (Style0 or 100, 9 or 109, 13 or 113, 20 or 120, 21 or 121) always returns the digital Century (yyyy ). ** When convertingDatetimeInput; output when converted to character data. * ** It is specially used for XML. ForDatetimeOrSmalldatetimeToCharacterData conversion. The output format is shown in the table. ForFloat,MoneyOrSmallmoneyToCharacterData conversion and output are equivalentStyle2. ForRealToCharacterData conversion and output are equivalentStyle1. Important By default, SQL Server interprets two-digit years based on the end year 2049. That is, the year 49 with two digits is interpreted as 2049, while the year with two digits is 50 is interpreted as 1950. Many client applications (such as client applications based on OLE automation objects) Use 2030 as the end year. SQL Server provides a configuration option ("two-digit cutoff year") to change the SQL Server The End year used and the date is consistent. However, the safest way is to specify a four-digit year.   WhenSmalldatetimeWhen converting to character data, the style that contains seconds or milliseconds will display zero at these locations. WhenDatetimeOrSmalldatetimeYou can use the appropriateCharOrVarcharThe length of the Data Type to truncate the date part that is not required. If you want to remove the separator from the string, you can use the replace function. For example, replace (convert (Varchar (30), getdate (), 121 ),'-',''),'',''),':',''),'. ','')

DBCC command details

We know that database integrity and consistency must be ensured in the development and application of database systems.

When a database has a serious error, we suspect that the database is damaged (for example, the drop command cannot be used to delete the database or object, or the "unreliable data" information appears when a table is used ); when the user changes
The server's default sorting order or changing the character set needs to be checked; when the SA performs regular checks on the system; in these cases, we need to use the database consistency check tool (Database
Consistenecy
Checker (DBCC ). DBCC is a practical command set used to check the logical and physical consistency of a database. DBCC is frequently used in development and applications.
Command.

The format of the DBCC command is as follows:

DBCC
(Checktable (table name | table ID ([, skip_ncindex]) |
Checkdb [(database name [, skip_ncindex])] |
Checkalloc [(database name [, fix | nofix])] |
Tablealloc ({table name | table ID}
[, {Full | optimized | fast | null}
[, Fix | nofix]) |
Indexalloc ({table name | table ID}), index ID
[, {Full | optimezed | fast | null}
[, Fix | nofix]) |
Checkcatalog [(Database Name)] |
Dbrepair (Database Name, dropdb) |
Reindex ({table name | table ID}) |
Fix_text ({table name | table ID )}

　
DBCC permissions. For checktable, fix_text and reindex are assigned to the table owner by default. For checkdb, checkalloc,
Checkcatalog, dbrepair, indexalloc, and tablealloc are assigned to the database owner by default. DBO automatically obtains the DBCC command and all
Option. This permission cannot be transferred. In addition, DBCC runs when the database is active, except for the dbrepair option and the DBCC checkalloc with the fix option.

Checktable options

　
Checktable is used to check a specified table, ensure that the index and data page are correctly connected, the index is stored in the correct order, the consistency of all pointers, the rationality of the data information on each page, the page
The rationality of the Offset. If the log segment is on its own (log) device, use dbcc for the syslogs table
The checktable command can report the used and remaining log space, and use the skip_ncindex option to make DBCC
Checktable skips the check of nonclustered indexes on user tables. All indexes are checked by default.

Example 1. Check the log space and unused space:

DBCC checktable (syslogs)

If the log segment is on the log device, the following information is returned:

Checking syslogs
The total number of data pages 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.4 Mbytes, 99.87%.
DBCC execution completed. If DBCC printed error messages,
Contact a user with SA role.

If the log is not on its own device, the following information is displayed:

Notice: notification of log space used/free.
Can not be reported because the log segment is not on its own device.
Example 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 options

The run checkdb option is the same as the checktable check content, but it performs this check for each table in a specified database. If no database name is specified, checkdb checks the current database. The information returned by checkdb is also the same as that returned by checktable.

Checkalloc Option

　
Checkalloc is used to check the specified database and view all the correctly allocated pages and unallocated pages. If no database name is specified, checkalloc checks the current database.
Checkalloc returns the allocated and used space. The default mode of checkalloc is nofix. To use the fix option, you must place the database in single-user mode.

Example:

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 Option

　
Tablealloc checks the specified table to ensure that all pages are correctly allocated. It is the reduced version of checkalloc. Perform the same integrity check for a single table. Use
Tablealloc can generate three types of reports: full, optimized, and fast. The full option is equivalent to the table-level checkalloc; it reports various types
Type allocation error. The optimized option generates a report based on the distribution page listed on the object allocation image (OAM) page of the table. It does not report, nor can it organize the allocation pages that are not listed on the OAM page
No referenced extension (extent ). If no type is specified or null is used, the optimized option is the default setting. The fast option does not generate the allocation report, but generates
An additional report of pages referenced but not allocated in the extension. Fix | the nofix option determines tablealloc.
Whether to sort out the allocation errors found in the table. For all tables, the default value is "fix", except for system tables. The default value is "nofix. To use the fix option for the system table, you must first set the database to a single-use
User mode.

Example:

DBCC tablealloc (titles)
The following information is displayed:
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 Option

Indexalloc checks the specified index to ensure that all pages are correctly allocated. It is a scaled-out version of checkalloc and specifies the same integrity check for a single index. The options are the same as those of tablealloc.

Checkcatalog options

　
The checkcatalog option is used to check the consistency between system tables in the system table. For example, it ensures that each data type in the syscolumns table is in the policypes table
Each table and view in sysobjects should have a description Record about each of them in the syscolumns table.
The Last checkpoint is valid. Checkcatalog also reports any defined segments. If no database name is specified, check the current database.

Dbrepair options

　
Dbrepair (Database Name, dropdb) option is to delete a damaged database. The damaged database cannot be deleted using the drop database Command.
The database can only delete normal databases. When the dbrepair command is executed, no user (including the user executing this command) can use the database being deleted. This option must be
Run in the master database.

Reindex Option

　
Reindex option by running DBCC
The checktable "fast" execution method checks the integrity of the index on the user table. If it detects an index problem, it will delete and recreate the index. In SQL
After the order of the server is changed, the SA or table owner should execute this option. This option cannot be run in a user-defined transaction.

Example:

DBCC reindex (titles)
Response Message: one or more indexes than upt. They will be rebuilt.

Fix_text Option

　
After the character set of SQL Server is changed from single-byte to multi-byte, The fix_text option is used to upgrade the text value. SQL
Changing the character set of server from single-byte to multi-byte makes the management of text data more complicated. Because the text value may be large enough to cover several pages
The server must be able to process (by page constraints) characters that may span pages. To do this, the server needs to add some information on each article page. Sa or table owner must run on each table of text data
Line DBCC fix_text to calculate the new page number.

In short, the information returned by the DBCC command can accurately reflect the status of the database and its various objects. It is a good helper for us to detect the database.