Different organization and individualshave various methods of protecting data and the information that they want tokeep safe. Views: T-SQL View is a table whose content isindicated and defined by a well-created query, and the subset table islogically stored. It appears like a table to the database user with many rowsand columns of data. The T-SQL view is not stored in the database, but it’s aquery that has been stored as an object where SQL creates an illusion of theview (Anley, 2012).
The organization uses them for security purpose in theirdatabase system. Users are restricted from viewing particular columns. Thereforeby using this database system, one can only be allowed to access specified rowsor columns. Example: Lets us think that we have table calledvoters with data of voterid, votername , address, state, SSN Suppose auser want to retrieve the information of the voter but SSN should beconfidential and it should not be seen by others in this case we can create aview which contains all other coulmns expect SSN and give access to see theother data of the voter using a view. CREATE VIEW vtest AS select voterid, votername, address from voterstable. We canretrieve the data from the view using normal sql statements like Select *from vtest where voterid=’12344′; Stored Procedure: Stored procedure hasbeen characterized by users as a de facto standard for particular applicationsthat enable access and manipulation of the particular database through the useof codes. Developers are offered the opportunity to manipulate or couple theset-based SQL power with specific set control codes.
They are built andreferred to as method calls. They offer iterative loops and conditionals forthe developers. Stored procedures allow users to undertake modular programmingand faster execution hence reducing network traffic. They provide improvedperformance because of the few calls that need to be made by the user. Forexample, in case the SQL statement has four Statement, there will be only onecall made for all the statements.
Stored procedure uses parameters. Example of creatingsimple stored procedure without using parameters: CREATEPROCEDURE ShowNumberOfOrdersSELECTC. cid, C. cname, COUNT(*)FROMCustomers C, Orders aWHEREC. cid = O.
cidGROUP BY C. cid, C. cname We can call or execute the stored procedure by using Execute ShowNumberOfOrders Benefits of usingT-SQL ViewMany benefits are enjoyed when one is using T-SQL view system. It offersadequate security to the database. Users are only able to access data that theyhave been allowed to access hence restricting further access. This ensures thatinformation is not manipulated or seen by unauthorized personnel. Secondly, thesystem query is simple to understand and use.
One can draw data from manydifferent tables and form a single table. This means that it is possible toturn multiple table queries into a single table view. Another benefit is thatthe view is structured in a simple manner, where the user can get a set ofvirtual tables. Using visible tables make ones understanding easier and faster. Finally, the T-SQL view offers data integrity and consistency. Data is checkedto ensure it complies with the required integrity minimums.
Benefits of usingstored procedureInthis modern society, many individuals have embraced the use of storedprocedures due to its many benefits received. Some of these benefits include: maintainability, where scripts are located in one area and traceability, ismade more accessible. The system can be tested as an independent part of theapplication. Another benefits received is that the stored procedures have ahigh speed and are optimized.
They are cached on the computer servers, andtheir execution plan is easily reviewable
