index temp tables?

Howdy all. Ive got some SP's that make heavy use of temp tables. So when I
look at the Execution Plan I see about 25 table scans on these guys. The Row
Count and Estimated Row Size are usually only about 3-120 rows though. Do you
guys Index temp tables this size?
TIA,
ChrisR
Depends on the row size to some degree but usually tables that small it
won't matter much. Most if not all the rows are on a single page anyway.
But the only way to know for sure is to test it with and without to see if
the gain is worth the time and effort to create the indexes.
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:A23705AA-6B95-43E0-877A-05B1E457DE27@.microsoft.com...
> Howdy all. Ive got some SP's that make heavy use of temp tables. So when I
> look at the Execution Plan I see about 25 table scans on these guys. The
> Row
> Count and Estimated Row Size are usually only about 3-120 rows though. Do
> you
> guys Index temp tables this size?
>
> --
> TIA,
> ChrisR

index temp tables?

Howdy all. Ive got some SP's that make heavy use of temp tables. So when I
look at the Execution Plan I see about 25 table scans on these guys. The Row
Count and Estimated Row Size are usually only about 3-120 rows though. Do yo
u
guys Index temp tables this size?
TIA,
ChrisRDepends on the row size to some degree but usually tables that small it
won't matter much. Most if not all the rows are on a single page anyway.
But the only way to know for sure is to test it with and without to see if
the gain is worth the time and effort to create the indexes.
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:A23705AA-6B95-43E0-877A-05B1E457DE27@.microsoft.com...
> Howdy all. Ive got some SP's that make heavy use of temp tables. So when I
> look at the Execution Plan I see about 25 table scans on these guys. The
> Row
> Count and Estimated Row Size are usually only about 3-120 rows though. Do
> you
> guys Index temp tables this size?
>
> --
> TIA,
> ChrisR

index temp tables?

Howdy all. Ive got some SP's that make heavy use of temp tables. So when I
look at the Execution Plan I see about 25 table scans on these guys. The Row
Count and Estimated Row Size are usually only about 3-120 rows though. Do you
guys Index temp tables this size?
--
TIA,
ChrisRDepends on the row size to some degree but usually tables that small it
won't matter much. Most if not all the rows are on a single page anyway.
But the only way to know for sure is to test it with and without to see if
the gain is worth the time and effort to create the indexes.
--
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:A23705AA-6B95-43E0-877A-05B1E457DE27@.microsoft.com...
> Howdy all. Ive got some SP's that make heavy use of temp tables. So when I
> look at the Execution Plan I see about 25 table scans on these guys. The
> Row
> Count and Estimated Row Size are usually only about 3-120 rows though. Do
> you
> guys Index temp tables this size?
>
> --
> TIA,
> ChrisRsql

Index Spool/Eager Spool

Hey Guys,
I have a good one!
What would cause the server to use a huge index but then perform a index
spool/eager spool action in the query plan and basically recreate the
whole index?
Thanks,
FrankThe query optimizer may have chosen a plan that requires the rows to be in a
different order for two different operations. For example, the optimizer
may choose to process the restrict condition (WHERE restrict_condition)
before processing the join condition (ON (join_condition)), where the index
used to process the restrict condition is in a different order than is
needed to process the join. If the execution plan contains an iteration
step, then the eager spool may be used to minimize the number of reads
required to satisfy the join. The SEEK:() predicate on the Index Spool
operator restricts the rows that are copied into tempdb, which minimizes the
number of reads required for lookups in each iteration.
"Frank W" <me@.frankwisniewski.net> wrote in message
news:8zhUe.245376$gL1.240016@.tornado.texas.rr.com...
> Hey Guys,
> I have a good one!
> What would cause the server to use a huge index but then perform a index
> spool/eager spool action in the query plan and basically recreate the
> whole index?
> Thanks,
> Frank

Index REBUILD question

Hey all,
One of the guys here (Tibor Karaszi) kindly pointed me to a script in
BOL that checks the fragmentation of indexes in a db, then depending on
the amount of fragmentation it REBUILDS or REORGANIZES the index. The
script I want to use is as it appears in BOL, but I've tweaked it to
specify a FILLFACTOR of 95 for the REBUILDS.
So my question is, if I have this script say running in a maintenance
plan at a 24/7 site, and it decides to REBUILD an index, if someone
indirectly accesses the table via our application would the application
receive an error as the table has been locked by the REBUILD?
Or will the query from the user wait until the lock on the table is
returned by the REBUILD process? (this is probably the ideal situation
for us)

>From BOL I understand it may have a shared lock (ie. readonly) at some
times.
But I'm also concerned about writes to the table.
We would prefer that the access is slow at that point rather than
returning an error message.
Can anyone clear this up for me?
Cheers!!
PS:
We can't use REBUILD ONLINE as
1) We use large object data types and
2) The client does not have Enterprise versionA REBUILD by default is a totally off-line process in that the table has an
Exclusive lock for the duration. So if the user tries to access the table it
will be blocked. How long is determined by the amount of time it takes to
rebuild the index or the timeout setting which ever comes first. If you need
it to be available all the time then you should look into the REORGANIZE
option instead. And in either case I would not recommend specifying the fill
factor in that script. By doing so you loose the ability to tune the fill
factors on individual indexes. If you do not specify it then it will use the
last setting. So if you initially want 95 then you should have built them
with 95 or manually issue a REBUILD with 95 as the setting. From then on
don't specify it again in the general rebuild script.
Andrew J. Kelly SQL MVP
<davconts@.gmail.com> wrote in message
news:1152064393.005694.10670@.j8g2000cwa.googlegroups.com...
> Hey all,
> One of the guys here (Tibor Karaszi) kindly pointed me to a script in
> BOL that checks the fragmentation of indexes in a db, then depending on
> the amount of fragmentation it REBUILDS or REORGANIZES the index. The
> script I want to use is as it appears in BOL, but I've tweaked it to
> specify a FILLFACTOR of 95 for the REBUILDS.
> So my question is, if I have this script say running in a maintenance
> plan at a 24/7 site, and it decides to REBUILD an index, if someone
> indirectly accesses the table via our application would the application
> receive an error as the table has been locked by the REBUILD?
> Or will the query from the user wait until the lock on the table is
> returned by the REBUILD process? (this is probably the ideal situation
> for us)
>
> times.
> But I'm also concerned about writes to the table.
> We would prefer that the access is slow at that point rather than
> returning an error message.
> Can anyone clear this up for me?
> Cheers!!
> PS:
> We can't use REBUILD ONLINE as
> 1) We use large object data types and
> 2) The client does not have Enterprise version
>|||In addition, if you have Enterprise Edition, you can do the rebuild with the
ONLINE option.
REORGANIZE might be better for you than REBUILD with ONLINE, you have to tes
t to see what suits you
best.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e5Uduv9nGHA.4848@.TK2MSFTNGP03.phx.gbl...
>A REBUILD by default is a totally off-line process in that the table has an
Exclusive lock for the
>duration. So if the user tries to access the table it will be blocked. How
long is determined by
>the amount of time it takes to rebuild the index or the timeout setting whi
ch ever comes first. If
>you need it to be available all the time then you should look into the REOR
GANIZE option instead.
>And in either case I would not recommend specifying the fill factor in that
script. By doing so you
>loose the ability to tune the fill factors on individual indexes. If you do
not specify it then it
>will use the last setting. So if you initially want 95 then you should have
built them with 95 or
>manually issue a REBUILD with 95 as the setting. From then on don't specify
it again in the general
>rebuild script.
> --
> Andrew J. Kelly SQL MVP
> <davconts@.gmail.com> wrote in message news:1152064393.005694.10670@.j8g2000
cwa.googlegroups.com...
>

Index REBUILD question

Hey all,
One of the guys here (Tibor Karaszi) kindly pointed me to a script in
BOL that checks the fragmentation of indexes in a db, then depending on
the amount of fragmentation it REBUILDS or REORGANIZES the index. The
script I want to use is as it appears in BOL, but I've tweaked it to
specify a FILLFACTOR of 95 for the REBUILDS.
So my question is, if I have this script say running in a maintenance
plan at a 24/7 site, and it decides to REBUILD an index, if someone
indirectly accesses the table via our application would the application
receive an error as the table has been locked by the REBUILD?
Or will the query from the user wait until the lock on the table is
returned by the REBUILD process? (this is probably the ideal situation
for us)
>From BOL I understand it may have a shared lock (ie. readonly) at some
times.
But I'm also concerned about writes to the table.
We would prefer that the access is slow at that point rather than
returning an error message.
Can anyone clear this up for me?
Cheers!!
PS:
We can't use REBUILD ONLINE as
1) We use large object data types and
2) The client does not have Enterprise versionA REBUILD by default is a totally off-line process in that the table has an
Exclusive lock for the duration. So if the user tries to access the table it
will be blocked. How long is determined by the amount of time it takes to
rebuild the index or the timeout setting which ever comes first. If you need
it to be available all the time then you should look into the REORGANIZE
option instead. And in either case I would not recommend specifying the fill
factor in that script. By doing so you loose the ability to tune the fill
factors on individual indexes. If you do not specify it then it will use the
last setting. So if you initially want 95 then you should have built them
with 95 or manually issue a REBUILD with 95 as the setting. From then on
don't specify it again in the general rebuild script.
--
Andrew J. Kelly SQL MVP
<davconts@.gmail.com> wrote in message
news:1152064393.005694.10670@.j8g2000cwa.googlegroups.com...
> Hey all,
> One of the guys here (Tibor Karaszi) kindly pointed me to a script in
> BOL that checks the fragmentation of indexes in a db, then depending on
> the amount of fragmentation it REBUILDS or REORGANIZES the index. The
> script I want to use is as it appears in BOL, but I've tweaked it to
> specify a FILLFACTOR of 95 for the REBUILDS.
> So my question is, if I have this script say running in a maintenance
> plan at a 24/7 site, and it decides to REBUILD an index, if someone
> indirectly accesses the table via our application would the application
> receive an error as the table has been locked by the REBUILD?
> Or will the query from the user wait until the lock on the table is
> returned by the REBUILD process? (this is probably the ideal situation
> for us)
>>From BOL I understand it may have a shared lock (ie. readonly) at some
> times.
> But I'm also concerned about writes to the table.
> We would prefer that the access is slow at that point rather than
> returning an error message.
> Can anyone clear this up for me?
> Cheers!!
> PS:
> We can't use REBUILD ONLINE as
> 1) We use large object data types and
> 2) The client does not have Enterprise version
>|||In addition, if you have Enterprise Edition, you can do the rebuild with the ONLINE option.
REORGANIZE might be better for you than REBUILD with ONLINE, you have to test to see what suits you
best.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e5Uduv9nGHA.4848@.TK2MSFTNGP03.phx.gbl...
>A REBUILD by default is a totally off-line process in that the table has an Exclusive lock for the
>duration. So if the user tries to access the table it will be blocked. How long is determined by
>the amount of time it takes to rebuild the index or the timeout setting which ever comes first. If
>you need it to be available all the time then you should look into the REORGANIZE option instead.
>And in either case I would not recommend specifying the fill factor in that script. By doing so you
>loose the ability to tune the fill factors on individual indexes. If you do not specify it then it
>will use the last setting. So if you initially want 95 then you should have built them with 95 or
>manually issue a REBUILD with 95 as the setting. From then on don't specify it again in the general
>rebuild script.
> --
> Andrew J. Kelly SQL MVP
> <davconts@.gmail.com> wrote in message news:1152064393.005694.10670@.j8g2000cwa.googlegroups.com...
>> Hey all,
>> One of the guys here (Tibor Karaszi) kindly pointed me to a script in
>> BOL that checks the fragmentation of indexes in a db, then depending on
>> the amount of fragmentation it REBUILDS or REORGANIZES the index. The
>> script I want to use is as it appears in BOL, but I've tweaked it to
>> specify a FILLFACTOR of 95 for the REBUILDS.
>> So my question is, if I have this script say running in a maintenance
>> plan at a 24/7 site, and it decides to REBUILD an index, if someone
>> indirectly accesses the table via our application would the application
>> receive an error as the table has been locked by the REBUILD?
>> Or will the query from the user wait until the lock on the table is
>> returned by the REBUILD process? (this is probably the ideal situation
>> for us)
>>From BOL I understand it may have a shared lock (ie. readonly) at some
>> times.
>> But I'm also concerned about writes to the table.
>> We would prefer that the access is slow at that point rather than
>> returning an error message.
>> Can anyone clear this up for me?
>> Cheers!!
>> PS:
>> We can't use REBUILD ONLINE as
>> 1) We use large object data types and
>> 2) The client does not have Enterprise version
>

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 Index

Hi Guys,
Recently, my friend faced an interesting interview question that asks
"Can we create an index on an index" .....
Is it possible to create that?If so how?
Could anyone trace it out...............
Thanks,
Dutt.....Hello,
We can not create an Index based on a INDEX object.
Thanks
Hari
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1180329868.716346.164660@.x35g2000prf.googlegroups.com...
> Hi Guys,
> Recently, my friend faced an interesting interview question that asks
> "Can we create an index on an index" .....
> Is it possible to create that?If so how?
> Could anyone trace it out...............
> Thanks,
> Dutt.....
>|||It's hard to know exactly what the interviewer meant.
When you create a clustered index on a table, the table is then part of the
clustered index. So in a sense, when you then build a nonclustered index on
a table with a clustered index, you're building an index on an index.
Nonclustered indexes contain clustered index keys, so that further seems to
imply that the nonclustered index is build 'on' the clustered index.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1180329868.716346.164660@.x35g2000prf.googlegroups.com...
> Hi Guys,
> Recently, my friend faced an interesting interview question that asks
> "Can we create an index on an index" .....
> Is it possible to create that?If so how?
> Could anyone trace it out...............
> Thanks,
> Dutt.....
>|||Dutt,
It sure is a good interview question, because it allows the interviewed
person to explain the difference between the pure syntax (No, you cannot
create an index on an index), the semantics (you *can* create an index
on an indexed view) and index implementation (isn't an Indexed View just
an index structure with included columns?)
I can understand that a novice would not know how to answer the
question. My reply would probably be: Why would you ever want to index
an index? What would be the added value as opposed to creating an index
on the base table or view?
Gert-Jan
Dutt wrote:
> Hi Guys,
> Recently, my friend faced an interesting interview question that asks
> "Can we create an index on an index" .....
> Is it possible to create that?If so how?
> Could anyone trace it out...............
> Thanks,
> Dutt.....|||Instead of giving a yes or no answer, I'd either ask the interviewer to
clarify or just briefly explain how SQL Server does indexing and hope that
the interviewer is open-minded (i.e. doesn't have a preset answer in mind) t
o
see that the interviewee understands the fundamentals and therefore shouldn'
t
have any problem once the intent of the question becomes clear.
Linchi
"Dutt" wrote:

> Hi Guys,
> Recently, my friend faced an interesting interview question that asks
> "Can we create an index on an index" .....
> Is it possible to create that?If so how?
> Could anyone trace it out...............
> Thanks,
> Dutt.....
>sql

Index on Index

Hi Guys,
Recently, my friend faced an interesting interview question that asks
"Can we create an index on an index" .....
Is it possible to create that?If so how?
Could anyone trace it out...............
Thanks,
Dutt.....
Hello,
We can not create an Index based on a INDEX object.
Thanks
Hari
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1180329868.716346.164660@.x35g2000prf.googlegr oups.com...
> Hi Guys,
> Recently, my friend faced an interesting interview question that asks
> "Can we create an index on an index" .....
> Is it possible to create that?If so how?
> Could anyone trace it out...............
> Thanks,
> Dutt.....
>
|||It's hard to know exactly what the interviewer meant.
When you create a clustered index on a table, the table is then part of the
clustered index. So in a sense, when you then build a nonclustered index on
a table with a clustered index, you're building an index on an index.
Nonclustered indexes contain clustered index keys, so that further seems to
imply that the nonclustered index is build 'on' the clustered index.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1180329868.716346.164660@.x35g2000prf.googlegr oups.com...
> Hi Guys,
> Recently, my friend faced an interesting interview question that asks
> "Can we create an index on an index" .....
> Is it possible to create that?If so how?
> Could anyone trace it out...............
> Thanks,
> Dutt.....
>
|||Dutt,
It sure is a good interview question, because it allows the interviewed
person to explain the difference between the pure syntax (No, you cannot
create an index on an index), the semantics (you *can* create an index
on an indexed view) and index implementation (isn't an Indexed View just
an index structure with included columns?)
I can understand that a novice would not know how to answer the
question. My reply would probably be: Why would you ever want to index
an index? What would be the added value as opposed to creating an index
on the base table or view?
Gert-Jan
Dutt wrote:
> Hi Guys,
> Recently, my friend faced an interesting interview question that asks
> "Can we create an index on an index" .....
> Is it possible to create that?If so how?
> Could anyone trace it out...............
> Thanks,
> Dutt.....
|||Instead of giving a yes or no answer, I'd either ask the interviewer to
clarify or just briefly explain how SQL Server does indexing and hope that
the interviewer is open-minded (i.e. doesn't have a preset answer in mind) to
see that the interviewee understands the fundamentals and therefore shouldn't
have any problem once the intent of the question becomes clear.
Linchi
"Dutt" wrote:

> Hi Guys,
> Recently, my friend faced an interesting interview question that asks
> "Can we create an index on an index" .....
> Is it possible to create that?If so how?
> Could anyone trace it out...............
> Thanks,
> Dutt.....
>

Index on Index

Hi Guys,
Recently, my friend faced an interesting interview question that asks
"Can we create an index on an index" .....
Is it possible to create that?If so how?
Could anyone trace it out...............
Thanks,
Dutt.....Hello,
We can not create an Index based on a INDEX object.
Thanks
Hari
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1180329868.716346.164660@.x35g2000prf.googlegroups.com...
> Hi Guys,
> Recently, my friend faced an interesting interview question that asks
> "Can we create an index on an index" .....
> Is it possible to create that?If so how?
> Could anyone trace it out...............
> Thanks,
> Dutt.....
>|||It's hard to know exactly what the interviewer meant.
When you create a clustered index on a table, the table is then part of the
clustered index. So in a sense, when you then build a nonclustered index on
a table with a clustered index, you're building an index on an index.
Nonclustered indexes contain clustered index keys, so that further seems to
imply that the nonclustered index is build 'on' the clustered index.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1180329868.716346.164660@.x35g2000prf.googlegroups.com...
> Hi Guys,
> Recently, my friend faced an interesting interview question that asks
> "Can we create an index on an index" .....
> Is it possible to create that?If so how?
> Could anyone trace it out...............
> Thanks,
> Dutt.....
>|||Dutt,
It sure is a good interview question, because it allows the interviewed
person to explain the difference between the pure syntax (No, you cannot
create an index on an index), the semantics (you *can* create an index
on an indexed view) and index implementation (isn't an Indexed View just
an index structure with included columns?)
I can understand that a novice would not know how to answer the
question. My reply would probably be: Why would you ever want to index
an index? What would be the added value as opposed to creating an index
on the base table or view?
Gert-Jan
Dutt wrote:
> Hi Guys,
> Recently, my friend faced an interesting interview question that asks
> "Can we create an index on an index" .....
> Is it possible to create that?If so how?
> Could anyone trace it out...............
> Thanks,
> Dutt.....|||Instead of giving a yes or no answer, I'd either ask the interviewer to
clarify or just briefly explain how SQL Server does indexing and hope that
the interviewer is open-minded (i.e. doesn't have a preset answer in mind) to
see that the interviewee understands the fundamentals and therefore shouldn't
have any problem once the intent of the question becomes clear.
Linchi
"Dutt" wrote:
> Hi Guys,
> Recently, my friend faced an interesting interview question that asks
> "Can we create an index on an index" .....
> Is it possible to create that?If so how?
> Could anyone trace it out...............
> Thanks,
> Dutt.....
>