Showing posts with label identity. Show all posts
Showing posts with label identity. 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 size question (DDL)

This should be the DDL:
CREATE TABLE [dbo].[Transactions] (
[ID] [int] IDENTITY (1000, 1) NOT NULL ,
[SSN_TIN] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[ACCT_NUMBER] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[Settle_Date] [smalldatetime] NOT NULL ,
[SEQ_NUM] [int] NOT NULL ,
[FUND_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[TRANS_CODE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[INT_TYPE] [smallint] NOT NULL ,
[Trade_Date] [smalldatetime] NULL ,
[QUANTITY] [decimal](16, 6) NOT NULL ,
[PRICE] [decimal](21, 8) NOT NULL ,
[PROCEEDS] [money] NULL ,
[FIN_INST_ID] [smallint] NOT NULL ,
[FUND_CODE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[TradeMonth] [int] NULL ,
[Acct_Pre] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Transactions-Old] (
[SSN_TIN] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[ACCT_NUMBER] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[Settle_Date] [smalldatetime] NOT NULL ,
[SEQ_NUM] [int] NOT NULL ,
[FUND_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[TRANS_CODE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[INT_TYPE] [smallint] NOT NULL ,
[Trade_Date] [smalldatetime] NULL ,
[QUANTITY] [decimal](16, 6) NOT NULL ,
[PRICE] [decimal](21, 8) NOT NULL ,
[PROCEEDS] [money] NULL ,
[FIN_INST_ID] [smallint] NOT NULL ,
[FUND_CODE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[TradeMonth] AS (datepart(year,[Trade_Date]) * 100 + datepart
(month,[Trade_Date])) ,
[Acct_Pre] AS (left([Acct_Number],3))
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Transactions-Old] WITH NOCHECK ADD
CONSTRAINT [PK_Transactions] PRIMARY KEY CLUSTERED
(
[SSN_TIN],
[ACCT_NUMBER],
[Settle_Date],
[SEQ_NUM]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_CL_Transactions_TradeDate] ON [dbo].
[Transactions]([ID]) WITH FILLFACTOR = 80 ON [PRIMARY]
GO
ALTER TABLE [dbo].[Transactions] ADD
CONSTRAINT [PK_Transactions_ID] PRIMARY KEY NONCLUSTERED
(
[ID]
) ON [PRIMARY]
GO
DW <None> wrote in news:OupT2CjAEHA.3348@.TK2MSFTNGP11.phx.gbl:

> I have a SQL 2000 table with 16 million rows. I made a copy of it.
> The two tables have the same number of rows, 16,152,139 to be exact.
> The old table had a clustered, composite primary key across the first
> four columns -- ssn (char(9)), Acct Number (varchar 20), sequence
> number (varchar(20)), and transaction date (smalldatetime). The
> database size was 1,708,032 KB and the index was 8,520 KB.
> To the new table, I added an ID field of type Int, and made it the
> primary key nonclustered, also an identity field. The only other
> index is a different date field in the table that's a clustered index
> (smalldatetime). I also set the index fill factor to 80% from 90% in
> the old one.
> The new table takes 2,406,592 KB; it's bigger because of the extra
> field. BUT the index (as shown in the Task Pad summary) is 163,656
> KB. *How could two single-column indexes take 19 times the storage
> space as one 4-column composite index?* I have run dbcc dbreindex on
> the table.
> I also ran dbcc updateusage on the new table and got trivial
> differences.
> Here is what SHOWCONTIG gives, if that helps. Anything else I can
> look at?
> DBCC SHOWCONTIG scanning 'Transactions-Old' table...
> Table: 'Transactions-Old' (87671360); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 212443
> - Extents Scanned.......................: 26688
> - Extent Switches.......................: 26687
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.51% [26556:266
88]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.50%
> - Avg. Bytes Free per Page................: 766.4
> - Avg. Page Density (full)................: 90.53%
> DBCC SHOWCONTIG scanning 'Transactions' table...
> Table: 'Transactions' (711673583); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 280366
> - Extents Scanned.......................: 35103
> - Extent Switches.......................: 35102
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.84% [35046:351
03]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.11%
> - Avg. Bytes Free per Page................: 1562.7
> - Avg. Page Density (full)................: 80.69%
> DBCC SHOWCONTIG scanning 'Transactions' table...
> Table: 'Transactions' (711673583); index ID: 2, database ID: 7
> LEAF level scan performed.
> - Pages Scanned........................: 19966
> - Extents Scanned.......................: 2500
> - Extent Switches.......................: 2499
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.84% [2496:2500
]
> - Logical Scan Fragmentation ..............: 0.01%
> - Extent Scan Fragmentation ...............: 0.60%
> - Avg. Bytes Free per Page................: 6.2
> - Avg. Page Density (full)................: 99.92%
> I would post the DDL but I can't find the link to get the format...
> Thanks.
> David Walker
>Oops -- the new Transactions table actually has the ID (int) field
indexed twice, both clustered and non-clustered, with 2 different
indexes. Now how did that happen? :-)
I'll fix the indexes and check again.
David Walker
DW <None> wrote in news:ebCGMOjAEHA.3828@.TK2MSFTNGP10.phx.gbl:

> This should be the DDL:
> CREATE TABLE [dbo].[Transactions] (
> [ID] [int] IDENTITY (1000, 1) NOT NULL ,
> [SSN_TIN] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL
> ,
> [ACCT_NUMBER] [varchar] (20) COLLATE SQL_Latin1_General_CP1_C
I_AS
> NOT NULL ,
> [Settle_Date] [smalldatetime] NOT NULL ,
> [SEQ_NUM] [int] NOT NULL ,
> [FUND_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT
> NULL ,
> [TRANS_CODE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI
_AS
> NOT NULL ,
> [INT_TYPE] [smallint] NOT NULL ,
> [Trade_Date] [smalldatetime] NULL ,
> [QUANTITY] [decimal](16, 6) NOT NULL ,
> [PRICE] [decimal](21, 8) NOT NULL ,
> [PROCEEDS] [money] NULL ,
> [FIN_INST_ID] [smallint] NOT NULL ,
> [FUND_CODE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_
AS
> NULL ,
> [TradeMonth] [int] NULL ,
> [Acct_Pre] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Transactions-Old] (
> [SSN_TIN] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL
> ,
> [ACCT_NUMBER] [varchar] (20) COLLATE SQL_Latin1_General_CP1_C
I_AS
> NOT NULL ,
> [Settle_Date] [smalldatetime] NOT NULL ,
> [SEQ_NUM] [int] NOT NULL ,
> [FUND_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT
> NULL ,
> [TRANS_CODE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI
_AS
> NOT NULL ,
> [INT_TYPE] [smallint] NOT NULL ,
> [Trade_Date] [smalldatetime] NULL ,
> [QUANTITY] [decimal](16, 6) NOT NULL ,
> [PRICE] [decimal](21, 8) NOT NULL ,
> [PROCEEDS] [money] NULL ,
> [FIN_INST_ID] [smallint] NOT NULL ,
> [FUND_CODE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_
AS
> NULL ,
> [TradeMonth] AS (datepart(year,[Trade_Date]) * 100 + datepart
> (month,[Trade_Date])) ,
> [Acct_Pre] AS (left([Acct_Number],3))
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Transactions-Old] WITH NOCHECK ADD
> CONSTRAINT [PK_Transactions] PRIMARY KEY CLUSTERED
> (
> [SSN_TIN],
> [ACCT_NUMBER],
> [Settle_Date],
> [SEQ_NUM]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [IX_CL_Transactions_TradeDate] ON [dbo].
> [Transactions]([ID]) WITH FILLFACTOR = 80 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Transactions] ADD
> CONSTRAINT [PK_Transactions_ID] PRIMARY KEY NONCLUSTERED
> (
> [ID]
> ) ON [PRIMARY]
> GO
>
> DW <None> wrote in news:OupT2CjAEHA.3348@.TK2MSFTNGP11.phx.gbl:
>
>|||Um, I fixed the incorrect index to be a clustered index on the
Trade_Date column like it should have been, and the results are
essentially the same. I'm still confused.
Thanks for any insights.
David Walker
DW <None> wrote in news:ebCGMOjAEHA.3828@.TK2MSFTNGP10.phx.gbl:

> This should be the DDL:
> CREATE TABLE [dbo].[Transactions] (
> [ID] [int] IDENTITY (1000, 1) NOT NULL ,
> [SSN_TIN] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL
> ,
> [ACCT_NUMBER] [varchar] (20) COLLATE SQL_Latin1_General_CP1_C
I_AS
> NOT NULL ,
> [Settle_Date] [smalldatetime] NOT NULL ,
> [SEQ_NUM] [int] NOT NULL ,
> [FUND_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT
> NULL ,
> [TRANS_CODE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI
_AS
> NOT NULL ,
> [INT_TYPE] [smallint] NOT NULL ,
> [Trade_Date] [smalldatetime] NULL ,
> [QUANTITY] [decimal](16, 6) NOT NULL ,
> [PRICE] [decimal](21, 8) NOT NULL ,
> [PROCEEDS] [money] NULL ,
> [FIN_INST_ID] [smallint] NOT NULL ,
> [FUND_CODE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_
AS
> NULL ,
> [TradeMonth] [int] NULL ,
> [Acct_Pre] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Transactions-Old] (
> [SSN_TIN] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL
> ,
> [ACCT_NUMBER] [varchar] (20) COLLATE SQL_Latin1_General_CP1_C
I_AS
> NOT NULL ,
> [Settle_Date] [smalldatetime] NOT NULL ,
> [SEQ_NUM] [int] NOT NULL ,
> [FUND_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT
> NULL ,
> [TRANS_CODE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI
_AS
> NOT NULL ,
> [INT_TYPE] [smallint] NOT NULL ,
> [Trade_Date] [smalldatetime] NULL ,
> [QUANTITY] [decimal](16, 6) NOT NULL ,
> [PRICE] [decimal](21, 8) NOT NULL ,
> [PROCEEDS] [money] NULL ,
> [FIN_INST_ID] [smallint] NOT NULL ,
> [FUND_CODE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_
AS
> NULL ,
> [TradeMonth] AS (datepart(year,[Trade_Date]) * 100 + datepart
> (month,[Trade_Date])) ,
> [Acct_Pre] AS (left([Acct_Number],3))
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Transactions-Old] WITH NOCHECK ADD
> CONSTRAINT [PK_Transactions] PRIMARY KEY CLUSTERED
> (
> [SSN_TIN],
> [ACCT_NUMBER],
> [Settle_Date],
> [SEQ_NUM]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [IX_CL_Transactions_TradeDate] ON [dbo].
> [Transactions]([ID]) WITH FILLFACTOR = 80 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Transactions] ADD
> CONSTRAINT [PK_Transactions_ID] PRIMARY KEY NONCLUSTERED
> (
> [ID]
> ) ON [PRIMARY]
> GO
>
> DW <None> wrote in news:OupT2CjAEHA.3348@.TK2MSFTNGP11.phx.gbl:
>
>|||Hi David,
Thank you for using the newsgroup and it is my pleasure to help you with
you issue.
From you informaiton provided, you generate the SQL script from one table.
You noticed that the ID column in the following part:
CREATE CLUSTERED INDEX [IX_CL_Transactions_TradeDate] ON [dbo].
[Transactions]([ID]) WITH FILLFACTOR = 80 ON [PRIMARY]
GO
ALTER TABLE [dbo].[Transactions] ADD
CONSTRAINT [PK_Transactions_ID] PRIMARY KEY NONCLUSTERED
(
[ID]
) ON [PRIMARY]
GO
There is an clustered index and nonclustered index bulid on the table. You
wonder how it come, right?
To get the information on the table, you could run this statement in you
Query Analyzer:
exec sp_help transactions
OR
sp_helpindex transactions
From my experience, in the Enterprise Manger you have first create a
clustered index 'IX_CL_Transactions_TradeDate' on the column ID, then you
create a PRIMARY KEY constraint on this same column. So, finaly, you will
found that the 'sp_help transactions' or 'sp_helpindex transactions' will
show that the index on the column ID is non-clustered.
For maintenance purpose, you could run the DBCC INDEXDEFRAG
Hope this helps and if you still have questions, please feel free to post
your message here and I am glad to help.
Thanks.
Sincerely Yours
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Hello David,
Thanks to Gert for pointing you in the right direction. For
additional information, I would recommend you to read
"Estimating the Size of a Table with a Clustered Index" topic in the
Books On line which give some formula of calculating the
size of Clustered/Non-Clustered indexs in the Table. As per this
info for a Non-Clustered index
Total leaf index row size (Index_Row_Size) = CIndex_Row_Size +
Fixed_Key_Size + Variable_Key_Size + Index_Null_Bitmap + 1
The final value of 1 represents the index row header.
"CIndex_Row_Size is the total index row size for the clustered index
key".
That means NonClustered Index size would contain Clustered index row
size which is why you see larger size for non-clustered index.
Does that help answer your question ?
Thanks for using MSDN Newsgroup.
Vikrant Dalwale
Microsoft SQL Server Support Professional
Microsoft highly recommends to all of our customers that they visit
the http://www.microsoft.com/protect site and perform the three
straightforward steps listed to improve your computers security.
This posting is provided "AS IS" with no warranties, and confers no
rights.
--
>Subject: Re: Index size question (DDL)
>From: DW <None>
>References: <OupT2CjAEHA.3348@.TK2MSFTNGP11.phx.gbl>
<ebCGMOjAEHA.3828@.TK2MSFTNGP10.phx.gbl>
>User-Agent: Xnews/06.08.25
>Message-ID: <O6osh$jAEHA.1212@.TK2MSFTNGP12.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.server
>Date: Thu, 04 Mar 2004 15:24:44 -0800
>NNTP-Posting-Host: nensdsllascruces195.hyperspeeddsl.com
209.136.33.195
>Lines: 1
>Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGXA06.phx.gbl!TK2MSFTNGXA05.phx.gbl!TK2MS
FTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
>Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:332420
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Um, I fixed the incorrect index to be a clustered index on the
>Trade_Date column like it should have been, and the results are
>essentially the same. I'm still confused.
>Thanks for any insights.
>David Walker
>
>DW <None> wrote in news:ebCGMOjAEHA.3828@.TK2MSFTNGP10.phx.gbl:
>
SQL_Latin1_General_CP1_CI_AS[color=darkr
ed]
NOT
SQL_Latin1_General_CP1_CI_AS[color=darkr
ed]
SQL_Latin1_General_CP1_CI_AS[color=darkr
ed]
SQL_Latin1_General_CP1_CI_AS[color=darkr
ed]
NOT
SQL_Latin1_General_CP1_CI_AS[color=darkr
ed]
SQL_Latin1_General_CP1_CI_AS[color=darkr
ed]
it.
exact.
first
index
90% in
163,656
storage
dbreindex on
[26556:26688]
[35046:35103]
[2496:2500]
format...
>|||> info for a Non-Clustered index
> Total leaf index row size (Index_Row_Size) = CIndex_Row_Size +
> Fixed_Key_Size + Variable_Key_Size + Index_Null_Bitmap + 1

> That means NonClustered Index size would contain Clustered index row
> size which is why you see larger size for non-clustered index.
What exactly is "clustered index row size" and "index row size"? I
can't quite parse that... The clustered index is on a column, and all
rows have the same size ;-)
And larger, yes, but this much larger' hmmmmm...
Both tables, where one is a copy of the other, have clustered indexes.
The size of the first table's clustered index (on the first 4 columns)
is 8 meg. The size of the second tables two indexes, one clustered on a
smalldatetime field and the other non-clustered on Account Number and
SSN, is 137 meg. So the non-clustered index takes 129 meg.
Any more help would be great. Mr Wei, I didn't "wonder how it come", I
wondered why the one that should be smaller was so much bigger than the
other. I ran dbreindex, but I'll run indexdefrag as you suggested, and
see what that does.
Thanks.
David Walker
vikrantd@.online.microsoft.com (Vikrant V Dalwale [MSFT]) wrote in news:
#q#3CcTBEHA.4044@.cpmsftngxa06.phx.gbl:

>
> Hello David,
> Thanks to Gert for pointing you in the right direction. For
> additional information, I would recommend you to read
> "Estimating the Size of a Table with a Clustered Index" topic in the
> Books On line which give some formula of calculating the
> size of Clustered/Non-Clustered indexs in the Table. As per this
> info for a Non-Clustered index
> Total leaf index row size (Index_Row_Size) = CIndex_Row_Size +
> Fixed_Key_Size + Variable_Key_Size + Index_Null_Bitmap + 1
> The final value of 1 represents the index row header.
> "CIndex_Row_Size is the total index row size for the clustered index
> key".
> That means NonClustered Index size would contain Clustered index row
> size which is why you see larger size for non-clustered index.
> Does that help answer your question ?
> Thanks for using MSDN Newsgroup.
> Vikrant Dalwale
> Microsoft SQL Server Support Professional
>
> Microsoft highly recommends to all of our customers that they visit
> the http://www.microsoft.com/protect site and perform the three
> straightforward steps listed to improve your computers security.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> --
> <ebCGMOjAEHA.3828@.TK2MSFTNGP10.phx.gbl>
> 209.136.33.195
> cpmsftngxa06.phx.gbl!TK2MSFTNGXA06.phx.gbl!TK2MSFTNGXA05.phx.gbl!TK2MS
> FTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
> SQL_Latin1_General_CP1_CI_AS
> NOT
> SQL_Latin1_General_CP1_CI_AS
> SQL_Latin1_General_CP1_CI_AS
> SQL_Latin1_General_CP1_CI_AS
> NOT
> SQL_Latin1_General_CP1_CI_AS
> SQL_Latin1_General_CP1_CI_AS
> it.
> exact.
> first
> index
> 90% in
> 163,656
> storage
> dbreindex on
> [26556:26688]
> [35046:35103]
> [2496:2500]
> format...
>|||I posted before reading that BOL topic; sorry -- I'll check it out.
In addition to the formulas, what is the logical reason, that I can wrap my
head around, why a nonclustered index would be 19 times as large as a
clustered index?
Thanks.
David Walker|||You may want to read my reply again, and check out BOL on indexes.
Bottom line is, that your clustered index is not 8 MB, and so the NC
index is not 19 times larger. Only the branches (the pointers to the
lowest level index pages) are 8 MB in total. As mentioned before, no
system can possibly index a table with just 0.5 bytes per row (on
average). The index will need at least the same amount of space as the
(average) size of the indexed column.
Actually, your clustered index used 1716 MB (table data which includes
leafs of clustered index 1708MB + branches of clustered index 8MB). So
with 129 MB I would say your NC index is considerably smaller...
Gert-Jan
DW wrote:
> I posted before reading that BOL topic; sorry -- I'll check it out.
> In addition to the formulas, what is the logical reason, that I can wrap m
y
> head around, why a nonclustered index would be 19 times as large as a
> clustered index?
> Thanks.
> David Walker
(Please reply only to the newsgroup)|||OK, thanks for the info. I'm reading Inside SQL Server 2000, and when I'm
done, I should understand!
David
Gert-Jan Strik <sorry@.toomuchspamalready.nl> wrote in
news:4050DBB4.42F156BA@.toomuchspamalready.nl:

> You may want to read my reply again, and check out BOL on indexes.
> Bottom line is, that your clustered index is not 8 MB, and so the NC
> index is not 19 times larger. Only the branches (the pointers to the
> lowest level index pages) are 8 MB in total. As mentioned before, no
> system can possibly index a table with just 0.5 bytes per row (on
> average). The index will need at least the same amount of space as the
> (average) size of the indexed column.
> Actually, your clustered index used 1716 MB (table data which includes
> leafs of clustered index 1708MB + branches of clustered index 8MB). So
> with 129 MB I would say your NC index is considerably smaller...
> Gert-Jan
>
> DW wrote:
>

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

Friday, March 23, 2012

Index Question

Hello,
I have a 500,000 record table with the primary key being a bigint
identity column (clustered). I have another column (smallint) that it is not
unique and only has 20 possible values, this column is indexed in ascending
order.
When I do a select statement in the query analyzer filtering by the
smallint column, I notice that in the execution plan the index of this
column is not being used, it does only a clustered scan. Is it because the
smallint column is not unique? Other reason?
Thanks in advance...
Jose.Most likely because of the low selectivity on that index. With a very low
number of unique values compared to the number of rows in the table, doing
an index seek is probably more expensive than a table (or clustered index)
scan. This is particularly true if the query is not "covered" by the index
in question. You could verify this by using an index hint in your query and
looking at the execution plan compared to the execution plan for the full
scan.
"Jose Ines Cantu Arrambide" <joseine@.nospam.com> wrote in message
news:%23dAp9jULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a 500,000 record table with the primary key being a bigint
> identity column (clustered). I have another column (smallint) that it is
not
> unique and only has 20 possible values, this column is indexed in
ascending
> order.
> When I do a select statement in the query analyzer filtering by the
> smallint column, I notice that in the execution plan the index of this
> column is not being used, it does only a clustered scan. Is it because the
> smallint column is not unique? Other reason?
> Thanks in advance...
> Jose.
>|||Yes I did it and the smallint index turned out with 0% cost.
Thanks.
"Don Peterson" <no1@.nunya.com> wrote in message
news:%23bxFqwULEHA.3472@.TK2MSFTNGP09.phx.gbl...
> Most likely because of the low selectivity on that index. With a very low
> number of unique values compared to the number of rows in the table, doing
> an index seek is probably more expensive than a table (or clustered index)
> scan. This is particularly true if the query is not "covered" by the
index
> in question. You could verify this by using an index hint in your query
and
> looking at the execution plan compared to the execution plan for the full
> scan.
> "Jose Ines Cantu Arrambide" <joseine@.nospam.com> wrote in message
> news:%23dAp9jULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> > Hello,
> > I have a 500,000 record table with the primary key being a bigint
> > identity column (clustered). I have another column (smallint) that it is
> not
> > unique and only has 20 possible values, this column is indexed in
> ascending
> > order.
> > When I do a select statement in the query analyzer filtering by the
> > smallint column, I notice that in the execution plan the index of this
> > column is not being used, it does only a clustered scan. Is it because
the
> > smallint column is not unique? Other reason?
> >
> > Thanks in advance...
> > Jose.
> >
> >
>

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

Index on identity column

Hi everyone,
I am hoping that someone has come across the following situation and
can give me some insight on their findings. I have a table that logs a
lot of information that is then staged into a data warehouse. This
table receives about 10 - 20 inserts a second. Every 20 - 30 days, the
database inserts get jammed up and bring my server almost to a halt.
The inserts keep coming in but the server just cannot keep up with
heavy load. I have had this same setup do 100 transactions a second
with no problem so the hardware us up to par. After the slow down has
started, I can try rebooting the server, stopping services etc but
will not the fix the problem. The only thing that I have found to work
is to rebuild or defrag the index on the identity column. I have used
both a clustered and non clustered index on this column and the
behaviour is similiar after the 20 - 30 day timeframe. I realize that
as pages get fragmented it greatly slows down insert performance but I
don't see how a clustered index can get that fragmented on an identity
column that it would cause a slowdown as a result of page splitting
which I beleive to be the cause. Taking the index off this column is
not an option because I need to quickly scan this table for data
warehouse population. This table is now at 130,000,000 rows and I plan
to truncate this down every few months as I don't need the
transactional data for very long. So, I guess my question is is there
any advice that you can give me on maybe managing these indexes to
avoid this problem or is a rebuild or defrag my only option? BTW I am
running SQL Server 2000. Thank you.
CoreyHi Corey
What makes you think page splits are the problem? Have you looked at the
Performance Monitor counter Page Splits/second when you are having your
slowdowns and compared it to the page splitting rate when not having
problems. Have you run dbcc showcontig to see what kind of fragmentation you
are getting? Do you ever actually rebuild the index or indexes?
Actually, 20-30 days is a long time to not do any maintenance on an index
used for heavy insert operations. You're lucky you don't have problems more
often. I would suggest rebuilding the index on a weekly basis, with a
fillfactor less than full. I couldn't suggest a particular value for the
fillfactor, that would need to be determined by testing, but you might start
with rebuilding the index with a fillfactor of 75 (or 75% of the current
value, which SQL Server attempts to observe during defragging) and see how
that works.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Corey" <kildenc@.hotmail.com> wrote in message
news:fbb80b9b.0401051445.3bc0c22d@.posting.google.com...
> Hi everyone,
> I am hoping that someone has come across the following situation and
> can give me some insight on their findings. I have a table that logs a
> lot of information that is then staged into a data warehouse. This
> table receives about 10 - 20 inserts a second. Every 20 - 30 days, the
> database inserts get jammed up and bring my server almost to a halt.
> The inserts keep coming in but the server just cannot keep up with
> heavy load. I have had this same setup do 100 transactions a second
> with no problem so the hardware us up to par. After the slow down has
> started, I can try rebooting the server, stopping services etc but
> will not the fix the problem. The only thing that I have found to work
> is to rebuild or defrag the index on the identity column. I have used
> both a clustered and non clustered index on this column and the
> behaviour is similiar after the 20 - 30 day timeframe. I realize that
> as pages get fragmented it greatly slows down insert performance but I
> don't see how a clustered index can get that fragmented on an identity
> column that it would cause a slowdown as a result of page splitting
> which I beleive to be the cause. Taking the index off this column is
> not an option because I need to quickly scan this table for data
> warehouse population. This table is now at 130,000,000 rows and I plan
> to truncate this down every few months as I don't need the
> transactional data for very long. So, I guess my question is is there
> any advice that you can give me on maybe managing these indexes to
> avoid this problem or is a rebuild or defrag my only option? BTW I am
> running SQL Server 2000. Thank you.
> Corey|||What recovery mode are you in for the database? If it is a warehouse you
might be able to use simple recovery which will help you with the log
growing. Also, what other indexes are on the table? I would definitely
recommend keeping a clustered index on that table as heaps reclaim empty
space and if you are deleting and inserting that would cost you as well.
When you get to the point where you can not insert any records what is going
on with performance? As Kalen mentioned knowing if you are having page
splits or blocking/deadlocks will help you to remedy the situation.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Corey" <kildenc@.hotmail.com> wrote in message
news:fbb80b9b.0401051445.3bc0c22d@.posting.google.com...
> Hi everyone,
> I am hoping that someone has come across the following situation and
> can give me some insight on their findings. I have a table that logs a
> lot of information that is then staged into a data warehouse. This
> table receives about 10 - 20 inserts a second. Every 20 - 30 days, the
> database inserts get jammed up and bring my server almost to a halt.
> The inserts keep coming in but the server just cannot keep up with
> heavy load. I have had this same setup do 100 transactions a second
> with no problem so the hardware us up to par. After the slow down has
> started, I can try rebooting the server, stopping services etc but
> will not the fix the problem. The only thing that I have found to work
> is to rebuild or defrag the index on the identity column. I have used
> both a clustered and non clustered index on this column and the
> behaviour is similiar after the 20 - 30 day timeframe. I realize that
> as pages get fragmented it greatly slows down insert performance but I
> don't see how a clustered index can get that fragmented on an identity
> column that it would cause a slowdown as a result of page splitting
> which I beleive to be the cause. Taking the index off this column is
> not an option because I need to quickly scan this table for data
> warehouse population. This table is now at 130,000,000 rows and I plan
> to truncate this down every few months as I don't need the
> transactional data for very long. So, I guess my question is is there
> any advice that you can give me on maybe managing these indexes to
> avoid this problem or is a rebuild or defrag my only option? BTW I am
> running SQL Server 2000. Thank you.
> Corey

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

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

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

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