Implement Programming Objects: Create and alter views

(This is part of the Study Guide series, 70-464)
Microsoft’s Measured Skill description: This objective may include but is not limited to: set up and configure partitioned tables and partitioned views; design for using views and stored procedures, and remove the direct usage of tables
What I see:
·         partitioned tables
·         partitioned views
·         create views
Partitioned Tables
                There are times when you may want to partition a table.  This is not typically implemented with performance in mind (although the optimizer can eliminate partitions), but more for management and maintainability for large tables.  This allows you to window your data and keep cold, warm, and hot data on different filegroups, and therefore different disks (i.e. inexpensive disks for cold partitions that are rarely queried, or primarily reads).  Below is an example of how to partition a table in the AdventureWorks2012 database.  I create a copy of an existing table.  The last query shows row count per partition, to display data distribution:
use AdventureWorks2012;
go
select *
into Production.ProductPartitioned
from Production.Product;
alter database AdventureWorks2012
add filegroup FG1;
go
alter database AdventureWorks2012
add filegroup FG2;
go
alter database AdventureWorks2012
add filegroup FG3;
go
alter database AdventureWorks2012
add filegroup FG4;
go
alter database AdventureWorks2012
add file
(
       name = FG1_file,
       filename = ‘C:\SqlServer\Testing\AdventureWorks\AW_fg1.ndf’,
       size = 5mb,
       maxsize = 10mb,
       filegrowth = 1mb
)
to filegroup FG1;
go
alter database AdventureWorks2012
add file
(
       name = FG2_file,
       filename = ‘C:\SqlServer\Testing\AdventureWorks\AW_fg2.ndf’,
       size = 5mb,
       maxsize = 10mb,
       filegrowth = 1mb
)
to filegroup FG2;
go
alter database AdventureWorks2012
add file
(
       name = FG3_file,
       filename = ‘C:\SqlServer\Testing\AdventureWorks\AW_fg3.ndf’,
       size = 5mb,
       maxsize = 10mb,
       filegrowth = 1mb
)
to filegroup FG3;
go
alter database AdventureWorks2012
add file
(
       name = FG4_file,
       filename = ‘C:\SqlServer\Testing\AdventureWorks\AW_fg4.ndf’,
       size = 5mb,
       maxsize = 10mb,
       filegrowth = 1mb
)
to filegroup FG4;
go
create partition function ProductPartitionFunc(datetime)
as range right
for values
(
       ‘2005-01-01’,
       ‘2006-01-01’,
       ‘2007-01-01’
);
go
create partition scheme ProductPartitionScheme
as partition ProductPartitionFunc
to
(
       FG1,
       FG2,
       FG3,
       FG4
);
go
create clustered index Production_ProductPartitioned
on Production.ProductPartitioned
(
       ProductID
) on ProductPartitionScheme(SellStartDate);
go
select *
from sys.partitions;
select
       object_name(object_id),
       partition_id,
       row_count
from sys.dm_db_partition_stats
where object_id = object_id(‘Production.ProductPartitioned’);
Partitioned Views
                Partitioned views are another way to distribute data across multiple partitions in order to manage large quantities of data.  Basically, a partitioned view is just a normal view that runs a SELECT on related tables and UNIONs them all.  There are a few “gotchas”, though:  the underlying tables will have a CHECK constraint to tell SQL Server where to distribute data.  This column will be a part of the primary key.  Below is an implementation example, and at the bottom you can see which tables the data was placed into when we ran an INSERT INTOon the partitioned view:
use MeasureMySkills;
go
create table Data2001p
(
       id int not null,
       SomeData nvarchar(128) not null,
       ImportYear int not null
              check (ImportYear = 2001),
       constraint PK_Data2001p
              primary key (id, ImportYear)
);
go
create table Data2002p
(
       id int not null,
       SomeData nvarchar(128) not null,
       ImportYear int not null
              check (ImportYear = 2002),
       constraint PK_Data2002p
              primary key (id, ImportYear)
);
go
create table Data2003p
(
       id int not null,
       SomeData nvarchar(128) not null,
       ImportYear int not null
              check (ImportYear = 2003),
       constraint PK_Data2003p
              primary key (id, ImportYear)
);
go
create view DataYearPartition
as
       select *
       from Data2001p
       union all
       select *
       from Data2002p
       union all
       select *
       from Data2003p;
go
insert into DataYearPartition
(
       id,
       SomeData,
       ImportYear
)
values
(
       1,
       ‘Data2002’,
       2002
),
(
       2,
       ‘Data2001’,
       2001
),
(
       3,
       ‘Data2003’,
       2003
),
(
       4,
       ‘Data2002’,
       2002
),
(
       5,
       ‘Data2003’,
       2003
);
select *
from Data2001p;
select *
from Data2002p;
select *
from Data2003p;
Create Views
                The CREATE VIEW DDL statement is one of the more basic ones, so I won’t go into detail.  I recommend referencing BOLin order to get a refresher and read up on the many options that can be used.  The exam objective implicitly states that views should be an abstraction from the underlying database tables.  There are a lot of opinions and personal preferences on this type of implementation, but just know that it is a way.  Views can also be used for security measures for the underlying tables.
References
·         BOL reference on CREATE VIEW
If there are any comments, questions, issues, or suggestions please feel free to leave a comment below or email me at sqlsalt@gmail.com.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s