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 or not to index

I have a "Products" table, which currently only has the idProduct primary
key indexed. I now have a requirement to perform searches through an ASP
front end (ASP front end issuing SQL directly against SQL using SQL Server
OLE DB Provider) on
- a details text(16) field
- a description varchar(250) field
- a descriptionLong varchar(250) field
At any time, this table would have around 150-300 records, although records
do get added and deleted from time to time. I am considering Creating an
INDEX on details, description and descriptionLong field. However, I am
concerned
- whether creating an index on such a relatively small table would really
give performance gain or would it just add towards "inefficiency"
- if creating index is deemed a good idea, then what kind of index should I
use?
- Since records get added and deleted from time to time, would it be a good
idea to "re-index" the table (presumably, as part of the DB Maintenance
plan)?
Many thanks in advance!I think you shouldn't create an index because this index will be hardly used
but will decrease performance of DML commands execution
"Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
news:OjTbwYXQEHA.620@.TK2MSFTNGP10.phx.gbl...
> I have a "Products" table, which currently only has the idProduct primary
> key indexed. I now have a requirement to perform searches through an ASP
> front end (ASP front end issuing SQL directly against SQL using SQL Server
> OLE DB Provider) on
> - a details text(16) field
> - a description varchar(250) field
> - a descriptionLong varchar(250) field
> At any time, this table would have around 150-300 records, although
records
> do get added and deleted from time to time. I am considering Creating an
> INDEX on details, description and descriptionLong field. However, I am
> concerned
> - whether creating an index on such a relatively small table would really
> give performance gain or would it just add towards "inefficiency"
> - if creating index is deemed a good idea, then what kind of index should
I
> use?
> - Since records get added and deleted from time to time, would it be a
good
> idea to "re-index" the table (presumably, as part of the DB Maintenance
> plan)?
> Many thanks in advance!
>|||Definitelly not to index. It is too small, index fields are too large...|||Hi Patrick,
Alex Cieszinski and Bojidar Alexandrov has give you their suggestions I
wanted to post a quick note to see if you would like additional assistance
or information regarding this particular issue.
We appreciate your patience and look forward to hearing from you!
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Online Support
---
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Are you Michael or Mingqing after all |||Hi Bojidar,
Michael is Mingqing
Thank you :D
Sincerely yours,
Mingqing Cheng
Microsoft Online Support
---
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Index or not to index

I have a "Products" table, which currently only has the idProduct primary
key indexed. I now have a requirement to perform searches through an ASP
front end (ASP front end issuing SQL directly against SQL using SQL Server
OLE DB Provider) on
- a details text(16) field
- a description varchar(250) field
- a descriptionLong varchar(250) field
At any time, this table would have around 150-300 records, although records
do get added and deleted from time to time. I am considering Creating an
INDEX on details, description and descriptionLong field. However, I am
concerned
- whether creating an index on such a relatively small table would really
give performance gain or would it just add towards "inefficiency"
- if creating index is deemed a good idea, then what kind of index should I
use?
- Since records get added and deleted from time to time, would it be a good
idea to "re-index" the table (presumably, as part of the DB Maintenance
plan)?
Many thanks in advance!
I think you shouldn't create an index because this index will be hardly used
but will decrease performance of DML commands execution
"Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
news:OjTbwYXQEHA.620@.TK2MSFTNGP10.phx.gbl...
> I have a "Products" table, which currently only has the idProduct primary
> key indexed. I now have a requirement to perform searches through an ASP
> front end (ASP front end issuing SQL directly against SQL using SQL Server
> OLE DB Provider) on
> - a details text(16) field
> - a description varchar(250) field
> - a descriptionLong varchar(250) field
> At any time, this table would have around 150-300 records, although
records
> do get added and deleted from time to time. I am considering Creating an
> INDEX on details, description and descriptionLong field. However, I am
> concerned
> - whether creating an index on such a relatively small table would really
> give performance gain or would it just add towards "inefficiency"
> - if creating index is deemed a good idea, then what kind of index should
I
> use?
> - Since records get added and deleted from time to time, would it be a
good
> idea to "re-index" the table (presumably, as part of the DB Maintenance
> plan)?
> Many thanks in advance!
>
|||Definitelly not to index. It is too small, index fields are too large...
|||Hi Patrick,
Alex Cieszinski and Bojidar Alexandrov has give you their suggestions I
wanted to post a quick note to see if you would like additional assistance
or information regarding this particular issue.
We appreciate your patience and look forward to hearing from you!
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Online Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Are you Michael or Mingqing after all
|||Hi Bojidar,
Michael is Mingqing
Thank you :D
Sincerely yours,
Mingqing Cheng
Microsoft Online Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
sql

Index or not to index

I have a "Products" table, which currently only has the idProduct primary
key indexed. I now have a requirement to perform searches through an ASP
front end (ASP front end issuing SQL directly against SQL using SQL Server
OLE DB Provider) on
- a details text(16) field
- a description varchar(250) field
- a descriptionLong varchar(250) field
At any time, this table would have around 150-300 records, although records
do get added and deleted from time to time. I am considering Creating an
INDEX on details, description and descriptionLong field. However, I am
concerned
- whether creating an index on such a relatively small table would really
give performance gain or would it just add towards "inefficiency"
- if creating index is deemed a good idea, then what kind of index should I
use?
- Since records get added and deleted from time to time, would it be a good
idea to "re-index" the table (presumably, as part of the DB Maintenance
plan)?
Many thanks in advance!I think you shouldn't create an index because this index will be hardly used
but will decrease performance of DML commands execution
"Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
news:OjTbwYXQEHA.620@.TK2MSFTNGP10.phx.gbl...
> I have a "Products" table, which currently only has the idProduct primary
> key indexed. I now have a requirement to perform searches through an ASP
> front end (ASP front end issuing SQL directly against SQL using SQL Server
> OLE DB Provider) on
> - a details text(16) field
> - a description varchar(250) field
> - a descriptionLong varchar(250) field
> At any time, this table would have around 150-300 records, although
records
> do get added and deleted from time to time. I am considering Creating an
> INDEX on details, description and descriptionLong field. However, I am
> concerned
> - whether creating an index on such a relatively small table would really
> give performance gain or would it just add towards "inefficiency"
> - if creating index is deemed a good idea, then what kind of index should
I
> use?
> - Since records get added and deleted from time to time, would it be a
good
> idea to "re-index" the table (presumably, as part of the DB Maintenance
> plan)?
> Many thanks in advance!
>|||Definitelly not to index. It is too small, index fields are too large...|||Hi Patrick,
Alex Cieszinski and Bojidar Alexandrov has give you their suggestions :) I
wanted to post a quick note to see if you would like additional assistance
or information regarding this particular issue.
We appreciate your patience and look forward to hearing from you!
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Online Support
---
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Are you Michael or Mingqing after all :)|||Hi Bojidar,
Michael is Mingqing :)
Thank you :D
Sincerely yours,
Mingqing Cheng
Microsoft Online Support
---
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Index on Views ...

I know that we can create indexes on views (Indexed views) in sql 2k. I am
trying to understand what would be the reason for creating a index on a view
.
Anyway a view is a virtual table and if there is an index on those table
won't it be enough? Hope somebody could make me understand this.
Regards
PradeepHi
I think you missed the concept of INDEXED VIEWS
http://www.sql-server-performance.com/indexed_views.asp
In addition please take a look at Steve Kass's scipt to ensure that using
an indexe view SQL Server creates more efficient execution plan
create table T (
i int,
filler char(1000) default 'abc'
)
go
create view T_count with schemabinding as
select
cast(i as bit) as val,
count_big(*) T_count
from dbo.T group by cast(i as bit)
go
create unique clustered index T_count_uci on T_count(val)
go
insert into T(i)
select OrderID
from Northwind..[Order Details]
go
set statistics io on
select count(*) from T
go
select sum(T_count) from T_count with (noexpand)
go
set statistics io off
-- uses an efficient query plan on the materialized view
go
drop view T_count
drop table T
"SqlBeginner" <SqlBeginner@.discussions.microsoft.com> wrote in message
news:F47FC536-3817-4DCE-A1F5-8E1C077ACDCF@.microsoft.com...
>I know that we can create indexes on views (Indexed views) in sql 2k. I am
> trying to understand what would be the reason for creating a index on a
> view.
> Anyway a view is a virtual table and if there is an index on those table
> won't it be enough? Hope somebody could make me understand this.
> Regards
> Pradeep|||Thanks for ur response Uri. I would go through that link.
Regards
Pradeep
"Uri Dimant" wrote:

> Hi
> I think you missed the concept of INDEXED VIEWS
> http://www.sql-server-performance.com/indexed_views.asp
>
> In addition please take a look at Steve Kass's scipt to ensure that using
> an indexe view SQL Server creates more efficient execution plan
> create table T (
> i int,
> filler char(1000) default 'abc'
> )
> go
> create view T_count with schemabinding as
> select
> cast(i as bit) as val,
> count_big(*) T_count
> from dbo.T group by cast(i as bit)
> go
> create unique clustered index T_count_uci on T_count(val)
> go
> insert into T(i)
> select OrderID
> from Northwind..[Order Details]
> go
> set statistics io on
> select count(*) from T
> go
> select sum(T_count) from T_count with (noexpand)
> go
> set statistics io off
> -- uses an efficient query plan on the materialized view
> go
> drop view T_count
> drop table T
>
>
> "SqlBeginner" <SqlBeginner@.discussions.microsoft.com> wrote in message
> news:F47FC536-3817-4DCE-A1F5-8E1C077ACDCF@.microsoft.com...
>
>

index on PK

Hello, could someone please tell me why you would index a Primary Key column. By default is it not already indexed, and isn't a primary key unique? If so why would you index a unique column? I'm sure this is a dumb question and I probably don't understand the concept of indexes in SQL. Any insight is well appreciated. Thanks.BOL:

When you specify a PRIMARY KEY constraint for a table, Microsoft SQL Server 2000 enforces data uniqueness by creating a unique index for the primary key columns. This index also permits fast access to data when the primary key is used in queries.

Index on Computed column or Indexed View

I have a large nvarchar(2000) that need to be queried on often based on a
subset of the data (first 50 characters). The application creating and using
the data cannot be modified to capture a short and long column... I was
wondering if creating a computed column with the formula being
Left(longcolumn, 50) and creating an index based on this column could be a
good option? Or would it be preferable to create an indexed view?
Any other suggestion are welcomed
Thank you for your helpI would go with a computed column to start with.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Martin Rajotte" <MartinRajotte@.discussions.microsoft.com> wrote in message
news:0EB763CD-5C4E-455C-83E2-1454742D5507@.microsoft.com...
> I have a large nvarchar(2000) that need to be queried on often based on a
> subset of the data (first 50 characters). The application creating and
using
> the data cannot be modified to capture a short and long column... I was
> wondering if creating a computed column with the formula being
> Left(longcolumn, 50) and creating an index based on this column could be a
> good option? Or would it be preferable to create an indexed view?
> Any other suggestion are welcomed
> Thank you for your help
>|||Thank you for help. It confirms my tests that I performed last night.
"Narayana Vyas Kondreddi" wrote:

> I would go with a computed column to start with.
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Martin Rajotte" <MartinRajotte@.discussions.microsoft.com> wrote in messag
e
> news:0EB763CD-5C4E-455C-83E2-1454742D5507@.microsoft.com...
> using
>
>

Index in view

I 've 5 tables not indexed(must).
Now i create 5 views for these with indexing.
creating index only on views are possible, if so it can increase the
performance od query. Can u give me the soln ?Read about indexed views in Books Online. Note that only Enterprise Edition
will use such indexes
automatically. I would reconsider why you cannot create indexes on the base
tables. Why is that?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"JJFreds" <JJFreds@.discussions.microsoft.com> wrote in message
news:9A6FA327-D8F1-4A85-99F4-E521EEA400D8@.microsoft.com...
>I 've 5 tables not indexed(must).
> Now i create 5 views for these with indexing.
> creating index only on views are possible, if so it can increase the
> performance od query. Can u give me the soln ?

index and primary key

By defining a numeric field in table as primary key, will the table be indexed on that particular field?yes, a primary key always gets an index, that's how the database system determines if a value exists already or not (for uniqueness)|||am extending my qn a littl bit

suppose the table has the following structure

myTable
(
myPK bigint identity (Primary key)
myUniqNo bigint
myName varchar (50)
)

can i create an index on myUniqNo, if myUniqNo is unique..|||You can create an index on almost any column, whether it is unique or not. You can create a unique constraint or a unique index on a column if there are no duplicate values in the column.

I'd recommend using a constraint instead of an index unless there is some specific, compelling reason for using the index.

-PatP|||yes you can

but then, if myUniqNo is going to be unique, why do you want an IDENTITY column as the primary key?

and by the way, why bigint? are you planning on having over 2 billion rows?|||but then, if myUniqNo is going to be unique, why do you want an IDENTITY column as the primary key?A surrogate key for the existing surrogate key? That way they can allow updates to their existing column?

Ow, ooo, ow! Quit throwing things, that hurts!!!

-PatP|||Yes, but then they can change the "key" without having to cascade all of the updates...|||I'd recommend using a constraint instead of an index unless there is some specific, compelling reason for using the index.Can you tell us why you'd recommend that? Do you know that when you create a unique constraint you implicitly create a unique index?|||Yes I'm sure Pat knows...I think Pat is spouting party line...M$ reccomends that as well...

Never could figure out why...or maybe we did and I forgot...|||About the only thing that a unique constraint has going for it as opposed to a unique index is that you can have a foreign key dependent on a unique constraint. After that, it gets a bit fuzzy. Does anyone know of any articles where the order of checks is done for an insert in SQL Server? For example, are check constraints checked before foreign keys are? Or do some triggers fire before computed columns are generated? That sort of information might give some insight.|||When UNIQUE constraint gets created, a UNIQUE index gets created at the same time with the same name. If you drop the constraint the index gets dropped with it, also implicitly. Trigger never gets to execute if uniqueness is violated either due to constraint or unique index.

Talking about differences, the only one I see is that while constraint is very strict in respect to controlling RI, unique index can be altered in such a way, where in a batch of 100 rows attempted to be inserted there is 1 duplicate row, 99 will be successfully inserted. Nothing can be done to accomplish the same with unique constraint. That's why MS (and Pat) recommend using constraints over indexes.|||Yes I'm sure Pat knows...I think Pat is spouting party line...M$ reccomends that as well...Not hardly... The only time I "spout party line" is when I'm actually at the party.

Creating a constraint creates metadata. Some programs use metadata now, and more will in the future. Metadata is an important stepping stone toward getting real "relational algebra" tools (especially things like OLAP), which will make life lots easier for everyone as they become more readily available.

-PatP|||dont forget the null.
you have to mention the 1 null...|||according to this thread (http://www.dbforums.com/t998479.html) there is a dodgy way around the "only 1 null in a unique index" problem, but i haven't confirmed that it works, i just bookmarked it|||Not hardly... The only time I "spout party line" is when I'm actually at the party.

Creating a constraint creates metadata. Some programs use metadata now, and more will in the future. Metadata is an important stepping stone toward getting real "relational algebra" tools (especially things like OLAP), which will make life lots easier for everyone as they become more readily available.

-PatP

Good Point...so where's the party?|||according to this thread (http://www.dbforums.com/t998479.html) there is a dodgy way around the "only 1 null in a unique index" problem, but i haven't confirmed that it works, i just bookmarked it

according to ruprect, its as simple as setting the column to not null. :D|||"...unique index can be altered in such a way, where in a batch of 100 rows attempted to be inserted there is 1 duplicate row, 99 will be successfully inserted. "

That is one I haven't seen before.

Got code?|||Never mind. Didn't read your post clearly.

Index / Search question

Is a full-text search the only way to find specific data in a table or
database? Can a full-text, indexed search scan an entire database instead of
individual tables?
Thanks!
Dave W
No, AFAIK thats no working, but you can use the script from Vysh.(even this
is not using any fulltext features, but extending your search all columns in
al tables):
http://vyaskn.tripod.com/search_all_...all_tables.htm
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Dave W" <DaveW@.discussions.microsoft.com> schrieb im Newsbeitrag
news:2E5175AB-9EDD-4D34-92A2-51E683161FF9@.microsoft.com...
> Is a full-text search the only way to find specific data in a table or
> database? Can a full-text, indexed search scan an entire database instead
> of
> individual tables?
> Thanks!
> --
> Dave W
|||Thanks Jens! Your reply was exactly what I needed!
Dave W
"Jens Sü?meyer" wrote:

> No, AFAIK thats no working, but you can use the script from Vysh.(even this
> is not using any fulltext features, but extending your search all columns in
> al tables):
> http://vyaskn.tripod.com/search_all_...all_tables.htm
>
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Dave W" <DaveW@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:2E5175AB-9EDD-4D34-92A2-51E683161FF9@.microsoft.com...
>
>

Index / Search question

Is a full-text search the only way to find specific data in a table or
database? Can a full-text, indexed search scan an entire database instead o
f
individual tables?
Thanks!
--
Dave WNo, AFAIK thats no working, but you can use the script from Vysh.(even this
is not using any fulltext features, but extending your search all columns in
al tables):
http://vyaskn.tripod.com/search_all..._all_tables.htm
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Dave W" <DaveW@.discussions.microsoft.com> schrieb im Newsbeitrag
news:2E5175AB-9EDD-4D34-92A2-51E683161FF9@.microsoft.com...
> Is a full-text search the only way to find specific data in a table or
> database? Can a full-text, indexed search scan an entire database instead
> of
> individual tables?
> Thanks!
> --
> Dave W|||Thanks Jens! Your reply was exactly what I needed!
--
Dave W
"Jens Sü?meyer" wrote:

> No, AFAIK thats no working, but you can use the script from Vysh.(even thi
s
> is not using any fulltext features, but extending your search all columns
in
> al tables):
> http://vyaskn.tripod.com/search_all..._all_tables.htm
>
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Dave W" <DaveW@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:2E5175AB-9EDD-4D34-92A2-51E683161FF9@.microsoft.com...
>
>

Index / Search question

Is a full-text search the only way to find specific data in a table or
database? Can a full-text, indexed search scan an entire database instead of
individual tables?
Thanks!
--
Dave WNo, AFAIK thats no working, but you can use the script from Vysh.(even this
is not using any fulltext features, but extending your search all columns in
al tables):
http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Dave W" <DaveW@.discussions.microsoft.com> schrieb im Newsbeitrag
news:2E5175AB-9EDD-4D34-92A2-51E683161FF9@.microsoft.com...
> Is a full-text search the only way to find specific data in a table or
> database? Can a full-text, indexed search scan an entire database instead
> of
> individual tables?
> Thanks!
> --
> Dave W|||Thanks Jens! Your reply was exactly what I needed!
--
Dave W
"Jens Sü�meyer" wrote:
> No, AFAIK thats no working, but you can use the script from Vysh.(even this
> is not using any fulltext features, but extending your search all columns in
> al tables):
> http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm
>
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Dave W" <DaveW@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:2E5175AB-9EDD-4D34-92A2-51E683161FF9@.microsoft.com...
> > Is a full-text search the only way to find specific data in a table or
> > database? Can a full-text, indexed search scan an entire database instead
> > of
> > individual tables?
> >
> > Thanks!
> > --
> > Dave W
>
>