A SQL Server Database is an organized collection of data and supporting data structure & tools to manage it. The data is stored in a series of tables. A database does not only contain the data but also contains
- Tools to easily access, manage, modify, update and organize the data
- Register & Manager users
- Enforce data security
- Monitor Performance
- Maintain data integrity
- Deal with concurrency control
- Recover data when the system fails
Table of Contents
Database Objects
A Database much more than a simple table. The following are some of the important objects of the SQL server database
- Users & Roles
- Schemas
- Tables
- Indexes
- Constrains
- Triggers
- Views
- Stored Procedures
- Functions
- TSQL
Users & Roles
The SQL Server follows Role-based security. We assign Permissions to roles instead of users. The users are assigned to various roles. It also comes with several built-in fixed server roles. These permissions play a part in keeping SQL Server secure.
Schema
Schema is the logical container or namespace for the objects in the database. It allows us to group objects into separate namespaces. For Example the database objects like tables, views, stored procedures etc might belong to schema’s like production, sales, HR etc
The following is the Simplified view of an SQL Server database. it contains two schema. dbo & Sales. Each of these schema’s contain tables, views, functions, procedures etc.
A Schema must have a database user as an owner. A Schema owner owns everything that schema owns. And he has full control over the schema. You can any time change the schema owner or move objects from one schema to another
Tables
Tables stores the data in the form of rows and columns. A Table can have many such rows. A typical database can contain lots of such tables. The following is the Employee table from the AdventureWorks sample database
Indexes
The indexes makes the retrieval of the data faster. Without index, the database engine will have to scan the entire row for each query.
An index is built from a key. The key consists of values from or more columns in the table or view. SQL Server stores these keys in a index file and in a B-tree structure. The SQL Server searches the index to find the row for an associated with the key.
Constraints
The Constrains specify the rules & restrictions that we define on the data. The SQL Server enforces these rules when user inserts , updates or deletes the data. If the row does not honor the constraints then the SQL Server rollbacks the changes. The Constrains are important to maintain the integrity of the data
In SQL Server database, you can define Constraints like Check Constraint, Default Constraint, Unique Constraint, Primary Key Constraint, Foreign Key Constraint, etc
Triggers
Triggers are special procedures that get triggered when an event happens. The event can be an inserting a record into the table, creating a new table or a user login.
There are three types of triggers. DML Triggers, DDL Triggers & Logon Triggers.
The Data manipulation language (DML) triggers fires when the user inserts, Updates, or deletes an record(s).
The Data definition language (DDL) triggers fires when the user creates, alters, or deletes an table, index, etc.
Logon triggers which fire in response to LOGON events
Views
Views are virtual tables that hold data from one or more tables or from other views. They are nothing but a set of queries and do not contain any data. The SQL Server stores it as a query. Whenever the user requests for the view, the server executes the query and returns the data.
The View adds a layer of abstraction over existing tables. They are useful in securing the data, hiding the implementation details, can have data from multiple tables, etc.
Stored Procedures
A Stored Procedure is a collection of T-SQL code or SQL Querries. The stored procedures can accept parameters and return results. Both are optional. Once we create a stored procedure, we can execute whenever we want. They are very useful in storing a business logics, repetitive tasks or perform some DBA tasks, etc
Functions
A Function accepts parameters that are optional but it must return a result. The returned results can be a table or a single value.
TSQL
TSQL is Microsoft’s Implementation of Structured Query language or SQL for use in SQL Server Database.
The SQL is a structured query language used for the purpose of inserting, updating, deleting & querying data from the tables. it is the standard language designed by the ANSI (American National Standards Institute).
Summary
Here we learnt What is SQL Server Database and some of the important objects in the SQL Server database.
