SQL Server 2000 Design and Implementation, Part VIII -
Modifying Data...

This series of articles is focusing primarily on the physical data store Microsoft developers are most likely to use for their applications, SQL Server. The main reference for this series of articles is MCSE: SQL Server 2000 Design by Israel and Jones.

By: Chris Sully

Date: June 5, 2004

Introduction

This series of articles is focussing primarily on the physical data store Microsoft developers are most likely to use for their applications, SQLServer. The main reference for this series of articles is MCSE: SQLServer 2000 Design by Israel and Jones.

Here's a quick overview of what we've covered so far as well as what we shall be covering:

Article I: we started with developing a logical data model, before looking at how we go about implementing the physical database in SQLServer 2000.

Article II: creating and maintaining databases, in particular the files that comprise the database and how they can be split into filegroups and why they should be on occasion.

Article III: creating and maintaining tables - looking at what we can store in tables, and how we do so.

Article IV: data integrity - default values, check constraints and rules, primary keys, unique constraints, as well as foreign keys and relationships.

Article V: indexes and the related area of statistics.

Article VI: creating and maintaining the various database objects that exist- views, stored procedures, transactions, user defined functions and triggers. This concludes the look at the physical design per se. The remainder of the articles look at issues relating to data access.

Article VII: accessing data

Article VIII: modifying data

Article IX: importing and exporting data

Article X: locking

Article XI: security

Article XII: analysing and optimising data access

Thus far we've reached article VIII: modifying data. In this article we'll be looking at modifying data using the following techniques: INSERT, UPDATE and DELETE statements, cursors, distributed queries and XML.

The examples presented are based on the Northwind database supplied with SQL Server.

INSERT

There are three formats for inserting a single row into a table:

an explicit value can be defined
a select query can be used that returns a single row
a stored procedure can be called that returns a single row in a single result set

Not every column must be included in an INSERT statement if the table meets certain criteria. If a column is identified as an identity field, it should not be included. Additionally a column may be omitted when the column either allows NULL values or has a default value bound to the column.

If an explicit value is required for an identify field SQLServer does provide a mechanism for inserting the data – you can turn off identity inserts temporarily with SET IDENTITY_INSERT ON|OFF.

Inserting multiple rows works exactly like inserting single rows with the exception that the VALUES clause cannot be used. Either a SELECT query or a stored procedure must be called to insert a set of rows. The DEFAULT keyword cannot be used in either the SELECT query or in the stored procedure, so values must be supplied for all columns if there is no column list explicitly defined.

You are also able to specify table hints as part of the standard syntax (see Books Online) to influence the query optimizer. We’ll look at these in greater detail in a subsequent article but these include HOLDLOCK, PAGELOCK, REPEATABLEREAD, etc.

Examples:

Single row:

INSERT [Order Details]
(OrderID, ProductID, UnitPrice, Quantity, Discount)
VALUES
(10248, 14, 50, 5, 0)

Multiple rows:

Insert Products
(ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued)
SELECT ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice *.6, UnitsInStock, 0,0,1
FROM Products

UPDATE

Unlike INSERT where the simplest form adds a single row, the simplest UPDATE statement will update all rows in a table.

UPDATE <table_name>
SET <column_name> = <expression>

Normally you would qualify with a WHERE clause to specify a particular row or set of rows. Further the FROM clause can be added to allow other tables from those being updated to be used to make qualifications.

To update multiple columns you simply separate the <column_name> = <expression> with commas.

Views can similarly be updated, with the following restrictions:

the view and the column being updated must be defined as updateable
the definition of the view must contain at least one table in the FROM clause
the columns being updated cannot be derived columns
the UPDATE statement can only affect a single base table in the view
any columns from the base table that are included in a view can be updated and are updated using the name of the column defined in the view
if the view definition contains aggregates, then it is not updateable with the exception of the situation where aggregates appear in a subquery and are not affected by any update operations

Example:

UPDATE Products
SET UnitPrice=UnitPrice*0.8, Discontinued=1

DELETE

With DELETE the simplest form deletes all rows from a table:

DELETE <table_name>

Note that on large tables deleting all rows may take a large amount of time as each deletion is logged in the transaction log. It is usually quicker to issue a TRUNCATE TABLE <table_name>. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log.

To DELETE a set of rows use the FROM and WHERE statements to qualify the operation.

Modifying data with cursors

Cursors were introduced in the last article. If the default values are used when declaring a cursor it will not be updatable. The UPDATE keyword should be included in the declaration. Optionally, a list of columns that are updateable can be included.

The UPDATE statement used to modify the base table actually references the base table and must conform to all rules for UPDATE statement, though the row is qualified using the cursor rather than the standard WHERE clause.

e.g. UPDATE <table_name>
SET <column_name> = <expression>
WHERE CURRENT OF <cursor_name>

DECLARE abc CURSOR FOR
SELECT CompanyName
FROM Shippers

OPEN abc
GO

FETCH NEXT FROM abc
GO

UPDATE Shippers SET CompanyName = N'Speedy Express, Inc.'
WHERE CURRENT OF abc
GO

CLOSE abc
DEALLOCATE abc

Modifying data with XML

The OPENXML function provides a view of the XML data that looks like a standard SQLServer rowset. This rowset can be used in place of any other rowset in SELECT statements. This includes the SELECT statement used for INSERT, UPDATE and DELETE statements.

In order for an XML data source to be used in TSQL statements, the document must be parsed and stored in memory in SQLServer in a tree representation. The system stored procedure SP_XML_PREPAREDOCUMENT achieves this. The stored procedure returns a handle for the internal representation of the XML document for use by other processes. The (basic) syntax is:

SP_XML_PREPAREDOCUMENT hdoc OUTPUT

where hdoc is the handle or reference to the internal XML representation returned to the calling program which may then reference hdoc if it needs to refer to the internal representation.

See detail of the other available options in books online.

Note that after processing an XML document that was created in memory with SP_XML_PREPAREDOCUMENT, you should always remove the document with SP_XML_REMOVEDOCUMENT to release resources.

Once the document is parsed and stored in memory, the OPENXML function can be used to provide a rowset view of the XML document, basic syntax as follows:

OPENXML(hdoc INT[in], row_pattern nvarchar[in],[flags byte[on]])
[WITH (schema_declaration | table_name)]

hdoc is the integer handle to the internal document representation

rowpattern is an XPATH pattern that identifies the nodes to be processed

flags indicates the type of mapping to be used between XML and rowset data, as well as determining how extra columns should be filled. Vales are 0 (Default – attribute centric, 1 – attribute centric and 2 - element centric). For detail see books online.

schema declaration provides for the specific mapping between the nodes in the XML document and the resulting rowset.

table_name can be given in place of a schema declaration – the name of an existing schema that holds the desired schema of the result set.

If no WITH clause is included then the results of the OPENXML function are returned as an edge table which is capable of representing the structure of the XML document in a single table.

You can insert, update and delete rows with XML using OPENXML. Without going into specifics the process is basically:

Create an internal representation of the XML document with SP_XML_PREPAREDOCUMENT
Perform the INSERT/ UPDATE / DELETE using the FROM OPENXML () WITH ... syntax, referencing the internal representation of the XML document
Destroy the internal representation of the XML document with SP_XML_REMOVEDOCUMENT

An example:

declare @idoc int
declare @doc varchar(1000)
set @doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
  <Order OrderID="10248" CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
    <OrderDetail ProductID="11" Quantity="12"/>
    <OrderDetail ProductID="42" Quantity="10"/>
  </Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
  <Order OrderID="10283" CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
    <OrderDetail ProductID="72" Quantity="3"/>
  </Order>
</Customer>
</ROOT>'
--Create an internal representation of the XML document.
exec sp_xml_preparedocument @idoc OUTPUT, @doc
DELETE Customers
FROM OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail',2)
    WITH (OrderID int '../@OrderID',
    CustomerID varchar(10) '../@CustomerID',
    OrderDate datetime '../@OrderDate',
    ProdID int '@ProductID',
    Qty int '@Quantity') b
WHERE Customer.CustomerID=b.CustomerID

EXEC sp_xml_removedocument @idoc

Modifying data via distributed queries

The same techniques described in the last article for retrieving data can be used to modify the data, if the provider for the data source supports data modification. As previously described you can use either linked servers or the OPENROWSET function.

There we leave modifying data. In the next article we consider the options for importing and exporting data to and from SQLServer.

References

MCSE: SQLServer 2000 Design
Israel and Jones
Sybex

MSDN

Books Online