Transcript PowerPoint 97

SQL
Table Basics
Database Objects
•
•
•
•
•
Tables
Temporary tables (begin with #)
Views
Keys
Indexes
Tables
CREATE TABLE table_name
(
applicant_name CHAR(50) NOT NULL,
applicant_address CHAR(255) NULL
)
DROP TABLE table_name
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
System Procedures
• The Swiss Army Knife: sp_help
• Without parameters: all objects
• With parameters: structure of object
sp_help table_name
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.
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) )
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 >= 0 or x <= 0)
go
What does the select statement return?
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)
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
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
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)
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 [,…])
Clustered Indexes
• One per table.
• Physical sort of table data on indexed
column.
• Good for range retrievals because of
adjacency.
From SQL Server 6 Unleashed.
Nonclustered Indexes
• Separate structure independent of physical
sort order.
• Up to 249 nonclustered indexes on a table.
• Index keys are in sorted order.
From SQL Server 6 Unleashed