This is the 12th day of my participation in Gwen Challenge.
Hello everyone, I am Wukong.
Summary:
This is the next article on table expressions, covering only views and inline table valued functions.
Table expressions contain four types:
1. Derived tables
2. Common table expressions
3. View
4. Inline table valued functions
Here is a mind map for table expressions:
Table expression:
1. A named query expression representing a valid relational table.
2. You can use table expressions in data processing statements just like any other table.
3. Objects that are not physically real, they are virtual. All queries for expressions are translated into queries for underlying objects within the database engine.
Why table expressions are used:
1. The advantage of using table expressions is logical, with no performance gain.
2. Simplify the solution to the problem through a modular approach and avoid some language restrictions. The column alias assigned in the SELECT clause of the internal query can be referenced in any clause of the external query. For example, an alias in a SELECT clause cannot be used in WHERE,group by, etc. (the logical order precedes the SELECT clause). Table expressions can solve this problem.
The following SQL script will help you create databases, create tables, and insert data.
Download the script file: tsqlals2008.zip
The view,
1. Differences and similarities between views and derived tables and Ctes
The difference between:
Derived tables and Ctes are not reusable: they are limited to the scope of a single statement and disappear as soon as the external query containing these table expressions completes the operation.
Views and inline table-valued functions are reusable: their definitions are stored in a data object that, once created, is a permanent part of the database; They will only be removed from the database if they are displayed with a delete statement or deleted by right-clicking.
Thing in common:
In many ways, views and inline table-valued functions are handled similarly to derived tables and Ctes. When querying views and inline table-valued functions, SQL Server extends the definition of table expressions before directly querying the underlying objects.
2. Grammar
The following example defines a view named sales.usacusts that queries all customers from the United States.
USE TSQLFundamentals2008
IF OBJECT_ID('Sales.USACusts') IS NOT NULL
DROP VIEW Sales.USACusts;
GO
CREATE VIEW Sales.USACusts
AS
SELECT custid ,
companyname ,
contacttitle ,
address ,
city ,
region ,
postalcode ,
country ,
phone ,
fax
FROM Sales.Customers
WHERE country = N'USA'
Copy the codeOnce the view is defined, the newly created view Sales.usacusts appears after the view list is refreshed in the database
The view can then be queried just like any other table in the database:
SELECT custid ,
companyname
FROM sales.usacusts
Copy the code3. View permissions
You can control the permissions of a view like any other database object: SELECT, INSERT, UPDATE, DELETE permissions
4. Avoid SELECT * statements
Columns are enumerated when the view is compiled, and newly added columns are not automatically added to the view. Later, several columns were added to the tables used in the view, which were not automatically added to the view. The metadata of the view can be refreshed using sp_refreshView’s stored procedure, but the required column names are displayed in the view for future maintenance. If you add columns to the underlying table and need those columns in the VIEW, you can use the ALTER VIEW statement to modify the VIEW definition accordingly.
5. Requirements for creating views:
All of the requirements for table expressions mentioned earlier in the introduction to derived tables must be met:
A. Columns must have names
B. Columns must be unique
C. A certain order is not guaranteed. The ORDER BY clause is not allowed in a query that defines a table expression. Because there is no order between rows in a relational table.
6. ENCRYPTION option
You can use the ENCRYPTION option when creating views, stored procedures, triggers, and user-defined functions. If the ENCRYPTION option is specified, SQL Server internally obfuscates the text information that defines the object. Ordinary users cannot access the text of the view. Only privileged users can access the text of the object creation by special means.
In the header of the view definition, use the WITH sentence to specify the ENCRYPTION option, as follows:
CREATE VIEW Sales.USACusts WITH ENCRYPTION
Copy the codeYou can view the text of the view with the following statement:
SELECT OBJECT_DEFINITION(OBJECT_ID('Sales.USACusts'))
Copy the codeThe results are as follows:
You can also use sp_helptext to view the text of the view:
sp_helptext 'Sales.USACusts'
Copy the codeThe results are as follows:
View encryption is required only when security requirements are high. In general, no encryption is required.
7. SCHEMABINDING option SCHEMABINDING
Views and user-defined functions support the SCHEMABINDING option. Once this option is specified, objects referenced by a view cannot be deleted, and referenced columns cannot be deleted or modified.
In the header of the view definition, use the WITH word to specify the SCHEMABINDING option, as follows:
CREATE VIEW Sales.USACusts WITH SCHEMABINDING
Copy the codeYou can update the address column of the Sales.Customers object referenced by the Sales.USACusts view with the following statement
ALTER TABLE Sales.Customers DROP COLUMN address
Copy the codeThe results are as follows:
It is recommended to use the SCHEMABINDING option when creating views.
If you use the SCHEMABINDING option, you must meet two technical requirements:
A. Column names must be explicitly listed in the SELECT clause
B. When referring to objects, you must use the full object name with the schema name modifier.
8. CHECK the OPTION OPTION
The purpose of the CHECK OPTION OPTION is to prevent data modifications performed through the view from colliding with filtering conditions set in the view, assuming that filtering conditions exist in the query that defines the view.
To imagine inserting data into the sales.customers table through the sales.usacusts view, use the following statement:
INSERT INTO Sales.USACusts
( companyname ,
contactname ,
contacttitle ,
address ,
city ,
region ,
postalcode ,
country ,
phone ,
fax
)
VALUES ( 'A' ,
'A' ,
'A' ,
'A' ,
'London' ,
'A' ,
'A' ,
'UK' ,
'123' ,
'123'
)
Copy the codeThen query the sales.customers table as follows:
SELECT custid,companyname,country FROM Sales.Customers WHERE companyname = 'A'Copy the codeResults:
If you query with a view, the following would look like:
SELECT custid ,
companyname ,
country
FROM Sales.USACusts
WHERE companyname = 'A'
Copy the codeYou get an empty result set because the WHERE condition in the view WHERE country = N’USA’ filters only customers from the United States.
To prevent changes that conflict WITH the view’s query filtering criteria, add WITH CHECK OPTION to the end of the query statement defining the view:
ALTER VIEW [Sales].[USACusts]
AS
SELECT custid ,
companyname ,
contactname ,
contacttitle ,
address ,
city ,
region ,
postalcode ,
country ,
phone ,
fax
FROM Sales.Customers
WHERE country = N'USA'
WITH CHECK OPTION;
GO
Copy the codeTry again to insert records that conflict with the view’s filter conditions:
INSERT INTO Sales.USACusts
( companyname ,
contactname ,
contacttitle ,
address ,
city ,
region ,
postalcode ,
country ,
phone ,
fax
)
VALUES ( 'A' ,
'A' ,
'A' ,
'A' ,
'London' ,
'A' ,
'A' ,
'UK' ,
'123' ,
'123'
)
Copy the codeThe results are as follows:
9. Exercises:
(1) Create a view that returns the total number of orders processed per employee per year:
Expected Results:
Create a view
IF OBJECT_ID('Sales.VEmpOrders') IS NOT NULL
DROP VIEW Sales.VEmpOrders;
GO
CREATE VIEW Sales.VEmpOrders
AS
SELECT empid ,
YEAR(orderdate) AS orderyear ,
SUM(qty) AS qty
FROM Sales.Orders AS O
INNER JOIN Sales.OrderDetails AS D ON O.orderid = D.orderid
GROUP BY empid ,
YEAR(orderdate);
GO
Copy the code(2) Write a query to the sales.vemporders table that returns the total sequential orders processed by each employee each year
Expected output:
This question requires a correlation subquery:
SELECT empid ,
orderyear ,
qty ,
( SELECT SUM(qty) AS runqty
FROM Sales.VEmpOrders AS EO2
WHERE EO1.empid = EO2.empid
AND EO1.orderyear <= EO2.orderyear
)
FROM Sales.VEmpOrders AS EO1
ORDER BY EO1.empid ,
Copy the codeThe subquery returns all rows whose order year is less than or equal to the order year of the current row in the outer query and calculates the sum of the order quantities for those rows.
2. Inline table valued functions
1. What is an inline table valued function
A reusable table expression that supports input parameters. Inline table valued functions are similar to views in every respect except that they support input parameters.
2. How to define inline table valued functions
The following example creates a function, fn_GetCustOrders. This inline table value takes an input customer ID parameter @cid and another input parameter order year parameter @OrderDateYear and returns all orders placed by customers whose customer ID is equal to @CID and whose order year is equal to @OrderDateYear
IF OBJECT_ID('dbo.fn_GetCustOrders') IS NOT NULL
DROP FUNCTION dbo.fn_GetCustOrders
GO
CREATE FUNCTION dbo.fn_GetCustOrders ( @cid AS INT ,@orderdateyear AS DATETIME)
RETURNS TABLE
AS RETURN
SELECT orderid ,
custid ,
empid ,
orderdate ,
requireddate ,
shippeddate ,
shipperid ,
freight ,
shipname ,
shipaddress ,
shipcity ,
shipregion ,
shippostalcode ,
shipcountry
FROM Sales.Orders
WHERE custid = @cid AND YEAR(orderdate) = YEAR(@orderdateyear)
Go
Copy the codeOnce the inline table-valued functions are defined, the function fn_GetCustOrders you just created appears after refreshing the programmability function-table-valued functions list in the database
3. How to use inline table valued functions
Select * from customer where id=1 and order date = year =2008;
SELECT orderid,custid,orderdate FROM fn_GetCustOrders(1,'2008')
Copy the codeInline table values can also be used in join queries:
The following example associates the HR.Employees table with an inline table value function to query all orders with customer ID =1, order date, year =2008, and employee details for those orders:
SELECT orderid , custid , orderdate , empid , lastname , firstname , title FROM fn_GetCustOrders(1, '2008') INNER JOIN HR.Employees AS E ON dbo.fn_GetCustOrders.empid = E.empidCopy the codeThe results are as follows:
Exercises of 4.
(1) Create an inline table value function whose input parameters are supplier ID(@supID AS INT) and required product quantity (@n AS INT). This function returns the @n products with the highest unit price among the products provided by the given supplier @supid.
When the following query is executed:
SELECT * FROM fn_TopProducts (5, 2)Copy the codeExpected Results:
This problem can be divided into three steps:
1. Write a query statement
SELECT TOP ( 1 )
productid ,
productname ,
unitprice
FROM Production.Products
WHERE supplierid = 1
ORDER BY unitprice DESC;
Copy the code2. Replace the parameters:
SELECT TOP ( @n )
productid ,
productname ,
unitprice
FROM Production.Products
WHERE supplierid = @supid
ORDER BY unitprice DESC;
Copy the code3. Place the query in an inline table value function
IF OBJECT_ID('dbo.fn_TopProducts') IS NOT NULL DROP FUNCTION dbo.fn_TopProducts GO CREATE FUNCTION dbo.fn_TopProducts ( @supid AS INT, @n AS INT ) RETURNS TABLE AS RETURN SELECT TOP ( @n ) productid , productname , unitprice FROM Production.Products WHERE supplierid = @supid ORDER BY unitprice DESC; GoCopy the codeThe APPLY operator
1. APPLY operator
The APPLY operator is a non-standard standard operator. The APPLY operator operates on two inputs, where the table on the right can be a table expression.
CROSS APPLY** : ** APPLY the right expression to each row in the left table and combine the result sets to produce a unified result table. Similar to cross join
OUTER APPLY** : ** APPLY the right expression to each row in the left table, combine the result set, and add the OUTER rows. Similar to the step in the left outer join to add outer rows
Exercises of 2.
(1) Use the CROSS APPLY operator and the fn_TopProducts function to return the two most expensive products for each supplier.
Related tables: Production.Suppliers
Expected Results:
APPLY the previous fn_TopProducts function for each vendor using the CROSS APPLY operator.
SELECT supplierid ,
companyname ,
productid ,
productname ,
unitprice
FROM Production.Suppliers AS S
CROSS APPLY fn_TopProducts(S.supplierid, 2) AS P
Copy the codeReferences:
SQL2008 technical Insider: T-SQL Language Basics
Welcome to follow my official account: “Wukong Chat Framework”
About the author: 8-year Veteran of Internet workplace | full stack engineer | super dad after 90 | open source practitioner | owner of public number 10,000 fans original number. Blue Bridge signed the author, the author of “JVM performance tuning practice” column, handwritten a set of 70,000 words SpringCloud practice summary and 30,000 words distributed algorithm summary. Welcome to follow my public account “Wukong Chat framework”, free access to learning materials.
I am Wukong, strive to become stronger, become super Saiya people!