[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

2005 Table function with index?

jbyrd

3/15/2007 2:44:00 PM

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

Tibor Karaszi

3/15/2007 4:13:00 PM

0

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

CREATE FUNCTION fn()
RETURNS @a TABLE (c1 int, c2 int PRIMARY KEY(c1, c2))
AS
BEGIN
RETURN
END

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/d...
http://sqlblog.com/blogs/tib...


"jbyrd" <jayloub@comcast.net> wrote in message
news:1173969820.852793.30010@y80g2000hsf.googlegroups.com...
> Can a table function have an index or a primary key that contains 2
> columns in SQL 2005?
>

Gail Erickson [MS]

3/15/2007 5:05:00 PM

0

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


"jbyrd" <jayloub@comcast.net> wrote in message
news:1173969820.852793.30010@y80g2000hsf.googlegroups.com...
> Can a table function have an index or a primary key that contains 2
> columns in SQL 2005?
>


Tom Cooper

3/15/2007 5:33:00 PM

0

Yes, you can also do that in 2000.

Tom

"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in
message news:un765yxZHHA.4808@TK2MSFTNGP04.phx.gbl...
> Yes, a PK. (I think that was possible in 2000 as well, but I'm too lazy to
> test right now... ;-) ):
>
> CREATE FUNCTION fn()
> RETURNS @a TABLE (c1 int, c2 int PRIMARY KEY(c1, c2))
> AS
> BEGIN
> RETURN
> END
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/d...
> http://sqlblog.com/blogs/tib...
>
>
> "jbyrd" <jayloub@comcast.net> wrote in message
> news:1173969820.852793.30010@y80g2000hsf.googlegroups.com...
>> Can a table function have an index or a primary key that contains 2
>> columns in SQL 2005?
>>
>


jbyrd

3/15/2007 6:59:00 PM

0

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 Erickson [MS]

3/15/2007 7:41:00 PM

0

> 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/...
"jbyrd" <jayloub@comcast.net> wrote in message
news:1173985121.872483.228440@o5g2000hsb.googlegroups.com...
> 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?
>


jat

6/19/2011 2:14:00 PM

0

On 06/19/2011 10:38 AM, TORREBLANCA? wrote:
> On 19 jun, 10:25, jat<j...@none.null> wrote:
>> On 06/19/2011 09:34 AM, ljsprojects wrote:>http://alizeebhg....
>>
>>> T.Schmidt
>>
>> N I C E ! ;-)
>>
>
>
> mamaguebo !!!!
Negative! - You cock-eater and sucker, too

--
/jat
Knowledge shall make you free
El conocimiento te har? libre

jat

6/19/2011 2:52:00 PM

0

On 6/19/2011 11:11 AM, ljsprojects wrote:
> On Jun 19, 9:08 am, TORREBLANCA?<donquicove...@gmail.com> wrote:
>> On 19 jun, 10:25, jat<j...@none.null> wrote:
>>
>>> On 06/19/2011 09:34 AM, ljsprojects wrote:>http://alizeebhg....
>>
>>>> T.Schmidt
>>
>>> N I C E ! ;-)
>>
>> mamaguebo !!!!
>
> Torreblanca se esta contangiando con Paul Lamot.
>
> T.Schmidt

Esos son un par de amargados...
--
/jat
Knowledge shall make you free
El conocimiento te har? libre

jat

6/19/2011 2:59:00 PM

0

On 6/19/2011 11:26 AM, PL wrote:
> On 19/06/2011 17:51, jat wrote:
>> On 6/19/2011 11:11 AM, ljsprojects wrote:
>>> On Jun 19, 9:08 am, TORREBLANCA?<donquicove...@gmail.com> wrote:
>>>> On 19 jun, 10:25, jat<j...@none.null> wrote:
>>>>
>>>>> On 06/19/2011 09:34 AM, ljsprojects wrote:>http://alizeebhg....
>>>>
>>>>>> T.Schmidt
>>>>
>>>>> N I C E ! ;-)
>>>>
>>>> mamaguebo !!!!
>>>
>>> *********** se esta contangiando con ******.
>>>
>>> T.Schmidt
>>
>> Esos son un par de amargados...
>
> Insultos no cambian nada a la realidad Jesusita.
> Y si llegan de un ^par de pendejos vulgares como tu y Teddy Schmidt
> hasta menos.
> :-)

No hables paja, PL y disfruta el v?deo...
--
/jat
Knowledge shall make you free
El conocimiento te har? libre

jat

6/19/2011 3:18:00 PM

0

On 6/19/2011 11:30 AM, PL wrote:
> On 19/06/2011 17:58, jat wrote:
>> On 6/19/2011 11:26 AM, PL wrote:
>>> On 19/06/2011 17:51, jat wrote:
>>>> On 6/19/2011 11:11 AM, ljsprojects wrote:
>>>>> On Jun 19, 9:08 am, TORREBLANCA?<donquicove...@gmail.com> wrote:
>>>>>> On 19 jun, 10:25, jat<j...@none.null> wrote:
>>>>>>
>>>>>>> On 06/19/2011 09:34 AM, ljsprojects
>>>>>>> wrote:>http://alizeebhg....
>>>>>>
>>>>>>>> T.Schmidt
>>>>>>
>>>>>>> N I C E ! ;-)
>>>>>>
>>>>>> mamaguebo !!!!
>>>>>
>>>>> *********** se esta contangiando con ******.
>>>>>
>>>>> T.Schmidt
>>>>
>>>> Esos son un par de amargados...
>>>
>>> Insultos no cambian nada a la realidad Jesusita.
>>> Y si llegan de un ^par de pendejos vulgares como tu y Teddy Schmidt
>>> hasta menos.
>>> :-)
>>
>> No hables paja,
>
> El que habla "paja" eres tu.
> Muestras de nuevo que eres nada mas que un seguidor vulgar.
> Un nada.
>
Come on men ! Fuck off !

--
/jat
Knowledge shall make you free
El conocimiento te har? libre

jat

6/19/2011 3:19:00 PM

0

On 6/19/2011 11:30 AM, PL wrote:
> On 19/06/2011 17:58, jat wrote:
>> On 6/19/2011 11:26 AM, PL wrote:
>>> On 19/06/2011 17:51, jat wrote:
>>>> On 6/19/2011 11:11 AM, ljsprojects wrote:
>>>>> On Jun 19, 9:08 am, TORREBLANCA?<donquicove...@gmail.com> wrote:
>>>>>> On 19 jun, 10:25, jat<j...@none.null> wrote:
>>>>>>
>>>>>>> On 06/19/2011 09:34 AM, ljsprojects
>>>>>>> wrote:>http://alizeebhg....
>>>>>>
>>>>>>>> T.Schmidt
>>>>>>
>>>>>>> N I C E ! ;-)
>>>>>>
>>>>>> mamaguebo !!!!
>>>>>
>>>>> *********** se esta contangiando con ******.
>>>>>
>>>>> T.Schmidt
>>>>
>>>> Esos son un par de amargados...
>>>
>>> Insultos no cambian nada a la realidad Jesusita.
>>> Y si llegan de un ^par de pendejos vulgares como tu y Teddy Schmidt
>>> hasta menos.
>>> :-)
>>
>> No hables paja,
>
> El que habla "paja" eres tu.
> Muestras de nuevo que eres nada mas que un seguidor vulgar.
> Un nada.
>
Come on man ! Fuck off !

--
/jat
Knowledge shall make you free
El conocimiento te har? libre