March 2, 2016 at 4:07 am
I have created a schema bound view that is self referencing via a computed column function. Creating an index on the view works. Querying the view returns results and errors. In SQL 2008, no error is returned, SQL 2008 R2 it is.
Jonathan Kehayias wrote about a workaround by Steve Kass and Aaron Bertrand.
I need some help applying this to my case.
Repro code: (Quite long sry)
USE TempDB
GO
-- Schema bound view has to be dropped first.
IF OBJECT_ID('dbo.vHeaderTblAgg', 'V') IS NOT NULL
DROP VIEW dbo.vHeaderTblAgg
IF OBJECT_ID('HeaderTbl') IS NOT NULL
DROP TABLE HeaderTbl
-- Niether the table or the function can be dropped without first dropping the column.
IF OBJECT_ID('CompColTbl') IS NOT NULL
ALTER TABLE CompColTbl DROP COLUMN CompCol_StartingSec
IF OBJECT_ID('dbo.ufn_GetSelfRefValue', 'FN') IS NOT NULL
DROP FUNCTION dbo.ufn_GetSelfRefValue
IF OBJECT_ID('CompColTbl') IS NOT NULL
DROP TABLE CompColTbl
CREATE TABLE HeaderTbl
(
HeaderTblID TinyInt IDENTITY CONSTRAINT PK_HeaderTblID_ID PRIMARY KEY CLUSTERED,
CustIDTinyInt NOT NULL CONSTRAINT DF_HeaderTbl_CustID DEFAULT(1)
)
CREATE TABLE CompColTbl
(
CompColTblIDTinyInt IDENTITY CONSTRAINT PK_CompColTbl_ID PRIMARY KEY CLUSTERED,
HeaderTblIDTinyInt NOT NULL,
SortOrderNrTinyInt NOT NULL,
DurationSecTinyInt NOT NULL
)
GO
CREATE FUNCTION dbo.ufn_GetSelfRefValue
(
@CompColTblIDTinyInt
)
RETURNS TINYINT WITH SCHEMABINDING
AS
BEGIN
DECLARE @DurationSec Int;
SELECT@DurationSec = ISNULL(
(
SELECTSUM(DurationSec)
FROMdbo.CompColTbl CCT
WHERECCT.HeaderTblID = CCT1.HeaderTblID
AND ISNULL(CAST(CCT.SortOrderNr AS INT),CCT.CompColTblID) < ISNULL(CAST(CCT1.SortOrderNr AS INT),CCT1.CompColTblID)
),0)
FROMdbo.CompColTbl CCT1
WHERECCT1.CompColTblID = @CompColTblID
RETURN@DurationSec
END
GO
ALTER TABLE CompColTbl ADD CompCol_StartingSec AS dbo.ufn_GetSelfRefValue(CompColTblID)
GO
INSERT HeaderTbl DEFAULT VALUES
DECLARE@HeaderTblID TinyInt = SCOPE_IDENTITY()
INSERTCompColTbl
(HeaderTblID, SortOrderNr, DurationSec)
SELECTTOP (5)
HeaderTblID= @HeaderTblID,
SortOrderTblID= ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
DurationSec= ABS(CHECKSUM(NEWID())) % 50 + 1
FROMsys.all_columns c1, sys.all_columns c2
SELECT* FROM HeaderTbl
SELECT* FROM CompColTbl
GO
CREATE VIEW dbo.vHeaderTblAgg
WITH SCHEMABINDING
AS
SELECTCCT.HeaderTblID,
HT.CustID,
TotalSec= SUM(ISNULL(CCT.DurationSec, 0)),
CompColTblCnt= COUNT_BIG(*)
FROMdbo.HeaderTbl HT
INNER JOIN dbo.CompColTbl CCT
ON HT.HeaderTblID = CCT.HeaderTblID
GROUPBY CCT.HeaderTblID, HT.CustID
GO
SELECT*
FROMvHeaderTblAgg
CREATE UNIQUE CLUSTERED INDEX UCI_vHeaderTblAgg_HeaderID ON dbo.vHeaderTblAgg (HeaderTblID)
--Cannot create index on view 'TempDB.dbo.vHeaderTblAgg' because its select list does not include a proper use of COUNT_BIG. Consider adding COUNT_BIG(*) to select list.
SELECT*
FROMvHeaderTblAgg
/* 2008 R2 and above
Msg 4429, Level 16, State 1, Procedure ufn_GetSelfRefValue, Line 111
View or function 'vHeaderTblAgg' contains a self-reference. Views or functions cannot reference themselves directly or indirectly.
*/
-- Cleanup
IF OBJECT_ID('dbo.HeaderTblAgg', 'V') IS NOT NULL
DROP VIEW dbo.vHeaderTblAgg
IF OBJECT_ID('HeaderTbl') IS NOT NULL
DROP TABLE HeaderTbl
-- Niether the table or the function can be dropped without first dropping the column.
IF OBJECT_ID('CompColTbl') IS NOT NULL
ALTER TABLE CompColTbl DROP COLUMN CompCol_StartingSec
IF OBJECT_ID('dbo.ufn_GetSelfRefValue', 'FN') IS NOT NULL
DROP FUNCTION dbo.ufn_GetSelfRefValue
IF OBJECT_ID('CompColTbl') IS NOT NULL
DROP TABLE CompColTbl
March 8, 2016 at 7:55 am
Annoyingly enough, this resolved itself after a couple of hours. :alien:
I went back to it to try adding synonyms into the mix, but it was no longer needed.
March 8, 2016 at 9:05 am
DennisPost (3/8/2016)
Annoyingly enough, this resolved itself after a couple of hours. :alien:I went back to it to try adding synonyms into the mix, but it was no longer needed.
There might be a simpler and more robust solution for this. Can you give us a brief explanation of what you're trying to do? For instance, the FUNCTION dbo.ufn_GetSelfRefValue appears to sum up all of the durations of rows less than the parent row, or something.
More of a what you're trying to do than a how you're trying to do it.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 9, 2016 at 3:13 am
Thanks for your input Chris.
You are right in what the function does.
I am migrating a SSIS package to a new acceptance environment. The package works fine live, but not in acceptance due to this problem.
Now that it has resolved itself the package works fine in acceptance.
Did you get the same error as I did running the repro code?
I get the feeling the code is a dud.
March 22, 2016 at 9:39 am
I started getting this error again so I added in a workaround with a synonym.
So far so good....
Repro code change:
IF OBJECT_ID('syn_CompColTbl', 'SN') IS NOT NULL
DROP SYNONYM syn_CompColTbl
-- Workaround for self referencing error
CREATE SYNONYM syn_CompColTbl FOR dbo.CompColTbl
GO
CREATE FUNCTION dbo.ufn_GetSelfRefValue
(
@CompColTblIDTinyInt
)
RETURNS TINYINT -- WITH SCHEMABINDING -- Cannot use schemabinding with synonyms.
AS
BEGIN
DECLARE @DurationSec Int;
SELECT@DurationSec = ISNULL(
(
SELECTSUM(DurationSec)
FROMsyn_CompColTbl CCT --dbo.CompColTbl CCT
WHERECCT.HeaderTblID = CCT1.HeaderTblID
AND ISNULL(CAST(CCT.SortOrderNr AS INT),CCT.CompColTblID) < ISNULL(CAST(CCT1.SortOrderNr AS INT),CCT1.CompColTblID)
),0)
FROMdbo.CompColTbl CCT1
WHERECCT1.CompColTblID = @CompColTblID
RETURN@DurationSec
END
GO
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply