SQL

1
SQL

• Table Basics

2
Database Objects

• Tables
• Temporary tables (begin with )
• Views
• Keys
• Indexes

3
Tables

• CREATE TABLE table_name
• (
• applicant_name CHAR(50) NOT NULL,
• applicant_address CHAR(255) NULL
• )
• DROP TABLE table_name

4
Object Names

• All SQL Server object names are up to 30
  characters in length, and are case-sensitive. No
  embedded blanks or punctuation allowed.
• Full pathname
• database_name.owner_name.object_name

5
System Procedures

• The Swiss Army Knife sp_help
• Without parameters all objects
• With parameters structure of object
• sp_help table_name

6
Basic Datatypes

• CHAR() a fixed-length string field.
• VARCHAR() a variable-length string field.
• TEXT strings up to 2GB. Note difficult to
  manage in T-SQL. Easy through ODBC.
• INTEGER -231 to 231
• DATETIME date and time. See convert command for
  formats.

7
Column Properties

• NULL (a value does not need to be specified)
• NOT NULL (a value must be specified)
• IDENTITY sequential, unique numbering.
• identity(seed,increment)
• CREATE TABLE table_name(
• Column_name int identity(1,1) )

8
NULL

• If possible, avoid allowing null columns.
• 3 valued logic complexity and bugs.
• create table truth_table (
• x int NULL )
• go
• insert truth_table(x) values(NULL)
• go
• select 1 from truth_table where (x gt 0 or x lt
  0)
• go
• What does the select statement return?

9
Temporary Tables

• Temporary tables are like regular tables, but
  they are for temporary storage of records.
• All SELECT, INSERT, UPDATE and DELETE operations
  are allowed on them.
• If not explicitly dropped, they will be dropped
  by the server when the batch completes.
• Are created in the database tempdb.
• Most importantly, the table name begins with a
  pound sign ().
• CREATE TABLE temp(a int,b int)

10
Views

• A named select statement, a virtual table.
• CREATE VIEW view_name
• (
• SELECT a.applicant_name,b.skill_code
• FROM applicant a, applicant_skill b
• WHERE a.applicant_id b.applicant_id
• )
• go
• select from view_name order by skill_code
• go

11
Primary Keys

• PKs require all values in a column be unique.
  Defaults to a clustered index.
• ALTER TABLE table_name
• ADD CONSTRAINT PK_table_name_id
• PRIMARY KEY (table_name_id)
• sp_helpconstraint table_name
• ALTER TABLE table_name DROP CONSTRAINT
  PK_table_name_id

12
Foreign Key

• One or more columns of a table whose values must
  be equal to a primary key in another table.
• SQL Server enforces the referential integrity.
• ALTER TABLE table_name
• ADD CONSTRAINT FK_table_name_id
• FOREIGN KEY (table_name_id)

13
Indexes

• Maintain uniqueness of the indexed columns
  (primary keys).
• Provide fast access to the tables. Avoidance of
  evil table scans.
• CREATE UNIQUE CLUSTERED NONCLUSTERED INDEX
  table_name_idx ON table_name (column ,)

14
Clustered Indexes

• One per table.
• Physical sort of table data on indexed column.
• Good for range retrievals because of adjacency.

15
From SQL Server 6 Unleashed.
16
Nonclustered Indexes

• Separate structure independent of physical sort
  order.
• Up to 249 nonclustered indexes on a table.
• Index keys are in sorted order.

17
From SQL Server 6 Unleashed