Features/ Advantages of SQL

Stored Procedures

The one main advantage of using SQL is the use of stored procedures. Stored procedures are lines of code

that are called by the application. They are placed on the server, and they are pre-compiled for quicker

response times. Stored procedures require the knowledge of SQL Server syntax, which is called T-SQL

(transaction SQL). The use of stored procedures also centralizes code, so troubleshooting bad database

requests can be observed by a database administrator.

 Scalability

The term scalability is used to describe the ability to grow when the business becomes bigger. When

businesses grow quickly, a small database application like Access can be a bottleneck for a website or

desktop software. Microsoft SQL Server is quick for large and small businesses, so as the business grows,

the SQL Server can handle the new volume of database requests. SQL Server can handle millions of records

and transactions.

 Security

Security is a major issue for any site. SQL Server allows the administrator to grant access or deny access for

users. The SQL Server has a specific section of the application where users are added to the permissions.

SQL Server allows administrators to specify which tables and stored procedures users are able to access and

query. This limits what records and user information can be queried, which protects the business's customer

information.

 Transaction Logs

Transaction logs are objects on the SQL Server that record the retrieval, update and deletion of records.

There are two reasons to keep transaction logs. The first is for rollback procedures. This process is used for

accidental updates or deletions. The administrator can return records back to the original data by using

transaction logs. Secondly, the transaction logs can be used for security purposes. If the administrator

suspects a breach of security, he can watch the transaction logs for any type of data retrieval and identify the

severity of the breach.

 Automatic Backup

SQL Server has an automatic backup option. The SQL Server automatically saves a copy of the database

and the transaction logs on another hard drive or media like a CD-ROM or a DVD. Small applications like

Access do not have this option, and backups are an integral part of disaster recovery. SQL Server also has

procedures that allow the administrator to quickly restore a database when data is lost or corrupted, or the

server has a hard drive crash.

SQL Components

SQL consists of three components:

1. Data Definition Language (DDL)

2. Data Manipulation Language (DML)

3. Data Control Language (DCL)

The Data Definition Language (DDL). This component of the SQL language is used to create and modify

tables and other objects in the database. For tables there are three main commands:

CREATE TABLE table name to create a table in the database

DROP TABLE table name to remove a table from the database

ALTER TABLE table name to add or remove columns from a table in the database

The Data Manipulation Language (DML) component of the SQL language is used to manipulate data

within a table. There are four main commands:

SELECT to select rows of data from a table

INSERT to insert rows of data into a table

UPDATE to change rows of data in a table

DELETE to remove rows of data from a table

The Data Control Language (DCL) This component of the SQL language is used to create privileges to

allow users access to, and manipulation of, the database. There are two main commands:

GRANT to grant a privilege to a user

REVOKE to revoke (remove) a privilege from a user