mytest_customer_create.sql
/*==============================================
* *
* create table customer *
* *
==============================================*/
USE mytest; -- you must have created the database mytest
IF OBJECT_ID ('customer', 'U') IS NOT NULL
BEGIN
PRINT 'The table customer already exists. Delete it.'
RETURN
END
-- =========== create table customer ===========
CREATE TABLE customer (
custid INT NOT NULL IDENTITY(1000, 1) PRIMARY KEY,
firstname VARCHAR(40) NULL,
lastname VARCHAR(40) NOT NULL,
birthdate DATE NULL,
gender VARCHAR(1) NULL,
street VARCHAR(20) NULL,
city VARCHAR(20) NULL,
country_id INT NULL
)
CREATE INDEX idx_name ON customer(lastname, firstname)
CREATE INDEX idx_name_birthdate ON customer(lastname, birthdate) -- useful if many searches are performed on name and birth date