Indexes and Deadlocks: int

Showing posts with label int. Show all posts

Wednesday, March 21, 2012

Indexing with null or zero

I am adding an int field to a table that will be used to link it to another
table when it has a number in it. I plan to index it for faster access but
wondered if my default for that table should be NULL or zero or if it makes
a difference. Thanks.
DavidTo an index, NULL is just a value (as is 0). So use whatever makes most sens
e to you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David C" <dlchase@.lifetimeinc.com> wrote in message news:uy%238XzdSFHA.252@.TK2MSFTNGP12.ph
x.gbl...
>I am adding an int field to a table that will be used to link it to another
table when it has a
>number in it. I plan to index it for faster access but wondered if my defa
ult for that table
>should be NULL or zero or if it makes a difference. Thanks.
> David
>|||I have to say that it would be nice if there was a built-in means to create
a
unique constraint on a column such that nulls are ignored. Going the route o
f
indexed views or triggers is a colossal pain.
Thomas
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OmrRbKeSFHA.580@.TK2MSFTNGP15.phx.gbl...
> To an index, NULL is just a value (as is 0). So use whatever makes most se
nse
> to you.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "David C" <dlchase@.lifetimeinc.com> wrote in message
> news:uy%238XzdSFHA.252@.TK2MSFTNGP12.phx.gbl...
>|||Tibor,
I don't think this is quite true. A column's nullability (assuming
that is part of the choice here) affects what query plans can be
considered. Also, indexes aside, there can be different and
unexpected results depending on whether NULL or 0 is used.
An example we often see in the newsgroups is
select this, that
from T
where T.parent not in (
select parent
from T_children
)
which returns no rows even when there are T.parent values
not present in the T_children table.
There are other differences less likely to be an issue, like
calculating percentages:
select
T.parent,
sum(case when parent = T_children.parent then 1.00 else 0.00 end) /
count(T_children.parent) as percentage
from T join T_children
on T.parent = T_children.parent
group by T.parent
Unfortunately, while using NULL is probably truer to the principles
of good modeling, using 0 may have some practical advantages, if
the referring column can then be declared as NOT NULL. To maintain
referential integrity via a foreign key constraint, NULL is a better choice,
since 0 requires putting a dummy entry into the referenced table.
Steve Kass
Drew University
Tibor Karaszi wrote:

>To an index, NULL is just a value (as is 0). So use whatever makes most sen
se to you.
>
>|||
Thomas wrote:

>I have to say that it would be nice if there was a built-in means to create
a
>unique constraint on a column such that nulls are ignored. Going the route
of
>indexed views or triggers is a colossal pain.
>
>
There is at least one solution using an indexed column instead of
an indexed view. Functionally, it is much the same, but you might
find it easier to maintain.
http://groups.google.co.uk/groups?q...B8-B7567063D1CC
SK

>Thomas
>
>"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
>message news:OmrRbKeSFHA.580@.TK2MSFTNGP15.phx.gbl...
>
>
>|||Hmm..That's an clever solution. For the purposes of other people reading thi
s
thread, the idea is to create a computed column that either equals the colum
n on
which you really want a unique index or the primary key when that value is n
ull.
You would then put the unique index on this computed column.
Granted, it does mean dealing with computed columns which can be a persnicke
ty
but it does get around the issue.
Thomas
"Steve Kass" <skass@.drew.edu> wrote in message
news:uSJlWXeSFHA.3788@.tk2msftngp13.phx.gbl...
>
> Thomas wrote:
>
> There is at least one solution using an indexed column instead of
> an indexed view. Functionally, it is much the same, but you might
> find it easier to maintain.
> http://groups.google.co.uk/groups?q...B8-B7567063D1CC
> SK
>|||If a Foreign Key column allows null, then this is definitely NOT the same as
putting a zero in the column. A value of zero MUST exist in the reference
table, a null value does NOT have to (indeed it cannot exist) as PK in the
reference table. The distinction is critical, it is the dfference between a
[one]-to-[zero or many] relationship, and a [zero or one]-to-[zero or many]
relationship.
Example, In a [one]-to-[zero or many] Employees have zero o many timecard
punches, but for each time card punch there must be one employee - and only
one an employee.
In a [zero or one]-to-[zero or many] relatonship, Each Library patron can
have zero or many books checked out to them, and each book can be checked
out to zero or one library patron...
The only way to model this distinction is by allowing, (and using) null
values in the Foreign Key column on the many side of the DRI constraint.
"Tibor Karaszi" wrote:

> To an index, NULL is just a value (as is 0). So use whatever makes most se
nse to you.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "David C" <dlchase@.lifetimeinc.com> wrote in message news:uy%238XzdSFHA.25
2@.TK2MSFTNGP12.phx.gbl...
>
>|||When I want a Foreign Key Column to be unique, but Allow any number of Nulls
,
(that's a [One]-to-[Zero or One] Relationship, I use an extra table
CREATE TABLE [TabA] (
[AID] [int] NOT NULL ,
[Name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_TabA] PRIMARY KEY CLUSTERED
([AID]) ON [PRIMARY]
) ON [PRIMARY]
GO
-- ****************************************
***
CREATE TABLE [TabB] (
[BID] [int] NOT NULL ,
[AID] [int] NULL ,
CONSTRAINT [PK_TabB] PRIMARY KEY CLUSTERED
( [BID]) ON [PRIMARY] ,
CONSTRAINT [FK_TabB_TabA] FOREIGN KEY
([AID]) REFERENCES [TabA] ([AID])
) ON [PRIMARY]
GO
-- ****************************************
***
CREATE TABLE [TabC] (
[BID] [int] NOT NULL ,
[AID] [int] NOT NULL ,
CONSTRAINT [PK_TabC] PRIMARY KEY CLUSTERED
([BID]) ON [PRIMARY] ,
CONSTRAINT [IX_TabCUniqueAID] UNIQUE NONCLUSTERED
([AID]) ON [PRIMARY] ,
CONSTRAINT [FK_TabC_TabB] FOREIGN KEY
([BID]) REFERENCES [TabB] ([BID])
) ON [PRIMARY]
GO
-- Then I add ALL the child records to TabB, Both those with null values of
AID, and Non-Null values of AID, but only add the Non-Null AID Records to
TabC... TabB is teh real table, and has all the other attriobutes in it,
TabC is only there to enforce uniqueness on the Non-Null Values of AID in Ta
bB
"Thomas" wrote:

> Hmm..That's an clever solution. For the purposes of other people reading t
his
> thread, the idea is to create a computed column that either equals the col
umn on
> which you really want a unique index or the primary key when that value is
null.
> You would then put the unique index on this computed column.
> Granted, it does mean dealing with computed columns which can be a persnic
kety
> but it does get around the issue.
>
> Thomas
>
>
> "Steve Kass" <skass@.drew.edu> wrote in message
> news:uSJlWXeSFHA.3788@.tk2msftngp13.phx.gbl...
>
>|||Perhaps I was in a bit too much hurry when responding. I was looking at it p
urely from the index'
perspective, how the data is stored in the index. You definitely want to thi
nk through semantics
carefully. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:EC2328B5-9A67-4E1A-A096-9B071AE179EB@.microsoft.com...
> If a Foreign Key column allows null, then this is definitely NOT the same
as
> putting a zero in the column. A value of zero MUST exist in the referenc
e
> table, a null value does NOT have to (indeed it cannot exist) as PK in the
> reference table. The distinction is critical, it is the dfference between
a
> [one]-to-[zero or many] relationship, and a [zero or one]-to-[zero or many]
> relationship.
>
> Example, In a [one]-to-[zero or many] Employees have zero o many timecard
> punches, but for each time card punch there must be one employee - and on
ly
> one an employee.
> In a [zero or one]-to-[zero or many] relatonship, Each Library patron can
> have zero or many books checked out to them, and each book can be checked
> out to zero or one library patron...
> The only way to model this distinction is by allowing, (and using) null
> values in the Foreign Key column on the many side of the DRI constraint.
> "Tibor Karaszi" wrote:
>|||Tibor,
Neglected to make clear that details in my post,were, of course, not
directed at you... figured you just overlooked that. I slip into
pedanticLand way too easily...
Respectfully, Charly
"Tibor Karaszi" wrote:

> Perhaps I was in a bit too much hurry when responding. I was looking at it
purely from the index'
> perspective, how the data is stored in the index. You definitely want to t
hink through semantics
> carefully. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:EC2328B5-9A67-4E1A-A096-9B071AE179EB@.microsoft.com...
>
>

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

Hi Gurus,
I have a table called Companies with int identity column as primary key and other fields. Also there is a Status column which can hold either 0 or 1. I use this status column in a join from some child table like where a.status = 1 along with other conditions.

Now, the question is should I create an index for this Status column? Will it improve the performance?

Thanks.I would say NO. One of the criteria for creating a good index is selectivity. So your index on a booleon column would not help the performance. In addition, it is just an overhead on the inserts.

- CB|||Post the query...because the answr is it depends...

If yo had SELECT a.Col1, a.Status, a.Col2 FROM myTable1
INNER JOIN myTable2 b ON a.col1 = b.col and a.col2 = b.col2

I'd add it to the index...not for look up, but to prevent it from having to go to the data pages..|||I agree with Brett. In the situation that he described (covered indexes), it could be helpful to tag that column at the end of the composite index to avoid another trip to get the data.

- CB
Originally posted by Brett Kaiser
Post the query...because the answr is it depends...

If yo had SELECT a.Col1, a.Status, a.Col2 FROM myTable1
INNER JOIN myTable2 b ON a.col1 = b.col and a.col2 = b.col2

I'd add it to the index...not for look up, but to prevent it from having to go to the data pages..|||Ok, here is a sample:

SELECT A.*, B.NAME
FROM Orders A,
Companies B
Where B.CompanyId = A.CompanyId
and B.Status = 1
ORDER BY B.NAME

Hope this helps.|||In this situation, adding status to the index will not help, unless Brett thinks otherwise.

- CB
Originally posted by shekarnarayanan
Ok, here is a sample:

SELECT A.*, B.NAME
FROM Orders A,
Companies B
Where B.CompanyId = A.CompanyId
and B.Status = 1
ORDER BY B.NAME

Hope this helps.|||Quick question,.. why don't you try it and see what happens? Worse comes to worst you can just delete it afterwards...|||Agreed, just try it. Set up a test/dev environment. Run query before index added, look at query execution plan, apply index and look once again at query execution plan. It will help.|||Well, I tried as suggested and the execution plan does not seem to use the new index at all! It just uses the clustered PK index. So I guess the answer is NO to the new index.

Thanks for all the suggestions.|||SELECT *...

No, No, No...

Do you really need all of the columns?

If so, list them out...

Only use SELECT * for testing, analysis...

What's the DDL for the 2 tables?

And the optimizer is making the right call in your case

How many rows of data are we talking about?|||You say your column only holds ones and zeros. If it is a bit field it cannot be indexed. Even if it is not a bit field, if the distribution of values for one and zero are about 50%, the optimizer might not get much out of using the index. In a binary tree it would only save 1 search ply.

blindman|||Originally posted by Brett Kaiser
SELECT *...

No, No, No...

Do you really need all of the columns?

If so, list them out...

Only use SELECT * for testing, analysis...

What's the DDL for the 2 tables?

And the optimizer is making the right call in your case

How many rows of data are we talking about?

Hi Brett,
Thank you for your concern. Yes I do list all the fields and never use the * from my programs. Number of records in the comp. table is around 500 and the orders table may be few thousands. I also filter by company.|||On such a small number of records, you will not see much of an improvement. Anytime you have so a limited distribution like yes/no, male/female ... the optimizer will normally chose a table scan over an index (so normally the recommendation is No Way). Unless your distribution is very high for 1 value and very low for the other value, an index will only help for the low value anyway. If the distribution of these values are remotely close to each the optimizer will probably perform a table scan anyway. Since these tables are small, sql will probably chose a table scan over an index even if your distribution is ripe for an index.

Friday, March 9, 2012

INDEXES: Is this correct

Wednesday, March 7, 2012

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 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?
> >> >
> >> >
> >>
> >>
> >>
>
>

Wednesday, March 21, 2012

Indexing with null or zero

Indexing question

Indexing Question

Friday, March 9, 2012

INDEXES: Is this correct

INDEXES: Is this correct

INDEXES: Is this correct

Wednesday, March 7, 2012

Indexes confusion

Indexes confusion

Indexes confusion

Indexes and Deadlocks

Blog Archive

About Me