Insert, Update, Delete, Truncate

1
Insert, Update, Delete, Truncate
2
Insert

• Basic Insert Form
• Insert into table-a
• (col_1, col_2, col_n)
• Values
• (val_1, val_2, val_n)

3
Multi-Row Insert

• An Insert and Select can be combined to insert
  multiple rows from one table into a second table
• Insert into table_a
• Select col1, col2, coln
• From table_b
• Where coln YES

4
Update

• Basic Update Form
• Update table_a
• set col_1 1
• ,col_2 col_21.1
• Where col_n YES

5
Update with Case Statement

• A CASE statement can be used within an UPDATE
• Update employee_salaries
• set salary
• case review
• when 4 then salary salary 2.0
• when 3 then salary salary 1.6
• when 2 then salary salary 1.2
• else salary
• end

6
Truncate Statement

• The TRUNCATE removes all rows from a table, like
  an unqualified delete
• The Truncate is much faster because it invokes
  less overhead than an unqualified delete

7
Truncate Example

• Run this code against your CCUSERXX DB
• Begin tran
• Select count() from student
• Truncate table student
• Select count() from student
• Rollback tran
• Select count() from student

8
RI Rules
9
Referential Constraints

• In ISQA425 you learned to code Primary Keys and
  Foreign Keys
• The Foreign Key Definition tied a child table to
  a parent table
• Order/Order Detail in Northwood is an example

10
RI Implications

• There are implications for update and delete
  actions based on how you want the RI relationship
  to work

11
Update RI

• No Action (default)
• If NO ACTION is specified, SQL Server raises an
  error and the update action on the row in the
  parent table is rolled back.
• Cascade
• If CASCADE is specified, the row is updated in
  the referencing table if that row is updated in
  the parent table.

12
RI Update Example

• For example, in the Northwind database, the
  Orders table has a referential relationship with
  the Customers table Orders.CustomerID foreign
  key references the Customers.CustomerID primary
  key.
• If an UPDATE statement is executed on a row in
  the Customers table, and an ON UPDATE CASCADE
  action is specified for Orders.CustomerID, SQL
  Server checks for one or more dependent rows in
  the Orders table. If any exist, the dependent
  rows in the Orders table are updated, as well as
  the row referenced in the Customers.
• Alternately, if NO ACTION is specified, SQL
  Server raises an error and rolls back the update
  action on the Customers row if there is at least
  one row in the Orders table that references it.

13
Delete RI

• No Action (default)
• If NO ACTION is specified, SQL Server raises an
  error and the delete action on the row in the
  parent table is rolled back.
• Cascade
• If CASCADE is specified, a row is deleted from
  the referencing table if that row is deleted from
  the parent table.

14
RI Delete Example

• For example, in the Northwind database, the
  Orders table has a referential relationship with
  the Customers table. The Orders.CustomerID
  foreign key references the Customers.CustomerID
  primary key.
• If a DELETE statement is executed on a row in the
  Customers table, and an ON DELETE CASCADE action
  is specified for Orders.CustomerID, SQL Server
  checks for one or more dependent rows in the
  Orders table. If any, the dependent rows in the
  Orders table are deleted, as well as the row
  referenced in the Customers table.
• On the other hand, if NO ACTION is specified, SQL
  Server raises an error and rolls back the delete
  action on the Customers row if there is at least
  one row in the Orders table that references it.

15
Manual RI Rules

• A program can internally control RI rules without
  the use of FK Definitions, but with use of
  Transactions

16
Manual Delete RI

• If child record is found that belongs to the
  parent, then
• No Action Rollback the transaction
• Cascade Delete all the children then the parent

17
Manual No Action Delete

• Begin tran
• Declare _at_rowcnt, _at_error_var
• Delete
• From child_table
• Where key_col Row1
• Select _at_rowcnt _at__at_rowcount, _at_error_var _at__at_error
• If (_at_error_var ltgt 0) goto on_error
• If (_at_rowcnt gt 0) goto on_error
• Delete
• From parent_table
• Where key_col Row1
• Select _at_rowcnt _at__at_rowcount, _at_error_var _at__at_error

• If (_at_error_varltgt 0) goto on_error
• Commit tran
• Goto finish
• On_error
• Rollback tran
• Finish

18
Manual Cascade Delete

• Begin tran
• Declare _at_rowcnt int, _at_error_var int
• Delete
• From parent_table
• Where key_col Row1
• Select _at_rowcnt _at__at_rowcount, _at_error_var _at__at_error
• If (_at_error_var ltgt 0) goto on_error
• Delete
• From child_table
• Where key_col Row1

• Select _at_rowcnt _at__at_rowcount, _at_error_var _at__at_error
• If (_at_error_varltgt 0) goto on_error
• Commit tran
• Goto finish
• On_error
• Rollback tran
• Finish

19
Manual Update RI

• If a child record is found that belongs to the
  parent record then
• No Action Rollback Transaction
• Cascade Update children, then parent
• If a child record is being updated
• Verify that a parent exists for it

20
Manual No Action Update

• Begin tran
• Declare _at_rowcnt int, _at_error_var int
• Update child_table
• set key_col Row1a
• Where key_col Row1
• Select _at_rowcnt _at__at_rowcount, _at_error_var _at__at_error
• If (_at_error_varltgt 0) goto on_error
• If (_at_rowcnt gt 0) goto on_error
• Update parent_table
• set key_col Row1a
• Where key_col Row1

• Select _at_rowcnt _at__at_rowcount, _at_error_var _at__at_error
• If (_at_error_varltgt 0) goto on_error
• Commit tran
• Goto finish
• On_error
• Rollback tran
• Finish

21
Manual Cascade Update

• Begin tran
• Declare _at_rowcnt int, _at_error_var int
• Update parent_table
• set key_col Row1a
• Where key_col Row1
• Select _at_rowcnt _at__at_rowcount, _at_error_var _at__at_error
• If (_at_error_varltgt 0) goto on_error
• Update child_table
• set key_col Row1a
• Where key_col Row1

• Select _at_rowcnt _at__at_rowcount, _at_error_var _at__at_error
• If (_at_error_varltgt 0) goto on_error
• Commit tran
• Goto finish
• On_error
• Rollback tran
• Finish

22
_at__at_ROWCOUNT

• _at__at_ROWCOUNT is a system variable that contains the
  number of rows impacted by the latest SQL
  statement

23
_at__at_rowcount and _at__at_error

• _at__at_rowcount and _at__at_error are set after an SQL
  statement is executed
• _at__at_rowcount and _at__at_error are zeroed out when the
  next statement is executed -- even if it is not
  a SQL statement
• They must be captured immediately after the SQL
  statement

24
Capturing _at__at_rowcount and _at__at_error

• Do this by using a select statement
• Declare _at_rowcnt int, _at_error_var int
• Delete from .
• Select _at_rowcnt _at__at_rowcount, _at_error_var _at__at_error

25
Printing Error Codes

• Error codes can be printed by using the print
  statement
• Print This statement had an error
  rtrim(convert (char(5) _at_error_var))

26
Last RI Notes

• RI Constraints can form trees that are larger
  than a Parent/Child relationship
• They can go many levels deep. If they do and you
  are manually maintaining them, you may have
  significant coding
• Beware of circular RI definitions

27
Indexes
28
Indexes

• Indexes are a user-defined disk structure used to
  provide fast access to data when the data can be
  searched by value of the index key

29
Purpose of Index

• To provide fast access
• To enforce uniqueness

30
Types of Indexes

• Unique/Non-unique Indexes
• Clustered/Non-clustered Indexes
• Primary Key

31
Unique/Non-unique

• Whether duplicate values will be allowed for
  column(s) in a table
• A table can have multiple unique indexes

32
Clustered/Non-clustered

• A non-clustered index does not impact the
  physical order of the data in the table. It can
  be any order, generally entry order sequence
• A clustered index will keep the row physically in
  the same order as the order of the indexed
  columns
• Only one clustered index is allowed per table

33
Primary Index

• A primary index is an unique index that is
  required for a column(s) that are a tables
  primary key

34
Create Index

• CREATE UNIQUE CLUSTERED NONCLUSTERED
  INDEX index_name     ON table view (
  column ASC DESC ,...n ) WITH lt
  index_option gt ,...n ON filegroup
• lt index_option gt      PAD_INDEX
          FILLFACTOR fillfactor
          IGNORE_DUP_KEY         DROP_EXISTING
      STATISTICS_NORECOMPUTE
      SORT_IN_TEMPDB