A SQL database is used to store and retrieve data. The database is housed in a database server and largely controlled by a database management system. All SQL databases, whether they are MS SQL Server, MySQL, Oracle, or Progress have several components in common. They are:
- Tables
- Indexes
- Views
- Stored Procedures
- Triggers
It is these various pieces that are used to house, retrieve, and process data with the SQL database.
SQL Database Tables
Tables are used to store data within the database. They are its main component and without them, the database would serve little purpose. Tables are uniquely named within a database. Many operations, such as queries use these names. Typically, a table is named to represent the type of data stored within.
For example, a table holding employee data may be called Employees. A table consists of rows and columns. The columns are defined to house a specific data type, such as dates, numeric, or textual data. Each column is also given a name. Continuing with our example, an employee’s name may be defined in the table as two columns as FirstName and LastName.
Indexes
Indexes are used to make data retrieval faster. Rather than having to scan an entire table for data, an index allows the database to, essentially, directly retrieve the data being asked of it. An index consists of keys, which in most cases directly relate to columns in a table.
For example, we could create an index using FirstName and LastName to make it quicker to look up employees by their name. One common property of an index is uniqueness. If an index is unique, then it can only contain unique values for its defined keys. In our employee example, this wouldn’t be practical, as a company may have more than one John Smith working in it; however, it would make sense to create a unique index on employee number.
Views
Relationships between SQL database tables can become quite complicated as data is stored in separate tables. Views help combat this issue by allowing the database administrator to create “canned” or pre-built queries that developers, report writer, and users can use in their own database queries. In this way, the view hides some of the database complexity. This makes it easier to read queries; however, danger does lurk in this as it can be easy to forget the amount of processing a view represents.
Stored Procedures
There are many situations where queries alone are insufficient to solve a problem. In these cases, developers rely on programming languages to process logic, to loop through records, and perform conditional comparisons as required. These programs can be stored in the SQL database as stored procedures.
The language used to create the stored procedures are vendor specific. T/SQL is the language used by Microsoft SQL Server; whereas, PL/SQL is used by Oracle. In each case, the language provides the same basic abilities, such as being able to move record by record through a query, perform if-then logic, and call special built in functions to assist with complicated calculations.
Triggers
Triggers are special instructions that are executed when important events, such as inserting or updating records in a table happen. The most common triggers are Insert, Update, and Delete triggers. Two items define a trigger on a table: a stored procedure and an event, such as inserting a record that invokes its execution.
In our employee example, we may want to keep track every time an employee record is updated. To do this, you could create an update trigger which calls a stored procedure to update the affected row’s modification date. Triggers are useful to ensure that data is updated consistently. You don’t have to rely on the user or program that originally modified the employee to also modify the date.
Remember! I want to remind you all that if you have other questions you want answered, then post a comment or tweet me. I’m here to help you.
The post What are the Major Part of a SQL DB? appeared first on Essential SQL.
