Views and table objects. Representation
SQL-performance(SQL view) is a virtual table made up of other tables or views. A view does not have its own data, but rather aggregates data from tables or views that are included in it. Views are created using SELECT statements and have many uses:
With their help, you can hide individual columns or rows from access. This is a security mechanism that allows users to access data through views, but does not grant them permission to directly access the underlying tables.
They allow you to display calculated columns and hide complex SQL statements to simplify and customize how each user perceives information in the database.
Views also provide a layer of abstraction between the data processed by the application and the actual data contained in the tables. The following provides examples of each of these uses and discusses some other uses of views.
Sql indexes
Index(English index) - a database object created to improve the performance of data retrieval. Tables in a database may have a large number of rows that are stored in random order, and searching for them based on a given criterion by sequentially scanning the table row by row can be time consuming. An index is formed from the values of one or more table columns and pointers to the corresponding table rows and thus allows you to search for rows that satisfy the search criteria. Speeding up work using indexes is achieved primarily due to the fact that the index has a structure optimized for search - for example, a balanced tree.
Triggers
Triggers are techniques that can be used to ensure the integrity of a database even when it is being used by multiple applications.
Trigger - This is a special type of stored procedure that is automatically executed whenever an attempt is made to change the data it protects. Triggers ensure data integrity by preventing unauthorized or incorrect changes to data.
Let's say that the database has tables connected through a field Stop. Name. For example, this could be a table of city transport stops and routes. It makes sense to define a trigger that, whenever an attempt is made to delete a stop entry, will check for the presence of routes passing through that stop and allow the entry to be deleted only if there are none.
Triggers do not accept parameters and do not return values. They are executed implicitly, that is, the trigger is fired only when there is an attempt to change the data.
Stored procedures
Stored procedure - it is a sequence of compiled Transact-SQL statements stored in the SQL Server system database. Stored procedures are precompiled, making them more efficient to execute than regular queries. Stored procedures run directly on the server and fit well into the client-server model.
There are two types of stored procedures: system and user.
System Stored Procedures are designed to obtain information from system tables and perform various service operations and are especially useful in database administration. Their names begin with sp_ (stored procedure).
Custom Stored Procedures created directly by developers or database administrators.
The usefulness of stored procedures is determined primarily by the high (compared to ordinary T-SQL queries) speed of their execution. In addition, they are a means of systematizing frequently performed operations. When executing a stored procedure for the first time, there are a number of steps that can be distinguished.
The procedure is broken down into individual components by an expression lexical analyzer.
Components that reference database objects (tables, indexes, views, etc.) are mapped to these objects, first checking their existence. This process is called oncelink solution.
The syscomments system table stores the source text of the procedure, and the sysobjects table stores its name.
A preliminary query execution plan is created. This preliminary plan is called normalized plan or query tree and is stored in the sysprocedures system table.
The first time a stored procedure is executed, the query tree is read and finally optimized.
This scheme makes it possible for repeated calls to avoid wasting time on parsing, resolving links, and compiling the query tree. And on subsequent calls, only the fifth step is performed. Moreover, the plan of the stored procedure after the first execution is contained in the high-speed procedural cache. This means that during a procedure call the speed of reading it will be very high.
Using stored procedures has a number of additional advantages.
1. Stored procedures allow you to separate rules into a separate structure. These rules are then used by many applications to form an error-tolerant data interface. The benefit of this approach is that you can change the rules only for a separate part of the database objects, and not for all of its applications.
2. The use of stored procedures significantly improves query performance, but the greatest increase is achieved when performing repetitive operations when the query plan is constantly stored in the system cache.
3. Stored procedures can accept arguments when run and return values (as result sets).
4. Stored procedures can be run according to a schedule (in automatic execution mode) specified when SQL Server starts.
5. Stored procedures are used to retrieve or modify data at any time.
6. Stored procedures, unlike triggers, are called explicitly. That is, when directly accessing a procedure from an application, script, package or task.
Stored procedures are a powerful data processing tool. System stored procedures play a very important role in database administration and maintenance. Custom stored procedures are used to solve almost any problem. In addition, a user can be given the right to execute a stored procedure even if he does not have access rights to the objects that the procedure accesses.
Representation
Removal
Renaming
Change
Managing Stored Procedures
To change an existing procedure, use the ALTER PROC statement; the parameters of this command are similar to the parameters of the command to create a procedure.
To do this, you need to use a special system stored procedure:
sp_rename 'ObjectName' 'NewObjectName'.
To delete a stored procedure, use the Transact‑SQL command:
DROP PROC ProcedureName.
Performance(View) is another object that makes up the logical structure of any database. The end-user view looks like a table, but does not contain data, but only represents it. The physically represented data is located in various database tables.
The view is implemented as a saved query, on the basis of which a selection is made from various database tables.
Views have the following benefits:
· ensure the confidentiality of information, because allow you to display only the necessary information, hiding certain fields;
· simplify the presentation of data, because the user works with the view as a single table, which is created based on data selection from several tables;
· manage data access rights, for example, instead of granting permissions to execute queries on certain table fields, it is easier to allow queries to be executed through a view.
MS SQL Server provides various ways to create views: using Transact-SQL tools and in the administration utility Management Studio.
To create a view, use the CREATE VIEW command, and role members have the right to execute it sysadmin, db_owner, db_dlladmin:
CREATE VIEW ViewName [(field [,...n])]
QuerySamples
When specified ViewName it is necessary to adhere to the previously defined rules for naming objects; also, this name should not coincide with the name of an existing table in the database. Parameter WITH ENCRYPTION defines the encryption of the request code and ensures that users cannot view or use it.
QuerySamples is a SELECT statement whose parameters determine the contents of the view. View field names are specified either using aliases in the select statement or specified in a parameter field.
For example, let's create a view containing only the following information about the company's employees AdventureWorks , such as: employee’s position and login, date of birth.
CREATE VIEW InfoEmployees ([Number], [Last Name], [Date of Birth]) AS
SELECT BusinessEntityID, JobTitle + "(" + LoginID + ")",
CONVERT (char(10), BirthDate, 104)
FROM HumanResources.Employee
To view the contents of the projection, the following query is executed:
SELECT * FROM InfoEmployees
Using this view, access to some fields of the source table is limited Employee, in this case they say that the table is superimposed vertical filter, i.e. Access to some table fields is limited without column-level protection.
If a condition for selecting records is defined in the selection query code, then the table is said to be subject to horizontal filter. For example, the following view provides access to information about manufacturers that have online ordering services:
CREATE VIEW OnlineVendors
FROM Purchasing.Vendor
WHERE PurchasingWebServiceURL IS NOT NULL
A SELECT command of any complexity may be specified in a selection request, but it is prohibited to use sections ORDER BY, which can later be used when retrieving data from the created view. It is also recommended that you only create views based on tables that have an inner join.
For example, let's create a view that displays the total cost of each order, indicating the customer and his number:
CREATE VIEW InfoOrders
SELECT FirstName + " " + LastName as [Company Name],
SalesOrderHeader.SalesOrderID as [Order number],
Convert (money, sum(UnitPrice*OrderQty*(1-UnitPriceDiscount)),0) as [Total]
FROM (Person.Contact INNER JOIN Sales.SalesOrderHeader
ON Contact.ContactID=SalesOrderHeader.ContactID)
INNER JOIN Sales.SalesOrderDetail
ON SalesOrderHeader.SalesOrderID=SalesOrderDetail.SalesOrderID
GROUP BY SalesOrderHeader.SalesOrderID, FirstName + " " + LastName
Keep in mind that using views does not improve performance. Calling a view causes its internal code to be executed, so in the best case, the view NOT will reduce database performance.
1) Concept of representation
Views are database objects that do not contain their own tables, but their content is taken from other tables or views by executing a query.
2) Creating views
CREATE VIEW
[()]
AS
CHECK OPTION]
Notes:
In SQL Server, the text of a view can be encrypted using the WITH ENCRYPTION option after the view name.
3) Removing views
DROP VIEW CASDADE|RESTRICT
Note:
RESTRICT– There must not be any reference to the view being deleted in the view and constraints, otherwise the deletion will be denied.
CASADE– means deleting all objects that reference this view.
4) Keywords
a) RECURSIVE
A representation is created that receives values from itself.
b) WITH CHECK OPTION
Prevents table updates based on views if the data that is changed or added is not reflected in the view.
The prohibition only applies to values that do not meet the conditions specified in the WHERE clause.
c) LOCAL
Controls that changes to base tables are reflected only in the current view.
d) CASCADED
Controls whether changes are reflected in all views defined on a given view.
5) Limitations and features
1. Column names are usually specified when some columns are calculated and therefore not named, and when two or more columns have the same name in the corresponding tables in the query. Always in InterBase.
2. In some DBMSs, you cannot use the ORDER BY section, which provides sorting.
3. Views can be connected to both base tables and other views using queries on both objects.
6) Criteria for updating views
1. It should be based on only one table. It is desirable that it includes the primary key of the table.
2. It should not contain columns resulting from aggregation functions.
3. It cannot contain a DISTINCT specification in its definition.
4. It cannot use GROUP BY or HAVING in its definition.
5. It should not contain subqueries.
6. If it is defined on another view, then it must be updatable.
7. It cannot include constants, strings or expressions in the list of output fields. Rearranging and renaming fields is not allowed.
8. For an INSERT statement, it must include any fields from the underlying views of the base table that have NOT NULL constraints, but the default value may be set to another value.
7) Examples
1. CREATE VIEW LondonStaff
AS SELECT * FROM SalesPeople WHERE City=’London’2. CREATE VIEW SalesOwn
AS SELECT SNum, SName, City FROM SalesPeople3. CREATE VIEW NameOrders
AS SELECT ONum, Amt, A.SNum, SName, CName
FROM Orders A, Customer B, SalesPeople C
WHERE A.CNum=B.CNum AND A.SNUM=C.SNum
Examples for blocking updates:
CREATE VIEW. This method is preferable to others if there is a possibility that you will create other views in the future, since you can put T-SQL statements in a script file and then edit and use that file over and over again. SQL Server Enterprise Manager supports a graphical environment in which you can create a view. Finally, you can use the Create View Wizard when you need help walking through the view creation process, which can be useful for both the novice and the expert.1. CREATE VIEW HighRating AS SELECT CNum, Rating
2. Add a line that the view does not see:
INSERT INTO HighRating VALUES(2018, 200)
3. We prohibit adding lines outside of visibility:
CREATE VIEW HighRating AS SELECT CNum, Rating
FROM Customer WHERE Rating=300
WITH CHECK OPTION
4. Create a new one that allows you to add again:
CREATE VIEW MyRating AS SELECT * FROM HighRating
Using T-SQL to Create a View
Creating views using T-SQL is a fairly simple process: you run the statement CREATE VIEW to create a view using ISQL, OSQL or Query Analyzer. As discussed, using T-SQL statements in a script is preferable because these statements can be modified and reused. (You should also store your database definitions in scripts in case you need to recreate your database.)
Operator CREATE VIEW has the following syntax:
CREATE VIEW view_name [(column, column...)] AS your SELECT statement
When you create a view, you can activate two features that change the behavior of the view. To enable these features, you must include the WITH ENCRYPTION and/or WITH CHECK OPTION keywords in the T-SQL statement. Let's look at these tools in more detail.
The WITH ENCRYPTION keyword specifies that the view definition (the SELECT statement that defines the view) should be encrypted. SQL Server uses the same method for encrypting SQL statements as it does for passwords. This security technique can be useful if you don't want certain classes of users to know which tables are being accessed.
The WITH CHECK OPTION keyword specifies that data modification operations applied to the view must meet the criteria contained in the SELECT statement. For example, you can prevent a data modification operation from being applied to a view to create a table row that is not visible within the view. Suppose that you are defining a view to sample information about all employees of the finance department (finance department). If the WITH CHECK OPTION keyword is not included in the statement, you can change the department column's finance value to a value that specifies a different department. But if this keyword is specified, then this change will not be allowed, since changing the value of the department column in any row will make that row unavailable from that view. The WITH CHECK OPTION keyword specifies that you cannot make any row unavailable from a view by making any change within that view.
The SELECT statement can be modified to create any view you want. It can be used to select a subset of columns or a subset of rows, or to perform some kind of join operation. In the following sections, you will learn how to use T-SQL to create different types of views.
Column subset
A view containing a subset of columns can be useful if you need to secure a table that should only be partially accessible to users. Let's look at one example. Let's assume that a company's employee database contains a table named Employee with data columns (Figure 18.1).
Rice. 18.1.
Most of this data is critical and should only be viewable by certain employees. However, it may be useful to allow all users to view some of this data. To do this, you can create a view that allows all users to access only certain data. This representation can also be used to avoid data duplication about employees in other data tables.
To create a view on the Employee table that only has access to the name, phone, and office columns, use the following T-SQL statement:
CREATE VIEW emp_vw AS SELECT name, phone, office FROM Employee
The resulting view will contain columns (Figure 18.2). Although these columns also exist in the base table, users who access data through this view can only see these columns in this view. And because a view can have a different security level than the view's underlying table, the view can be made available to any user while the underlying table remains protected. In other words, you can restrict access to the Employee table to, for example, only the HR department, and you can give all users access to this view.
Views can be manipulated in the Query Editor by executing SQL scripts that use the DDL commands CREATE, ALTER, and DROP. The basic syntax for creating a view is as follows:
CREATE VIEW view_name AS SELECT_statement
For example, to programmatically create a v_Customer view that returns a list of customers with their city of residence, you would run the following command in the query window.
CREATE VIEW.
SELECT dbo.Customer.IdCust, dbo.Customer.FName, dbo.Customer.LName, dbo.City.CityName
FROM dbo.Customer INNER JOIN
dbo.City ON dbo.Customer.IdCity = dbo.City.IdCity
Trying to create a view that already exists will throw an error. Once the view is created, the SELECT statement can be easily edited using the ALTER command:
ALTER view_name AS modified_SELECT_statement
If changing a view also means changing its access permissions, it is preferable to delete it and create it again, since deleting a view also deletes the access permissions that were previously set.
To remove a view from the database, use the DROP command:
DROP VIEW view_name
order by clause and views
Views serve as a source of data for other queries and do not support sorting internally. For example, the following code retrieves data from the v_Customer view and organizes it by the LName and FName fields. The ORDER BY clause is not part of the v_Customer view, but is applied to it by calling the SQL statement:
SELECT IdCust, FName, LName, CityName
FROM dbo.v_Customer
ORDER BY LName, FName
Executing Views
The performance cannot be performed on its own. The SELECT statement from which the view is created can be executed, but in this form, the SQL statement is not technically a view. A view is only useful as a source of data in a query.
That's why ManagementStudio's Open View context menu automatically generates a simple query that retrieves all the columns from the view. The view only displays results. However, turning on the other Query Designer panels allows you to see the query itself as extracted from the view.
The SQL Panel will display the view in the FROM clause of the SELECT statement. This is the form in which users refer to the view:
SELECT * FROM v_Customer
Assignment for independent work: Create a view that returns a list of orders, indicating the customer name and the number of items in each order. Thus, the result should include the following attributes: IdOrd, OrdDate, IdCust, FName, LName, Number of product types in the order.
Lab #7: t-sql programming t-sql syntax and conventions
Rules for generating identifiers
All objects in SQLServer have names (identifiers). Examples of objects are tables, views, stored procedures, etc. Identifiers can be up to 128 characters, including letters, _@$#, and numbers. The first character must always be alphabetic. Variables and temporary tables use special naming schemes. The object name cannot contain spaces and cannot be the same as the SQLServer reserved keyword, regardless of case used. By enclosing identifiers in square brackets, prohibited characters can be used in object names.
Completing the instructions
The ANSISQL standard requires a semicolon to be placed at the end of each statement. At the same time, when programming in T-SQL, the semicolon is not required.
Comments
The T-SQL language allows two styles of comments: ANCI and C. The first of them begins with two hyphens and ends at the end of the line:
This is a one-line ANSI style comment
ANSI style comments can also be inserted at the end of the statement line:
SELECT CityName – columns to be retrieved
FROM City – source table
WHERE IdCity = 1; -- line limit
The SQL editor can apply and remove comments on all selected lines. To do this, select the appropriate commands in the menu Edit or on the toolbar.
