Creating Database with filegroups and performing different Operations - (Class IV Code)

-- Created Database with filegroups and set different attibutes of the database

CREATE DATABASE [AW_Marketing] ON PRIMARY
( NAME = N'AW_Marketing_Data1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\AW_Marketing_Data1.mdf' , SIZE = 10240KB , FILEGROWTH = 0),
FILEGROUP [ArchivedData]
( NAME = N'AW_Marketing_Data3', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\AW_Marketing_Data3.ndf' , SIZE = 25600KB , FILEGROWTH = 0),
FILEGROUP [CurrentData]
( NAME = N'AW_Marketing_Data2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\AW_Marketing_Data2.ndf' , SIZE = 10240KB , FILEGROWTH = 0)
LOG ON
( NAME = N'AW_Marketing_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\AW_Marketing_log.ldf' , SIZE = 10240KB , FILEGROWTH = 0)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'AW_Marketing', @new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [AW_Marketing].[dbo].[sp_fulltext_database] @action = 'disable'
end
GO
ALTER DATABASE [AW_Marketing] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [AW_Marketing] SET ANSI_NULLS OFF
GO
ALTER DATABASE [AW_Marketing] SET ANSI_PADDING OFF
GO
ALTER DATABASE [AW_Marketing] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [AW_Marketing] SET ARITHABORT OFF
GO
ALTER DATABASE [AW_Marketing] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [AW_Marketing] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [AW_Marketing] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [AW_Marketing] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [AW_Marketing] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [AW_Marketing] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [AW_Marketing] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [AW_Marketing] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [AW_Marketing] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [AW_Marketing] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [AW_Marketing] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [AW_Marketing] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [AW_Marketing] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [AW_Marketing] SET READ_WRITE
GO
ALTER DATABASE [AW_Marketing] SET RECOVERY FULL
GO
ALTER DATABASE [AW_Marketing] SET MULTI_USER
GO
ALTER DATABASE [AW_Marketing] SET PAGE_VERIFY CHECKSUM
GO
USE [AW_Marketing]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [AW_Marketing] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO

-- Create Schemas and tables for the above created database and file groups

USE AW_Marketing
GO

CREATE SCHEMA Promotions
CREATE TABLE ProductDiscounts
(ProductID int PRIMARY KEY,
Discount decimal,
Description nvarchar(50))
ON CurrentData
GO

CREATE SCHEMA Sponsorship
CREATE TABLE SponsorshipDeals
(SponsorshipID int IDENTITY PRIMARY KEY,
Description nvarchar(200))
ON CurrentData
GO

CREATE SCHEMA PastPromotions
CREATE TABLE ProductDiscounts
(ProductID int,
Discount decimal,
Description nvarchar(50))
ON ArchivedData
GO

CREATE SCHEMA PastSponsorship
CREATE TABLE SponsorshipDeals
(SponsorshipID int,
Description nvarchar(200))
ON ArchivedData
GO

-- Perform different operations on earlier created tables

USE AW_Marketing
GO

-- Remove tables if they already exist
IF EXISTS (SELECT * FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.[Name] = 'SpecialOffers'
AND s.[Name] = 'Promotions')
DROP TABLE Promotions.SpecialOffers

IF EXISTS (SELECT * FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.[Name] = 'SpecialOffers'
AND s.[Name] = 'PastPromotions')
DROP TABLE PastPromotions.SpecialOffers
GO


-- Create table for special offers. No filegroup specified
CREATE TABLE Promotions.SpecialOffers
(OfferID int IDENTITY PRIMARY KEY,
Description nvarchar(200),
StartDate datetime,
EndDate datetime,
DiscountPercent decimal)

-- Create table for archived offers on the ArchivedData filegroup
CREATE TABLE PastPromotions.SpecialOffers
(OfferID int IDENTITY PRIMARY KEY,
Description nvarchar(200),
StartDate datetime,
EndDate datetime,
DiscountPercent decimal)
ON ArchivedData