Sql server® 2012 t-sql fundamentals

 

CHAPTER 8

 

Data Modification

 

 261

the range. You call the procedure, indicate the size of the range you want, and collect the first value in 

the range, as well as other information, by using output parameters. Here’s an example of calling the 

procedure and asking for a range of 1,000 sequence values.

DECLARE @first AS SQL_VARIANT; 

 

EXEC sys.sp_sequence_get_range 

  @sequence_name     = N'dbo.SeqOrderIDs', 

  @range_size        = 1000, 

  @range_first_value = @first OUTPUT ; 

 

SELECT @first;

If you run the code twice, you will find that the returned first value in the second call is greater 

than the first by 1,000.

Note that like identity, the sequence object does not guarantee that you will have no gaps. If a new 

sequence value was generated by a transaction that failed, the sequence change is not undone.

When you’re done, run the following code for cleanup.

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

IF OBJECT_ID('dbo.SeqOrderIDs', 'So') IS NOT NULL DROP SEQUENCE dbo.SeqOrderIDs;

Deleting Data

T-SQL provides two statements for deleting rows from a table—DELETE and TRUNCATE. In this sec-

tion, I’ll describe those statements. The examples I provide in this section are against copies of the 

Customers and Orders tables from the Sales schema created in the dbo schema. Run the following 

code to create and populate those tables.

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

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

 

CREATE TABLE dbo.Customers 

( 

  custid       INT          NOT NULL, 

  companyname  NVARCHAR(40) NOT NULL, 

  contactname  NVARCHAR(30) NOT NULL, 

  contacttitle NVARCHAR(30) NOT NULL, 

  address      NVARCHAR(60) NOT NULL, 

  city         NVARCHAR(15) NOT NULL, 

  region       NVARCHAR(15) NULL, 

  postalcode   NVARCHAR(10) NULL, 

  country      NVARCHAR(15) NOT NULL, 

  phone        NVARCHAR(24) NOT NULL, 

  fax          NVARCHAR(24) NULL, 

  CONSTRAINT PK_Customers PRIMARY KEY(custid) 

); 

 

www.it-ebooks.info

---

262  

Microsoft SQL Server 2012 T-SQL Fundamentals

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), 

  CONSTRAINT FK_Orders_Customers FOREIGN KEY(custid) 

    REFERENCES dbo.Customers(custid) 

); 

GO 

 

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

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