WHAT IS A DATABASE?

A database is an organized collection of data, generally stored and accessed electronically from a computer system. Where databases are more complex they are often developed using formal design and modeling techniques

TYPES OF DATABASE

Four types of database management systems

A relational database management system is one of four common types of systems you can use to manage your business data. The other three include:

• hierarchical database systems
• network database systems
• object-oriented database systems

Hierarchical database model resembles a tree structure, similar to a folder architecture in your computer system. The relationships between records are pre-defined in a one to one manner, between ‘parent and child’ nodes. They require the user to pass a hierarchy in order to access needed data. Due to limitations, such databases may be confined to specific uses.

Network database models also have a hierarchical structure. However, instead of using a single-parent tree hierarchy, this model supports many to many relationships, as child tables can have more than one parent.

Finally, in object-oriented databases,the information is represented as objects, with different types of relationships possible between two or more objects. Such databases use an object-oriented programming language for development.

NoSQL or non-relational databases

A popular alternative to relational databases, NoSQL databases take a variety of forms and allow you to store and manipulate large amounts of unstructured and semi-structured data. Examples include key-value stores, document stores and graph databases.

WHAT IS DATABASE CONSOLE?

Query or database consoles are SQL files in which you can compose and execute SQL statements. Unlike scratch files, consoles are attached to a data source. You can use a query console in the same way as you use a terminal. Just type and run your code.

When you create a data source, a query console is created automatically. If necessary, you can create additional consoles. Each console creates a new connection. If you do not want to create new connections, enable single connection mode..

Working with console files

Create a query console

To create a console, use one of the following actions in the Database tool window (View | Tool Windows | Database ).

• Click a data source and select File | New | Query Console.
• Right-click a data source and select New | Query Console.
• Click a data source, press Alt+Insert, and select Query Console.
• Click a data source, press Ctrl+Shift+F10, and select New Query Console.

New console means a new connection to a data source. You can work with just one connection for the data source and have all consoles use that same one connection. Read more about the single connection mode in Single Connection Mode.

Open a console

When a console is open, you can write SQL here, run it and get the result.

• In the Project tool window (View | Tool Windows | Project ), navigate to Scratches and Consoles | Database Consoles | <data_source_name>, double-click a console file.
• In the Database tool window, click the Jump to Query Console icon () and select a console that you want to open or create a new one.
• Click a data source, press F4, and select the console that you need.

Rename a query console

1. In the Project tool window (View | Tool Windows | Project ), navigate to Scratches and Consoles | Database Consoles.
2. Expand a data source group that includes your console.
3. Right-click a console file and select Refactor | Rename.

Group consoles under the data source directory

1. In the Project tool window (View | Tool Windows | Project ), navigate to Scratches and Consoles | Database Consoles.
2. Right-click a data source group where you want to create a new directory, select New | Directory.
3. Specify a name of the directory.
4. Select and drag console files to the directory.
5. In the Move dialog, click OK.

Delete a query console

1. In the Project tool window (View | Tool Windows | Project ), navigate to Scratches and Consoles | Database Consoles.
2. Expand a data source group that includes your console.
3. Right-click a console file and select Delete.

Copy a console file to a new location

1. In the Project tool window (View | Tool Windows | Project ), navigate to Scratches and Consoles | Database Consoles.
2. Expand a data source group that includes your console.
3. Right-click a console file and select Refactor | Copy File.
4. In the To directory field, specify a new location for the file.

Configuring console settings

Modify console settings

• Open a console and click the Data Source Properties icon ( ).

Configuring console dialects

Change the SQL dialect in the editor

• Right-click the editing area of the input pane and select Change Dialect (<current_dialect>). In the Change SQL Dialect window, select a dialect.Note that the selected dialect is applied to all the data sources that were created with the current driver. For example, let’s say that you have several data sources that use the PostgreSQL driver. If you change the dialect to Greenplum in one console, all the consoles and data sources that use the PostgreSQL driver will switch to the Greenplum dialect.Gif

By default, the SQL console dialect is defined by a data source that is attached to the console.

Disable syntax error highlighting

• Right-click the editing area of the input pane, select Change Dialect, and select Generic SQL. The Generic SQL dialect supports completion and highlighting for SQL keywords, table and column names. Syntax error highlighting is disabled, that is all statements in a query console are shown as syntactically correct).

The change of the default dialect will influence all consoles and data sources that use the current data source driver.

DATABASE CONSOLE COMMANDS

DBCC stands for Database Console Commands (DBCC) or Database Consistency Checker. DBCC commands are used for  troubleshooting purposes. Microsoft SQL Server has introduced many DBCC commands, and there were a  few in Version 2000, 2005 and so on. With each version, they are adding new DBCC commands and these DBCC commands are helpful when you use them. When you start using them more often, you will notice how these commands are useful. 

Let’s Start

• Open SSMS (SQL Server Management Studio)
  
  
  
  Connect SQL Server and select TEST database, where you want to try those DBCC commands.
  
• DBCC help (‘?’)
  
  
  
  We will start from DBCC Help, as you can see in the bottom all DBCC Commands are displayed there.
• DBCC TRACEON
  
  
  
  In Sp_who2, we will see SPID and just type DBCC TRACEON (SPID),
  
  
  
  To set Trace off, just type DBCC TRACEOFF (SPID).
  
  
  
  If there is any DLL, that’s accumulating memory, like stored procedures and so on.
  
• DBCC dllname (FREE)
  
  
  
• CHECKTABLE
  1. USE DATABASENAME  
  2. DBCC CHECKTABLE (‘TABLENAME’)  It checks the pages in that table and its integrity constraints.
     
     
     
• CHECKIDENT
  1. USE DATABASENAME  
  2. DBCC CHECKIDENT (‘TABLENAME’)  
     
     As you see in the screenshot above, it checks the integrity value and the column value of that table.
     
• CHECKDB
  1. USE MASTER  
  2. DBCC CHECKDB (‘DATABASENAME’)  
     
     
     
     
     
     You will see a detailed description which is in that database.
     
• CheckFilegroup
  
  
  
  It gives a detailed description of allocation, tables, rows, indexes, views and so on.
  
• CHECKALLOC
  
  
  
  It checks for the allocation and disk space of that database.
  
• DBCC Inputbufer
  
  
  
  DBCC inputbuffer will give you a detailed information, which query is running; not just in parameter but SPID.
  
• CLEANTABLE
  1. DBCC CLEANTABLE(DATABASENAME,’TABLENAME’,0)  
     
     Cleantable clears the space in the table.
     
• DropcleanBuffers
  
  
  
  Removes all buffer from the pool.
  
• DBCC FREEPROCCACHE
  
  Delete all the elements in procedure cache,
  
  
  
• DBCC SHRINKDATABASE
  
  
  
  It shrinks the database, but you have to specify the size of a log; how much you want to shrink in MB.
  
• DBCC SHRINKFILE
  
  
  
  You have to specify the log file of the database and it will shrink log file again in MB.
  
• DBCC SQLPERF(LOGSPACE)
  
  
  
  It will show the log size of each database in MB and log space used.
• DBCC OPENTRAN
  
  
  
  This is used to find out the transactions that are running in the database. This is mostly used to find the oldest transaction and kill it .
  
• DBCC SHOW_STATISTICS
  
  
  
  This command is used to tune a query or tracing is done but you have to the pass table name and an index name .
  
• DBCC UPDATEUSAGE
  
  
  
  This command is used to update data and space in sysindexes table and clears the space.

Conclusion

This sums up DBCC Commands in SQL Server, its uses and where we can use these commands.