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!
Showing posts with label note. Show all posts
Showing posts with label note. Show all posts
Sunday, March 11, 2012
Thursday, March 8, 2012
"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!
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!
Sunday, February 19, 2012
"Internal SQL Server error."
"Server: Msg 8624, Level 16, State 7, Procedure p_get_LocalAuthor2, Line 58
Internal SQL Server error."
...and on that note, I'm going home.
What does SELECT @.@.VERSION yield? What does p_get_LocalAuthor2 look like?
What is on line 58?
"Mike C#" <xyz@.xyz.com> wrote in message
news:uOisHvzEHHA.3608@.TK2MSFTNGP02.phx.gbl...
> "Server: Msg 8624, Level 16, State 7, Procedure p_get_LocalAuthor2, Line
> 58
> Internal SQL Server error."
> ...and on that note, I'm going home.
>
|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e66wS1zEHHA.3784@.TK2MSFTNGP02.phx.gbl...
> What does SELECT @.@.VERSION yield? What does p_get_LocalAuthor2 look like?
> What is on line 58?
Hi Aaron,
Don't sweat it. This is a stored procedure that ran fine about 20 times in
a row on SQL 2000 SP 4. The 21st time seems to have been the charm. I
already checked and there's no KB articles that address this particular
situation (though there are a few that talk about "Internal Server Error"
under different circumstances that don't apply to my situation.)
BTW, Line 58 looks like this: DECLARE @.s VARCHAR(50)
Cool, huh? Thanks.
|||> already checked and there's no KB articles that address this particular
> situation (though there are a few that talk about "Internal Server Error"
> under different circumstances that don't apply to my situation.)
Most "Internal Server Error" issues are internal bugs that are fixed with
SPs or hotfixes. It may very well be a symptom that is fixed by a hotfix
that addressed a different system (not all bugs ever make it to the
knowledge base, so don't consider it to be gospel).
But, if you want to stay at SP4 and ignore the more recent hotfixes, you may
still continue to have this problem. If you can reproduce it on a second
box, especially after applying the latest hotfix, you may want to post a bug
on connect.
A
|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23SYpze8EHHA.4016@.TK2MSFTNGP02.phx.gbl...
> Most "Internal Server Error" issues are internal bugs that are fixed with
> SPs or hotfixes. It may very well be a symptom that is fixed by a hotfix
> that addressed a different system (not all bugs ever make it to the
> knowledge base, so don't consider it to be gospel).
> But, if you want to stay at SP4 and ignore the more recent hotfixes, you
> may still continue to have this problem. If you can reproduce it on a
> second box, especially after applying the latest hotfix, you may want to
> post a bug on connect.
Thanks, I was just venting
For now I've rebooted the box and it
suddenly works again. Go figure. I'll probably post a bug if it happens
again.
Thanks again
|||> Thanks, I was just venting
You should consider a blog.
Here, just about everything is presumed to be a request for help.
A
Internal SQL Server error."
...and on that note, I'm going home.
What does SELECT @.@.VERSION yield? What does p_get_LocalAuthor2 look like?
What is on line 58?
"Mike C#" <xyz@.xyz.com> wrote in message
news:uOisHvzEHHA.3608@.TK2MSFTNGP02.phx.gbl...
> "Server: Msg 8624, Level 16, State 7, Procedure p_get_LocalAuthor2, Line
> 58
> Internal SQL Server error."
> ...and on that note, I'm going home.
>
|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e66wS1zEHHA.3784@.TK2MSFTNGP02.phx.gbl...
> What does SELECT @.@.VERSION yield? What does p_get_LocalAuthor2 look like?
> What is on line 58?
Hi Aaron,
Don't sweat it. This is a stored procedure that ran fine about 20 times in
a row on SQL 2000 SP 4. The 21st time seems to have been the charm. I
already checked and there's no KB articles that address this particular
situation (though there are a few that talk about "Internal Server Error"
under different circumstances that don't apply to my situation.)
BTW, Line 58 looks like this: DECLARE @.s VARCHAR(50)
Cool, huh? Thanks.
|||> already checked and there's no KB articles that address this particular
> situation (though there are a few that talk about "Internal Server Error"
> under different circumstances that don't apply to my situation.)
Most "Internal Server Error" issues are internal bugs that are fixed with
SPs or hotfixes. It may very well be a symptom that is fixed by a hotfix
that addressed a different system (not all bugs ever make it to the
knowledge base, so don't consider it to be gospel).
But, if you want to stay at SP4 and ignore the more recent hotfixes, you may
still continue to have this problem. If you can reproduce it on a second
box, especially after applying the latest hotfix, you may want to post a bug
on connect.
A
|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23SYpze8EHHA.4016@.TK2MSFTNGP02.phx.gbl...
> Most "Internal Server Error" issues are internal bugs that are fixed with
> SPs or hotfixes. It may very well be a symptom that is fixed by a hotfix
> that addressed a different system (not all bugs ever make it to the
> knowledge base, so don't consider it to be gospel).
> But, if you want to stay at SP4 and ignore the more recent hotfixes, you
> may still continue to have this problem. If you can reproduce it on a
> second box, especially after applying the latest hotfix, you may want to
> post a bug on connect.
Thanks, I was just venting
For now I've rebooted the box and itsuddenly works again. Go figure. I'll probably post a bug if it happens
again.
Thanks again
|||> Thanks, I was just venting
You should consider a blog.
Here, just about everything is presumed to be a request for help.
A
"Internal SQL Server error."
"Server: Msg 8624, Level 16, State 7, Procedure p_get_LocalAuthor2, Line 58
Internal SQL Server error."
...and on that note, I'm going home.What does SELECT @.@.VERSION yield? What does p_get_LocalAuthor2 look like?
What is on line 58?
"Mike C#" <xyz@.xyz.com> wrote in message
news:uOisHvzEHHA.3608@.TK2MSFTNGP02.phx.gbl...
> "Server: Msg 8624, Level 16, State 7, Procedure p_get_LocalAuthor2, Line
> 58
> Internal SQL Server error."
> ...and on that note, I'm going home.
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:e66wS1zEHHA.3784@.TK2MSFTNGP02.phx.gbl...
> What does SELECT @.@.VERSION yield? What does p_get_LocalAuthor2 look like?
> What is on line 58?
Hi Aaron,
Don't sweat it. This is a stored procedure that ran fine about 20 times in
a row on SQL 2000 SP 4. The 21st time seems to have been the charm. I
already checked and there's no KB articles that address this particular
situation (though there are a few that talk about "Internal Server Error"
under different circumstances that don't apply to my situation.)
BTW, Line 58 looks like this: DECLARE @.s VARCHAR(50)
Cool, huh? Thanks.|||> already checked and there's no KB articles that address this particular
> situation (though there are a few that talk about "Internal Server Error"
> under different circumstances that don't apply to my situation.)
Most "Internal Server Error" issues are internal bugs that are fixed with
SPs or hotfixes. It may very well be a symptom that is fixed by a hotfix
that addressed a different system (not all bugs ever make it to the
knowledge base, so don't consider it to be gospel).
But, if you want to stay at SP4 and ignore the more recent hotfixes, you may
still continue to have this problem. If you can reproduce it on a second
box, especially after applying the latest hotfix, you may want to post a bug
on connect.
A|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:%23SYpze8EHHA.4016@.TK2MSFTNGP02.phx.gbl...
> Most "Internal Server Error" issues are internal bugs that are fixed with
> SPs or hotfixes. It may very well be a symptom that is fixed by a hotfix
> that addressed a different system (not all bugs ever make it to the
> knowledge base, so don't consider it to be gospel).
> But, if you want to stay at SP4 and ignore the more recent hotfixes, you
> may still continue to have this problem. If you can reproduce it on a
> second box, especially after applying the latest hotfix, you may want to
> post a bug on connect.
Thanks, I was just venting For now I've rebooted the box and it
suddenly works again. Go figure. I'll probably post a bug if it happens
again.
Thanks again|||> Thanks, I was just venting
You should consider a blog.
Here, just about everything is presumed to be a request for help.
A
Internal SQL Server error."
...and on that note, I'm going home.What does SELECT @.@.VERSION yield? What does p_get_LocalAuthor2 look like?
What is on line 58?
"Mike C#" <xyz@.xyz.com> wrote in message
news:uOisHvzEHHA.3608@.TK2MSFTNGP02.phx.gbl...
> "Server: Msg 8624, Level 16, State 7, Procedure p_get_LocalAuthor2, Line
> 58
> Internal SQL Server error."
> ...and on that note, I'm going home.
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:e66wS1zEHHA.3784@.TK2MSFTNGP02.phx.gbl...
> What does SELECT @.@.VERSION yield? What does p_get_LocalAuthor2 look like?
> What is on line 58?
Hi Aaron,
Don't sweat it. This is a stored procedure that ran fine about 20 times in
a row on SQL 2000 SP 4. The 21st time seems to have been the charm. I
already checked and there's no KB articles that address this particular
situation (though there are a few that talk about "Internal Server Error"
under different circumstances that don't apply to my situation.)
BTW, Line 58 looks like this: DECLARE @.s VARCHAR(50)
Cool, huh? Thanks.|||> already checked and there's no KB articles that address this particular
> situation (though there are a few that talk about "Internal Server Error"
> under different circumstances that don't apply to my situation.)
Most "Internal Server Error" issues are internal bugs that are fixed with
SPs or hotfixes. It may very well be a symptom that is fixed by a hotfix
that addressed a different system (not all bugs ever make it to the
knowledge base, so don't consider it to be gospel).
But, if you want to stay at SP4 and ignore the more recent hotfixes, you may
still continue to have this problem. If you can reproduce it on a second
box, especially after applying the latest hotfix, you may want to post a bug
on connect.
A|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:%23SYpze8EHHA.4016@.TK2MSFTNGP02.phx.gbl...
> Most "Internal Server Error" issues are internal bugs that are fixed with
> SPs or hotfixes. It may very well be a symptom that is fixed by a hotfix
> that addressed a different system (not all bugs ever make it to the
> knowledge base, so don't consider it to be gospel).
> But, if you want to stay at SP4 and ignore the more recent hotfixes, you
> may still continue to have this problem. If you can reproduce it on a
> second box, especially after applying the latest hotfix, you may want to
> post a bug on connect.
Thanks, I was just venting
For now I've rebooted the box and itsuddenly works again. Go figure. I'll probably post a bug if it happens
again.
Thanks again|||> Thanks, I was just venting
You should consider a blog.
Here, just about everything is presumed to be a request for help.
A
"Internal SQL Server error."
"Server: Msg 8624, Level 16, State 7, Procedure p_get_LocalAuthor2, Line 58
Internal SQL Server error."
...and on that note, I'm going home.What does SELECT @.@.VERSION yield? What does p_get_LocalAuthor2 look like?
What is on line 58?
"Mike C#" <xyz@.xyz.com> wrote in message
news:uOisHvzEHHA.3608@.TK2MSFTNGP02.phx.gbl...
> "Server: Msg 8624, Level 16, State 7, Procedure p_get_LocalAuthor2, Line
> 58
> Internal SQL Server error."
> ...and on that note, I'm going home.
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e66wS1zEHHA.3784@.TK2MSFTNGP02.phx.gbl...
> What does SELECT @.@.VERSION yield? What does p_get_LocalAuthor2 look like?
> What is on line 58?
Hi Aaron,
Don't sweat it. This is a stored procedure that ran fine about 20 times in
a row on SQL 2000 SP 4. The 21st time seems to have been the charm. I
already checked and there's no KB articles that address this particular
situation (though there are a few that talk about "Internal Server Error"
under different circumstances that don't apply to my situation.)
BTW, Line 58 looks like this: DECLARE @.s VARCHAR(50)
Cool, huh? Thanks.|||> already checked and there's no KB articles that address this particular
> situation (though there are a few that talk about "Internal Server Error"
> under different circumstances that don't apply to my situation.)
Most "Internal Server Error" issues are internal bugs that are fixed with
SPs or hotfixes. It may very well be a symptom that is fixed by a hotfix
that addressed a different system (not all bugs ever make it to the
knowledge base, so don't consider it to be gospel).
But, if you want to stay at SP4 and ignore the more recent hotfixes, you may
still continue to have this problem. If you can reproduce it on a second
box, especially after applying the latest hotfix, you may want to post a bug
on connect.
A|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23SYpze8EHHA.4016@.TK2MSFTNGP02.phx.gbl...
>> already checked and there's no KB articles that address this particular
>> situation (though there are a few that talk about "Internal Server Error"
>> under different circumstances that don't apply to my situation.)
> Most "Internal Server Error" issues are internal bugs that are fixed with
> SPs or hotfixes. It may very well be a symptom that is fixed by a hotfix
> that addressed a different system (not all bugs ever make it to the
> knowledge base, so don't consider it to be gospel).
> But, if you want to stay at SP4 and ignore the more recent hotfixes, you
> may still continue to have this problem. If you can reproduce it on a
> second box, especially after applying the latest hotfix, you may want to
> post a bug on connect.
Thanks, I was just venting :) For now I've rebooted the box and it
suddenly works again. Go figure. I'll probably post a bug if it happens
again.
Thanks again|||> Thanks, I was just venting :)
You should consider a blog.
Here, just about everything is presumed to be a request for help.
A
Internal SQL Server error."
...and on that note, I'm going home.What does SELECT @.@.VERSION yield? What does p_get_LocalAuthor2 look like?
What is on line 58?
"Mike C#" <xyz@.xyz.com> wrote in message
news:uOisHvzEHHA.3608@.TK2MSFTNGP02.phx.gbl...
> "Server: Msg 8624, Level 16, State 7, Procedure p_get_LocalAuthor2, Line
> 58
> Internal SQL Server error."
> ...and on that note, I'm going home.
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e66wS1zEHHA.3784@.TK2MSFTNGP02.phx.gbl...
> What does SELECT @.@.VERSION yield? What does p_get_LocalAuthor2 look like?
> What is on line 58?
Hi Aaron,
Don't sweat it. This is a stored procedure that ran fine about 20 times in
a row on SQL 2000 SP 4. The 21st time seems to have been the charm. I
already checked and there's no KB articles that address this particular
situation (though there are a few that talk about "Internal Server Error"
under different circumstances that don't apply to my situation.)
BTW, Line 58 looks like this: DECLARE @.s VARCHAR(50)
Cool, huh? Thanks.|||> already checked and there's no KB articles that address this particular
> situation (though there are a few that talk about "Internal Server Error"
> under different circumstances that don't apply to my situation.)
Most "Internal Server Error" issues are internal bugs that are fixed with
SPs or hotfixes. It may very well be a symptom that is fixed by a hotfix
that addressed a different system (not all bugs ever make it to the
knowledge base, so don't consider it to be gospel).
But, if you want to stay at SP4 and ignore the more recent hotfixes, you may
still continue to have this problem. If you can reproduce it on a second
box, especially after applying the latest hotfix, you may want to post a bug
on connect.
A|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23SYpze8EHHA.4016@.TK2MSFTNGP02.phx.gbl...
>> already checked and there's no KB articles that address this particular
>> situation (though there are a few that talk about "Internal Server Error"
>> under different circumstances that don't apply to my situation.)
> Most "Internal Server Error" issues are internal bugs that are fixed with
> SPs or hotfixes. It may very well be a symptom that is fixed by a hotfix
> that addressed a different system (not all bugs ever make it to the
> knowledge base, so don't consider it to be gospel).
> But, if you want to stay at SP4 and ignore the more recent hotfixes, you
> may still continue to have this problem. If you can reproduce it on a
> second box, especially after applying the latest hotfix, you may want to
> post a bug on connect.
Thanks, I was just venting :) For now I've rebooted the box and it
suddenly works again. Go figure. I'll probably post a bug if it happens
again.
Thanks again|||> Thanks, I was just venting :)
You should consider a blog.
Here, just about everything is presumed to be a request for help.
A
Subscribe to:
Posts (Atom)
