Indexing/Clearing Cache REsultset - Sql SErver 2000

How do you put a combined index on two fields within a table where they are
both varchar'
Is it possible to clear out a cache resultset from with sql query analyzer?
J. D> How do you put a combined index on two fields within a table where they
> are both varchar'
What is a "combined index"? Do you mean:
CREATE INDEX idxWhatever ON tableName(col1, col2)
?

> Is it possible to clear out a cache resultset from with sql query
> analyzer?
If you hit Ctrl+R, it will "hide" the results. There is no way that I know
of to get rid of them completely, without closing the current query window.
A|||DBCC DROPCLEANBUFFERS
go
DBCC FREEPROCCACHE
go
--Kristy
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OXmbtMrRFHA.3740@.TK2MSFTNGP10.phx.gbl...
> What is a "combined index"? Do you mean:
> CREATE INDEX idxWhatever ON tableName(col1, col2)
> ?
>
> If you hit Ctrl+R, it will "hide" the results. There is no way that I
know
> of to get rid of them completely, without closing the current query
window.
> A
>|||Aaron,
That was what I thought they meant, but was not 100% sure....thanks
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OXmbtMrRFHA.3740@.TK2MSFTNGP10.phx.gbl...
> What is a "combined index"? Do you mean:
> CREATE INDEX idxWhatever ON tableName(col1, col2)
> ?
>
> If you hit Ctrl+R, it will "hide" the results. There is no way that I
> know of to get rid of them completely, without closing the current query
> window.
> A
>|||Thanks Kristy
"Kristy" <pleasereplyby@.posting.com> wrote in message
news:eON$ITrRFHA.2680@.TK2MSFTNGP09.phx.gbl...
> DBCC DROPCLEANBUFFERS
> go
> DBCC FREEPROCCACHE
> go
> --Kristy
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:OXmbtMrRFHA.3740@.TK2MSFTNGP10.phx.gbl...
> know
> window.
>|||On Thu, 21 Apr 2005 16:45:52 -0400, Aaron [SQL Server MVP] wrote:
(snip)
>If you hit Ctrl+R, it will "hide" the results. There is no way that I know
>of to get rid of them completely, without closing the current query window.
Hi Aaron,
In "Results in Text" mode: switch to results pane, hit Ctrl-A to select
all, hit Backspace or Delete key.
In both "Results in Text" and "Results in Grid" mode: switch to query
pane, highlight one or more space characters, hit Ctrl-F5 to execute.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Indexing question

I have a basic rudimentary question concerning creating indexes.
Assuming a table :
FieldA varchar(1)
FieldB varchar(254)
FieldC varchar(25)
FieldD int (identity)
Now assuming we want to query
Select * from table1 where fieldb like '%b%' and fieldA is null
Should I create an index on FieldA and a separate index on FieldB
OR
create an index with FieldA AND FieldB
Basically do I create several individual indexes or create one index for
each type of query (as I may run several different kinds on the same table)
How does SQLServer know to use which index..'
Sorry for the newbieness...
Thanks,
-CraigCraig,
First of all, the only advantage of including fieldB in an index is
if you can thereby have a covering index either for the full WHERE
clause or for the SELECT list. Since you have SELECT *, and the table
has columns besides fieldA and fieldB, the entire table will have to be
accessed in any case. I would suggest these three as the most
reasonable possibilities:
Clustered index on (FieldA) or (FieldA, other columns)
This will always help unless (fieldA is null) is true for most of the
table, but since you can have only one clustered index on the table, it
only makes sense if no other clustered index is more compelling.
or
Nonclustered index on (fieldA, fieldB)
This can only help if the number of rows for which (fieldA is null) and
(fieldB like '%b%') is relatively small - definitely it would have to be
less than the number of data pages in the entire table, which could mean
between about 1-4% of the rows of the table or fewer.
or
Nonclustered index on (fieldA, fieldB, fieldC, fieldD)
This can help unless (fieldA is null) is true for most of the rows, and
allows a clustered index on some other column(s).
Other considerations include the activity on the table. For example, if
fieldC or fieldD is frequently updated, any index including those
columns will result in extra work from the updates.
There are no simple answers - the index tuning wizard may help you out,
and you could look through some books, such as Ken Henderson's Guru's
Guide to Transact-SQL or Kalen Delaney's Inside SQL Server 2000.
SK
Craig Stadler wrote:

quote:

---

>I have a basic rudimentary question concerning creating indexes.
>Assuming a table :
>FieldA varchar(1)
>FieldB varchar(254)
>FieldC varchar(25)
>FieldD int (identity)
>Now assuming we want to query
>Select * from table1 where fieldb like '%b%' and fieldA is null
>Should I create an index on FieldA and a separate index on FieldB
>OR
>create an index with FieldA AND FieldB
>Basically do I create several individual indexes or create one index for
>each type of query (as I may run several different kinds on the same table)
>How does SQLServer know to use which index..'
>Sorry for the newbieness...
>Thanks,
>-Craig
>
>

---

|||Craig,
Creating a clustered index on FieldA (or FieldA and some other
column(s)) is about the only way to slightly improve performance for
this query. This is because:
- the predicate FieldB LIKE '%b%' cannot use partial index scan or index
seek
- any index on FieldB will be almost as big as the table itself
A nonclustered index on FieldA could be an option if there are very few
rows where FieldA IS NULL (let's say, less than 3% of all rows).
Hope this helps,
Gert-Jan
Craig Stadler wrote:

quote:

---

> I have a basic rudimentary question concerning creating indexes.
> Assuming a table :
> FieldA varchar(1)
> FieldB varchar(254)
> FieldC varchar(25)
> FieldD int (identity)
> Now assuming we want to query
> Select * from table1 where fieldb like '%b%' and fieldA is null
> Should I create an index on FieldA and a separate index on FieldB
> OR
> create an index with FieldA AND FieldB
> Basically do I create several individual indexes or create one index for
> each type of query (as I may run several different kinds on the same table
)
> How does SQLServer know to use which index..'
> Sorry for the newbieness...
> Thanks,
> -Craig

---

sql

Indexing question

I have a basic rudimentary question concerning creating indexes.
Assuming a table :
FieldA varchar(1)
FieldB varchar(254)
FieldC varchar(25)
FieldD int (identity)
Now assuming we want to query
Select * from table1 where fieldb like '%b%' and fieldA is null
Should I create an index on FieldA and a separate index on FieldB
OR
create an index with FieldA AND FieldB
Basically do I create several individual indexes or create one index for
each type of query (as I may run several different kinds on the same table)
How does SQLServer know to use which index..'
Sorry for the newbieness...
Thanks,
-CraigCraig,
First of all, the only advantage of including fieldB in an index is
if you can thereby have a covering index either for the full WHERE
clause or for the SELECT list. Since you have SELECT *, and the table
has columns besides fieldA and fieldB, the entire table will have to be
accessed in any case. I would suggest these three as the most
reasonable possibilities:
Clustered index on (FieldA) or (FieldA, other columns)
This will always help unless (fieldA is null) is true for most of the
table, but since you can have only one clustered index on the table, it
only makes sense if no other clustered index is more compelling.
or
Nonclustered index on (fieldA, fieldB)
This can only help if the number of rows for which (fieldA is null) and
(fieldB like '%b%') is relatively small - definitely it would have to be
less than the number of data pages in the entire table, which could mean
between about 1-4% of the rows of the table or fewer.
or
Nonclustered index on (fieldA, fieldB, fieldC, fieldD)
This can help unless (fieldA is null) is true for most of the rows, and
allows a clustered index on some other column(s).
Other considerations include the activity on the table. For example, if
fieldC or fieldD is frequently updated, any index including those
columns will result in extra work from the updates.
There are no simple answers - the index tuning wizard may help you out,
and you could look through some books, such as Ken Henderson's Guru's
Guide to Transact-SQL or Kalen Delaney's Inside SQL Server 2000.
SK
Craig Stadler wrote:
>I have a basic rudimentary question concerning creating indexes.
>Assuming a table :
>FieldA varchar(1)
>FieldB varchar(254)
>FieldC varchar(25)
>FieldD int (identity)
>Now assuming we want to query
>Select * from table1 where fieldb like '%b%' and fieldA is null
>Should I create an index on FieldA and a separate index on FieldB
>OR
>create an index with FieldA AND FieldB
>Basically do I create several individual indexes or create one index for
>each type of query (as I may run several different kinds on the same table)
>How does SQLServer know to use which index..'
>Sorry for the newbieness...
>Thanks,
>-Craig
>
>|||Craig,
Creating a clustered index on FieldA (or FieldA and some other
column(s)) is about the only way to slightly improve performance for
this query. This is because:
- the predicate FieldB LIKE '%b%' cannot use partial index scan or index
seek
- any index on FieldB will be almost as big as the table itself
A nonclustered index on FieldA could be an option if there are very few
rows where FieldA IS NULL (let's say, less than 3% of all rows).
Hope this helps,
Gert-Jan
Craig Stadler wrote:
> I have a basic rudimentary question concerning creating indexes.
> Assuming a table :
> FieldA varchar(1)
> FieldB varchar(254)
> FieldC varchar(25)
> FieldD int (identity)
> Now assuming we want to query
> Select * from table1 where fieldb like '%b%' and fieldA is null
> Should I create an index on FieldA and a separate index on FieldB
> OR
> create an index with FieldA AND FieldB
> Basically do I create several individual indexes or create one index for
> each type of query (as I may run several different kinds on the same table)
> How does SQLServer know to use which index..'
> Sorry for the newbieness...
> Thanks,
> -Craig

INDEXES: Is this correct

I have a temp table and created two indexes on it.
Please See below
--
CREATE TABLE #Invoice (
InvoiceID int NOT NULL,
CustomerKey varchar(20),
DebtorId int,
Reference varchar(50),
BalanceDue money,
DebtID int,
Reason varchar(100),
DebtStatus int,
)
CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
---
I use DebtID as index because I use it again as a inner join to this table
in another query. So I assume it will make much faster. All I want to know is
would this code work?> All I want to know is would this code work?
Well, did you try it? What happened?
--
http://www.aspfaq.com/
(Reverse address to reply.)

INDEXES: Is this correct

I have a temp table and created two indexes on it.
Please See below
--
CREATE TABLE #Invoice (
InvoiceID int NOT NULL,
CustomerKey varchar(20),
DebtorId int,
Reference varchar(50),
BalanceDue money,
DebtID int,
Reason varchar(100),
DebtStatus int,
)
CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
---
I use DebtID as index because I use it again as a inner join to this table
in another query. So I assume it will make much faster. All I want to know i
s
would this code work?> All I want to know is would this code work?
Well, did you try it? What happened?
http://www.aspfaq.com/
(Reverse address to reply.)

INDEXES: Is this correct

I have a temp table and created two indexes on it.
Please See below
CREATE TABLE #Invoice (
InvoiceID int NOT NULL,
CustomerKey varchar(20),
DebtorId int,
Reference varchar(50),
BalanceDue money,
DebtID int,
Reason varchar(100),
DebtStatus int,
)
CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
I use DebtID as index because I use it again as a inner join to this table
in another query. So I assume it will make much faster. All I want to know is
would this code work?
> All I want to know is would this code work?
Well, did you try it? What happened?
http://www.aspfaq.com/
(Reverse address to reply.)

Indexes confusion

I have a temp table and created two indexes on it.
Please See below
CREATE TABLE #Invoice (
InvoiceID int NOT NULL,
CustomerKey varchar(20),
DebtorId int,
Reference varchar(50),
BalanceDue money,
DebtID int,
Reason varchar(100),
DebtStatus int,
)
CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
I use DebtID as index because I use it again as a inner join to this table
in another query. So I assume it will make much faster. All I want to know is
would this code work?
Already answered, please see your other thread, and these articles:
http://www.aspfaq.com/5007
http://www.aspfaq.com/5003
http://www.aspfaq.com/
(Reverse address to reply.)
"raj" <raj@.discussions.microsoft.com> wrote in message
news:0C4B1D69-3A7F-4202-B2DD-059127C6175C@.microsoft.com...
> I have a temp table and created two indexes on it.
> Please See below
> --
> CREATE TABLE #Invoice (
> InvoiceID int NOT NULL,
> CustomerKey varchar(20),
> DebtorId int,
> Reference varchar(50),
> BalanceDue money,
> DebtID int,
> Reason varchar(100),
> DebtStatus int,
> )
> CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
> CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
> I use DebtID as index because I use it again as a inner join to this table
> in another query. So I assume it will make much faster. All I want to know
is
> would this code work?
>
|||I'm sorry , but I couldn't find the answer in the newsgroup.
"Aaron [SQL Server MVP]" wrote:

> Already answered, please see your other thread, and these articles:
> http://www.aspfaq.com/5007
> http://www.aspfaq.com/5003
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "raj" <raj@.discussions.microsoft.com> wrote in message
> news:0C4B1D69-3A7F-4202-B2DD-059127C6175C@.microsoft.com...
> is
>
>
|||It should work fine. But bear in mind you're creating a temporary table
(with the # prefix on the tablename) so it will get created in tempdb and
will be automatically dropped when it goes out of scope (most likely at the
end of your batch). I don't know how much faster the indexes will really
make your queries - the best way to find out is trial and error (set a
couple session variables ("set statistics io on" and "set statistics time
on") at the beginning of your batch and turn on the execution plans to see
what real difference it makes).
Most temp table solutions don't benefit too much from indexes (since they're
usually pretty small tables, they're very short lived anyway and it takes
some resources to maintain the indexes). Both indexes you create in your
script are nonclustered which means your data itself is unsorted (it's a
"heap"). You might benefit from creating a clustered index on your table on
a suitable column, but as I just mentioned it'll probably take more
resources to maintain these indexes than it would to query the table without
the indexes in the first place. Trial & error is the only way to tell.
Cheers,
Mike
"raj" <raj@.discussions.microsoft.com> wrote in message
news:86C3A5FC-2F1E-4717-9130-7D372FB1B66E@.microsoft.com...[vbcol=seagreen]
> I'm sorry , but I couldn't find the answer in the newsgroup.
> "Aaron [SQL Server MVP]" wrote:
|||Thank you very much for your help. Greatly appreciate.
raj
"Mike Hodgson" wrote:

> It should work fine. But bear in mind you're creating a temporary table
> (with the # prefix on the tablename) so it will get created in tempdb and
> will be automatically dropped when it goes out of scope (most likely at the
> end of your batch). I don't know how much faster the indexes will really
> make your queries - the best way to find out is trial and error (set a
> couple session variables ("set statistics io on" and "set statistics time
> on") at the beginning of your batch and turn on the execution plans to see
> what real difference it makes).
> Most temp table solutions don't benefit too much from indexes (since they're
> usually pretty small tables, they're very short lived anyway and it takes
> some resources to maintain the indexes). Both indexes you create in your
> script are nonclustered which means your data itself is unsorted (it's a
> "heap"). You might benefit from creating a clustered index on your table on
> a suitable column, but as I just mentioned it'll probably take more
> resources to maintain these indexes than it would to query the table without
> the indexes in the first place. Trial & error is the only way to tell.
> --
> Cheers,
> Mike
> "raj" <raj@.discussions.microsoft.com> wrote in message
> news:86C3A5FC-2F1E-4717-9130-7D372FB1B66E@.microsoft.com...
>
>

Indexes confusion

I have a temp table and created two indexes on it.
Please See below
--
CREATE TABLE #Invoice (
InvoiceID int NOT NULL,
CustomerKey varchar(20),
DebtorId int,
Reference varchar(50),
BalanceDue money,
DebtID int,
Reason varchar(100),
DebtStatus int,
)
CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
---
I use DebtID as index because I use it again as a inner join to this table
in another query. So I assume it will make much faster. All I want to know i
s
would this code work?Already answered, please see your other thread, and these articles:
http://www.aspfaq.com/5007
http://www.aspfaq.com/5003
http://www.aspfaq.com/
(Reverse address to reply.)
"raj" <raj@.discussions.microsoft.com> wrote in message
news:0C4B1D69-3A7F-4202-B2DD-059127C6175C@.microsoft.com...
> I have a temp table and created two indexes on it.
> Please See below
> --
> CREATE TABLE #Invoice (
> InvoiceID int NOT NULL,
> CustomerKey varchar(20),
> DebtorId int,
> Reference varchar(50),
> BalanceDue money,
> DebtID int,
> Reason varchar(100),
> DebtStatus int,
> )
> CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
> CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
> ---
> I use DebtID as index because I use it again as a inner join to this table
> in another query. So I assume it will make much faster. All I want to know
is
> would this code work?
>|||I'm sorry , but I couldn't find the answer in the newsgroup.
"Aaron [SQL Server MVP]" wrote:

> Already answered, please see your other thread, and these articles:
> http://www.aspfaq.com/5007
> http://www.aspfaq.com/5003
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "raj" <raj@.discussions.microsoft.com> wrote in message
> news:0C4B1D69-3A7F-4202-B2DD-059127C6175C@.microsoft.com...
> is
>
>|||It should work fine. But bear in mind you're creating a temporary table
(with the # prefix on the tablename) so it will get created in tempdb and
will be automatically dropped when it goes out of scope (most likely at the
end of your batch). I don't know how much faster the indexes will really
make your queries - the best way to find out is trial and error (set a
couple session variables ("set statistics io on" and "set statistics time
on") at the beginning of your batch and turn on the execution plans to see
what real difference it makes).
Most temp table solutions don't benefit too much from indexes (since they're
usually pretty small tables, they're very short lived anyway and it takes
some resources to maintain the indexes). Both indexes you create in your
script are nonclustered which means your data itself is unsorted (it's a
"heap"). You might benefit from creating a clustered index on your table on
a suitable column, but as I just mentioned it'll probably take more
resources to maintain these indexes than it would to query the table without
the indexes in the first place. Trial & error is the only way to tell.
Cheers,
Mike
"raj" <raj@.discussions.microsoft.com> wrote in message
news:86C3A5FC-2F1E-4717-9130-7D372FB1B66E@.microsoft.com...[vbcol=seagreen]
> I'm sorry , but I couldn't find the answer in the newsgroup.
> "Aaron [SQL Server MVP]" wrote:
>|||Thank you very much for your help. Greatly appreciate.
raj
"Mike Hodgson" wrote:

> It should work fine. But bear in mind you're creating a temporary table
> (with the # prefix on the tablename) so it will get created in tempdb and
> will be automatically dropped when it goes out of scope (most likely at th
e
> end of your batch). I don't know how much faster the indexes will really
> make your queries - the best way to find out is trial and error (set a
> couple session variables ("set statistics io on" and "set statistics time
> on") at the beginning of your batch and turn on the execution plans to see
> what real difference it makes).
> Most temp table solutions don't benefit too much from indexes (since they'
re
> usually pretty small tables, they're very short lived anyway and it takes
> some resources to maintain the indexes). Both indexes you create in your
> script are nonclustered which means your data itself is unsorted (it's a
> "heap"). You might benefit from creating a clustered index on your table
on
> a suitable column, but as I just mentioned it'll probably take more
> resources to maintain these indexes than it would to query the table witho
ut
> the indexes in the first place. Trial & error is the only way to tell.
> --
> Cheers,
> Mike
> "raj" <raj@.discussions.microsoft.com> wrote in message
> news:86C3A5FC-2F1E-4717-9130-7D372FB1B66E@.microsoft.com...
>
>

Indexes confusion

I have a temp table and created two indexes on it.
Please See below
--
CREATE TABLE #Invoice (
InvoiceID int NOT NULL,
CustomerKey varchar(20),
DebtorId int,
Reference varchar(50),
BalanceDue money,
DebtID int,
Reason varchar(100),
DebtStatus int,
)
CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
---
I use DebtID as index because I use it again as a inner join to this table
in another query. So I assume it will make much faster. All I want to know is
would this code work?Already answered, please see your other thread, and these articles:
http://www.aspfaq.com/5007
http://www.aspfaq.com/5003
--
http://www.aspfaq.com/
(Reverse address to reply.)
"raj" <raj@.discussions.microsoft.com> wrote in message
news:0C4B1D69-3A7F-4202-B2DD-059127C6175C@.microsoft.com...
> I have a temp table and created two indexes on it.
> Please See below
> --
> CREATE TABLE #Invoice (
> InvoiceID int NOT NULL,
> CustomerKey varchar(20),
> DebtorId int,
> Reference varchar(50),
> BalanceDue money,
> DebtID int,
> Reason varchar(100),
> DebtStatus int,
> )
> CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
> CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
> ---
> I use DebtID as index because I use it again as a inner join to this table
> in another query. So I assume it will make much faster. All I want to know
is
> would this code work?
>|||I'm sorry , but I couldn't find the answer in the newsgroup.
"Aaron [SQL Server MVP]" wrote:
> Already answered, please see your other thread, and these articles:
> http://www.aspfaq.com/5007
> http://www.aspfaq.com/5003
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "raj" <raj@.discussions.microsoft.com> wrote in message
> news:0C4B1D69-3A7F-4202-B2DD-059127C6175C@.microsoft.com...
> > I have a temp table and created two indexes on it.
> > Please See below
> > --
> > CREATE TABLE #Invoice (
> > InvoiceID int NOT NULL,
> > CustomerKey varchar(20),
> > DebtorId int,
> > Reference varchar(50),
> > BalanceDue money,
> > DebtID int,
> > Reason varchar(100),
> > DebtStatus int,
> > )
> > CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
> > CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
> > ---
> >
> > I use DebtID as index because I use it again as a inner join to this table
> > in another query. So I assume it will make much faster. All I want to know
> is
> > would this code work?
> >
> >
>
>|||It should work fine. But bear in mind you're creating a temporary table
(with the # prefix on the tablename) so it will get created in tempdb and
will be automatically dropped when it goes out of scope (most likely at the
end of your batch). I don't know how much faster the indexes will really
make your queries - the best way to find out is trial and error (set a
couple session variables ("set statistics io on" and "set statistics time
on") at the beginning of your batch and turn on the execution plans to see
what real difference it makes).
Most temp table solutions don't benefit too much from indexes (since they're
usually pretty small tables, they're very short lived anyway and it takes
some resources to maintain the indexes). Both indexes you create in your
script are nonclustered which means your data itself is unsorted (it's a
"heap"). You might benefit from creating a clustered index on your table on
a suitable column, but as I just mentioned it'll probably take more
resources to maintain these indexes than it would to query the table without
the indexes in the first place. Trial & error is the only way to tell.
--
Cheers,
Mike
"raj" <raj@.discussions.microsoft.com> wrote in message
news:86C3A5FC-2F1E-4717-9130-7D372FB1B66E@.microsoft.com...
> I'm sorry , but I couldn't find the answer in the newsgroup.
> "Aaron [SQL Server MVP]" wrote:
>> Already answered, please see your other thread, and these articles:
>> http://www.aspfaq.com/5007
>> http://www.aspfaq.com/5003
>> --
>> http://www.aspfaq.com/
>> (Reverse address to reply.)
>>
>>
>> "raj" <raj@.discussions.microsoft.com> wrote in message
>> news:0C4B1D69-3A7F-4202-B2DD-059127C6175C@.microsoft.com...
>> > I have a temp table and created two indexes on it.
>> > Please See below
>> > --
>> > CREATE TABLE #Invoice (
>> > InvoiceID int NOT NULL,
>> > CustomerKey varchar(20),
>> > DebtorId int,
>> > Reference varchar(50),
>> > BalanceDue money,
>> > DebtID int,
>> > Reason varchar(100),
>> > DebtStatus int,
>> > )
>> > CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
>> > CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
>> > ---
>> >
>> > I use DebtID as index because I use it again as a inner join to this
>> > table
>> > in another query. So I assume it will make much faster. All I want to
>> > know
>> is
>> > would this code work?
>> >
>> >
>>|||Thank you very much for your help. Greatly appreciate.
raj
"Mike Hodgson" wrote:
> It should work fine. But bear in mind you're creating a temporary table
> (with the # prefix on the tablename) so it will get created in tempdb and
> will be automatically dropped when it goes out of scope (most likely at the
> end of your batch). I don't know how much faster the indexes will really
> make your queries - the best way to find out is trial and error (set a
> couple session variables ("set statistics io on" and "set statistics time
> on") at the beginning of your batch and turn on the execution plans to see
> what real difference it makes).
> Most temp table solutions don't benefit too much from indexes (since they're
> usually pretty small tables, they're very short lived anyway and it takes
> some resources to maintain the indexes). Both indexes you create in your
> script are nonclustered which means your data itself is unsorted (it's a
> "heap"). You might benefit from creating a clustered index on your table on
> a suitable column, but as I just mentioned it'll probably take more
> resources to maintain these indexes than it would to query the table without
> the indexes in the first place. Trial & error is the only way to tell.
> --
> Cheers,
> Mike
> "raj" <raj@.discussions.microsoft.com> wrote in message
> news:86C3A5FC-2F1E-4717-9130-7D372FB1B66E@.microsoft.com...
> > I'm sorry , but I couldn't find the answer in the newsgroup.
> >
> > "Aaron [SQL Server MVP]" wrote:
> >
> >> Already answered, please see your other thread, and these articles:
> >>
> >> http://www.aspfaq.com/5007
> >> http://www.aspfaq.com/5003
> >>
> >> --
> >> http://www.aspfaq.com/
> >> (Reverse address to reply.)
> >>
> >>
> >>
> >>
> >> "raj" <raj@.discussions.microsoft.com> wrote in message
> >> news:0C4B1D69-3A7F-4202-B2DD-059127C6175C@.microsoft.com...
> >> > I have a temp table and created two indexes on it.
> >> > Please See below
> >> > --
> >> > CREATE TABLE #Invoice (
> >> > InvoiceID int NOT NULL,
> >> > CustomerKey varchar(20),
> >> > DebtorId int,
> >> > Reference varchar(50),
> >> > BalanceDue money,
> >> > DebtID int,
> >> > Reason varchar(100),
> >> > DebtStatus int,
> >> > )
> >> > CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
> >> > CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
> >> > ---
> >> >
> >> > I use DebtID as index because I use it again as a inner join to this
> >> > table
> >> > in another query. So I assume it will make much faster. All I want to
> >> > know
> >> is
> >> > would this code work?
> >> >
> >> >
> >>
> >>
> >>
>
>