There are two types of database objects in SQL Server- system objects and user defined objects.
When you install SQL Server then you find system objects. You get system databases e.g. master, model, tempdb etc. Each of these system databases contains other system objects like system tables, system views, system role etc.
You can create database objects in SQL Server which are called user defined database objects. When you create a database, you find system objects like system tables, system views, system role etc which are used for querying and managing the created database. You can create user defined tables, views, stored procedures, synonyms, functions, triggers, users, roles etc in user defined databases. Mostly we will be concerned with these user defined database objects.
A table is a database object which is used to store data permanently in database. There are different types of tables in SQL Server e.g. temporary table.
A query is a set of statements which is executed at database server. When client sends SQL query to the database server then the query is executed line by line. Every time to run a query, request is sent to database server and then server executes the query. There is time consumption in sending query from client machine to database server to execute the query.
On the other hand a stored procedure is a compiled query which is already stored at the database server. Therefore the execution of stored procedure is faster than compared to a query. A stored procedure is basically a named query which can take parameters as well. There are two types of parameters possible in a stored procedure- input parameters and output parameters. The data type of parameter of stored procedure can be simple data type or complex data type e.g. table data type. Stored procedure can return a simple scalar value or complex data like table.
SQL Server database provides system stored procedures which can be used to perform different kind of operations such as
- Listing all the users logged on to a server
- Listing all the tables or views in a database
- Configuring the server
- Sending email from database
- Querying different objects of database
A function is a reusable piece of code in programming. In SQL Server we have user defined function for the same purpose. A function in T-SQL is a reusable set of statements that can be executed again and again as per the need. To create functions we use CREATE FUNCTION statement. This statement is followed by a function name and then some parameters inside the square bracket. It has RETURNS clause after parameter list, which defines what will be the return type of the function. The value that is returned by the function is given using return statement beginning with RETURN keyword.
Trigger is a special type of stored procedure. Unlike stored procedure, it is not executed by user. Trigger is executed by database server under certain conditions. Trigger is part of event based programming.
There are three possible types of trigger based on operation performed on a table. When data is inserted into a table, insert trigger runs. When data is deleted from a table, delete trigger runs. When data is updated into a table, update trigger runs.
Trigger is useful to schedule some task of database server because data are automatically inserted deleted or updated in tables.
Database Rule is a predefined set of permissions granted to user. Database rules are database objects which are designed for improving performance and security purposes. For example in a company there are several employees with different roles such as sales personnel, accounting personnel, marketing personnel etc. So, a rule is defined in SQL Server according to business rule. Depending on the role a business entity is allowed permission to perform some operations on a database object.
SQL Server supports ownership and security of data. There are several entities available for this purpose in SQL Server. They are as follows
- Logins
- Users
- Rules
- Owners
- Permissions
Logins are the accounts through which users connect to SQL Server. Username and password are used to create a login. Username and password of login is stored at SQL Server.
User refers to an identity inside a specific database. Login can map to different users in different databases.
Owner is basically used to create full naming schema for SQL Server objects.
No comments:
Post a Comment