Sams Teach Yourself Transact-SQL in 21:INSERT, UPDATE, and DELETE

Deleting Tables

To remove an object, such as a table, use the DROPoperation. If you need to re-create a table, you must drop it before you can re-create it. This drops the customertable:

drop table customer

Once you drop a table, it is gone forever. If you had a millionrows in the table, they’re all gone, too. The server will letyou drop a table that has rows in it. If you accidentally drop atable, the only way to recover it is to restore it from the backup.Many times, tables have relationships that make restoring just oneof them a very sticky issue. The moral is: Drop stuff only whenyou’re really sure you don’t need it any more.

Limits on Table Creations

A row of data may be no more than 1,962 bytes in size. Theserver stores everything on 2KB “pages,” each of whichis 2,048 bytes in size. After leaving space for tracking the pageholding rows that came before this page, the page holding rows thatcome after this page, log information, and other overhead, 1,962bytes are left over for data. Each row needs to fit completely ona page, so it needs to squeeze into 1,962 bytes.

Adding Identity Columns

You may specify one numeric column in a table as an identitycolumn. Specifically, you may use an integer, smallint, tinyint,decimal or numeric type. This column gets a unique ascending valueevery time a row is inserted into the table. No null status needsto be defined, because identity columns always get a value and areconsidered not null. Identity columns are used for two things:

• Most often, identity columns are used as a way touniquely identify a row in a table where other columns cannot beused to guarantee uniqueness.

• They are also useful as a method for restricting themaximum number of rows allowed in a table.

WARNING: Identity columns cannot be used to provideabsolute row numbers in a table. If an INSERT fails forsome reason (if you don’t provide a value for a non-nullcolumn, for instance), the next identity value will be skipped. Ifa row’s identity column contains the value 150, itis no guarantee that this is the 150th row in the table. This isbecause identity values are allocated before the server knowswhether the INSERT will succeed. Identity values are neverreused.

To restrict the maximum number of rows in a table, define theidentity column on a data type, like a tiny integer, that allowsa low maximum value. Tiny integers can have a value between0 and 255. This allows for a maximum of 256discrete values, although the number of rows inserted may be less(because some could be skipped).

The IDENTITY keyword is followed, optionally, by twoarguments: seed and step. The seed isthe number that the first row will receive as its identity column;the step gives the amount to increase subsequent identity values. These parameters take default values of 1 and0, respectively, if no values are provided.

Here, an inventory table has an identity column defined oninv_id:

create table inventory(
     inv_id int identity(1,1),
     item_id int not null,
     qty tinyint null
     )

I’ll come back to this example in a moment to show you howto add columns to a table with an identity column.

Defining Default Values

You may provide a default value for a column, which is insertedwhen an explicit value is not provided. In the inventory table,this defines a default value of 1 on the qty column:

/* First, I must drop the table I created in the last example... */
drop table inventory
go
create table inventory(
     inv_id int identity(1,1),
     item_id int not null,
     qty tinyint null default 1
     )

Before I re-create the table, I need to drop it. The wordGO indicates that ISQL/w should execute the DROPTABLE, GO, wait for the command to complete, and thencontinue with the CREATE TABLE.

When I create the table, after I define the column status forqty, I define a default constraint. If a row is inserted intoinventory without providing a value for qty, the column receivesa 1 for that row.

Let’s look at the INSERT statement so that you canstart putting some of these new tables to use.

The INSERT Statement

INSERT allows you to put new rows in a table. Here isan example where I first create a simple table and thenINSERT some rows into it. I’ll finish up by selectingthe rows out again:

create table t1(c1 int null)
go
insert into t1(c1) values(0)
insert t1(c1) values(-41)
insert t1 values(200)
insert t1 values(null)
go
select c1 from t1
go

(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
c1
--------
0
-41
200
null
(4 row(s) affected)

The server acknowledges the inserts by returning (1row(s) affected).

TIP: You can turn off the row count acknowledgmentsby using the NOCOUNT option:
set nocount on

The data you inserted is returned to you in the order youinserted it. You did not specify an ORDER BY clause, andthere are no indexes on your table, so data is kept in the table,and returned to you, in the order it was inserted.

To insert rows, you specify the columns you want to insert inparentheses, after the table name, as in the first and secondinserts. Follow this with the keyword VALUES and specifythe values for the row in the parentheses. If you do not specifya column list, values for all columns must be provided. When youdo not provide a column list, values must be provided in the orderin which they appeared in the original CREATE TABLEstatement.

WARNING: If you are inserting data into tables from anapplication program, you should always provide a column list. Thiswill prevent problems should the table be recreated with itscolumns in a different order.

Here is the syntax statement for a simpleINSERT:

insert   [into]   <tablename>   [(<column>[,<column>…]   )   ]
         values   (<data>   [,   <data> …]   )

Table of Contents

Используются технологии uCoz