% - A substitute for zero or more characters (can be in the middle) _ - A substitute for exactly on character [charlist] - Any single character in the list [!charlist] - Any single character not in the list
Returns rows when there is at least one match in both tables.
Returns all rows from the left table even if there are no matches in the right table.
Returns all rows from the right table even if there are no matches in the left table.
Returns rows when there is a match in one of the tables.
Combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union. The UNION operation is different from using joins that combine columns from two ore more tables.
The following are basic rules for combining the result sets of two queries by using UNION:
-
The number and the order of the columns must be the same in all queries. -
The data types must be compatible.
ALL: Incorporates all rows into the results, including duplicates. If not specified, duplicates are removed.
A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The result set is the number of rows in the first table multiplied by the number of rows in the second table.
If there is a WHERE clause then the result is no different than an inner join.
The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output.
The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.
You can also use APPLY with derived tables joined to your main query. This means you can select from a view instead of a TV function which can help you avoid ridiculously long/complex order by & group by in outer selects.
CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function.
OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.
Suppose you have a view which you use to denormalize some of your data and provide a single authoritative place to go for some particular bit of logic.
For example an Employees view which brings together various tables from your normalized table schema to provide an easy way to look at payroll, user, and work history data all in one place. This view might also encapsulate some important logic which your system needs to process in various places.
A view makes sense as it provides you with:
- A denormalized schema while retaining your normalized design underneath.
- Code reuse on top of the normalized design.
- Decent maintainability
- Good performance through schema caching, etc.
One problem occurs when you build views upon views so e.g.
ViewD SELECTS from JOIN between ViewC and ViewB,
and ViewB itself SELECTS from ViewA
This produces a really good level of code reuse since you have well named
views which present some "view" on the data which is useful all over the
system and saves you time later on, and gives you one place to go for
questions of a particular type.
But, if you want an ordered select from one of these views, and want to join
to this ordered select, and possibly group by too, but you only want to do
this once.
Enter APPLY
SELECT TOP 100 Offices.*, Manager.*, YoungestEmployee.*
-- Rest of select
FROM Offices
INNER JOIN Users AS Managers ON Offices.ManagerID = Managers.ID
OUTER APPLY
(
-- SELECT IN ORIGINAL WAY FROM EXISTING VIEW
SELECT TOP 1
Employees.FirstName,
Employees.LastName,
Employees.PayGradeID,
Employees.DateOfBirth,
Employees.DepartmentID
FROM Employees
WHERE
Employees.OfficeID = Offices.ID AND
Employees.ManagerID = Managers.ID
ORDER BY DateOfBirth DESC
) as YoungestEmployee
LEFT OUTER JOIN Departments AS EmployeeDepartments
ON YoungestEmployee.DepartmentID = EmployeeDepartments.ID
-- Rest of VIEW, Multiple Joins, Where Clause, Order By, Group By, etc.
Keep your queries simple and readable, avoid addig unnecessary one-off
views, avoid executing SPROCS from within other queries, avoid
repeated unwieldly and hard to maintain inline subselects, but you can
ORDER BY, TOP X, GROUP BY and JOIN to the parent query.
IFs can be optimized the same way as a view, hence it is sometimes referred to as a parameterized view.
Views are virtual tables that serve the following purposes.
-
of data in one or more base tables.To provide a security mechanism that restricts users to a certain subset -
logically view the data stored in base tables.To provide a mechanism that allows developers to customize how users can
When a View is referenced in the FROM clause of another query, the metadata is retrieved from the system catalog and expanded in place of the view's reference. After this the query optimizer selects the lowest cost plan it can find, based on estimates of the actual time it will take to execute each query plan.
In the case of a non-indexed view, these costs are part of the execution of each query referencing the view.
It is possible to create a unique clustered index on a view, as well as nonclustered indexes, to improve data access performance on the most complex queries. A view that has a unique clustered index is referred to as an indexed view. After a unique clustered index is created on the view, the view's result set is materialized immediately and persisted in physical storage in the database, saving the overhead of performing this costly operation at execution time.
Indexed views provide additional performance benefits that cannot be achieved using standard indexes. Indexed views can increase query performance in the following ways:
-
expensive computations during query execution.Aggregations can be precomputed and stored in the index to minimize -
Tables an be prejoined and the resulting data set stored. -
Combinations of joins or aggregations can be stored.
-
indexed views.Frequently occurring aggregations and joins are the best candidates for -
Repeated patterns of queries -
Repeated aggregations on the same or overlapping sets of columns -
Repeated joins of the same tables on the same keys.
--- Use Table Variable to avoid Distributed Join for performance (BI-1246) DECLARE @tp8lat_MiscExtra TABLE ( ID INT NOT NULL ,Number INT NULL ,Title VARCHAR(30) NULL ,TheData VARCHAR(100) NULL ,PRIMARY KEY (ID) ,UNIQUE(Number, Title) -- These are the two fields used in the join and where (Creates a Unique Index) )
INSERT INTO @tp8lat_MiscExtra(ID, Number, Title, TheData) SELECT ID, Number, Title, TheData FROM [TP8LATITUDEDB].[Collect_2000_Agency].[dbo].[MiscExtra] WHERE Title = 'Account Segmentation Group'
EXEC sp_linkedservers