got slightly similar table.
#Temp Table:
name,name1,name2,exam,score
A,A1,A21,
A,A1,A21,math100,88
A,A1,A21,math101,56
A,A1,A21,
A,A1,A21,math102,67
A,A1,A21,
A,A1,A21,math104,45
A,A1,A21,Chm100,55
A,A1,A21,
A,A1,A21,chm101,86
A,A1,A21,chm102,44
A,A1,A21,
(skipping)
A,A1,A22,math100,86
A,A1,A22,
A,A1,A22,math101,99
(skipping)
A,A2,A21,
A,A2,A21,math100,68
A,A2,A21,
A,A2,A21,math101,92
(skipping)
How to select * in case of highest
score and lowest score grouped
by name,name1,name2 where exam
and score are not null
(empty fields)?.
drop Table #Temp
--
Sent by techquest from fastmail subpart from fm
This is a spam protected message. Please answer with reference header.
Posted via http://www.usenet-replayer.comYour question is not clear. Can you tell us that your desired result set is?
Shervin
"techquest@.fastmail.fm" <u753143310@.spawnkill.ip-mobilphone.net> wrote in
message news:l.1066876242.1226959228@.[63.127.215.130]...
> got slightly similar table.
> #Temp Table:
> name,name1,name2,exam,score
> A,A1,A21,
> A,A1,A21,math100,88
> A,A1,A21,math101,56
> A,A1,A21,
> A,A1,A21,math102,67
> A,A1,A21,
> A,A1,A21,math104,45
> A,A1,A21,Chm100,55
> A,A1,A21,
> A,A1,A21,chm101,86
> A,A1,A21,chm102,44
> A,A1,A21,
> (skipping)
> A,A1,A22,math100,86
> A,A1,A22,
> A,A1,A22,math101,99
> (skipping)
> A,A2,A21,
> A,A2,A21,math100,68
> A,A2,A21,
> A,A2,A21,math101,92
> (skipping)
> How to select * in case of highest
> score and lowest score grouped
> by name,name1,name2 where exam
> and score are not null
> (empty fields)?.
> drop Table #Temp
>
>
> --
> Sent by techquest from fastmail subpart from fm
> This is a spam protected message. Please answer with reference header.
> Posted via http://www.usenet-replayer.com
Showing posts with label similar. Show all posts
Showing posts with label similar. Show all posts
Wednesday, March 28, 2012
query help
got slightly similar table.
#Temp Table:
name,name1,name2,exam,score
A,A1,A21,
A,A1,A21,math100,88
A,A1,A21,math101,56
A,A1,A21,
A,A1,A21,math102,67
A,A1,A21,
A,A1,A21,math104,45
A,A1,A21,Chm100,55
A,A1,A21,
A,A1,A21,chm101,86
A,A1,A21,chm102,44
A,A1,A21,
(skipping)
A,A1,A22,math100,86
A,A1,A22,
A,A1,A22,math101,99
(skipping)
A,A2,A21,
A,A2,A21,math100,68
A,A2,A21,
A,A2,A21,math101,92
(skipping)
#Temp Table:
name,name1,name2,exam,score
A,A1,A21,
A,A1,A21,math100,88
A,A1,A21,math101,56
A,A1,A21,
A,A1,A21,math102,67
A,A1,A21,
A,A1,A21,math104,45
A,A1,A21,Chm100,55
A,A1,A21,
A,A1,A21,chm101,86
A,A1,A21,chm102,44
A,A1,A21,
(skipping)
A,A1,A22,math100,86
A,A1,A22,
A,A1,A22,math101,99
(skipping)
A,A2,A21,
A,A2,A21,math100,68
A,A2,A21,
A,A2,A21,math101,92
(skipping)
How to select * in case of highest
score and lowest score grouped
by name,name1,name2 where exam
and score are not null
(empty fields)?.
drop Table #Temp
--
Sent by techquest from fastmail subpart from fm
This is a spam protected message. Please answer with reference header.
Posted via http://www.usenet-replayer.comYour question is not clear. Can you tell us that your desired result set is?
Shervin
"techquest@.fastmail.fm" <u753143310@.spawnkill.ip-mobilphone.net> wrote in
message news:l.1066876242.1226959228@.[63.127.215.130]...
> got slightly similar table.
> #Temp Table:
> name,name1,name2,exam,score
> A,A1,A21,
> A,A1,A21,math100,88
> A,A1,A21,math101,56
> A,A1,A21,
> A,A1,A21,math102,67
> A,A1,A21,
> A,A1,A21,math104,45
> A,A1,A21,Chm100,55
> A,A1,A21,
> A,A1,A21,chm101,86
> A,A1,A21,chm102,44
> A,A1,A21,
> (skipping)
> A,A1,A22,math100,86
> A,A1,A22,
> A,A1,A22,math101,99
> (skipping)
> A,A2,A21,
> A,A2,A21,math100,68
> A,A2,A21,
> A,A2,A21,math101,92
> (skipping)
> How to select * in case of highest
> score and lowest score grouped
> by name,name1,name2 where exam
> and score are not null
> (empty fields)?.
> drop Table #Temp
>
>
> --
> Sent by techquest from fastmail subpart from fm
> This is a spam protected message. Please answer with reference header.
> Posted via http://www.usenet-replayer.com
Monday, March 26, 2012
query gives out different results
Hi,
I did post a similar question yesterday - but it seems to have disappeared
from view, so appologies if you have seen this before.
I originally had a program which I sas told to put into a stored procedure.
I have done this BUT, to check that it's working I'm running them against
each other BUT and getting different results, in the final output. I do know
that the query is producing the correct result. I think the only difference
is I have a GO statement in the original program, between where the pquery i
s
run and the query updates the main table.
There are a number of steps to this process.
1. I copy the DISTINCT ref to a new output table (This works OK, and creates
538332 records)
2. I add another column to the output table called TENURE (This also works O
K)
3. I exec the query which has two parts to it. The first part runs a query
which puts the DISTINCT ref into a temporary table (This works ok and create
s
64379 records - as does the original code)
This but looks like:
CREATE TABLE #URN_STEP05
(REF varchar(255))
INSERT URN_STEP05(ref)
SELECT DISTINCT ref
FROM U_NFADHOC
WHERE CONVERT(DATETIME,DATE) BETWEEN DATEADD(DAY,+1,DATEADD(MONTH,
-12,CONVERT(DATETIME,@.enddate))) AND CONVERT(DATETIME, @.enddate)
AND RESPTYPE='NF Cash Donation'
AND ref IN(SELECT ref
FROM U_NFADHOC
WHERE CONVERT(DATETIME,DATE) BETWEEN DATEADD(DAY,+1,DATEADD(MONTH,
-24,CONVERT(DATETIME,@.enddate))) AND DATEADD(MONTH,
-12,CONVERT(DATETIME,@.enddate))
AND RESPTYPE='NF Cash Donation')
ORDER BY ref
5. The next bit is copying 70519 records into the output file. I can't work
out why.
UPDATE U_tenure
SET TENURE = 'CORE'
FROM U_tenure
INNER JOIN URN_STEP05
ON U_tenure.REF = URN_STEP05.REF
WHERE U_tenure.REF = URN_STEP05.REF
Is there something I'm doing wrong here? Is it possible to UPDATE the
original table instead of using a temporary table first?
Any help would be appreciated
RobWhy you are creating a temporary table #URN_STEP05
and referencing a permanent table URN_STEP05
in your insert and update statements?
I guess that might be the problem
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:E9433D93-A679-441A-B6E2-F6F566D8BC54@.microsoft.com...
> Hi,
> I did post a similar question yesterday - but it seems to have disappeared
> from view, so appologies if you have seen this before.
> I originally had a program which I sas told to put into a stored
> procedure.
> I have done this BUT, to check that it's working I'm running them against
> each other BUT and getting different results, in the final output. I do
> know
> that the query is producing the correct result. I think the only
> difference
> is I have a GO statement in the original program, between where the pquery
> is
> run and the query updates the main table.
> There are a number of steps to this process.
> 1. I copy the DISTINCT ref to a new output table (This works OK, and
> creates
> 538332 records)
> 2. I add another column to the output table called TENURE (This also works
> OK)
> 3. I exec the query which has two parts to it. The first part runs a query
> which puts the DISTINCT ref into a temporary table (This works ok and
> creates
> 64379 records - as does the original code)
> This but looks like:
> CREATE TABLE #URN_STEP05
> (REF varchar(255))
> INSERT URN_STEP05(ref)
> SELECT DISTINCT ref
> FROM U_NFADHOC
> WHERE CONVERT(DATETIME,DATE) BETWEEN DATEADD(DAY,+1,DATEADD(MONTH,
> -12,CONVERT(DATETIME,@.enddate))) AND CONVERT(DATETIME, @.enddate)
> AND RESPTYPE='NF Cash Donation'
> AND ref IN(SELECT ref
> FROM U_NFADHOC
> WHERE CONVERT(DATETIME,DATE) BETWEEN DATEADD(DAY,+1,DATEADD(MONTH,
> -24,CONVERT(DATETIME,@.enddate))) AND DATEADD(MONTH,
> -12,CONVERT(DATETIME,@.enddate))
> AND RESPTYPE='NF Cash Donation')
> ORDER BY ref
> 5. The next bit is copying 70519 records into the output file. I can't
> work
> out why.
> UPDATE U_tenure
> SET TENURE = 'CORE'
> FROM U_tenure
> INNER JOIN URN_STEP05
> ON U_tenure.REF = URN_STEP05.REF
> WHERE U_tenure.REF = URN_STEP05.REF
> --
> Is there something I'm doing wrong here? Is it possible to UPDATE the
> original table instead of using a temporary table first?
> Any help would be appreciated
> Rob
>
>|||Hi Roji,
I am not sure how to put the output direct into the table - when I tried
that it copied CORE to all of the TENURE fields instead of just the 64379
records. Is there a way to put them in directly? If so that would be a lot
better. Can you possably show me how to do this please?
Regards
Rob
"Roji. P. Thomas" wrote:
> Why you are creating a temporary table #URN_STEP05
> and referencing a permanent table URN_STEP05
> in your insert and update statements?
> I guess that might be the problem
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "Robert" <Robert@.discussions.microsoft.com> wrote in message
> news:E9433D93-A679-441A-B6E2-F6F566D8BC54@.microsoft.com...
>
>|||Did you read both replies in yesterday's thread?
http://www.google.co.uk/groups?hl=e...40microsoft.com
> 5. The next bit is copying 70519 records into the output file. I
can't work
out why.
As previously stated, UPDATE doesn't "copy records". Your UPDATE will
set the value of Tenure on every row whose Ref is in URN_STEP05. If
that's not what you want then please post enough code actually to
reproduce the problem: DDL, sample data INSERTs and show your required
end result from that sample (not all 70,000 rows - just a few rows but
enough to illustrate the problem.)
David Portas
SQL Server MVP
--
I did post a similar question yesterday - but it seems to have disappeared
from view, so appologies if you have seen this before.
I originally had a program which I sas told to put into a stored procedure.
I have done this BUT, to check that it's working I'm running them against
each other BUT and getting different results, in the final output. I do know
that the query is producing the correct result. I think the only difference
is I have a GO statement in the original program, between where the pquery i
s
run and the query updates the main table.
There are a number of steps to this process.
1. I copy the DISTINCT ref to a new output table (This works OK, and creates
538332 records)
2. I add another column to the output table called TENURE (This also works O
K)
3. I exec the query which has two parts to it. The first part runs a query
which puts the DISTINCT ref into a temporary table (This works ok and create
s
64379 records - as does the original code)
This but looks like:
CREATE TABLE #URN_STEP05
(REF varchar(255))
INSERT URN_STEP05(ref)
SELECT DISTINCT ref
FROM U_NFADHOC
WHERE CONVERT(DATETIME,DATE) BETWEEN DATEADD(DAY,+1,DATEADD(MONTH,
-12,CONVERT(DATETIME,@.enddate))) AND CONVERT(DATETIME, @.enddate)
AND RESPTYPE='NF Cash Donation'
AND ref IN(SELECT ref
FROM U_NFADHOC
WHERE CONVERT(DATETIME,DATE) BETWEEN DATEADD(DAY,+1,DATEADD(MONTH,
-24,CONVERT(DATETIME,@.enddate))) AND DATEADD(MONTH,
-12,CONVERT(DATETIME,@.enddate))
AND RESPTYPE='NF Cash Donation')
ORDER BY ref
5. The next bit is copying 70519 records into the output file. I can't work
out why.
UPDATE U_tenure
SET TENURE = 'CORE'
FROM U_tenure
INNER JOIN URN_STEP05
ON U_tenure.REF = URN_STEP05.REF
WHERE U_tenure.REF = URN_STEP05.REF
Is there something I'm doing wrong here? Is it possible to UPDATE the
original table instead of using a temporary table first?
Any help would be appreciated
RobWhy you are creating a temporary table #URN_STEP05
and referencing a permanent table URN_STEP05
in your insert and update statements?
I guess that might be the problem
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:E9433D93-A679-441A-B6E2-F6F566D8BC54@.microsoft.com...
> Hi,
> I did post a similar question yesterday - but it seems to have disappeared
> from view, so appologies if you have seen this before.
> I originally had a program which I sas told to put into a stored
> procedure.
> I have done this BUT, to check that it's working I'm running them against
> each other BUT and getting different results, in the final output. I do
> know
> that the query is producing the correct result. I think the only
> difference
> is I have a GO statement in the original program, between where the pquery
> is
> run and the query updates the main table.
> There are a number of steps to this process.
> 1. I copy the DISTINCT ref to a new output table (This works OK, and
> creates
> 538332 records)
> 2. I add another column to the output table called TENURE (This also works
> OK)
> 3. I exec the query which has two parts to it. The first part runs a query
> which puts the DISTINCT ref into a temporary table (This works ok and
> creates
> 64379 records - as does the original code)
> This but looks like:
> CREATE TABLE #URN_STEP05
> (REF varchar(255))
> INSERT URN_STEP05(ref)
> SELECT DISTINCT ref
> FROM U_NFADHOC
> WHERE CONVERT(DATETIME,DATE) BETWEEN DATEADD(DAY,+1,DATEADD(MONTH,
> -12,CONVERT(DATETIME,@.enddate))) AND CONVERT(DATETIME, @.enddate)
> AND RESPTYPE='NF Cash Donation'
> AND ref IN(SELECT ref
> FROM U_NFADHOC
> WHERE CONVERT(DATETIME,DATE) BETWEEN DATEADD(DAY,+1,DATEADD(MONTH,
> -24,CONVERT(DATETIME,@.enddate))) AND DATEADD(MONTH,
> -12,CONVERT(DATETIME,@.enddate))
> AND RESPTYPE='NF Cash Donation')
> ORDER BY ref
> 5. The next bit is copying 70519 records into the output file. I can't
> work
> out why.
> UPDATE U_tenure
> SET TENURE = 'CORE'
> FROM U_tenure
> INNER JOIN URN_STEP05
> ON U_tenure.REF = URN_STEP05.REF
> WHERE U_tenure.REF = URN_STEP05.REF
> --
> Is there something I'm doing wrong here? Is it possible to UPDATE the
> original table instead of using a temporary table first?
> Any help would be appreciated
> Rob
>
>|||Hi Roji,
I am not sure how to put the output direct into the table - when I tried
that it copied CORE to all of the TENURE fields instead of just the 64379
records. Is there a way to put them in directly? If so that would be a lot
better. Can you possably show me how to do this please?
Regards
Rob
"Roji. P. Thomas" wrote:
> Why you are creating a temporary table #URN_STEP05
> and referencing a permanent table URN_STEP05
> in your insert and update statements?
> I guess that might be the problem
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "Robert" <Robert@.discussions.microsoft.com> wrote in message
> news:E9433D93-A679-441A-B6E2-F6F566D8BC54@.microsoft.com...
>
>|||Did you read both replies in yesterday's thread?
http://www.google.co.uk/groups?hl=e...40microsoft.com
> 5. The next bit is copying 70519 records into the output file. I
can't work
out why.
As previously stated, UPDATE doesn't "copy records". Your UPDATE will
set the value of Tenure on every row whose Ref is in URN_STEP05. If
that's not what you want then please post enough code actually to
reproduce the problem: DDL, sample data INSERTs and show your required
end result from that sample (not all 70,000 rows - just a few rows but
enough to illustrate the problem.)
David Portas
SQL Server MVP
--
Friday, March 23, 2012
Query for most recent of duplicate records
I need some ideas on this query.
I have a table with entries similar to the following with columns name,
id, and timestamp.
kmyoung 345 2005-08-22 07:29:00.000
kmyoung 345 2005-08-29 07:29:15.000
mphillips 360 2005-08-27 14:48:18.000
rbeheler 360 2005-08-22 09:29:11.000
rbeheler 360 2005-08-24 09:28:19.000
rbeheler 360 2005-08-29 09:27:54.000
I need a resultant set that gives me the records with the most recent
timestamp for each ID as listed below.
kmyoung 345 2005-08-29 07:29:15.000
rbeheler 360 2005-08-29 09:27:54.000
Thanks for the help.Try,
select
*
from
t1 as a
where
c3 = (select max(b.c3) from t1 as b where b.[id] = a.[id])
go
AMB
"Jeff" wrote:
> I need some ideas on this query.
> I have a table with entries similar to the following with columns name,
> id, and timestamp.
> kmyoung 345 2005-08-22 07:29:00.000
> kmyoung 345 2005-08-29 07:29:15.000
> mphillips 360 2005-08-27 14:48:18.000
> rbeheler 360 2005-08-22 09:29:11.000
> rbeheler 360 2005-08-24 09:28:19.000
> rbeheler 360 2005-08-29 09:27:54.000
> I need a resultant set that gives me the records with the most recent
> timestamp for each ID as listed below.
> kmyoung 345 2005-08-29 07:29:15.000
> rbeheler 360 2005-08-29 09:27:54.000
> Thanks for the help.
>|||select name, id, max(timestamp) as timestamp
from thetable
group by name, id
having count(*)>1 -- if you need just the ones that have dupes, add this
line
Jeff wrote:
> I need some ideas on this query.
> I have a table with entries similar to the following with columns name,
> id, and timestamp.
> kmyoung 345 2005-08-22 07:29:00.000
> kmyoung 345 2005-08-29 07:29:15.000
> mphillips 360 2005-08-27 14:48:18.000
> rbeheler 360 2005-08-22 09:29:11.000
> rbeheler 360 2005-08-24 09:28:19.000
> rbeheler 360 2005-08-29 09:27:54.000
> I need a resultant set that gives me the records with the most recent
> timestamp for each ID as listed below.
> kmyoung 345 2005-08-29 07:29:15.000
> rbeheler 360 2005-08-29 09:27:54.000
> Thanks for the help.
>|||It worked great as long as t1 was an actual table. But in actuality t1 is a
union of two tables. When I substitute (select * from t1 union select * fro
m
t2) as t1, it no longer works. Would it be possible to rewrite this with a
subquery instead of t1?
"Alejandro Mesa" wrote:
> Try,
> select
> *
> from
> t1 as a
> where
> c3 = (select max(b.c3) from t1 as b where b.[id] = a.[id])
> go
>
> AMB
> "Jeff" wrote:
>|||Very close, but I ended up with this resultant set instead.
kmyoung 345 2005-08-29 07:29:15.000
mphillips 360 2005-08-27 14:48:18.000
rbeheler 360 2005-08-29 09:27:54.000
I ended up with two entries for id 360.
"Trey Walpole" wrote:
> select name, id, max(timestamp) as timestamp
> from thetable
> group by name, id
> having count(*)>1 -- if you need just the ones that have dupes, add this
> line
>
> Jeff wrote:
>|||Nevermind. It worked fine by simply substituting the subquery in place of
t1. Works exactly as I need it to .
Thanks!
"Jeff" wrote:
> It worked great as long as t1 was an actual table. But in actuality t1 is
a
> union of two tables. When I substitute (select * from t1 union select * f
rom
> t2) as t1, it no longer works. Would it be possible to rewrite this with
a
> subquery instead of t1?
> "Alejandro Mesa" wrote:
>|||oops - seeing a little cross-eyed today...
Jeff wrote:
> Very close, but I ended up with this resultant set instead.
> kmyoung 345 2005-08-29 07:29:15.000
> mphillips 360 2005-08-27 14:48:18.000
> rbeheler 360 2005-08-29 09:27:54.000
> I ended up with two entries for id 360.
> "Trey Walpole" wrote:
>
I have a table with entries similar to the following with columns name,
id, and timestamp.
kmyoung 345 2005-08-22 07:29:00.000
kmyoung 345 2005-08-29 07:29:15.000
mphillips 360 2005-08-27 14:48:18.000
rbeheler 360 2005-08-22 09:29:11.000
rbeheler 360 2005-08-24 09:28:19.000
rbeheler 360 2005-08-29 09:27:54.000
I need a resultant set that gives me the records with the most recent
timestamp for each ID as listed below.
kmyoung 345 2005-08-29 07:29:15.000
rbeheler 360 2005-08-29 09:27:54.000
Thanks for the help.Try,
select
*
from
t1 as a
where
c3 = (select max(b.c3) from t1 as b where b.[id] = a.[id])
go
AMB
"Jeff" wrote:
> I need some ideas on this query.
> I have a table with entries similar to the following with columns name,
> id, and timestamp.
> kmyoung 345 2005-08-22 07:29:00.000
> kmyoung 345 2005-08-29 07:29:15.000
> mphillips 360 2005-08-27 14:48:18.000
> rbeheler 360 2005-08-22 09:29:11.000
> rbeheler 360 2005-08-24 09:28:19.000
> rbeheler 360 2005-08-29 09:27:54.000
> I need a resultant set that gives me the records with the most recent
> timestamp for each ID as listed below.
> kmyoung 345 2005-08-29 07:29:15.000
> rbeheler 360 2005-08-29 09:27:54.000
> Thanks for the help.
>|||select name, id, max(timestamp) as timestamp
from thetable
group by name, id
having count(*)>1 -- if you need just the ones that have dupes, add this
line
Jeff wrote:
> I need some ideas on this query.
> I have a table with entries similar to the following with columns name,
> id, and timestamp.
> kmyoung 345 2005-08-22 07:29:00.000
> kmyoung 345 2005-08-29 07:29:15.000
> mphillips 360 2005-08-27 14:48:18.000
> rbeheler 360 2005-08-22 09:29:11.000
> rbeheler 360 2005-08-24 09:28:19.000
> rbeheler 360 2005-08-29 09:27:54.000
> I need a resultant set that gives me the records with the most recent
> timestamp for each ID as listed below.
> kmyoung 345 2005-08-29 07:29:15.000
> rbeheler 360 2005-08-29 09:27:54.000
> Thanks for the help.
>|||It worked great as long as t1 was an actual table. But in actuality t1 is a
union of two tables. When I substitute (select * from t1 union select * fro
m
t2) as t1, it no longer works. Would it be possible to rewrite this with a
subquery instead of t1?
"Alejandro Mesa" wrote:
> Try,
> select
> *
> from
> t1 as a
> where
> c3 = (select max(b.c3) from t1 as b where b.[id] = a.[id])
> go
>
> AMB
> "Jeff" wrote:
>|||Very close, but I ended up with this resultant set instead.
kmyoung 345 2005-08-29 07:29:15.000
mphillips 360 2005-08-27 14:48:18.000
rbeheler 360 2005-08-29 09:27:54.000
I ended up with two entries for id 360.
"Trey Walpole" wrote:
> select name, id, max(timestamp) as timestamp
> from thetable
> group by name, id
> having count(*)>1 -- if you need just the ones that have dupes, add this
> line
>
> Jeff wrote:
>|||Nevermind. It worked fine by simply substituting the subquery in place of
t1. Works exactly as I need it to .
Thanks!
"Jeff" wrote:
> It worked great as long as t1 was an actual table. But in actuality t1 is
a
> union of two tables. When I substitute (select * from t1 union select * f
rom
> t2) as t1, it no longer works. Would it be possible to rewrite this with
a
> subquery instead of t1?
> "Alejandro Mesa" wrote:
>|||oops - seeing a little cross-eyed today...
Jeff wrote:
> Very close, but I ended up with this resultant set instead.
> kmyoung 345 2005-08-29 07:29:15.000
> mphillips 360 2005-08-27 14:48:18.000
> rbeheler 360 2005-08-29 09:27:54.000
> I ended up with two entries for id 360.
> "Trey Walpole" wrote:
>
Wednesday, March 7, 2012
Query Cache?
Does SQL Server have a query cache similar to mysql, whereas the query
result is cached, if the table has not been changed?
result is cached, if the table has not been changed?
If so, please refer me to more info.
Thanks.
--
http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/General-Dis...pict170423.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=571428steve (UseLinkToEmail@.dbForumz.com) writes:
> Does SQL Server have a query cache similar to mysql, whereas the query
> result is cached, if the table has not been changed?
SQL Server maintains a cache, but certainly not with canned results for a
queries. In a OLTP system, many tables are frequently updated, so such the
likelyhood the of the same query yielding the same result twice is not that
fantastic. And for that matter, the likelyhood of the same query reappering
with the exactly the same parameters is not that extreme either. It is also
worth to keep in mind that most queries refers to more than one table.
What SQL Server has in its cache are recently accessed data pages, as well
as query plan for recently submitted queries and invoked stored procedure.
This makes it possible to retrieve the result of a query without accessing
the disk for frequently accessed tables. Note here that this cache works
better, the bigger it is. Thus, spending cache on less usful information,
such as canned query results, may have adverse results on performance.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Subscribe to:
Posts (Atom)