Partitioned Views in SQL Server 2008

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Partitioned Views in SQL Server 2008

Author

Topic

Idyana
Yak Posting Veteran

96 Posts

Posted - 2012-01-08 : 10:39:55

I'm currently using SQL Server Standard Edition 32bit

I was implement Partitioned Views as following,


USE [myPARTITION]
GO
/****** Object:  Table [dbo].[paymentH_201204]    Script Date: 01/08/2012 23:41:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[paymentH_201204](
	[idx] [int] NOT NULL,
	[amt] [decimal](10, 2) NOT NULL,
	[crtDte] [date] NOT NULL,
 CONSTRAINT [PK_paymentH_201204] PRIMARY KEY CLUSTERED 
(
	[idx] ASC,
	[crtDte] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[paymentH_201203]    Script Date: 01/08/2012 23:41:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[paymentH_201203](
	[idx] [int] NOT NULL,
	[amt] [decimal](10, 2) NOT NULL,
	[crtDte] [date] NOT NULL,
 CONSTRAINT [PK_paymentH_201203] PRIMARY KEY CLUSTERED 
(
	[idx] ASC,
	[crtDte] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[paymentH_201202]    Script Date: 01/08/2012 23:41:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[paymentH_201202](
	[idx] [int] NOT NULL,
	[amt] [decimal](10, 2) NOT NULL,
	[crtDte] [date] NOT NULL,
 CONSTRAINT [PK_paymentH_201202] PRIMARY KEY CLUSTERED 
(
	[idx] ASC,
	[crtDte] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[paymentH_201201]    Script Date: 01/08/2012 23:41:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[paymentH_201201](
	[idx] [int] NOT NULL,
	[amt] [decimal](10, 2) NOT NULL,
	[crtDte] [date] NOT NULL,
 CONSTRAINT [PK_paymentH_201201] PRIMARY KEY CLUSTERED 
(
	[idx] ASC,
	[crtDte] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[paymentH_201112]    Script Date: 01/08/2012 23:41:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[paymentH_201112](
	[idx] [int] NOT NULL,
	[amt] [decimal](10, 2) NOT NULL,
	[crtDte] [date] NOT NULL,
 CONSTRAINT [PK_paymentH_201112] PRIMARY KEY CLUSTERED 
(
	[idx] ASC,
	[crtDte] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  View [dbo].[paymentHPartitioned]    Script Date: 01/08/2012 23:41:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create view [dbo].[paymentHPartitioned] with schemabinding
as
select idx, amt, crtDte from dbo.paymentH_201112
union all
select idx, amt, crtDte from dbo.paymentH_201201
union all
select idx, amt, crtDte from dbo.paymentH_201202
union all
select idx, amt, crtDte from dbo.paymentH_201203
union all
select idx, amt, crtDte from dbo.paymentH_201204
GO
/****** Object:  Check [CK_paymentH_201112_1]    Script Date: 01/08/2012 23:41:11 ******/
ALTER TABLE [dbo].[paymentH_201112]  WITH CHECK ADD  CONSTRAINT [CK_paymentH_201112_1] CHECK  ((datepart(year,[crtDte])=(2011) AND datepart(month,[crtDte])=(12)))
GO
ALTER TABLE [dbo].[paymentH_201112] CHECK CONSTRAINT [CK_paymentH_201112_1]
GO
/****** Object:  Check [CK_paymentH_201201_1]    Script Date: 01/08/2012 23:41:11 ******/
ALTER TABLE [dbo].[paymentH_201201]  WITH CHECK ADD  CONSTRAINT [CK_paymentH_201201_1] CHECK  ((datepart(year,[crtDte])=(2012) AND datepart(month,[crtDte])=(1)))
GO
ALTER TABLE [dbo].[paymentH_201201] CHECK CONSTRAINT [CK_paymentH_201201_1]
GO
/****** Object:  Check [CK_paymentH_201202_1]    Script Date: 01/08/2012 23:41:11 ******/
ALTER TABLE [dbo].[paymentH_201202]  WITH CHECK ADD  CONSTRAINT [CK_paymentH_201202_1] CHECK  ((datepart(year,[crtDte])=(2012) AND datepart(month,[crtDte])=(2)))
GO
ALTER TABLE [dbo].[paymentH_201202] CHECK CONSTRAINT [CK_paymentH_201202_1]
GO
/****** Object:  Check [CK_paymentH_201203_1]    Script Date: 01/08/2012 23:41:11 ******/
ALTER TABLE [dbo].[paymentH_201203]  WITH CHECK ADD  CONSTRAINT [CK_paymentH_201203_1] CHECK  ((datepart(year,[crtDte])=(2012) AND datepart(month,[crtDte])=(3)))
GO
ALTER TABLE [dbo].[paymentH_201203] CHECK CONSTRAINT [CK_paymentH_201203_1]
GO
/****** Object:  Check [CK_paymentH_201204_1]    Script Date: 01/08/2012 23:41:11 ******/
ALTER TABLE [dbo].[paymentH_201204]  WITH CHECK ADD  CONSTRAINT [CK_paymentH_201204_1] CHECK  ((datepart(year,[crtDte])=(2012) AND datepart(month,[crtDte])=(4)))
GO
ALTER TABLE [dbo].[paymentH_201204] CHECK CONSTRAINT [CK_paymentH_201204_1]
GO

At this level, my Partitioned Views is ready.

I was run T-SQL as following,


insert into dbo.paymentHPartitioned(idx,amt,crtDte) values(3,3000.20,'20120301')

The error raise as following,
UNION ALL view 'myPARTITION.dbo.paymentHPartitioned' is not updatable because a partitioning column was not found.

Really need help. I'm stuck

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-08 : 10:51:02

any special reason why you're creating a seperate table for each period and then using a view to merge them? why not put all the data in same table and implement partitioning in table to span data partitions across multiple file groups?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Idyana
Yak Posting Veteran

96 Posts

Posted - 2012-01-08 : 10:54:07

Sir,

I'm using Standard Edition not Enterprise Edition.

As I know, Partition function can only be created in Enterprise edition of SQL Server

Sachin.Nand

2937 Posts

Posted - 2012-01-08 : 15:17:41

Create an instead of trigger on the view and then based on the value of crtDte in the insert statement insert the data in the appropriate table.

After Monday and Tuesday even the calendar says W T F ....

Idyana
Yak Posting Veteran

96 Posts

Posted - 2012-01-08 : 16:25:45

Hi Mr Saching.Nand,

I have no idea to Create an instead of trigger on the view and then based on the value of crtDte in the insert statement insert the data in the appropriate table.

Can you show me the SQL?

I'm stuck

Idyana
Yak Posting Veteran

96 Posts

Posted - 2012-01-09 : 03:28:41

Help me.......

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-09 : 04:32:29

quote:
Originally posted by Idyana

Help me.......

something like


CREATE TRIGGER MyTrigger
ON paymentHPartitioned
INSTEAD OF INSERT 
AS
BEGIN
INSERT INTO [dbo].[paymentH_201112]
SELECT *
FROM INSERTED
WHERE crtDte>='20111201'
AND crtDte< '20120101'


INSERT INTO [dbo].[paymentH_201201]
SELECT *
FROM INSERTED
WHERE crtDte>='20120101'
AND crtDte< '20120201'

INSERT INTO [dbo].[paymentH_201202]
SELECT *
FROM INSERTED
WHERE crtDte>='20120201'
AND crtDte< '20120301'

INSERT INTO [dbo].[paymentH_201203]
SELECT *
FROM INSERTED
WHERE crtDte>='20120301'
AND crtDte< '20120401'

INSERT INTO [dbo].[paymentH_201204]
SELECT *
FROM INSERTED
WHERE crtDte>='20120401'
AND crtDte< '20120501'
END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Idyana
Yak Posting Veteran

96 Posts

Posted - 2012-01-09 : 07:27:21

tq sir

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-09 : 07:34:29

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources