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