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.


Article by srirama
A Good advice from parent to a Child , Master to a Student , Scholar to an Ignorant is like a doctor prescribed pill it is bitter to take but when they take it will do all good for them --- Bhushan

Follow srirama or read 73 articles authored by srirama

Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: