2005 Table function with index?  
Author Message
Bike





PostPosted: Thu Mar 15 11:12:33 CDT 2007 Top

SQL Server Developer >> 2005 Table function with index?

Can a table function have an index or a primary key that contains 2
columns in SQL 2005?

SQL Server222  
 
 
Tibor





PostPosted: Thu Mar 15 11:12:33 CDT 2007 Top

SQL Server Developer >> 2005 Table function with index? Yes, a PK. (I think that was possible in 2000 as well, but I'm too lazy to test right now... ;-) ):

CREATE FUNCTION fn()

AS
BEGIN
RETURN
END

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi




> Can a table function have an index or a primary key that contains 2
> columns in SQL 2005?
>

 
 
Gail





PostPosted: Thu Mar 15 12:04:46 CDT 2007 Top

SQL Server Developer >> 2005 Table function with index? > Can a table function have an index or a primary key that contains 2
> columns in SQL 2005?


The CREATE FUNCTION topic in SQL Server 2005 Books Online was extensively
revised to include all the contstraint and index syntax. You'll see in the
table_constraint section shown below that multiple columns can be specified
as indicated by the [ ,...n ] convention. In addition, multiple index
options can be specified. Multiple columns in the constraint also applies
to SQL Server 2000.

<table_constraint>::=
{
{ PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
( column_name [ ASC | DESC ] [ ,...n ] )
[ WITH FILLFACTOR = fillfactor
| WITH ( <index_option> [ , ...n ] )
| [ CHECK ( logical_expression ) ] [ ,...n ]
}

<index_option>::=
{
PAD_INDEX = { ON | OFF }
| FILLFACTOR = fillfactor
| IGNORE_DUP_KEY = { ON | OFF }
| STATISTICS_NORECOMPUTE = { ON | OFF }
| ALLOW_ROW_LOCKS = { ON | OFF }
| ALLOW_PAGE_LOCKS ={ ON | OFF }
}

Regards,
Gail
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx




> Can a table function have an index or a primary key that contains 2
> columns in SQL 2005?
>


 
 
Tom





PostPosted: Thu Mar 15 12:32:37 CDT 2007 Top

SQL Server Developer >> 2005 Table function with index? Yes, you can also do that in 2000.

Tom



> Yes, a PK. (I think that was possible in 2000 as well, but I'm too lazy to
> test right now... ;-) ):
>
> CREATE FUNCTION fn()

> AS
> BEGIN
> RETURN
> END
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
>


>> Can a table function have an index or a primary key that contains 2
>> columns in SQL 2005?
>>
>


 
 
jbyrd





PostPosted: Thu Mar 15 13:58:41 CDT 2007 Top

SQL Server Developer >> 2005 Table function with index? So do the index options only come into play when I have a Primary
Key? If there is no primary key, there can't be an index on the table
function, correct?

 
 
Gail





PostPosted: Thu Mar 15 14:41:10 CDT 2007 Top

SQL Server Developer >> 2005 Table function with index? > So do the index options only come into play when I have a Primary
> Key? If there is no primary key, there can't be an index on the table
> function, correct?

Nope. You can create both a PRIMARY or UNIQUE constraint on the TVF. Either
of these will create an index (clustered or nonclustered). Obviously, only
one of these constraints can be clustered.

--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx


> So do the index options only come into play when I have a Primary
> Key? If there is no primary key, there can't be an index on the table
> function, correct?
>