RE: Creating an Index for Temporary table returned by Functions

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: dance2die (dance2die_at_discussions.microsoft.com)
Date: 11/09/04 

Date: Tue, 9 Nov 2004 13:03:03 -0800

Thanks for the help guys.
I have decided to go with a temporary table instead of using an UDF.
I have tried, both Temp tables and Dynamic SQL.
While Temp table solution seemed to be working, Dynamic SQL didn't in my case.

But it seems like I have learned a lesson that a table isn't necessarily
should be called a table without a primary key.

"dance2die" wrote:

> I have a function returning a table.
> ==========
> CREATE FUNCTION fnExample()
> RETURNS @exTable TABLE (
> f1 char(8)
> , f2 datetime
> , f3 integer
> )
> AS
> BEGIN
> -- MAIN
> -- Fill @exTable...
> RETURN
> END
> ==========
>
> Rows returned from the @exTable is not UNIQUE. therefore I cannot use UNIQUE
> or PRIMARY KEY constraints to implicitly creating indexes on any of fields.
>
> When I tried
> ==========
> CREATE INDEX ix_exTable ON @exTable(f1 ASC, f2, ASC, f3 ASC)
> -- with GO and without GO
> ==========
> in that "--MAIN" section, I received an error(i had to translate the error
> from Korean to English)
>
> "There is an Error near @exTable".
>
> Basically I kept on getting errors on creating indexes before filling up
> @exTable.
> I'd appreciate your kind help.

Relevant Pages

  • Re: RUNNING COUNT Function
    ... I assume there is a primary key somewhere in the table? ... It could turn to be much faster with the RANK syntax, ... WITH temp ... When designed, partial-years werent known about. ...
    (microsoft.public.access.queries)
  • Re: RUNNING COUNT Function
    ... I assume there is a primary key somewhere in the table? ... It could turn to be much faster with the RANK syntax, ... WITH temp ... When designed, partial-years werent known about. ...
    (microsoft.public.access.queries)
  • Re: Deleting Rows in a Many-To-Many Relationship
    ... INT NOT NULL PRIMARY KEY, ... INSERT INTO paretnts VALUES ... GFID INT NOT NULL FOREIGN KEY REFERENCES paretnts ON DELETE CASCADE ... > 1) Create a temp table and copy the rows from B to the temp table that ...
    (microsoft.public.sqlserver.programming)
  • Re: RUNNING COUNT Function
    ... I assume there is a primary key somewhere in the table? ... It could turn to be much faster with the RANK syntax, ... WITH temp ... When designed, partial-years werent known about. ...
    (microsoft.public.access.queries)
  • Re: Can I use a table variable in sp_executesql for output
    ... First of all that is a table variable and not a temp table. ... tables that are created inside dynamic sql are limited to that execution ... Once they are done the code outside of that dynamic batch will not ... Andrew J. Kelly SQL MVP "thejamie" wrote in message ...
    (microsoft.public.sqlserver.connect)