Index Speed use of REPLACE in clause

I have a frequently run Query, due to the design of the Sproc the indexed
columns I have added to support these are being used but are slow due to the
fact the use of the REPLACE in the Clause (Replace is being used to remove
spaces)
I.E
Select <columns>
FROM <Table>
WHERE
(sEMail1 = 'hambly_neil@.hotmail.com' OR sEMail2 = ''hambly_neil@.hotmail.com')
OR
(REPLACE(sTel1,' ','') = '0123456789' AND REPLACE(sPostCode,' ','') = 'N118HQ')
Does anyone have any ideas on recoding or indexes changes to help here
--
Neil HamblyHow about cleansing the data on the way in (i.e. when you INSERT it, run the
REPLACE), so that you won't have to do it every time you query?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Neil Hambly" <hambly_neil@.hotmail.com> wrote in message
news:3D4000CA-7EFE-4A9F-AF74-3F0F6CB465EF@.microsoft.com...
> I have a frequently run Query, due to the design of the Sproc the indexed
> columns I have added to support these are being used but are slow due to
the
> fact the use of the REPLACE in the Clause (Replace is being used to remove
> spaces)
> I.E
> Select <columns>
> FROM <Table>
> WHERE
> (sEMail1 = 'hambly_neil@.hotmail.com' OR sEMail2 => ''hambly_neil@.hotmail.com')
> OR
> (REPLACE(sTel1,' ','') = '0123456789' AND REPLACE(sPostCode,' ','') => 'N118HQ')
> Does anyone have any ideas on recoding or indexes changes to help here
> --
> Neil Hambly|||This would be the ideal scenario of course, unofrtunately due to Data-Privacy
and other reasons, the data cannot be Cleansed
One thought I had was the use of Computed colums - do not know if this would
be suitable for this type of issue
"Adam Machanic" wrote:
> How about cleansing the data on the way in (i.e. when you INSERT it, run the
> REPLACE), so that you won't have to do it every time you query?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Neil Hambly" <hambly_neil@.hotmail.com> wrote in message
> news:3D4000CA-7EFE-4A9F-AF74-3F0F6CB465EF@.microsoft.com...
> > I have a frequently run Query, due to the design of the Sproc the indexed
> > columns I have added to support these are being used but are slow due to
> the
> > fact the use of the REPLACE in the Clause (Replace is being used to remove
> > spaces)
> > I.E
> > Select <columns>
> > FROM <Table>
> > WHERE
> > (sEMail1 = 'hambly_neil@.hotmail.com' OR sEMail2 => > ''hambly_neil@.hotmail.com')
> > OR
> > (REPLACE(sTel1,' ','') = '0123456789' AND REPLACE(sPostCode,' ','') => > 'N118HQ')
> >
> > Does anyone have any ideas on recoding or indexes changes to help here
> >
> > --
> > Neil Hambly
>
>|||Possibly, yes. You can index computed columns to semi-persist them, as well
(they'll be persisted in the index); in this case, you could do:
ALTER TABLE YourTable
ADD Tel1NoSpace AS (REPLACE(sTel1, ' ', ''))
ALTER TABLE YourTable
ADD PostCodeNoSpace AS (REPLACE(sPostCode, ' ', ''))
CREATE NONCLUSTERED INDEX IX_TelPostSearch ON YourTable (Tel1NoSpace,
PostCodeNoSpace)
... That would solve your issue, I think. But _better_ would still be to
cleanse the data -- I'm not sure what you mean by data privacy; the data is
in your database already, isn't it?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Neil Hambly" <hambly_neil@.hotmail.com> wrote in message
news:40183D6E-BAD0-4A2E-A474-26B6DD25E763@.microsoft.com...
> This would be the ideal scenario of course, unofrtunately due to
Data-Privacy
> and other reasons, the data cannot be Cleansed
> One thought I had was the use of Computed colums - do not know if this
would
> be suitable for this type of issue
> "Adam Machanic" wrote:
> > How about cleansing the data on the way in (i.e. when you INSERT it, run
the
> > REPLACE), so that you won't have to do it every time you query?
> >
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.sqljunkies.com/weblog/amachanic
> > --
> >
> >
> > "Neil Hambly" <hambly_neil@.hotmail.com> wrote in message
> > news:3D4000CA-7EFE-4A9F-AF74-3F0F6CB465EF@.microsoft.com...
> > > I have a frequently run Query, due to the design of the Sproc the
indexed
> > > columns I have added to support these are being used but are slow due
to
> > the
> > > fact the use of the REPLACE in the Clause (Replace is being used to
remove
> > > spaces)
> > > I.E
> > > Select <columns>
> > > FROM <Table>
> > > WHERE
> > > (sEMail1 = 'hambly_neil@.hotmail.com' OR sEMail2 => > > ''hambly_neil@.hotmail.com')
> > > OR
> > > (REPLACE(sTel1,' ','') = '0123456789' AND REPLACE(sPostCode,' ','') => > > 'N118HQ')
> > >
> > > Does anyone have any ideas on recoding or indexes changes to help here
> > >
> > > --
> > > Neil Hambly
> >
> >
> >|||Yes, a little test on the Northwind database suggests that this should
work (provided you are using Enterprise Edition).
Gert-Jan
Neil Hambly wrote:
> This would be the ideal scenario of course, unofrtunately due to Data-Privacy
> and other reasons, the data cannot be Cleansed
> One thought I had was the use of Computed colums - do not know if this would
> be suitable for this type of issue
> "Adam Machanic" wrote:
> > How about cleansing the data on the way in (i.e. when you INSERT it, run the
> > REPLACE), so that you won't have to do it every time you query?
> >
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.sqljunkies.com/weblog/amachanic
> > --
> >
> >
> > "Neil Hambly" <hambly_neil@.hotmail.com> wrote in message
> > news:3D4000CA-7EFE-4A9F-AF74-3F0F6CB465EF@.microsoft.com...
> > > I have a frequently run Query, due to the design of the Sproc the indexed
> > > columns I have added to support these are being used but are slow due to
> > the
> > > fact the use of the REPLACE in the Clause (Replace is being used to remove
> > > spaces)
> > > I.E
> > > Select <columns>
> > > FROM <Table>
> > > WHERE
> > > (sEMail1 = 'hambly_neil@.hotmail.com' OR sEMail2 => > > ''hambly_neil@.hotmail.com')
> > > OR
> > > (REPLACE(sTel1,' ','') = '0123456789' AND REPLACE(sPostCode,' ','') => > > 'N118HQ')
> > >
> > > Does anyone have any ideas on recoding or indexes changes to help here
> > >
> > > --
> > > Neil Hambly
> >
> >
> >|||"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:41C22D18.3AE1AF4E@.toomuchspamalready.nl...
> Yes, a little test on the Northwind database suggests that this should
> work (provided you are using Enterprise Edition).
What's Enterprise Edition have to do with it?
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Re: Data privacy act - We do not own all the data some of it is supplied by
our clients.
Anyway I built some computed columns with the formula using Replace(sTel1, '
', '') etc..Created indexes on these columns and amended my Sprocs to use the
computed columns etc.. Went from 10 secs (22, 000 logical reads) to a few ms
with 12 logical reads..
As this is run over 300,000 times per month that is a huge perf impact
Thanks for your posts
"Adam Machanic" wrote:
> Possibly, yes. You can index computed columns to semi-persist them, as well
> (they'll be persisted in the index); in this case, you could do:
> ALTER TABLE YourTable
> ADD Tel1NoSpace AS (REPLACE(sTel1, ' ', ''))
> ALTER TABLE YourTable
> ADD PostCodeNoSpace AS (REPLACE(sPostCode, ' ', ''))
> CREATE NONCLUSTERED INDEX IX_TelPostSearch ON YourTable (Tel1NoSpace,
> PostCodeNoSpace)
>
> ... That would solve your issue, I think. But _better_ would still be to
> cleanse the data -- I'm not sure what you mean by data privacy; the data is
> in your database already, isn't it?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Neil Hambly" <hambly_neil@.hotmail.com> wrote in message
> news:40183D6E-BAD0-4A2E-A474-26B6DD25E763@.microsoft.com...
> > This would be the ideal scenario of course, unofrtunately due to
> Data-Privacy
> > and other reasons, the data cannot be Cleansed
> > One thought I had was the use of Computed colums - do not know if this
> would
> > be suitable for this type of issue
> >
> > "Adam Machanic" wrote:
> >
> > > How about cleansing the data on the way in (i.e. when you INSERT it, run
> the
> > > REPLACE), so that you won't have to do it every time you query?
> > >
> > >
> > > --
> > > Adam Machanic
> > > SQL Server MVP
> > > http://www.sqljunkies.com/weblog/amachanic
> > > --
> > >
> > >
> > > "Neil Hambly" <hambly_neil@.hotmail.com> wrote in message
> > > news:3D4000CA-7EFE-4A9F-AF74-3F0F6CB465EF@.microsoft.com...
> > > > I have a frequently run Query, due to the design of the Sproc the
> indexed
> > > > columns I have added to support these are being used but are slow due
> to
> > > the
> > > > fact the use of the REPLACE in the Clause (Replace is being used to
> remove
> > > > spaces)
> > > > I.E
> > > > Select <columns>
> > > > FROM <Table>
> > > > WHERE
> > > > (sEMail1 = 'hambly_neil@.hotmail.com' OR sEMail2 => > > > ''hambly_neil@.hotmail.com')
> > > > OR
> > > > (REPLACE(sTel1,' ','') = '0123456789' AND REPLACE(sPostCode,' ','') => > > > 'N118HQ')
> > > >
> > > > Does anyone have any ideas on recoding or indexes changes to help here
> > > >
> > > > --
> > > > Neil Hambly
> > >
> > >
> > >
>
>|||I could be wrong. I thought that maybe indexes on computed columns would
not be automatically used in Standard Edition, just as indexes on views
are not automatically used (unless you use Enterprise Edition or provide
special hints).
But as I said: I could be wrong. Maybe someone with Standard Edition can
test this example to see if an index seek is used.
USE Northwind
GO
ALTER TABLE Customers ADD CompanyNameNoSpace AS
Replace(CompanyName,space(1),'')
GO
CREATE INDEX IX_Customers_CompanyNameNoSpace ON
Customers(CompanyNameNoSpace)
GO
SET SHOWPLAN_TEXT ON
GO
SELECT * FROM Customers WHERE CompanyNameNoSpace='EasternConnection'
GO
SET SHOWPLAN_TEXT OFF
Gert-Jan
Adam Machanic wrote:
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:41C22D18.3AE1AF4E@.toomuchspamalready.nl...
> > Yes, a little test on the Northwind database suggests that this should
> > work (provided you are using Enterprise Edition).
> What's Enterprise Edition have to do with it?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --|||Confirmed:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)
|--Compute
Scalar(DEFINE:([Customers].[CompanyNameNoSpace]=replace([Customers].[Company
Name], Convert(space(1)), Convert(''))))
|--Bookmark Lookup(BOOKMARK:([Bmk1000]),
OBJECT:([Northwind].[dbo].[Customers]))
|--Index
Seek(OBJECT:([Northwind].[dbo].[Customers].[IX_Customers_CompanyNameNoSpace]
), SEEK:([Customers].[CompanyNameNoSpace]=Convert([@.1])) ORDERED FORWARD)
:-)
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:41C2E4BC.868B93CB@.toomuchspamalready.nl...
> I could be wrong. I thought that maybe indexes on computed columns would
> not be automatically used in Standard Edition, just as indexes on views
> are not automatically used (unless you use Enterprise Edition or provide
> special hints).
> But as I said: I could be wrong. Maybe someone with Standard Edition can
> test this example to see if an index seek is used.
> USE Northwind
> GO
> ALTER TABLE Customers ADD CompanyNameNoSpace AS
> Replace(CompanyName,space(1),'')
> GO
> CREATE INDEX IX_Customers_CompanyNameNoSpace ON
> Customers(CompanyNameNoSpace)
> GO
> SET SHOWPLAN_TEXT ON
> GO
> SELECT * FROM Customers WHERE CompanyNameNoSpace='EasternConnection'
> GO
> SET SHOWPLAN_TEXT OFF
> Gert-Jan
> Adam Machanic wrote:
> >
> > "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> > news:41C22D18.3AE1AF4E@.toomuchspamalready.nl...
> > > Yes, a little test on the Northwind database suggests that this should
> > > work (provided you are using Enterprise Edition).
> >
> > What's Enterprise Edition have to do with it?
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.sqljunkies.com/weblog/amachanic
> > --|||Thanks,
Gert-Jan
Adam Machanic wrote:
> Confirmed:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
> NT 5.0 (Build 2195: Service Pack 4)
> |--Compute
> Scalar(DEFINE:([Customers].[CompanyNameNoSpace]=replace([Customers].[Company
> Name], Convert(space(1)), Convert(''))))
> |--Bookmark Lookup(BOOKMARK:([Bmk1000]),
> OBJECT:([Northwind].[dbo].[Customers]))
> |--Index
> Seek(OBJECT:([Northwind].[dbo].[Customers].[IX_Customers_CompanyNameNoSpace]
> ), SEEK:([Customers].[CompanyNameNoSpace]=Convert([@.1])) ORDERED FORWARD)
> :-)
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:41C2E4BC.868B93CB@.toomuchspamalready.nl...
> > I could be wrong. I thought that maybe indexes on computed columns would
> > not be automatically used in Standard Edition, just as indexes on views
> > are not automatically used (unless you use Enterprise Edition or provide
> > special hints).
> >
> > But as I said: I could be wrong. Maybe someone with Standard Edition can
> > test this example to see if an index seek is used.
> >
> > USE Northwind
> > GO
> > ALTER TABLE Customers ADD CompanyNameNoSpace AS
> > Replace(CompanyName,space(1),'')
> > GO
> > CREATE INDEX IX_Customers_CompanyNameNoSpace ON
> > Customers(CompanyNameNoSpace)
> > GO
> > SET SHOWPLAN_TEXT ON
> > GO
> > SELECT * FROM Customers WHERE CompanyNameNoSpace='EasternConnection'
> > GO
> > SET SHOWPLAN_TEXT OFF
> >
> > Gert-Jan
> >
> > Adam Machanic wrote:
> > >
> > > "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> > > news:41C22D18.3AE1AF4E@.toomuchspamalready.nl...
> > > > Yes, a little test on the Northwind database suggests that this should
> > > > work (provided you are using Enterprise Edition).
> > >
> > > What's Enterprise Edition have to do with it?
> > >
> > > --
> > > Adam Machanic
> > > SQL Server MVP
> > > http://www.sqljunkies.com/weblog/amachanic
> > > --|||Actually, I just took a closer look at the execution plan. Interesting that
CompanyNameNoSpace is being re-computed after the bookmark lookup, even
though it was used for the initial index seek! I'm surprised that SQL
Server can't use the value from the index...
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ekLNvJE5EHA.2804@.TK2MSFTNGP15.phx.gbl...
> Confirmed:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
> NT 5.0 (Build 2195: Service Pack 4)
>
> |--Compute
>
Scalar(DEFINE:([Customers].[CompanyNameNoSpace]=replace([Customers].[Company
> Name], Convert(space(1)), Convert(''))))
> |--Bookmark Lookup(BOOKMARK:([Bmk1000]),
> OBJECT:([Northwind].[dbo].[Customers]))
> |--Index
>
Seek(OBJECT:([Northwind].[dbo].[Customers].[IX_Customers_CompanyNameNoSpace]
> ), SEEK:([Customers].[CompanyNameNoSpace]=Convert([@.1])) ORDERED FORWARD)
> :-)
>|||Indeed. I hadn't noticed.
I think Microsoft never took the time to build that optimization. To me,
that shows that they really only added a rudimentary version of indexed
computed columns.
Because the index value could have been used. If you change the query to
SELECT CustomerID,CompanyNameNoSpace FROM Customers WHERE
CompanyNameNoSpace='EasternConnection'
you will see that only the covering index is accessed, and that the
computed value is not recomputed.
Gert-Jan
Adam Machanic wrote:
> Actually, I just took a closer look at the execution plan. Interesting that
> CompanyNameNoSpace is being re-computed after the bookmark lookup, even
> though it was used for the initial index seek! I'm surprised that SQL
> Server can't use the value from the index...
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:ekLNvJE5EHA.2804@.TK2MSFTNGP15.phx.gbl...
> > Confirmed:
> >
> > Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> > Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
> > NT 5.0 (Build 2195: Service Pack 4)
> >
> >
> > |--Compute
> >
> Scalar(DEFINE:([Customers].[CompanyNameNoSpace]=replace([Customers].[Company
> > Name], Convert(space(1)), Convert(''))))
> > |--Bookmark Lookup(BOOKMARK:([Bmk1000]),
> > OBJECT:([Northwind].[dbo].[Customers]))
> > |--Index
> >
> Seek(OBJECT:([Northwind].[dbo].[Customers].[IX_Customers_CompanyNameNoSpace]
> > ), SEEK:([Customers].[CompanyNameNoSpace]=Convert([@.1])) ORDERED FORWARD)
> >
> > :-)
> >|||On Wed, 15 Dec 2004 07:35:02 -0800, "Neil Hambly"
<hambly_neil@.hotmail.com> wrote:
>Does anyone have any ideas on recoding or indexes changes to help here
If you have a LOT of this kind of thing, how about using the full-text
indexing?
J.

Index Problem

I have a clustered index (columns a and b) on a table that is used when
running the SQL
Select a,b,c from table where a='test' and b='test1'
However when running the sql
exec sp_executesql 'Select a,b,c from atable where a=@.p1 and b=@.p2', '@.p1'
,'@.p2'.'test','test1'
an index scan is used on the primary key which is not one of the above
columns.
My application server packages sql up as the second option.
Any ideas why the clustered index is not being used for the second option.
TIA Paul"news.microsoft.com" <P@.cookson_remove_.demon.co.uk> wrote in message
news:efvsqK8qEHA.376@.TK2MSFTNGP14.phx.gbl...
> I have a clustered index (columns a and b) on a table that is used when
> running the SQL
> Select a,b,c from table where a='test' and b='test1'
> However when running the sql
> exec sp_executesql 'Select a,b,c from atable where a=@.p1 and b=@.p2', '@.p1'
> ,'@.p2'.'test','test1'
> an index scan is used on the primary key which is not one of the above
> columns.
> My application server packages sql up as the second option.
> Any ideas why the clustered index is not being used for the second option.
> TIA Paul
>
The application server was putting it as the wrong data type nvarchar(5000)
and hence the index was not being used.
regards Paul (changed username this time)

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

index on joined columns

I have and joined between two tables like this:
......TB1 join TB2 on TB1.col1=TB2.col3 and TB1 .clo2=TB2.Col4
There a lot of records in TB2 ,so I wondered to make an index on Col3 and
Col4 of TB2.Now my question is that according to the join above it's better
to have a compound index on col3,col4 or each inividually?
Thanks for your help.
RayHi Ray,
I recommend you to create a composite Non clustered index on Col1 and Col2
of Table 1 , Col3 and Col4 of Table 2.
This index will be very good if you have a clustered index on Key column.
You can also use the Index tuning Wizard for Index recommendations. As well
see the Logical and Physical reads, Cpu usage before and
after the creation of index.
Thanks
Hari
SQL Server MVP
"RayAll" <RayAll@.microsft.com> wrote in message
news:%238E3mJjOFHA.3296@.TK2MSFTNGP15.phx.gbl...
>I have and joined between two tables like this:
> ......TB1 join TB2 on TB1.col1=TB2.col3 and TB1 .clo2=TB2.Col4
>
> There a lot of records in TB2 ,so I wondered to make an index on Col3 and
> Col4 of TB2.Now my question is that according to the join above it's
> better to have a compound index on col3,col4 or each inividually?
> Thanks for your help.
> Ray
>

Index on IsRowGuidCol ?

For merge replication I have only a few tables where the primary key spans
two columns. For the rest of the tables the primary key is one column and
is a uniqueidentifier.
For the fre tables where it spans two columns, I have to add an additional
uniqueidentifier column and mark it as the RowGuid Column for merge
replication.
My Question:
Will adding an index on this column speed merge replication?
--Pepto
Merge replication will add a unique index to the column that it uses for
merge replication. I believe in this case that it will create another
rowguid column to use.
Three conditions have to be met to precreate a rowguid column for merge
replication to use
1) it is called rowguid
2) its data type is uniqueidentifier ROWGUIDCOLUMN
3) it has a unique index on it.
If any of the three conditions are not met, merge replication will create
another column to use that meets these conditions although its name will be
slightly different - rowguidi where i increments.
"Pepto" <pepto@.hotmail.com> wrote in message
news:Ooh2BduPFHA.164@.TK2MSFTNGP12.phx.gbl...
> For merge replication I have only a few tables where the primary key spans
> two columns. For the rest of the tables the primary key is one column and
> is a uniqueidentifier.
> For the fre tables where it spans two columns, I have to add an additional
> uniqueidentifier column and mark it as the RowGuid Column for merge
> replication.
> My Question:
> Will adding an index on this column speed merge replication?
> --Pepto
>
>

Index on bit column

Hi,

MSSql2000: According to docs:
"Columns of type bit cannot have indexes on them"

Its impossible to define index on bit column using EM but create index command in QA is working and the index is created.
I understand why not to create index like this but its valid or invalid operation to create index on BIT column ?

ThanksWhile it does not make sense to create an index on a bit column, it is a valid operation. EM filters out the bit columns when it shows you the columns available to create indexes.

Bible also says:
(Under the topic "Create Index")
Columns consisting of the ntext, text, or image data types cannot be specified as columns for an index.

Not that Bit is not mentioned in this list.|||Hi sbaru, thanks for the reply.

I would like to know why EM filters out the bit columns for index creation.

I found the limitation about bit as index field under the topic "bit data type, described"

Index on 4 columns does not return results in expected order

We have a Full Text Index on 4 columns and it doesn’t return the results in
the order we think it should in 2000 or 2005. In 2005 I created a persisted
calculated column of these 4 columns and created the Full Text Index on it
and I returned the results in the order I expected them. In my mind both of
these methods should return the results in the same order. If not then I
don’t see how anyone could use Full Text Indexes on multiple columns.
I'm I doing something wrong or is this just the way it is?
Kenny wrote on Thu, 8 Feb 2007 09:14:04 -0800:

> We have a Full Text Index on 4 columns and it doesnt return the results
> in the order we think it should in 2000 or 2005. In 2005 I created a
> persisted calculated column of these 4 columns and created the Full Text
> Index on it and I returned the results in the order I expected them. In
> my mind both of these methods should return the results in the same order.
> If not then I dont see how anyone could use Full Text Indexes on multiple
> columns.
> I'm I doing something wrong or is this just the way it is?
>
Are you using the RANK value to sort them? If you have no ORDER BY clause in
your query, the row ordering is always indeterminate. If you use an ORDER BY
clause you should always get them in the same order (unless use RANK to
order them and the RANK calculations are different in 2000 and 2005, Hilary
would probably be able to tell you if this is the case).
Dan
|||I am ordering by Rank. I was trying to ask the question in a generic way but
maybe it would be better to see the code.
I’ve done some testing with FT Search and I’m not sure if it works like I
think it should. In my mind these two Indexes should return the same results
for the table below:
CREATE FULLTEXT INDEX ON dbo.wrkTestFTIndex
(cFirstName,cLastName,cTitle,vcEmailAddr)
KEY INDEX PK_wrkTestFTIndex
ON [TestFCCatalog]
CREATE FULLTEXT INDEX ON dbo.wrkTestFTIndex
(ccFullName)
KEY INDEX PK_wrkTestFTIndex
ON [TestFCCatalog]
The FT Index on ccFullName the persisted calculated column seems to return
better results than the other one (example below: Gary Walker is returned
with the highest ranking). Am I doing something wrong querying the other
index?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[wrkTestFTIndex](
[intFaclNbr] [int] NOT NULL,
[cFirstName] [char](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[cLastName] [char](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[cTitle] [char](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[vcEmailAddr] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FCidentity] [int] IDENTITY(1,1) NOT NULL,
[ccFullName] AS ((((((rtrim(isnull([cFirstname],''))+'
')+rtrim(isnull([cLastName],'')))+' ')+rtrim(isnull([cTitle],'')))+'
')+rtrim(isnull([vcEmailAddr],''))) PERSISTED,
CONSTRAINT [PK_wrkTestFTIndex] PRIMARY KEY CLUSTERED
( [FCidentity] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
CREATE FULLTEXT CATALOG [TestFCCatalog]
ON FILEGROUP [FTIndex]
IN PATH 'd:\sql2000ftindex'
AS DEFAULT
Select Rank,fc.* from wrkTestFTIndex fc inner join
FREETEXTTABLE(wrkTestFTIndex, *,'Gary Walker') AS KEY_TBL
ON fc.FCidentity = KEY_TBL.[KEY]
order by Rank DESC
"Daniel Crichton" wrote:

> Kenny wrote on Thu, 8 Feb 2007 09:14:04 -0800:
>
> Are you using the RANK value to sort them? If you have no ORDER BY clause in
> your query, the row ordering is always indeterminate. If you use an ORDER BY
> clause you should always get them in the same order (unless use RANK to
> order them and the RANK calculations are different in 2000 and 2005, Hilary
> would probably be able to tell you if this is the case).
> Dan
>
>

index of multiple columns vs. mutliple index of 1 column?

If I have this hierarchy in my db, there are many Objects in a Room, and
there are many Rooms in a Warehouse:
Object
(
ObjectId uniqueidentifier primary key clustered index,
RoomId uniqueidnetifier not null foreign key references Room( RoomID )
WarehouseId uniqueidentifer not null foreign key references Warehouse(
WarehouseId )
many more columns here about the object...
)
Room
(
RoomId uniqueidentifier primary key clustered index,
WarehouseId uniqueidentifer not null foreign key references Warehouse(
WarehouseId )
)
if I frequently do these statements:
select *
from object
where RoomId = 'xyz'
select *
from object
where Warehouse = 'abc'
What would an experienced db designer create for index for the Object table?
Two indexes: one on RoomId, and one on WarehouseId; OR just on combo index
(WarehouseId, RoomId)?
Thank you very much.I would suggest 2 separate indexes since you can not take advantage of a
composite index when searching by the 2nd column alone.
"Zeng" <Zeng5000@.hotmail.com> wrote in message
news:ORPZTkkjFHA.2472@.TK2MSFTNGP15.phx.gbl...
> If I have this hierarchy in my db, there are many Objects in a Room, and
> there are many Rooms in a Warehouse:
> Object
> (
> ObjectId uniqueidentifier primary key clustered index,
> RoomId uniqueidnetifier not null foreign key references Room( RoomID )
> WarehouseId uniqueidentifer not null foreign key references Warehouse(
> WarehouseId )
> many more columns here about the object...
> )
> Room
> (
> RoomId uniqueidentifier primary key clustered index,
> WarehouseId uniqueidentifer not null foreign key references Warehouse(
> WarehouseId )
> )
> if I frequently do these statements:
> select *
> from object
> where RoomId = 'xyz'
> select *
> from object
> where Warehouse = 'abc'
> What would an experienced db designer create for index for the Object
> table?
> Two indexes: one on RoomId, and one on WarehouseId; OR just on combo index
> (WarehouseId, RoomId)?
> Thank you very much.|||On Thu, 21 Jul 2005 16:03:37 -0700, Zeng wrote:

>If I have this hierarchy in my db, there are many Objects in a Room, and
>there are many Rooms in a Warehouse:
>Object
>(
> ObjectId uniqueidentifier primary key clustered index,
> RoomId uniqueidnetifier not null foreign key references Room( RoomID )
> WarehouseId uniqueidentifer not null foreign key references Warehouse(
>WarehouseId )
> many more columns here about the object...
> )
>Room
>(
> RoomId uniqueidentifier primary key clustered index,
> WarehouseId uniqueidentifer not null foreign key references Warehouse(
>WarehouseId )
> )
>if I frequently do these statements:
>select *
>from object
>where RoomId = 'xyz'
>select *
>from object
>where Warehouse = 'abc'
>What would an experienced db designer create for index for the Object table
?
>Two indexes: one on RoomId, and one on WarehouseId; OR just on combo index
>(WarehouseId, RoomId)?
Hi Zeng,
An experienced DB designer would not create an index for the Object
table at all. Instead, an experienced DB designer would first eliminate
some major flaws from the existing design.
1. "If you have a watch, you always know exactly how late it is. If you
have two watches, you never know."
In other words: don't store redundant information. The WarehouseId is
functionally dependent on room, so it should be in the Rooms table only,
not in the Objects table (where it is not only redundant, but violating
third normal form as well!)
2. If you want (*) to use surrogate keys, then at least remember that
they are a surrogate for something. A table with only a surrogate key is
a sure way to get unwanted duplicates, and lots of problems when you
need to remove them. Always use the natural key in addition to the
surrogate, and don't forget to declare a UNIQUE constraint for it.
(*) Whether or not you should want to use surrogate keys is another
subject. Or rather: holy war. I'd rather not go there right now :-)
3. Also, if you need surrogate keys, IDENTITY is almost always the
better choice. UNIQUEIDENTIFIER should only be used in the cases where
it is really needed - and those are scarce!
4. Finally, an experienced DB designer would never ever use SELECT * in
production code. Always list the columns you need. It saves bandwidth,
and it gives the optimizer the possibility to consider alternative plans
if there are covering indexes.
Once you have corrected all the above, feel free to repost. My first
guess is that nonclustered indexes for the foreign key columns would
suffice for the queries you gave, but that's just a quick guess, since I
don't know how your revised schema and queries will look.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

index of multiple columns vs. mutliple index of 1 column?

If I have this hierarchy in my db, there are many Objects in a Room, and
there are many Rooms in a Warehouse:
Object
(
ObjectId uniqueidentifier primary key clustered index,
RoomId uniqueidnetifier not null foreign key references Room( RoomID )
WarehouseId uniqueidentifer not null foreign key references Warehouse(
WarehouseId )
many more columns here about the object...
)
Room
(
RoomId uniqueidentifier primary key clustered index,
WarehouseId uniqueidentifer not null foreign key references Warehouse(
WarehouseId )
)
if I frequently do these statements:
select *
from object
where RoomId = 'xyz'
select *
from object
where Warehouse = 'abc'
What would an experienced db designer create for index for the Object table?
Two indexes: one on RoomId, and one on WarehouseId; OR just on combo index
(WarehouseId, RoomId)?
Thank you very much.
I would suggest 2 separate indexes since you can not take advantage of a
composite index when searching by the 2nd column alone.
"Zeng" <Zeng5000@.hotmail.com> wrote in message
news:ORPZTkkjFHA.2472@.TK2MSFTNGP15.phx.gbl...
> If I have this hierarchy in my db, there are many Objects in a Room, and
> there are many Rooms in a Warehouse:
> Object
> (
> ObjectId uniqueidentifier primary key clustered index,
> RoomId uniqueidnetifier not null foreign key references Room( RoomID )
> WarehouseId uniqueidentifer not null foreign key references Warehouse(
> WarehouseId )
> many more columns here about the object...
> )
> Room
> (
> RoomId uniqueidentifier primary key clustered index,
> WarehouseId uniqueidentifer not null foreign key references Warehouse(
> WarehouseId )
> )
> if I frequently do these statements:
> select *
> from object
> where RoomId = 'xyz'
> select *
> from object
> where Warehouse = 'abc'
> What would an experienced db designer create for index for the Object
> table?
> Two indexes: one on RoomId, and one on WarehouseId; OR just on combo index
> (WarehouseId, RoomId)?
> Thank you very much.
|||On Thu, 21 Jul 2005 16:03:37 -0700, Zeng wrote:

>If I have this hierarchy in my db, there are many Objects in a Room, and
>there are many Rooms in a Warehouse:
>Object
>(
> ObjectId uniqueidentifier primary key clustered index,
> RoomId uniqueidnetifier not null foreign key references Room( RoomID )
> WarehouseId uniqueidentifer not null foreign key references Warehouse(
>WarehouseId )
> many more columns here about the object...
>)
>Room
>(
> RoomId uniqueidentifier primary key clustered index,
> WarehouseId uniqueidentifer not null foreign key references Warehouse(
>WarehouseId )
>)
>if I frequently do these statements:
>select *
>from object
>where RoomId = 'xyz'
>select *
>from object
>where Warehouse = 'abc'
>What would an experienced db designer create for index for the Object table?
>Two indexes: one on RoomId, and one on WarehouseId; OR just on combo index
>(WarehouseId, RoomId)?
Hi Zeng,
An experienced DB designer would not create an index for the Object
table at all. Instead, an experienced DB designer would first eliminate
some major flaws from the existing design.
1. "If you have a watch, you always know exactly how late it is. If you
have two watches, you never know."
In other words: don't store redundant information. The WarehouseId is
functionally dependent on room, so it should be in the Rooms table only,
not in the Objects table (where it is not only redundant, but violating
third normal form as well!)
2. If you want (*) to use surrogate keys, then at least remember that
they are a surrogate for something. A table with only a surrogate key is
a sure way to get unwanted duplicates, and lots of problems when you
need to remove them. Always use the natural key in addition to the
surrogate, and don't forget to declare a UNIQUE constraint for it.
(*) Whether or not you should want to use surrogate keys is another
subject. Or rather: holy war. I'd rather not go there right now :-)
3. Also, if you need surrogate keys, IDENTITY is almost always the
better choice. UNIQUEIDENTIFIER should only be used in the cases where
it is really needed - and those are scarce!
4. Finally, an experienced DB designer would never ever use SELECT * in
production code. Always list the columns you need. It saves bandwidth,
and it gives the optimizer the possibility to consider alternative plans
if there are covering indexes.
Once you have corrected all the above, feel free to repost. My first
guess is that nonclustered indexes for the foreign key columns would
suffice for the queries you gave, but that's just a quick guess, since I
don't know how your revised schema and queries will look.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

index of multiple columns vs. mutliple index of 1 column?

If I have this hierarchy in my db, there are many Objects in a Room, and
there are many Rooms in a Warehouse:
Object
(
ObjectId uniqueidentifier primary key clustered index,
RoomId uniqueidnetifier not null foreign key references Room( RoomID )
WarehouseId uniqueidentifer not null foreign key references Warehouse(
WarehouseId )
many more columns here about the object...
)
Room
(
RoomId uniqueidentifier primary key clustered index,
WarehouseId uniqueidentifer not null foreign key references Warehouse(
WarehouseId )
)
if I frequently do these statements:
select *
from object
where RoomId = 'xyz'
select *
from object
where Warehouse = 'abc'
What would an experienced db designer create for index for the Object table?
Two indexes: one on RoomId, and one on WarehouseId; OR just on combo index
(WarehouseId, RoomId)?
Thank you very much.I would suggest 2 separate indexes since you can not take advantage of a
composite index when searching by the 2nd column alone.
"Zeng" <Zeng5000@.hotmail.com> wrote in message
news:ORPZTkkjFHA.2472@.TK2MSFTNGP15.phx.gbl...
> If I have this hierarchy in my db, there are many Objects in a Room, and
> there are many Rooms in a Warehouse:
> Object
> (
> ObjectId uniqueidentifier primary key clustered index,
> RoomId uniqueidnetifier not null foreign key references Room( RoomID )
> WarehouseId uniqueidentifer not null foreign key references Warehouse(
> WarehouseId )
> many more columns here about the object...
> )
> Room
> (
> RoomId uniqueidentifier primary key clustered index,
> WarehouseId uniqueidentifer not null foreign key references Warehouse(
> WarehouseId )
> )
> if I frequently do these statements:
> select *
> from object
> where RoomId = 'xyz'
> select *
> from object
> where Warehouse = 'abc'
> What would an experienced db designer create for index for the Object
> table?
> Two indexes: one on RoomId, and one on WarehouseId; OR just on combo index
> (WarehouseId, RoomId)?
> Thank you very much.|||On Thu, 21 Jul 2005 16:03:37 -0700, Zeng wrote:
>If I have this hierarchy in my db, there are many Objects in a Room, and
>there are many Rooms in a Warehouse:
>Object
>(
> ObjectId uniqueidentifier primary key clustered index,
> RoomId uniqueidnetifier not null foreign key references Room( RoomID )
> WarehouseId uniqueidentifer not null foreign key references Warehouse(
>WarehouseId )
> many more columns here about the object...
>)
>Room
>(
> RoomId uniqueidentifier primary key clustered index,
> WarehouseId uniqueidentifer not null foreign key references Warehouse(
>WarehouseId )
>)
>if I frequently do these statements:
>select *
>from object
>where RoomId = 'xyz'
>select *
>from object
>where Warehouse = 'abc'
>What would an experienced db designer create for index for the Object table?
>Two indexes: one on RoomId, and one on WarehouseId; OR just on combo index
>(WarehouseId, RoomId)?
Hi Zeng,
An experienced DB designer would not create an index for the Object
table at all. Instead, an experienced DB designer would first eliminate
some major flaws from the existing design.
1. "If you have a watch, you always know exactly how late it is. If you
have two watches, you never know."
In other words: don't store redundant information. The WarehouseId is
functionally dependent on room, so it should be in the Rooms table only,
not in the Objects table (where it is not only redundant, but violating
third normal form as well!)
2. If you want (*) to use surrogate keys, then at least remember that
they are a surrogate for something. A table with only a surrogate key is
a sure way to get unwanted duplicates, and lots of problems when you
need to remove them. Always use the natural key in addition to the
surrogate, and don't forget to declare a UNIQUE constraint for it.
(*) Whether or not you should want to use surrogate keys is another
subject. Or rather: holy war. I'd rather not go there right now :-)
3. Also, if you need surrogate keys, IDENTITY is almost always the
better choice. UNIQUEIDENTIFIER should only be used in the cases where
it is really needed - and those are scarce!
4. Finally, an experienced DB designer would never ever use SELECT * in
production code. Always list the columns you need. It saves bandwidth,
and it gives the optimizer the possibility to consider alternative plans
if there are covering indexes.
Once you have corrected all the above, feel free to repost. My first
guess is that nonclustered indexes for the foreign key columns would
suffice for the queries you gave, but that's just a quick guess, since I
don't know how your revised schema and queries will look.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

index not used when column is nullable and using jdbc

I have a table with a few columns, and one of the non-PK columns is indexed
and defined as nullable. Most of my selects happen on this column, but I
notice that as the no. of rows in this table increase, the time for selects
keeps going up. This happens while accessing this table using jdbc from a
java program.
I guess this is due to the index not being used by the server (query
optimizer?). When I look at the query execution plan in Query Analyzer, it
shows that the index will be used. What's going on? Is this a quirk of using
a jdbc driver?
thanks & regards,
Palani
Palaniappan N wrote:

> I have a table with a few columns, and one of the non-PK columns is indexed
> and defined as nullable. Most of my selects happen on this column, but I
> notice that as the no. of rows in this table increase, the time for selects
> keeps going up. This happens while accessing this table using jdbc from a
> java program.
> I guess this is due to the index not being used by the server (query
> optimizer?). When I look at the query execution plan in Query Analyzer, it
> shows that the index will be used. What's going on? Is this a quirk of using
> a jdbc driver?
> thanks & regards,
> Palani
It *probably* has nothing to do with the driver. You can verify that by
timing the query from a non-jdbc application. The only possibility is
if the column is a varchar and you are parameterizing the search value.
Try timing a select where the search value is specified in the SQL, like
"select * from mytable where mycol = 'myval'". If that's much faster,
try adding the property SendStringParametersAsUnicode=false to your
connection.
Joe Weinstein at BEA
|||Thanks for the reply. I already had set 'SendStringParametersAsUnicode=false'
in the connection string.
I found out that on installing SP3a on SqlServer 2000, the issue disappeared.
regards,
Palani
"Joe Weinstein" wrote:

>
> Palaniappan N wrote:
>
> It *probably* has nothing to do with the driver. You can verify that by
> timing the query from a non-jdbc application. The only possibility is
> if the column is a varchar and you are parameterizing the search value.
> Try timing a select where the search value is specified in the SQL, like
> "select * from mytable where mycol = 'myval'". If that's much faster,
> try adding the property SendStringParametersAsUnicode=false to your
> connection.
> Joe Weinstein at BEA
>

Index limited to 16 fields

Is there anyway I can by pass this sqlserver limitation of
having no more than 16 columns in a index?
I know it's a lot but the table is small and performance
should not be a problem.Nope, this is a hard limit.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Sandra" <anonymous@.discussions.microsoft.com> wrote in message
news:041501c3d704$575f24c0$a301280a@.phx.gbl...

quote:

---

> Is there anyway I can by pass this sqlserver limitation of
> having no more than 16 columns in a index?
> I know it's a lot but the table is small and performance
> should not be a problem.

---

|||If I remember correctly, it seems that you might be able to create a virtual
column which concatenates several other columns, then index it..., but the
900 byte limit is a hard limit..
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"Sandra" <anonymous@.discussions.microsoft.com> wrote in message
news:041501c3d704$575f24c0$a301280a@.phx.gbl...

quote:

---

> Is there anyway I can by pass this sqlserver limitation of
> having no more than 16 columns in a index?
> I know it's a lot but the table is small and performance
> should not be a problem.

---

|||You might post DDL and some sample DML on how queries will be run on your
table, maybe a large composite index isn't the best choice.
Ray Higdon MCSE, MCDBA, CCNA
--
"Sandra" <anonymous@.discussions.microsoft.com> wrote in message
news:041501c3d704$575f24c0$a301280a@.phx.gbl...

quote:

---

> Is there anyway I can by pass this sqlserver limitation of
> having no more than 16 columns in a index?
> I know it's a lot but the table is small and performance
> should not be a problem.

---

Index limited to 16 fields

Is there anyway I can by pass this sqlserver limitation of
having no more than 16 columns in a index?
I know it's a lot but the table is small and performance
should not be a problem.Nope, this is a hard limit.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Sandra" <anonymous@.discussions.microsoft.com> wrote in message
news:041501c3d704$575f24c0$a301280a@.phx.gbl...
> Is there anyway I can by pass this sqlserver limitation of
> having no more than 16 columns in a index?
> I know it's a lot but the table is small and performance
> should not be a problem.|||If I remember correctly, it seems that you might be able to create a virtual
column which concatenates several other columns, then index it..., but the
900 byte limit is a hard limit..
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"Sandra" <anonymous@.discussions.microsoft.com> wrote in message
news:041501c3d704$575f24c0$a301280a@.phx.gbl...
> Is there anyway I can by pass this sqlserver limitation of
> having no more than 16 columns in a index?
> I know it's a lot but the table is small and performance
> should not be a problem.|||You might post DDL and some sample DML on how queries will be run on your
table, maybe a large composite index isn't the best choice.
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Sandra" <anonymous@.discussions.microsoft.com> wrote in message
news:041501c3d704$575f24c0$a301280a@.phx.gbl...
> Is there anyway I can by pass this sqlserver limitation of
> having no more than 16 columns in a index?
> I know it's a lot but the table is small and performance
> should not be a problem.

Index is not faster any more

After I have modified couple columns my database access is very slow in that table. (Update Statistics Rx_Control is in Progress). It happened before I got back to same status by restoring the data. I really don't want to restore this time. Please some one post a sloution.

Thank you
Raj SankarOriginally posted by raj_sankar
After I have modified couple columns my database access is very slow in that table. (Update Statistics Rx_Control is in Progress). It happened before I got back to same status by restoring the data. I really don't want to restore this time. Please some one post a sloution.

Thank you
Raj Sankar
Are these updates on any index columns. Try rebuilding the index using
DBCC dbreindex

Joe|||Originally posted by mkg_1232000
Are these updates on any index columns. Try rebuilding the index using
DBCC dbreindex

Joe

The index is ok, Some how execution plan changed. Now every this ok after uodating the statistics. how ever the foloowing problem remains.

from query analyser.
select * from table_name where store = '3' --is faster and using index scan.

Declare @.store as int
set @.store = '3'
select * from table_name where store = @.store --is very slow and using table scan.

I don't how to fix this, it was ok before.

Index details

How do i retrive index details if i have table name and
column details..
I need index name,type ,whether composite ,if composite
what are the columns present..
Have a look at sp_helpindex in BOL e.g. exec sp_helpindex 'Orders'
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<anonymous@.discussions.microsoft.com> wrote in message
news:8b0e01c432ac$97bbaf80$a101280a@.phx.gbl...
> How do i retrive index details if i have table name and
> column details..
> I need index name,type ,whether composite ,if composite
> what are the columns present..
>
|||Hi
INDEXPROPERTY ( table_ID , index , property )
<anonymous@.discussions.microsoft.com> wrote in message
news:8b0e01c432ac$97bbaf80$a101280a@.phx.gbl...
> How do i retrive index details if i have table name and
> column details..
> I need index name,type ,whether composite ,if composite
> what are the columns present..
>

Index details

How do i retrive index details if i have table name and
column details..
I need index name,type ,whether composite ,if composite
what are the columns present..Have a look at sp_helpindex in BOL e.g. exec sp_helpindex 'Orders'
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<anonymous@.discussions.microsoft.com> wrote in message
news:8b0e01c432ac$97bbaf80$a101280a@.phx.gbl...
> How do i retrive index details if i have table name and
> column details..
> I need index name,type ,whether composite ,if composite
> what are the columns present..
>|||Hi
INDEXPROPERTY ( table_ID , index , property )
<anonymous@.discussions.microsoft.com> wrote in message
news:8b0e01c432ac$97bbaf80$a101280a@.phx.gbl...
> How do i retrive index details if i have table name and
> column details..
> I need index name,type ,whether composite ,if composite
> what are the columns present..
>

Index details

How do i retrive index details if i have table name and
column details..
I need index name,type ,whether composite ,if composite
what are the columns present..Have a look at sp_helpindex in BOL e.g. exec sp_helpindex 'Orders'
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<anonymous@.discussions.microsoft.com> wrote in message
news:8b0e01c432ac$97bbaf80$a101280a@.phx.gbl...
> How do i retrive index details if i have table name and
> column details..
> I need index name,type ,whether composite ,if composite
> what are the columns present..
>|||Hi
INDEXPROPERTY ( table_ID , index , property )
<anonymous@.discussions.microsoft.com> wrote in message
news:8b0e01c432ac$97bbaf80$a101280a@.phx.gbl...
> How do i retrive index details if i have table name and
> column details..
> I need index name,type ,whether composite ,if composite
> what are the columns present..
>

index creation

Hi, Folks

I have got a big table containing huge data, I am trying to create a composite index on three columns, the three columns are varchar2 datatype, but I notice(and confirm) that the data in these columns are numeric. in other words, I may change their datatype to be Numeric, hence, I have got two choices to create index either upon varchar, or Nuerice, can you tell me which way has the better performance to retrive data ?

Any advice will be highly appreciated.

xiongOriginally posted by xli
Hi, Folks

I have got a big table containing huge data, I am trying to create a composite index on three columns, the three columns are varchar2 datatype, but I notice(and confirm) that the data in these columns are numeric. in other words, I may change their datatype to be Numeric, hence, I have got two choices to create index either upon varchar, or Nuerice, can you tell me which way has the better performance to retrive data ?

Any advice will be highly appreciated.

xiong
If all the values in the column are menat to be numeric, then NUMBER is a better choice than VARCHAR2. It will use fewer bytes, and comparisons will be faster.

Index column order?

I need to create an index with multple columns, for example, an index
contains "State", "City" and "Street". Obviously the density of the columns
is State < City < Street. Is it necessary to follow a particular order when
create index for better performance? For example (State, City, Street) or
(Street, City, State)?Yes, it can have a huge impact. Higher selectivity = less rows traversed =better performance. Also remember that index statistics are only computed
for the first column in the index; statistics play a large part when the
optimizer does its job, so it's important to keep that in mind.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"nick" <nick@.discussions.microsoft.com> wrote in message
news:56387438-D942-40EB-AE98-26653AF2F0E4@.microsoft.com...
> I need to create an index with multple columns, for example, an index
> contains "State", "City" and "Street". Obviously the density of the
columns
> is State < City < Street. Is it necessary to follow a particular order
when
> create index for better performance? For example (State, City, Street) or
> (Street, City, State)?|||If you frequently search on some subset of the indexed columns then put
those columns first. Otherwise, put the most selective first (the column
with the highest cardinality). So I expect that's (Street, City, State).
--
David Portas
SQL Server MVP
--|||Hi
Based on your where clause.
If you have an index on State, City, Street and use a where clause City,
State, Street, the index will not be used. In this case, SQL can not guess
that state is of any use, and it then can't use the index for the sort.
Maybe and Index on state, a separate one on city, and a separate one on
street.
A compound index is generally a bad idea as a data modification will result
in a lot of index maintenance, slowing down the operation.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"nick" <nick@.discussions.microsoft.com> wrote in message
news:56387438-D942-40EB-AE98-26653AF2F0E4@.microsoft.com...
>I need to create an index with multple columns, for example, an index
> contains "State", "City" and "Street". Obviously the density of the
> columns
> is State < City < Street. Is it necessary to follow a particular order
> when
> create index for better performance? For example (State, City, Street) or
> (Street, City, State)?|||"nick" <nick@.discussions.microsoft.com> wrote in message
news:56387438-D942-40EB-AE98-26653AF2F0E4@.microsoft.com...
> I need to create an index with multple columns, for example, an index
> contains "State", "City" and "Street". Obviously the density of the
columns
> is State < City < Street. Is it necessary to follow a particular order
when
> create index for better performance? For example (State, City, Street) or
> (Street, City, State)?
That depends on what you are searching for, and how you want your data
sorted.
If you were selecting city and street, then the State, City, Street index
would not be useful.
Think of the data in your index as if it were listed in that order in your
phone book. If you want to find everyone in the state, sorted by city and
street, then having the phone laid out by State, City and then Street would
work best for that particular query.
Take a look at the index tuning wizard. It can provide you a lot of great
information about which indexes to create given a workload.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||Tips on Optimizing SQL Server Composite Indexes
http://www.sql-server-performance.com/composite_indexes.asp
AMB
"nick" wrote:
> I need to create an index with multple columns, for example, an index
> contains "State", "City" and "Street". Obviously the density of the columns
> is State < City < Street. Is it necessary to follow a particular order when
> create index for better performance? For example (State, City, Street) or
> (Street, City, State)?|||Adam,
I am a little bit confused. If I create the following index:
use northwind
go
create nonclustered index ix_nc_employees_country_city_postalcode on
employees(country, city, postalcode)
go
dbcc show_statistics (employees, ix_nc_employees_country_city_postalcode)
go
I see statistics for:
- country
- country, city
- country, city, postalcode
how does this apply to "index statistics are only computed for the first
column in the index"?
if I do:
set showplan_text on
go
select
*
from
dbo.employees
where
country = 'usa'
and city like 's%'
go
set showplan_text on
go
the stimated execution plan shows an index seek in
ix_nc_employees_country_city_postalcode.
Thanks in advance,
Alejandro Mesa
"Adam Machanic" wrote:
> Yes, it can have a huge impact. Higher selectivity = less rows traversed => better performance. Also remember that index statistics are only computed
> for the first column in the index; statistics play a large part when the
> optimizer does its job, so it's important to keep that in mind.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "nick" <nick@.discussions.microsoft.com> wrote in message
> news:56387438-D942-40EB-AE98-26653AF2F0E4@.microsoft.com...
> > I need to create an index with multple columns, for example, an index
> > contains "State", "City" and "Street". Obviously the density of the
> columns
> > is State < City < Street. Is it necessary to follow a particular order
> when
> > create index for better performance? For example (State, City, Street) or
> > (Street, City, State)?
>
>|||Sorry Mike, but the order of the logical expressions in the where clause is
not important. SQL Server does not evaluate them in a specific order. What I
think is important is that the columns in the where clause follow this
pattern (the order of the logical expressions is not important):
...
where state ...
-- or
where state ... and city ...
-- or
where state ... and street ...
-- or
...
where state ... and city ... and street ...
if you filter by (city, street) then sql server will not implement an index
seek in this index.
Regards,
Alejandro Mesa
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Based on your where clause.
> If you have an index on State, City, Street and use a where clause City,
> State, Street, the index will not be used. In this case, SQL can not guess
> that state is of any use, and it then can't use the index for the sort.
> Maybe and Index on state, a separate one on city, and a separate one on
> street.
> A compound index is generally a bad idea as a data modification will result
> in a lot of index maintenance, slowing down the operation.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "nick" <nick@.discussions.microsoft.com> wrote in message
> news:56387438-D942-40EB-AE98-26653AF2F0E4@.microsoft.com...
> >I need to create an index with multple columns, for example, an index
> > contains "State", "City" and "Street". Obviously the density of the
> > columns
> > is State < City < Street. Is it necessary to follow a particular order
> > when
> > create index for better performance? For example (State, City, Street) or
> > (Street, City, State)?
>
>|||So to maxmize the performance (without think about the update penalty), we
better create three index:
(state, city, street)
(City, Street)
(Street)
Or the following order is the same?
(Street, City, State)
(City, State)
(State)
will be used for all situation like
where state='...' and street = '...' and city='...' (Order doesn't matter?)
where street = ''
where city='' and street=''
but not "where sat
"Alejandro Mesa" wrote:
> Sorry Mike, but the order of the logical expressions in the where clause is
> not important. SQL Server does not evaluate them in a specific order. What I
> think is important is that the columns in the where clause follow this
> pattern (the order of the logical expressions is not important):
> ...
> where state ...
> -- or
> where state ... and city ...
> -- or
> where state ... and street ...
> -- or
> ...
> where state ... and city ... and street ...
> if you filter by (city, street) then sql server will not implement an index
> seek in this index.
>
> Regards,
> Alejandro Mesa
> "Mike Epprecht (SQL MVP)" wrote:
> > Hi
> >
> > Based on your where clause.
> >
> > If you have an index on State, City, Street and use a where clause City,
> > State, Street, the index will not be used. In this case, SQL can not guess
> > that state is of any use, and it then can't use the index for the sort.
> >
> > Maybe and Index on state, a separate one on city, and a separate one on
> > street.
> >
> > A compound index is generally a bad idea as a data modification will result
> > in a lot of index maintenance, slowing down the operation.
> >
> > Regards
> > --
> > Mike Epprecht, Microsoft SQL Server MVP
> > Zurich, Switzerland
> >
> > IM: mike@.epprecht.net
> >
> > MVP Program: http://www.microsoft.com/mvp
> >
> > Blog: http://www.msmvps.com/epprecht/
> >
> > "nick" <nick@.discussions.microsoft.com> wrote in message
> > news:56387438-D942-40EB-AE98-26653AF2F0E4@.microsoft.com...
> > >I need to create an index with multple columns, for example, an index
> > > contains "State", "City" and "Street". Obviously the density of the
> > > columns
> > > is State < City < Street. Is it necessary to follow a particular order
> > > when
> > > create index for better performance? For example (State, City, Street) or
> > > (Street, City, State)?
> >
> >
> >|||"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:327FEF28-39BC-4B5C-991F-612139545D77@.microsoft.com...
> I see statistics for:
> - country
> - country, city
> - country, city, postalcode
> how does this apply to "index statistics are only computed for the first
> column in the index"?
I probably phrased that poorly. Should have been, "index statistics are
based on the first column in the index." Which, I hope, serves to imply
that no statistics will be computed on only the second column, or only the
second and third columns, etc. The first column is the key.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||If you create the composite index described, SHOW_STATISTICS will show you
density info for each subset of keys, but the density info is only the
average number of duplicates across all values. Following the density info
you should see the statistics, which is the histogram on number of
occurences of specific values for the index key. That only applies to the
leading column of the index. Please see this whitepaper for more info:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/statquery.asp?frame=true
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:327FEF28-39BC-4B5C-991F-612139545D77@.microsoft.com...
> Adam,
> I am a little bit confused. If I create the following index:
> use northwind
> go
> create nonclustered index ix_nc_employees_country_city_postalcode on
> employees(country, city, postalcode)
> go
> dbcc show_statistics (employees, ix_nc_employees_country_city_postalcode)
> go
> I see statistics for:
> - country
> - country, city
> - country, city, postalcode
> how does this apply to "index statistics are only computed for the first
> column in the index"?
> if I do:
> set showplan_text on
> go
> select
> *
> from
> dbo.employees
> where
> country = 'usa'
> and city like 's%'
> go
> set showplan_text on
> go
> the stimated execution plan shows an index seek in
> ix_nc_employees_country_city_postalcode.
>
> Thanks in advance,
> Alejandro Mesa
> "Adam Machanic" wrote:
>> Yes, it can have a huge impact. Higher selectivity = less rows traversed
>> =>> better performance. Also remember that index statistics are only
>> computed
>> for the first column in the index; statistics play a large part when the
>> optimizer does its job, so it's important to keep that in mind.
>>
>> --
>> Adam Machanic
>> SQL Server MVP
>> http://www.datamanipulation.net
>> --
>>
>> "nick" <nick@.discussions.microsoft.com> wrote in message
>> news:56387438-D942-40EB-AE98-26653AF2F0E4@.microsoft.com...
>> > I need to create an index with multple columns, for example, an index
>> > contains "State", "City" and "Street". Obviously the density of the
>> columns
>> > is State < City < Street. Is it necessary to follow a particular order
>> when
>> > create index for better performance? For example (State, City, Street)
>> > or
>> > (Street, City, State)?
>>|||Thanks for the explanation.
AMB
"Adam Machanic" wrote:
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:327FEF28-39BC-4B5C-991F-612139545D77@.microsoft.com...
> >
> > I see statistics for:
> >
> > - country
> > - country, city
> > - country, city, postalcode
> >
> > how does this apply to "index statistics are only computed for the first
> > column in the index"?
> I probably phrased that poorly. Should have been, "index statistics are
> based on the first column in the index." Which, I hope, serves to imply
> that no statistics will be computed on only the second column, or only the
> second and third columns, etc. The first column is the key.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
>|||Kalen,
Thanks a lot for the link. It helped me to understand the theme better.
Does Sql Server query optimizer use the density of the combination of
columns (in the case of multi-column statistics, or an index) in conjuntion
with the histogram to choose the most efficient plan?
Thanks in advance,
Alejandro Mesa
"Kalen Delaney" wrote:
> If you create the composite index described, SHOW_STATISTICS will show you
> density info for each subset of keys, but the density info is only the
> average number of duplicates across all values. Following the density info
> you should see the statistics, which is the histogram on number of
> occurences of specific values for the index key. That only applies to the
> leading column of the index. Please see this whitepaper for more info:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/statquery.asp?frame=true
>
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:327FEF28-39BC-4B5C-991F-612139545D77@.microsoft.com...
> > Adam,
> >
> > I am a little bit confused. If I create the following index:
> >
> > use northwind
> > go
> >
> > create nonclustered index ix_nc_employees_country_city_postalcode on
> > employees(country, city, postalcode)
> > go
> >
> > dbcc show_statistics (employees, ix_nc_employees_country_city_postalcode)
> > go
> >
> > I see statistics for:
> >
> > - country
> > - country, city
> > - country, city, postalcode
> >
> > how does this apply to "index statistics are only computed for the first
> > column in the index"?
> >
> > if I do:
> >
> > set showplan_text on
> > go
> >
> > select
> > *
> > from
> > dbo.employees
> > where
> > country = 'usa'
> > and city like 's%'
> > go
> >
> > set showplan_text on
> > go
> >
> > the stimated execution plan shows an index seek in
> > ix_nc_employees_country_city_postalcode.
> >
> >
> > Thanks in advance,
> >
> > Alejandro Mesa
> >
> > "Adam Machanic" wrote:
> >
> >> Yes, it can have a huge impact. Higher selectivity = less rows traversed
> >> => >> better performance. Also remember that index statistics are only
> >> computed
> >> for the first column in the index; statistics play a large part when the
> >> optimizer does its job, so it's important to keep that in mind.
> >>
> >>
> >> --
> >> Adam Machanic
> >> SQL Server MVP
> >> http://www.datamanipulation.net
> >> --
> >>
> >>
> >> "nick" <nick@.discussions.microsoft.com> wrote in message
> >> news:56387438-D942-40EB-AE98-26653AF2F0E4@.microsoft.com...
> >> > I need to create an index with multple columns, for example, an index
> >> > contains "State", "City" and "Street". Obviously the density of the
> >> columns
> >> > is State < City < Street. Is it necessary to follow a particular order
> >> when
> >> > create index for better performance? For example (State, City, Street)
> >> > or
> >> > (Street, City, State)?
> >>
> >>
> >>
>
>|||Nick,
It depends on the selectivity of each column. Try using one index by each
column, test and see if it helps. If it does not help, then start doing
combinations (composite keys help to have better selectivity but at the
expense of more load when updating the keys), using in the most left side of
the key, the column with best selectivity or if the selectivity are similar
(or good selectivity), use the one you will use alone in a query.
Take the time to read the link posted by Kalen and all the good tips from
http://www.sql-server-performance.com/
AMB
"nick" wrote:
> So to maxmize the performance (without think about the update penalty), we
> better create three index:
> (state, city, street)
> (City, Street)
> (Street)
> Or the following order is the same?
> (Street, City, State)
> (City, State)
> (State)
> will be used for all situation like
> where state='...' and street = '...' and city='...' (Order doesn't matter?)
> where street = ''
> where city='' and street=''
> but not "where sat
> "Alejandro Mesa" wrote:
> > Sorry Mike, but the order of the logical expressions in the where clause is
> > not important. SQL Server does not evaluate them in a specific order. What I
> > think is important is that the columns in the where clause follow this
> > pattern (the order of the logical expressions is not important):
> >
> > ...
> > where state ...
> >
> > -- or
> >
> > where state ... and city ...
> >
> > -- or
> >
> > where state ... and street ...
> >
> > -- or
> > ...
> > where state ... and city ... and street ...
> >
> > if you filter by (city, street) then sql server will not implement an index
> > seek in this index.
> >
> >
> > Regards,
> >
> > Alejandro Mesa
> >
> > "Mike Epprecht (SQL MVP)" wrote:
> >
> > > Hi
> > >
> > > Based on your where clause.
> > >
> > > If you have an index on State, City, Street and use a where clause City,
> > > State, Street, the index will not be used. In this case, SQL can not guess
> > > that state is of any use, and it then can't use the index for the sort.
> > >
> > > Maybe and Index on state, a separate one on city, and a separate one on
> > > street.
> > >
> > > A compound index is generally a bad idea as a data modification will result
> > > in a lot of index maintenance, slowing down the operation.
> > >
> > > Regards
> > > --
> > > Mike Epprecht, Microsoft SQL Server MVP
> > > Zurich, Switzerland
> > >
> > > IM: mike@.epprecht.net
> > >
> > > MVP Program: http://www.microsoft.com/mvp
> > >
> > > Blog: http://www.msmvps.com/epprecht/
> > >
> > > "nick" <nick@.discussions.microsoft.com> wrote in message
> > > news:56387438-D942-40EB-AE98-26653AF2F0E4@.microsoft.com...
> > > >I need to create an index with multple columns, for example, an index
> > > > contains "State", "City" and "Street". Obviously the density of the
> > > > columns
> > > > is State < City < Street. Is it necessary to follow a particular order
> > > > when
> > > > create index for better performance? For example (State, City, Street) or
> > > > (Street, City, State)?
> > >
> > >
> > >|||There may be cases where it uses both, but in general, if the optimizer has
a specific value to work with it uses the histogram, otherwise it uses the
density.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:9EA20A9A-CB5B-42E4-BABE-1FF889E2EDA1@.microsoft.com...
> Kalen,
> Thanks a lot for the link. It helped me to understand the theme better.
> Does Sql Server query optimizer use the density of the combination of
> columns (in the case of multi-column statistics, or an index) in
> conjuntion
> with the histogram to choose the most efficient plan?
>
> Thanks in advance,
> Alejandro Mesa
>
> "Kalen Delaney" wrote:
>> If you create the composite index described, SHOW_STATISTICS will show
>> you
>> density info for each subset of keys, but the density info is only the
>> average number of duplicates across all values. Following the density
>> info
>> you should see the statistics, which is the histogram on number of
>> occurences of specific values for the index key. That only applies to the
>> leading column of the index. Please see this whitepaper for more info:
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/statquery.asp?frame=true
>>
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
>> message
>> news:327FEF28-39BC-4B5C-991F-612139545D77@.microsoft.com...
>> > Adam,
>> >
>> > I am a little bit confused. If I create the following index:
>> >
>> > use northwind
>> > go
>> >
>> > create nonclustered index ix_nc_employees_country_city_postalcode on
>> > employees(country, city, postalcode)
>> > go
>> >
>> > dbcc show_statistics (employees,
>> > ix_nc_employees_country_city_postalcode)
>> > go
>> >
>> > I see statistics for:
>> >
>> > - country
>> > - country, city
>> > - country, city, postalcode
>> >
>> > how does this apply to "index statistics are only computed for the
>> > first
>> > column in the index"?
>> >
>> > if I do:
>> >
>> > set showplan_text on
>> > go
>> >
>> > select
>> > *
>> > from
>> > dbo.employees
>> > where
>> > country = 'usa'
>> > and city like 's%'
>> > go
>> >
>> > set showplan_text on
>> > go
>> >
>> > the stimated execution plan shows an index seek in
>> > ix_nc_employees_country_city_postalcode.
>> >
>> >
>> > Thanks in advance,
>> >
>> > Alejandro Mesa
>> >
>> > "Adam Machanic" wrote:
>> >
>> >> Yes, it can have a huge impact. Higher selectivity = less rows
>> >> traversed
>> >> =>> >> better performance. Also remember that index statistics are only
>> >> computed
>> >> for the first column in the index; statistics play a large part when
>> >> the
>> >> optimizer does its job, so it's important to keep that in mind.
>> >>
>> >>
>> >> --
>> >> Adam Machanic
>> >> SQL Server MVP
>> >> http://www.datamanipulation.net
>> >> --
>> >>
>> >>
>> >> "nick" <nick@.discussions.microsoft.com> wrote in message
>> >> news:56387438-D942-40EB-AE98-26653AF2F0E4@.microsoft.com...
>> >> > I need to create an index with multple columns, for example, an
>> >> > index
>> >> > contains "State", "City" and "Street". Obviously the density of the
>> >> columns
>> >> > is State < City < Street. Is it necessary to follow a particular
>> >> > order
>> >> when
>> >> > create index for better performance? For example (State, City,
>> >> > Street)
>> >> > or
>> >> > (Street, City, State)?
>> >>
>> >>
>> >>
>>|||Kalen,
Thanks again, I really appreciate your help.
Alejandro Mesa
"Kalen Delaney" wrote:
> There may be cases where it uses both, but in general, if the optimizer has
> a specific value to work with it uses the histogram, otherwise it uses the
> density.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:9EA20A9A-CB5B-42E4-BABE-1FF889E2EDA1@.microsoft.com...
> > Kalen,
> >
> > Thanks a lot for the link. It helped me to understand the theme better.
> >
> > Does Sql Server query optimizer use the density of the combination of
> > columns (in the case of multi-column statistics, or an index) in
> > conjuntion
> > with the histogram to choose the most efficient plan?
> >
> >
> > Thanks in advance,
> >
> > Alejandro Mesa
> >
> >
> > "Kalen Delaney" wrote:
> >
> >> If you create the composite index described, SHOW_STATISTICS will show
> >> you
> >> density info for each subset of keys, but the density info is only the
> >> average number of duplicates across all values. Following the density
> >> info
> >> you should see the statistics, which is the histogram on number of
> >> occurences of specific values for the index key. That only applies to the
> >> leading column of the index. Please see this whitepaper for more info:
> >>
> >> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/statquery.asp?frame=true
> >>
> >>
> >> --
> >> HTH
> >> --
> >> Kalen Delaney
> >> SQL Server MVP
> >> www.SolidQualityLearning.com
> >>
> >>
> >> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> >> message
> >> news:327FEF28-39BC-4B5C-991F-612139545D77@.microsoft.com...
> >> > Adam,
> >> >
> >> > I am a little bit confused. If I create the following index:
> >> >
> >> > use northwind
> >> > go
> >> >
> >> > create nonclustered index ix_nc_employees_country_city_postalcode on
> >> > employees(country, city, postalcode)
> >> > go
> >> >
> >> > dbcc show_statistics (employees,
> >> > ix_nc_employees_country_city_postalcode)
> >> > go
> >> >
> >> > I see statistics for:
> >> >
> >> > - country
> >> > - country, city
> >> > - country, city, postalcode
> >> >
> >> > how does this apply to "index statistics are only computed for the
> >> > first
> >> > column in the index"?
> >> >
> >> > if I do:
> >> >
> >> > set showplan_text on
> >> > go
> >> >
> >> > select
> >> > *
> >> > from
> >> > dbo.employees
> >> > where
> >> > country = 'usa'
> >> > and city like 's%'
> >> > go
> >> >
> >> > set showplan_text on
> >> > go
> >> >
> >> > the stimated execution plan shows an index seek in
> >> > ix_nc_employees_country_city_postalcode.
> >> >
> >> >
> >> > Thanks in advance,
> >> >
> >> > Alejandro Mesa
> >> >
> >> > "Adam Machanic" wrote:
> >> >
> >> >> Yes, it can have a huge impact. Higher selectivity = less rows
> >> >> traversed
> >> >> => >> >> better performance. Also remember that index statistics are only
> >> >> computed
> >> >> for the first column in the index; statistics play a large part when
> >> >> the
> >> >> optimizer does its job, so it's important to keep that in mind.
> >> >>
> >> >>
> >> >> --
> >> >> Adam Machanic
> >> >> SQL Server MVP
> >> >> http://www.datamanipulation.net
> >> >> --
> >> >>
> >> >>
> >> >> "nick" <nick@.discussions.microsoft.com> wrote in message
> >> >> news:56387438-D942-40EB-AE98-26653AF2F0E4@.microsoft.com...
> >> >> > I need to create an index with multple columns, for example, an
> >> >> > index
> >> >> > contains "State", "City" and "Street". Obviously the density of the
> >> >> columns
> >> >> > is State < City < Street. Is it necessary to follow a particular
> >> >> > order
> >> >> when
> >> >> > create index for better performance? For example (State, City,
> >> >> > Street)
> >> >> > or
> >> >> > (Street, City, State)?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>