I've got what I thought was a partitioned view, but perhaps I haven't. The
creation scripts are at the end.

When I try to insert into the view, I get a message to the effect that the
insert failed because TableA has an IDENTITY constraint. I have read BOL and
whilst it mentions identity columns and a range of hoops that have to be
jumped through, I can't see anything that says what I have below won't work.
Perhaps I'm reading it incorrectly, but could someone point me to the
relevant text? Perhaps someone could also suggest how I should change what I
have so that it works, whilst remaining in the spirit of what is already
there.

TIA

Charles


-- Creation Scripts
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TableA](
[PartCol] [int] NOT NULL,
[TableAId] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](max) NOT NULL,
[Description] [varchar](max) NULL,
[CreatedDate] [datetime] NOT NULL,
CONSTRAINT [PK_TableA_1] PRIMARY KEY CLUSTERED
(
[PartCol] ASC,
[TableAId] 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
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[TableA] WITH CHECK ADD CONSTRAINT [CK_TableA_PartCol]
CHECK (([PartCol]=(1)))
GO
ALTER TABLE [dbo].[TableA] CHECK CONSTRAINT [CK_TableA_PartCol]
GO
ALTER TABLE [dbo].[TableA] ADD CONSTRAINT [DF_TableA_Round] DEFAULT ((1))
FOR [PartCol]
GO
ALTER TABLE [dbo].[TableA] ADD CONSTRAINT [DF_TableA_CreatedDate] DEFAULT
(getutcdate()) FOR [CreatedDate]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TableB](
[PartCol] [int] NOT NULL,
[TableBId] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](max) NOT NULL,
[Description] [varchar](max) NULL,
[CreatedDate] [datetime] NOT NULL,
CONSTRAINT [PK_TableB_1] PRIMARY KEY CLUSTERED
(
[PartCol] ASC,
[TableBId] 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
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[TableB] WITH CHECK ADD CONSTRAINT [CK_TableB_PartCol]
CHECK (([PartCol]=(2)))
GO
ALTER TABLE [dbo].[TableB] CHECK CONSTRAINT [CK_TableB_PartCol]
GO
ALTER TABLE [dbo].[TableB] ADD CONSTRAINT [DF_TableB_Round] DEFAULT ((2))
FOR [PartCol]
GO
ALTER TABLE [dbo].[TableB] ADD CONSTRAINT [DF_TableB_CreatedDate] DEFAULT
(getutcdate()) FOR [CreatedDate]
GO
CREATE VIEW [dbo].[ViewAB]
AS
SELECT *
FROM dbo.TableA
UNION ALL
SELECT *
FROM dbo.TableB
GO

Charles (blank (AT) nowhere (DOT) com) writes:
Under CREATE VIEW I find this point in the section "Conditions for Modifying
Data in Partitioned Views":

Columns in the view that are an identity column in one or more of the
member tables cannot be modified by using an INSERT or UPDATE statement.

I'm not entirely sure that I see the point with an IDENTITY column in a
table that is part of a paritioned view, but a possible workaround is
to use an INSTEAD OF trigger:

CREATE TRIGGER tri ON ViewAB INSTEAD OF INSERT AS
INSERT TableA(PartCol, Name, Description, CreatedDate)
SELECT PartCol, Name, Description, CreatedDate
FROM inserted WHERE PartCol = 1
INSERT TableB(PartCol, Name, Description, CreatedDate)
SELECT PartCol, Name, Description, CreatedDate
FROM inserted WHERE PartCol = 2
go
INSERT ViewAB(PartCol, TableAId, Name, Description, CreatedDate)
VALUES(1, NULL, 'NULL', NULL, getdate())




--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Hi Erland

Thanks for the quick reply.

I saw the same thing, but as I'm not trying to modify the identity column I
assumed I was ok.

The reason for the identity column is that it is used as a FK in associated
tables. I started off with, for example,

TableA
TableWithFKOnTableA

This has a PK that is an identity column. Then I needed to partition the
data. So I added

TableB
TableWithFKOnTableB

I have a partitioned view - ViewAB - that allows me to SELECT the data, but
I also need to be able to INSERT into the underlying tables as well.

I will try the INSTEAD OF trigger, although it seems to defeat part of the
object of a partitioned view. Should I expect it to be more or less
efficient/fast, or is it much the same?

Charles


"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote

Charles (blank (AT) nowhere (DOT) com) writes:
Well, there is this other paragraph:

The INSERT statement must supply values for all the columns in the
view, even if the underlying member tables have a DEFAULT constraint
for those columns or if they allow for null values. For those member
table columns that have DEFAULT definitions, the statements cannot
explicitly use the keyword DEFAULT.

So you must provide a value for that IDENTITY column, which means that
you would modify it.

I guess there is some overhead, but it may be acceptable.

Then again, an alternative is to roll your own and use IDENTITY. The main
reason to use IDENTITY is permit high concurrency, as rolling your own
means that you need to lock the current key value until the end of the
transaction.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

I take your point, but it seems like a perversely round-about way to state
that the underlying table can't have an identity column; they could just
come out and say it to avoid any confusion.

Could you elaborate on the "roll your own" idea? I won't have a concurrency
issue as there will only ever be one process inserting at any time, although
I would obviously not like to leave that door open. It might be that the
overhead of the trigger is not too great, but some tables I will want to
insert ~500,000 rows, one at-a-time, in one hit, so to speak, and I would
want it to be as quick as possible. I also like to explore all the options
before I finally settle on one.

Thanks

Charles


"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote

Charles (blank (AT) nowhere (DOT) com) writes:
For a plain table, you do:

BEGIN TRANSACTION

SELECT @id = coalesce(MAX(id), 0)
FROM ViewAB WITH (UPDLOCK)
WHERE PartCol = @PartCol

INSERT ViewAB(PartCol, id, ...)
VALUES (PartCol, @id, ...)

COMMIT TRANSACTION

If you insert many rows at a time, you can use the row_number to generate
the successive id:s.

Note that code above works correctly with multiple inserters; the UPDLOCK
protects against two processes getting the same ID (save when the
underlying table is empty). But since there is a serialisation point,
the throughput is not as good as with IDENTITY.




--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Ok, I see what you mean now. I think I'll stick with the trigger idea for
now then. I'll give it a trial run and see how the performance compares.

Thanks

Charles


"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote