May 11, 2015 at 9:57 am
Hi, is it possible to have a field that is calculated using a User Defined Function, and index that field?
May 11, 2015 at 10:06 am
Yes and Yes no. If you post an example of what you are trying to do specifically we can provide a specific example.
If you are talking about doing something like this:
CREATE FUNCTION dbo.twoPlus(@x int)
RETURNS int AS
BEGIN
RETURN 2+@x
END;
GO
CREATE TABLE dbo.sometable
(
someid int identity primary key,
someint AS dbo.twoPlus(10),
somevalue char(36)
)
GO
Yes, you can use a UDF to create the value (forgive my lame example)... but no, you can't add an index to that column.
-- Itzik Ben-Gan 2001
May 11, 2015 at 10:31 am
Thanks for your reply. A pity.... probably have to use a trigger that updates the indexed field when the record is updated/inserted....
May 11, 2015 at 10:51 am
Alan.B (5/11/2015)
Yes andYesno. If you post an example of what you are trying to do specifically we can provide a specific example.If you are talking about doing something like this:
CREATE FUNCTION dbo.twoPlus(@x int)RETURNS int AS
BEGIN
RETURN 2+@x
END;
GO
CREATE TABLE dbo.sometable
(
someid int identity primary key,
someint AS dbo.twoPlus(10),
somevalue char(36)
)
GO
Yes, you can use a UDF to create the value (forgive my lame example)... but no, you can't add an index to that column.
Sorry Alan, but patently not correct. If the function is a well written scalar function and is determinant in nature, then you CAN PERSIST the column and put an index on it. It's one of the tricks I use when people make the horrible mistake of storing months and years in different columns and then wanting to search them or sort them.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 11, 2015 at 11:37 am
What I want is a system that a user can enter a telephonenumber, and that that automatically gets converted to international style. For example for a Ducth mobile number: 06-12345678 would get converted to 0031612345678.
I have an example, please create 1 UDF and two tables:
CREATE FUNCTION [dbo].[fx_formatTelnr] (@project INT, @telnr NCHAR(20))
RETURNS NCHAR(20)
AS
BEGIN
DECLARE @retTel NCHAR(20) = ''
SET @retTel = CASE (SELECT country FROM project WHERE pid=@project)
WHEN 1 THEN RTRIM('0031' + @telnr)
WHEN 2 THEN RTRIM('0032' + @telnr)
ELSE @telnr
END
RETURN (@retTel)
END
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[campaign](
[PID] [int] IDENTITY(1,1) NOT NULL,
[project] [int] NULL,
[user_tel] [nchar](20) NULL,
[intl_tel] AS ([dbo].[fx_formatTelnr]([project],[user_tel]))
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[project] Script Date: 11-5-2015 19:33:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[project](
[PID] [int] IDENTITY(1,1) NOT NULL,
[country] [int] NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[campaign] ON
GO
INSERT [dbo].[campaign] ([PID], [project], [user_tel]) VALUES (1, 1, N'12345 ')
GO
INSERT [dbo].[campaign] ([PID], [project], [user_tel]) VALUES (2, 2, N'54321 ')
GO
SET IDENTITY_INSERT [dbo].[campaign] OFF
GO
SET IDENTITY_INSERT [dbo].[project] ON
GO
INSERT [dbo].[project] ([PID], [country]) VALUES (1, 1)
GO
INSERT [dbo].[project] ([PID], [country]) VALUES (2, 2)
GO
SET IDENTITY_INSERT [dbo].[project] OFF
GO
This works all pretty well. But when I try to change the dbo.campaign field intl_tel into IsPersisted = YES (needed for indexing) I get the error:
'campaign' table
- Unable to modify table.
Computed column 'intl_tel' in table 'Tmp_campaign' cannot be persisted because the column is non-deterministic.
What now?
May 11, 2015 at 12:05 pm
maybe add another column to your table and set its value in the stored procedure that adds the record.
May 11, 2015 at 12:52 pm
Further on Jeff's post, simpler logic can be wrapped in SQL deterministic functions.
😎
This function is not deterministic although it is only a simple subtraction of two columns
CREATE FUNCTION dbo.SVF_SUBTRACT_TAX_FROM_TOTAL
(
@TRAN_ID INT
)
RETURNS NUMERIC(12,2)
AS
BEGIN
RETURN
(
SELECT
ST.TOTAL_AMOUNT - ST.TAX_AMOUNT
FROM dbo.TBL_SAMPLE_TRANSACTION ST
WHERE ST.TRAN_ID = @TRAN_ID
)
END
;
A calculated column using this function cannot be persisted but using i.e. ABS() will work
ALTER TABLE dbo.TBL_SAMPLE_TRANSACTION ADD TRAN_NET AS (ABS(TOTAL_AMOUNT - TAX_AMOUNT)) PERSISTED;
And if you have a flawed tax system then this will also work
ALTER TABLE dbo.TBL_SAMPLE_TRANSACTION ADD TRAN_NET AS (ABS(TOTAL_AMOUNT - TAX_AMOUNT) * SIGN(TOTAL_AMOUNT - TAX_AMOUNT)) PERSISTED;
SQL Server's built in deterministic functions are:
ABS, DATEDIFF, POWER, ACOS, DAY, RADIANS, ASIN, DEGREES, ROUND, ATAN, EXP, SIGN, ATN2, FLOOR, SIN, CEILING, ISNULL, SQUARE, COALESCE, ISNUMERIC, SQRT, COS, LOG, TAN, COT, LOG10, YEAR, DATALENGTH, MONTH, DATEADD, NULLIF, ASCII, LTRIM, SOUNDEX, CHAR, NCHAR, SPACE, CHARINDEX, PATINDEX, STR, CONCAT, QUOTENAME, STUFF, DIFFERENCE, REPLACE, SUBSTRING, FORMAT, REPLICATE, UNICODE, LEFT, REVERSE, UPPER, LEN, RIGHT, LOWER, RTRIM
PS: thinking about it, maybe not the best example as this of course will work too
ALTER TABLE dbo.TBL_SAMPLE_TRANSACTION ADD TRAN_NET AS (TOTAL_AMOUNT - TAX_AMOUNT) PERSISTED;
Edit: added tax haven
May 11, 2015 at 1:52 pm
Jeff Moden (5/11/2015)
Alan.B (5/11/2015)
Yes andYesno. If you post an example of what you are trying to do specifically we can provide a specific example.If you are talking about doing something like this:
CREATE FUNCTION dbo.twoPlus(@x int)RETURNS int AS
BEGIN
RETURN 2+@x
END;
GO
CREATE TABLE dbo.sometable
(
someid int identity primary key,
someint AS dbo.twoPlus(10),
somevalue char(36)
)
GO
Yes, you can use a UDF to create the value (forgive my lame example)... but no, you can't add an index to that column.
Sorry Alan, but patently not correct. If the function is a well written scalar function and is determinant in nature, then you CAN PERSIST the column and put an index on it. It's one of the tricks I use when people make the horrible mistake of storing months and years in different columns and then wanting to search them or sort them.
Thank you for the correction Jeff. And sorry to the OP for the bad info.
-- Itzik Ben-Gan 2001
May 11, 2015 at 1:54 pm
Sounds like it should work, however when I change my formula to:
RTRIM(([dbo].[fx_formatTelnr]([project],[user_tel])))
I still get the error.
What strikes me is the following:I am trying to persist the field in table campaign, but the error refers to table 'Tmp_campaign' ?????? What is this table?
May 11, 2015 at 2:06 pm
Raymond van Laake (5/11/2015)
Sounds like it should work, however when I change my formula to:
RTRIM(([dbo].[fx_formatTelnr]([project],[user_tel])))I still get the error.
What strikes me is the following:I am trying to persist the field in table campaign, but the error refers to table 'Tmp_campaign' ?????? What is this table?
Quick thought, possibly you are going down the wrong road here as it is much easier to use an insert/update trigger on a normal column rather than a calculated column for this.
😎
May 11, 2015 at 2:06 pm
Raymond van Laake (5/11/2015)
Sounds like it should work, however when I change my formula to:
RTRIM(([dbo].[fx_formatTelnr]([project],[user_tel])))I still get the error.
What strikes me is the following:I am trying to persist the field in table campaign, but the error refers to table 'Tmp_campaign' ?????? What is this table?
Are you using the GUI to make the change or are you using T-SQL?
May 11, 2015 at 2:20 pm
Hmm, yes was considering using a trigger... thanks for all your inputs
May 11, 2015 at 2:33 pm
Raymond van Laake (5/11/2015)
Hmm, yes was considering using a trigger... thanks for all your inputs
When you use the GUI to make some changes to a table, SSMS will create a temporary table (Tmp_tablename) with your changes, move the data from the original table to the temporary table, drop the original table, rename the temporary table to the original table and recreate the indexes and other constraints. This is why you are seeing the error on a table named Tmp_tablename.
May 11, 2015 at 2:34 pm
Lynn Pettis (5/11/2015)
Raymond van Laake (5/11/2015)
Hmm, yes was considering using a trigger... thanks for all your inputsWhen you use the GUI to make some changes to a table, SSMS will create a temporary table (Tmp_tablename) with your changes, move the data from the original table to the temporary table, drop the original table, rename the temporary table to the original table and recreate the indexes and other constraints. This is why you are seeing the error on a table named Tmp_tablename.
+1 Good catch!
😎
May 11, 2015 at 2:50 pm
Jeff Moden (5/11/2015)
Alan.B (5/11/2015)
Yes andYesno. If you post an example of what you are trying to do specifically we can provide a specific example.If you are talking about doing something like this:
CREATE FUNCTION dbo.twoPlus(@x int)RETURNS int AS
BEGIN
RETURN 2+@x
END;
GO
CREATE TABLE dbo.sometable
(
someid int identity primary key,
someint AS dbo.twoPlus(10),
somevalue char(36)
)
GO
Yes, you can use a UDF to create the value (forgive my lame example)... but no, you can't add an index to that column.
Sorry Alan, but patently not correct. If the function is a well written scalar function and is determinant in nature, then you CAN PERSIST the column and put an index on it. It's one of the tricks I use when people make the horrible mistake of storing months and years in different columns and then wanting to search them or sort them.
Something like this?
-- Reference: https://technet.microsoft.com/en-us/library/aa214775%28v=sql.80%29.aspx
USE tempdb
GO
IF OBJECT_ID('tempdb.dbo.x') IS NOT NULL DROP TABLE dbo.x;
IF OBJECT_ID('tempdb.dbo.MakeDate') IS NOT NULL DROP FUNCTION dbo.MakeDate;
GO
-- poorly designed table where a date attribute is split
CREATE TABLE dbo.x
(
id int identity primary key,
xMonth tinyint,
xYear smallint
);
GO
-- sample values
INSERT dbo.x (xMonth, xYear) VALUES (1,2010),(2,2010),(3,2010);
GO
-- results without the calculated column
SELECT * FROM dbo.x;
GO
-- now we fix this table...a
-- create a deterministic function to turn xMonth and xYear into a Date
CREATE FUNCTION dbo.MakeDate(@m varchar(2), @y char(4))
RETURNS varchar(10) WITH SCHEMABINDING AS -- required to make function deterministic
BEGIN
RETURN '1-'+@m+'-'+@y
END
GO
-- add a date Persisted date column that used a deterministic UDF
ALTER TABLE dbo.x ADD xDate AS dbo.MakeDate(xMonth,xYear) PERSISTED;
GO
-- create the index
CREATE INDEX xxx ON dbo.x(xDate) INCLUDE (xMonth, xYear);
-- run this statement with "include query plan on"
-- Let's get the most recent month/year
SELECT TOP 1 xMonth, xYear, xDate
FROM dbo.x
ORDER BY xDate DESC;
-- Itzik Ben-Gan 2001
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply