Quick Question: Creating Indexes on Functions

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 2000 Forums

SQL Server Development (2000)

Quick Question: Creating Indexes on Functions

Author

Topic

danielhai
Yak Posting Veteran

50 Posts

Posted - 2004-05-19 : 19:09:13

I'm using a function that is returning data similar to the hierarchy article here: http://www.sqlteam.com/item.asp?ItemID=8866

I'm wondering if it's possible to create an index on it - my unique data works across 2 fields rather then just one. Would be nice to have clustered index seek rather then an table scan on every hit of this function.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-19 : 19:13:27

Indexes can only be created on tables or views.

Tara

gpl
Posting Yak Master

195 Posts

Posted - 2004-05-19 : 19:24:05

Tara
I must have been doing it wrong, Ive been creating a clustered index on my functions that return a table format

Graham

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-19 : 19:30:36

Show me an example. I get an error when I try to do it:

CREATE FUNCTION SalesByStore (@storeid varchar(30))
RETURNS TABLE
AS
RETURN (SELECT title, qty
FROM sales s, titles t
WHERE s.stor_id = @storeid and
t.title_id = s.title_id)

CREATE UNIQUE CLUSTERED INDEX test
ON SalesByStore (title, qty)

The function is copied directly out of BOL. Here is the error that I get:

Server: Msg 1914, Level 16, State 1, Line 1
Index cannot be created on object 'SalesByStore' because the object is not a user table or view.

Tara

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-19 : 19:32:04

Post an example Graham, please.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-05-19 : 19:45:33

He may be referring to a table variable..

They can cope with PK and Unique Contraints.. as far clustering the constraints/indexes, that should be a moot point as the table is meant to be created in memory..... NOT :-)

DavidM

"Always pre-heat the oven"

gpl
Posting Yak Master

195 Posts

Posted - 2004-05-20 : 04:53:54

Tara et al

here is an example adapted from code on this site

CREATE FUNCTION dbo.CsvToTable (@Array VARCHAR(1000))
RETURNS @IntTable TABLE (IntValue int, PRIMARY KEY CLUSTERED ([IntValue]))
AS
BEGIN
DECLARE @Separator Char(1)
DECLARE @SeparatorPos int
DECLARE @ArrayValue Varchar(1000)

SET @Separator = ','
SET @Array = @Array + ','

WHILE PATINDEX('%,%', @Array) <> 0
BEGIN
SET @SeparatorPos = PATINDEX('%,%', @Array)
SET @ArrayValue = LEFT(@Array, @SeparatorPos -1)

INSERT @IntTable VALUES(
CAST(@ArrayValue AS INT))

SET @Array = STUFF(@Array, 1, @SeparatorPos, '')
END

RETURN
END

David -yes its a table variable (I assume, Im new to functions and SQL 2k in general)

Graham

danielhai
Yak Posting Veteran

50 Posts

Posted - 2004-05-21 : 18:48:28

primary key on a returned table variable function. If you test it out it does do a clustered index scan if you're looking @ the right column.

CREATE FUNCTION dbo.fn_GetIDsFromString (@String varchar(8000))
RETURNS @Return Table (ID int, RowID int identity PRIMARY KEY)
AS
BEGIN

declare @Current smallint,
@TempString varchar(20)

set @Current = 1

while (CHARINDEX(',',@String,@Current) > 0)
begin
set @tempstring = CHARINDEX(',',@String,@Current) - @Current

if @tempstring <> 0
begin
insert into @Return (ID) values (substring(@String,@Current, cast(@tempstring as int)) )
end

set @Current = CHARINDEX(',',@String,@Current) + 1
end

if substring(@String,@Current, len(@String)) <> ''
insert into @Return (ID) values (substring(@String,@Current, len(@String)) )

return
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources