Can I index a table in UDF ?

I create a UDF based on a table. The table (say tblA) has an index on column
A.
When I do a sql query like "select .. from dbo.udfMyProc() as MyProc,tblB
where tblB.colA = MyProc.A", will it use the index on tblA column A , or do
I
need to (and can I) create an index on the UDF ?
CREATE function dbo.udfMyProc()
returns @.myTable TABLE(a int, b bit, c varchar(50), d varchar(50), e
varchar(50), ...)
AS BEGIN
INSERT INTO @.myTable(a,b,c,d,e...)
select a,b,c...from tblA --> tblA.a is indexed
return
end
Thank you very much.That query will not use the index on "tblA" - you need to create one on the
temp table returned by the UDF. You cannot create a regular index on table
variables though (a CREATE INDEX statement), but you can use PRIMARY KEY and
UNIQUE constraints:
...
RETURNS @.tmp TABLE
(
somestring varchar(50) NOT NULL PRIMARY KEY
)
If the index can have non-unique records you can fake an index by adding an
identity column so that each record will be unique:
...
RETURNS @.tmp TABLE
(
somestring varchar(50) NOT NULL
, meaningless_column int NOT NULL IDENTITY(1,1)
, PRIMARY KEY (somestring, meaningless_column)
)
Make sure to make the IDENTITY column last in the PK/UNIQUE definition so
the index is still useful.
KH
"Paul fpvt2" wrote:

> I create a UDF based on a table. The table (say tblA) has an index on colu
mn A.
> When I do a sql query like "select .. from dbo.udfMyProc() as MyProc,tblB
> where tblB.colA = MyProc.A", will it use the index on tblA column A , or d
o I
> need to (and can I) create an index on the UDF ?
> CREATE function dbo.udfMyProc()
> returns @.myTable TABLE(a int, b bit, c varchar(50), d varchar(50), e
> varchar(50), ...)
> AS BEGIN
> INSERT INTO @.myTable(a,b,c,d,e...)
> select a,b,c...from tblA --> tblA.a is indexed
> return
> end
> Thank you very much.|||What do you mean by "use the index"? It will not copy the index onto
@.table, if that's what you mean. The only indexes you can create on a table
variable are via PRIMARY KEY or UNIQUE constraints... if you're talking
about ordering, that's a different conversation altogether...
"Paul fpvt2" <Paulfpvt2@.discussions.microsoft.com> wrote in message
news:1BA572FB-C5DA-4528-BAB2-A484FA8837A8@.microsoft.com...
>I create a UDF based on a table. The table (say tblA) has an index on
>column A.
> When I do a sql query like "select .. from dbo.udfMyProc() as MyProc,tblB
> where tblB.colA = MyProc.A", will it use the index on tblA column A , or
> do I
> need to (and can I) create an index on the UDF ?
> CREATE function dbo.udfMyProc()
> returns @.myTable TABLE(a int, b bit, c varchar(50), d varchar(50), e
> varchar(50), ...)
> AS BEGIN
> INSERT INTO @.myTable(a,b,c,d,e...)
> select a,b,c...from tblA --> tblA.a is indexed
> return
> end
> Thank you very much.|||"Paul fpvt2" <Paulfpvt2@.discussions.microsoft.com> wrote in message
news:1BA572FB-C5DA-4528-BAB2-A484FA8837A8@.microsoft.com...
>I create a UDF based on a table. The table (say tblA) has an index on
>column A.
> When I do a sql query like "select .. from dbo.udfMyProc() as MyProc,tblB
> where tblB.colA = MyProc.A", will it use the index on tblA column A , or
> do I
> need to (and can I) create an index on the UDF ?
> CREATE function dbo.udfMyProc()
> returns @.myTable TABLE(a int, b bit, c varchar(50), d varchar(50), e
> varchar(50), ...)
> AS BEGIN
> INSERT INTO @.myTable(a,b,c,d,e...)
> select a,b,c...from tblA --> tblA.a is indexed
> return
> end
> Thank you very much.
If your function truly consists of a single INSERT... SELECT statement then
you should turn it into an inline function rather than a multi-statement
one. That way your query against the function will be more likely to benefit
from an index on the base table. An example version of an inline function is
given below. This looks subtly different from what you posted but in terms
of the way the function works the difference is very significant.
CREATE function dbo.udfMyProc()
RETURNS TABLE
AS
RETURN (SELECT a,b,c...FROM tblA)
GO
BTW, if the function doesn't have any parameters then why use a function at
all? You could use a view for that.
Hope this helps.
David Portas
SQL Server MVP
--|||Thank you everybody for your replies.
I tried the view and it works a lot faster, I will use it instead.
But, I have a question about inline function. The example that you posted
looks the same with the UDF that I posted (CREATE function dbo.udfMyProc() )
What is the difference between UDF and inline function ?
Thanks a lot.
"David Portas" wrote:

> "Paul fpvt2" <Paulfpvt2@.discussions.microsoft.com> wrote in message
> news:1BA572FB-C5DA-4528-BAB2-A484FA8837A8@.microsoft.com...
> If your function truly consists of a single INSERT... SELECT statement the
n
> you should turn it into an inline function rather than a multi-statement
> one. That way your query against the function will be more likely to benef
it
> from an index on the base table. An example version of an inline function
is
> given below. This looks subtly different from what you posted but in terms
> of the way the function works the difference is very significant.
> CREATE function dbo.udfMyProc()
> RETURNS TABLE
> AS
> RETURN (SELECT a,b,c...FROM tblA)
> GO
> BTW, if the function doesn't have any parameters then why use a function a
t
> all? You could use a view for that.
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>
>|||One more question about inline function and views.
I understand that the ORDER BY clause is invalid in views and inline
functions.
If I need to use ORDER BY clause in my query inside the views or inline
function, Is there a way around it ?
Thanks.
"David Portas" wrote:

> "Paul fpvt2" <Paulfpvt2@.discussions.microsoft.com> wrote in message
> news:1BA572FB-C5DA-4528-BAB2-A484FA8837A8@.microsoft.com...
> If your function truly consists of a single INSERT... SELECT statement the
n
> you should turn it into an inline function rather than a multi-statement
> one. That way your query against the function will be more likely to benef
it
> from an index on the base table. An example version of an inline function
is
> given below. This looks subtly different from what you posted but in terms
> of the way the function works the difference is very significant.
> CREATE function dbo.udfMyProc()
> RETURNS TABLE
> AS
> RETURN (SELECT a,b,c...FROM tblA)
> GO
> BTW, if the function doesn't have any parameters then why use a function a
t
> all? You could use a view for that.
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>
>|||> What is the difference between UDF and inline function ?
there are two types of UDFs:
Inline. Consists of only one query. Think of it as a view or "macro".
Multi-statement. Here you define a table variable and populate that table va
riable, and when
function code exist at run-time the data is selected from the variable, All
that work is overhead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul fpvt2" <Paulfpvt2@.discussions.microsoft.com> wrote in message
news:B7BABDF8-B1A1-4735-BBFA-5A250B92B1F6@.microsoft.com...
> Thank you everybody for your replies.
> I tried the view and it works a lot faster, I will use it instead.
> But, I have a question about inline function. The example that you posted
> looks the same with the UDF that I posted (CREATE function dbo.udfMyProc()
)
> What is the difference between UDF and inline function ?
> Thanks a lot.
>
> "David Portas" wrote:
>|||Thanks.
Is the following UDF considered inline (because it only has 1 query: select
a,b,c...) ?
CREATE function dbo.udfMyProc()
returns @.myTable TABLE(a int, b bit, c varchar(50), d varchar(50), e
varchar(50), ...)
AS BEGIN
INSERT INTO @.myTable(a,b,c,d,e...)
select a,b,c...from tblA --> tblA.a is indexed
return
end
"Tibor Karaszi" wrote:

> there are two types of UDFs:
> Inline. Consists of only one query. Think of it as a view or "macro".
> Multi-statement. Here you define a table variable and populate that table
variable, and when
> function code exist at run-time the data is selected from the variable, Al
l that work is overhead.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul fpvt2" <Paulfpvt2@.discussions.microsoft.com> wrote in message
> news:B7BABDF8-B1A1-4735-BBFA-5A250B92B1F6@.microsoft.com...
>|||No, that is a multi-statement UDF. An Inline is:
CREATE FUNCTION f(...)
RETURNS TABLE
AS
RETURN (SELECT ...)
For above, SQL Server doesn't have to populate a table variable and then ret
urn the result. SQL
Server can, and will "inline" above query in the outer query, just like it d
oes with a view. Or
think of it as a macro, if you wish.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul fpvt2" <Paulfpvt2@.discussions.microsoft.com> wrote in message
news:D30F1F9A-E3D1-412A-B9D5-30A0F265B8C8@.microsoft.com...
> Thanks.
> Is the following UDF considered inline (because it only has 1 query: selec
t
> a,b,c...) ?
> CREATE function dbo.udfMyProc()
> returns @.myTable TABLE(a int, b bit, c varchar(50), d varchar(50), e
> varchar(50), ...)
> AS BEGIN
> INSERT INTO @.myTable(a,b,c,d,e...)
> select a,b,c...from tblA --> tblA.a is indexed
> return
> end
> "Tibor Karaszi" wrote:
>|||Thanks.
In the sample query that you posted, does it mean that you return a table
that is populated with the select statement ?
"Tibor Karaszi" wrote:

> No, that is a multi-statement UDF. An Inline is:
> CREATE FUNCTION f(...)
> RETURNS TABLE
> AS
> RETURN (SELECT ...)
> For above, SQL Server doesn't have to populate a table variable and then r
eturn the result. SQL
> Server can, and will "inline" above query in the outer query, just like it
does with a view. Or
> think of it as a macro, if you wish.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul fpvt2" <Paulfpvt2@.discussions.microsoft.com> wrote in message
> news:D30F1F9A-E3D1-412A-B9D5-30A0F265B8C8@.microsoft.com...
>