index and key: null

Showing posts with label null. 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
>

index statistics are created when?

If the auto create stats and auto update stats is off (actually the value is null) at the db level - are stats generated when an index is recreated?

Additionaly, running the sp_autostats on the above db shows that the auto update stats are on. Where is the option to set it on for an object rather than the db?

Mike

I just read by Kalen that if the options are false (off) this overrides the table level. Still not clear on what occurs when options are <null>True when you run DBCC DBREINDEX those stats will be updated too.

SP_AUTOSTATS is used to display or change the automatic UPDATE STATISTICS setting for a specific index and statistics, or for all indexes and statistics for a given table or indexed view in the current database.

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

Monday, March 26, 2012

Index Question

Friday, March 23, 2012

Index Question

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

Wednesday, March 21, 2012

Index on two columns doesnt allow NULL in both - HELP!

Table DDL below:

The tables I have contain Timesheet information. Each row in the
tblTSCollected table contains an entry for an employee into the
timesheet system, specifically by scanning the barcode on their badge.

A whole bunch of business logic periodically attempts to "pair" these
into logically matched scans. For example, some employees will scan in
and out of a single place of work. For these there will be a row
written to the tblTSRuleApplied table which contains, inter alia and
some redundant data, the fldCollectedID for the two rows. The earlier
will be put into the fldStartTimeCollectedID, and the later into the
fldEndTimeCollectedID. Some employees will clock on at their base,
then perform sub-duties at different locations during the day, and
clock off at their home base at the end of their shift. For these, the
system would identify the outer records as a matching pair, and then
pair up inner records by location.

However, if the employee fails to enter a valid "clocking in and out"
pair (for example, if they clock in at the wrong location) the system
needs to generate a "dummy" "clocking in and out" record for the
payroll department. Ideally, this would have NULL values in the
fldStartTimeCollectedID and fldEndTimeCollectedID columns. This would
alert a user in a different part of the system, where missing
timesheets were being arbitrated, that an employee appeared to have
failed to clock in for that day. Of course, the user could see
on-screen that they had clocked in, but at an incorrect location.

Unfortunately, the database designer is not here for the moment (he was
knocked off his bicycle recently), but he put a unique index on the
tblTSRuleApplied table that prevents the same value being entered into
the fldStartTimeCollectedID and fldEndTimeCollectedID columns. This is
generally A Good Thing, since we don't want the same timesheet scan to
form both a "clocking on" event and a "clocking off" event.

So, is there any way of retaining the requirement that the
fldStartTimeCollectedID and the fldEndTimeCollectedID columns may not
contain the same value in a single row, UNLESS that value is NULL in
which case all is hunky dory. I should add that the clients don't much
care for Triggers (and neither do I for that matter).

Many thanks if you are able to help.

Edward

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblTSRuleApplied_tblTSCollected]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblTSRuleApplied] DROP CONSTRAINT
FK_tblTSRuleApplied_tblTSCollected
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblTSRuleApplied_tblTSCollected1]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblTSRuleApplied] DROP CONSTRAINT
FK_tblTSRuleApplied_tblTSCollected1
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblTSArbAccept_tblTSRuleApplied]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblTSArbAccept] DROP CONSTRAINT
FK_tblTSArbAccept_tblTSRuleApplied
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblTSCollected_tblTSRuleApplied]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblTSCollected] DROP CONSTRAINT
FK_tblTSCollected_tblTSRuleApplied
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblTSCollected]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblTSCollected]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblTSRuleApplied]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblTSRuleApplied]
GO

CREATE TABLE [dbo].[tblTSCollected] (
[fldCollectedID] [int] IDENTITY (1, 1) NOT NULL ,
[fldEmployeeID] [int] NULL ,
[fldLocationCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[fldTimeStamp] [datetime] NULL ,
[fldRuleAppliedID] [int] NULL ,
[fldBarCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[fldProcessed] [smallint] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblTSRuleApplied] (
[fldEmpRuleID] [int] NOT NULL ,
[fldRuleAppliedID] [int] IDENTITY (1, 1) NOT NULL ,
[fldStartTime] [datetime] NULL ,
[fldEndTime] [datetime] NULL ,
[fldStartTimeCollectedID] [int] NULL ,
[fldEndTimeCollectedID] [int] NULL ,
[fldStartArbStatus] [smallint] NULL ,
[fldEndArbStatus] [smallint] NULL ,
[fldDurationArbStatus] [smallint] NULL ,
[fldPrimary] [smallint] NOT NULL ,
[fldDateEntered] [datetime] NULL ,
[fldEnteredBy] [int] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblTSCollected] WITH NOCHECK ADD
CONSTRAINT [DF_tblTSCollected_fldProcessed] DEFAULT (0) FOR
[fldProcessed],
CONSTRAINT [PK_tblTimesheetCollected] PRIMARY KEY CLUSTERED
(
[fldCollectedID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblTSRuleApplied] WITH NOCHECK ADD
CONSTRAINT [DF_tblTSRuleApplied_fldPrimary] DEFAULT (1) FOR
[fldPrimary],
CONSTRAINT [PK_tblTSRuleApplied] PRIMARY KEY CLUSTERED
(
[fldRuleAppliedID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [IX_tblTSRuleApplied_1] UNIQUE NONCLUSTERED
(
[fldStartTimeCollectedID],
[fldEndTimeCollectedID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblTSCollected] ADD
CONSTRAINT [FK_tblTSCollected_tblEmployee1] FOREIGN KEY
(
[fldEmployeeID]
) REFERENCES [dbo].[tblEmployee] (
[fldEmployeeID]
),
CONSTRAINT [FK_tblTSCollected_tblLocation] FOREIGN KEY
(
[fldLocationCode]
) REFERENCES [dbo].[tblLocation] (
[fldLocationCode]
),
CONSTRAINT [FK_tblTSCollected_tblTSRuleApplied] FOREIGN KEY
(
[fldRuleAppliedID]
) REFERENCES [dbo].[tblTSRuleApplied] (
[fldRuleAppliedID]
)
GO

ALTER TABLE [dbo].[tblTSRuleApplied] ADD
CONSTRAINT [FK_tblTSRuleApplied_tblTSCollected] FOREIGN KEY
(
[fldStartTimeCollectedID]
) REFERENCES [dbo].[tblTSCollected] (
[fldCollectedID]
),
CONSTRAINT [FK_tblTSRuleApplied_tblTSCollected1] FOREIGN KEY
(
[fldEndTimeCollectedID]
) REFERENCES [dbo].[tblTSCollected] (
[fldCollectedID]
),
CONSTRAINT [FK_tblTSRuleApplied_tblTSDurationStatus] FOREIGN KEY
(
[fldDurationArbStatus]
) REFERENCES [dbo].[tblTSDurationStatus] (
[fldStatus]
),
CONSTRAINT [FK_tblTSRuleApplied_tblTSEmpRules] FOREIGN KEY
(
[fldEmpRuleID]
) REFERENCES [dbo].[tblTSEmpRules] (
[fldEmpRuleID]
),
CONSTRAINT [FK_tblTSRuleApplied_tblTSTimeStatus] FOREIGN KEY
(
[fldStartArbStatus]
) REFERENCES [dbo].[tblTSTimeStatus] (
[fldStatus]
),
CONSTRAINT [FK_tblTSRuleApplied_tblTSTimeStatus1] FOREIGN KEY
(
[fldEndArbStatus]
) REFERENCES [dbo].[tblTSTimeStatus] (
[fldStatus]
)
GO> So, is there any way of retaining the requirement that the
> fldStartTimeCollectedID and the fldEndTimeCollectedID columns may not
> contain the same value in a single row, UNLESS that value is NULL in
> which case all is hunky dory. I should add that the clients don't much
> care for Triggers (and neither do I for that matter).

There are a couple of methods to accomplish this. One method is with a
trigger. Another, with SQL 2000 and above, is using an index view including
non-null values instead of a unique constraint:

CREATE VIEW v_tblTSRuleApplied
WITH SCHEMABINDING
AS
SELECT fldStartTimeCollectedID, fldEndTimeCollectedID
FROM dbo.tblTSRuleApplied
WHERE fldStartTimeCollectedID IS NOT NULL AND
fldEndTimeCollectedID IS NOT NULL
GO

CREATE UNIQUE CLUSTERED INDEX v_tblTSRuleApplied_cdx
ON v_tblTSRuleApplied(fldStartTimeCollectedID, fldEndTimeCollectedID)
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

<teddysnips@.hotmail.com> wrote in message
news:1135265109.464713.76030@.f14g2000cwb.googlegro ups.com...
> Table DDL below:
> The tables I have contain Timesheet information. Each row in the
> tblTSCollected table contains an entry for an employee into the
> timesheet system, specifically by scanning the barcode on their badge.
> A whole bunch of business logic periodically attempts to "pair" these
> into logically matched scans. For example, some employees will scan in
> and out of a single place of work. For these there will be a row
> written to the tblTSRuleApplied table which contains, inter alia and
> some redundant data, the fldCollectedID for the two rows. The earlier
> will be put into the fldStartTimeCollectedID, and the later into the
> fldEndTimeCollectedID. Some employees will clock on at their base,
> then perform sub-duties at different locations during the day, and
> clock off at their home base at the end of their shift. For these, the
> system would identify the outer records as a matching pair, and then
> pair up inner records by location.
> However, if the employee fails to enter a valid "clocking in and out"
> pair (for example, if they clock in at the wrong location) the system
> needs to generate a "dummy" "clocking in and out" record for the
> payroll department. Ideally, this would have NULL values in the
> fldStartTimeCollectedID and fldEndTimeCollectedID columns. This would
> alert a user in a different part of the system, where missing
> timesheets were being arbitrated, that an employee appeared to have
> failed to clock in for that day. Of course, the user could see
> on-screen that they had clocked in, but at an incorrect location.
> Unfortunately, the database designer is not here for the moment (he was
> knocked off his bicycle recently), but he put a unique index on the
> tblTSRuleApplied table that prevents the same value being entered into
> the fldStartTimeCollectedID and fldEndTimeCollectedID columns. This is
> generally A Good Thing, since we don't want the same timesheet scan to
> form both a "clocking on" event and a "clocking off" event.
> So, is there any way of retaining the requirement that the
> fldStartTimeCollectedID and the fldEndTimeCollectedID columns may not
> contain the same value in a single row, UNLESS that value is NULL in
> which case all is hunky dory. I should add that the clients don't much
> care for Triggers (and neither do I for that matter).
> Many thanks if you are able to help.
> Edward
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_tblTSRuleApplied_tblTSCollected]') and
> OBJECTPROPERTY(id, N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[tblTSRuleApplied] DROP CONSTRAINT
> FK_tblTSRuleApplied_tblTSCollected
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_tblTSRuleApplied_tblTSCollected1]') and
> OBJECTPROPERTY(id, N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[tblTSRuleApplied] DROP CONSTRAINT
> FK_tblTSRuleApplied_tblTSCollected1
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_tblTSArbAccept_tblTSRuleApplied]') and
> OBJECTPROPERTY(id, N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[tblTSArbAccept] DROP CONSTRAINT
> FK_tblTSArbAccept_tblTSRuleApplied
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_tblTSCollected_tblTSRuleApplied]') and
> OBJECTPROPERTY(id, N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[tblTSCollected] DROP CONSTRAINT
> FK_tblTSCollected_tblTSRuleApplied
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblTSCollected]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[tblTSCollected]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblTSRuleApplied]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[tblTSRuleApplied]
> GO
> CREATE TABLE [dbo].[tblTSCollected] (
> [fldCollectedID] [int] IDENTITY (1, 1) NOT NULL ,
> [fldEmployeeID] [int] NULL ,
> [fldLocationCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [fldTimeStamp] [datetime] NULL ,
> [fldRuleAppliedID] [int] NULL ,
> [fldBarCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [fldProcessed] [smallint] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tblTSRuleApplied] (
> [fldEmpRuleID] [int] NOT NULL ,
> [fldRuleAppliedID] [int] IDENTITY (1, 1) NOT NULL ,
> [fldStartTime] [datetime] NULL ,
> [fldEndTime] [datetime] NULL ,
> [fldStartTimeCollectedID] [int] NULL ,
> [fldEndTimeCollectedID] [int] NULL ,
> [fldStartArbStatus] [smallint] NULL ,
> [fldEndArbStatus] [smallint] NULL ,
> [fldDurationArbStatus] [smallint] NULL ,
> [fldPrimary] [smallint] NOT NULL ,
> [fldDateEntered] [datetime] NULL ,
> [fldEnteredBy] [int] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblTSCollected] WITH NOCHECK ADD
> CONSTRAINT [DF_tblTSCollected_fldProcessed] DEFAULT (0) FOR
> [fldProcessed],
> CONSTRAINT [PK_tblTimesheetCollected] PRIMARY KEY CLUSTERED
> (
> [fldCollectedID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblTSRuleApplied] WITH NOCHECK ADD
> CONSTRAINT [DF_tblTSRuleApplied_fldPrimary] DEFAULT (1) FOR
> [fldPrimary],
> CONSTRAINT [PK_tblTSRuleApplied] PRIMARY KEY CLUSTERED
> (
> [fldRuleAppliedID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY] ,
> CONSTRAINT [IX_tblTSRuleApplied_1] UNIQUE NONCLUSTERED
> (
> [fldStartTimeCollectedID],
> [fldEndTimeCollectedID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblTSCollected] ADD
> CONSTRAINT [FK_tblTSCollected_tblEmployee1] FOREIGN KEY
> (
> [fldEmployeeID]
> ) REFERENCES [dbo].[tblEmployee] (
> [fldEmployeeID]
> ),
> CONSTRAINT [FK_tblTSCollected_tblLocation] FOREIGN KEY
> (
> [fldLocationCode]
> ) REFERENCES [dbo].[tblLocation] (
> [fldLocationCode]
> ),
> CONSTRAINT [FK_tblTSCollected_tblTSRuleApplied] FOREIGN KEY
> (
> [fldRuleAppliedID]
> ) REFERENCES [dbo].[tblTSRuleApplied] (
> [fldRuleAppliedID]
> )
> GO
> ALTER TABLE [dbo].[tblTSRuleApplied] ADD
> CONSTRAINT [FK_tblTSRuleApplied_tblTSCollected] FOREIGN KEY
> (
> [fldStartTimeCollectedID]
> ) REFERENCES [dbo].[tblTSCollected] (
> [fldCollectedID]
> ),
> CONSTRAINT [FK_tblTSRuleApplied_tblTSCollected1] FOREIGN KEY
> (
> [fldEndTimeCollectedID]
> ) REFERENCES [dbo].[tblTSCollected] (
> [fldCollectedID]
> ),
> CONSTRAINT [FK_tblTSRuleApplied_tblTSDurationStatus] FOREIGN KEY
> (
> [fldDurationArbStatus]
> ) REFERENCES [dbo].[tblTSDurationStatus] (
> [fldStatus]
> ),
> CONSTRAINT [FK_tblTSRuleApplied_tblTSEmpRules] FOREIGN KEY
> (
> [fldEmpRuleID]
> ) REFERENCES [dbo].[tblTSEmpRules] (
> [fldEmpRuleID]
> ),
> CONSTRAINT [FK_tblTSRuleApplied_tblTSTimeStatus] FOREIGN KEY
> (
> [fldStartArbStatus]
> ) REFERENCES [dbo].[tblTSTimeStatus] (
> [fldStatus]
> ),
> CONSTRAINT [FK_tblTSRuleApplied_tblTSTimeStatus1] FOREIGN KEY
> (
> [fldEndArbStatus]
> ) REFERENCES [dbo].[tblTSTimeStatus] (
> [fldStatus]
> )
> GO|||Dan Guzman wrote:
> > So, is there any way of retaining the requirement that the
> > fldStartTimeCollectedID and the fldEndTimeCollectedID columns may not
> > contain the same value in a single row, UNLESS that value is NULL in
> > which case all is hunky dory. I should add that the clients don't much
> > care for Triggers (and neither do I for that matter).
> There are a couple of methods to accomplish this. One method is with a
> trigger. Another, with SQL 2000 and above, is using an index view including
> non-null values instead of a unique constraint:
[snip]

Many thanks - I'll put this to the vote just after the holidays.

I *love* usenet.

Edward|||I don't think Mr. Guzman's solution will work for the business problem
you are trying to solve. It does allow the index, but you will never be
able to retrieve any of the data where EITHER start OR end time is
null.

I guess I'm trying to understand when a record would be created when
both entries are null?|||On 22 Dec 2005 11:26:28 -0800, Doug wrote:

>I don't think Mr. Guzman's solution will work for the business problem
>you are trying to solve. It does allow the index, but you will never be
>able to retrieve any of the data where EITHER start OR end time is
>null.

Hi Doug,

Not from the indexed view, but you can still get this data from the
table itself.

The view suggested by Dan is intended merely to enforce the constraint,
not to replace the table in queries.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hmmmm......

I don't think the view forces the constraint onto the table. Is this
correct?|||On 22 Dec 2005 16:06:57 -0800, Doug wrote:

>Hmmmm......
>I don't think the view forces the constraint onto the table. Is this
>correct?

Hi Doug,

Have you tried it?

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||> I don't think the view forces the constraint onto the table. Is this
> correct?

SQL Server automatically maintains the view index to reflect underlying
table changes. This will have the effect of a unique constraint that
ignores null values. Duplicate non-null values will not be allowed in the
underlying table.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Doug" <drmiller100@.hotmail.com> wrote in message
news:1135296417.507570.119610@.f14g2000cwb.googlegr oups.com...
> Hmmmm......
> I don't think the view forces the constraint onto the table. Is this
> correct?sql

Friday, March 9, 2012

Index for username/password

Does this make sense for a logon table:

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

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

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

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

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

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

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

Do you think this is a bad idea?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

password varchar(15) not null
)

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

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

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

constraint pk_Permission primary key clustered ( logon_id,
security_id )
)

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

Tony.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

An example schema is as follows :-

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

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

password varchar(15) not null
)

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

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

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

constraint pk_Permission primary key clustered ( logon_id,
security_id )
)

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

Tony.

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

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

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

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

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

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

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

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

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

Friday, March 30, 2012

Index Tuning

index statistics are created when?

Wednesday, March 28, 2012

Index size question (DDL)

Monday, March 26, 2012

Index Question

Friday, March 23, 2012

Index Question

Index Question

Wednesday, March 21, 2012

Index on two columns doesnt allow NULL in both - HELP!

Friday, March 9, 2012

Index for username/password

index and key

Blog Archive

About Me