Dynamic tables in sql

Question

Is there any method for creating dynamic tables in sql server 2000?

Answer 1

You can create temporary tables by prefixing them with an octothorp (#), or you can use table variables which are prefixed with the @ symbol.

create table #tempTable (col1 char(1)) -- Temporary table

declare @tempTableVariable table (col1 char(1)) -- Table variable

From http://www.sqlteam.com/article/temporary-tables

• If you have less than 100 rows generally use a table variable. Otherwise use a temporary table. This is because SQL Server won't create statistics on table variables.
• If you need to create indexes on it then you must use a temporary table.
• When using temporary tables always create them and create any indexes and then use them. This will help reduce recompilations. The impact of this is reduced starting in SQL Server 2005 but it's still a good idea.

Answer 2

Here is an example of a user defined function that returns a table variable :

CREATE FUNCTION getDynamicTable () 
RETURNS     
    @output table (
        id int identity,
        value nvarchar(50)
    )
AS
BEGIN

    insert into @output (value) 
    values ('test 1')

    insert into @output (value) 
    values ('test 2')

    return
END

Hope this helps