October 9, 2015 at 10:38 am
The short version of the story is this...
I have a Column called "BillID" that is a VARCHAR(20) data type. A typical value looks something like this... A6304158-2
The problem is that the numeric portion between the last alpha character and the dash, is an invoice number and we need to be able join that back to our actual invoice tables.
So, I had the bright idea to simply write a scalar udf and use that to create a persisted computed column...
So far so good... I have verified that the column is indeed persisted.
The problem is that SQL Server is balking at any attempt to include this column in an index.
AND
Looking at the execution plan for a newly written query, the UDF is being called rather than simply looking at the persisted value.
Clearly my understanding of persisted columns is lacking here. I'm hoping that someone may be able to fill in the blanks.
Would I be better off dropping the computed column and simply use an insert/update trigger to populate this column?
The function code is below.
CREATE FUNCTION dbo.GetInvoiceIDFromBillID
/* ===========================================================
10/08/2015 JL, Created to strip the InvoiceID out or the BillID
to be used to create a persisted, computed column on the BlahBlahBlah_PRI table
=========================================================== */
(
@BillID VARCHAR(20)
)
RETURNS INT WITH SCHEMABINDING
AS
BEGIN
DECLARE @InvoiceID INT;
IF TRY_CAST(@BillID AS INT) > 0
BEGIN
SET @InvoiceID = @BillID;
END
ELSE
BEGIN
SELECT @InvoiceID = dbo.CLR_RemoveNonNumeric(sc.Item) FROM dbo.SplitCSVToTable8K(@BillID, '-') sc WHERE sc.ItemNumber = 1;
END
RETURN @InvoiceID
END;
Note: dbo.CLR_RemoveNonNumeric is a CLRfunction that simply strips out any non-numeric characters and dbo.SplitCSVToTable8K is a renamed version of Jeff's DelimitedSplit8K function.
The error I get when trying to create an index is as follows:
Msg 10137, Level 16, State 1, Line 4
Cannot create index on view "RevenueCycle.dbo.SplitCSVToTable8K" because it references common table expression "E1". Views referencing common table expressions cannot be indexed. Consider not indexing the view, or removing the common table expression from the view definition.
The statement has been terminated.
Baffling since "RevenueCycle.dbo.SplitCSVToTable8K" is a iTVF not a view and the fact that I'm attempting to index is persisted.
Let me know if you required test tables and sample data...
Thanks in advance,
Jason
October 9, 2015 at 10:56 am
Who am I kidding? Test table & data is always required...
USE RandomTests;
GO
CREATE TABLE dbo.PersistedColumnTest (
BillID VARCHAR(20)
);
INSERT dbo.PersistedColumnTest (BillID) VALUES
('5345227-2'),('6209822-2'),('6209822-2'),('6721796-2'),('6721796-2'),('6721796-2'),('6721796-2'),('6719008-2'),('6210153-2'),('6203656-2'),
('6209822-2'),('6209822-2'),('6700546-2'),('6680192-2'),('6700546-2'),('6619450-2'),('6721796-2'),('6145204-2'),('6145204-2'),('5906198-2'),
('7390138'),('7390138'),('7390130'),('7390138'),('7390138'),('7390138'),('7390130'),('7390138'),('7390130'),('7390130'),('7390130'),('7389910'),
('7389910'),('7390501'),('7390101'),('7390101'),('7390101'),('7395511'),('7395511'),('7390101'),('A6761374'),('A6761374'),('A6761374'),('A6761374'),
('A6760682'),('A6760682'),('A6760682'),('A6760682'),('A6745076'),('A6745076'),('A6745076'),('A6745076'),('A6745076'),('A6745076'),('A6757524'),
('A6757524'),('A6757524'),('A6757524'),('A6760741'),('A6762905'),('A6762905'),('A6371075-2'),('A6371075-2'),('A6371075-2'),('A6371075-2'),
('A6376568-2'),('A6376568-2'),('A6376568-2'),('A6376568-2'),('A6315422-2'),('A6315422-2'),('A6315422-2'),('A6315422-2'),('A6363316-2'),('A6363316-2'),
('A6363316-2'),('A6193029-3'),('A6193029-3'),('A6089801-3'),('A6089801-3'),('A6315423-2');
GO
CREATE FUNCTION dbo.GetInvoiceIDFromBillID
/* ===========================================================
10/08/2015 JL, Created to strip the InvoiceID out or the BillID
to be used to create a persisted, computed column on the BlahBlahBlah_PRI table
=========================================================== */
(
@BillID VARCHAR(20)
)
RETURNS INT WITH SCHEMABINDING
AS
BEGIN
DECLARE @InvoiceID INT;
IF TRY_CAST(@BillID AS INT) > 0
BEGIN
SET @InvoiceID = @BillID;
END
ELSE
BEGIN
SELECT @InvoiceID = dbo.CLR_RemoveNonNumeric(sc.Item) FROM dbo.SplitCSVToTable8K(@BillID, '-') sc WHERE sc.ItemNumber = 1;
END
RETURN @InvoiceID
END;
GO
ALTER TABLE dbo.PersistedColumnTest ADD InvoiceNumber AS dbo.GetInvoiceIDFromBillID(BillID) PERSISTED;
GO
Unfortunately, I don't have direct access to our CLR assemblies, so that part will be tougher to replicate. Sorry about that...
October 9, 2015 at 11:16 am
Jason A. Long (10/9/2015)
The short version of the story is this...I have a Column called "BillID" that is a VARCHAR(20) data type. A typical value looks something like this... A6304158-2
The problem is that the numeric portion between the last alpha character and the dash, is an invoice number and we need to be able join that back to our actual invoice tables.
So, I had the bright idea to simply write a scalar udf and use that to create a persisted computed column...
So far so good... I have verified that the column is indeed persisted.
The problem is that SQL Server is balking at any attempt to include this column in an index.
AND
Looking at the execution plan for a newly written query, the UDF is being called rather than simply looking at the persisted value.
Clearly my understanding of persisted columns is lacking here. I'm hoping that someone may be able to fill in the blanks.
Would I be better off dropping the computed column and simply use an insert/update trigger to populate this column?
The function code is below.
CREATE FUNCTION dbo.GetInvoiceIDFromBillID
/* ===========================================================
10/08/2015 JL, Created to strip the InvoiceID out or the BillID
to be used to create a persisted, computed column on the BlahBlahBlah_PRI table
=========================================================== */
(
@BillID VARCHAR(20)
)
RETURNS INT WITH SCHEMABINDING
AS
BEGIN
DECLARE @InvoiceID INT;
IF TRY_CAST(@BillID AS INT) > 0
BEGIN
SET @InvoiceID = @BillID;
END
ELSE
BEGIN
SELECT @InvoiceID = dbo.CLR_RemoveNonNumeric(sc.Item) FROM dbo.SplitCSVToTable8K(@BillID, '-') sc WHERE sc.ItemNumber = 1;
END
RETURN @InvoiceID
END;
Note: dbo.CLR_RemoveNonNumeric is a CLRfunction that simply strips out any non-numeric characters and dbo.SplitCSVToTable8K is a renamed version of Jeff's DelimitedSplit8K function.
The error I get when trying to create an index is as follows:
Msg 10137, Level 16, State 1, Line 4
Cannot create index on view "RevenueCycle.dbo.SplitCSVToTable8K" because it references common table expression "E1". Views referencing common table expressions cannot be indexed. Consider not indexing the view, or removing the common table expression from the view definition.
The statement has been terminated.
Baffling since "RevenueCycle.dbo.SplitCSVToTable8K" is a iTVF not a view and the fact that I'm attempting to index is persisted.
Let me know if you required test tables and sample data...
Thanks in advance,
Jason
Question, using this as an example: A6304158-2, is there always a single alpha character at the beginning followed by one or more numeric values (0 - 9) followed by a dash then one or more numeric values. Also, you only want the numeric value between the single alpha character and the dash, correct?
October 9, 2015 at 11:25 am
I found a 'GetNumbers' function here [/url] and that, when combined with Jeff's function, allows you to take the CLR out of the equation ... at least for the data you provide.
create function dbo.GetNumbers ( @data varchar(8000) )
returns varchar(8000)
as
begin
return left(
substring(@Data, patindex('%[0-9.-]%', @data), 8000),
patindex('%[^0-9.-]%', substring(@Data, patindex('%[0-9.-]%', @data), 8000) + 'X')-1);
end;
go
select BillID ,
dbo.GetNumbers(split.Item)
from dbo.PersistedColumnTest
cross apply dbo.SplitCSVToTable8K(BillID, '-') split
where split.ItemNumber = 1;
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 9, 2015 at 11:35 am
Question, using this as an example: A6304158-2, is there always a single alpha character at the beginning followed by one or more numeric values (0 - 9) followed by a dash then one or more numeric values. Also, you only want the numeric value between the single alpha character and the dash, correct?
No. It can be a straight up numeric value that requires no cleansing, a leading alpha with no dash, a leading alpha with a dash or no leading alpha with the dash.
So basically 4 different variants (that I've found so far).
October 9, 2015 at 11:38 am
This might seem weird, but have you tried to recreate the table?
I kept getting an error which seemed to be replicating your problem saying that the column couldn't be indexed because it used a cte. When I moved the column to the CREATE TABLE statement, the error stopped.
October 9, 2015 at 11:42 am
Phil Parkin (10/9/2015)
I found a 'GetNumbers' function here [/url] and that, when combined with Jeff's function, allows you to take the CLR out of the equation ... at least for the data you provide.
Phil - I don't think it's the CLR that's causing the problem. I only mentioned I figured that it would make it difficult for answers to replicate the function in their own test environment. The index error appears to be balking at the splitter function, not the CLR.
The real question in my mind, why is the function coming into play at all when the column values are persisted?
What's the point in persisting a value if SQL Server is going to ignore the value and recalculate it on the fly every time it's included in a query?
October 9, 2015 at 11:43 am
Here's an iTVF version of the query I posted earlier.
create function dbo.GetNumbers ( @data varchar(8000) )
returns table
return
select nums = left(substring(@Data, patindex('%[0-9.-]%', @data), 8000),
patindex('%[^0-9.-]%',
substring(@Data, patindex('%[0-9.-]%', @data),
8000) + 'X') - 1);
go
select BillID ,
nums.*
from dbo.PersistedColumnTest
cross apply dbo.SplitCSVToTable8K(BillID, '-') split
cross apply dbo.GetNumbers(split.Item) nums
where split.ItemNumber = 1;
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 9, 2015 at 11:45 am
Jason A. Long (10/9/2015)
Phil Parkin (10/9/2015)
I found a 'GetNumbers' function here [/url] and that, when combined with Jeff's function, allows you to take the CLR out of the equation ... at least for the data you provide.Phil - I don't think it's the CLR that's causing the problem. I only mentioned I figured that it would make it difficult for answers to replicate the function in their own test environment. The index error appears to be balking at the splitter function, not the CLR.
The real question in my mind, why is the function coming into play at all when the column values are persisted?
What's the point in persisting a value if SQL Server is going to ignore the value and recalculate it on the fly every time it's included in a query?
Oops. I read 'CTE' and converted it to 'CLR' in my mind. Time to log off and down a beer :hehe:
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 9, 2015 at 11:45 am
Luis Cazares (10/9/2015)
This might seem weird, but have you tried to recreate the table?I kept getting an error which seemed to be replicating your problem saying that the column couldn't be indexed because it used a cte. When I moved the column to the CREATE TABLE statement, the error stopped.
Interesting... I'll give that a go in test and see if I get the same results. Thanks Luis!
October 9, 2015 at 11:48 am
Here's an alternative that will work, but not got around to performance testing yet, sorry.
CREATE FUNCTION dbo.GetInvoiceIDFromBillID(@Input VARCHAR(20))
RETURNS VARCHAR(20)
WITH SCHEMABINDING
AS
BEGIN
-- Function to extract all characters from the first numeric (0-9) up to but not including the first hyphen (-).
RETURN
ISNULL((
SELECT TOP(1) SUBSTRING(@Input, N, CHARINDEX('-', @Input+'-', N)- N) VALUE
FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20)) T(N)
WHERE SUBSTRING(@Input, N, 20) >= '0' AND SUBSTRING(@Input, N, 20) <= '9'
ORDER BY N),'');
END;
Edit:Change Function Name
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);October 9, 2015 at 11:48 am
Phil Parkin (10/9/2015)
Here's an iTVF version of the query I posted earlier.
Correct me if I'm wrong (a strong possibility), but I don't think we're able to reference TVFs in computed columns.
If I am wrong, what is the correct syntax?
October 9, 2015 at 11:50 am
Phil Parkin (10/9/2015)
Jason A. Long (10/9/2015)
Phil Parkin (10/9/2015)
I found a 'GetNumbers' function here [/url] and that, when combined with Jeff's function, allows you to take the CLR out of the equation ... at least for the data you provide.Phil - I don't think it's the CLR that's causing the problem. I only mentioned I figured that it would make it difficult for answers to replicate the function in their own test environment. The index error appears to be balking at the splitter function, not the CLR.
The real question in my mind, why is the function coming into play at all when the column values are persisted?
What's the point in persisting a value if SQL Server is going to ignore the value and recalculate it on the fly every time it's included in a query?
Oops. I read 'CTE' and converted it to 'CLR' in my mind. Time to log off and down a beer :hehe:
LOL... No worries. I appreciate you (and everyone else) taking the time to look at this with me. 🙂
October 9, 2015 at 12:01 pm
Jason A. Long (10/9/2015)
Phil Parkin (10/9/2015)
Here's an iTVF version of the query I posted earlier.Correct me if I'm wrong (a strong possibility), but I don't think we're able to reference TVFs in computed columns.
If I am wrong, what is the correct syntax?
I didn't check that. As you are already referencing dbo.SplitCSVToTable8K, I assumed it was OK.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 9, 2015 at 12:01 pm
mister.magoo (10/9/2015)
Here's an alternative that will work, but not got around to performance testing yet, sorry.
CREATE FUNCTION dbo.GetInvoiceIDFromBillID(@Input VARCHAR(20))
RETURNS VARCHAR(20)
WITH SCHEMABINDING
AS
BEGIN
-- Function to extract all characters from the first numeric (0-9) up to but not including the first hyphen (-).
RETURN
ISNULL((
SELECT TOP(1) SUBSTRING(@Input, N, CHARINDEX('-', @Input+'-', N)- N) VALUE
FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20)) T(N)
WHERE SUBSTRING(@Input, N, 20) >= '0' AND SUBSTRING(@Input, N, 20) <= '9'
ORDER BY N),'');
END;
Edit:Change Function Name
Absolute genius Magoo!
A simple inline tally split that stops on the dash... No deed for cross applied CTEs because we know the max length of 20 in advance. Simple and brilliant.
I'll replace my existing function and test before I attempt to recreate the entire table with the function in the table definition.
I have a meeting right now but I'll test as soon as that over and post the results.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply