any impact if create index and then drop index

hi,
i want to create index to speed up one operation (DELETE) and the drop the
index to resume.
any side effects or impacts on the db?
On Tue, 30 Nov 2004 15:34:47 +0800, Mullin Yu wrote:

>hi,
>i want to create index to speed up one operation (DELETE) and the drop the
>index to resume.
>any side effects or impacts on the db?
>
Hi Mullin,
Creating the index will take some time and will also lock parts of your
data. Dropping the index doesn't take much time (unless it is a clustered
index).
However, why would you want to drop the index after the delete?
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||because want to add an index on the third-party product for a temp
operation. don't know any impacts on that product if exist an index
persistently.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:fkdoq0p5sb73abfmke0phv999kjaa2k4p9@.4ax.com... [vbcol=seagreen]
> On Tue, 30 Nov 2004 15:34:47 +0800, Mullin Yu wrote:
the
> Hi Mullin,
> Creating the index will take some time and will also lock parts of your
> data. Dropping the index doesn't take much time (unless it is a clustered
> index).
> However, why would you want to drop the index after the delete?
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||On Wed, 1 Dec 2004 15:47:04 +0800, Mullin Yu wrote:

>because want to add an index on the third-party product for a temp
>operation. don't know any impacts on that product if exist an index
>persistently.
Hi Mullin,
An extra index will:
* Slightly decrease performance of inserts, deletes and updates that
affect the column(s) used in the index,
* Speed up those queries that can use this index,
* Slightly increase the size of databases and backups,
* Have no effect on any other operation.
All this on the assumption that you're talking about a nonclustered index.
A clustered index has more impact. (But since building and dropping a
clustered index is quite costly, I don't think you'd consider this for
your temp index).
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

any impact if create index and then drop index

hi,
i want to create index to speed up one operation (DELETE) and the drop the
index to resume.
any side effects or impacts on the db?On Tue, 30 Nov 2004 15:34:47 +0800, Mullin Yu wrote:

>hi,
>i want to create index to speed up one operation (DELETE) and the drop the
>index to resume.
>any side effects or impacts on the db?
>
Hi Mullin,
Creating the index will take some time and will also lock parts of your
data. Dropping the index doesn't take much time (unless it is a clustered
index).
However, why would you want to drop the index after the delete?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||because want to add an index on the third-party product for a temp
operation. don't know any impacts on that product if exist an index
persistently.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:fkdoq0p5sb73abfmke0phv999kjaa2k4p9@.
4ax.com...
> On Tue, 30 Nov 2004 15:34:47 +0800, Mullin Yu wrote:
>
the[vbcol=seagreen]
> Hi Mullin,
> Creating the index will take some time and will also lock parts of your
> data. Dropping the index doesn't take much time (unless it is a clustered
> index).
> However, why would you want to drop the index after the delete?
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Wed, 1 Dec 2004 15:47:04 +0800, Mullin Yu wrote:

>because want to add an index on the third-party product for a temp
>operation. don't know any impacts on that product if exist an index
>persistently.
Hi Mullin,
An extra index will:
* Slightly decrease performance of inserts, deletes and updates that
affect the column(s) used in the index,
* Speed up those queries that can use this index,
* Slightly increase the size of databases and backups,
* Have no effect on any other operation.
All this on the assumption that you're talking about a nonclustered index.
A clustered index has more impact. (But since building and dropping a
clustered index is quite costly, I don't think you'd consider this for
your temp index).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

any impact if create index and then drop index

hi,
i want to create index to speed up one operation (DELETE) and the drop the
index to resume.
any side effects or impacts on the db?On Tue, 30 Nov 2004 15:34:47 +0800, Mullin Yu wrote:
>hi,
>i want to create index to speed up one operation (DELETE) and the drop the
>index to resume.
>any side effects or impacts on the db?
>
Hi Mullin,
Creating the index will take some time and will also lock parts of your
data. Dropping the index doesn't take much time (unless it is a clustered
index).
However, why would you want to drop the index after the delete?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||because want to add an index on the third-party product for a temp
operation. don't know any impacts on that product if exist an index
persistently.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:fkdoq0p5sb73abfmke0phv999kjaa2k4p9@.4ax.com...
> On Tue, 30 Nov 2004 15:34:47 +0800, Mullin Yu wrote:
> >hi,
> >
> >i want to create index to speed up one operation (DELETE) and the drop
the
> >index to resume.
> >
> >any side effects or impacts on the db?
> >
> Hi Mullin,
> Creating the index will take some time and will also lock parts of your
> data. Dropping the index doesn't take much time (unless it is a clustered
> index).
> However, why would you want to drop the index after the delete?
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Wed, 1 Dec 2004 15:47:04 +0800, Mullin Yu wrote:
>because want to add an index on the third-party product for a temp
>operation. don't know any impacts on that product if exist an index
>persistently.
Hi Mullin,
An extra index will:
* Slightly decrease performance of inserts, deletes and updates that
affect the column(s) used in the index,
* Speed up those queries that can use this index,
* Slightly increase the size of databases and backups,
* Have no effect on any other operation.
All this on the assumption that you're talking about a nonclustered index.
A clustered index has more impact. (But since building and dropping a
clustered index is quite costly, I don't think you'd consider this for
your temp index).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Anti Virus software and SQL

Does anyone have a recommenation for AV software to work with SQL that does
not impact performance.How about a recommendation from Microsoft?
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Richard" <anonymous@.discussions.microsoft.com> wrote in message
news:7415981C-A736-4341-972C-76F4E6F89E8A@.microsoft.com...
> Does anyone have a recommenation for AV software to work with SQL that
does not impact performance.|||Lets try this again.
How about a recommendation from Microsoft:
INF: Consideration for a Virus Scanner on a Computer That Is Running SQL
Server
http://support.microsoft.com/defaul...2&Product=sql2k
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Richard" <anonymous@.discussions.microsoft.com> wrote in message
news:7415981C-A736-4341-972C-76F4E6F89E8A@.microsoft.com...
> Does anyone have a recommenation for AV software to work with SQL that
does not impact performance.

ANSI NULLS performance impact

Could someone explain why having the ANSI NULLS database option off causes
the following piece of SQL to run substantially slower. When the option is
on, in my applications ODBC settings, the query will return in less than a
second; however if the option is off then the query will take 28 seconds to
execute.
The execution plans are identical in both cases.
The application I am running is connecting via ODBC to SQL Server2000
database.
I was interested as to why this option had such a big impact on performance
- could someone explain why?
SELECT
instrument.instrument_id,
instrument.name
FROM
instrument
WHERE
(instrument.instrument_id IN ('4056719', '4072168', '4072919' .... { about
700 items in this list} ... ))
AND ((instrument.instrument_subtype_customer in ('FB', 'OB', 'SB', 'CO',
...{more items } ... 'SZ', 'WZ', 'PO', 'FS'))
and ((instrument.exchange_customer in ('EA1', 'EB1', 'LF1', 'FC1',
'EC1',...{more items } ... 'NA1', 'NAL', 'NAI')))
and not ((instrument.instrument_subtype_customer = 'DC')))
Execution plan
|--Bookmark Lookup(BOOKMARK:([Bmk1000]),
OBJECT:([PROD_GART_S40M0T0ODB].[dbo].[instrument]))
|--Hash Match(Inner Join, HASH:([Bmk1000])=([Bmk1000]),
RESIDUAL:([Bmk1000]=[Bmk1000]))
|--Hash Match(Inner Join, HASH:([Bmk1000])=([Bmk1000]),
RESIDUAL:([Bmk1000]=[Bmk1000]))
| |--Index
S(OBJECT:([PROD_GART_S40M0T0ODB].[dbo].[instrument].[pk_instrument_instrument_id]),
SEEK:([instrument].[instrument_id] >= '4000998' AND
[instrument].[instrument_id] <= '4000998' OR [instrument].[instrument_id] >=
'4002121' AND
| |--Index
S(OBJECT:([PROD_GART_S40M0T0ODB].[dbo].[instrument].[idx_instrument_exchange_customer]),
SEEK:([instrument].[exchange_customer] >= 'EA1' AND
[instrument].[exchange_customer] <= 'EA1' OR [instrument].[exchange_customer]
>= 'EB1'
|--Index
S(OBJECT:([PROD_GART_S40M0T0ODB].[dbo].[instrument].[idx_instrument_subtype_customer]),
SEEK:([instrument].[instrument_subtype_customer] >= 'CO' AND
[instrument].[instrument_subtype_customer] <= 'CO' OR
[instrument].[instrument_subtWeird stuff can happen when setting ansi_nulls to off.
A NULL Puzzle
http://groups-beta.google.com/group...aca91a7912bdc76
AMB
"dave" wrote:

> Could someone explain why having the ANSI NULLS database option off causes
> the following piece of SQL to run substantially slower. When the option is
> on, in my applications ODBC settings, the query will return in less than a
> second; however if the option is off then the query will take 28 seconds t
o
> execute.
> The execution plans are identical in both cases.
> The application I am running is connecting via ODBC to SQL Server2000
> database.
> I was interested as to why this option had such a big impact on performanc
e
> - could someone explain why?
>
> SELECT
> instrument.instrument_id,
> instrument.name
> FROM
> instrument
> WHERE
> (instrument.instrument_id IN ('4056719', '4072168', '4072919' .... { abo
ut
> 700 items in this list} ... ))
> AND ((instrument.instrument_subtype_customer in ('FB', 'OB', 'SB', 'CO',
> ...{more items } ... 'SZ', 'WZ', 'PO', 'FS'))
> and ((instrument.exchange_customer in ('EA1', 'EB1', 'LF1', 'FC1',
> 'EC1',...{more items } ... 'NA1', 'NAL', 'NAI')))
> and not ((instrument.instrument_subtype_customer = 'DC')))
>
> Execution plan
> |--Bookmark Lookup(BOOKMARK:([Bmk1000]),
> OBJECT:([PROD_GART_S40M0T0ODB].[dbo].[instrument]))
> |--Hash Match(Inner Join, HASH:([Bmk1000])=([Bmk1000]),
> RESIDUAL:([Bmk1000]=[Bmk1000]))
> |--Hash Match(Inner Join, HASH:([Bmk1000])=([Bmk1000]),
> RESIDUAL:([Bmk1000]=[Bmk1000]))
> | |--Index
> S(OBJECT:([PROD_GART_S40M0T0ODB].[dbo].[instrument].[pk_instrument_instrument_id]),
> SEEK:([instrument].[instrument_id] >= '4000998' AND
> [instrument].[instrument_id] <= '4000998' OR [instrument].[instrument_id] >=
> '4002121' AND
> | |--Index
> S(OBJECT:([PROD_GART_S40M0T0ODB].[dbo].[instrument].[idx_instrument_exchange_customer]),
> SEEK:([instrument].[exchange_customer] >= 'EA1' AND
> [instrument].[exchange_customer] <= 'EA1' OR [instrument].[exchange_customer]
> |--Index
> S(OBJECT:([PROD_GART_S40M0T0ODB].[dbo].[instrument].[idx_instrument_subtype_customer]),
> SEEK:([instrument].[instrument_subtype_customer] >= 'CO' AND
> [instrument].[instrument_subtype_customer] <= 'CO' OR
> [instrument].[instrument_subt
>