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
>
Showing posts with label variable. Show all posts
Showing posts with label variable. Show all posts
Wednesday, March 21, 2012
index on table variable
Hi guys,
Can we put Index on table type variable like if we create a
table type variable by this:
DECLARE @.Results TABLE (
[rownum] [int] IDENTITY (1, 1) PRIMARY KEY NOT NULL,
[PlanID] BIGINT,
PlanPriceID BIGINT,
PricePerMonth MONEY,
ConnectionFee MONEY,
ConnectionFeeWModem MONEY,
Priority INT
)
after creating this I put index on @.Results
Like...
CREATE INDEX plan_ind
ON @.Results (PlanID)
It is giving Error, can't we create index on that
ManishHi
You can not create an index on a table variable, in general if you are using
a table variable to warrant an index, it may be better to use a temporary
table instead.
John
"Manish Sukhija" wrote:
> Hi guys,
> Can we put Index on table type variable like if we create a
> table type variable by this:
> DECLARE @.Results TABLE (
> [rownum] [int] IDENTITY (1, 1) PRIMARY KEY NOT NULL,
> [PlanID] BIGINT,
> PlanPriceID BIGINT,
> PricePerMonth MONEY,
> ConnectionFee MONEY,
> ConnectionFeeWModem MONEY,
> Priority INT
> )
> after creating this I put index on @.Results
> Like...
> CREATE INDEX plan_ind
> ON @.Results (PlanID)
> It is giving Error, can't we create index on that
> Manish
>|||Hi John,
If i create temp table instead of table type type variable, will
it make any difference on code, i mean we have to drop temp table after
completion of work but we are need not to do this work in table variable.
bye...
"John Bell" wrote:
> Hi
> You can not create an index on a table variable, in general if you are usi
ng
> a table variable to warrant an index, it may be better to use a temporary
> table instead.
> John
> "Manish Sukhija" wrote:
>|||Well,
There is no other difference in terms of code other than you have to drop
the temp table. But, outside the deveopers point of view. Teble variables ar
e
in memory tables but temp tables are stored in temp db. But again table
variables can be stored in temp db if there is no sufficient memory. you can
use local temp tables in peace if this particular proc where you are using
doesn't have high concurrency.
But, just a question, why do you need a primary key on rownum?
and on plan id. If you can give a little more insight on what your
requirement is, then may be we can get it done with the table variables only
.|||Hi
Temporary tables will be automatically dropped when they go out of scope,
although you may want to explicitly drop them. See the information on
temporary tables in the Books Online topic "CREATE TABLE"
Make sure that you declare the temporary table at the start of a stored
procedure to help avoid re-compilation.
John
"Manish Sukhija" wrote:
> Hi John,
> If i create temp table instead of table type type variable, wil
l
> it make any difference on code, i mean we have to drop temp table after
> completion of work but we are need not to do this work in table variable.
> bye...
> "John Bell" wrote:
>|||You can put indexes on table variables by using part of the constriant
syntax, see below...
DECLARE @.Results TABLE (
[rownum] [int] IDENTITY (1, 1) NOT NULL,
[PlanID] BIGINT null unique ( planid, rownum ),
PlanPriceID BIGINT,
PricePerMonth MONEY,
ConnectionFee MONEY,
ConnectionFeeWModem MONEY,
Priority INT
)
select *
from @.results
where planid = 1245
The above does an index s
because i have put a unique constraint on
planid, rownum.
You can't specify the index in an hint and there aren't any statistics for
the optimiser to use so don't assume it will be used etc... you are probably
better using # tables if you are going to start indexing stuff because of
performance; table variables are really good as a solution to stop plan
recompilation compared to # tables.
Tony
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Manish Sukhija" <ManishSukhija@.discussions.microsoft.com> wrote in message
news:22D81225-08F7-4483-9442-7839BF26F02B@.microsoft.com...
> Hi guys,
> Can we put Index on table type variable like if we create a
> table type variable by this:
> DECLARE @.Results TABLE (
> [rownum] [int] IDENTITY (1, 1) PRIMARY KEY NOT NULL,
> [PlanID] BIGINT,
> PlanPriceID BIGINT,
> PricePerMonth MONEY,
> ConnectionFee MONEY,
> ConnectionFeeWModem MONEY,
> Priority INT
> )
> after creating this I put index on @.Results
> Like...
> CREATE INDEX plan_ind
> ON @.Results (PlanID)
> It is giving Error, can't we create index on that
> Manish
>|||> If i create temp table instead of table type type variable, will
> it make any difference on code,
http://www.aspfaq.com/2475
> i mean we have to drop temp table after
> completion of work but we are need not to do this work in table variable.
Well, no, technically you do not have to drop a table variable, but in any
case, if this is your idea of "extra work"...
Can we put Index on table type variable like if we create a
table type variable by this:
DECLARE @.Results TABLE (
[rownum] [int] IDENTITY (1, 1) PRIMARY KEY NOT NULL,
[PlanID] BIGINT,
PlanPriceID BIGINT,
PricePerMonth MONEY,
ConnectionFee MONEY,
ConnectionFeeWModem MONEY,
Priority INT
)
after creating this I put index on @.Results
Like...
CREATE INDEX plan_ind
ON @.Results (PlanID)
It is giving Error, can't we create index on that
ManishHi
You can not create an index on a table variable, in general if you are using
a table variable to warrant an index, it may be better to use a temporary
table instead.
John
"Manish Sukhija" wrote:
> Hi guys,
> Can we put Index on table type variable like if we create a
> table type variable by this:
> DECLARE @.Results TABLE (
> [rownum] [int] IDENTITY (1, 1) PRIMARY KEY NOT NULL,
> [PlanID] BIGINT,
> PlanPriceID BIGINT,
> PricePerMonth MONEY,
> ConnectionFee MONEY,
> ConnectionFeeWModem MONEY,
> Priority INT
> )
> after creating this I put index on @.Results
> Like...
> CREATE INDEX plan_ind
> ON @.Results (PlanID)
> It is giving Error, can't we create index on that
> Manish
>|||Hi John,
If i create temp table instead of table type type variable, will
it make any difference on code, i mean we have to drop temp table after
completion of work but we are need not to do this work in table variable.
bye...
"John Bell" wrote:
> Hi
> You can not create an index on a table variable, in general if you are usi
ng
> a table variable to warrant an index, it may be better to use a temporary
> table instead.
> John
> "Manish Sukhija" wrote:
>|||Well,
There is no other difference in terms of code other than you have to drop
the temp table. But, outside the deveopers point of view. Teble variables ar
e
in memory tables but temp tables are stored in temp db. But again table
variables can be stored in temp db if there is no sufficient memory. you can
use local temp tables in peace if this particular proc where you are using
doesn't have high concurrency.
But, just a question, why do you need a primary key on rownum?
and on plan id. If you can give a little more insight on what your
requirement is, then may be we can get it done with the table variables only
.|||Hi
Temporary tables will be automatically dropped when they go out of scope,
although you may want to explicitly drop them. See the information on
temporary tables in the Books Online topic "CREATE TABLE"
Make sure that you declare the temporary table at the start of a stored
procedure to help avoid re-compilation.
John
"Manish Sukhija" wrote:
> Hi John,
> If i create temp table instead of table type type variable, wil
l
> it make any difference on code, i mean we have to drop temp table after
> completion of work but we are need not to do this work in table variable.
> bye...
> "John Bell" wrote:
>|||You can put indexes on table variables by using part of the constriant
syntax, see below...
DECLARE @.Results TABLE (
[rownum] [int] IDENTITY (1, 1) NOT NULL,
[PlanID] BIGINT null unique ( planid, rownum ),
PlanPriceID BIGINT,
PricePerMonth MONEY,
ConnectionFee MONEY,
ConnectionFeeWModem MONEY,
Priority INT
)
select *
from @.results
where planid = 1245
The above does an index s
because i have put a unique constraint onplanid, rownum.
You can't specify the index in an hint and there aren't any statistics for
the optimiser to use so don't assume it will be used etc... you are probably
better using # tables if you are going to start indexing stuff because of
performance; table variables are really good as a solution to stop plan
recompilation compared to # tables.
Tony
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Manish Sukhija" <ManishSukhija@.discussions.microsoft.com> wrote in message
news:22D81225-08F7-4483-9442-7839BF26F02B@.microsoft.com...
> Hi guys,
> Can we put Index on table type variable like if we create a
> table type variable by this:
> DECLARE @.Results TABLE (
> [rownum] [int] IDENTITY (1, 1) PRIMARY KEY NOT NULL,
> [PlanID] BIGINT,
> PlanPriceID BIGINT,
> PricePerMonth MONEY,
> ConnectionFee MONEY,
> ConnectionFeeWModem MONEY,
> Priority INT
> )
> after creating this I put index on @.Results
> Like...
> CREATE INDEX plan_ind
> ON @.Results (PlanID)
> It is giving Error, can't we create index on that
> Manish
>|||> If i create temp table instead of table type type variable, will
> it make any difference on code,
http://www.aspfaq.com/2475
> i mean we have to drop temp table after
> completion of work but we are need not to do this work in table variable.
Well, no, technically you do not have to drop a table variable, but in any
case, if this is your idea of "extra work"...
Subscribe to:
Posts (Atom)