Can you create non-unique Indexes on UDF returned tables?

[JNeave]

I can create PKs and Unique Clustered and Non-clustered indexes on multi line udf's

But I'd like to create a non-unique clustered index for the table that is returned as it is then joined to several other tables.

I can't do:
CREATE CLUSTERED INDEX ON tblOut (ColumnName)

it throw invalid use of CREATE INDEX in a function

and I can only add unique and primary key contraints to the table definition.

Is it therefore impossible?

Cheers,

Jim.

 

[tlbroadbent]

One of the limitations of the table variable is the inability to add indexes. This is one area where temporary tables still have an advatage over table variables.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[JNeave]

Temporary tables which can't be used in UDFs right? I tried that first and it complained.



Ta,

Jim.

 

[SQLSister]

Yes but you could try putting the results of the udf into a temp table and setting the index on that.

 

[tlbroadbent]

True. Temp tables cannot be created or updated in a UDF. You could insert the result of the function into a temporary table and add appropriate indexes. This may or may not be as efficient as using the function in a query.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[JNeave]

Well, according to the true Execution plan in Query analyzer, the udf generated table is used on the bottom half of a nested loop join, even though that is a fairly bad decision. If I could get a clustered index on the udf table or temp table, it would use one of those good joins, not hash, not nested loop, the other one, Merge is it?

Well, I'll try the INSERT INTO EXEC thing then, thanks guys..

Jim.