"Select TOP " question

I ran two query: one with order by, one does not.
Note that a clustered index has been build on column
DCN, CO_cd. No index on Sta_Rsn_Cd.
1) select top 1 DCN, CO_Cd, Dept from tblTest
where Dept = '212' and Sta_Rsn_Cd <> 'TRN' and Sta_Rsn_Cd <> 'NDF'
and Sufx = 'C'
and check_out <> 'Y'
order by Sta_Rsn_Cd
2) select top 1 DCN, CO_Cd, Dept from tblTest
where Dept = '212' and Sta_Rsn_Cd <> 'TRN' and Sta_Rsn_Cd <> 'NDF'
and Sufx = 'C'
and check_out <> 'Y'
In BOL
"If a SELECT statement that includes TOP also has an ORDER BY clause,
the rows to be returned are selected from the ordered result set. The
entire result set is built in the specified order and the top n rows in
the ordered result set are returned."
It seems that 2) would run faster than 1). When I run,
1) is faster than 2). I use SQL 2000. Any comments?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!It's hard to say from this. Can you run the SHOWPLAN for each of these
commands and include that here as well.
Rick Sawtell
"YB" <yb@.dex.com> wrote in message
news:eidd8xPoEHA.2948@.TK2MSFTNGP11.phx.gbl...
> I ran two query: one with order by, one does not.
> Note that a clustered index has been build on column
> DCN, CO_cd. No index on Sta_Rsn_Cd.
> 1) select top 1 DCN, CO_Cd, Dept from tblTest
> where Dept = '212' and Sta_Rsn_Cd <> 'TRN' and Sta_Rsn_Cd <> 'NDF'
> and Sufx = 'C'
> and check_out <> 'Y'
> order by Sta_Rsn_Cd
> 2) select top 1 DCN, CO_Cd, Dept from tblTest
> where Dept = '212' and Sta_Rsn_Cd <> 'TRN' and Sta_Rsn_Cd <> 'NDF'
> and Sufx = 'C'
> and check_out <> 'Y'
> In BOL
> "If a SELECT statement that includes TOP also has an ORDER BY clause,
> the rows to be returned are selected from the ordered result set. The
> entire result set is built in the specified order and the top n rows in
> the ordered result set are returned."
> It seems that 2) would run faster than 1). When I run,
> 1) is faster than 2). I use SQL 2000. Any comments?
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

"Select TOP " question

I ran two query: one with order by, one does not.
Note that a clustered index has been build on column
DCN, CO_cd. No index on Sta_Rsn_Cd.
1) select top 1 DCN, CO_Cd, Dept from tblTest
where Dept = '212' and Sta_Rsn_Cd <> 'TRN' and Sta_Rsn_Cd <> 'NDF'
and Sufx = 'C'
and check_out <> 'Y'
order by Sta_Rsn_Cd
2) select top 1 DCN, CO_Cd, Dept from tblTest
where Dept = '212' and Sta_Rsn_Cd <> 'TRN' and Sta_Rsn_Cd <> 'NDF'
and Sufx = 'C'
and check_out <> 'Y'
In BOL
"If a SELECT statement that includes TOP also has an ORDER BY clause,
the rows to be returned are selected from the ordered result set. The
entire result set is built in the specified order and the top n rows in
the ordered result set are returned."
It seems that 2) would run faster than 1). When I run,
1) is faster than 2). I use SQL 2000. Any comments?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
It's hard to say from this. Can you run the SHOWPLAN for each of these
commands and include that here as well.
Rick Sawtell
"YB" <yb@.dex.com> wrote in message
news:eidd8xPoEHA.2948@.TK2MSFTNGP11.phx.gbl...
> I ran two query: one with order by, one does not.
> Note that a clustered index has been build on column
> DCN, CO_cd. No index on Sta_Rsn_Cd.
> 1) select top 1 DCN, CO_Cd, Dept from tblTest
> where Dept = '212' and Sta_Rsn_Cd <> 'TRN' and Sta_Rsn_Cd <> 'NDF'
> and Sufx = 'C'
> and check_out <> 'Y'
> order by Sta_Rsn_Cd
> 2) select top 1 DCN, CO_Cd, Dept from tblTest
> where Dept = '212' and Sta_Rsn_Cd <> 'TRN' and Sta_Rsn_Cd <> 'NDF'
> and Sufx = 'C'
> and check_out <> 'Y'
> In BOL
> "If a SELECT statement that includes TOP also has an ORDER BY clause,
> the rows to be returned are selected from the ordered result set. The
> entire result set is built in the specified order and the top n rows in
> the ordered result set are returned."
> It seems that 2) would run faster than 1). When I run,
> 1) is faster than 2). I use SQL 2000. Any comments?
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

"Index was out of range" error on a very simple report with one ma

Hello,
I have a very simple report with one matrix setup on a landscape page
orientation. Margins are properly setup. I cannot get this to preview in
Report Designer. What is the solution for this?
Thanks,
SamA couple of questions:
* Do you have Reporting Services SP1 installed?
* Can you post to the newsgroup or send me an RDL that reproduces the
problem?
Ideally the report should work against a sample database (Northwind,
Pubs, or AdventureWorks).
* If you have SP1 installed could you delete the rdl.Data file for this
report and then preview the report?
The rdl.Data files are created in the report project directory.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:A4F98E05-A7FB-4BAC-81C4-E76685047B10@.microsoft.com...
> Hello,
> I have a very simple report with one matrix setup on a landscape page
> orientation. Margins are properly setup. I cannot get this to preview in
> Report Designer. What is the solution for this?
> Thanks,
> Sam|||* Yes.
* I have recreated the report. It works fine now.
Thanks,
Sam
"Bruce Johnson [MSFT]" wrote:
> A couple of questions:
> * Do you have Reporting Services SP1 installed?
> * Can you post to the newsgroup or send me an RDL that reproduces the
> problem?
> Ideally the report should work against a sample database (Northwind,
> Pubs, or AdventureWorks).
> * If you have SP1 installed could you delete the rdl.Data file for this
> report and then preview the report?
> The rdl.Data files are created in the report project directory.
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Sam" <Sam@.discussions.microsoft.com> wrote in message
> news:A4F98E05-A7FB-4BAC-81C4-E76685047B10@.microsoft.com...
> > Hello,
> >
> > I have a very simple report with one matrix setup on a landscape page
> > orientation. Margins are properly setup. I cannot get this to preview in
> > Report Designer. What is the solution for this?
> >
> > Thanks,
> > Sam
>
>

"Full-Text Index Table" is disabled?

I'm trying to do some local development so I installed Full-Text Indexing as
part of the SQL 2000 Developer Edition on my Windows XP machine.
The MS Search Service is running but the option is still disabled in EM when
I right-click on a table.
Thanks, Dave.
http://www.indexserverfaq.com/tablenotenabled.htm
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:BE6EABAB-0F45-40B2-9C9C-1A29A1A8746B@.microsoft.com...
> I'm trying to do some local development so I installed Full-Text Indexing
> as
> part of the SQL 2000 Developer Edition on my Windows XP machine.
> The MS Search Service is running but the option is still disabled in EM
> when
> I right-click on a table.
> Thanks, Dave.

"error 5180"

Hello we are running SQL 2000 sp 3 with the hotfixes. One of our server
showing this error and when I run dbcc checkdb I get errors regarding index.
I don't know what to do how to resolve. pls help
What is the error?
"Kiran" wrote:

> Hello we are running SQL 2000 sp 3 with the hotfixes. One of our server
> showing this error and when I run dbcc checkdb I get errors regarding index.
> I don't know what to do how to resolve. pls help

"error 5180"

Hello we are running SQL 2000 sp 3 with the hotfixes. One of our server
showing this error and when I run dbcc checkdb I get errors regarding index.
I don't know what to do how to resolve. pls helpWhat is the error?
"Kiran" wrote:
> Hello we are running SQL 2000 sp 3 with the hotfixes. One of our server
> showing this error and when I run dbcc checkdb I get errors regarding index.
> I don't know what to do how to resolve. pls help

"error 5180"

Hello we are running SQL 2000 sp 3 with the hotfixes. One of our server
showing this error and when I run dbcc checkdb I get errors regarding index.
I don't know what to do how to resolve. pls helpWhat is the error?
"Kiran" wrote:

> Hello we are running SQL 2000 sp 3 with the hotfixes. One of our server
> showing this error and when I run dbcc checkdb I get errors regarding inde
x.
> I don't know what to do how to resolve. pls help

"DROP INDEX failed because the following SET options have incorrect settings: 'ARITHABORT

Hi,
I've got a big import routine that brings a lot of data in raw tables into
another database - the routine is about 2000 lines long. I have a DTS
package that first brings the data in from CSV files and other sources, then
it runs this sproc to bring the data into the other database, then finally
it truncates the logs, and shrinks the databases. In the sproc, I EXEC a
sproc in the target database which drops all indexes on the target database,
start a transaction, bring all of the data in, then EXEC another sproc on
the target database to recreate the indexes again. Like I said, it's a
monster! :)
This sproc worked flawlessly for about six months, but now when I run the
DTS package it fails on the sproc step with the error mentioned in the
Subject line. The table on which this is failing has a clustered unique
index and 8 other standard indexes, but there are no indexes on calculated
columns or indexed views or anything like that. I am able to run the sproc
on the data from Query Analyzer with no errors, and I have tried DROPping
INDEXes with ARITHABORT on and ARITHABORT off, with no complaints.
Can anybody help out with this?
Thanks, JimDo you have any hypothetical indexes created by the ITW ?
select * from sysindexes where name like 'hind_%'
If you have no "real" indexes on computed columns or views its woeth
dropping any hypothetical ones and seeing it that helps
It may be worth explicitly setting the correct set options in your sproc
anyway to be on the safe side
set ANSI_PADDING,ANSI_WARNINGS,
CONCAT_NULL_YIELDS_NULL,ARITHABORT,
QUOTED_IDENTIFIER,ANSI_NULLS on
set NUMERIC_ROUNDABORT off
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jim" <jim@.x.com> wrote in message
news:erLkiH%23JEHA.3276@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I've got a big import routine that brings a lot of data in raw tables into
> another database - the routine is about 2000 lines long. I have a DTS
> package that first brings the data in from CSV files and other sources,
then
> it runs this sproc to bring the data into the other database, then finally
> it truncates the logs, and shrinks the databases. In the sproc, I EXEC a
> sproc in the target database which drops all indexes on the target
database,
> start a transaction, bring all of the data in, then EXEC another sproc on
> the target database to recreate the indexes again. Like I said, it's a
> monster! :)
> This sproc worked flawlessly for about six months, but now when I run the
> DTS package it fails on the sproc step with the error mentioned in the
> Subject line. The table on which this is failing has a clustered unique
> index and 8 other standard indexes, but there are no indexes on calculated
> columns or indexed views or anything like that. I am able to run the
sproc
> on the data from Query Analyzer with no errors, and I have tried DROPping
> INDEXes with ARITHABORT on and ARITHABORT off, with no complaints.
> Can anybody help out with this?
> Thanks, Jim
>

"Change Tracking" with "Update Index in Background" FREEZE ENTERPRISE

Hi,
When trying to set up "Change Tracking" with "Update Index in Background"
the Enterprise Manager console freezes.
Do I need to stop some Windows services to do that ?
Configuration :
SQL Server 2000 + SP3
Windows 2000 + SP3
Any help will be appreciated.
Thanks.
This should not happen at all. What happens if you configure change tracking
with update index in background using TSQL.
Here is an example of how you would do this for the authors table:
exec sp_fulltext_table 'authors', 'start_change_tracking'
GO
exec sp_fulltext_table 'authors', 'start_background_updateindex'
GO
It is possible that you are experiencing some locking during the intial
population if you have a high number of inserts/updates/and deletes running.
Also are you running profiler? Profiler will degrade performance even while
you have paused the trace.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Jean-Marc PUGIN" <jmpugin@.mobileworkers.com> wrote in message
news:euMN3dD3EHA.2804@.TK2MSFTNGP15.phx.gbl...
> Hi,
> When trying to set up "Change Tracking" with "Update Index in Background"
> the Enterprise Manager console freezes.
> Do I need to stop some Windows services to do that ?
> Configuration :
> SQL Server 2000 + SP3
> Windows 2000 + SP3
> Any help will be appreciated.
> Thanks.
>
|||Jean-Marc,
Most likely setting "Change Tracking" with "Update Index in Background" has
launched either an Incremental (if the table has a timestamp column) or a
Full Population in order to sync-up the MSSearch controlled FT Catalog with
the SQL Server 2000 table. Depending upon your server's resources this may
cause very high CPU-usage as well as with high memory and disk I/O to occur
during certain phases of the Incremental or Full Population and this will
have the affect of temporarily freezing the Enterprise Manager. Note, you
can use SQL Profiler or Perfmon or Task Mgr. to monitor the system usage.
Regards,
John
"Jean-Marc PUGIN" <jmpugin@.mobileworkers.com> wrote in message
news:euMN3dD3EHA.2804@.TK2MSFTNGP15.phx.gbl...
> Hi,
> When trying to set up "Change Tracking" with "Update Index in Background"
> the Enterprise Manager console freezes.
> Do I need to stop some Windows services to do that ?
> Configuration :
> SQL Server 2000 + SP3
> Windows 2000 + SP3
> Any help will be appreciated.
> Thanks.
>
|||John-
I have seen your name on quite a few posts and I believe you are helping
one of my colleagues on a FTS problem.
I thought I saw somewhere that you might have a list of Microsoft Full
Text Search replacements?
I am having a heck of a time finding something to replace the MS FTS in
SQL Server 2000. So far all I can find are web crawlers.
Could you give me some suggestions?
Thanks,
Dave
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||I'm sorry - hit submit by mistake.
I was actually going to offer some assistance on this question.
I did a small FTS build the other day, and I had the same problem. I
was using Query Analyzer, and it took 10 minutes for the second command
to complete execution (see previous posting).
If you do this in EM, expect the same behavior. It will appear to
freeze up for quite a while until it processes the commands.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

"Case insensitive" index

How to create "case insensitive" index? I mean, how to create a table index that will effectivelly index table by mains that differences between small and capital letters are ignored in query like:
SELECT name,surname WHERE UPPER(surname)=UPPER(?)Dobar dan :)

Check this (http://asktom.oracle.com/pls/ask/f?p=4950:8:5884730836779251727::NO::F4950_P8_DISPL AYID,F4950_P8_CRITERIA:1560806455855,) link.