index and key: int

Showing posts with label int. Show all posts

Friday, March 30, 2012

Index Tuning

Hi All!
I have this table:
CREATE TABLE [dbo].[Constructions] (
[ConstructionID] [int] IDENTITY (1, 1) NOT NULL , --PK
[TypeID] [int] NOT NULL ,
[TerritoryID] [int] NOT NULL,
[BeginDate] [datetime] NOT NULL ,
[EndDate] [datetime] NOT NULL ,
[IsCancelled] [bit] NOT NULL
) ON [PRIMARY]
With over 1.000.000 records and daily inserts of 10.000 records.
The fields that are updated by users from time to time are BeginDate,
EndDate and IsCancelled.
This is a table of an online database and the indexes could by rebuild once
a day or so.
Here is the question:
What index would be the appropriate for this type of query (highly used):
SELECT
ConstructionID, TypeID, TerritoryID, BeginDate, EndDate
FROM
Constructions
WHERE
TerritoryID = @.TerritoryID
AND
EndDate < GETDATE()
AND
IsCancelled = 0I guess
1) Create an index on any very selective column. If something is very
selective then that's the only index you need!
2) Create in index on any combination of selective criteria. You don't need
everything from the WHERE clause but try and supply the combination of
columns that will yield a selective set!
3) If nothing is selective - even when combined - then cover the query!
"Jorgebg" <Jorgebg@.discussions.microsoft.com> wrote in message
news:C72F8FBF-5E27-4271-A5DA-BD2DD8A3AD3B@.microsoft.com...
> Hi All!
> I have this table:
> CREATE TABLE [dbo].[Constructions] (
> [ConstructionID] [int] IDENTITY (1, 1) NOT NULL , --PK
> [TypeID] [int] NOT NULL ,
> [TerritoryID] [int] NOT NULL,
> [BeginDate] [datetime] NOT NULL ,
> [EndDate] [datetime] NOT NULL ,
> [IsCancelled] [bit] NOT NULL
> ) ON [PRIMARY]
> With over 1.000.000 records and daily inserts of 10.000 records.
> The fields that are updated by users from time to time are BeginDate,
> EndDate and IsCancelled.
> This is a table of an online database and the indexes could by rebuild
> once
> a day or so.
> Here is the question:
> What index would be the appropriate for this type of query (highly used):
> SELECT
> ConstructionID, TypeID, TerritoryID, BeginDate, EndDate
> FROM
> Constructions
> WHERE
> TerritoryID = @.TerritoryID
> AND
> EndDate < GETDATE()
> AND
> IsCancelled = 0|||It is pure guesswork without being able to analyze the actual data in
the table, but I suspect that an index on (TerritoryID, IsCancelled,
EndDate) would be a good place to start.
If I had access to the table I would be trying to get a feel for the
data by running queries along the lines of:
select IsCancelled, count(*) as rows
from Constructions
group by IsCancelled
select count(distinct TerritoryID) from Constructions
select TerritoryID, count(*) as rows,
sum(case when IsCancelled = 1 then 1 else 0 end) as Cancelled
from Constructions
group by TerritoryID
order by 2 desc
The idea behind queries like this is to start to understand the data.
This includes how selective each column is and how evenly distributed
the data is. A table with 100 values for TerritoryID is one thing,
but if 1 TerritoryID out of the hundred has 80% of all rows that is
something else. Likewise knowing if 1%, or 50%, or 99% of the rows
are cancelled makes rather a large difference.
Roy Harvey
Beacon Falls, CT
On Tue, 6 Jun 2006 02:21:01 -0700, Jorgebg
<Jorgebg@.discussions.microsoft.com> wrote:

>Hi All!
>I have this table:
>CREATE TABLE [dbo].[Constructions] (
> [ConstructionID] [int] IDENTITY (1, 1) NOT NULL , --PK
> [TypeID] [int] NOT NULL ,
> [TerritoryID] [int] NOT NULL,
> [BeginDate] [datetime] NOT NULL ,
> [EndDate] [datetime] NOT NULL ,
> [IsCancelled] [bit] NOT NULL
> ) ON [PRIMARY]
>With over 1.000.000 records and daily inserts of 10.000 records.
>The fields that are updated by users from time to time are BeginDate,
>EndDate and IsCancelled.
>This is a table of an online database and the indexes could by rebuild once
>a day or so.
>Here is the question:
>What index would be the appropriate for this type of query (highly used):
>SELECT
> ConstructionID, TypeID, TerritoryID, BeginDate, EndDate
>FROM
> Constructions
>WHERE
> TerritoryID = @.TerritoryID
> AND
> EndDate < GETDATE()
> AND
> IsCancelled = 0|||These are good suggestions. Alternatively, you could try using the Database
Tuning Advisor (or the Indexing Tuning Wizard in SQL Server 2000) to tune
your indexes for your whole workload.
SQL Server 2005 also includes the "Missing Indexes" feature which is
suitable for the task. Check the SQL Server 2005 Books Online for details on
the above options.
Regards,
Leo
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:4joa825gpkmpqe6ojmm2ig00fs338873f9@.
4ax.com...
> It is pure guesswork without being able to analyze the actual data in
> the table, but I suspect that an index on (TerritoryID, IsCancelled,
> EndDate) would be a good place to start.
> If I had access to the table I would be trying to get a feel for the
> data by running queries along the lines of:
> select IsCancelled, count(*) as rows
> from Constructions
> group by IsCancelled
> select count(distinct TerritoryID) from Constructions
> select TerritoryID, count(*) as rows,
> sum(case when IsCancelled = 1 then 1 else 0 end) as Cancelled
> from Constructions
> group by TerritoryID
> order by 2 desc
> The idea behind queries like this is to start to understand the data.
> This includes how selective each column is and how evenly distributed
> the data is. A table with 100 values for TerritoryID is one thing,
> but if 1 TerritoryID out of the hundred has 80% of all rows that is
> something else. Likewise knowing if 1%, or 50%, or 99% of the rows
> are cancelled makes rather a large difference.
> Roy Harvey
> Beacon Falls, CT
>
> On Tue, 6 Jun 2006 02:21:01 -0700, Jorgebg
> <Jorgebg@.discussions.microsoft.com> wrote:
>|||On Tue, 6 Jun 2006 02:21:01 -0700, Jorgebg wrote:

>Hi All!
>I have this table:
>CREATE TABLE [dbo].[Constructions] (
> [ConstructionID] [int] IDENTITY (1, 1) NOT NULL , --PK
> [TypeID] [int] NOT NULL ,
> [TerritoryID] [int] NOT NULL,
> [BeginDate] [datetime] NOT NULL ,
> [EndDate] [datetime] NOT NULL ,
> [IsCancelled] [bit] NOT NULL
> ) ON [PRIMARY]
Hi Jorgebg,
The "--PK" comment suggests that the IDENTITY column is the primary key,
but you didn't declare it as such. SQL Server will not automatically
define a PRIMARY KEY constsaint for IDENTITY columns.
In addition to the identity surrogate key, your table should also have a
business key (and you should declare a UNIQUE constraint for it).
(snip)
>Here is the question:
>What index would be the appropriate for this type of query (highly used):
>SELECT
> ConstructionID, TypeID, TerritoryID, BeginDate, EndDate
>FROM
> Constructions
>WHERE
> TerritoryID = @.TerritoryID
> AND
> EndDate < GETDATE()
> AND
> IsCancelled = 0
Suggestion 1:
CREATE NONCLUSTERED INDEX ix1
ON Constructions (TerritoryID, IsCancelled, EndDate)
Suggestion 2 - assuming you don't have a CLUSUTERED index, or that you
can change it:
CREATE CLUSTERED INDEX ix2
ON Constructions (TerritoryID, IsCancelled, EndDate)
Suggestion 3.1 - assuming you have a CLUSTERED index on ConstructionID
and don't want to change it, AND assuming you're using SQL Server 2000:
CREATE NONCLUSTERED INDEX ix3_1
ON Constructions (TerritoryID, IsCancelled, EndDate, TypeID,
BeginDate)
Suggestion 3.2 assuming you have a CLUSTERED index on ConstructionID and
don't want to change it, AND assuming you're using SQL Server 2005
CREATE NONCLUSTERED INDEX ix3_1
ON Constructions (TerritoryID, IsCancelled, EndDate)
INCLUDE (TypeID, BeginDate)
Hugo Kornelis, SQL Server MVP|||I may be mistaken, but since isCancelled is a bit column, it is a poor
choice for an index. In fact, I don't think SQL Server will even allow it
(at least it does not on 2000). Otherwise, the advice you have gotten so
far should get you the performance that you need.
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:ti9d82d667kunlbek0mrfu6midipucq7g7@.
4ax.com...
> On Tue, 6 Jun 2006 02:21:01 -0700, Jorgebg wrote:
>
> Hi Jorgebg,
> The "--PK" comment suggests that the IDENTITY column is the primary key,
> but you didn't declare it as such. SQL Server will not automatically
> define a PRIMARY KEY constsaint for IDENTITY columns.
> In addition to the identity surrogate key, your table should also have a
> business key (and you should declare a UNIQUE constraint for it).
> (snip)
> Suggestion 1:
> CREATE NONCLUSTERED INDEX ix1
> ON Constructions (TerritoryID, IsCancelled, EndDate)
> Suggestion 2 - assuming you don't have a CLUSUTERED index, or that you
> can change it:
> CREATE CLUSTERED INDEX ix2
> ON Constructions (TerritoryID, IsCancelled, EndDate)
> Suggestion 3.1 - assuming you have a CLUSTERED index on ConstructionID
> and don't want to change it, AND assuming you're using SQL Server 2000:
> CREATE NONCLUSTERED INDEX ix3_1
> ON Constructions (TerritoryID, IsCancelled, EndDate, TypeID,
> BeginDate)
> Suggestion 3.2 assuming you have a CLUSTERED index on ConstructionID and
> don't want to change it, AND assuming you're using SQL Server 2005
> CREATE NONCLUSTERED INDEX ix3_1
> ON Constructions (TerritoryID, IsCancelled, EndDate)
> INCLUDE (TypeID, BeginDate)
>
> --
> Hugo Kornelis, SQL Server MVP|||On Wed, 7 Jun 2006 10:44:48 -0400, Jim Underwood wrote:

>I may be mistaken, but since isCancelled is a bit column, it is a poor
>choice for an index. In fact, I don't think SQL Server will even allow it
>(at least it does not on 2000).
Hi Jim,
Sorry, but you are wrong. Here's a script to prove it. If you execute
just the two SELECT statements with the option to show execution plan
on, you'll see that the index is not only create but also used.
CREATE TABLE BitTest
(PKCol int NOT NULL IDENTITY PRIMARY KEY,
BitCol bit NOT NULL)
go
CREATE INDEX x_bit ON BitTest (BitCol)
go
INSERT INTO BitTest (BitCol) VALUES (0)
INSERT INTO BitTest (BitCol) VALUES (0)
INSERT INTO BitTest (BitCol) VALUES (1)
INSERT INTO BitTest (BitCol) VALUES (1)
INSERT INTO BitTest (BitCol) VALUES (0)
INSERT INTO BitTest (BitCol) VALUES (1)
INSERT INTO BitTest (BitCol) VALUES (0)
go
-- Searched query - uses index s

on x_bit
SELECT * FROM BitTest WHERE BitCol = CAST(1 AS bit)
-- Query w/o WHERE - uses index scan on x_bit
SELECT * FROM BitTest
go
DROP TABLE BitTest
go
Here's the version of SQL Server I tested this on:
SELECT @.@.Version
go
Microsoft SQL Server 2000 - 8.00.2187 (Intel X86)
Mar 9 2006 11:38:51
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
Hugo Kornelis, SQL Server MVP|||Hugo,
Thanks for that example. The results were identical on my installation.
I was misled by this quote from BOL.
bit
Integer data type 1, 0, or NULL.
Remarks
Columns of type bit cannot have indexes on them.
That will teach me to take BOL for granted without testing first.
That said, would it be preferable to put the bit col as the last column in
the index, after EndDate? Where EndDate will be more selective than
isCancelled? Unless, of course, isCancelled has very few values of 0.
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:tqfe82p289pqt02752cshltfhaoroubl4n@.
4ax.com...
> On Wed, 7 Jun 2006 10:44:48 -0400, Jim Underwood wrote:
>
it
> Hi Jim,
> Sorry, but you are wrong. Here's a script to prove it. If you execute
> just the two SELECT statements with the option to show execution plan
> on, you'll see that the index is not only create but also used.
> CREATE TABLE BitTest
> (PKCol int NOT NULL IDENTITY PRIMARY KEY,
> BitCol bit NOT NULL)
> go
> CREATE INDEX x_bit ON BitTest (BitCol)
> go
> INSERT INTO BitTest (BitCol) VALUES (0)
> INSERT INTO BitTest (BitCol) VALUES (0)
> INSERT INTO BitTest (BitCol) VALUES (1)
> INSERT INTO BitTest (BitCol) VALUES (1)
> INSERT INTO BitTest (BitCol) VALUES (0)
> INSERT INTO BitTest (BitCol) VALUES (1)
> INSERT INTO BitTest (BitCol) VALUES (0)
> go
> -- Searched query - uses index s

on x_bit
> SELECT * FROM BitTest WHERE BitCol = CAST(1 AS bit)
> -- Query w/o WHERE - uses index scan on x_bit
> SELECT * FROM BitTest
> go
> DROP TABLE BitTest
> go
> Here's the version of SQL Server I tested this on:
> SELECT @.@.Version
> go
> Microsoft SQL Server 2000 - 8.00.2187 (Intel X86)
> Mar 9 2006 11:38:51
> Copyright (c) 1988-2003 Microsoft Corporation
> Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
> --
> Hugo Kornelis, SQL Server MVP|||> That said, would it be preferable to put the bit col as the last column in
> the index, after EndDate? Where EndDate will be more selective than
> isCancelled?
Possibly. Remember that statistics is only kept for the first column in the
index. So if you have:
WHERE bitcol = 1
AND othercol = 2786
Then SQL Server doesn't know the selectivity for the "othercol = 2768" condi
tion, and because of
that the cost estimation can be off. Having othercol as the first column in
the index mean
statistics is available for that column and selectivity for othercol = 2786
can be determined.
However, if the index is defined over (othercol, bitcol) and you have anothe
r query:
WHERE bitcol = 1
Then SQL Server cannot use that index, as bitcol isn't the first column in t
hat index.
Tradeoffs...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:ecQcasviGHA.4504@.TK2MSFTNGP03.phx.gbl...
> Hugo,
> Thanks for that example. The results were identical on my installation.
> I was misled by this quote from BOL.
> bit
> Integer data type 1, 0, or NULL.
> Remarks
> Columns of type bit cannot have indexes on them.
> That will teach me to take BOL for granted without testing first.
> That said, would it be preferable to put the bit col as the last column in
> the index, after EndDate? Where EndDate will be more selective than
> isCancelled? Unless, of course, isCancelled has very few values of 0.
>
> "Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
> news:tqfe82p289pqt02752cshltfhaoroubl4n@.
4ax.com...
> it
>|||Actually, my thought process was a little different, let me try to explain.
I may be way off here, but this is how I thought multicolumn indexes
worked...
We query for (yes, I realize this is different from the OP) :
TerritoryID = @.TerritoryID
AND EndDate = @.EndDate
AND IsCancelled = 0
Lets say we have 1,000,000 rows of data, and we have an index on
(TerritoryID, IsCancelled, EndDate)
@.TerritoryID narrows the search to 100 records
IsCancelled = 0 cuts that in half to 50 records
@.EndDate narrows those 50 records to 5 records
Now, lets say we have an index on (TerritoryID, EndDate, IsCancelled)
@.TerritoryID narrows the search to 100 records
@.EndDate narrows those 100 records to 10 records
IsCancelled = 0 cuts that in half to 5 records
In the case above wouldn't it be more efficient to have the date as the
second column in the index? Essentially, put the most selective column
before the others in the index. Of course, this assumes that SQL Server's
algorithms use the physical order of the columns to filter data, which may
not be the case.
Upon reviewing the query in the OP, it occurs to me that EndDate <= GetDate
may actually be less selective than IsCancelled = 0, and that for that
specific query having the bit column precede the date may be more efficient
after all.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OS58CwviGHA.1600@.TK2MSFTNGP04.phx.gbl...
in
> Possibly. Remember that statistics is only kept for the first column in
the index. So if you have:
> WHERE bitcol = 1
> AND othercol = 2786
> Then SQL Server doesn't know the selectivity for the "othercol = 2768"
condition, and because of
> that the cost estimation can be off. Having othercol as the first column
in the index mean
> statistics is available for that column and selectivity for othercol =
2786 can be determined.
> However, if the index is defined over (othercol, bitcol) and you have
another query:
> WHERE bitcol = 1
> Then SQL Server cannot use that index, as bitcol isn't the first column in
that index.
> Tradeoffs...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:ecQcasviGHA.4504@.TK2MSFTNGP03.phx.gbl...
in
allow
>

Wednesday, March 28, 2012

Index Seek (or) Index Scan in Execution Plan

Hi all,
I have one table. Where :
DonorID Int (Identity) Primary Key
FirstName Varchar(25)
LastName Varchar(25)
...
...
I have One nonclustred index on Lastname another nonclustred index on
(lastname, firstname).
Suppose I Execute the Query:
select * from TABLE where lastname like 'abott%' (This Query uses
Index Seek on the Compound Index)
But if I use the below Query:
select * from TABLE where lastname like 'smith%' (This Query uses
Index Scan)
But
select * from TABLE (index = ind_CMP_name) where lastname like 'smith%'
(But this Query uses the Index Seek)
NOTE: ind_CMP_name is the Compound Index.
Why there is the Difference, One Query uses Index Seek while other uses
Index Scan, even if both the query uses the same where condition on same
column?
Thanks
Prabhat
Hi all,
In Adition to Above Post / Question I have 2 More Questions:
1) Is the Index Seek is Faster or Index Scan? and Why?
2) How Can I Replace the Index = IndexName in the Above Post? (I Think the
Index = is used only for backward compatibility in SQL Server 2000)
Thanks in Advance for any Suggestion and help for these 2 posts...
Thanks
Prabhat
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:#J1NJu$vEHA.3096@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I have one table. Where :
> DonorID Int (Identity) Primary Key
> FirstName Varchar(25)
> LastName Varchar(25)
> ...
> ...
> I have One nonclustred index on Lastname another nonclustred index on
> (lastname, firstname).
> Suppose I Execute the Query:
> select * from TABLE where lastname like 'abott%' (This Query
uses
> Index Seek on the Compound Index)
> But if I use the below Query:
> select * from TABLE where lastname like 'smith%' (This Query uses
> Index Scan)
> But
> select * from TABLE (index = ind_CMP_name) where lastname like 'smith%'
> (But this Query uses the Index Seek)
> NOTE: ind_CMP_name is the Compound Index.
> Why there is the Difference, One Query uses Index Seek while other uses
> Index Scan, even if both the query uses the same where condition on same
> column?
> Thanks
> Prabhat
>
|||The 2 queries in your list are not the same. They are searching for
different rows, and a different number of rows will be returned for each.
This is called selectivity - If a very small percentage of rows in the table
will be returned ( 3-5%) then the query is very selective. Index Seeks are
better for very selective queries and index scans or better for queries with
low selectivity. SQL Server's optimizer is smart enough to figure this out
and (generally) choose a good plan..
see inline
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:%23ByYLXAwEHA.3908@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> In Adition to Above Post / Question I have 2 More Questions:
> 1) Is the Index Seek is Faster or Index Scan? and Why?
Index seek does a binary search from the root to the leaf level, a Scan
reads through part of all of the leaf level... So scans generally do more
IO than seeks.
> 2) How Can I Replace the Index = IndexName in the Above Post? (I Think the
> Index = is used only for backward compatibility in SQL Server 2000)
>
It is preferable to not use index hints, but if performance is killing
you...( update statistics first, then see if you get better response).
select yad yad from table WITH (index = whatever)
Be sure to use the with clause for compatilibility with SQL 2005
> Thanks in Advance for any Suggestion and help for these 2 posts...
> Thanks
> Prabhat
>
> "Prabhat" <not_a_mail@.hotmail.com> wrote in message
> news:#J1NJu$vEHA.3096@.TK2MSFTNGP14.phx.gbl...
> uses
>
|||Hi Wayne,
Thanks for your Suggestions.
Reg the 2 Queries:
Yes They are searching for different Rows. And the 1st Query is Retrieving 2
Rows while the 2nd Query returns 5622 Rows.
So As you told SQL Server optimizer will Use Index Seek for 1st Query and
Index Scan for 2nd Query?
Then If I use "Index=" Keyword in the Second Query then that the 2nd Query
uses the Index Seek. Why is like that?
And Now If I write :
select * from TABLE with(index = ind_CMP_name) where lastname like 'smith%'
So This is Better then using Only "Index=" as this is Also Supported in
2005?
Thanks
Prabhat
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:e6A#GRBwEHA.1400@.TK2MSFTNGP11.phx.gbl...
> The 2 queries in your list are not the same. They are searching for
> different rows, and a different number of rows will be returned for each.
> This is called selectivity - If a very small percentage of rows in the
table
> will be returned ( 3-5%) then the query is very selective. Index Seeks are
> better for very selective queries and index scans or better for queries
with[vbcol=seagreen]
> low selectivity. SQL Server's optimizer is smart enough to figure this out
> and (generally) choose a good plan..
> see inline
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Prabhat" <not_a_mail@.hotmail.com> wrote in message
> news:%23ByYLXAwEHA.3908@.TK2MSFTNGP12.phx.gbl...
> Index seek does a binary search from the root to the leaf level, a Scan
> reads through part of all of the leaf level... So scans generally do more
> IO than seeks.
the[vbcol=seagreen]
> It is preferable to not use index hints, but if performance is killing
> you...( update statistics first, then see if you get better response).
> select yad yad from table WITH (index = whatever)
> Be sure to use the with clause for compatilibility with SQL 2005
Query[vbcol=seagreen]
uses[vbcol=seagreen]
'smith%'[vbcol=seagreen]
uses[vbcol=seagreen]
same
>
|||Prabhat
If you use the (INDEX = ..) hint you are FORCING SQL Server to use the index
you tell it to use, whether or not that is a good choice. If you measure the
peformance (perhaps SET STATISTICS IO ON) you will see that when you force
the index, the performance is worse than when you let SQL Server make its
own choice, and it chooses to do the scan.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:uuZ%23H4BwEHA.2944@.TK2MSFTNGP12.phx.gbl...
> Hi Wayne,
> Thanks for your Suggestions.
> Reg the 2 Queries:
> Yes They are searching for different Rows. And the 1st Query is Retrieving
> 2
> Rows while the 2nd Query returns 5622 Rows.
> So As you told SQL Server optimizer will Use Index Seek for 1st Query and
> Index Scan for 2nd Query?
> Then If I use "Index=" Keyword in the Second Query then that the 2nd Query
> uses the Index Seek. Why is like that?
> And Now If I write :
> select * from TABLE with(index = ind_CMP_name) where lastname like
> 'smith%'
> So This is Better then using Only "Index=" as this is Also Supported in
> 2005?
> Thanks
> Prabhat
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:e6A#GRBwEHA.1400@.TK2MSFTNGP11.phx.gbl...
> table
> with
> the
> Query
> uses
> 'smith%'
> uses
> same
>
|||Hi Kalen,
Thanks for reply.
I use the "Index =" mainly for 2 reasons.
1) My SQL Query uses 2 Conditions in where clause. And I can see that there
is a Index Scan Involve in that Query. So I prefer "Index =" which make
Index Seek.
2) In Some cases My output should be Order by Lastname, FirstName. And Also
the query will have the Where Clause as above. So Here also i can see some
time it uses Index Scan. And I use a Compound Index on Lastname, Firstname -
To get the order. So I use the Index= in this case also.
You can see the Example of Query in the Main (TOP / original Post).
Kindly suggest.
Thanks
Prabhat
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:#h9EUmCwEHA.1264@.TK2MSFTNGP12.phx.gbl...
> Prabhat
> If you use the (INDEX = ..) hint you are FORCING SQL Server to use the
index
> you tell it to use, whether or not that is a good choice. If you measure
the[vbcol=seagreen]
> peformance (perhaps SET STATISTICS IO ON) you will see that when you force
> the index, the performance is worse than when you let SQL Server make its
> own choice, and it chooses to do the scan.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Prabhat" <not_a_mail@.hotmail.com> wrote in message
> news:uuZ%23H4BwEHA.2944@.TK2MSFTNGP12.phx.gbl...
Retrieving[vbcol=seagreen]
and[vbcol=seagreen]
Query[vbcol=seagreen]
each.[vbcol=seagreen]
Think[vbcol=seagreen]
on
>
|||Prabhat wrote:
> Hi Kalen,
> Thanks for reply.
> I use the "Index =" mainly for 2 reasons.
> 1) My SQL Query uses 2 Conditions in where clause. And I can see that
> there is a Index Scan Involve in that Query. So I prefer "Index ="
> which make Index Seek.
> 2) In Some cases My output should be Order by Lastname, FirstName.
> And Also the query will have the Where Clause as above. So Here also
> i can see some time it uses Index Scan. And I use a Compound Index on
> Lastname, Firstname - To get the order. So I use the Index= in this
> case also.
> You can see the Example of Query in the Main (TOP / original Post).
> Kindly suggest.
> Thanks
> Prabhat
>
Yes, you are correct that using the hint forces SQL Server to use the
index. But what Kalen is trying to explain to you is that using a
table/clustered index scan operation on the table when many rows are
returned is usually more cost effective for SQL Server. Unless you
dealing with a covering index, SQL Server has to perform a bookmark
lookup for each matching row. And all these bookmark lookups are very
costly when you consider SQL Server has to perform 5,000+ of them. In
that case, SQL Server chose to use a scan operation instead because it
is easier and faster for it to scan the table.
Now SQL Server does not always make the right decision. That's why
having updated statistics in your tables is important. But to force SQL
Server to always use the index misses the point. You are trying to
outthink the SQL Server query optimizer and that's a tough battle to win
in the long run.
David Gugick
Imceda Software
www.imceda.com
|||Thanks David for your Suggestion. Can U please tell me what exactly a
Covering Index? And Does that Help in my case?
Thanks
Prabhat
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:O3nP1vLwEHA.2944@.TK2MSFTNGP12.phx.gbl...
> Prabhat wrote:
> Yes, you are correct that using the hint forces SQL Server to use the
> index. But what Kalen is trying to explain to you is that using a
> table/clustered index scan operation on the table when many rows are
> returned is usually more cost effective for SQL Server. Unless you
> dealing with a covering index, SQL Server has to perform a bookmark
> lookup for each matching row. And all these bookmark lookups are very
> costly when you consider SQL Server has to perform 5,000+ of them. In
> that case, SQL Server chose to use a scan operation instead because it
> is easier and faster for it to scan the table.
> Now SQL Server does not always make the right decision. That's why
> having updated statistics in your tables is important. But to force SQL
> Server to always use the index misses the point. You are trying to
> outthink the SQL Server query optimizer and that's a tough battle to win
> in the long run.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Hi All,
Index Seek or Bookmark Lookup - Cost?
=============================
I have the below 2 Queries:
(1)
select top 100 donorid, firstname, lastname, state, zip, phonenum, country,
olddonorid, sourceid
from donor (index = ind_donor_name)
where lastname >= 'nath' and sourceid = 'flcc'
(2)
select top 100 donorid, firstname, lastname, state, zip, phonenum, country,
olddonorid, sourceid
from donor (index = ind_donor_name)
where lastname like 'nath%' and sourceid = 'flcc'
NOTE: ind_donor_name is the Compound Index on LastName, FirstName.
Even if Both the Queries are not Same in Where Condition, But Still refers
to the same Index. But I see a Different is Cost in Execution Plan.
that is:
the 1st Query Cost 1% in Index Seek and 99% in Bookmark Lookup.
But the 2nd Query Cost 51% in Index Seek and 49% in Bookmark Lookup.
[Note: Please refer the Discussions in this thread for more details...]
So As per the Above Cost Criteria which Plan is Best?
Thanks
Prabhat
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:#J1NJu$vEHA.3096@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I have one table. Where :
> DonorID Int (Identity) Primary Key
> FirstName Varchar(25)
> LastName Varchar(25)
> ...
> ...
> I have One nonclustred index on Lastname another nonclustred index on
> (lastname, firstname).
> Suppose I Execute the Query:
> select * from TABLE where lastname like 'abott%' (This Query
uses
> Index Seek on the Compound Index)
> But if I use the below Query:
> select * from TABLE where lastname like 'smith%' (This Query uses
> Index Scan)
> But
> select * from TABLE (index = ind_CMP_name) where lastname like 'smith%'
> (But this Query uses the Index Seek)
> NOTE: ind_CMP_name is the Compound Index.
> Why there is the Difference, One Query uses Index Seek while other uses
> Index Scan, even if both the query uses the same where condition on same
> column?
> Thanks
> Prabhat
>
|||Prabhat wrote:

>Hi Steve,
>My requrement is to search for "nath" (I know both the queries are
>different). In 1st case i am listing All > nath and in 2nd case like nath.
>That Does not matter.
>
But that's why the estimated execution costs are different. You haven't
shown the plans, but the plans may be identical, and just have different
costs because of the difference in the estimated number of rows
returned. If the queries return the same results, it's possible that
the actual running times are the same. Have you run the queries with
set statistics io on to see if there's a difference?
Sorry, but I still don't understand why if you want to search for
"nath", you are comparing plans that do something else.
SK

>Suppose I have 2 same queries with 2 diferent approach with that 2 Execution
>Plan, Then Which Plan I should go for?
>Some Additional Hint:
>1st Query Cost 98.35% relative to the batch
>while the 2nd Query Cost 1.65% relative to the Bacth.
>Thanks
>Prabhat
>
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:eupyWfOwEHA.2624@.TK2MSFTNGP11.phx.gbl...
>
>country,
>
>country,
>
>refers
>
>
>

Index Seek (or) Index Scan in Execution Plan

Hi all,
I have one table. Where :
DonorID Int (Identity) Primary Key
FirstName Varchar(25)
LastName Varchar(25)
...
...
I have One nonclustred index on Lastname another nonclustred index on
(lastname, firstname).
Suppose I Execute the Query:
select * from TABLE where lastname like 'abott%' (This Query uses
Index Seek on the Compound Index)
But if I use the below Query:
select * from TABLE where lastname like 'smith%' (This Query uses
Index Scan)
But
select * from TABLE (index = ind_CMP_name) where lastname like 'smith%'
(But this Query uses the Index Seek)
NOTE: ind_CMP_name is the Compound Index.
Why there is the Difference, One Query uses Index Seek while other uses
Index Scan, even if both the query uses the same where condition on same
column?
Thanks
PrabhatHi all,
In Adition to Above Post / Question I have 2 More Questions:
1) Is the Index Seek is Faster or Index Scan? and Why?
2) How Can I Replace the Index = IndexName in the Above Post? (I Think the
Index = is used only for backward compatibility in SQL Server 2000)
Thanks in Advance for any Suggestion and help for these 2 posts...
Thanks
Prabhat
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:#J1NJu$vEHA.3096@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I have one table. Where :
> DonorID Int (Identity) Primary Key
> FirstName Varchar(25)
> LastName Varchar(25)
> ...
> ...
> I have One nonclustred index on Lastname another nonclustred index on
> (lastname, firstname).
> Suppose I Execute the Query:
> select * from TABLE where lastname like 'abott%' (This Query
uses
> Index Seek on the Compound Index)
> But if I use the below Query:
> select * from TABLE where lastname like 'smith%' (This Query uses
> Index Scan)
> But
> select * from TABLE (index = ind_CMP_name) where lastname like 'smith%'
> (But this Query uses the Index Seek)
> NOTE: ind_CMP_name is the Compound Index.
> Why there is the Difference, One Query uses Index Seek while other uses
> Index Scan, even if both the query uses the same where condition on same
> column?
> Thanks
> Prabhat
>|||The 2 queries in your list are not the same. They are searching for
different rows, and a different number of rows will be returned for each.
This is called selectivity - If a very small percentage of rows in the table
will be returned ( 3-5%) then the query is very selective. Index Seeks are
better for very selective queries and index scans or better for queries with
low selectivity. SQL Server's optimizer is smart enough to figure this out
and (generally) choose a good plan..
see inline
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:%23ByYLXAwEHA.3908@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> In Adition to Above Post / Question I have 2 More Questions:
> 1) Is the Index Seek is Faster or Index Scan? and Why?
Index seek does a binary search from the root to the leaf level, a Scan
reads through part of all of the leaf level... So scans generally do more
IO than seeks.
> 2) How Can I Replace the Index = IndexName in the Above Post? (I Think the
> Index = is used only for backward compatibility in SQL Server 2000)
>
It is preferable to not use index hints, but if performance is killing
you...( update statistics first, then see if you get better response).
select yad yad from table WITH (index = whatever)
Be sure to use the with clause for compatilibility with SQL 2005
> Thanks in Advance for any Suggestion and help for these 2 posts...
> Thanks
> Prabhat
>
> "Prabhat" <not_a_mail@.hotmail.com> wrote in message
> news:#J1NJu$vEHA.3096@.TK2MSFTNGP14.phx.gbl...
> uses
>|||Hi Wayne,
Thanks for your Suggestions.
Reg the 2 Queries:
Yes They are searching for different Rows. And the 1st Query is Retrieving 2
Rows while the 2nd Query returns 5622 Rows.
So As you told SQL Server optimizer will Use Index Seek for 1st Query and
Index Scan for 2nd Query?
Then If I use "Index=" Keyword in the Second Query then that the 2nd Query
uses the Index Seek. Why is like that?
And Now If I write :
select * from TABLE with(index = ind_CMP_name) where lastname like 'smith%'
So This is Better then using Only "Index=" as this is Also Supported in
2005?
Thanks
Prabhat
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:e6A#GRBwEHA.1400@.TK2MSFTNGP11.phx.gbl...
> The 2 queries in your list are not the same. They are searching for
> different rows, and a different number of rows will be returned for each.
> This is called selectivity - If a very small percentage of rows in the
table
> will be returned ( 3-5%) then the query is very selective. Index Seeks are
> better for very selective queries and index scans or better for queries
with
> low selectivity. SQL Server's optimizer is smart enough to figure this out
> and (generally) choose a good plan..
> see inline
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Prabhat" <not_a_mail@.hotmail.com> wrote in message
> news:%23ByYLXAwEHA.3908@.TK2MSFTNGP12.phx.gbl...
> Index seek does a binary search from the root to the leaf level, a Scan
> reads through part of all of the leaf level... So scans generally do more
> IO than seeks.
the[vbcol=seagreen]
> It is preferable to not use index hints, but if performance is killing
> you...( update statistics first, then see if you get better response).
> select yad yad from table WITH (index = whatever)
> Be sure to use the with clause for compatilibility with SQL 2005
Query[vbcol=seagreen]
uses[vbcol=seagreen]
'smith%'[vbcol=seagreen]
uses[vbcol=seagreen]
same[vbcol=seagreen]
>|||Prabhat
If you use the (INDEX = ..) hint you are FORCING SQL Server to use the index
you tell it to use, whether or not that is a good choice. If you measure the
peformance (perhaps SET STATISTICS IO ON) you will see that when you force
the index, the performance is worse than when you let SQL Server make its
own choice, and it chooses to do the scan.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:uuZ%23H4BwEHA.2944@.TK2MSFTNGP12.phx.gbl...
> Hi Wayne,
> Thanks for your Suggestions.
> Reg the 2 Queries:
> Yes They are searching for different Rows. And the 1st Query is Retrieving
> 2
> Rows while the 2nd Query returns 5622 Rows.
> So As you told SQL Server optimizer will Use Index Seek for 1st Query and
> Index Scan for 2nd Query?
> Then If I use "Index=" Keyword in the Second Query then that the 2nd Query
> uses the Index Seek. Why is like that?
> And Now If I write :
> select * from TABLE with(index = ind_CMP_name) where lastname like
> 'smith%'
> So This is Better then using Only "Index=" as this is Also Supported in
> 2005?
> Thanks
> Prabhat
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:e6A#GRBwEHA.1400@.TK2MSFTNGP11.phx.gbl...
> table
> with
> the
> Query
> uses
> 'smith%'
> uses
> same
>|||Hi Kalen,
Thanks for reply.
I use the "Index =" mainly for 2 reasons.
1) My SQL Query uses 2 Conditions in where clause. And I can see that there
is a Index Scan Involve in that Query. So I prefer "Index =" which make
Index Seek.
2) In Some cases My output should be Order by Lastname, FirstName. And Also
the query will have the Where Clause as above. So Here also i can see some
time it uses Index Scan. And I use a Compound Index on Lastname, Firstname -
To get the order. So I use the Index= in this case also.
You can see the Example of Query in the Main (TOP / original Post).
Kindly suggest.
Thanks
Prabhat
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:#h9EUmCwEHA.1264@.TK2MSFTNGP12.phx.gbl...
> Prabhat
> If you use the (INDEX = ..) hint you are FORCING SQL Server to use the
index
> you tell it to use, whether or not that is a good choice. If you measure
the
> peformance (perhaps SET STATISTICS IO ON) you will see that when you force
> the index, the performance is worse than when you let SQL Server make its
> own choice, and it chooses to do the scan.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Prabhat" <not_a_mail@.hotmail.com> wrote in message
> news:uuZ%23H4BwEHA.2944@.TK2MSFTNGP12.phx.gbl...
Retrieving[vbcol=seagreen]
and[vbcol=seagreen]
Query[vbcol=seagreen]
each.[vbcol=seagreen]
Think[vbcol=seagreen]
on[vbcol=seagreen]
>|||Prabhat wrote:
> Hi Kalen,
> Thanks for reply.
> I use the "Index =" mainly for 2 reasons.
> 1) My SQL Query uses 2 Conditions in where clause. And I can see that
> there is a Index Scan Involve in that Query. So I prefer "Index ="
> which make Index Seek.
> 2) In Some cases My output should be Order by Lastname, FirstName.
> And Also the query will have the Where Clause as above. So Here also
> i can see some time it uses Index Scan. And I use a Compound Index on
> Lastname, Firstname - To get the order. So I use the Index= in this
> case also.
> You can see the Example of Query in the Main (TOP / original Post).
> Kindly suggest.
> Thanks
> Prabhat
>
Yes, you are correct that using the hint forces SQL Server to use the
index. But what Kalen is trying to explain to you is that using a
table/clustered index scan operation on the table when many rows are
returned is usually more cost effective for SQL Server. Unless you
dealing with a covering index, SQL Server has to perform a bookmark
lookup for each matching row. And all these bookmark lookups are very
costly when you consider SQL Server has to perform 5,000+ of them. In
that case, SQL Server chose to use a scan operation instead because it
is easier and faster for it to scan the table.
Now SQL Server does not always make the right decision. That's why
having updated statistics in your tables is important. But to force SQL
Server to always use the index misses the point. You are trying to
outthink the SQL Server query optimizer and that's a tough battle to win
in the long run.
David Gugick
Imceda Software
www.imceda.com|||Thanks David for your Suggestion. Can U please tell me what exactly a
Covering Index? And Does that Help in my case?
Thanks
Prabhat
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:O3nP1vLwEHA.2944@.TK2MSFTNGP12.phx.gbl...
> Prabhat wrote:
> Yes, you are correct that using the hint forces SQL Server to use the
> index. But what Kalen is trying to explain to you is that using a
> table/clustered index scan operation on the table when many rows are
> returned is usually more cost effective for SQL Server. Unless you
> dealing with a covering index, SQL Server has to perform a bookmark
> lookup for each matching row. And all these bookmark lookups are very
> costly when you consider SQL Server has to perform 5,000+ of them. In
> that case, SQL Server chose to use a scan operation instead because it
> is easier and faster for it to scan the table.
> Now SQL Server does not always make the right decision. That's why
> having updated statistics in your tables is important. But to force SQL
> Server to always use the index misses the point. You are trying to
> outthink the SQL Server query optimizer and that's a tough battle to win
> in the long run.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Hi All,
Index Seek or Bookmark Lookup - Cost?
=============================
I have the below 2 Queries:
(1)
select top 100 donorid, firstname, lastname, state, zip, phonenum, country,
olddonorid, sourceid
from donor (index = ind_donor_name)
where lastname >= 'nath' and sourceid = 'flcc'
(2)
select top 100 donorid, firstname, lastname, state, zip, phonenum, country,
olddonorid, sourceid
from donor (index = ind_donor_name)
where lastname like 'nath%' and sourceid = 'flcc'
NOTE: ind_donor_name is the Compound Index on LastName, FirstName.
Even if Both the Queries are not Same in Where Condition, But Still refers
to the same Index. But I see a Different is Cost in Execution Plan.
that is:
the 1st Query Cost 1% in Index Seek and 99% in Bookmark Lookup.
But the 2nd Query Cost 51% in Index Seek and 49% in Bookmark Lookup.
[Note: Please refer the Discussions in this thread for more details...]
So As per the Above Cost Criteria which Plan is Best?
Thanks
Prabhat
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:#J1NJu$vEHA.3096@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I have one table. Where :
> DonorID Int (Identity) Primary Key
> FirstName Varchar(25)
> LastName Varchar(25)
> ...
> ...
> I have One nonclustred index on Lastname another nonclustred index on
> (lastname, firstname).
> Suppose I Execute the Query:
> select * from TABLE where lastname like 'abott%' (This Query
uses
> Index Seek on the Compound Index)
> But if I use the below Query:
> select * from TABLE where lastname like 'smith%' (This Query uses
> Index Scan)
> But
> select * from TABLE (index = ind_CMP_name) where lastname like 'smith%'
> (But this Query uses the Index Seek)
> NOTE: ind_CMP_name is the Compound Index.
> Why there is the Difference, One Query uses Index Seek while other uses
> Index Scan, even if both the query uses the same where condition on same
> column?
> Thanks
> Prabhat
>|||Prabhat,
You are comparing apples to oranges. The queries are different, so
what do you mean "which plan is best"?
If I said "I can either pay for a new bicycle with a check or pay for
a new television with cash. Which is better?" -- well, it depends on
whether you need a bicycle or a television.
SK
Prabhat wrote:

>Hi All,
>Index Seek or Bookmark Lookup - Cost?
>=============================
>I have the below 2 Queries:
>(1)
>select top 100 donorid, firstname, lastname, state, zip, phonenum, country
,
>olddonorid, sourceid
>from donor (index = ind_donor_name)
>where lastname >= 'nath' and sourceid = 'flcc'
>(2)
>select top 100 donorid, firstname, lastname, state, zip, phonenum, country
,
>olddonorid, sourceid
>from donor (index = ind_donor_name)
>where lastname like 'nath%' and sourceid = 'flcc'
>NOTE: ind_donor_name is the Compound Index on LastName, FirstName.
>Even if Both the Queries are not Same in Where Condition, But Still refers
>to the same Index. But I see a Different is Cost in Execution Plan.
>that is:
>the 1st Query Cost 1% in Index Seek and 99% in Bookmark Lookup.
>But the 2nd Query Cost 51% in Index Seek and 49% in Bookmark Lookup.
>[Note: Please refer the Discussions in this thread for more details...]
>So As per the Above Cost Criteria which Plan is Best?
>Thanks
>Prabhat
>"Prabhat" <not_a_mail@.hotmail.com> wrote in message
>news:#J1NJu$vEHA.3096@.TK2MSFTNGP14.phx.gbl...
>
>uses
>
>
>

Index scans while using PreparedStatements

I have a query that does a 3-table join. The tables involved
are
1. ct_list_item(li_key int, li_code nvarchar(329))
li_key is the primary key
2. ct_list_item_lang(li_key int, lang_code varchar(5),
value nvarchar(64))
li_key and lang_code form a 2-part primary key.
3. ct_list_item_map(list_key int, li_key int, list_level int)
list_key and li_key form a 2-part primary key.
All of these tables have clustered indexes on their primary
keys.
Here's the query:
SELECT map.list_key, map.li_key, lil.value, li.li_code
FROM ct_list_item li
JOIN ct_list_item_map map on map.li_key = li.li_key
JOIN ct_list_item_lang lil on li.li_key = lil.li_key and
lil.lang_code='en'
WHERE map.list_key= 1011
I am finding that when I run the query using a JDBC
PreparedStatement with bind variables (on lang_code and
list_key), the query performs an index scan over the
clustered index on the ct_list_item_lang's primary key.
However if I run the query using a JDBC Statement without
bind variables, it does an clustered index seek. I am
puzzled as to why there is a difference.
Since we hard code values in the query statement, the SQL knows the values
before hand and can use Index seek. For
preparedstatement using parameters SQL has no knowledge about the value for
each
parameter during the preparation hence Index Scan is used. This results in
Preparedstatement running slower than regular Statement with hard coded
query.
If you need to use parameterized query in code, in stead of using ad hoc
query, you can create a stored proc and call it from Java code. This
should generate a plan using Index Seek which results in better performance.
sql

Index related problems? Whats happening here?

All queries for a particular table seems to be slow. It has one
clustered index on the primary key column which of data type INT and
has identity insert ON. This table has < 10000 rows and is fast with
response in all other circumstances. The clustered index is at a fill
factor of 90% and I have toyed upto 70% fillfactor.
When it is slow I ran DBCC SHOWCONTIG and there were signs of
fragmentation which didn't look very serious. The BOL says it is not
reliable for smaller tables.
I run DBCC INDEXDEFRAG on a particular database. The results suggest
that there were 72 pages and 72 pages were moved and 0 deleted. Still
no improvement in performance.
I run DBCC DBREINDEX and viola query runs fast... I am happy but what
is happening here?
All help is welcome and appreciated...
ThanksDid you do a lot of updates/inserts/deletes and you didn't update statistics?
http://sqlservercode.blogspot.com/
"MasterNone" wrote:
> All queries for a particular table seems to be slow. It has one
> clustered index on the primary key column which of data type INT and
> has identity insert ON. This table has < 10000 rows and is fast with
> response in all other circumstances. The clustered index is at a fill
> factor of 90% and I have toyed upto 70% fillfactor.
> When it is slow I ran DBCC SHOWCONTIG and there were signs of
> fragmentation which didn't look very serious. The BOL says it is not
> reliable for smaller tables.
> I run DBCC INDEXDEFRAG on a particular database. The results suggest
> that there were 72 pages and 72 pages were moved and 0 deleted. Still
> no improvement in performance.
> I run DBCC DBREINDEX and viola query runs fast... I am happy but what
> is happening here?
>
> All help is welcome and appreciated...
> Thanks
>|||I had been monitoring the inserts they are of the order of 10-11 for a
table of 7500 rows. There were the same number of updates but not to
the primary key/indexed column. Currently the Autoupdate Statistics
option is turned on.|||MasterNone wrote:
> I had been monitoring the inserts they are of the order of 10-11 for a
> table of 7500 rows. There were the same number of updates but not to
> the primary key/indexed column. Currently the Autoupdate Statistics
> option is turned on.
Please post table DDL and your slow queries. You should also look at the
query plan with QA. A common cause for the phenomenon you seem to observe
is that the index is not used at all.
Regards
robert

Monday, March 26, 2012

Index Question

Friday, March 23, 2012

Index Question

Hi,
I have the following table:
create table ProjectResource
(
ProjectCode nvarchar(20) not null,
RevisionNum int not null,
ResourceID nvarchar(15) not null,
ResourceSiteURN nvarchar(128) not null,
ActiveFlag int not null
default 1
constraint CK_ProjectResource_ActiveFlag check (ActiveFlag in (1,0)),
PrimaryFlag int not null
default 0
constraint CK_ProjectResource_PrimaryFlag check (PrimaryFlag in
(1,0)),
EPMProjectUID uniqueidentifier null ,
EPMResourceUID uniqueidentifier null ,
constraint PK_ProjectResource primary key (ProjectCode, RevisionNum,
ResourceID, ResourceSiteURN)
)
You will see that the PK constraint contains the ProjectCode, RevisionNum,
ResourceID, and ResourceSiteURN columns. I've been given a request to add a
new index on the table where it contains the following columns in the order
they are given: ProjectCode, RevisionNum, ResourceID, ResourceSiteURN,
PrimaryFlag.
What are the pros and cons of creating this new index?
Thanks in advance,
DeeThe pro is that an index that includes no more than those five columns
will be covered by the non-clustered index, and will perform a bit
faster.
The con is that the index takes up considerable space, and adds a
certain amount of overhead when rows are inserted or deleted.
Roy Harvey
Beacon Falls, CT
On Tue, 13 Nov 2007 11:02:02 -0800, bpdee
<bpdee@.discussions.microsoft.com> wrote:
>Hi,
>I have the following table:
>create table ProjectResource
>(
> ProjectCode nvarchar(20) not null,
> RevisionNum int not null,
> ResourceID nvarchar(15) not null,
> ResourceSiteURN nvarchar(128) not null,
> ActiveFlag int not null
> default 1
> constraint CK_ProjectResource_ActiveFlag check (ActiveFlag in (1,0)),
> PrimaryFlag int not null
> default 0
> constraint CK_ProjectResource_PrimaryFlag check (PrimaryFlag in
>(1,0)),
> EPMProjectUID uniqueidentifier null ,
> EPMResourceUID uniqueidentifier null ,
> constraint PK_ProjectResource primary key (ProjectCode, RevisionNum,
>ResourceID, ResourceSiteURN)
>)
>You will see that the PK constraint contains the ProjectCode, RevisionNum,
>ResourceID, and ResourceSiteURN columns. I've been given a request to add a
>new index on the table where it contains the following columns in the order
>they are given: ProjectCode, RevisionNum, ResourceID, ResourceSiteURN,
>PrimaryFlag.
>What are the pros and cons of creating this new index?
>Thanks in advance,
>Dee|||Thanks, Roy, for your quick response! I was a bit concerned at first since
we already have a clustered index on four of the five columns that are in the
non-clustered index. I thought that maybe this non-clustered index is
considered as a "duplicate" index.
"Roy Harvey (SQL Server MVP)" wrote:
> The pro is that an index that includes no more than those five columns
> will be covered by the non-clustered index, and will perform a bit
> faster.
> The con is that the index takes up considerable space, and adds a
> certain amount of overhead when rows are inserted or deleted.
> Roy Harvey
> Beacon Falls, CT
>
> On Tue, 13 Nov 2007 11:02:02 -0800, bpdee
> <bpdee@.discussions.microsoft.com> wrote:
> >Hi,
> >
> >I have the following table:
> >
> >create table ProjectResource
> >(
> > ProjectCode nvarchar(20) not null,
> > RevisionNum int not null,
> > ResourceID nvarchar(15) not null,
> > ResourceSiteURN nvarchar(128) not null,
> > ActiveFlag int not null
> > default 1
> > constraint CK_ProjectResource_ActiveFlag check (ActiveFlag in (1,0)),
> > PrimaryFlag int not null
> > default 0
> > constraint CK_ProjectResource_PrimaryFlag check (PrimaryFlag in
> >(1,0)),
> > EPMProjectUID uniqueidentifier null ,
> > EPMResourceUID uniqueidentifier null ,
> > constraint PK_ProjectResource primary key (ProjectCode, RevisionNum,
> >ResourceID, ResourceSiteURN)
> >)
> >
> >You will see that the PK constraint contains the ProjectCode, RevisionNum,
> >ResourceID, and ResourceSiteURN columns. I've been given a request to add a
> >new index on the table where it contains the following columns in the order
> >they are given: ProjectCode, RevisionNum, ResourceID, ResourceSiteURN,
> >PrimaryFlag.
> >
> >What are the pros and cons of creating this new index?
> >
> >Thanks in advance,
> >Dee
>|||On Tue, 13 Nov 2007 11:31:02 -0800, bpdee
<bpdee@.discussions.microsoft.com> wrote:
>Thanks, Roy, for your quick response! I was a bit concerned at first since
>we already have a clustered index on four of the five columns that are in the
>non-clustered index. I thought that maybe this non-clustered index is
>considered as a "duplicate" index.
I would not normally set up an index like that unless there was a
specific need for a covered index that it satisfied. Other than that
it really serves no purpose at all.
Roy Harvey
Beacon Falls, CT|||I agree with Roy. When the index key is narrow, and the row size
relatively wide, then such a "duplicate" index could be useful for
covering queries. However, in this case the row is only 36 bytes wider
than the index key of the "duplicate" index, which is probably not even
50% larger than the key size.
Had the primary key just been one int, then IMO it would have been a
different matter. As it is, I think adding this extra index is a waste
of space and will cause unnecessary contention for
inserts/updates/deletes.
BTW: I wonder why the two "Flag" columns are defined as int, and not as
something like tinyint.
--
Gert-Jan
bpdee wrote:
> Thanks, Roy, for your quick response! I was a bit concerned at first since
> we already have a clustered index on four of the five columns that are in the
> non-clustered index. I thought that maybe this non-clustered index is
> considered as a "duplicate" index.
> "Roy Harvey (SQL Server MVP)" wrote:
> > The pro is that an index that includes no more than those five columns
> > will be covered by the non-clustered index, and will perform a bit
> > faster.
> >
> > The con is that the index takes up considerable space, and adds a
> > certain amount of overhead when rows are inserted or deleted.
> >
> > Roy Harvey
> > Beacon Falls, CT
> >
> >
> > On Tue, 13 Nov 2007 11:02:02 -0800, bpdee
> > <bpdee@.discussions.microsoft.com> wrote:
> >
> > >Hi,
> > >
> > >I have the following table:
> > >
> > >create table ProjectResource
> > >(
> > > ProjectCode nvarchar(20) not null,
> > > RevisionNum int not null,
> > > ResourceID nvarchar(15) not null,
> > > ResourceSiteURN nvarchar(128) not null,
> > > ActiveFlag int not null
> > > default 1
> > > constraint CK_ProjectResource_ActiveFlag check (ActiveFlag in (1,0)),
> > > PrimaryFlag int not null
> > > default 0
> > > constraint CK_ProjectResource_PrimaryFlag check (PrimaryFlag in
> > >(1,0)),
> > > EPMProjectUID uniqueidentifier null ,
> > > EPMResourceUID uniqueidentifier null ,
> > > constraint PK_ProjectResource primary key (ProjectCode, RevisionNum,
> > >ResourceID, ResourceSiteURN)
> > >)
> > >
> > >You will see that the PK constraint contains the ProjectCode, RevisionNum,
> > >ResourceID, and ResourceSiteURN columns. I've been given a request to add a
> > >new index on the table where it contains the following columns in the order
> > >they are given: ProjectCode, RevisionNum, ResourceID, ResourceSiteURN,
> > >PrimaryFlag.
> > >
> > >What are the pros and cons of creating this new index?
> > >
> > >Thanks in advance,
> > >Dee
> >

Wednesday, March 21, 2012

Index on UDT

Hi,
I wanted to create an index on a property of my UDT and used this DDL:
create table t2(
c1 int identity,
c2 point,
c3 as c2.X persisted,
c4 as c2.Y persisted)
go
But I get this error:
Msg 4936, Level 16, State 1, Line 1
Computed column 'c3' in table 't2' cannot be persisted because the column is
non-deterministic.
X and Y are properties of Point UDT, but I cannot use
SqlFunction(IsDeterministic:=True) for that.
Any help would be greatly appreciated.
LeilaLook up SqlMethodAttribute in MSDN.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23fifF85LGHA.2320@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I wanted to create an index on a property of my UDT and used this DDL:
> create table t2(
> c1 int identity,
> c2 point,
> c3 as c2.X persisted,
> c4 as c2.Y persisted)
> go
> But I get this error:
> Msg 4936, Level 16, State 1, Line 1
> Computed column 'c3' in table 't2' cannot be persisted because the column
> is non-deterministic.
> X and Y are properties of Point UDT, but I cannot use
> SqlFunction(IsDeterministic:=True) for that.
> Any help would be greatly appreciated.
> Leila
>|||I used this attribute for X property but generated error: This attribute is
not valid on this declaration type.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23MLKkb9LGHA.500@.TK2MSFTNGP15.phx.gbl...
> Look up SqlMethodAttribute in MSDN.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23fifF85LGHA.2320@.TK2MSFTNGP11.phx.gbl...
>|||You need to set it on the get or set method of the property independently.
e.g.:
public int X
{
[SqlMethodAttribute(...)]
get
{
//...
}
set
{
//...
}
}
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23AgVJVBMGHA.964@.tk2msftngp13.phx.gbl...
>I used this attribute for X property but generated error: This attribute is
>not valid on this declaration type.
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:%23MLKkb9LGHA.500@.TK2MSFTNGP15.phx.gbl...
>|||Thanks indeed :-)
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OBV22nCMGHA.2628@.TK2MSFTNGP15.phx.gbl...
> You need to set it on the get or set method of the property independently.
> e.g.:
> public int X
> {
> [SqlMethodAttribute(...)]
> get
> {
> //...
> }
> set
> {
> //...
> }
> }
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23AgVJVBMGHA.964@.tk2msftngp13.phx.gbl...
>

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

Monday, March 19, 2012

Index Names not Schema Bound

I have a 2 Class tables in 2 different schemas and both have a ClassID field
as the primary key.
Staff.Class (ClassID int, ClassName varchar256))
RMS.Class (ClassID int, ClassName varchar256))
I have a primary key index on Staff.Class called pk__Class__ClassID. When I
try to create the same index on the RMS.Class table is says the name is
already used. Shouldnt the Index Name be schema bound like the table?
Hi John,
Index names are unique within the scope of the table on which it is defined.
There shouldn't be any problem having the same index name in different
tables even if they're in the same schema.
How are you creating your constraints? Below is a sample that does what
you want I think
USE AdventureWorks;
GO
CREATE SCHEMA Staff
CREATE SCHEMA RMS
GO
CREATE TABLE Staff.Class (ClassID int, ClassName varchar (256)
CONSTRAINT pk__Class__ClassID PRIMARY KEY (ClassID))
GO
CREATE TABLE RMS.Class (ClassID int, ClassName varchar (256)
CONSTRAINT pk__Class__ClassID PRIMARY KEY (ClassID))
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
news:6857A402-B019-4F9D-98D0-0CCB85D1E6BF@.microsoft.com...
>I have a 2 Class tables in 2 different schemas and both have a ClassID
>field
> as the primary key.
> Staff.Class (ClassID int, ClassName varchar256))
> RMS.Class (ClassID int, ClassName varchar256))
> I have a primary key index on Staff.Class called pk__Class__ClassID. When
> I
> try to create the same index on the RMS.Class table is says the name is
> already used. Shouldnt the Index Name be schema bound like the table?
|||Yes, it is possible to have both. Try this
create schema one
create schema two
create table one.class (id int constraint abc primary key, name varchar(30))
create table two.class (id int constraint abc primary key, name varchar(30))
select * from sys.objects
where name = 'abc'
create index xyz on one.class(id)
create index xyz on two.class(id)
select * from sys.indexes
where name = 'xyz'
Hope this helps,
Ben Nevarez
"John Barr" wrote:

> I have a 2 Class tables in 2 different schemas and both have a ClassID field
> as the primary key.
> Staff.Class (ClassID int, ClassName varchar256))
> RMS.Class (ClassID int, ClassName varchar256))
> I have a primary key index on Staff.Class called pk__Class__ClassID. When I
> try to create the same index on the RMS.Class table is says the name is
> already used. Shouldnt the Index Name be schema bound like the table?
|||I am simply going throug the diagrams interface and/or the design interface
in enterprise manager (sql server 2005) and attempting to create it. If you
try it, it should fail for you as well.
"Gail Erickson [MS]" wrote:

> Hi John,
> Index names are unique within the scope of the table on which it is defined.
> There shouldn't be any problem having the same index name in different
> tables even if they're in the same schema.
> How are you creating your constraints? Below is a sample that does what
> you want I think
> USE AdventureWorks;
> GO
> CREATE SCHEMA Staff
> CREATE SCHEMA RMS
> GO
> CREATE TABLE Staff.Class (ClassID int, ClassName varchar (256)
> CONSTRAINT pk__Class__ClassID PRIMARY KEY (ClassID))
> GO
> CREATE TABLE RMS.Class (ClassID int, ClassName varchar (256)
> CONSTRAINT pk__Class__ClassID PRIMARY KEY (ClassID))
>
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
> "John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
> news:6857A402-B019-4F9D-98D0-0CCB85D1E6BF@.microsoft.com...
>
>
|||I don't believe the diagrams interface (nor some of the underlying functions
for the visual designers) have been updated to understand what "schema" is,
or at least cope with it correctly, so it is probably checking against the
following logic:
if there exists a table named foo with an index named bar, return an error,
otherwise continue
If you are going to be developing with schemas I would suggest learning and
using the SQL DDL and supporting syntax instead of using diagrams and visual
helpers.
A
"John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
news:B9B22398-F27E-43F5-8F59-C25CE18AF80F@.microsoft.com...[vbcol=seagreen]
>I am simply going throug the diagrams interface and/or the design interface
> in enterprise manager (sql server 2005) and attempting to create it. If
> you
> try it, it should fail for you as well.
> "Gail Erickson [MS]" wrote:
|||I just tested creating the tables using the database diagram and also worked
for me: I have the two primary keys with the same name.
When you create the table using a database diagram you need to select the
schema from the Properties window.
Ben Nevarez
"Aaron Bertrand [SQL Server MVP]" wrote:

> I don't believe the diagrams interface (nor some of the underlying functions
> for the visual designers) have been updated to understand what "schema" is,
> or at least cope with it correctly, so it is probably checking against the
> following logic:
> if there exists a table named foo with an index named bar, return an error,
> otherwise continue
> If you are going to be developing with schemas I would suggest learning and
> using the SQL DDL and supporting syntax instead of using diagrams and visual
> helpers.
> A
>
>
>
> "John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
> news:B9B22398-F27E-43F5-8F59-C25CE18AF80F@.microsoft.com...
>
|||> I just tested creating the tables using the database diagram and also
> worked
> for me: I have the two primary keys with the same name.
> When you create the table using a database diagram you need to select the
> schema from the Properties window.
Ok, there are several items on Connect that seem to suggest that some of
this stuff is broken and won't be fixed, at least for 2008. <shrug>
In any case, it doesn't mean that using proper DDL instead of visual tools
is a bad alternative. At least you can check DDL into source control.

Friday, March 9, 2012

Index for username/password

Does this make sense for a logon table:

CREATE TABLE Logon
(
ID INT NOT NULL IDENTITY PRIMARY KEY,
name VARCHAR(15) NOT NULL,
password VARCHAR(15) NOT NULL
)
GO
CREATE UNIQUE INDEX IX_Logon_Name ON Logon(name)
CREATE INDEX IX_Logon_NameAndPassword ON Logon(name,password)
GO

I do want the name to be unique but also will search frequently on both
name & password. Is this how it should be done? I don't fully
understand the difference between placing a single index in name &
password VS one on both name & password.Cecil (cecilkain0@.yahoo.com) writes:
> Does this make sense for a logon table:
> CREATE TABLE Logon
> (
> ID INT NOT NULL IDENTITY PRIMARY KEY,
> name VARCHAR(15) NOT NULL,
> password VARCHAR(15) NOT NULL
> )
> GO
> CREATE UNIQUE INDEX IX_Logon_Name ON Logon(name)
> CREATE INDEX IX_Logon_NameAndPassword ON Logon(name,password)
> GO
> I do want the name to be unique but also will search frequently on both
> name & password. Is this how it should be done? I don't fully
> understand the difference between placing a single index in name &
> password VS one on both name & password.

I don't see the purpose of the ID column? Why not make the name the primary
key?

The index on (name, password) does not seem very useful here. Usually an
index on the form (uniquecolumn, othercolumn) is not meaningful, but it
can be sometimes, to achieved so-called covered queries. But as long as
the table does not have lots of other columns, it's difficult to see a
case for it here.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

I was thinking of doing that, but I intend for the Logon table to be
like an ID card. Only for efficient identification. I wanted to reuse
this table design in multiple projects that would require
authentication.

So if I later had an employee say, that needs to login, rather than add
a username,password to the Employee table I could simply add a LogonID
field to the employee table to link it w/ their identification record
in the Logon table.

Do you think this is a bad idea?

Also I thought it would be faster to always use an int ID as my primary
key instead of a string for searching and joining.

If I were to have a foreign key linking to the logon table I'd have to
stick the whole string as the foreign key instead of just an int. So it
was my plan to make sure each table had an int primary key even if it
was possible to uniquely id a record by an already present column like
username.

Again, do you think this is a bad idea? What would you name the foreign
key to a varchar username field? usernameID? It just seems like it
should be a number to me if it has ID appended to it. I like using ID
becuase I know it is a key of somekind when I see it but maybe I
shouldn't do that.

I was reading a post by someone earlier who suggested to me that all
field names be unique across my schema. So if I understand him
correctly:

LogonID, LogonName, & LogonPassword would be better field names.
LogonPassword seems sorta like overkill compared to just password but
if you're going to be unique you might have another field called
password in another table so I guess you'd have to do it that way.
Almost like table-qualifying each field name.

I'm starting a simple DB from scratch so I'm trying to use as good a
practices as I can and would be very interested in your reccomendations
Erland. Thanks.|||Cecil wrote:

> >>I don't see the purpose of the ID column? Why not make the name the primary
> >>key?
> I was thinking of doing that, but I intend for the Logon table to be
> like an ID card. Only for efficient identification. I wanted to reuse
> this table design in multiple projects that would require
> authentication.

Name would still be unique though wouldn't it? So it should still have
a unique constraint on name.

Storing passwords in the database is an inherent security flaw. Don't
store them, encrypted or otherwise. If you must, store a secure hash of
the password. If you are using SQL Server 2005 then use the built in
encryption / authentication. Where possible, use integrated security
rather than invent your own.

--
David Portas
SQL Server MVP
--|||David Portas wrote:

> Cecil wrote:
> > >>I don't see the purpose of the ID column? Why not make the name the primary
> > >>key?
> > I was thinking of doing that, but I intend for the Logon table to be
> > like an ID card. Only for efficient identification. I wanted to reuse
> > this table design in multiple projects that would require
> > authentication.
> Name would still be unique though wouldn't it? So it should still have
> a unique constraint on name.

Apologies, I see that you have declared a unique INDEX on name. A
unique CONSTRAINT is virtually equivalent however and is usually the
preferred choice rather than an index.

--
David Portas
SQL Server MVP
--|||I agree Windows Auth is the way to go, but this DB is for a website and
as such, Windows Auth is not practical.
I was planning to encrypt the password using .NET before storing it in
the DB.

I'm not sure what the built in encryption / authentication SQL2005 has
other than Windows Auth. Is there another feature?

I used an unique index on name because I wished to have fast lookups of
names. I thought an index was how to best accomplish this, No?

I'm not possitive when to use indexex on a column and when to do so on
multiple columns. I don't get the difference.|||I'd still have the 'ID' column but make it a surrogate key instead and use
that on other tables, may be a permissions, for example...

create table Logon (
id int not null identity constraint sk_logon unique clustered,

name varchar(15) not null constraint pk_logon primary key
nonclustered,

password varchar(15) not null
)

In other tables you would use Logon.id and not Logon.name, so if you had a
permissions table say you'd do it like this...

create table Permission (
id int not null identity constraint sk_permission unique
nonclustered,

logon_id int not null references Logon( id ),
security_ticket_id int not null references SecurityTicket ( id ),

constraint pk_Permission primary key clustered ( logon_id,
security_id )
)

Then in the application use 'id' everywhere, it encapsulates the data and
allows for 'name' to change without breaking the application logic.

Tony.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns973A9A6DF18F1Yazorman@.127.0.0.1...
> Cecil (cecilkain0@.yahoo.com) writes:
>> Does this make sense for a logon table:
>>
>> CREATE TABLE Logon
>> (
>> ID INT NOT NULL IDENTITY PRIMARY KEY,
>> name VARCHAR(15) NOT NULL,
>> password VARCHAR(15) NOT NULL
>> )
>> GO
>> CREATE UNIQUE INDEX IX_Logon_Name ON Logon(name)
>> CREATE INDEX IX_Logon_NameAndPassword ON Logon(name,password)
>> GO
>>
>> I do want the name to be unique but also will search frequently on both
>> name & password. Is this how it should be done? I don't fully
>> understand the difference between placing a single index in name &
>> password VS one on both name & password.
> I don't see the purpose of the ID column? Why not make the name the
> primary
> key?
> The index on (name, password) does not seem very useful here. Usually an
> index on the form (uniquecolumn, othercolumn) is not meaningful, but it
> can be sometimes, to achieved so-called covered queries. But as long as
> the table does not have lots of other columns, it's difficult to see a
> case for it here.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Yeah I think that's a good idea Tony.
That's essentially what I had in mind, perhaps making the ID a
surrogate key does better model what I'm doing w/ it.|||Cecil (cecilkain0@.yahoo.com) writes:
> So if I later had an employee say, that needs to login, rather than add
> a username,password to the Employee table I could simply add a LogonID
> field to the employee table to link it w/ their identification record
> in the Logon table.
> Do you think this is a bad idea?

The ID is superfluous when you have a natural key in the username.
Sometimes surrogates keys are called for.

> Also I thought it would be faster to always use an int ID as my primary
> key instead of a string for searching and joining.

Or it's slower. Say you want to display list which includes the username.
If the username is the foreign key, it's already in the table. With an
ID, you will have to join to the Logins table. And the ID column makes
the table larger, and more space means worse performacne.

The true story, that this is the wrong place to look for performance in,
Whatever you do, it is not likely to have any measurable effect, as I
suspect the volumes will be modest here. Manageability is much more
important, and a username without ID appears more manageable here. The one
case where an ID is nicer, is when a user wants to change his username.

> If I were to have a foreign key linking to the logon table I'd have to
> stick the whole string as the foreign key instead of just an int. So it
> was my plan to make sure each table had an int primary key even if it
> was possible to uniquely id a record by an already present column like
> username.

That's a bad plan. Surrogates are sometimes called for. For instance,
an Orders table typically as an integer key generated by the system.
But an OrderDetails table should have a two-column key with OrderID
and RowNumber (or ProductId).

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Cecil (cecilkain0@.yahoo.com) writes:
> I agree Windows Auth is the way to go, but this DB is for a website and
> as such, Windows Auth is not practical.
> I was planning to encrypt the password using .NET before storing it in
> the DB.
> I'm not sure what the built in encryption / authentication SQL2005 has
> other than Windows Auth. Is there another feature?

SQL 2005 has a whole slew of encryption stuff with asymmetric keys,
symmetric keys, certificates and God knows what. And they are not
dependent on how you log in.

Encryption is not my best subject, but you are probably right encrypting
the password already in the app. Sending it in clear text over the wire
is not that good.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Consider a list of names in say a drop down list, you would code the value
part as the 'id' and the text part as the 'name'.

When a user selects an entry from the drop down you pass the 'id' back to
the database and not the 'name', you then use the 'id' on the query etc...
Basically, the 'name' is used only as meta data for display; of course, if
its a textbox then the user enters the 'name' and thats used, but
applications tend not to work like that - most choices are drop downs,
checkboxes, radio buttons; users don't always remember the full text of
'name'.

Now bear the above in mind and re-read your reasoning, suddenly you have
very narrow tables and you get better performance because joins are on 4
bytes rather than 20 / 30 etc... storage is reduced because of the same
reason. When passing back results, all the joining is done on the 'id' and
you only pass back the 'name' for the small subset of data you are
presenting to the user.

An example schema is as follows :-

create table Logon (
id int not null identity constraint sk_logon unique clustered,

name varchar(15) not null constraint pk_logon primary key
nonclustered,

password varchar(15) not null
)

In other tables you would use Logon.id and not Logon.name, so if you had a
permissions table say you'd do it like this...

create table Permission (
id int not null identity constraint sk_permission unique
nonclustered,

logon_id int not null references Logon( id ),
security_ticket_id int not null references SecurityTicket ( id ),

constraint pk_Permission primary key clustered ( logon_id,
security_id )
)

I think, if I have time I'll write an article over this surrogate key stuff
and how it should be used in the application - it seems to be one of the
biggest misunderstood methods in the db space at the moment.

Tony.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns973AE2E0716BBYazorman@.127.0.0.1...
> Cecil (cecilkain0@.yahoo.com) writes:
>> So if I later had an employee say, that needs to login, rather than add
>> a username,password to the Employee table I could simply add a LogonID
>> field to the employee table to link it w/ their identification record
>> in the Logon table.
>>
>> Do you think this is a bad idea?
> The ID is superfluous when you have a natural key in the username.
> Sometimes surrogates keys are called for.
>> Also I thought it would be faster to always use an int ID as my primary
>> key instead of a string for searching and joining.
> Or it's slower. Say you want to display list which includes the username.
> If the username is the foreign key, it's already in the table. With an
> ID, you will have to join to the Logins table. And the ID column makes
> the table larger, and more space means worse performacne.
> The true story, that this is the wrong place to look for performance in,
> Whatever you do, it is not likely to have any measurable effect, as I
> suspect the volumes will be modest here. Manageability is much more
> important, and a username without ID appears more manageable here. The one
> case where an ID is nicer, is when a user wants to change his username.
>> If I were to have a foreign key linking to the logon table I'd have to
>> stick the whole string as the foreign key instead of just an int. So it
>> was my plan to make sure each table had an int primary key even if it
>> was possible to uniquely id a record by an already present column like
>> username.
> That's a bad plan. Surrogates are sometimes called for. For instance,
> an Orders table typically as an integer key generated by the system.
> But an OrderDetails table should have a two-column key with OrderID
> and RowNumber (or ProductId).
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Tony Rogerson (tonyrogerson@.torver.net) writes:
> The ID thing is a misconception by many DBA's, what really happens in an
> application is this...
> Consider a list of names in say a drop down list, you would code the value
> part as the 'id' and the text part as the 'name'.
> When a user selects an entry from the drop down you pass the 'id' back to
> the database and not the 'name', you then use the 'id' on the query etc...
> Basically, the 'name' is used only as meta data for display; of course, if
> its a textbox then the user enters the 'name' and thats used, but
> applications tend not to work like that - most choices are drop downs,
> checkboxes, radio buttons; users don't always remember the full text of
> 'name'.
> Now bear the above in mind and re-read your reasoning, suddenly you have
> very narrow tables and you get better performance because joins are on 4
> bytes rather than 20 / 30 etc... storage is reduced because of the same
> reason.

There are of course lots of situations where this strategy is the way
to go. For instance, say that users want to be able to define customer
groups and add customers to them, for statistical purposes or whatever.
Since it is likely that the user would like to have long descriptive
names for their groups, the names are not really good for a key. Not
the least since the users may want to change the group names everyonce
in a while.

A username in a login table is a little different. Usernames are
typically fairly short. They are also less prone to changes. In fact,
you could consider it a business rules that they should not change.

There is always a trade-off in these situations. An id may take up
less space - but you will have to join to the Logins table each time.
And performance is not everything. One advantage with using the login
name as key, is that when you review auditing data or columns, you
see the username directly without joining. The same argument applies
to a customer group as well, but I far more have reason to look at
user-id columns from Query Analyzer than customer-groups ids.

> I think, if I have time I'll write an article over this surrogate key
> stuff and how it should be used in the application - it seems to be one
> of the biggest misunderstood methods in the db space at the moment.

I think most knowledgeable SQL users knows this concept well. The
difficult part is to know when to use it, and when to not. Usernames
is a case where I think a surrogate is a bad idea.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I suspect you're both right. I'm much more of a programmer than a DBA
which is why I like to get opinions on table deign from more
knowledgable people than myself. But I think Tony is right in implying
as a programmer your table design decisions may seem odd when in fact
the "extra key" in a table to a web-app may be a small price to pay for
an efficient and reliable key in a long list of items on a web page, or
even a rich client for that matter.

Friday, March 30, 2012

Index Tuning

Wednesday, March 28, 2012

Index Seek (or) Index Scan in Execution Plan

Index Seek (or) Index Scan in Execution Plan

Index scans while using PreparedStatements

Index related problems? Whats happening here?

Monday, March 26, 2012

Index Question

Friday, March 23, 2012

Index Question

Index Question

Wednesday, March 21, 2012

Index on UDT

index on table variable

Monday, March 19, 2012

Index Names not Schema Bound

Friday, March 9, 2012

Index for username/password

index and key

Blog Archive

About Me