Different organization and individuals have various methods of protecting data and the information that they want to keep safe.Views: 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 Suppose a 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.
We can retrieve the data from the view using normal sql statements like Select * 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:CREATE PROCEDURE ShowNumberOfOrdersSELECT C.cid, C.cname, COUNT(*)FROM Customers C, Orders aWHERE C.
cid = O.cidGROUP BY C.cid, C.cname We can call or execute the stored procedure by usingExecute ShowNumberOfOrders Benefits of using T-SQL ViewMany 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 stored procedureIn 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.