/****************************************************** * * Name: designing-with-ssms.sql * * Design Phase: * Author: John Miner * Date: 01-29-2013 * Blog: www.craftydba.com * * Purpose: Create a series of articles on * database developer (70-433) topics. * * Topic: Talk about using SSMS for design. * ******************************************************/ -- -- 1A - Create the sample database -- -- Which database to use. USE [master] GO -- Delete existing databases. IF EXISTS (SELECT name FROM sys.databases WHERE name = N'AUTOS') DROP DATABASE [AUTOS] GO -- Add new databases. CREATE DATABASE [AUTOS] ON PRIMARY ( NAME = N'AUTOS_DAT', FILENAME = N'C:\MSSQL\DATA\AUTOS-DAT.MDF' , SIZE = 16MB , MAXSIZE = UNLIMITED, FILEGROWTH = 4MB) LOG ON ( NAME = N'AUTOS_LOG', FILENAME = N'C:\MSSQL\LOG\AUTOS-LOG.LDF' , SIZE = 4MB , MAXSIZE = UNLIMITED , FILEGROWTH = 1MB) GO -- -- 1B - Modify sample database 2 add new file groups -- ALTER DATABASE [AUTOS] ADD FILEGROUP [FG_AUTOS_ACTIVE] GO ALTER DATABASE [AUTOS] ADD FILEGROUP [FG_AUTOS_STAGE] GO -- -- 1C - Modify sample database 2 add new files -- -- Active tables ALTER DATABASE [AUTOS] ADD FILE ( NAME= 'FN_AUTOS_ACTIVE', FILENAME = 'C:\MSSQL\DATA\AUTOS-ACT.NDF', SIZE = 16MB , MAXSIZE = UNLIMITED , FILEGROWTH = 4MB ) TO FILEGROUP [FG_AUTOS_ACTIVE] GO -- Stage tables ALTER DATABASE [AUTOS] ADD FILE ( NAME= 'FN_AUTOS_STAGE', FILENAME = 'C:\MSSQL\DATA\AUTOS-STG.NDF', SIZE = 16MB , MAXSIZE = UNLIMITED , FILEGROWTH = 4MB ) TO FILEGROUP [FG_AUTOS_STAGE] GO -- -- 1D - Change ownership -- -- Switch owner to system admin ALTER AUTHORIZATION ON DATABASE::AUTOS TO SA; GO -- -- 1E - Modify sample database 2 add new schemas -- -- Use the database USE [AUTOS]; GO -- Add new schema CREATE SCHEMA [ACTIVE] AUTHORIZATION [dbo] GO -- Add new schema CREATE SCHEMA [STAGE] AUTHORIZATION [dbo] GO -- -- 1F - Modify server 2 add new logins -- -- Use the database USE [AUTOS]; GO -- Delete existing login. IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'AUTOS_USER') DROP LOGIN [AUTOS_USER] GO -- Add new login. CREATE LOGIN [AUTOS_USER] WITH PASSWORD=N'M0a2r0c4h13#', DEFAULT_DATABASE=[AUTOS] GO -- Delete existing login. IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'AUTOS_ADMIN') DROP LOGIN [AUTOS_ADMIN] GO -- Add new login. CREATE LOGIN [AUTOS_ADMIN] WITH PASSWORD=N'M0a2r0c4h13#', DEFAULT_DATABASE=[AUTOS] GO -- -- 1G - Modify database 2 add new users -- -- Use the database USE [AUTOS]; GO -- Delete existing user IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'AUTOS_USER') DROP USER [AUTOS_USER] GO -- Add new user. CREATE USER [AUTOS_USER] FOR LOGIN [AUTOS_USER] WITH DEFAULT_SCHEMA=[ACTIVE] GO -- Delete existing user IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'AUTOS_ADMIN') DROP USER [AUTOS_ADMIN] GO -- Add new user. CREATE USER [AUTOS_ADMIN] FOR LOGIN [AUTOS_ADMIN] WITH DEFAULT_SCHEMA=[STAGE] GO -- -- 1H - Modify database 2 add users 2 roles -- -- Use the database USE [AUTOS]; GO -- Add to database read role 2 user EXEC sp_addrolemember 'db_datareader', 'AUTOS_USER' -- Add to database read / write roles 2 admin EXEC sp_addrolemember 'db_datareader', 'AUTOS_ADMIN' EXEC sp_addrolemember 'db_datawriter', 'AUTOS_ADMIN' GO -- -- 1I - Create the sample tables with good contraints -- -- Delete existing table IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ACTIVE].[MAKES]') AND type in (N'U')) DROP TABLE [ACTIVE].[MAKES] GO -- Create makes table, add constraints with alter CREATE TABLE [ACTIVE].[MAKES] ( MAKER_ID INT IDENTITY(1,1), MAKER_NM VARCHAR(25), START_YR SMALLINT, END_YR SMALLINT ) ON [FG_AUTOS_ACTIVE]; GO -- User defined constraint ALTER TABLE [ACTIVE].[MAKES] ADD CONSTRAINT CHK_START_YR CHECK (ISNULL(START_YR, 0) >= 1903 and ISNULL(START_YR, 0) <= YEAR(GETDATE()) + 1); GO -- User defined constraint ALTER TABLE [ACTIVE].[MAKES] ADD CONSTRAINT CHK_END_YR CHECK ( (END_YR IS NULL) OR (END_YR >= 1903 AND END_YR <= YEAR(GETDATE()) + 1) ); GO -- Entity constraint - natural key ALTER TABLE [ACTIVE].[MAKES] ADD CONSTRAINT UNQ_MAKER_NM UNIQUE (MAKER_NM); GO -- Entity constraint - surrogate key ALTER TABLE [ACTIVE].[MAKES] ADD CONSTRAINT PK_MAKER_ID PRIMARY KEY CLUSTERED (MAKER_ID); GO -- Delete existing table IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ACTIVE].[MODELS]') AND type in (N'U')) DROP TABLE [ACTIVE].[MODELS] GO -- Create models table with inline constraints CREATE TABLE [ACTIVE].[MODELS] ( MODEL_ID INT IDENTITY(1,1) CONSTRAINT PK_MODEL_ID PRIMARY KEY CLUSTERED, MODEL_NM VARCHAR(25) NOT NULL CONSTRAINT DF_MODEL_NM DEFAULT 'UNKNOWN' CONSTRAINT UNQ_MODEL_NM UNIQUE (MODEL_NM), MODEL_YR SMALLINT CONSTRAINT CHK_MDOEL_YR CHECK (ISNULL(MODEL_YR, 0) >= 1903 and ISNULL(MODEL_YR,0) <= YEAR(GETDATE()) + 1), MSRP SMALLMONEY NOT NULL CONSTRAINT DF_MSRP DEFAULT 0, MAKER_ID INT NOT NULL CONSTRAINT FK_MAKES FOREIGN KEY (MAKER_ID) REFERENCES [ACTIVE].[MAKES] (MAKER_ID) ) ON [FG_AUTOS_ACTIVE]; GO -- -- 1J - Add good data to the tables -- -- Add data to makes INSERT INTO [ACTIVE].[MAKES] (MAKER_NM, START_YR, END_YR) VALUES ('Chevrolet', 1912, Null), ('Dodge', 1915, Null), ('Ford', 1903, Null), ('Lincoln', 1917, Null), ('Mercury', 1938, 2011); GO -- Review data SELECT * FROM [ACTIVE].[MAKES] GO -- Add data INSERT INTO [ACTIVE].[MODELS] (MODEL_NM, MODEL_YR, MSRP, MAKER_ID) VALUES ('Corvette', 2013, 49600.00, 1), ('Ram 1500', 2013, 22589.99, 2), ('Mustang Boss 302', 2013, 42200.00, 3), ('Navigator', 2013, 57775.00, 4), ('Mariner', 2011, 23082.00, 5) GO -- Review data SELECT * FROM [ACTIVE].[MODELS] GO -- -- 1K - Updated view to have 1 x N join -- CREATE VIEW [ACTIVE].[VW_JOIN_MAKES_2_MODELS] AS SELECT MA.[MAKER_ID] , MA.[MAKER_NM] , MA.[START_YR] , MA.[END_YR] , MO.[MODEL_ID] , MO.[MODEL_NM] , MO.[MODEL_YR] , MO.[MSRP] FROM [ACTIVE].[MAKES] MA LEFT JOIN [ACTIVE].[MODELS] MO ON MA.MAKER_ID = MO.MAKER_ID GO -- Review view data SELECT * FROM [ACTIVE].[VW_JOIN_MAKES_2_MODELS] GO -- -- 1L - Create the staging models table without constraints -- -- Delete existing table IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[STAGE].[MODELS]') AND type in (N'U')) DROP TABLE [STAGE].[MODELS] GO CREATE TABLE [STAGE].[MODELS] ( MODEL_NM VARCHAR(25) NOT NULL CONSTRAINT UNQ_MODEL_NM_STG UNIQUE (MODEL_NM), MODEL_YR SMALLINT, MSRP SMALLMONEY, MAKER_ID INT ) ON [FG_AUTOS_STAGE]; GO -- Insert new sample data INSERT INTO [STAGE].[MODELS] (MODEL_NM, MODEL_YR, MSRP, MAKER_ID) VALUES ('F-150 XL ', 2013, 23670.00, 3), ('F-150 STX', 2013, 25935.00, 3), ('F-150 XLT', 2013, 29050.00, 3), ('F-150 FX2', 2013, 35625.00, 3); GO -- Show the new data SELECT * FROM [STAGE].[MODELS] GO -- -- 1M - Create a sample procedure -- CREATE PROCEDURE [ACTIVE].[USP_MOVE_MODEL_STG2PRD] AS -- Nothing to do IF NOT EXISTS (SELECT TOP 1 * FROM [STAGE].[MODELS]) RETURN 0; -- Copy data from stage to active INSERT INTO [ACTIVE].[MODELS] (MODEL_NM, MODEL_YR, MSRP, MAKER_ID) SELECT STG.MODEL_NM, STG.MODEL_YR, STG.MSRP, STG.MAKER_ID FROM [STAGE].[MODELS] AS STG; -- Failure (error) was detected IF @@ERROR <> 0 BEGIN PRINT 'The stored procedure that moves model data from stage to active schemas failed with an error.'; RETURN 99; END; -- Remove data from stage DELETE FROM [STAGE].[MODELS]; -- Failure (error) was detected IF @@ERROR <> 0 BEGIN PRINT 'The stored procedure that moves model data from stage to active schemas failed with an error.'; RETURN 99; END; -- Success (no error) was detected PRINT 'The stored procedure that moves model data from stage to active schemas completed successfully.'; RETURN 0; GO -- Call the new stored procedure DECLARE @VAR_RET INT; EXEC @VAR_RET = [ACTIVE].[USP_MOVE_MODEL_STG2PRD]; PRINT @VAR_RET; -- -- 1N - Create a sample function -- -- Create stub scalar valued function CREATE FUNCTION [ACTIVE].[UFN_GET_MODEL_CNT] (@VAR_MAKER varchar (25)) RETURNS [int] AS BEGIN -- Declare local variable DECLARE @VAR_RET INT; -- Find the number of models for a given make SELECT @VAR_RET = COUNT(*) FROM [ACTIVE].[VW_JOIN_MAKES_2_MODELS] AS M WHERE M.MAKER_NM = @VAR_MAKER; -- Return the result IF (@VAR_RET IS NULL) SET @VAR_RET = 0; RETURN @VAR_RET; END GO -- How many ford models SELECT [ACTIVE].[UFN_GET_MODEL_CNT]('Ford') AS FORD_MODEL_CNT; GO -- -- 1O - Create a sample trigger -- CREATE TRIGGER [ACTIVE].[UTR_PREVENT_DEL_FROM_MAKES] ON [ACTIVE].[MAKES] INSTEAD OF DELETE AS BEGIN PRINT 'The business team does not want any make records removed from the database'; END; -- Test the trigger DELETE TOP (1) FROM [ACTIVE].[MAKES]