Database Console Commands for SQL-SERVER
what is DBCC for sql-server ? what are the important Statements or commands that exits in this DBCC ? First of all we will know what is DBCC and where it is Useful in Sql ? and one Important thing is How to reset your Auto Increment of a column Property with this. We will discuss here with Code Snippets
what is Database console Commands for sql-server ? what are the important Statements or commands that exits in this DBCC ? First of all we will know what is DBCC and where it is Useful in Sql ? and one Important thing is How to reset your Auto Increment of a column Property with this. We will discuss here with Code Snippets Introduction of DBCC :
Database console Commands in short we call it as (DBCC) Transact SQL a series of Commands in programming Language to check physical and logical of your database. Uses of DBCC :
Many DBCC statements can fix defected problems for ex :- Dbcc is used to find out why the table or the index is heavily fragmented. Table Fragmentation occurs when a large number of insert and update operations performed in the table.
It can be used as to reseed the auto increment column it is newly interoduced in Sql-server 2008 and advance of SQL -SERVER VERSIONS
Commands to achieve DBCC :
There are several Commands in DBCC . I am Describing the Most Popular ones..
1) DBCC SHOWCONTIG
Syntax
DBCC SHOWCONTIG(TABLENAME [,INDEX NAME])
in the above statement to view the fragmentation information for the table for ex :
DBCC SHOWCONTIG('MASTER')
It will tell the Page scanned, Extents scanned , Extent switches , Avg. Pages per extent , Extent scan Fragmentation, Avg. Bytes for free per page , avg. page density (full). DBCCINDEXD FRAG :
Syntax :
DBCC INDEXDEFRAG [{Database_name | database_id | 0)
This command is useful to defragment Clustered and Secondary indexes of the specified table or view
DBCC INDEXDFRAG (Pubs, Titleauthors, idxtitleauthors)
if you want to use the current database you can use it 0.DBCC CHECKINDENT
Now the Important Command is reset auto increment value or without resetting the Auto increment value
Scenario :
suppose i have a table with auto increment Column suppose i delete all the rows than my Auto increment id value will reset to its original value when we want insert the new values in to the table.
Syntax :
DBCC CHECKINDENT(tablename , [ {noressed | { reseed [, new reseed_value] } } ]) [with no_INFOMSGS]
Dbcc CHECKINDENT('authors', reseed ,1);
Dbcc CHECKINDENT('Authors',noreseed );
dbcc checkIndent('Authors');
dbcc CheckIndent('Authors', Reseed, newreseedvalue);
Note :
This CheckIndent will be useful in Sqllite database and in Mysql database.
Follow