When you optimize database performance, tuning individual queries is as important as tuning server hardware and software configurations. Even one runaway query can. Delete Query Tutorial. The Delete query in MS Access SQL command used to remove one or more rows of data from a relational database table. The number of rows deleted. Microsoft Access Append Query examples, SQL INSERT Syntax and errors to add multiple and single records into tables.

Microsoft Access is one of the least used products in the Office family. But it's also the most powerful. Here's how you can use them with the SQL querying language.

Update data by using a query. This article describes how to create and run an update query. You use an update query when you have to update or change existing data in a set of records. You cannot use an update query to add new records to a database, or to delete entire records from a database. To add new records to a database you use an append query, and to delete whole records from a database you use a delete query. For more information, see the See Also section. In this article. Overview.

Create and run an update query. Update data from one table to another.

Stop Disabled Mode from blocking a query. SQL version: UPDATE statement.

Overview. You use update queries to add, change, or delete the data in one or more existing records. You can think of update queries as a powerful form of the Find and Replace dialog box. Similarities and differences between Find and Replace and an update query.

Like the Find and Replace dialog box, an update query lets you: Specify a value that you want to replace. Specify the value to use as a replacement. Unlike the Find and Replace dialog box, an update query lets you: Use criteria that do not depend on the value that you want to replace.

Update lots of records in one pass. Change records in more than one table at the same time. Restrictions on fields that can be updated. An update query cannot be used to update data in the following types of fields: Calculated fields    The values in calculated fields do not permanently reside in tables. They only exist in your computer's temporary memory after Access calculates them.

Because calculated fields do not have a permanent storage location, you cannot update them. Fields from a totals query or a crosstab query    The values in these types of query are calculated, and therefore cannot be updated by an update query. Auto. Number fields    By design, the values in Auto. Number fields change only when you add a record to a table. Fields in unique- values queries and unique- records queries    The values in such queries are summarized. Some of the values represent a single record, and others represent more than one record.

The update operation is not possible because it is not possible to determine what records were excluded as duplicates, and therefore not possible to update all the necessary records. This restriction applies whether you use an update query or try to update data manually by entering values in a form or a datasheet. Fields in a union query    You cannot update data from fields in a union query because each record that appears in two or more data sources only appears once in the union query result.

Because some duplicate records are removed from the results, Access cannot update all the necessary records. Fields that are primary keys    In some cases, such as if the primary key field is used in a table relationship, you cannot update the field by using a query unless you first set the relationship to automatically cascade updates. Note: When you cascade updates, Access automatically updates foreign key values when you change a primary key value in a parent table. For more information about cascading updates, see the article, Create, edit or delete a relationship. For general information about other ways to add records to a database or change existing data, see the article Add one or more records to a database. Top of Page. Create and run an update query. As a best practice, there are two main steps that you must follow to create and run an update query: create a select query that identifies the records that you want to update, and then convert that query to an update query that you can run to update the records.

Tip: You might want to back up your database before you run an update query. You cannot undo the results of an update query, and making a backup helps make sure that you can reverse your changes if you change your mind. How to back up your database. Click the Microsoft Office Button. Manage, and then, under Manage this database, click Back Up Database. In the Save Backup As dialog box, specify a name and location for the backup copy, and then click Save.

Access closes the original file, creates a backup, and then reopens the original file. To revert to a backup, close and rename the original file so that the backup copy can use the name of the original version. Assign the name of the original version to the backup copy, and then open the renamed backup copy in Access. In this section. Step 1: Create a select query to identify the records to update. Step 2: Update the records. Step 1: Create a select query to identify the records to update.

Open the database that contains the records you want to update. On the Create tab, in the Other group, click Query Design. The query designer opens, and the Show Table dialog box opens. Click the Tables tab.

Select the table or tables that contain the records that you want to update, click Add, and then click Close. The table or tables appear as one or more windows in the query designer, and the windows list all the fields in each table. This figure shows the query designer with a typical table. Table shown in the query designer. Query design grid.

Double- click the fields that you want to update in the table windows. The selected fields appear in the Field row in the query design grid. You can add one table field per column in the query design grid. To add all the fields in a table quickly, double- click the asterisk (*) at the top of the list of table fields in the table window.

This figure shows the query design grid with all the fields added. To limit the query results based on field values, in the query design grid, in the Criteria row, enter the criteria that you want to use to limit the results. Table of example criteria. The following table shows some example criteria and explains the effect that they have on the results of a query. Note: Many of the examples in this table use wildcard characters to make the query more flexible or powerful. For more information about how to use wildcard characters in your queries, see the article Access wildcard character reference.

Criteria. Effect> 2. Returns all numbers greater than 2. To find all numbers less than 2. If your database uses the ANSI- 9. Example: Between '2/2/2. And '1. 2/1/2. 00. Not . If your database uses the ANSI- 9.

If your database uses the ANSI- 9. The Once And Future King Author. In(Canada,UK)In a list, finds all records containing Canada or UK. Like . If your database uses the ANSI- 9. Like . If your database uses the ANSI- 9. Like . If your database uses the ANSI- 9. If your database uses the ANSI- 9. Date() Uses the Date function to return all records containing today's date.

Between Date() And Date. Add(. You use zero- length strings when you need to add a value to a required field, but you don't yet know what that value is. For example, a field might require a fax number, but some of your customers might not have fax machines. In that case, you enter a pair of double quotation marks with no space between them (. This procedure shows you how to change a select query to an update query. When you do this, Access adds the Update to row in the query design grid. The following illustration shows an update query that returns all the assets purchased after January 5, 2.

Expression. Result. The expression uses the DSum function because it can operate against more than one table and table field.

On the Design tab, in the Results group, click Run. An alert message appears. To run the query and update the data, click Yes. Note: When you run the query, you might notice that some fields are missing from your result set. If your query contains fields that you don't update, Access does not display those fields in the results, by default. For example, you might include ID fields from two tables to help ensure that your query identifies and updates the correct records.

If you don't update those ID fields, Access does not display them in the results. Top of Page. Update data from one table to another. When you need to update data from one table to another, consider the following rule: the data types for the source and destination fields must either match or be compatible. Furthermore, when you update data from one table to another and use compatible data types instead of matching data types, Access converts the data types of those fields in the destination table. As a result, some of the data in the destination fields may be truncated (deleted). The section Restrictions on data type conversions lists the ways in which you can and cannot convert data types.

The table in this section also explains when converting a data type can change or eliminate some or all the data in a field, and what data might be eliminated. The process of updating data from one table to another follows these broad steps: Create an update query and add both the source and destination tables to the query. Join those tables on the fields that contain the related information. Add the names of your destination fields to the Field row of the query design grid. Add the names of your source fields to the Update To row of the query design grid by using the following syntax: .

In this example, the Clients table is located in a database that you just inherited, and it contains more recent data than the Customers table. You can see that some of the manager names and addresses have changed. For that reason, you decide to update the Customers table with the data from the Clients table. The Clients table. Client IDName. Address. City. State/Province.

Postal Code. Country/Region. Phone. Contact. 1Baldwin Museum of Science. Main St. New York. NY1. 23. 45. USA(5. Josh Barnhill. 2Blue Yonder Airlines. St. Boston. MA2. 34. USA(1. 04) 5. 55- 2.

Waleed Heloo. 3Coho Winery. Ave. S. W. Seattle. WA3. 45. 67. USA(2.

Pica Guido. 4Contoso Pharmaceuticals. Contoso Blvd. London. Bucks. NS1 EW2. England(1. Zoltan Harmuth. 5Fourth Coffee. Calle Smith 2. Mexico City.

Mexico(7) 5. 55- 2. Julian Price. 6Consolidated Messenger.

St. S. Seattle. WA3. USA(2. 06) 5. 55- 2. Miles Reid. 7Graphic Design Institute. Office Pkwy. Tampa. FL8. 76. 54. USA(9.

Optimizing SQL Server Query Performance. SQL Server. Optimizing SQL Server Query Performance. Maciej Pilecki. At a Glance: Analyzing execution plans.

Optimizing queries. Identifying queries to tune. When optimizing your database server, you need to tune the performance of individual queries. This is as important as—perhaps even more important than—tuning other aspects of your server installation, that affect performance, such as hardware and software configurations. Even if your database server runs on the most powerful hardware available, its performance can be negatively affected by a handful of misbehaving queries. In fact, even one bad query, sometimes called a .

In this article, I will look at some of the techniques you can employ to identify and tune the most expensive and worst performing queries on your server. Analyzing Execution Plans. When tuning an individual query, you usually start by looking at the execution plan of that query. The execution plan describes the sequence of operations, physical and logical, that SQL Server. TM will perform in order to fulfill the query and produce the desired resultset. The execution plan is produced by a database engine component called Query Optimizer during the optimization phase of query processing—this takes into account many different factors, such as the search predicates used in the query, the tables involved and their join conditions, the list of columns returned, and the presence of useful indexes that can serve as efficient access paths to the data.

For complex queries, the number of all possible permutations can be huge, so the query optimizer does not evaluate all possibilities but instead tries to find a plan that is . This is because finding a perfect plan may not always be possible; and even when it is possible, the cost of evaluating all the possibilities to find the perfect plan could easily outweigh any performance gains. From a DBA point of view, it is important to understand the process and its limitations. There are a number of ways to retrieve an execution plan for a query: Management Studio provides Display Actual Execution Plan and Display Estimated Execution Plan features, which present the plan in a graphical way. These features offer the most suitable solution for direct examination and are by far the most often- used approach to display and analyze execution plans. An XML- based representation can also be saved to a file with the .

Management Studio to produce a graphical representation. These files can also be saved for later analysis, eliminating, of course, the need to reproduce the execution plan each time you want to analyze it. This is especially useful when you want to compare your plans to see how they change over time.

Estimated Cost of Execution. The first thing you have to understand about execution plans is how they are generated. SQL Server uses a cost- based query optimizer—that is, it tries to generate an execution plan with the lowest estimated cost.

The estimate is based on the data distribution statistics that are available to the optimizer when it evaluates each table involved in the query. If those statistics are missing or outdated, the query optimizer will lack vital information that it needs for the query optimization process and therefore its estimates will likely be off the mark. In such cases, the optimizer will choose a less than optimal plan by either overestimating or underestimating the execution costs of different plans. There are some common false assumptions about the estimated execution cost. In particular, people often assume that the estimated execution cost is a good indicator of how much time the query will take to execute and that this estimate allows you to distinguish good plans from bad plans. This is not true. First, it is sufficiently documented what the units for expressing the estimated cost are and if they have any direct relation to the time of execution.

Second, since this is an estimate and can be wrong, plans with higher estimated costs can sometimes be much more efficient in terms of CPU, I/O, and time of execution, despite their higher estimates. This is often the case with queries that involve table variables—since no statistics are available for table variables, query optimizer always assumes that a table variable contains just one row, even if it contains many times that. Thus, query optimizer will choose a plan based on an inaccurate estimate. Therefore, when comparing the execution plans of your queries, you should not rely on estimated query cost alone. Instead, include the output of the STATISTICS I/O and STATISTICS TIME options in the analysis to understand of what the real cost execution is in terms of I/O and CPU time. One special type of execution plan, called a parallel plan, is worth mentioning here.

If you are running your query on a server with more than one CPU and your query is eligible for parallelization, a parallel plan may be chosen. So why are they preferred over cheaper, non- parallel plans? Thanks to using the processing power of multiple CPUs, parallel plans tend to yield results faster than standard plans.

Depending on your specific scenario, including such variables as available resources and concurrent load from other queries, this situation may be desirable for your setup. If so, you should control which of your queries can produce parallel plans and how many CPUs each can utilize. You do this by setting the max degree of parallelism option on the server level and overriding it on individual query level with OPTION (MAXDOP n) as needed. Analyzing an Execution Plan. Now I'll look at a simple query, its execution plan, and some ways to improve its performance.

Say I execute this query using Management Studio with the Include Actual Execution Plan option turned on in the Adventure Works sample database on SQL Server 2. SELECT c. Customer. ID, SUM(Line. Total). FROM Sales. Sales. Order. Detail od.

JOIN Sales. Sales. Order. Header oh. ON od. Sales. Order. ID=oh. Sales. Order.

ID. JOIN Sales. Customer c ON oh. Customer. ID=c. Customer. ID. GROUP BY c. Customer. ID. As a result, I see the execution plan depicted in Figure 1. This simple query calculates the total amount of orders placed by each customer of Adventure Works. Looking at the execution plan, you can see how the database engine processes the query and produces the result.

Graphical execution plans should be read from top to bottom and from right to left. Each icon represents a logical and physical operation performed, and arrows show data flow between operations. The thickness of the arrows represents the number of rows being passed between operations—the thicker the arrow, the more rows involved. If you place your pointer over one of the operator icons, a yellow Tool. Tip (like the one shown in Figure 2) will display details of that particular operation. Figure 1 Sample execution plan (Click the image for a larger view)Figure 2 Details about an operation (Click the image for a larger view)Looking at each of the operators, you can analyze the sequence of steps performed: The database engine performs a Clustered Index Scan operation on the Sales. Customer table and returns the Customer.

ID column for all the rows in that table. It then performs an Index Scan (meaning a non- clustered index scan) on one of the indexes in the Sales. Sales. Order. Header table.

This is an index on the Customer. ID column, but it also implicitly includes the Sales. Order. ID column (the table clustering key).

The values from both of those columns are returned by the scan. Output from both scans is joined on the Customer. ID column using the Merge Join physical operator. It's fast but requires both inputs to be sorted on a joined column. In this case, both scan operations have already returned the rows sorted on Customer. ID so there is no need to perform the additional sort operation).

Next, the database engine performs a scan of the clustered index on the table Sales. Sales. Order. Detail, retrieving the values of four columns (Sales. Order. ID, Order. Qty, Unit. Price, and Unit. Price. Discount) from all rows in this table. This completes the calculation of the Line. Total column and returns it, along with column Sales.

Order. ID, to the next operator. The output of the Merge Join operator in Step 3 is joined with the output of the Compute Scalar operator from Step 6, using the Hash Match physical operator. Another Hash Match operator is then applied to group rows returned from Merge Join by the Customer.

ID column value and calculated SUM aggregate of the Line. Total column. The last node, SELECT, is not a physical or logical operator but rather a place holder that represents the overall query results and cost. On my laptop, this execution plan had an estimated cost of 3,3. Figure 3). When executed with STATISTICS I/O ON, the query reported performing a total of 1,3. The percentages displayed under each of the operators represent the cost of each individual operator relative to the overall estimated cost of the whole execution plan. Looking at the plan in Figure 1, you can see that most of the total cost of the entire execution plan is associated with the following three operators: the Clustered Index Scan of the Sales.

Sales. Order. Detail table and the two Hash Match operators. But before I move to optimize those, I would like to point out a very simple change in my query that will allow me to eliminate two of the operators altogether. Figure 3 Total estimated execution cost of the query Since the only column I am returning from the Sales.

Customer table is Customer. ID, and this column is also included as a foreign key in the Sales.

Sales. Order. Header. Table, I can completely eliminate the Customer table from the query without changing the logical meaning or the result produced by our query by using this code.

SELECT oh. Customer. ID, SUM(Line. Total). FROM Sales. Sales. Order. Detail od JOIN Sales. Sales. Order. Header oh.

ON od. Sales. Order. ID=oh. Sales. Order. ID. GROUP BY oh. Customer.