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

Index on foreign key [newbie] ?

Hi,
I need to track which modules a user is able to access. I'm designing a
table called UserPermissions which suits this purpose, where the columns
are:
ID int
UserID int
ModuleID int
AllowDeny bit
UserID is a foreign key which points to the "ID" column of another table
called Users. Now, here's the question:
I'll be writing a lot of queries that check access based on the user's
ID. For example:
"SELECT ModuleID FROM UserPermissions WHERE UserID=777"
Does this mean that I should create an index based on UserID since it is
frequently used in WHERE clauses? For example, should I create a
non-clustered unique index consisting of UserID+ModuleID since I know that
this combination will always be unique? ... or is the fact that UserID a
foreign key enough to cause SQL Server to optimize lookup based on UserID
automatically without my having to explicitly create an index?
For my trivial example, the decision of whether or not to use an index
probably won't make a difference in performance. I'm just trying to
understand the general concept of whether or not it's wise to create indexes
based on foreign keys or if SQL automatically indexes these foreign keys for
you [like it does for primary keys].
Julesa non-clust on UserID will do you fine. composite other columns if they will
be part of select
can you give an excact example of a query that you will execute ?
"Jules Winfield" <ghetto@.englewood.com> wrote in message
news:j7GdnR2cWLbEffXeRVn-tw@.giganews.com...
> Hi,
> I need to track which modules a user is able to access. I'm designing a
> table called UserPermissions which suits this purpose, where the columns
> are:
> ID int
> UserID int
> ModuleID int
> AllowDeny bit
> UserID is a foreign key which points to the "ID" column of another
> table called Users. Now, here's the question:
> I'll be writing a lot of queries that check access based on the user's
> ID. For example:
> "SELECT ModuleID FROM UserPermissions WHERE UserID=777"
> Does this mean that I should create an index based on UserID since it
> is frequently used in WHERE clauses? For example, should I create a
> non-clustered unique index consisting of UserID+ModuleID since I know that
> this combination will always be unique? ... or is the fact that UserID a
> foreign key enough to cause SQL Server to optimize lookup based on UserID
> automatically without my having to explicitly create an index?
> For my trivial example, the decision of whether or not to use an index
> probably won't make a difference in performance. I'm just trying to
> understand the general concept of whether or not it's wise to create
> indexes based on foreign keys or if SQL automatically indexes these
> foreign keys for you [like it does for primary keys].
> Jules
>|||Creating an index on the foreign key UserID will for sure boost the
performance. as for your where statement I don't recommend to make a
composite index. index on UserID will be enough

Index on foreign key [newbie] ?

Hi,
I need to track which modules a user is able to access. I'm designing a
table called UserPermissions which suits this purpose, where the columns
are:
ID int
UserID int
ModuleID int
AllowDeny bit
UserID is a foreign key which points to the "ID" column of another table
called Users. Now, here's the question:
I'll be writing a lot of queries that check access based on the user's
ID. For example:
"SELECT ModuleID FROM UserPermissions WHERE UserID=777"
Does this mean that I should create an index based on UserID since it is
frequently used in WHERE clauses? For example, should I create a
non-clustered unique index consisting of UserID+ModuleID since I know that
this combination will always be unique? ... or is the fact that UserID a
foreign key enough to cause SQL Server to optimize lookup based on UserID
automatically without my having to explicitly create an index?
For my trivial example, the decision of whether or not to use an index
probably won't make a difference in performance. I'm just trying to
understand the general concept of whether or not it's wise to create indexes
based on foreign keys or if SQL automatically indexes these foreign keys for
you [like it does for primary keys].
Julesa non-clust on UserID will do you fine. composite other columns if they will
be part of select
can you give an excact example of a query that you will execute ?
"Jules Winfield" <ghetto@.englewood.com> wrote in message
news:j7GdnR2cWLbEffXeRVn-tw@.giganews.com...
> Hi,
> I need to track which modules a user is able to access. I'm designing a
> table called UserPermissions which suits this purpose, where the columns
> are:
> ID int
> UserID int
> ModuleID int
> AllowDeny bit
> UserID is a foreign key which points to the "ID" column of another
> table called Users. Now, here's the question:
> I'll be writing a lot of queries that check access based on the user's
> ID. For example:
> "SELECT ModuleID FROM UserPermissions WHERE UserID=777"
> Does this mean that I should create an index based on UserID since it
> is frequently used in WHERE clauses? For example, should I create a
> non-clustered unique index consisting of UserID+ModuleID since I know that
> this combination will always be unique? ... or is the fact that UserID a
> foreign key enough to cause SQL Server to optimize lookup based on UserID
> automatically without my having to explicitly create an index?
> For my trivial example, the decision of whether or not to use an index
> probably won't make a difference in performance. I'm just trying to
> understand the general concept of whether or not it's wise to create
> indexes based on foreign keys or if SQL automatically indexes these
> foreign keys for you [like it does for primary keys].
> Jules
>|||Creating an index on the foreign key UserID will for sure boost the
performance. as for your where statement I don't recommend to make a
composite index. index on UserID will be enough

Index on foreign key [newbie] ?

Hi,
I need to track which modules a user is able to access. I'm designing a
table called UserPermissions which suits this purpose, where the columns
are:
ID int
UserID int
ModuleID int
AllowDeny bit
UserID is a foreign key which points to the "ID" column of another table
called Users. Now, here's the question:
I'll be writing a lot of queries that check access based on the user's
ID. For example:
"SELECT ModuleID FROM UserPermissions WHERE UserID=777"
Does this mean that I should create an index based on UserID since it is
frequently used in WHERE clauses? For example, should I create a
non-clustered unique index consisting of UserID+ModuleID since I know that
this combination will always be unique? ... or is the fact that UserID a
foreign key enough to cause SQL Server to optimize lookup based on UserID
automatically without my having to explicitly create an index?
For my trivial example, the decision of whether or not to use an index
probably won't make a difference in performance. I'm just trying to
understand the general concept of whether or not it's wise to create indexes
based on foreign keys or if SQL automatically indexes these foreign keys for
you [like it does for primary keys].
Jules
a non-clust on UserID will do you fine. composite other columns if they will
be part of select
can you give an excact example of a query that you will execute ?
"Jules Winfield" <ghetto@.englewood.com> wrote in message
news:j7GdnR2cWLbEffXeRVn-tw@.giganews.com...
> Hi,
> I need to track which modules a user is able to access. I'm designing a
> table called UserPermissions which suits this purpose, where the columns
> are:
> ID int
> UserID int
> ModuleID int
> AllowDeny bit
> UserID is a foreign key which points to the "ID" column of another
> table called Users. Now, here's the question:
> I'll be writing a lot of queries that check access based on the user's
> ID. For example:
> "SELECT ModuleID FROM UserPermissions WHERE UserID=777"
> Does this mean that I should create an index based on UserID since it
> is frequently used in WHERE clauses? For example, should I create a
> non-clustered unique index consisting of UserID+ModuleID since I know that
> this combination will always be unique? ... or is the fact that UserID a
> foreign key enough to cause SQL Server to optimize lookup based on UserID
> automatically without my having to explicitly create an index?
> For my trivial example, the decision of whether or not to use an index
> probably won't make a difference in performance. I'm just trying to
> understand the general concept of whether or not it's wise to create
> indexes based on foreign keys or if SQL automatically indexes these
> foreign keys for you [like it does for primary keys].
> Jules
>
|||Creating an index on the foreign key UserID will for sure boost the
performance. as for your where statement I don't recommend to make a
composite index. index on UserID will be enough