Sql server® 2012 t-sql fundamentals

 

CHAPTER 8

 

Data Modification

 

 277

To get around this problem, define a table expression that returns both the column that you need 

to update (col2) and a result column based on an expression with the ROW_NUMBER function (call it 

rownum). The outer statement against the table expression would then be an UPDATE statement set-

ting col2 to rownum. Here’s how the code would look if you were using a CTE. 

WITH C AS 

( 

  SELECT col1, col2, ROW_NUMBER() OVER(ORDER BY col1) AS rownum 

  FROM dbo.T1 

) 

UPDATE C 

  SET col2 = rownum;

Query the table to see the result of the update.

SELECT * FROM dbo.T1;

You get the following output.

col1        col2 

----------- ----------- 

10          1 

20          2 

30          3

Modifications with TOP and OFFSET-FETCH

SQL Server supports using the TOP option directly in INSERT, UPDATE, DELETE, and MERGE state-

ments. When you use the TOP option, SQL Server stops processing the modification statement as 

soon as the specified number or percentage of rows are processed. Unfortunately, unlike with the 

SELECT statement, you cannot specify an ORDER BY clause for the TOP option with modification 

statements. Essentially, whichever rows SQL Server happens to access first will be the rows affected 

by the modification. 

An example for a typical usage scenario for modifications with TOP is when you have a large modi-

fication, such as a large deletion operation, and you want to split it into multiple smaller chunks.

The new alternative to TOP, OFFSET-FETCH, is considered to be part of the ORDER BY clause in 

T-SQL. Because modification statements do not support an ORDER BY clause, they do not support the 

OFFSET-FETCH option either—at least not directly. 

www.it-ebooks.info

---

278  

Microsoft SQL Server 2012 T-SQL Fundamentals

I’ll demonstrate modifications with TOP by using a table called dbo.Orders that you create and 

populate by running the following code.

IF OBJECT_ID('dbo.OrderDetails', 'U') IS NOT NULL DROP TABLE dbo.OrderDetails; 

IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL DROP TABLE dbo.Orders; 

 

CREATE TABLE dbo.Orders 

( 

  orderid        INT          NOT NULL, 

  custid         INT          NULL, 

  empid          INT          NOT NULL, 

  orderdate      DATETIME     NOT NULL, 

  requireddate   DATETIME     NOT NULL, 

  shippeddate    DATETIME     NULL, 

  shipperid      INT          NOT NULL, 

  freight        MONEY        NOT NULL 

    CONSTRAINT DFT_Orders_freight DEFAULT(0), 

  shipname       NVARCHAR(40) NOT NULL, 

  shipaddress    NVARCHAR(60) NOT NULL, 

  shipcity       NVARCHAR(15) NOT NULL, 

  shipregion     NVARCHAR(15) NULL, 

  shippostalcode NVARCHAR(10) NULL, 

  shipcountry    NVARCHAR(15) NOT NULL, 

  CONSTRAINT PK_Orders PRIMARY KEY(orderid) 

); 

GO 

 

INSERT INTO dbo.Orders SELECT * FROM Sales.Orders;

The following example demonstrates the use of a DELETE statement with the TOP option to delete 

50 rows from the Orders table.

DELETE TOP(50) FROM dbo.Orders;

Because you are not allowed to specify a logical ORDER BY for the TOP option in a modification 

statement, this query is problematic in the sense that you can’t control which 50 rows will be deleted. 

They will be the first 50 rows from the table that SQL Server happens to access first. This problem 

demonstrates the limitations of using TOP for modifications.

Similarly, you can use the TOP option with UPDATE and INSERT statements, but again, an ORDER 

BY is not allowed. As an example of an UPDATE statement with TOP, the following code updates 50 

rows from the Orders table, increasing their freight values by 10.

UPDATE TOP(50) dbo.Orders 

  SET freight += 10.00;

Again, you cannot control which 50 rows will be updated; they are the first 50 rows that SQL Server 

happens to access first.

In practice, of course, you would usually care which rows are affected and you wouldn’t want them 

to be chosen arbitrarily. To get around this problem, you can rely on the fact that you can modify 

data through table expressions. You can define a table expression based on a SELECT query with the 

www.it-ebooks.info

---