Transcript Slide 1

Creating and Altering Tables
cis 407
Object Names
Create Statement
Alter statement
Drop Statement
GUI v ANSI/TSQL
• ANSI works with all DBMSs
– (e.g., oracle, db2, …)
• Need a script to insure same DB schema
on development, test, production systems
• Easier (in my opinion)
Object Names in SQL Server
• [serverName.[DatabaseName.[SchemaName.]]]ObjectN
ame
• Schema Name --- ownership
• Generally DBO – best to keep all relations in database
owned by default DBO role.
• If my login is given create table authority then that table
owned by beard.mytable
• If fred is db owner (created the database) a table he
creates would be owned by fred.mytable
• Confusing
• Expensive: if relations in db owned by multiple owners
than constantly must check access.
– Stick to DBO by having anyone that needs to create tables have
db sysadmin role.
Database, server names
• Select *
from northwind.dbo.orders
select *
from northwind..orders (dbo default)
• Can access relation on another server
• Select *
from myserver.northwind..orders
– Server could be in PRC
Create Database
• CREATE DATABASE <database name>
[on primary] (
[name = <‘logical file name’>,]
[,SIZE=<size in kilo,.., terabytes>]
[,MAXSIZE= <size in kilo,…,terabytes>]
[,filegrowth=<kilo,…, terabytes>] ) ]
[log on
[name = <‘logical file name’>] ] …..
[collate <collation name> ]
……
CREATE DATABASE
• Create database Accounting
on (name = ‘accounting’,
filename = ‘c:\program files\microsoft sql
server\MSQL.1\mssql\data\accountingdata.mdf’.,
size = 10, maxsize = 50, fielgrowth = 5)
• Log on (name = ‘accountingLog’,
filename = ‘c:\program files\microsoft SQL server\
sqsql.1\mssql\data\accountingLog.ldf’,
size = 5MB, maxsize = 25MB, filegrowth=5mb)
• go
Create Table (pg 128)
• Create table customers
(
customerNo int identity(1,1) not null,
customerName varchar(30) not null,
address1
varchar(30) not null,
address2
varchar(30) not null,
…..
• See example from web page
• Exec sp_help customers
• Tsql data types pg 12-15
Alter Statement
• Almost identical to create statement but
change ‘create’ to ‘alter
• Don’t want to have to delete relation and
recreate (lose all that data!!)
Drop statement
• Drop customers
• BE CAREFUL – no “are you sure”
questions – it assumes you know what you
are doing.
• Use master
drop database accounting