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...
>

can a query be written to do this...

I have the following table defined: Not my design/idea and I can't change
it)
CREATE TABLE [dbo].[Task] (
[TaskID] [ROWIDENTIFIER] NOT NULL ,
[Name] [SHORTNAME] NULL ,
[Description] [SHORTDESCRIPTION] NULL ,
[PreviousTaskID] [ROWIDENTIFIER] NULL ,
[NextTaskID] [ROWIDENTIFIER] NULL ,
[ProcedureID] [ROWIDENTIFIER] NOT NULL ,
[IsActive] [WFBOOL] NULL
) ON [PRIMARY]
PreviousTaskID and NextTaskID form, what amounts to a linked list where
PreviousTaskID points to the TaskID that comes before the current task and
NextTaskID points to the TaskID of the task that follows the current task.
A PreviousTaskID equal to null signifies the first task in a list and a
NextTaskID equal to null signifies it is the last task in the list.
With all of that in mind: Is there any way to write a query that will return
a single set of rows ordered from first to last?
TIA
Brian WBW -
It seems unnecessary to have a Next and Previous so long as the chain is
always 1 for 1 (i.e. Task 2 always comes after Task 1, etc). Anyways, here'
s
something that should get you started:
create table #Task (
TaskID int not null
, [Name] varchar(50) null
, PreviousTaskID int null
, NextTaskID int null
)
insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(1,
'T1', null, 2)
insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(2,
'T1', 1, 3)
insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(3,
'T1', 2, 4)
insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(4,
'T1', 3, 5)
insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(5,
'T1', 4, 6)
insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(6,
'T1', 5, null)
declare @.parent_level int
set @.parent_level = 0
declare @.hierarchy table (parent int, item int, [level] int)
insert into @.hierarchy (parent, item, [level])
select null, taskid, 0
from #Task
where previoustaskid is null
while 1 = 1
begin
insert into @.hierarchy(parent, item, [level])
select nexttaskid, taskid, @.parent_level + 1
from #Task
where previoustaskid in (select item from @.hierarchy where [level] =
@.parent_level)
if @.@.rowcount = 0
break
set @.parent_level = @.parent_level + 1
end
select t.TaskID, h.[level] as Ordering
from #Task t
join @.hierarchy h on t.TaskID = h.item
order by 2 asc|||Perfect!
muchos gracias!
"Cris_Benge" <CrisBenge@.discussions.microsoft.com> wrote in message
news:C3096324-4209-4868-80AB-FA4FA7DB9B97@.microsoft.com...
> BW -
> It seems unnecessary to have a Next and Previous so long as the chain is
> always 1 for 1 (i.e. Task 2 always comes after Task 1, etc). Anyways,
here's
> something that should get you started:
> create table #Task (
> TaskID int not null
> , [Name] varchar(50) null
> , PreviousTaskID int null
> , NextTaskID int null
> )
> insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(1,
> 'T1', null, 2)
> insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(2,
> 'T1', 1, 3)
> insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(3,
> 'T1', 2, 4)
> insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(4,
> 'T1', 3, 5)
> insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(5,
> 'T1', 4, 6)
> insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(6,
> 'T1', 5, null)
> declare @.parent_level int
> set @.parent_level = 0
> declare @.hierarchy table (parent int, item int, [level] int)
> insert into @.hierarchy (parent, item, [level])
> select null, taskid, 0
> from #Task
> where previoustaskid is null
> while 1 = 1
> begin
> insert into @.hierarchy(parent, item, [level])
> select nexttaskid, taskid, @.parent_level + 1
> from #Task
> where previoustaskid in (select item from @.hierarchy where [level] =
> @.parent_level)
> if @.@.rowcount = 0
> break
> set @.parent_level = @.parent_level + 1
> end
> select t.TaskID, h.[level] as Ordering
> from #Task t
> join @.hierarchy h on t.TaskID = h.item
> order by 2 asc
>

Can a Domain Group be set as the dbo to a database?

To keep the Login list more manageable and keep names out of it can I set a
AD Domain Group as the dbo to a database?
This would also help cover the cases when someone goes on vacation and a
different member of the group covers for them.You can add the group to Database Role db_owner.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Jim Abel" wrote:

> To keep the Login list more manageable and keep names out of it can I set
a
> AD Domain Group as the dbo to a database?
> This would also help cover the cases when someone goes on vacation and a
> different member of the group covers for them.

Calling user-defined function without dbo. -- possible?

Is it possible to call a user-defined function without prefixing it
with 'dbo.' within a SELECT clause somehow? Just curious; it's not a
big issue but just a stylistic one for me.

Thanks!

Joel Thornton ~ <groups@.joelpt.eml.cc>You can do so with table function. However, you will have to specify an owner if
you're calling a scalar function. This is to allow sqlserver to distinguish an
udf as opposed to system function.

See if this helps:

create function dbo.scalar()
returns int
as
begin
return(select 123)
end
go
create function dbo.tb()
returns table
as
return(select top 5 * from Northwind..Orders)
go
select 'bad:'+cast(scalar() as varchar)
go
select 'good:'+cast(dbo.scalar() as varchar)
go
select * from tb()
go
select * from dbo.tb()
go
drop function dbo.tb,dbo.scalar
go

--
-oj
http://www.rac4sql.net

"Joel Thornton" <joelpt@.eml.cc> wrote in message
news:c190a45a.0401091144.40d9f8de@.posting.google.c om...
> Is it possible to call a user-defined function without prefixing it
> with 'dbo.' within a SELECT clause somehow? Just curious; it's not a
> big issue but just a stylistic one for me.
> Thanks!
> Joel Thornton ~ <groups@.joelpt.eml.cc

Calling User Defined Function in VC++ using SQLDMO

Hi all,
I had a user defined function by name dbo.GetContact (dbo is
owner),the function contains a select statement and returns a value and
i am using this function in a View. when i calling this function in
Query Analyzer it is working fine... but when i call this i use View
programtically,it is telling that "InValid Object dbo.GetContact".
View :
select dbo.getcontact('Address', '_332', 'Email')
Can anyone provide me the solution...
Looking forward for ur reply..
Thanz in advance...
Regards,
PrinceHi
Are you calling it in the view the same way or as part of a select statement
against a table?
Please post DDL and DML.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Prince" wrote:

> Hi all,
> I had a user defined function by name dbo.GetContact (dbo is
> owner),the function contains a select statement and returns a value and
> i am using this function in a View. when i calling this function in
> Query Analyzer it is working fine... but when i call this i use View
> programtically,it is telling that "InValid Object dbo.GetContact".
> View :
> select dbo.getcontact('Address', '_332', 'Email')
> Can anyone provide me the solution...
> Looking forward for ur reply..
> Thanz in advance...
> Regards,
> Prince
>|||hai mike..
i am calling it in a view like this :
pDatabase->ExecuteWithResults("that view",&pResults))
Mike Epprecht (SQL MVP) wrote:
> Hi
> Are you calling it in the view the same way or as part of a select stateme
nt
> against a table?
> Please post DDL and DML.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Prince" wrote:
>|||On 23 Aug 2005 02:48:47 -0700, Prince wrote:

>Hi all,
> I had a user defined function by name dbo.GetContact (dbo is
>owner),the function contains a select statement and returns a value and
>i am using this function in a View. when i calling this function in
>Query Analyzer it is working fine... but when i call this i use View
>programtically,it is telling that "InValid Object dbo.GetContact".
>View :
> select dbo.getcontact('Address', '_332', 'Email')
Hi Prince,
Try:
SELECT Column1, Column2, ...
FROM dbo.getcontact('Address', '_332', 'Email')
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Prince, Please try this Visual C++ code. It runs okay for me;
_ConnectionPtr pConn = NULL;
_bstr_t strCon("Provider=SQLOLEDB.1;Persist Security Info=False;User
ID=sa;Password=;Initial Catalog=Enterprise;Data Source=(local)");
HRESULT hr = S_OK;
CoInitialize(NULL);
try
{
hr = pConn.CreateInstance((__uuidof(Connection)));
if (FAILED(hr))
{
cout << "Error instantiating Connection object" << endl;
CoUninitialize();
return 0;
}
//Open the SQL Server connection
hr = pConn->Open(strCon,"","",0);
if (FAILED(hr))
{
cout << "Error opening Database Object using ADO _ConnectionPtr" << endl;
CoUninitialize();
return 0;
}
}
catch (_com_error& ce)
{
cout << "Error= " << ce.ErrorInfo << endl;
}
_RecordsetPtr pRst = NULL;
pRst.CreateInstance(__uuidof(Recordset));
_bstr_t strSQL("select dbo.DBCreationDate('Enterprise')");
VARIANT* ptr = NULL;
IADORecordBinding *picRs = NULL; // Interface Pointer declared
CCustomRs rs;
try {
pRst->Open(strSQL, variant_t((IDispatch *)pConn,true), adOpenStatic,
adLockOptimistic, adCmdText);
if(SUCCEEDED(pRst-> QueryInterface(__uuidof(IADORecordBindin
g), (void
**)&picRs)))
{
TESTHR(picRs->BindToRecordset(&rs));
}
}
catch(_com_error &e)
{
_bstr_t bstrSource(e.Source());
_bstr_t bstrDescription(e.Description());
// Print Com errors.
cout << "Error" <<endl;
cout << "Code = " << e.Error();
cout << "Code meaning = " <<
e.ErrorMessage();
cout << "Source = " << (LPCSTR) bstrSource;
cout << "Description = " << (LPCSTR)
bstrDescription;
}
Please email me at frankchang91@.gmail.com if you have any questions.
"Prince" wrote:

> Hi all,
> I had a user defined function by name dbo.GetContact (dbo is
> owner),the function contains a select statement and returns a value and
> i am using this function in a View. when i calling this function in
> Query Analyzer it is working fine... but when i call this i use View
> programtically,it is telling that "InValid Object dbo.GetContact".
> View :
> select dbo.getcontact('Address', '_332', 'Email')
> Can anyone provide me the solution...
> Looking forward for ur reply..
> Thanz in advance...
> Regards,
> Prince
>|||Prince, I use your user defined function in a view. When i execute the view
programmatically using the SQLOLEDB COM object , it still runs okay. Do you
have to use the SQLDMO COM object or can you switch to the SQLOLEDB COM
object? Thank you.
"frank chang" wrote:
> Prince, Please try this Visual C++ code. It runs okay for me;
> _ConnectionPtr pConn = NULL;
> _bstr_t strCon("Provider=SQLOLEDB.1;Persist Security Info=False;User
> ID=sa;Password=;Initial Catalog=Enterprise;Data Source=(local)");
> HRESULT hr = S_OK;
> CoInitialize(NULL);
> try
> {
> hr = pConn.CreateInstance((__uuidof(Connection)));
> if (FAILED(hr))
> {
> cout << "Error instantiating Connection object" << endl;
> CoUninitialize();
> return 0;
> }
> //Open the SQL Server connection
> hr = pConn->Open(strCon,"","",0);
> if (FAILED(hr))
> {
> cout << "Error opening Database Object using ADO _ConnectionPtr" << end
l;
> CoUninitialize();
> return 0;
> }
> }
> catch (_com_error& ce)
> {
> cout << "Error= " << ce.ErrorInfo << endl;
> }
>
> _RecordsetPtr pRst = NULL;
> pRst.CreateInstance(__uuidof(Recordset));
>
> _bstr_t strSQL("select dbo.DBCreationDate('Enterprise')");
> VARIANT* ptr = NULL;
> IADORecordBinding *picRs = NULL; // Interface Pointer declared
> CCustomRs rs;
> try {
> pRst->Open(strSQL, variant_t((IDispatch *)pConn,true), adOpenStatic,
> adLockOptimistic, adCmdText);
> if(SUCCEEDED(pRst-> QueryInterface(__uuidof(IADORecordBindin
g), (void
> **)&picRs)))
> {
> TESTHR(picRs->BindToRecordset(&rs));
> }
> }
> catch(_com_error &e)
> {
> _bstr_t bstrSource(e.Source());
> _bstr_t bstrDescription(e.Description());
> // Print Com errors.
> cout << "Error" <<endl;
> cout << "Code = " << e.Error();
> cout << "Code meaning = " <<
> e.ErrorMessage();
> cout << "Source = " << (LPCSTR) bstrSource;
> cout << "Description = " << (LPCSTR)
> bstrDescription;
> }
> Please email me at frankchang91@.gmail.com if you have any questions.
> "Prince" wrote:
>