Hi,
- Data Manipulation Language (DML) provides a means of retrieving and modifying data within the database.
- DML statements include SELECT, INSERT, UPDATE, and DELETE.
- Use of table aliases, self joins, subqueries, derived tables,
Here we'll cover below –
- WITH TIES Clause
- Table using INTO
- Table Joins
- Self Joins With Table Aliases
- Derived Tables
- Use Where Clause
- Use GROUP BY
- Use Subqueries
- COMPUTE in a SELECT
- FOR Clause
- INSERT Clause
- UPDATE Clause
- Delete & Truncate
- Use Hints
WITH TIES Clause
- Works with the TOP keyword and the ORDER BY clause, to retrieve any rows with equivalent values to the last value returned.
- E.g.
| SELECT TOP 5 vchTitle FROM leaders ORDER BY vchTitle | Output – AA BB CC CC CC |
| SELECT TOP 5 WITH TIES vchTitle FROM leaders ORDER BY vchTitle Using WITH TIES will show all occurrences of the last row value specified in the ORDER BY clause. | AA BB CC CC CC CC CC |
Create a Table using INTO
- The INTO clause allows you to create a table based on the results of a query result set.
- The columns you select will determine the schema of the table.
- The schema is reproduced, but constraints, indexes, and other separate objects dependent on the source table are not copied.
- Also used to generate an empty table, based on columns defined in a query
| SELECT [ALL | DISTINCT] TOP n [PERCENT] <column_1>, <column_N…> INTO <new_table_name> FROM <data_source> | SELECT emp_id, fname, lname INTO employee_backup FROM employee SELECT emp_id, fname, lname INTO employee_backup FROM employee Where 1=0 |
Table Joins-
- The JOIN clause allows you to combine multiple tables or views into one result set.
- With the JOIN clause, you can join primary and foreign key, composite key.
- Joined columns do not need to have the same name, only compatible data types.
SELECT <select_list>
FROM <table_1> <INNER or LEFT OUTER or RIGHT OUTER or FULL OUTER or CROSS> JOIN
<table_2> ON <search conditions>…
- INNER joins are the default JOIN type; you can use just the JOIN keyword, instead of INNER JOIN.
- Do not join columns that contain NULL values, since NULL values are not joinable.
- LEFT OUTER JOINs return unmatched rows from the first table of the join pair.
- RIGHT OUTER JOINs return unmatched rows from the second table of the join pair.
- The FULL OUTER JOIN clause returns unmatched rows on both the LEFT and RIGHT tables.
- FOJ is an excellent method for finding orphaned or missing rows, as you can identify missing associations and data between two tables.
- A CROSS JOIN results in a Cartesian product when a WHERE clause is not specified. Hence the a result set based on every possible combination of rows from the left & right table.
- If you include a WHERE clause to join two columns, the CROSS JOIN will act like a regular INNER JOIN.
- Not all WHERE clauses will cause the CROSS JOIN to behave like an INNER JOIN. WHERE clauses must join columns between two tables referenced in the CROSS JOIN.
| SELECT stores.stor_id, sales.ord_num FROM stores CROSS JOIN sales | SELECT stores.stor_id, sales.ord_num FROM stores CROSS JOIN sales WHERE stores.stor_id = sales.stor_id |
Self Joins With Table Aliases
- Table aliases are necessary for self join operations.
- A self join occurs when a table joins with itself to produce a query result set. Since you cannot reference the same table name twice within a query, you can use an alias to do this instead.
Use Derived Tables
- A derived table is a subquery in the FROM clause that is used as a data source.
- Derived tables are defined with table aliases, so they may be referenced within the query.
| SELECT [ALL | DISTINCT] TOP n [PERCENT] <column_1>, <column_N…> FROM (sub_query) AS <alias_name> | SELECT * FROM (SELECT job_desc,min_lvl FROM jobs) as MyJobs WHERE MyJobs.min_lvl > 1 |
Use Where Clause
| ESCAPE operator | SELECT * FROM Orders WHERE ShipAddress LIKE '%/_%' ESCAPE '/' |
Use GROUP BY
| GROUP BY | SELECT [ALL | DISTINCT] TOP n [PERCENT] <column_1>, <column_N…> INTO <new_table_name> FROM <data_source> WHERE <seach_condition or search_conditions> GROUP BY [ ALL ] column_1, column_N… WITH { CUBE | ROLLUP } |
| GROUP BY ALL | By choosing the ALL clause, all row values are used in the grouping, even if they were not qualified to appear via the WHERE clause. FROM Customers WHERE City IN (' GROUP BY |
| Using CUBE | - When used in conjuction with the GROUP BY clause, it add rows to your result that summarize total values based on the columns in the GROUP BY clause. SELECT Orders.ShipCity, SUM(OrderDetails.UnitPrice) FROM OrderDetails, Orders WHERE Orders.OrderId = OrderDetails.OrderId AND ShipCity IN ('Torino', ' GROUP BY Orders.ShipCity WITH CUBE
|
| Using ROLLUP | - When used in conjuction with the GROUP BY clause, it add rows to your result that hierarchical summarize total values based on the columns in the GROUP BY clause. SELECT Orders.ShipCity,OrderDetails.ProductId, SUM(OrderDetails.UnitPrice) as 'Total Price' FROM OrderDetails,vOrders WHERE Orders.OrderId = OrderDetails.OrderId AND ShipCity IN ('Torino', ' OrderDetails.ProductID IN (77, 71, 51) GROUP BY Orders.ShipCity, OrderDetails.ProductId WITH ROLLUP
|
Use Subqueries
- You cannot return NTEXT, TEXT, or IMAGE data types in a subquery SELECT list.
- The GROUP BY and HAVING clauses are only allowed if the subquery returns a single value.
- DISTINCT is not allowed if GROUP BY is specified.
- The INTO and COMPUTE clauses are not allowed at all.
| Within Select | SELECT iBookId, moBookPrice, (SELECT SUM(moBookSales) FROM BookOrders) AS 'Total Sales' FROM BookSales |
| Correlated Subquery | - Correlated subquery depends on the outer query for its values. - The subquery is repeatedly executed for each row selected by the outer query. SELECT ProductID, ProductName FROM Products WHERE CategoryID IN (SELECT Categories.CategoryId FROM Categories WHERE Products.CategoryID = Categories.CategoryID) - Correlated subqueries can also be used in the HAVING clause of an outer calling query SELECT iRegionId, moTotalSales FROM BookSales_NY GROUP BY iRegionId, moTotalSales HAVING MIN(moTotalSales) > ALL (SELECT MAX(moTotalSales) FROM BookSales_MN) SELECT vchBookName FROM Books B WHERE EXISTS (SELECT * FROM BookSales S WHERE B.iBookId = S.iBookId) |
COMPUTE in a SELECT Statement
| SELECT <columns> FROM <data source, sources> WHERE <query specification> ORDER BY <columns> COMPUTE <AVG | COUNT | MAX | MIN | STDEV | STDEVP | VAR | VARP | SUM > ( expression ) BY <expression or expressions> | |
| SELECT stor_id, ord_num, qty FROM sales COMPUTE AVG(qty), SUM(qty)
| SELECT stor_id, ord_num, qty FROM sales WHERE stor_id IN (6380, 7066, 7067) ORDER BY stor_id COMPUTE AVG(qty), SUM(qty) BY stor_id
|
FOR Clause
- The FOR clause is used to specify BROWSE or XML options.
- When FOR BROWSE is chosen, locks are NOT held while the SELECT statement accesses data.
- A table is browse compliant when it includes a TIMESTAMP column, a unique index, and uses FOR BROWSE in the SELECT statement.
- FOR XML is completely unrelated to BROWSE mode, and is used to return a relational result set in hierarchical XML format.
| SELECT [ALL | DISTINCT] TOP n [PERCENT] <column_1>, <column_N…> INTO <new_table_name> FROM <data_source> WHERE <seach_condition or search_conditions> GROUP BY [ ALL ] column_1, column_N… WITH { CUBE | ROLLUP } HAVING <search_condition> ORDER BY <order_expression> <ASC or DESC> FOR <BROWSE | XML > { RAW | AUTO | EXPLICIT } < , XMLDATA > <, ELEMENTS > <, BINARY BASE64 > |
INSERT Clause
| INSERT <INTO> <table_name or view_name> (<column_list>) VALUES (<comma separated value list>) | |
| Inserting DEFAULT values | INSERT Fruits (vchDescription, chFoodType) VALUES ('Apple', DEFAULT) |
| Inserting DEFAULT values | INSERT Fruits DEFAULT VALUES |
| Inserting data from a SELECT statement | INSERT fruits (vchDescription, chFoodType) SELECT vchDescription, chFoodType FROM exotic_fruits WHERE chFoodType = 'exotic' |
| Inserting data from a stored procedure call | INSERT RoleMembers EXEC sp_helprolemember |
UPDATE Clause
| UPDATE <table_name or view name> SET <column name> = <expression or default> FROM <table or view name> WHERE <search condition> | |
| UPDATEs based on other tables | UPDATE Fruits SET vchDescription = exotic_fruits.vchDescription FROM Fruits, Exotic_Fruits WHERE Fruits.iFruitId = Exotic_Fruits.iFruitId |
Delete & Truncate
| DELETE <FROM> <table_name or view name > FROM <Table>[, <Table n>] WHERE <search condition> | TRUNCATE TABLE <table_name> |
| | Deletes rows faster than DELETE, because it is minimally logged. |
| | TRUNCATE operation can be rolled back if used within a transaction. |
| | TRUNCATE operation, cannot use 'where' clause. |
| | TRUNCATE operation, cannot be run against columns actively referenced by a foreign key constraint. The syntax is: |
Use Hints
- Microsoft provides Query, Join, And Table hints that can be used to override SELECT, INSERT, UPDATE, and DELETE processing behavior.
- Because hints override SQL Server commands, their use is not recommended unless you have significant query-tuning experience, and good reasons for using them.
- Hints can improve performance in the short term, but underlying objects can undergo changes that minimize the improvements.
- Furthermore, new service packs and SQL Server editions could nullify any positive effects.
- If you choose to use hints, document where you used them so you can evaluate their usage later on.
- Be sure to test the performance of your queries with and without the query hints.
Can we suggest locking hints to SQL SERVER ?
- We can give locking hints that help's you override default decisions made by SQL Server.
- For instance, you can specify the ROWLOCK hint with your UPDATE statement to convince SQL Server to lock each row affected by that data modification.
- Whether it's prudent to do so is another story; what will happen if your UPDATE affects 95% of rows in the affected table? If the table contains 1000 rows, then SQL Server will have to acquire 950 individual locks, which is likely to cost a lot more in terms of memory than acquiring a single table lock.
- So think twice before you bombard your code with ROWLOCKS.
Table Hints
- Table hints can be used for SELECT, INSERT, UPDATE, and DELETE queries.
- Specify table hints by using the WITH clause.
- You can use multiple hints for one query, separated by commas, so long as they do not belong to the same category grouping.
| Below selects all rows of the Books table, without placing any locks (but risking dirty reads. SELECT * FROM Books WITH (NOLOCK) | Below uses three hints,- Producing no locks, Optimizing speed for the first row, and Forcing a specific index to be used. SELECT * FROM products WITH (NOLOCK, FASTFIRSTROW,INDEX(1)) |
| FASTFIRSTROW | - This hint optimizes the query to pull the first row of the result set very quickly. - Whether or not this hint will work depends on the size of your table, indexing used, and the type of data you are returning. - Test your results with and without the hint, to be sure that the hint is necessary. Use this hint in order to begin returning results to the client faster, not to improve the speed of the entire result set. |
| INDEX <Index Number 1, …n> | - Overrides SQL Server's index choice and forces a specific index for the table to be used. |
| NOEXPAND | |
| HOLDLOCK | |
| SERIALIZABLE | |
| REPEATABLEREAD | |
| READCOMMITTED | |
| READUNCOMMITTED | |
| NOLOCK | |
| ROWLOCK | - Determines the granularity of locking for the table; for example, selecting ROWLOCK to force only row locks for a query. |
| PAGLOCK | |
| TABLOCK | |
| TABLOCKX | |
| NOLOCK | |
| READPAST | |
| UPDLOCK | - The hint will force update locks instead of shared locks to be generated (not compatible with NOLOCK or XLOCK). |
| XLOCK | - This hint forces exclusive locks on the resources being referenced (not compatible with NOLOCK or UPDLOCK). |
Join Hints
- Join hints force the internal JOIN operation used to join two tables in a query.
| SELECT * FROM Employees E INNER MERGE JOIN EmployeeTerritories T ON E.EmployeeID = T.EmployeeID | |
| | - Each hint type specifies which method will be used to join the rows of two tables. |
| MERGE | - - MERGE joins are optimal for medium or large |
| HASH | - |
| REMOTE | - |
Query Hints
- Query hints can be used for SELECT, UPDATE, or DELETE operations (but not for an INSERT operation).
- Query hints are set with the OPTION clause
| SELECT ProductName, COUNT(*) FROM Products GROUP BY ProductName OPTION (HASH GROUP, ROBUST PLAN) | |
| <HASH or ORDER> GROUP | Each hint type specifies which method will be used to join the rows of two tables. |
| MERGE | indexes on the joined columns. MERGE joins are optimal for medium or large |
| FAST integer | |
| FORCE ORDER | Each hint type specifies which method will be used to join the rows of two tables. indexes on the joined columns. MERGE joins are optimal for medium or large tables that are sorted on the joined column. HASH joins are optimal for large, unsorted tables. REMOTE forces the join operation to occur at the site of the table referenced on the right (the second table referenced in a JOIN clause). For performance benefits, the left table should be the local table, and should have fewer rows than the remote right table. |
| ROBUST PLAN | |
| MAXDOP integer | |
| KEEP PLAN | |
| KEEPFIXED PLAN | |
| EXPAND VIEWS | |
| | |
Hope this helps.
Thanks & Regards,
Arun Manglick || Tech Lead
No comments:
Post a Comment