Types of SQL Server Commands

SQL commands are used as an instruction to communicate with the database to perform specific tasks like create database, delete, update records, alter etc. These commands perform different types of task to execute within the database. With the help of these commands user can create different objects. There are four types of commands that are categorized given below according to their importance or role in database.

DDL (Data Definition Language): These statements are used to define the structure or schema of the database. For example:

• Create: used to create objects in the database
• Alter: used to alter or change the database structure
• Drop: used to delete objects in the database
• Rename: used to rename the object in the database
• And truncate, comment etc.

DML (Data Manipulation Language): These statements are used to manipulate or manage data within schema objects. For example:

• Select: It is used to get back or retrieve data from the database
• Insert: It is used to insert data into table.
• Update: It is used to update existing data in the table.
• Delete: It is used to delete records from the table.
• Merge: Used to merge insert or update operation.

DCL (Data Control Language): These statements are used to control access to the data within the database. For example:

• Grant: It is used to give user access to the database.
• Revoke: It used to release or drop user access to the database by grant command.

TCL (Transaction Control Language): These statements are used to manage changes made by DML statements and helps to group statements in logical transactions. For example:

• Commit: Used to save process.
• Rollback: Used to rollback or restore the transaction to the previous state of commit.
• Savepoint: Used to save a point in transaction which can be further executed.
• Set Transaction: Used to set transaction level.

Read More

An Overview of the SQL Server System Databases

The endowment of Microsoft’s extensive data storage platform, SQL server carries out breakthrough efficiency in storing and maintenance of data. It is a highly featured database that is primarily designed to cope with the business environment. The SQL server supports ANSI-SQL and T-SQL query languages. The query languages are used to manage and retrieve data in any Relational database Management system.

Moving to the Database Concept in SQL

A Relational Database System like SQL server consists of many objects. Among them, the database is considered as the most significant-level object. A database is defined as a structured group which includes objects such as table, views, stored procedures related to the data that is stored in the tables of database. SQL server’s powerful RDBMS architecture deployment supports the storage of data in four system databases.

1. master
2. model
3. msdb
4. tempdb

View all these databases in SSMS (SQL Server Management Studio)

Let us discuss the role and details of data stored by each of the database in SQL server.

Master Database

As the name implies, the master database forms the primary database of SQL server. It maintains a set of system tables which keeps a complete track of the overall system. The master database contains the following list of information.

• Details of SQL server configuration settings
• Login information details of the account
• Transactional locks and processes
• Regarding registrations related to server
• Logins from remote sites

Path Location of Master Database Files on System

On installing SQL server, the master.mdf files and its corresponding log files (master.ldf) is created in the location by default

If the master database gets corrupted and is not recoverable from the backup, then a user has to again rebuild the master database. Therefore, it is always advised to maintain a current backup of the master database. As, everything crucial to SQL server is stored in the master database, so it cannot be possibly deleted. For this reason master database is often referred as mother or heart of all SQL Server.

Model Database

The model database sets a model for the entire newly created database. Technically, it serves as a template for the SQL server in order to create a new database. Whenever a user tends to create a new database, the data present in model database are moved to new database to create its default objects which include tables, stored procedures, etc. Primarily, the requirement of model database is not specific to creation of new database only. Whenever the SQL server initiates, the Tempdb is again created by using model database in the form of a template. The Model database does not contain any data by default.

Locating the Model Database Files

During the installing process of SQL server, the model database file and its related log file (model.ldf) are saved in in the same location as for master file.

MSDB Database

The Microsoft database (MSDB) is used by SQL server Management Studio, SQL Server Agent and SQL server, to store data related to backup and restores history and information pertaining to task scheduling. Additionally, the MSDB database stores data pertaining to log shipping, replication and maintenance etc.

List of Data Which Are Present in the MSDB Database

• Information associated with SQL Server Agent like job history, scheduling of jobs, notifications, etc.
• Configuration data about log shipping
• Maintenance data related to backup created online
• Configuration details about replication

As the volume of configuration related information keeps on increasing in the msdb database, it is always recommended to back it up at regular intervals. In the event msdb database getting corrupted, a user has to rebuild the database by making use of SQL build script which was set up during the installation procedure of SQL server on the system.

Location of SQL Build Script

The SQL build script for rebuilding MSDB database is instmsdb.sql and it is present in path. C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Install

When the msdb database is not present, the SQL build script will first ensure to again create the database and then rebuild the associated objects. A user can carry out the processing of instmsdb.sql script with the help of Query Analyzer application. After successfully rebuilding the msdb database, a user has to again configure the configuration settings associated with log shipping, replication and task scheduling.

Tempdb Database

From its name temporary, it refers that Tempdb database serves as a temporary storage place for processing of data such as tables or temporary stored procedures with their respective objects. It can be said that the tempdb works as a resource for users who are working on a particular instance of SQL server. The tempdb database stores the following list of data

• Temporary cursors, variables, tables and stored procedures etc
• Objects that are handled by SQL database engine to carry out jobs like sorting
• Row versions which are created during the transaction pertaining to modification of data

Location of Tempdb files

The tempdb.mdf files and tempdb.ldf can be located on the same path for Master file

Initially the tempdb file is set to a size of 8.0MB but a user can further increase its size by making use of the database properties or by initiating the command ‘Alter DATABASE’ for removing the processing overhead.

Instances Of Corruption in Database

Often the database of SQL database gets corrupted or damaged by corruption issues and in such cases if there is no proper backup of the database, then a user has to depend on third party solution to get access to the data that is stored in the database of the server. Learn more about database corruptions here

Read More

How to configure SQL Server Database Mail in 2014

SQL Server database mail keeps ail copies of the sent or outgoing email messages and user can see them in the MSDB database views section like sysmail_allitems, sysmail_unsentitems, sysmail_faileditems and sysmail_sentitems etc. User can view the sysmail_event_log for log details and to check the email message status run the query against this view in query editor. In SQL Server database mail, it is possible to add multiple user profiles at the same time and their requirement will be according to the need.
Follow Simple Manual Steps to Configure Database Mail

1) Open SSMS and go to the Management node on left panel.

2) Expand the Management node then go to the Database Mail option, Right click on the Database Mail, and then select Configure Database Mail.

3) After that a new pop-up Windows will be opened then click on the next button.

4) New Windows will show options where user have to choose 1^st one i.e. Setup database mail then click on next button.

5) On the next Window fill up the profile filed as your name, description field is not mandatory then click on Add button.

6) New Window will be shown as below, fill up all the fields as below:

Here I have used my managed account which is same as new, so fill up Outgoing SMTP fields

• Email address- your email address
• Display Name- your display name
• Reply email – same as above email address
• Server name – smtp.gmail.com (here I have used gmail smtp address) and Port no- 587

Select Authentication- Basic Authentication, type username, email passwords etc.
After all fields completion, click on the next button.

7) New Window will show profile name and on its next row select yes to default profile. Then click on next.

8) Next Window will show system parameter, view them and click on Next button.

9) New Window will show status of the created profile. Then click on the finish button.

10)  At last window will show success status of the account then click close.



11) To check the mail database, right click on the database mail then go to Send Test E-Mail

12) Then type email address on To field then message, then click on send email button.

Read More