Labels

Friday, January 9, 2009

01 - DML

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, UNION, COMPUTE, CUBE, ROLLUP, TRUNCATE, and query hints.

 

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.

 

 

SELECT City, COUNT(*)as 'Total Customers'

FROM Customers

WHERE City IN ('Buenos Aires', 'Bruxelles', 'Sao Paulo')

GROUP BY ALL City

 

 

 

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', 'Berlin', 'Vancouver')

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', 'Berlin', 'Vancouver')AND

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

 

 

LOOP

 

-                    Each hint type specifies which method will be used to join the rows of two tables.

 

MERGE

-                    LOOP joins operate best when one table is small and the other is large, with indexes on the joined columns.

-                    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

LOOP joins operate best when one table is small and the other is large, with

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.

LOOP joins operate best when one table is small and the other is large, with

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