A Developer's Guide to Data Modeling for SQL Server:
Covering SQL Server 2005 and 2008

A Developer's Guide to Data Modeling for SQL Server

Sample Code

Eric Johnson & Josh Jones

ISBN: 0321497643

“A Developer’s Guide to Data Modeling for SQL Server explains the concepts and practice of data modeling with a clarity that makes the technology accessible to anyone building databases and data-driven applications. If you're new to data modeling, or find the need to brush up on its concepts, this book is for you."


Peter Varhol, Executive Editor, Redmond Magazine


Model SQL Server Databases That Work Better, Do More, Evolve More Smoothly


Effective data modeling is essential to ensuring that your databases will perform well, scale well, and evolve to meet changing requirements. However, if you’re modeling databases to run on Microsoft SQL Server 2008 or 2005, theoretical or platform-agnostic data modeling knowledge isn’t enough: models that don’t reflect SQL Server’s unique real-world strengths and weaknesses often lead to disastrous performance.


A Developer’s Guide to Data Modeling for SQL Server is a practical, SQL Server-specific guide to data modeling for every developer, architect, and administrator. This book offers you invaluable start-to-finish guidance for designing new databases, redesigning existing SQL Server data models, and migrating databases from other platforms.


You’ll begin with a concise, practical overview of the core data modeling techniques that are in demand in today’s businesses. Next, you’ll walk through requirements gathering, and discover how to convert requirements into effective SQL Server logical models. Finally, you’ll systematically transform those logical models into physical models that make the most of SQL Server’s extended functionality. All of this book’s many examples are available for download on a companion Web site.


This book enables you to

  • Master all the elements of a successful logical data model

  • Understand your data model’s physical elements, from storage to referential integrity

  • Provide programmability via stored procedures, user-defined functions, triggers, and .NET CLR integration

  • Normalize data models, one step at a time

  • Gather and interpret requirements more effectively

  • Learn an effective, start-to-finish methodology for creating logical models

  • Overcome modeling problems related to entities, attribute, data types, storage overhead, performance, and relationships

  • Create physical models with SQL Server 2008 or 2005: from establishing naming guidelines through implementing business rules and constraints

  • Use SQL Server’s unique indexing capabilities, and overcome their limitations

  • Create abstraction layers that enhance security, extensibility, and flexibility

Sample Code

Chapter 3

********************************************************* CREATE VIEW customer_address
AS
SELECT customer.first_name, customer.last_name,
    customer.phone, address.address_line1, address.city,
    address.state, address.zip
FROM customer br />     ON address.customer_id = customer.customer_id
WHERE address.type = 'home' WHERE address.type = 'home'

********************************************************* CREATE TABLE Products(
    sku int NOT NULL PRIMARY KEY,
    modelnumber varchar(25) NOT NULL,
    name varchar(100) NOT NULL,
    manufacturer varchar(25) NOT NULL,
    description varchar(255) NOT NULL,
    warranty_details varchar(500) NOT NULL,
    price money NOT NULL,
    weight decimal(5, 2) NOT NULL,
    shippingweight decimal(5, 2) NOT NULL,
    height decimal(4, 2) NOT NULL,
    width decimal(4, 2) NOT NULL,
    depth decimal(4, 2) NOT NULL,
    isserialized bit NOT NULL,
    status tinyint NOT NULL
)

********************************************************* CREATE TABLE Products(
    sku int NOT NULL,
    modelnumber varchar(25) NOT NULL,
    name varchar(100) NOT NULL,
    manufacturer varchar(25) NOT NULL,
    description varchar(255) NOT NULL,
    price money NOT NULL,
    weight decimal(5, 2) NOT NULL,
    shippingweight decimal(5, 2) NOT NULL,
    height decimal(4, 2) NOT NULL,
    width decimal(4, 2) NOT NULL,
    depth decimal(4, 2) NOT NULL,
    isserialized bit NOT NULL,
    status tinyint NOT NULL,
    CONSTRAINT pk_product_sku PRIMARY KEY (sku)
)

********************************************************** ALTER TABLE Products
ADD CONSTRAINT pk_product_sku PRIMARY KEY (sku)

********************************************************* CREATE TABLE dbo.vehicle(
    objid int IDENTITY(1,1) NOT NULL,
    make varchar(50) NOT NULL,
    model varchar(50)NOT NULL,
    year char(4) NOT NULL,
    employee_objid int NOT NULL,
CONSTRAINT PK_vehicle PRIMARY KEY (objid),
CONSTRAINT FK_vehicle_employee
    FOREIGN KEY(employee_objid)
    REFERENCES employee (objid) )

********************************************************* ALTER TABLE dbo.Products
ADD CONSTRAINT chk_non_negative_values
    CHECK ( weight >= 0 AND (shippingweight >= 0
    AND shippingweight >= weight)
    AND height >= 0 AND width >= 0 AND depth >= 0 )

********************************************************* DECLARE @num_in_stock int

EXEC sp_check_product_stock @sku = 4587353,
    @stock_level = @num_in_stock OUTPUT

PRINT @num_in_stock

********************************************************* DECLARE @num_in_stock int

SET @num_in_stock = dbo.CheckProductStock (4587353)

PRINT @num_in_stock

********************************************************* INSERT INTO employee (employeeid, firstname, lastname) VALUES (dbo.GetNewEmployeeID(), 'Eric', 'Johnson')




Chapter 8

********************************************************** CREATE TABLE SortTest(
    StringDate varchar(50),
    RealDate datetime
)

********************************************************* INSERT INTO SortTest VALUES('1/1/2007' , '1/1/2007') INSERT INTO SortTest VALUES('1/2/2007' , '1/2/2007') INSERT INTO SortTest VALUES('1/3/2007' , '1/3/2007') INSERT INTO SortTest VALUES('1/4/2007' , '1/4/2007')
…
INSERT INTO SortTest VALUES('1/31/2007' , '1/31/2007')




Chapter 9

********************************************************* SELECT where, and, name, date
FROM INSERT
WHERE and = 1
    AND where = 'Omaha'

********************************************************* SELECT [where], [and], name, date
FROM [INSERT]
WHERE [and] = 1
    AND [where] = 'Omaha'

********************************************************* ALTER TABLE dbo.tbl_employee
ADD CONSTRAINT DF_status DEFAULT 1 FOR status

********************************************************* ALTER TABLE dbo.tbl_order
ADD CONSTRAINT [DF_ordernumber] DEFAULT
    dbo.udf_new_orderid() FOR ordernumber

********************************************************* (
[homephone] IS NOT NULL
OR [workphone] IS NOT NULL
OR [mobilephone] IS NOT NULL
)

********************************************************* ALTER TABLE dbo.tbl_customer
WITH CHECK ADD CONSTRAINT CK_phone_number CHECK
    (([homephone] IS NOT NULL
    OR [workphone] IS NOT NULL
    OR [mobilephone] IS NOT NULL))

********************************************************* ALTER TABLE dbo.tbl_order
ADD CONSTRAINT UNQ_ordernumber
    UNIQUE NONCLUSTERED (ordernumber)

********************************************************* CREATE TRIGGER trg_payment_data
ON dbo.tbl_payment FOR INSERT, UPDATE
AS
DECLARE @payment_type tinyint
,@cc_type varchar(16)
,@cc_number bigint
,@cc_expire date
,@ccv_code smallint
,@gc_number bigint
,@account_number bigint
,@routing_number int
,@license_number varchar(20)
,@license_state char(10)

SELECT @payment_type = payment_type
    ,@cc_type = cc_type
    ,@cc_number = cc_number
    ,@cc_expire = cc_expire
    ,@ccv_code = ccv_code
    ,@gc_number = gc_number
    ,@account_number = account_number
    ,@routing_number = routing_number
    ,@license_number = license_number
    ,@license_state = license_state
FROM inserted

IF @payment_type= 1 --Credit Card
BEGIN&        RAISERROR ('Payment Error: Credit Card Data is Missing.',11,1)
        ROLLBACK
    END
END

IF @payment_type = 2 --Gift Card
BEGIN
    IF @gc_number IS NULL
    BEGIN
        RAISERROR ('Payment Error: Gift Card Data is Missing.',11,1)
        ROLLBACK
    END
END

IF @payment_type = 3 --Bank Draft
BEGIN
    IF @account_number IS NULL
        OR @routing_number IS NULL
        OR @license_number IS NULL
        OR @license_state IS NULL
    BEGIN
        RAISERROR ('Payment Error: Bank Account Data is Missing.',11,1)
        ROLLBACK
    END
END

********************************************************* ALTER TRIGGER trg_one_dean_per_college
ON tbl_college FOR INSERT, UPDATE
AS
DECLARE @college_count int

SELECT @college_count = COUNT(tbl_college.id)
FROM tbl_college
JOIN tbl_faculty
    ON tbl_college.dean_id = tbl_faculty.id
WHERE tbl_faculty.id = (SELECT dean_id FROM INSERTED)

IF @college_count > 1
BEGIN
    RAISERROR('This faculty member is already dean of another college',11,1)
    ROLLBACK
END




Chapter 10

********************************************************* CREATE NONCLUSTERED INDEX idx_Customer_LastName_FirstName
ON Customer (LastName ASC, FirstName ASC)
WITH (FILlFACTOR = 70, SORT_IN_TEMPDB = ON, ONLINE = ON) ON IndexFileGroup




Chapter 11

********************************************************* CREATE VIEW vw_customer_detail
AS
SELECT email ,customer_id ,firstname ,lastname
    ,homephone ,workphone ,mobilephone
FROM tbl_customer

********************************************************* CREATE VIEW vw_customer_addresses
AS
SELECT address_objid = objid ,address_label
    ,addressline1 ,addressline2 ,city ,region ,zipcode
    ,customer_objid
FROM tbl_address
WHERE customer_objid IS NOT NULL

********************************************************* ALTER PROCEDURE prc_save_customer
    @email varchar(50)
    ,@customer_id char(10)
    ,@firstname varchar(50)     ,@lastname varchar(50)
    ,@homephone varchar(15)
    ,@workphone varchar(15)
    ,@mobilephone varchar(15)
    ,@addresses CustomerAddress ReadOnly 
    ,@customer_objid int OUTPUT
AS

MERGE tbl_customer AS pri_customer
    USING
        (
        SELECT customer_id = @customer_id
        ) AS source_customer(customer_id)
    ON
        (
        pri_customer.customer_id =        
        source_customer.customer_id
        )
    WHEN NOT MATCHED THEN
        INSERT (email, customer_id, firstname, lastname,
        homephone, workphone, mobilephone)
        VALUES (@email,@customer_id,@firstname,@lastname,
        @homephone,@workphone,@mobilephone)
    WHEN MATCHED THEN
        UPDATE
        SET email=@email,
            firstname=@firstname,
            lastname=@lastname,
            homephone=@homephone,
            workphone=@workphone,        
            mobilephone=@mobilephone;

SELECT @customer_objid = objid
FROM tbl_customer
WHERE cu MERGE tbl_address AS current_addresses
    USING
         SELECT customer_objid = @customer_objid,        
             address_label, addressline1, addressline2,
             city, region, country, zipcode, is_deleted
         FROM @addresses
        ) AS source_addresss(customer_objid,    
            address_label, addressline1,addressline2,
            city, region, country, zipcode, is_deleted)
    ON
        (current_addresses.address_label =
        source_addresses.address_label
        AND current_addresses.customer_objid =
        source_addresses.customer_objid )

    WHEN NOT MATCHED THEN
        INSERT(address_label, addressline1, addressline2,
            city, region, country, zipcode,
            customer_objid)
        VALUES(source_addresses.address_label,
            source_addresses.addressline1,
            source_addresses.addressline2,
            source_addresses.city,
            source_addresses.region,
            source_addresses.country,
            source_addresses.zipcode,
            @customer_objid)
    WHEN MATCHED AND source_addresses.is_deleted = 1
        THEN DELETE
    WHEN MATCHED THEN
    UPDATE
    SET address_label=source_addresses.address_label,
        addressline1=source_addresses.addressline1,
        addressline2=source_addresses.addressline2,
        city=source_addresses.city,
        region=source_addresses.region,
        country=source_addresses.country,
        zipcode=source_addresses.zipcode;