INDEX ON VARIABLE table

I create a variable table using this statement in my UDF ,I'm like to put an
index on of its field (Internal_ID),How can I do it?
DECLARE @.TBEnrollmentsForActiveClients TABLE(ENROLLMENT_ID
NUMERIC(9),COURSE_CODE VARCHAR(3),
COURSE_YEAR NUMERIC(5),COURSE_SESSION NUMERIC(5),
INTERNAL_ID NUMERIC(9))
ThanksYou cannot explicitly create indexes on a table variable, rather only those
created by unique or primary key constraints. If internal_id is unique,
create a unique constraint on it:
DECLARE @.TBEnrollmentsForActiveClients TABLE
(
ENROLLMENT_ID NUMERIC(9),
COURSE_CODE VARCHAR(3),
COURSE_YEAR NUMERIC(5),
COURSE_SESSION NUMERIC(5),
INTERNAL_ID NUMERIC(9) UNIQUE
)
If it isn't, make it the first column of a unique constraint, and include
additional columns that would make it unique.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Ray5531" <RayAll@.microsft.com> wrote in message
news:etd1LM0VFHA.2124@.TK2MSFTNGP14.phx.gbl...
> I create a variable table using this statement in my UDF ,I'm like to put
an
> index on of its field (Internal_ID),How can I do it?
> DECLARE @.TBEnrollmentsForActiveClients TABLE(ENROLLMENT_ID
> NUMERIC(9),COURSE_CODE VARCHAR(3),
> COURSE_YEAR NUMERIC(5),COURSE_SESSION NUMERIC(5),
> INTERNAL_ID NUMERIC(9))
> Thanks
>|||Dose it make a join on internal_ID boosted.The reason I was trying to create
index on it is that I'm joing this varaible table with another table on
internal_id ..
Thanks
"Itzik Ben-Gan" <itzik@.REMOVETHIS.solidqualitylearning.com> wrote in message
news:uCNjDU0VFHA.2492@.TK2MSFTNGP10.phx.gbl...
> You cannot explicitly create indexes on a table variable, rather only
> those
> created by unique or primary key constraints. If internal_id is unique,
> create a unique constraint on it:
> DECLARE @.TBEnrollmentsForActiveClients TABLE
> (
> ENROLLMENT_ID NUMERIC(9),
> COURSE_CODE VARCHAR(3),
> COURSE_YEAR NUMERIC(5),
> COURSE_SESSION NUMERIC(5),
> INTERNAL_ID NUMERIC(9) UNIQUE
> )
> If it isn't, make it the first column of a unique constraint, and include
> additional columns that would make it unique.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Ray5531" <RayAll@.microsft.com> wrote in message
> news:etd1LM0VFHA.2124@.TK2MSFTNGP14.phx.gbl...
> an
>|||Actually ENROLLMENT_ID is unique so I can 100% garanty that combination of
ENROLLMENT_ID and INTERNAL_ID is also unique,how can I say this in code
below?
Thanks
"Itzik Ben-Gan" <itzik@.REMOVETHIS.solidqualitylearning.com> wrote in message
news:uCNjDU0VFHA.2492@.TK2MSFTNGP10.phx.gbl...
> You cannot explicitly create indexes on a table variable, rather only
> those
> created by unique or primary key constraints. If internal_id is unique,
> create a unique constraint on it:
> DECLARE @.TBEnrollmentsForActiveClients TABLE
> (
> ENROLLMENT_ID NUMERIC(9),
> COURSE_CODE VARCHAR(3),
> COURSE_YEAR NUMERIC(5),
> COURSE_SESSION NUMERIC(5),
> INTERNAL_ID NUMERIC(9) UNIQUE
> )
> If it isn't, make it the first column of a unique constraint, and include
> additional columns that would make it unique.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Ray5531" <RayAll@.microsft.com> wrote in message
> news:etd1LM0VFHA.2124@.TK2MSFTNGP14.phx.gbl...
> an
>|||Just make both part of the unique constraint or primary key:
DECLARE @.TBEnrollmentsForActiveClients TABLE
(
ENROLLMENT_ID NUMERIC(9),
COURSE_CODE VARCHAR(3),
COURSE_YEAR NUMERIC(5),
COURSE_SESSION NUMERIC(5),
INTERNAL_ID NUMERIC(9),
UNIQUE(ENROLLMENT_ID, INTERNAL_ID)
)
or
DECLARE @.TBEnrollmentsForActiveClients TABLE
(
ENROLLMENT_ID NUMERIC(9),
COURSE_CODE VARCHAR(3),
COURSE_YEAR NUMERIC(5),
COURSE_SESSION NUMERIC(5),
INTERNAL_ID NUMERIC(9),
PRIMARY KEY(ENROLLMENT_ID, INTERNAL_ID)
)
BG, SQL Server MVP
www.SolidQualityLearning.com
"Ray5531" <RayAll@.microsft.com> wrote in message
news:#BGDjd0VFHA.2700@.TK2MSFTNGP12.phx.gbl...
> Actually ENROLLMENT_ID is unique so I can 100% garanty that combination
of
> ENROLLMENT_ID and INTERNAL_ID is also unique,how can I say this in code
> below?
> Thanks
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.solidqualitylearning.com> wrote in
message
> news:uCNjDU0VFHA.2492@.TK2MSFTNGP10.phx.gbl...
include
put
>|||Sure can.
Just keep in mind that the optimizer doesn't maintain statistics for table
variables like it does for regular and temp tables. So make sure you
benchmark solutions with temp tables as well, comparing the performence with
table variables.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Ray5531" <RayAll@.microsft.com> wrote in message
news:#hu8xY0VFHA.1044@.TK2MSFTNGP10.phx.gbl...
> Dose it make a join on internal_ID boosted.The reason I was trying to
create
> index on it is that I'm joing this varaible table with another table on
> internal_id ..
> Thanks
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.solidqualitylearning.com> wrote in
message
> news:uCNjDU0VFHA.2492@.TK2MSFTNGP10.phx.gbl...
include
put
>|||Thanks
"Itzik Ben-Gan" <itzik@.REMOVETHIS.solidqualitylearning.com> wrote in message
news:uwkDwU1VFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Just make both part of the unique constraint or primary key:
> DECLARE @.TBEnrollmentsForActiveClients TABLE
> (
> ENROLLMENT_ID NUMERIC(9),
> COURSE_CODE VARCHAR(3),
> COURSE_YEAR NUMERIC(5),
> COURSE_SESSION NUMERIC(5),
> INTERNAL_ID NUMERIC(9),
> UNIQUE(ENROLLMENT_ID, INTERNAL_ID)
> )
> or
> DECLARE @.TBEnrollmentsForActiveClients TABLE
> (
> ENROLLMENT_ID NUMERIC(9),
> COURSE_CODE VARCHAR(3),
> COURSE_YEAR NUMERIC(5),
> COURSE_SESSION NUMERIC(5),
> INTERNAL_ID NUMERIC(9),
> PRIMARY KEY(ENROLLMENT_ID, INTERNAL_ID)
> )
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Ray5531" <RayAll@.microsft.com> wrote in message
> news:#BGDjd0VFHA.2700@.TK2MSFTNGP12.phx.gbl...
> of
> message
> include
> put
>|||I know that sql server hugely uses statistics to execute queries iin the
best way,but I just wondred how it uses statistcs and what are they? why we
should update them with fullscan sometimes?
Thanks
"Itzik Ben-Gan" <itzik@.REMOVETHIS.solidqualitylearning.com> wrote in message
news:eeEcYV1VFHA.2572@.TK2MSFTNGP14.phx.gbl...
> Sure can.
> Just keep in mind that the optimizer doesn't maintain statistics for table
> variables like it does for regular and temp tables. So make sure you
> benchmark solutions with temp tables as well, comparing the performence
> with
> table variables.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Ray5531" <RayAll@.microsft.com> wrote in message
> news:#hu8xY0VFHA.1044@.TK2MSFTNGP10.phx.gbl...
> create
> message
> include
> put
>|||J-R, try this whitepaper:
http://support.microsoft.com/defaul...kb;en-us;322096
BG, SQL Server MVP
www.SolidQualityLearning.com
"J-R" <RayAll@.microsft.com> wrote in message
news:OeCMv01VFHA.2960@.TK2MSFTNGP15.phx.gbl...
> I know that sql server hugely uses statistics to execute queries iin the
> best way,but I just wondred how it uses statistcs and what are they? why
we
> should update them with fullscan sometimes?
> Thanks
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.solidqualitylearning.com> wrote in
message
> news:eeEcYV1VFHA.2572@.TK2MSFTNGP14.phx.gbl...
table
unique,
to
>