Different organization and individuals
have various methods of protecting data and the information that they want to
T-SQL View is a table whose content is
indicated and defined by a well-created query, and the subset table is
logically stored. It appears like a table to the database user with many rows
and columns of data. The T-SQL view is not stored in the database, but it’s a
query that has been stored as an object where SQL creates an illusion of the
view (Anley, 2012). The organization uses them for security purpose in their
database system. Users are restricted from viewing particular columns.
Therefore by using this database system, one can only be allowed to access
specified rows or columns.
Example: Lets us think that we have table called
voters with data of voterid, votername , address, state, SSN
user want to retrieve the information of the voter but SSN should be
confidential and it should not be seen by others in this case we can create a
view which contains all other coulmns expect SSN and give access to see the
other data of the voter using a view.
CREATE VIEW vtest AS select voterid,votername,address from voterstable.
retrieve the data from the view using normal sql statements like
from vtest where voterid=’12344′;
Stored Procedure: Stored procedure has
been characterized by users as a de facto standard for particular applications
that enable access and manipulation of the particular database through the use
of codes. Developers are offered the opportunity to manipulate or couple the
set-based SQL power with specific set control codes. They are built and
referred to as method calls. They offer iterative loops and conditionals for
the developers. Stored procedures allow users to undertake modular programming
and faster execution hence reducing network traffic. They provide improved
performance because of the few calls that need to be made by the user. For
example, in case the SQL statement has four Statement, there will be only one
call made for all the statements. Stored procedure uses parameters.
Example of creating simple
stored procedure without using parameters:
C, Orders a
GROUP BY C.cid, C.cname
We can call or execute the stored procedure by using
Benefits of using
Many benefits are enjoyed when one is using T-SQL view system. It offers
adequate security to the database. Users are only able to access data that they
have been allowed to access hence restricting further access. This ensures that
information is not manipulated or seen by unauthorized personnel. Secondly, the
system query is simple to understand and use. One can draw data from many
different tables and form a single table. This means that it is possible to
turn multiple table queries into a single table view. Another benefit is that
the view is structured in a simple manner, where the user can get a set of
virtual tables. Using visible tables make ones understanding easier and faster.
Finally, the T-SQL view offers data integrity and consistency. Data is checked
to ensure it complies with the required integrity minimums.
Benefits of using
In this modern society, many individuals have embraced the use of stored
procedures due to its many benefits received. Some of these benefits include:
maintainability, where scripts are located in one area and traceability, is
made more accessible. The system can be tested as an independent part of the
application. Another benefits received is that the stored procedures have a
high speed and are optimized. They are cached on the computer servers, and
their execution plan is easily reviewable.