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 INTO on 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 BOL in 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 PARTITION FUNCTION

·         BOL reference on CREATE PARTITION SCHEME

·         BOL reference on ALTER DATABASE File and Filegroup Options

·         BOL reference on Using Partitioned Views

·         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.