I am trying to read the results from a query like
select a, b
from mytbl
where col0 = ?
The first time I run SQLExecute, everything works fine, but when I call
SQLExecute a second time with a modified input parameter, it returns
SQL_ERROR. Below is the code I'm running with the error checking removed and
the names simplified.
SQLAllocHandle(SQL_HANDLE_STMT, hconn, &m_hstmt)) ;
SQLPrepare(m_hstmt, (UCHAR *)m_query.GetBuffer(), m_query.GetLength()));
SQLBindParameter(m_hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0,
&where_param, 0, 0);
SQLBindCol(m_hstmt, 1, SQL_C_BIT, addr1, 0, 0)
SQLBindCol(m_hstmt, 2, SQL_C_BIT, addr2, 0, 0)
SQLExecute(m_hstmt) //works ok
SQLFetchScroll(m_hstmt, SQL_FETCH_NEXT, 0)
where_param = 10
SQLExecute(m_hstmt) //fails
SQLFetchScroll(m_hstmt, SQL_FETCH_NEXT, 0)
My goal is to run execute many times with a variety of parameters. I was
trying to have the odbc driver read from the address specified in
SQLBindParameter to set the value of the input parameter. I'm further
confused by the problem because when I try to retrieve error information by
calling SQLGetDiagRec, nothing is returned. Thanks for your help.
Scott
The problem goes away when I call SQLCloseCursor after completing the fetch.
The MSDN SQLBindParameter documentation at
http://msdn.microsoft.com/library/de...dparameter.asp
gives no indication that SQLCloseCursor should be needed.
I suppose that I am now just concerned that I am introducing extra
processing overhead by calling SQLCloseCursor.
Scott
"ScottD" wrote:
> I am trying to read the results from a query like
> select a, b
> from mytbl
> where col0 = ?
> The first time I run SQLExecute, everything works fine, but when I call
> SQLExecute a second time with a modified input parameter, it returns
> SQL_ERROR. Below is the code I'm running with the error checking removed and
> the names simplified.
> SQLAllocHandle(SQL_HANDLE_STMT, hconn, &m_hstmt)) ;
> SQLPrepare(m_hstmt, (UCHAR *)m_query.GetBuffer(), m_query.GetLength()));
> SQLBindParameter(m_hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0,
> &where_param, 0, 0);
> SQLBindCol(m_hstmt, 1, SQL_C_BIT, addr1, 0, 0)
> SQLBindCol(m_hstmt, 2, SQL_C_BIT, addr2, 0, 0)
> SQLExecute(m_hstmt) //works ok
> SQLFetchScroll(m_hstmt, SQL_FETCH_NEXT, 0)
> where_param = 10
> SQLExecute(m_hstmt) //fails
> SQLFetchScroll(m_hstmt, SQL_FETCH_NEXT, 0)
>
> My goal is to run execute many times with a variety of parameters. I was
> trying to have the odbc driver read from the address specified in
> SQLBindParameter to set the value of the input parameter. I'm further
> confused by the problem because when I try to retrieve error information by
> calling SQLGetDiagRec, nothing is returned. Thanks for your help.
> Scott
|||Are you making sure that you've fetch all of the rows? Even if only
one row is returned by the query, you should call SQLFetchXXX() until
it returns SQL_NO_DATA. Otherwise the driver doesn't necessarily know
that you've finished fetching rows on the current resultset/cursor.
Alternatively, you can use SQLMoreResults() to flush the remaining data
on the wire and position on the next resultset (if there is one,
otherwise it cleans up the connection).
SQLCloseCursor shouldn't add too much overhead. I'm assuming you're
using the default (firehose) cursor. If so, SQLCloseCursor just makes
sure that all of the data from the previous statement has been
consumed, and the connection is ready for the next statement.
Brannon
Showing posts with label query. Show all posts
Showing posts with label query. Show all posts
Tuesday, March 27, 2012
Binding dataset to a reportviewer control
Hi,
I need to bind my custom dataset, which i retrieve by executing some query to a reportviewer control. I don't want to create a typed dataset in the application. Is there any way to do it? The dataset that i bind will be built in a seperate class. If I plan to use a Object Data source, what are the steps to follow. Pls help.
thanks,
Saravanan
www.gotreportviewer.com provides information about how to use object data sources specifically at: http://www.gotreportviewer.com/objectdatasources/index.html
-- Robert
|||Hi Robert,
Thanks for the reply. I already read through that article. What i really need is to bind a dataset ( which i create in my application) to be bound to the report viewer. The dataset will have some columns from the database ( thro a select query), some columns may be added by me. I want to bind this custom dataset to the report viewer control (local mode processing). Please let me know if this can be done and also some steps to do it.
Thanks in advance,
Saravanan.
bind variables / parameter queries
Hi,
I'm writing an Access pass-through query against a SQL server backend and I need some advice on passing parameters. Currently I use vba to substitute the literal values for the parameters prior to passing the query to SQL Server. However, I am going through a loop thousands of times with different literals for these parameters which causes the server's cache to fill up. In Oracle, there is a way to use bind variables for the parameters so that only one copy of the query is cached.
Does anyone know how I can do this in SQL Server?
For instance, I have 20,000 employees and I'm pulling info by SS#:
Select * from EmpTable where SS_number = [SSN]
Is there a way I can pass this query to SQL Server and then pass the value of [SSN] as I loop through the dataset?
Thanks.write a stored procedure, and instead of calling the database engine 20,000 times, just call it once and pass it a list of 20,000 numbers
come to think of it, where would you get 20,000 numbers? sounds like you might want to look for a JOIN solution|||SQL Server actually goes you one better, in that its ODBC drivers will automagically parameterize a query for you (unless you get really creative in modifying the query).
As Rudy pointed out though, if you have more than 20 iterations from a given client, you really ought to be thinking about a JOIN based solution... Doing that kind of thing on that scale one row at a time is WAY too much work for me!
-PatP|||Thanks, guys. I'll get write access to the backend and write a stored proc.
I'll have to read up on how to pass values to the proc (I'm guessing it's like a function).
Thanks again.|||How about this?:
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.Open "DSN=PKRebate2001", "sa", ""
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = cnn
.CommandText = "sp_UpdateCustomerUnique"
.CommandType = adCmdStoredProc
.Parameters.Refresh
.Parameters("@.ImportMonth").Value = IM
.Execute
End With
Set cmd = Nothing
Set cnn = Nothing|||If you are looking to get automatic parameterization, that VBA code is conceptually good.
-PatP
I'm writing an Access pass-through query against a SQL server backend and I need some advice on passing parameters. Currently I use vba to substitute the literal values for the parameters prior to passing the query to SQL Server. However, I am going through a loop thousands of times with different literals for these parameters which causes the server's cache to fill up. In Oracle, there is a way to use bind variables for the parameters so that only one copy of the query is cached.
Does anyone know how I can do this in SQL Server?
For instance, I have 20,000 employees and I'm pulling info by SS#:
Select * from EmpTable where SS_number = [SSN]
Is there a way I can pass this query to SQL Server and then pass the value of [SSN] as I loop through the dataset?
Thanks.write a stored procedure, and instead of calling the database engine 20,000 times, just call it once and pass it a list of 20,000 numbers
come to think of it, where would you get 20,000 numbers? sounds like you might want to look for a JOIN solution|||SQL Server actually goes you one better, in that its ODBC drivers will automagically parameterize a query for you (unless you get really creative in modifying the query).
As Rudy pointed out though, if you have more than 20 iterations from a given client, you really ought to be thinking about a JOIN based solution... Doing that kind of thing on that scale one row at a time is WAY too much work for me!
-PatP|||Thanks, guys. I'll get write access to the backend and write a stored proc.
I'll have to read up on how to pass values to the proc (I'm guessing it's like a function).
Thanks again.|||How about this?:
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.Open "DSN=PKRebate2001", "sa", ""
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = cnn
.CommandText = "sp_UpdateCustomerUnique"
.CommandType = adCmdStoredProc
.Parameters.Refresh
.Parameters("@.ImportMonth").Value = IM
.Execute
End With
Set cmd = Nothing
Set cnn = Nothing|||If you are looking to get automatic parameterization, that VBA code is conceptually good.
-PatP
Sunday, March 25, 2012
binary_checksum and rowsize error?
Hi,
I can see that by using the object ID rather that the object name, the
following SQL query works. Has anybody got any idea what is causing the
error?
-- Works OK
select o.id
,checksum_agg(binary_checksum(=ADm.text))
from sysobjects o
,syscomments m
where o.id =3D m.id
and o.xtype in ('FN','IF','P','TF','TR','V')
group by o.id
-- Error
-- Server: Msg 1540, Level 16, State 1, Line 1
-- Cannot sort a row of size 8096, which is greater than the
-- allowable maximum of 8094.
select object_name(o.id)
,checksum_agg(binary_checksum(=ADm.text))
from sysobjects o
,syscomments m
where o.id =3D m.id
and o.xtype in ('FN','IF','P','TF','TR','V')
group by object_name(o.id)
-- Error
-- Server: Msg 1540, Level 16, State 1, Line 1
-- Cannot sort a row of size 8096, which is greater than the
-- allowable maximum of 8094.
select o.name
,checksum_agg(binary_checksum(=ADm.text))
from sysobjects o
,syscomments m
where o.id =3D m.id
and o.xtype in ('FN','IF','P','TF','TR','V')
group by o.name
-- Workaround
select getdate()
,object_name(x.id)
,check_sum
from (select m.id
,checksum_agg(binary_checksum(=ADm.text)) as check_sum
from syscomments m
inner join
sysobjects o
on m.id =3D o.id
where o.xtype in ('FN','IF','P','TF','TR','V')
group by m.id) as x=20
Regards=20
LiamIt works at my database. The cause is that there is a row in syscomments
that has a rather long value in the text column, and that pushes the rowsize
of an intermediate result set over the maximum row size. SQL Server uses
only an estimate from the statistics to determine how large the largest row
can be in any intermediate result sets, so it can create an execution plan
that might fail with very long rows, especially when the very long rows are
rare. You can avoid this by using OPTION(ROBUST PLAN).
Jacco Schalkwijk
SQL Server MVP
<liam.caffrey@.gmail.com> wrote in message
news:1117784150.912579.240300@.g14g2000cwa.googlegroups.com...
Hi,
I can see that by using the object ID rather that the object name, the
following SQL query works. Has anybody got any idea what is causing the
error?
-- Works OK
select o.id
,checksum_agg(binary_checksum(_m.text))
from sysobjects o
,syscomments m
where o.id = m.id
and o.xtype in ('FN','IF','P','TF','TR','V')
group by o.id
-- Error
-- Server: Msg 1540, Level 16, State 1, Line 1
-- Cannot sort a row of size 8096, which is greater than the
-- allowable maximum of 8094.
select object_name(o.id)
,checksum_agg(binary_checksum(_m.text))
from sysobjects o
,syscomments m
where o.id = m.id
and o.xtype in ('FN','IF','P','TF','TR','V')
group by object_name(o.id)
-- Error
-- Server: Msg 1540, Level 16, State 1, Line 1
-- Cannot sort a row of size 8096, which is greater than the
-- allowable maximum of 8094.
select o.name
,checksum_agg(binary_checksum(_m.text))
from sysobjects o
,syscomments m
where o.id = m.id
and o.xtype in ('FN','IF','P','TF','TR','V')
group by o.name
-- Workaround
select getdate()
,object_name(x.id)
,check_sum
from (select m.id
,checksum_agg(binary_checksum(_m.text)) as check_sum
from syscomments m
inner join
sysobjects o
on m.id = o.id
where o.xtype in ('FN','IF','P','TF','TR','V')
group by m.id) as x
Regards
Liam
I can see that by using the object ID rather that the object name, the
following SQL query works. Has anybody got any idea what is causing the
error?
-- Works OK
select o.id
,checksum_agg(binary_checksum(=ADm.text))
from sysobjects o
,syscomments m
where o.id =3D m.id
and o.xtype in ('FN','IF','P','TF','TR','V')
group by o.id
-- Error
-- Server: Msg 1540, Level 16, State 1, Line 1
-- Cannot sort a row of size 8096, which is greater than the
-- allowable maximum of 8094.
select object_name(o.id)
,checksum_agg(binary_checksum(=ADm.text))
from sysobjects o
,syscomments m
where o.id =3D m.id
and o.xtype in ('FN','IF','P','TF','TR','V')
group by object_name(o.id)
-- Error
-- Server: Msg 1540, Level 16, State 1, Line 1
-- Cannot sort a row of size 8096, which is greater than the
-- allowable maximum of 8094.
select o.name
,checksum_agg(binary_checksum(=ADm.text))
from sysobjects o
,syscomments m
where o.id =3D m.id
and o.xtype in ('FN','IF','P','TF','TR','V')
group by o.name
-- Workaround
select getdate()
,object_name(x.id)
,check_sum
from (select m.id
,checksum_agg(binary_checksum(=ADm.text)) as check_sum
from syscomments m
inner join
sysobjects o
on m.id =3D o.id
where o.xtype in ('FN','IF','P','TF','TR','V')
group by m.id) as x=20
Regards=20
LiamIt works at my database. The cause is that there is a row in syscomments
that has a rather long value in the text column, and that pushes the rowsize
of an intermediate result set over the maximum row size. SQL Server uses
only an estimate from the statistics to determine how large the largest row
can be in any intermediate result sets, so it can create an execution plan
that might fail with very long rows, especially when the very long rows are
rare. You can avoid this by using OPTION(ROBUST PLAN).
Jacco Schalkwijk
SQL Server MVP
<liam.caffrey@.gmail.com> wrote in message
news:1117784150.912579.240300@.g14g2000cwa.googlegroups.com...
Hi,
I can see that by using the object ID rather that the object name, the
following SQL query works. Has anybody got any idea what is causing the
error?
-- Works OK
select o.id
,checksum_agg(binary_checksum(_m.text))
from sysobjects o
,syscomments m
where o.id = m.id
and o.xtype in ('FN','IF','P','TF','TR','V')
group by o.id
-- Error
-- Server: Msg 1540, Level 16, State 1, Line 1
-- Cannot sort a row of size 8096, which is greater than the
-- allowable maximum of 8094.
select object_name(o.id)
,checksum_agg(binary_checksum(_m.text))
from sysobjects o
,syscomments m
where o.id = m.id
and o.xtype in ('FN','IF','P','TF','TR','V')
group by object_name(o.id)
-- Error
-- Server: Msg 1540, Level 16, State 1, Line 1
-- Cannot sort a row of size 8096, which is greater than the
-- allowable maximum of 8094.
select o.name
,checksum_agg(binary_checksum(_m.text))
from sysobjects o
,syscomments m
where o.id = m.id
and o.xtype in ('FN','IF','P','TF','TR','V')
group by o.name
-- Workaround
select getdate()
,object_name(x.id)
,check_sum
from (select m.id
,checksum_agg(binary_checksum(_m.text)) as check_sum
from syscomments m
inner join
sysobjects o
on m.id = o.id
where o.xtype in ('FN','IF','P','TF','TR','V')
group by m.id) as x
Regards
Liam
binary sort versus DOCI
Hello:
Whether you are using SQL 2000 or SQL 2005, what is the best script to run
in Query Analyzer to determine the sort order that SQL was originally
installed under (binary versus dictionary order case insensitivity)?
Thanks!
childofthe1980sOn 27 Oct, 17:41, childofthe1980s
<childofthe19...@.discussions.microsoft.com> wrote:
> Hello:
> Whether you are using SQL 2000 or SQL 2005, what is the best script to run
> in Query Analyzer to determine the sort order that SQL was originally
> installed under (binary versus dictionary order case insensitivity)?
> Thanks!
> childofthe1980s
SERVERPROPERTY('Collation')
returns the name of the server's default collation.
--
David Portas|||Thank you, David!
childofthe1980s
"David Portas" wrote:
> On 27 Oct, 17:41, childofthe1980s
> <childofthe19...@.discussions.microsoft.com> wrote:
> > Hello:
> >
> > Whether you are using SQL 2000 or SQL 2005, what is the best script to run
> > in Query Analyzer to determine the sort order that SQL was originally
> > installed under (binary versus dictionary order case insensitivity)?
> >
> > Thanks!
> >
> > childofthe1980s
>
> SERVERPROPERTY('Collation')
> returns the name of the server's default collation.
> --
> David Portas
>sql
Whether you are using SQL 2000 or SQL 2005, what is the best script to run
in Query Analyzer to determine the sort order that SQL was originally
installed under (binary versus dictionary order case insensitivity)?
Thanks!
childofthe1980sOn 27 Oct, 17:41, childofthe1980s
<childofthe19...@.discussions.microsoft.com> wrote:
> Hello:
> Whether you are using SQL 2000 or SQL 2005, what is the best script to run
> in Query Analyzer to determine the sort order that SQL was originally
> installed under (binary versus dictionary order case insensitivity)?
> Thanks!
> childofthe1980s
SERVERPROPERTY('Collation')
returns the name of the server's default collation.
--
David Portas|||Thank you, David!
childofthe1980s
"David Portas" wrote:
> On 27 Oct, 17:41, childofthe1980s
> <childofthe19...@.discussions.microsoft.com> wrote:
> > Hello:
> >
> > Whether you are using SQL 2000 or SQL 2005, what is the best script to run
> > in Query Analyzer to determine the sort order that SQL was originally
> > installed under (binary versus dictionary order case insensitivity)?
> >
> > Thanks!
> >
> > childofthe1980s
>
> SERVERPROPERTY('Collation')
> returns the name of the server's default collation.
> --
> David Portas
>sql
Tuesday, March 20, 2012
Big query on different servers
Hi.
From my dektop PC I started Query Analyser on 12 servers and used it to
execute a Stored Procedure (same database structure on all servers). On eight
it worked, on four it did not, giving the message:
ODBC: Msg 0, Level 19,. State 1
SqlDumpExceptionHandler: Process nnn generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Connection broken
Can someone tell me what is going on and how to get round this? The Stored
Procedure is like this:
CREATE TABLE... (no problem here)
INSERT INTO... several thousand rows generated by reading a million plus
rows
from a different database on the same server
(this sometimes works, sometimes it fails at
this point)
UPDATE... all the rows from the INSERT, again with data generated by
reading a
million plus rows from a different database on the same
server
(if it gets beyond this point it works
correctly)
UPDATE... as the first update
UPDATE... as the first update
similar UPDATE... INSERT... UPDATE... UPDATE... UPDATE... UPDATE
A valid run takes about 10-15 minutes. The last one I tried failed during
the first UPDATE after 26 seconds.
TIA,
Peter.
Hi
SQL version and service pack level (select @.@.version)?
Check that you are on the latest SP's and possible hotfixes.
Regards
Mike
"PeterHyssett" wrote:
> Hi.
> From my dektop PC I started Query Analyser on 12 servers and used it to
> execute a Stored Procedure (same database structure on all servers). On eight
> it worked, on four it did not, giving the message:
> ODBC: Msg 0, Level 19,. State 1
> SqlDumpExceptionHandler: Process nnn generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> Connection broken
> Can someone tell me what is going on and how to get round this? The Stored
> Procedure is like this:
> CREATE TABLE... (no problem here)
> INSERT INTO... several thousand rows generated by reading a million plus
> rows
> from a different database on the same server
> (this sometimes works, sometimes it fails at
> this point)
> UPDATE... all the rows from the INSERT, again with data generated by
> reading a
> million plus rows from a different database on the same
> server
> (if it gets beyond this point it works
> correctly)
> UPDATE... as the first update
> UPDATE... as the first update
> similar UPDATE... INSERT... UPDATE... UPDATE... UPDATE... UPDATE
> A valid run takes about 10-15 minutes. The last one I tried failed during
> the first UPDATE after 26 seconds.
> TIA,
> Peter.
|||Thanks - the servers which gave trouble had no service packs applied - the
ones that worked were mostly at SP3.
Regards,
Peter.
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> SQL version and service pack level (select @.@.version)?
> Check that you are on the latest SP's and possible hotfixes.
> Regards
> Mike
> "PeterHyssett" wrote:
|||On the problem server, step through or simplify the code to find where it
breaks.
Jeff
"PeterHyssett" <PeterHyssett@.discussions.microsoft.com> wrote in message
news:A0CF95FA-3109-4A96-95D4-5ACFD7B4305F@.microsoft.com...[vbcol=seagreen]
> Thanks - the servers which gave trouble had no service packs applied - the
> ones that worked were mostly at SP3.
> Regards,
> Peter.
> "Mike Epprecht (SQL MVP)" wrote:
to[vbcol=seagreen]
On eight[vbcol=seagreen]
c0000005[vbcol=seagreen]
Stored[vbcol=seagreen]
plus[vbcol=seagreen]
fails at[vbcol=seagreen]
by[vbcol=seagreen]
same[vbcol=seagreen]
works[vbcol=seagreen]
during[vbcol=seagreen]
|||
> Hi.
> From my dektop PC I started Query Analyser on 12 servers and used it to
> execute a Stored Procedure (same database structure on all servers). On eight
> it worked, on four it did not, giving the message:
> ODBC: Msg 0, Level 19,. State 1
> SqlDumpExceptionHandler: Process nnn generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> Connection broken
> Can someone tell me what is going on and how to get round this? The Stored
> Procedure is like this:
> CREATE TABLE... (no problem here)
> INSERT INTO... several thousand rows generated by reading a million plus
> rows
> from a different database on the same server
> (this sometimes works, sometimes it fails at
> this point)
> UPDATE... all the rows from the INSERT, again with data generated by
> reading a
> million plus rows from a different database on the same
> server
> (if it gets beyond this point it works
> correctly)
> UPDATE... as the first update
> UPDATE... as the first update
> similar UPDATE... INSERT... UPDATE... UPDATE... UPDATE... UPDATE
> A valid run takes about 10-15 minutes. The last one I tried failed during
> the first UPDATE after 26 seconds.
> TIA,
> Peter.
User submitted from AEWNET (http://www.aewnet.com/)
sql
From my dektop PC I started Query Analyser on 12 servers and used it to
execute a Stored Procedure (same database structure on all servers). On eight
it worked, on four it did not, giving the message:
ODBC: Msg 0, Level 19,. State 1
SqlDumpExceptionHandler: Process nnn generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Connection broken
Can someone tell me what is going on and how to get round this? The Stored
Procedure is like this:
CREATE TABLE... (no problem here)
INSERT INTO... several thousand rows generated by reading a million plus
rows
from a different database on the same server
(this sometimes works, sometimes it fails at
this point)
UPDATE... all the rows from the INSERT, again with data generated by
reading a
million plus rows from a different database on the same
server
(if it gets beyond this point it works
correctly)
UPDATE... as the first update
UPDATE... as the first update
similar UPDATE... INSERT... UPDATE... UPDATE... UPDATE... UPDATE
A valid run takes about 10-15 minutes. The last one I tried failed during
the first UPDATE after 26 seconds.
TIA,
Peter.
Hi
SQL version and service pack level (select @.@.version)?
Check that you are on the latest SP's and possible hotfixes.
Regards
Mike
"PeterHyssett" wrote:
> Hi.
> From my dektop PC I started Query Analyser on 12 servers and used it to
> execute a Stored Procedure (same database structure on all servers). On eight
> it worked, on four it did not, giving the message:
> ODBC: Msg 0, Level 19,. State 1
> SqlDumpExceptionHandler: Process nnn generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> Connection broken
> Can someone tell me what is going on and how to get round this? The Stored
> Procedure is like this:
> CREATE TABLE... (no problem here)
> INSERT INTO... several thousand rows generated by reading a million plus
> rows
> from a different database on the same server
> (this sometimes works, sometimes it fails at
> this point)
> UPDATE... all the rows from the INSERT, again with data generated by
> reading a
> million plus rows from a different database on the same
> server
> (if it gets beyond this point it works
> correctly)
> UPDATE... as the first update
> UPDATE... as the first update
> similar UPDATE... INSERT... UPDATE... UPDATE... UPDATE... UPDATE
> A valid run takes about 10-15 minutes. The last one I tried failed during
> the first UPDATE after 26 seconds.
> TIA,
> Peter.
|||Thanks - the servers which gave trouble had no service packs applied - the
ones that worked were mostly at SP3.
Regards,
Peter.
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> SQL version and service pack level (select @.@.version)?
> Check that you are on the latest SP's and possible hotfixes.
> Regards
> Mike
> "PeterHyssett" wrote:
|||On the problem server, step through or simplify the code to find where it
breaks.
Jeff
"PeterHyssett" <PeterHyssett@.discussions.microsoft.com> wrote in message
news:A0CF95FA-3109-4A96-95D4-5ACFD7B4305F@.microsoft.com...[vbcol=seagreen]
> Thanks - the servers which gave trouble had no service packs applied - the
> ones that worked were mostly at SP3.
> Regards,
> Peter.
> "Mike Epprecht (SQL MVP)" wrote:
to[vbcol=seagreen]
On eight[vbcol=seagreen]
c0000005[vbcol=seagreen]
Stored[vbcol=seagreen]
plus[vbcol=seagreen]
fails at[vbcol=seagreen]
by[vbcol=seagreen]
same[vbcol=seagreen]
works[vbcol=seagreen]
during[vbcol=seagreen]
|||
> Hi.
> From my dektop PC I started Query Analyser on 12 servers and used it to
> execute a Stored Procedure (same database structure on all servers). On eight
> it worked, on four it did not, giving the message:
> ODBC: Msg 0, Level 19,. State 1
> SqlDumpExceptionHandler: Process nnn generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> Connection broken
> Can someone tell me what is going on and how to get round this? The Stored
> Procedure is like this:
> CREATE TABLE... (no problem here)
> INSERT INTO... several thousand rows generated by reading a million plus
> rows
> from a different database on the same server
> (this sometimes works, sometimes it fails at
> this point)
> UPDATE... all the rows from the INSERT, again with data generated by
> reading a
> million plus rows from a different database on the same
> server
> (if it gets beyond this point it works
> correctly)
> UPDATE... as the first update
> UPDATE... as the first update
> similar UPDATE... INSERT... UPDATE... UPDATE... UPDATE... UPDATE
> A valid run takes about 10-15 minutes. The last one I tried failed during
> the first UPDATE after 26 seconds.
> TIA,
> Peter.
User submitted from AEWNET (http://www.aewnet.com/)
sql
Big query on different servers
Hi.
From my dektop PC I started Query Analyser on 12 servers and used it to
execute a Stored Procedure (same database structure on all servers). On eight
it worked, on four it did not, giving the message:
ODBC: Msg 0, Level 19,. State 1
SqlDumpExceptionHandler: Process nnn generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Connection broken
Can someone tell me what is going on and how to get round this? The Stored
Procedure is like this:
CREATE TABLE... (no problem here)
INSERT INTO... several thousand rows generated by reading a million plus
rows
from a different database on the same server
(this sometimes works, sometimes it fails at
this point)
UPDATE... all the rows from the INSERT, again with data generated by
reading a
million plus rows from a different database on the same
server
(if it gets beyond this point it works
correctly)
UPDATE... as the first update
UPDATE... as the first update
similar UPDATE... INSERT... UPDATE... UPDATE... UPDATE... UPDATE
A valid run takes about 10-15 minutes. The last one I tried failed during
the first UPDATE after 26 seconds.
TIA,
Peter.Hi
SQL version and service pack level (select @.@.version)?
Check that you are on the latest SP's and possible hotfixes.
Regards
Mike
"PeterHyssett" wrote:
> Hi.
> From my dektop PC I started Query Analyser on 12 servers and used it to
> execute a Stored Procedure (same database structure on all servers). On eight
> it worked, on four it did not, giving the message:
> ODBC: Msg 0, Level 19,. State 1
> SqlDumpExceptionHandler: Process nnn generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> Connection broken
> Can someone tell me what is going on and how to get round this? The Stored
> Procedure is like this:
> CREATE TABLE... (no problem here)
> INSERT INTO... several thousand rows generated by reading a million plus
> rows
> from a different database on the same server
> (this sometimes works, sometimes it fails at
> this point)
> UPDATE... all the rows from the INSERT, again with data generated by
> reading a
> million plus rows from a different database on the same
> server
> (if it gets beyond this point it works
> correctly)
> UPDATE... as the first update
> UPDATE... as the first update
> similar UPDATE... INSERT... UPDATE... UPDATE... UPDATE... UPDATE
> A valid run takes about 10-15 minutes. The last one I tried failed during
> the first UPDATE after 26 seconds.
> TIA,
> Peter.|||Thanks - the servers which gave trouble had no service packs applied - the
ones that worked were mostly at SP3.
Regards,
Peter.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> SQL version and service pack level (select @.@.version)?
> Check that you are on the latest SP's and possible hotfixes.
> Regards
> Mike
> "PeterHyssett" wrote:
> > Hi.
> > From my dektop PC I started Query Analyser on 12 servers and used it to
> > execute a Stored Procedure (same database structure on all servers). On eight
> > it worked, on four it did not, giving the message:
> >
> > ODBC: Msg 0, Level 19,. State 1
> > SqlDumpExceptionHandler: Process nnn generated fatal exception c0000005
> > EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> >
> > Connection broken
> >
> > Can someone tell me what is going on and how to get round this? The Stored
> > Procedure is like this:
> >
> > CREATE TABLE... (no problem here)
> > INSERT INTO... several thousand rows generated by reading a million plus
> > rows
> > from a different database on the same server
> > (this sometimes works, sometimes it fails at
> > this point)
> > UPDATE... all the rows from the INSERT, again with data generated by
> > reading a
> > million plus rows from a different database on the same
> > server
> > (if it gets beyond this point it works
> > correctly)
> > UPDATE... as the first update
> > UPDATE... as the first update
> > similar UPDATE... INSERT... UPDATE... UPDATE... UPDATE... UPDATE
> >
> > A valid run takes about 10-15 minutes. The last one I tried failed during
> > the first UPDATE after 26 seconds.
> >
> > TIA,
> >
> > Peter.|||On the problem server, step through or simplify the code to find where it
breaks.
Jeff
"PeterHyssett" <PeterHyssett@.discussions.microsoft.com> wrote in message
news:A0CF95FA-3109-4A96-95D4-5ACFD7B4305F@.microsoft.com...
> Thanks - the servers which gave trouble had no service packs applied - the
> ones that worked were mostly at SP3.
> Regards,
> Peter.
> "Mike Epprecht (SQL MVP)" wrote:
> > Hi
> >
> > SQL version and service pack level (select @.@.version)?
> >
> > Check that you are on the latest SP's and possible hotfixes.
> >
> > Regards
> > Mike
> >
> > "PeterHyssett" wrote:
> >
> > > Hi.
> > > From my dektop PC I started Query Analyser on 12 servers and used it
to
> > > execute a Stored Procedure (same database structure on all servers).
On eight
> > > it worked, on four it did not, giving the message:
> > >
> > > ODBC: Msg 0, Level 19,. State 1
> > > SqlDumpExceptionHandler: Process nnn generated fatal exception
c0000005
> > > EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> > >
> > > Connection broken
> > >
> > > Can someone tell me what is going on and how to get round this? The
Stored
> > > Procedure is like this:
> > >
> > > CREATE TABLE... (no problem here)
> > > INSERT INTO... several thousand rows generated by reading a million
plus
> > > rows
> > > from a different database on the same server
> > > (this sometimes works, sometimes it
fails at
> > > this point)
> > > UPDATE... all the rows from the INSERT, again with data generated
by
> > > reading a
> > > million plus rows from a different database on the
same
> > > server
> > > (if it gets beyond this point it
works
> > > correctly)
> > > UPDATE... as the first update
> > > UPDATE... as the first update
> > > similar UPDATE... INSERT... UPDATE... UPDATE... UPDATE... UPDATE
> > >
> > > A valid run takes about 10-15 minutes. The last one I tried failed
during
> > > the first UPDATE after 26 seconds.
> > >
> > > TIA,
> > >
> > > Peter.|||> Hi.
> From my dektop PC I started Query Analyser on 12 servers and used it to
> execute a Stored Procedure (same database structure on all servers). On eight
> it worked, on four it did not, giving the message:
> ODBC: Msg 0, Level 19,. State 1
> SqlDumpExceptionHandler: Process nnn generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> Connection broken
> Can someone tell me what is going on and how to get round this? The Stored
> Procedure is like this:
> CREATE TABLE... (no problem here)
> INSERT INTO... several thousand rows generated by reading a million plus
> rows
> from a different database on the same server
> (this sometimes works, sometimes it fails at
> this point)
> UPDATE... all the rows from the INSERT, again with data generated by
> reading a
> million plus rows from a different database on the same
> server
> (if it gets beyond this point it works
> correctly)
> UPDATE... as the first update
> UPDATE... as the first update
> similar UPDATE... INSERT... UPDATE... UPDATE... UPDATE... UPDATE
> A valid run takes about 10-15 minutes. The last one I tried failed during
> the first UPDATE after 26 seconds.
> TIA,
> Peter.
User submitted from AEWNET (http://www.aewnet.com/)
From my dektop PC I started Query Analyser on 12 servers and used it to
execute a Stored Procedure (same database structure on all servers). On eight
it worked, on four it did not, giving the message:
ODBC: Msg 0, Level 19,. State 1
SqlDumpExceptionHandler: Process nnn generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Connection broken
Can someone tell me what is going on and how to get round this? The Stored
Procedure is like this:
CREATE TABLE... (no problem here)
INSERT INTO... several thousand rows generated by reading a million plus
rows
from a different database on the same server
(this sometimes works, sometimes it fails at
this point)
UPDATE... all the rows from the INSERT, again with data generated by
reading a
million plus rows from a different database on the same
server
(if it gets beyond this point it works
correctly)
UPDATE... as the first update
UPDATE... as the first update
similar UPDATE... INSERT... UPDATE... UPDATE... UPDATE... UPDATE
A valid run takes about 10-15 minutes. The last one I tried failed during
the first UPDATE after 26 seconds.
TIA,
Peter.Hi
SQL version and service pack level (select @.@.version)?
Check that you are on the latest SP's and possible hotfixes.
Regards
Mike
"PeterHyssett" wrote:
> Hi.
> From my dektop PC I started Query Analyser on 12 servers and used it to
> execute a Stored Procedure (same database structure on all servers). On eight
> it worked, on four it did not, giving the message:
> ODBC: Msg 0, Level 19,. State 1
> SqlDumpExceptionHandler: Process nnn generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> Connection broken
> Can someone tell me what is going on and how to get round this? The Stored
> Procedure is like this:
> CREATE TABLE... (no problem here)
> INSERT INTO... several thousand rows generated by reading a million plus
> rows
> from a different database on the same server
> (this sometimes works, sometimes it fails at
> this point)
> UPDATE... all the rows from the INSERT, again with data generated by
> reading a
> million plus rows from a different database on the same
> server
> (if it gets beyond this point it works
> correctly)
> UPDATE... as the first update
> UPDATE... as the first update
> similar UPDATE... INSERT... UPDATE... UPDATE... UPDATE... UPDATE
> A valid run takes about 10-15 minutes. The last one I tried failed during
> the first UPDATE after 26 seconds.
> TIA,
> Peter.|||Thanks - the servers which gave trouble had no service packs applied - the
ones that worked were mostly at SP3.
Regards,
Peter.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> SQL version and service pack level (select @.@.version)?
> Check that you are on the latest SP's and possible hotfixes.
> Regards
> Mike
> "PeterHyssett" wrote:
> > Hi.
> > From my dektop PC I started Query Analyser on 12 servers and used it to
> > execute a Stored Procedure (same database structure on all servers). On eight
> > it worked, on four it did not, giving the message:
> >
> > ODBC: Msg 0, Level 19,. State 1
> > SqlDumpExceptionHandler: Process nnn generated fatal exception c0000005
> > EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> >
> > Connection broken
> >
> > Can someone tell me what is going on and how to get round this? The Stored
> > Procedure is like this:
> >
> > CREATE TABLE... (no problem here)
> > INSERT INTO... several thousand rows generated by reading a million plus
> > rows
> > from a different database on the same server
> > (this sometimes works, sometimes it fails at
> > this point)
> > UPDATE... all the rows from the INSERT, again with data generated by
> > reading a
> > million plus rows from a different database on the same
> > server
> > (if it gets beyond this point it works
> > correctly)
> > UPDATE... as the first update
> > UPDATE... as the first update
> > similar UPDATE... INSERT... UPDATE... UPDATE... UPDATE... UPDATE
> >
> > A valid run takes about 10-15 minutes. The last one I tried failed during
> > the first UPDATE after 26 seconds.
> >
> > TIA,
> >
> > Peter.|||On the problem server, step through or simplify the code to find where it
breaks.
Jeff
"PeterHyssett" <PeterHyssett@.discussions.microsoft.com> wrote in message
news:A0CF95FA-3109-4A96-95D4-5ACFD7B4305F@.microsoft.com...
> Thanks - the servers which gave trouble had no service packs applied - the
> ones that worked were mostly at SP3.
> Regards,
> Peter.
> "Mike Epprecht (SQL MVP)" wrote:
> > Hi
> >
> > SQL version and service pack level (select @.@.version)?
> >
> > Check that you are on the latest SP's and possible hotfixes.
> >
> > Regards
> > Mike
> >
> > "PeterHyssett" wrote:
> >
> > > Hi.
> > > From my dektop PC I started Query Analyser on 12 servers and used it
to
> > > execute a Stored Procedure (same database structure on all servers).
On eight
> > > it worked, on four it did not, giving the message:
> > >
> > > ODBC: Msg 0, Level 19,. State 1
> > > SqlDumpExceptionHandler: Process nnn generated fatal exception
c0000005
> > > EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> > >
> > > Connection broken
> > >
> > > Can someone tell me what is going on and how to get round this? The
Stored
> > > Procedure is like this:
> > >
> > > CREATE TABLE... (no problem here)
> > > INSERT INTO... several thousand rows generated by reading a million
plus
> > > rows
> > > from a different database on the same server
> > > (this sometimes works, sometimes it
fails at
> > > this point)
> > > UPDATE... all the rows from the INSERT, again with data generated
by
> > > reading a
> > > million plus rows from a different database on the
same
> > > server
> > > (if it gets beyond this point it
works
> > > correctly)
> > > UPDATE... as the first update
> > > UPDATE... as the first update
> > > similar UPDATE... INSERT... UPDATE... UPDATE... UPDATE... UPDATE
> > >
> > > A valid run takes about 10-15 minutes. The last one I tried failed
during
> > > the first UPDATE after 26 seconds.
> > >
> > > TIA,
> > >
> > > Peter.|||> Hi.
> From my dektop PC I started Query Analyser on 12 servers and used it to
> execute a Stored Procedure (same database structure on all servers). On eight
> it worked, on four it did not, giving the message:
> ODBC: Msg 0, Level 19,. State 1
> SqlDumpExceptionHandler: Process nnn generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> Connection broken
> Can someone tell me what is going on and how to get round this? The Stored
> Procedure is like this:
> CREATE TABLE... (no problem here)
> INSERT INTO... several thousand rows generated by reading a million plus
> rows
> from a different database on the same server
> (this sometimes works, sometimes it fails at
> this point)
> UPDATE... all the rows from the INSERT, again with data generated by
> reading a
> million plus rows from a different database on the same
> server
> (if it gets beyond this point it works
> correctly)
> UPDATE... as the first update
> UPDATE... as the first update
> similar UPDATE... INSERT... UPDATE... UPDATE... UPDATE... UPDATE
> A valid run takes about 10-15 minutes. The last one I tried failed during
> the first UPDATE after 26 seconds.
> TIA,
> Peter.
User submitted from AEWNET (http://www.aewnet.com/)
Monday, March 19, 2012
Big Database
Hello all, i have a problem with a big database, the problem is that any query executed on that database, takes much much time, but i think that the db is over sized (5 gb aprox), because the table that contains most records has only 650.000, and i worked with db containing tables with more records and that not pass 1 gb.
Question 1: Why the db has grow so big?
Question 2: What can i do to speed up the querys?
Thanks for your help, and sorry for my english!I see that the database file has 650 mb (that's ok) but the log file has 5350 mb (that's absolutly wrong), i tried to truncate or shrink log file, but does'nt work.|||If you are not doing transaction log backups, you need to set the recovery mode to simple. After this, shrink the files and they will disappear to almost nothing. You only need full recovery mode, which is what you are probably using if your log files are that big.|||hey, i have reading a bit about that question of the recovery mode, but thanks for your suggestion, now my DB has 560 mb only!, that's great, but, the querys are a little bit slow yet!
Remember, i'm a programmer, not bilingual! jejeje|||Hello parmaia,
If you interested in making the queries to execute faster - I would start with doing a performance audit of your box . Check out sql-server-performance.com , Brad M. McGehee did a very nice series of articles on how to audit sql db server
http://www.sql-server-performance.com/sql_server_performance_audit.asp
If you run into questions, ask (either here or within sql-server-performance forums)
simas
Question 1: Why the db has grow so big?
Question 2: What can i do to speed up the querys?
Thanks for your help, and sorry for my english!I see that the database file has 650 mb (that's ok) but the log file has 5350 mb (that's absolutly wrong), i tried to truncate or shrink log file, but does'nt work.|||If you are not doing transaction log backups, you need to set the recovery mode to simple. After this, shrink the files and they will disappear to almost nothing. You only need full recovery mode, which is what you are probably using if your log files are that big.|||hey, i have reading a bit about that question of the recovery mode, but thanks for your suggestion, now my DB has 560 mb only!, that's great, but, the querys are a little bit slow yet!
Remember, i'm a programmer, not bilingual! jejeje|||Hello parmaia,
If you interested in making the queries to execute faster - I would start with doing a performance audit of your box . Check out sql-server-performance.com , Brad M. McGehee did a very nice series of articles on how to audit sql db server
http://www.sql-server-performance.com/sql_server_performance_audit.asp
If you run into questions, ask (either here or within sql-server-performance forums)
simas
Wednesday, March 7, 2012
Between vs. >= and <=
Which is better to use? I heard that <= and >= are better because it allows
SQL Server to retain the query plan in cache, or something like that. This i
s
in reference to a date comparison.
SELECT * FROM <table> WHERE <date> between <start_date> and <end_date>
SELECT * FROM <table> WHERE <date> >= <start_date> and <date> <= <end_date>
Performance wise they use they same indexes and are not different. But is
there an internal reason to use one over the other like plan reuse, or
caching?http://www.aspfaq.com/2280
"John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
news:F6C222FC-DB4A-4FE4-B8A6-2B84B9CB30E2@.microsoft.com...
> Which is better to use? I heard that <= and >= are better because it
> allows
> SQL Server to retain the query plan in cache, or something like that. This
> is
> in reference to a date comparison.
> SELECT * FROM <table> WHERE <date> between <start_date> and <end_date>
> SELECT * FROM <table> WHERE <date> >= <start_date> and <date> <=
> <end_date>
> Performance wise they use they same indexes and are not different. But is
> there an internal reason to use one over the other like plan reuse, or
> caching?
>|||Is there any information on caching or query plan reuse? Basically, I am
looking for information pertaining to the fact that SQL Server converts a
Between to a >= and <= pair, and if you resubmit the same between statement,
it converts it again and regenerates it plan. If you use the >= and <=
instead, it will reuse the existing plan to re-execute the statement. Is thi
s
true? and if so, is there documentation on it?
"Aaron Bertrand [SQL Server MVP]" wrote:
> http://www.aspfaq.com/2280
>
> "John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
> news:F6C222FC-DB4A-4FE4-B8A6-2B84B9CB30E2@.microsoft.com...
>
>|||I would think you would want to use DateDiff(), but that's just me.
Camps for Troubled Teens
http://www.brat-camps.com
Brat Camps - Free Search; Results Fast
"John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
news:F6C222FC-DB4A-4FE4-B8A6-2B84B9CB30E2@.microsoft.com...
Which is better to use? I heard that <= and >= are better because it allows
SQL Server to retain the query plan in cache, or something like that. This
is
in reference to a date comparison.
SELECT * FROM <table> WHERE <date> between <start_date> and <end_date>
SELECT * FROM <table> WHERE <date> >= <start_date> and <date> <= <end_date>
Performance wise they use they same indexes and are not different. But is
there an internal reason to use one over the other like plan reuse, or
caching?|||I have provided a example of why using datediff is not a correct solution in
this example.
Using between (as you will see in query anyliser) it will do an index s
,
using the datediff method causes it to ignore the index and scan the full
table.
IF OBJECT_ID('tempdb..#tmpDates') IS NOT NULL DROP TABLE #tmpDates
CREATE TABLE #tmpDates ( myDate DATETIME PRIMARY KEY CLUSTERED )
INSERT INTO
#tmpDates
SELECT
DATEADD( d , -n , GETDATE() ) AS myDate
FROM
tblNumbers
WHERE
N BETWEEN 1 AND 20
/* uses indexed s */
SELECT
*
FROM
#tmpDates
WHERE
myDate BETWEEN {d '2005-09-22'} AND DATEADD( ms , -3 , {d '2005-09-29'} )
/* forces scan */
SELECT
*
FROM
#tmpDates
WHERE
DATEDIFF( d , {d '2005-09-22'} , myDate ) >= 0
AND
DATEDIFF( d , myDate , {d '2005-09-28'} ) >= 0
"Brat Camps" <spam at diamondflex dot com> wrote in message
news:ONvyf30yFHA.916@.TK2MSFTNGP10.phx.gbl...
> I would think you would want to use DateDiff(), but that's just me.
> --
> Camps for Troubled Teens
> http://www.brat-camps.com
> Brat Camps - Free Search; Results Fast
>
> "John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
> news:F6C222FC-DB4A-4FE4-B8A6-2B84B9CB30E2@.microsoft.com...
> Which is better to use? I heard that <= and >= are better because it
allows
> SQL Server to retain the query plan in cache, or something like that. This
> is
> in reference to a date comparison.
> SELECT * FROM <table> WHERE <date> between <start_date> and <end_date>
> SELECT * FROM <table> WHERE <date> >= <start_date> and <date> <=
<end_date>
> Performance wise they use they same indexes and are not different. But is
> there an internal reason to use one over the other like plan reuse, or
> caching?
>
>|||Hammer that box! Woo Hoo!!!
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"Rebecca York" wrote:
> I have provided a example of why using datediff is not a correct solution
in
> this example.
> Using between (as you will see in query anyliser) it will do an index s
,
> using the datediff method causes it to ignore the index and scan the full
> table.
>
> IF OBJECT_ID('tempdb..#tmpDates') IS NOT NULL DROP TABLE #tmpDates
> CREATE TABLE #tmpDates ( myDate DATETIME PRIMARY KEY CLUSTERED )
> INSERT INTO
> #tmpDates
> SELECT
> DATEADD( d , -n , GETDATE() ) AS myDate
> FROM
> tblNumbers
> WHERE
> N BETWEEN 1 AND 20
> /* uses indexed s */
> SELECT
> *
> FROM
> #tmpDates
> WHERE
> myDate BETWEEN {d '2005-09-22'} AND DATEADD( ms , -3 , {d '2005-09-29'} )
> /* forces scan */
> SELECT
> *
> FROM
> #tmpDates
> WHERE
> DATEDIFF( d , {d '2005-09-22'} , myDate ) >= 0
> AND
> DATEDIFF( d , myDate , {d '2005-09-28'} ) >= 0
>
>
> "Brat Camps" <spam at diamondflex dot com> wrote in message
> news:ONvyf30yFHA.916@.TK2MSFTNGP10.phx.gbl...
> allows
> <end_date>
>
>|||Is there any information on caching or query plan reuse? Basically, I am
looking for information pertaining to the fact that SQL Server converts a
Between to a >= and <= pair, and if you resubmit the same between statement,
it converts it again and regenerates it plan. If you use the >= and <=
instead, it will reuse the existing plan to re-execute the statement. Is thi
s
true? and if so, is there documentation on it?
"John Barr" wrote:
> Which is better to use? I heard that <= and >= are better because it allow
s
> SQL Server to retain the query plan in cache, or something like that. This
is
> in reference to a date comparison.
> SELECT * FROM <table> WHERE <date> between <start_date> and <end_date>
> SELECT * FROM <table> WHERE <date> >= <start_date> and <date> <= <end_date
>
> Performance wise they use they same indexes and are not different. But is
> there an internal reason to use one over the other like plan reuse, or
> caching?
>|||On Fri, 7 Oct 2005 07:44:04 -0700, John Barr wrote:
>Is there any information on caching or query plan reuse? Basically, I am
>looking for information pertaining to the fact that SQL Server converts a
>Between to a >= and <= pair, and if you resubmit the same between statement
,
>it converts it again and regenerates it plan. If you use the >= and <=
>instead, it will reuse the existing plan to re-execute the statement. Is th
is
>true? and if so, is there documentation on it?
Hi John,
This is not true. It would be quite silly if MS had overlooked this - if
each proc that uses a BETWEEN is recompiled on each execution, DBA's
would be screaming and shouting at MS.
To test this, simple start Profiler, run the following code, then check
for any SP:Recompile events in the profiler trace.
use pubs
go
create proc dbo.test
as
select * from authors where au_fname between 'A' and 'C'
go
exec dbo.test
go
exec dbo.test
go
exec dbo.test
go
drop proc dbo.test
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||John
If you the BETWEEN, and you have a usable index so that the plan can be
autoparameterized, you will see that the cached plan in syscacheobjects has
already converted it to use the >= and <=
So then running the query with the >= and <= will use the already cached
plan. No new plan will need to be created.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
news:0961B42D-005E-4AEB-8143-97472DF54BBB@.microsoft.com...
> Is there any information on caching or query plan reuse? Basically, I am
> looking for information pertaining to the fact that SQL Server converts a
> Between to a >= and <= pair, and if you resubmit the same between
> statement,
> it converts it again and regenerates it plan. If you use the >= and <=
> instead, it will reuse the existing plan to re-execute the statement. Is
> this
> true? and if so, is there documentation on it?
>
> "John Barr" wrote:
>
>|||Since they compile the same way, ask yourself which form gives you
easier to read and maintain code. You will pick BETWEEN for the poor
guy that comes after you.
SQL Server to retain the query plan in cache, or something like that. This i
s
in reference to a date comparison.
SELECT * FROM <table> WHERE <date> between <start_date> and <end_date>
SELECT * FROM <table> WHERE <date> >= <start_date> and <date> <= <end_date>
Performance wise they use they same indexes and are not different. But is
there an internal reason to use one over the other like plan reuse, or
caching?http://www.aspfaq.com/2280
"John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
news:F6C222FC-DB4A-4FE4-B8A6-2B84B9CB30E2@.microsoft.com...
> Which is better to use? I heard that <= and >= are better because it
> allows
> SQL Server to retain the query plan in cache, or something like that. This
> is
> in reference to a date comparison.
> SELECT * FROM <table> WHERE <date> between <start_date> and <end_date>
> SELECT * FROM <table> WHERE <date> >= <start_date> and <date> <=
> <end_date>
> Performance wise they use they same indexes and are not different. But is
> there an internal reason to use one over the other like plan reuse, or
> caching?
>|||Is there any information on caching or query plan reuse? Basically, I am
looking for information pertaining to the fact that SQL Server converts a
Between to a >= and <= pair, and if you resubmit the same between statement,
it converts it again and regenerates it plan. If you use the >= and <=
instead, it will reuse the existing plan to re-execute the statement. Is thi
s
true? and if so, is there documentation on it?
"Aaron Bertrand [SQL Server MVP]" wrote:
> http://www.aspfaq.com/2280
>
> "John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
> news:F6C222FC-DB4A-4FE4-B8A6-2B84B9CB30E2@.microsoft.com...
>
>|||I would think you would want to use DateDiff(), but that's just me.
Camps for Troubled Teens
http://www.brat-camps.com
Brat Camps - Free Search; Results Fast
"John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
news:F6C222FC-DB4A-4FE4-B8A6-2B84B9CB30E2@.microsoft.com...
Which is better to use? I heard that <= and >= are better because it allows
SQL Server to retain the query plan in cache, or something like that. This
is
in reference to a date comparison.
SELECT * FROM <table> WHERE <date> between <start_date> and <end_date>
SELECT * FROM <table> WHERE <date> >= <start_date> and <date> <= <end_date>
Performance wise they use they same indexes and are not different. But is
there an internal reason to use one over the other like plan reuse, or
caching?|||I have provided a example of why using datediff is not a correct solution in
this example.
Using between (as you will see in query anyliser) it will do an index s
,using the datediff method causes it to ignore the index and scan the full
table.
IF OBJECT_ID('tempdb..#tmpDates') IS NOT NULL DROP TABLE #tmpDates
CREATE TABLE #tmpDates ( myDate DATETIME PRIMARY KEY CLUSTERED )
INSERT INTO
#tmpDates
SELECT
DATEADD( d , -n , GETDATE() ) AS myDate
FROM
tblNumbers
WHERE
N BETWEEN 1 AND 20
/* uses indexed s
*/SELECT
*
FROM
#tmpDates
WHERE
myDate BETWEEN {d '2005-09-22'} AND DATEADD( ms , -3 , {d '2005-09-29'} )
/* forces scan */
SELECT
*
FROM
#tmpDates
WHERE
DATEDIFF( d , {d '2005-09-22'} , myDate ) >= 0
AND
DATEDIFF( d , myDate , {d '2005-09-28'} ) >= 0
"Brat Camps" <spam at diamondflex dot com> wrote in message
news:ONvyf30yFHA.916@.TK2MSFTNGP10.phx.gbl...
> I would think you would want to use DateDiff(), but that's just me.
> --
> Camps for Troubled Teens
> http://www.brat-camps.com
> Brat Camps - Free Search; Results Fast
>
> "John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
> news:F6C222FC-DB4A-4FE4-B8A6-2B84B9CB30E2@.microsoft.com...
> Which is better to use? I heard that <= and >= are better because it
allows
> SQL Server to retain the query plan in cache, or something like that. This
> is
> in reference to a date comparison.
> SELECT * FROM <table> WHERE <date> between <start_date> and <end_date>
> SELECT * FROM <table> WHERE <date> >= <start_date> and <date> <=
<end_date>
> Performance wise they use they same indexes and are not different. But is
> there an internal reason to use one over the other like plan reuse, or
> caching?
>
>|||Hammer that box! Woo Hoo!!!
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"Rebecca York" wrote:
> I have provided a example of why using datediff is not a correct solution
in
> this example.
> Using between (as you will see in query anyliser) it will do an index s
,
> using the datediff method causes it to ignore the index and scan the full
> table.
>
> IF OBJECT_ID('tempdb..#tmpDates') IS NOT NULL DROP TABLE #tmpDates
> CREATE TABLE #tmpDates ( myDate DATETIME PRIMARY KEY CLUSTERED )
> INSERT INTO
> #tmpDates
> SELECT
> DATEADD( d , -n , GETDATE() ) AS myDate
> FROM
> tblNumbers
> WHERE
> N BETWEEN 1 AND 20
> /* uses indexed s
*/> SELECT
> *
> FROM
> #tmpDates
> WHERE
> myDate BETWEEN {d '2005-09-22'} AND DATEADD( ms , -3 , {d '2005-09-29'} )
> /* forces scan */
> SELECT
> *
> FROM
> #tmpDates
> WHERE
> DATEDIFF( d , {d '2005-09-22'} , myDate ) >= 0
> AND
> DATEDIFF( d , myDate , {d '2005-09-28'} ) >= 0
>
>
> "Brat Camps" <spam at diamondflex dot com> wrote in message
> news:ONvyf30yFHA.916@.TK2MSFTNGP10.phx.gbl...
> allows
> <end_date>
>
>|||Is there any information on caching or query plan reuse? Basically, I am
looking for information pertaining to the fact that SQL Server converts a
Between to a >= and <= pair, and if you resubmit the same between statement,
it converts it again and regenerates it plan. If you use the >= and <=
instead, it will reuse the existing plan to re-execute the statement. Is thi
s
true? and if so, is there documentation on it?
"John Barr" wrote:
> Which is better to use? I heard that <= and >= are better because it allow
s
> SQL Server to retain the query plan in cache, or something like that. This
is
> in reference to a date comparison.
> SELECT * FROM <table> WHERE <date> between <start_date> and <end_date>
> SELECT * FROM <table> WHERE <date> >= <start_date> and <date> <= <end_date
>
> Performance wise they use they same indexes and are not different. But is
> there an internal reason to use one over the other like plan reuse, or
> caching?
>|||On Fri, 7 Oct 2005 07:44:04 -0700, John Barr wrote:
>Is there any information on caching or query plan reuse? Basically, I am
>looking for information pertaining to the fact that SQL Server converts a
>Between to a >= and <= pair, and if you resubmit the same between statement
,
>it converts it again and regenerates it plan. If you use the >= and <=
>instead, it will reuse the existing plan to re-execute the statement. Is th
is
>true? and if so, is there documentation on it?
Hi John,
This is not true. It would be quite silly if MS had overlooked this - if
each proc that uses a BETWEEN is recompiled on each execution, DBA's
would be screaming and shouting at MS.
To test this, simple start Profiler, run the following code, then check
for any SP:Recompile events in the profiler trace.
use pubs
go
create proc dbo.test
as
select * from authors where au_fname between 'A' and 'C'
go
exec dbo.test
go
exec dbo.test
go
exec dbo.test
go
drop proc dbo.test
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||John
If you the BETWEEN, and you have a usable index so that the plan can be
autoparameterized, you will see that the cached plan in syscacheobjects has
already converted it to use the >= and <=
So then running the query with the >= and <= will use the already cached
plan. No new plan will need to be created.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
news:0961B42D-005E-4AEB-8143-97472DF54BBB@.microsoft.com...
> Is there any information on caching or query plan reuse? Basically, I am
> looking for information pertaining to the fact that SQL Server converts a
> Between to a >= and <= pair, and if you resubmit the same between
> statement,
> it converts it again and regenerates it plan. If you use the >= and <=
> instead, it will reuse the existing plan to re-execute the statement. Is
> this
> true? and if so, is there documentation on it?
>
> "John Barr" wrote:
>
>|||Since they compile the same way, ask yourself which form gives you
easier to read and maintain code. You will pick BETWEEN for the poor
guy that comes after you.
BETWEEN statement
I was wondering if someone can correct me on this. When I run this query I get no results displayed. By default, I made sure I used all the values from beginning to end to test it.
USE POS410
GO
SELECT SSN#, Hire_date
FROM Employee
WHERE Hire_date BETWEEN 1/1/1995 AND 12/31/2005
GO1/1/1995 is an arithmetic expression meaning "1 divided by 1 divided by 1995"
this will round off to zero
you will probably get some results if you write this --
WHERE Hire_date BETWEEN '1995-01-01' AND '2005-12-31'|||that did the trick! thank you
USE POS410
GO
SELECT SSN#, Hire_date
FROM Employee
WHERE Hire_date BETWEEN 1/1/1995 AND 12/31/2005
GO1/1/1995 is an arithmetic expression meaning "1 divided by 1 divided by 1995"
this will round off to zero
you will probably get some results if you write this --
WHERE Hire_date BETWEEN '1995-01-01' AND '2005-12-31'|||that did the trick! thank you
BETWEEN PROBLEM IN DATETIME QUERY
I have some Records in my table like that
2006-06-16 14:15:01.607
2006-06-16 14:17:28.187
2006-06-15 14:17:28.000
2006-06-13 14:17:28.000
2006-06-17 14:17:28.000
2006-06-19 14:17:10.000
2006-06-17 14:17:28.000
I want to have count number of the records if they have same date. I dont
care about hour,minute and second info. I care about query result based on
daily records.
When I Wrote
DECLARE @.Date AS VARCHAR(50)
DECLARE @.Date2 AS VARCHAR(50)
SELECT @.Date='13.06.1900'
SELECT @.Date2='13.06.2010'
SELECT @.Date=CONVERT (VARCHAR(10),@.Date,104)
SELECT @.Date2=CONVERT (VARCHAR(10),@.Date2,104)
SELECt CONVERT (VARCHAR(10), Referer_time,104 ) ,Count(*)
from TBL_referer
Group BY
CONVERT (VARCHAR(10), Referer_time,104 )
HAVING CONVERT (VARCHAR(10), Referer_time,104 )
> @.Date
It returns
13.06.2006 1
15.06.2006 1
16.06.2006 2
17.06.2006 2
19.06.2006 1
it is right one..
When I change it with that (Only Changing is BETWEEN @.Date AND @.Date2
instead of > @.Date )
DECLARE @.Date AS VARCHAR(50)
DECLARE @.Date2 AS VARCHAR(50)
SELECT @.Date='13.06.1900'
SELECT @.Date2='13.06.2010'
SELECT @.Date=CONVERT (VARCHAR(10),@.Date,104)
SELECT @.Date2=CONVERT (VARCHAR(10),@.Date2,104)
SELECt CONVERT (VARCHAR(10), Referer_time,104 ) ,Count(*)
from TBL_referer
Group BY
CONVERT (VARCHAR(10), Referer_time,104 )
HAVING CONVERT (VARCHAR(10), Referer_time,104 )
BETWEEN @.Date AND @.Date2
It olny returns
13.06.2006 1
What is the mistake ?
Could anyone help me ?Why are you converting in your having/where clause?
Take a look at this
create table TBL_referer (Referer_time datetime)
insert TBL_referer values('2006-06-16 14:15:01.607 ')
insert TBL_referer values('2006-06-16 14:17:28.187')
insert TBL_referer values('2006-06-15 14:17:28.000')
insert TBL_referer values('2006-06-13 14:17:28.000 ')
insert TBL_referer values('2006-06-17 14:17:28.000')
insert TBL_referer values('2006-06-19 14:17:10.000')
insert TBL_referer values('2006-06-17 14:17:28.000')
DECLARE @.Date AS datetime
DECLARE @.Date2 AS datetime
SELECT @.Date='19000613'
SELECT @.Date2='20100613'
-- >
SELECT CONVERT (VARCHAR(10), Referer_time,104 ) ,Count(*)
from TBL_referer
WHERE Referer_time > @.Date
Group BY
CONVERT (VARCHAR(10), Referer_time,104 )
--Between
SELECT CONVERT (VARCHAR(10), Referer_time,104 ) ,Count(*)
from TBL_referer
WHERE Referer_time BETWEEN @.Date AND @.Date2
Group BY
CONVERT (VARCHAR(10), Referer_time,104 )
You see no HAVING but a WHERE clause and no convert either
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Savas Ates wrote:
> I have some Records in my table like that
> 2006-06-16 14:15:01.607
> 2006-06-16 14:17:28.187
> 2006-06-15 14:17:28.000
> 2006-06-13 14:17:28.000
> 2006-06-17 14:17:28.000
> 2006-06-19 14:17:10.000
> 2006-06-17 14:17:28.000
> I want to have count number of the records if they have same date. I dont
> care about hour,minute and second info. I care about query result based on
> daily records.
> When I Wrote
> DECLARE @.Date AS VARCHAR(50)
> DECLARE @.Date2 AS VARCHAR(50)
> SELECT @.Date='13.06.1900'
> SELECT @.Date2='13.06.2010'
>
> SELECT @.Date=CONVERT (VARCHAR(10),@.Date,104)
> SELECT @.Date2=CONVERT (VARCHAR(10),@.Date2,104)
>
> SELECt CONVERT (VARCHAR(10), Referer_time,104 ) ,Count(*)
> from TBL_referer
> Group BY
> CONVERT (VARCHAR(10), Referer_time,104 )
>
> HAVING CONVERT (VARCHAR(10), Referer_time,104 )
> It returns
> 13.06.2006 1
> 15.06.2006 1
> 16.06.2006 2
> 17.06.2006 2
> 19.06.2006 1
> it is right one..
> When I change it with that (Only Changing is BETWEEN @.Date AND @.Date2
> instead of > @.Date )
> DECLARE @.Date AS VARCHAR(50)
> DECLARE @.Date2 AS VARCHAR(50)
> SELECT @.Date='13.06.1900'
> SELECT @.Date2='13.06.2010'
>
> SELECT @.Date=CONVERT (VARCHAR(10),@.Date,104)
> SELECT @.Date2=CONVERT (VARCHAR(10),@.Date2,104)
>
> SELECt CONVERT (VARCHAR(10), Referer_time,104 ) ,Count(*)
> from TBL_referer
> Group BY
> CONVERT (VARCHAR(10), Referer_time,104 )
>
> HAVING CONVERT (VARCHAR(10), Referer_time,104 )
> BETWEEN @.Date AND @.Date2
> It olny returns
> 13.06.2006 1
> What is the mistake ?
> Could anyone help me ?|||Thank You For your helpinh But How come Sql server evaluates
That.
WHERE Referer_time BETWEEN @.Date AND @.Date2
I Have
SELECT @.Date='19000613'
SELECT @.Date2='20060618'
But my records are like that
2006-06-16 14:15:01.607
2006-06-16 14:17:28.187
2006-06-15 14:17:28.000
2006-06-13 14:17:28.000
2006-06-17 14:17:28.000
2006-06-13 14:17:10.000
2006-06-17 14:17:28.000
can you give me an article or explanation how it does it ?|||Here is all you need
http://www.karaszi.com/SQLServer/info_datetime.asp
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Savas Ates wrote:
> Thank You For your helpinh But How come Sql server evaluates
> That.
> WHERE Referer_time BETWEEN @.Date AND @.Date2
> I Have
> SELECT @.Date='19000613'
> SELECT @.Date2='20060618'
> But my records are like that
> 2006-06-16 14:15:01.607
> 2006-06-16 14:17:28.187
> 2006-06-15 14:17:28.000
> 2006-06-13 14:17:28.000
> 2006-06-17 14:17:28.000
> 2006-06-13 14:17:10.000
> 2006-06-17 14:17:28.000
> can you give me an article or explanation how it does it ?|||This should help you: http://www.karaszi.com/SQLServer/info_datetime.asp. Yo
u can post back if you
still have problems after reading the article.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Savas Ates" <in da club> wrote in message news:OE457ZdlGHA.4052@.TK2MSFTNGP05.phx.gbl...[co
lor=darkred]
>I have some Records in my table like that
> 2006-06-16 14:15:01.607
> 2006-06-16 14:17:28.187
> 2006-06-15 14:17:28.000
> 2006-06-13 14:17:28.000
> 2006-06-17 14:17:28.000
> 2006-06-19 14:17:10.000
> 2006-06-17 14:17:28.000
> I want to have count number of the records if they have same date. I dont
care about hour,minute
> and second info. I care about query result based on daily records.
> When I Wrote
> DECLARE @.Date AS VARCHAR(50)
> DECLARE @.Date2 AS VARCHAR(50)
> SELECT @.Date='13.06.1900'
> SELECT @.Date2='13.06.2010'
>
> SELECT @.Date=CONVERT (VARCHAR(10),@.Date,104)
> SELECT @.Date2=CONVERT (VARCHAR(10),@.Date2,104)
>
> SELECt CONVERT (VARCHAR(10), Referer_time,104 ) ,Count(*)
> from TBL_referer
> Group BY
> CONVERT (VARCHAR(10), Referer_time,104 )
>
> HAVING CONVERT (VARCHAR(10), Referer_time,104 )
> It returns
> 13.06.2006 1
> 15.06.2006 1
> 16.06.2006 2
> 17.06.2006 2
> 19.06.2006 1
> it is right one..
> When I change it with that (Only Changing is BETWEEN @.Date AND @.Date2 i
nstead of > @.Date )
> DECLARE @.Date AS VARCHAR(50)
> DECLARE @.Date2 AS VARCHAR(50)
> SELECT @.Date='13.06.1900'
> SELECT @.Date2='13.06.2010'
>
> SELECT @.Date=CONVERT (VARCHAR(10),@.Date,104)
> SELECT @.Date2=CONVERT (VARCHAR(10),@.Date2,104)
>
> SELECt CONVERT (VARCHAR(10), Referer_time,104 ) ,Count(*)
> from TBL_referer
> Group BY
> CONVERT (VARCHAR(10), Referer_time,104 )
>
> HAVING CONVERT (VARCHAR(10), Referer_time,104 )
> BETWEEN @.Date AND @.Date2
> It olny returns
> 13.06.2006 1
> What is the mistake ?
> Could anyone help me ?
>
>
>[/color]
2006-06-16 14:15:01.607
2006-06-16 14:17:28.187
2006-06-15 14:17:28.000
2006-06-13 14:17:28.000
2006-06-17 14:17:28.000
2006-06-19 14:17:10.000
2006-06-17 14:17:28.000
I want to have count number of the records if they have same date. I dont
care about hour,minute and second info. I care about query result based on
daily records.
When I Wrote
DECLARE @.Date AS VARCHAR(50)
DECLARE @.Date2 AS VARCHAR(50)
SELECT @.Date='13.06.1900'
SELECT @.Date2='13.06.2010'
SELECT @.Date=CONVERT (VARCHAR(10),@.Date,104)
SELECT @.Date2=CONVERT (VARCHAR(10),@.Date2,104)
SELECt CONVERT (VARCHAR(10), Referer_time,104 ) ,Count(*)
from TBL_referer
Group BY
CONVERT (VARCHAR(10), Referer_time,104 )
HAVING CONVERT (VARCHAR(10), Referer_time,104 )
> @.Date
It returns
13.06.2006 1
15.06.2006 1
16.06.2006 2
17.06.2006 2
19.06.2006 1
it is right one..
When I change it with that (Only Changing is BETWEEN @.Date AND @.Date2
instead of > @.Date )
DECLARE @.Date AS VARCHAR(50)
DECLARE @.Date2 AS VARCHAR(50)
SELECT @.Date='13.06.1900'
SELECT @.Date2='13.06.2010'
SELECT @.Date=CONVERT (VARCHAR(10),@.Date,104)
SELECT @.Date2=CONVERT (VARCHAR(10),@.Date2,104)
SELECt CONVERT (VARCHAR(10), Referer_time,104 ) ,Count(*)
from TBL_referer
Group BY
CONVERT (VARCHAR(10), Referer_time,104 )
HAVING CONVERT (VARCHAR(10), Referer_time,104 )
BETWEEN @.Date AND @.Date2
It olny returns
13.06.2006 1
What is the mistake ?
Could anyone help me ?Why are you converting in your having/where clause?
Take a look at this
create table TBL_referer (Referer_time datetime)
insert TBL_referer values('2006-06-16 14:15:01.607 ')
insert TBL_referer values('2006-06-16 14:17:28.187')
insert TBL_referer values('2006-06-15 14:17:28.000')
insert TBL_referer values('2006-06-13 14:17:28.000 ')
insert TBL_referer values('2006-06-17 14:17:28.000')
insert TBL_referer values('2006-06-19 14:17:10.000')
insert TBL_referer values('2006-06-17 14:17:28.000')
DECLARE @.Date AS datetime
DECLARE @.Date2 AS datetime
SELECT @.Date='19000613'
SELECT @.Date2='20100613'
-- >
SELECT CONVERT (VARCHAR(10), Referer_time,104 ) ,Count(*)
from TBL_referer
WHERE Referer_time > @.Date
Group BY
CONVERT (VARCHAR(10), Referer_time,104 )
--Between
SELECT CONVERT (VARCHAR(10), Referer_time,104 ) ,Count(*)
from TBL_referer
WHERE Referer_time BETWEEN @.Date AND @.Date2
Group BY
CONVERT (VARCHAR(10), Referer_time,104 )
You see no HAVING but a WHERE clause and no convert either
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Savas Ates wrote:
> I have some Records in my table like that
> 2006-06-16 14:15:01.607
> 2006-06-16 14:17:28.187
> 2006-06-15 14:17:28.000
> 2006-06-13 14:17:28.000
> 2006-06-17 14:17:28.000
> 2006-06-19 14:17:10.000
> 2006-06-17 14:17:28.000
> I want to have count number of the records if they have same date. I dont
> care about hour,minute and second info. I care about query result based on
> daily records.
> When I Wrote
> DECLARE @.Date AS VARCHAR(50)
> DECLARE @.Date2 AS VARCHAR(50)
> SELECT @.Date='13.06.1900'
> SELECT @.Date2='13.06.2010'
>
> SELECT @.Date=CONVERT (VARCHAR(10),@.Date,104)
> SELECT @.Date2=CONVERT (VARCHAR(10),@.Date2,104)
>
> SELECt CONVERT (VARCHAR(10), Referer_time,104 ) ,Count(*)
> from TBL_referer
> Group BY
> CONVERT (VARCHAR(10), Referer_time,104 )
>
> HAVING CONVERT (VARCHAR(10), Referer_time,104 )
> It returns
> 13.06.2006 1
> 15.06.2006 1
> 16.06.2006 2
> 17.06.2006 2
> 19.06.2006 1
> it is right one..
> When I change it with that (Only Changing is BETWEEN @.Date AND @.Date2
> instead of > @.Date )
> DECLARE @.Date AS VARCHAR(50)
> DECLARE @.Date2 AS VARCHAR(50)
> SELECT @.Date='13.06.1900'
> SELECT @.Date2='13.06.2010'
>
> SELECT @.Date=CONVERT (VARCHAR(10),@.Date,104)
> SELECT @.Date2=CONVERT (VARCHAR(10),@.Date2,104)
>
> SELECt CONVERT (VARCHAR(10), Referer_time,104 ) ,Count(*)
> from TBL_referer
> Group BY
> CONVERT (VARCHAR(10), Referer_time,104 )
>
> HAVING CONVERT (VARCHAR(10), Referer_time,104 )
> BETWEEN @.Date AND @.Date2
> It olny returns
> 13.06.2006 1
> What is the mistake ?
> Could anyone help me ?|||Thank You For your helpinh But How come Sql server evaluates
That.
WHERE Referer_time BETWEEN @.Date AND @.Date2
I Have
SELECT @.Date='19000613'
SELECT @.Date2='20060618'
But my records are like that
2006-06-16 14:15:01.607
2006-06-16 14:17:28.187
2006-06-15 14:17:28.000
2006-06-13 14:17:28.000
2006-06-17 14:17:28.000
2006-06-13 14:17:10.000
2006-06-17 14:17:28.000
can you give me an article or explanation how it does it ?|||Here is all you need
http://www.karaszi.com/SQLServer/info_datetime.asp
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Savas Ates wrote:
> Thank You For your helpinh But How come Sql server evaluates
> That.
> WHERE Referer_time BETWEEN @.Date AND @.Date2
> I Have
> SELECT @.Date='19000613'
> SELECT @.Date2='20060618'
> But my records are like that
> 2006-06-16 14:15:01.607
> 2006-06-16 14:17:28.187
> 2006-06-15 14:17:28.000
> 2006-06-13 14:17:28.000
> 2006-06-17 14:17:28.000
> 2006-06-13 14:17:10.000
> 2006-06-17 14:17:28.000
> can you give me an article or explanation how it does it ?|||This should help you: http://www.karaszi.com/SQLServer/info_datetime.asp. Yo
u can post back if you
still have problems after reading the article.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Savas Ates" <in da club> wrote in message news:OE457ZdlGHA.4052@.TK2MSFTNGP05.phx.gbl...[co
lor=darkred]
>I have some Records in my table like that
> 2006-06-16 14:15:01.607
> 2006-06-16 14:17:28.187
> 2006-06-15 14:17:28.000
> 2006-06-13 14:17:28.000
> 2006-06-17 14:17:28.000
> 2006-06-19 14:17:10.000
> 2006-06-17 14:17:28.000
> I want to have count number of the records if they have same date. I dont
care about hour,minute
> and second info. I care about query result based on daily records.
> When I Wrote
> DECLARE @.Date AS VARCHAR(50)
> DECLARE @.Date2 AS VARCHAR(50)
> SELECT @.Date='13.06.1900'
> SELECT @.Date2='13.06.2010'
>
> SELECT @.Date=CONVERT (VARCHAR(10),@.Date,104)
> SELECT @.Date2=CONVERT (VARCHAR(10),@.Date2,104)
>
> SELECt CONVERT (VARCHAR(10), Referer_time,104 ) ,Count(*)
> from TBL_referer
> Group BY
> CONVERT (VARCHAR(10), Referer_time,104 )
>
> HAVING CONVERT (VARCHAR(10), Referer_time,104 )
> It returns
> 13.06.2006 1
> 15.06.2006 1
> 16.06.2006 2
> 17.06.2006 2
> 19.06.2006 1
> it is right one..
> When I change it with that (Only Changing is BETWEEN @.Date AND @.Date2 i
nstead of > @.Date )
> DECLARE @.Date AS VARCHAR(50)
> DECLARE @.Date2 AS VARCHAR(50)
> SELECT @.Date='13.06.1900'
> SELECT @.Date2='13.06.2010'
>
> SELECT @.Date=CONVERT (VARCHAR(10),@.Date,104)
> SELECT @.Date2=CONVERT (VARCHAR(10),@.Date2,104)
>
> SELECt CONVERT (VARCHAR(10), Referer_time,104 ) ,Count(*)
> from TBL_referer
> Group BY
> CONVERT (VARCHAR(10), Referer_time,104 )
>
> HAVING CONVERT (VARCHAR(10), Referer_time,104 )
> BETWEEN @.Date AND @.Date2
> It olny returns
> 13.06.2006 1
> What is the mistake ?
> Could anyone help me ?
>
>
>[/color]
between operator behavior
In sql server,teradata,oracle when you use between operator to query the
table it shows as below
Where 1 between 1 and 10 -> True
Where 1 between 10 and 1 -> False
Logically speaking the above method is wrong so I want to know why the
implementation is done like this across all databases.
Appreciate your help in understanding the myth.
Thanks
BETWEEN internally operates by expanding the expression to:
WHERE 1 >= (lower bound) AND 1 <= (upper bound)
That does not work for WHERE 1 BETWEEN 10 AND 1 ...
WHERE 1 >= 10 --FALSE
"PraveenMohan" <PraveenMohan@.discussions.microsoft.com> wrote in message
news:41C2CFE5-3A25-4678-A395-3EAAD516EAC9@.microsoft.com...
> In sql server,teradata,oracle when you use between operator to query the
> table it shows as below
> Where 1 between 1 and 10 -> True
> Where 1 between 10 and 1 -> False
> Logically speaking the above method is wrong so I want to know why the
> implementation is done like this across all databases.
> Appreciate your help in understanding the myth.
> Thanks
>
|||Because that's how it's defined in the ANSI/ISO SQL Standard.
X BETWEEN Y AND Z
is equivalent to
X>=Y AND X<=Z
therefore:
1 BETWEEN 10 AND 1
= 1>=10 AND 1<=1
= FALSE
It might seem "illogical" if you have some different intuitive model of what
"between" means but to me the only obvious alternative is to raise an error
if Y>Z. I think that would be a lot less convenient since you would
constantly have to implement bounds checking before using BETWEEN.
David Portas
SQL Server MVP
|||Just some useless info to add...:
Some earlier version of SQL Server did actually return rows even if the values are "swapped". My
guess is that 6.0 was the version that changed this as MS did a lot of ANSI SQL work on that
version. Imagine the number of bugs creeped up in sw, with 6.0... :-).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:ACDBB972-14E2-4240-8E8A-D4EABC5D13FA@.microsoft.com...
> Because that's how it's defined in the ANSI/ISO SQL Standard.
> X BETWEEN Y AND Z
> is equivalent to
> X>=Y AND X<=Z
> therefore:
> 1 BETWEEN 10 AND 1
> = 1>=10 AND 1<=1
> = FALSE
> It might seem "illogical" if you have some different intuitive model of what
> "between" means but to me the only obvious alternative is to raise an error
> if Y>Z. I think that would be a lot less convenient since you would
> constantly have to implement bounds checking before using BETWEEN.
> --
> David Portas
> SQL Server MVP
> --
>
table it shows as below
Where 1 between 1 and 10 -> True
Where 1 between 10 and 1 -> False
Logically speaking the above method is wrong so I want to know why the
implementation is done like this across all databases.
Appreciate your help in understanding the myth.
Thanks
BETWEEN internally operates by expanding the expression to:
WHERE 1 >= (lower bound) AND 1 <= (upper bound)
That does not work for WHERE 1 BETWEEN 10 AND 1 ...
WHERE 1 >= 10 --FALSE
"PraveenMohan" <PraveenMohan@.discussions.microsoft.com> wrote in message
news:41C2CFE5-3A25-4678-A395-3EAAD516EAC9@.microsoft.com...
> In sql server,teradata,oracle when you use between operator to query the
> table it shows as below
> Where 1 between 1 and 10 -> True
> Where 1 between 10 and 1 -> False
> Logically speaking the above method is wrong so I want to know why the
> implementation is done like this across all databases.
> Appreciate your help in understanding the myth.
> Thanks
>
|||Because that's how it's defined in the ANSI/ISO SQL Standard.
X BETWEEN Y AND Z
is equivalent to
X>=Y AND X<=Z
therefore:
1 BETWEEN 10 AND 1
= 1>=10 AND 1<=1
= FALSE
It might seem "illogical" if you have some different intuitive model of what
"between" means but to me the only obvious alternative is to raise an error
if Y>Z. I think that would be a lot less convenient since you would
constantly have to implement bounds checking before using BETWEEN.
David Portas
SQL Server MVP
|||Just some useless info to add...:
Some earlier version of SQL Server did actually return rows even if the values are "swapped". My
guess is that 6.0 was the version that changed this as MS did a lot of ANSI SQL work on that
version. Imagine the number of bugs creeped up in sw, with 6.0... :-).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:ACDBB972-14E2-4240-8E8A-D4EABC5D13FA@.microsoft.com...
> Because that's how it's defined in the ANSI/ISO SQL Standard.
> X BETWEEN Y AND Z
> is equivalent to
> X>=Y AND X<=Z
> therefore:
> 1 BETWEEN 10 AND 1
> = 1>=10 AND 1<=1
> = FALSE
> It might seem "illogical" if you have some different intuitive model of what
> "between" means but to me the only obvious alternative is to raise an error
> if Y>Z. I think that would be a lot less convenient since you would
> constantly have to implement bounds checking before using BETWEEN.
> --
> David Portas
> SQL Server MVP
> --
>
between operator
Hi,
I'm having problems using the between operator in a query statement.
Select *
from <mytable>
where date between @.date1 and @.date2
The date values with a hour specified, aren't returned. What is the approach you would recommend here?
Thx
EDIT: by playing with this problem I've figured out I can append the hour to the date like this: <date> between @.fromDate + '00:00:00' and @.toDate + '23:59:59'
this seem to work, but I'm not sure if this is correct
Select *
from <mytable>
where date >= @.date1 and date < dateadd(d,1,@.date2)
or
Select *
from <mytable>
where date >= @.date1 and date < @.date2 +1
Denis the SQL Menace
http://sqlservercode.blogspot.com/
|||i had a similar problem which i did through the application side:
string weekdate =txtFrom.Text;
string daydate = System.Convert.ToDateTime(txtTo.Text).Add(System.TimeSpan.FromDays(1)).ToString();
and later...
TechnicianViewInboxDataAdapter.SelectCommand.CommandText = tempselecttechnician + " where status = 1 and TechnicianID = " + Session["UserID"].ToString() + "and CallLoggedDT Between '" + weekdate + "' and '" + daydate + "' or TechnicianID = 1 and DepartmentID = 1 and status = 1 and CallLoggedDT Between '" + weekdate + "' and '" + daydate + "' or TechnicianID = 1 and status = 1 and CallLoggedDT Between '" + weekdate + "' and '" + daydate + "' and DepartmentID = " + Session["DepartmentID"].ToString();
this works as well.
the code ends up like this:
SELECT FirstName, LastName, Email, CallNumber, DepartmentName, UserName, Status, TechnicianID, CallLoggedDT, DepartmentID, CallOpenedDT, CallActionedDT, CallClosedDT FROM dbo.TechnicianInboxView where status = 1 and TechnicianID = 2and CallLoggedDT Between '2006/05/30' and '2006/06/07 12:00:00 AM' or TechnicianID = 1 and DepartmentID = 1 and status = 1 and CallLoggedDT Between '2006/05/30' and '2006/06/07 12:00:00 AM' or TechnicianID = 1 and status = 1 and CallLoggedDT Between '2006/05/30' and '2006/06/07 12:00:00 AM' and DepartmentID = 2
|||
You can also use the datediff function and the statement would look like this.
Select * from <mytable> where
datediff(dd,@.date1,<Datefield in the table>)>=0 and datediff(dd,@.date2,<Datefield in the table>)<=0
@.Date can be varchar too and it can be in the format of "mm/dd/yy" also. Need not have the time factor.
|||
where datediff(dd,@.date1,<Datefield in the table>)>=0 and datediff(dd,@.date2,<Datefield in the table>)<=0
Would not recommend doing so in the terms of performace. You will never get index seek over the index for datefield column: it will always index scan at the best.
Between Dates Query - HELP!
Hi,
I have an SQl query that will list the results if it lies between two dates, for example;
SELECT * FROM TABLE WHERE { fn Now() } BETWEEN Date1 AND Date2
This returns all results where Date1 and Date2 fall between the Current Date
What i am looking for is a way to replace the { fn Now() } with a date of my choice.
For example;
SELECT * FROM TABLE WHERE '10/10/2006' BETWEEN Date1 AND Date2
However this does not work.
Has anyone any ideas why this may be ??
Thanks in advance
Andrew Vint
try to use an sql server variable and assign it with the date u want and then put it in ur select statement
|||
Hi,
I am afraid that does not work either.
Thanks
|||You could use a variable in the SQL query. If it didnt work for you please post the code you have.
|||Are you sure you want to put a constant on the left while 2 fields on the right? Then you have to make sure Date2 is greater than Date1, otherwise the expression will return false no matter what's the constant. Another thing you need to pay attention to is the DATEFORMAT option of current user (which can be checked using DBCC USEROPTIONS), you can useSET DATEFORMAT command to change the current date format, or always use the ODBC date format yyyy-mm-dd.|||Hi,
Thanks for the response.
I have tried using the yyyy-mm-dd format without sucess i am afraid.
Any more ideas anyone ?
Andrew
|||I was able to run your query without a problem on SQL 2005. The query below also runs.
SELECT * FROM myTable
WHERE cast('9/1/2006' as datetime) BETWEEN Date1 AND Date2
What are the data types for your Date1 and Date2 fields? I assume they are either datetime or smalldatetime, right? Could the problem be that they are bigint fields which store ticks instead of the actual datetime? Or are they char/varchar fields that are storing the date as a string?
Can you describe the problem you have? Do you get an error message? Do you get zero results or results that seemingly should not be returned?
Hi All,
Thank you for your assitance in this but after hours of staring at the same lines of query strings it has become clear that it was staring me in the face all along.
I had a date convert on the date2, because of this it was not working. As soon as i removed the convert it worked as i had expected it too.
Now before you all deservedly laugh at me please look at what i had to work with below;
strSQLHoliday = "SELECT TBL_Holidays.AgentRef, TBL_Holidays.EmpVacatID, " _
& "TBL_Agents.LastName + ', ' + TBL_Agents.FirstName AS FullName, " _
& "TBL_Teams.TeamName, CONVERT(varchar(12), TBL_Holidays.ActivityDate, 106) " _
& "AS ActDate, CONVERT(varchar(12), (DATEADD(d, CASE DATEPART(dw, ActivityDate) " _
& "WHEN 7 THEN 2 WHEN 1 THEN 1 ELSE 0 END, TBL_Holidays.ActivityDate) + " _
& "(DATEPART(dw, DATEADD(d, CASE DATEPART(dw, ActivityDate) WHEN 7 THEN " _
& "2 WHEN 1 THEN 1 ELSE 0 END, TBL_Holidays.ActivityDate)) - 2 + " _
& "CAST(TBL_Holidays.TimeTaken / (TBL_Agents.ContractHrs / 5) AS Int)) % 5 " _
& "+ (DATEPART(dw, DATEADD(d, CASE DATEPART(dw, ActivityDate) WHEN 7 THEN " _
& "2 WHEN 1 THEN 1 ELSE 0 END, TBL_Holidays.ActivityDate)) - 2 + " _
& "CAST(TBL_Holidays.TimeTaken / (TBL_Agents.ContractHrs / 5) AS Int)) " _
& "/ 5 * 7) - (DATEPART(dw, DATEADD(d, CASE DATEPART(dw, ActivityDate) " _
& "WHEN 7 THEN 2 WHEN 1 THEN 1 ELSE 0 END, TBL_Holidays.ActivityDate)) " _
& "- 2), 106) AS ReturnDate, TBL_Agents.ContractHrs / 5 AS DailyHrs, " _
& "TBL_Holidays.TimeTaken, TBL_Holidays.TimeTaken / (TBL_Agents.ContractHrs " _
& "/ 5) AS DaysTaken, TBL_Holidays.Status, TBL_Holidays.TheNote FROM " _
& "TBL_Holidays INNER JOIN TBL_Agents ON TBL_Holidays.AgentRef = " _
& "TBL_Agents.AgentRef INNER JOIN TBL_TeamsTracker ON TBL_Agents.AgentRef = " _
& "TBL_TeamsTracker.AgentRef INNER JOIN TBL_Teams ON TBL_TeamsTracker.TeamRef " _
& "= TBL_Teams.TeamRef WHERE (TBL_TeamsTracker.EndDate IS NULL) AND " _
& "('" & ChosenDate & "' BETWEEN TBL_Holidays.ActivityDate AND (DATEADD(d, CASE " _
& "DATEPART(dw, ActivityDate) WHEN 7 THEN 2 WHEN 1 THEN 1 ELSE 0 END, " _
& "TBL_Holidays.ActivityDate) + (DATEPART(dw, DATEADD(d, CASE DATEPART(dw, " _
& "ActivityDate) WHEN 7 THEN 2 WHEN 1 THEN 1 ELSE 0 END, " _
& "TBL_Holidays.ActivityDate)) - 2 + CAST(TBL_Holidays.TimeTaken / " _
& "(TBL_Agents.ContractHrs / 5) AS Int)) % 5 + (DATEPART(dw, DATEADD(d, " _
& "CASE DATEPART(dw, ActivityDate) WHEN 7 THEN 2 WHEN 1 THEN 1 ELSE 0 END, " _
& "TBL_Holidays.ActivityDate)) - 2 + CAST(TBL_Holidays.TimeTaken / " _
& "(TBL_Agents.ContractHrs / 5) AS Int)) / 5 * 7) - (DATEPART(dw, " _
& "DATEADD(d, CASE DATEPART(dw, ActivityDate) WHEN 7 THEN 2 WHEN 1 THEN 1 " _
& "ELSE 0 END, TBL_Holidays.ActivityDate)) - 2)) AND " _
& "(TBL_Agents.Location = N'" & lblLocation.Text & "') " _
& "ORDER BY TBL_Holidays.ActivityDate DESC, TBL_Agents.LastName, " _
& "TBL_Holidays.Status"
I thank you all for taking the time to assist me with this matter, your comments did make me look more closely and as a result stumble across the solution.
I have now come to the conlcusion that 10 cups of coffee are no substitute for a good nights sleep when trying to code stuff :)
Thanks Again
Andrew
Between date selection
Hi,
I have the following situation.
I want to create reports with a start date parameter and an end date parameter
In my query i use "where date between @.startdate and @.enddate"
i configure my parameters so i get a nice calendar when i generate the report.
the problem is when i select for example a date starting 1 april 2007 and enddate 10 april 2007,
the result in my report gives me only the data from 1 until 9 april and not until 10.
in my database the date is stored as a date time (10/04/2007 17:25:30).
Any suggestion how i can solve my problem?
Greetings
Vinnie
Hello Vinnie,
The problem is that your database stores the time with your date and when you select a date in the date picker, it doesn't have a tme, so anything after midnight gets excluded. Try this as your where clause:
where date >= @.startdate and date < dateadd(d, 1, @.enddate)
Hope this helps.
Jarret
|||Hi Vinnie,
When I come across this I normally use a conversion in my query:
where convert(varchar(25),date,101) between @.startdate and @.enddate
The conversion eliminates the time from the database field and will pull back all rows with the date portion between the start and end dates.
Simone
|||Do you have a date picker control on your reporting website, so that instead of the user having to manually input the date in the correct format, they are given a control to pick the date ranges?
(Like datetimepicker control in C#)
Please let me know as that is what I need to do.
|||Yes I do. You need to change the data type of the parameter in the Report to datetime. Go to "Report" then "Report Parameters" Select the parameter from the list on the left and change the data type to DateTime. This should automatically give the date control.
Simone
Between Date Query
How would I make a query that checks for all dates using a feature that
automatically selects todays date and then 14 days in the future. Meaning
all records between today and 14 days in the future.
Thanks.The SQL should read something like:
SELECT *
FROM MyTable
WHERE MyDateField BETWEEN GETDATE() AND DATEADD(Day, 14, GETDATE())
HTH,
Magendo_man
"Samson" wrote:
> How would I make a query that checks for all dates using a feature that
> automatically selects todays date and then 14 days in the future. Meaning
> all records between today and 14 days in the future.
> Thanks.|||thank you. it worked
"magendo_man" wrote:
> The SQL should read something like:
> SELECT *
> FROM MyTable
> WHERE MyDateField BETWEEN GETDATE() AND DATEADD(Day, 14, GETDATE())
> HTH,
> Magendo_man
> "Samson" wrote:
> > How would I make a query that checks for all dates using a feature that
> > automatically selects todays date and then 14 days in the future. Meaning
> > all records between today and 14 days in the future.
> >
> > Thanks.
automatically selects todays date and then 14 days in the future. Meaning
all records between today and 14 days in the future.
Thanks.The SQL should read something like:
SELECT *
FROM MyTable
WHERE MyDateField BETWEEN GETDATE() AND DATEADD(Day, 14, GETDATE())
HTH,
Magendo_man
"Samson" wrote:
> How would I make a query that checks for all dates using a feature that
> automatically selects todays date and then 14 days in the future. Meaning
> all records between today and 14 days in the future.
> Thanks.|||thank you. it worked
"magendo_man" wrote:
> The SQL should read something like:
> SELECT *
> FROM MyTable
> WHERE MyDateField BETWEEN GETDATE() AND DATEADD(Day, 14, GETDATE())
> HTH,
> Magendo_man
> "Samson" wrote:
> > How would I make a query that checks for all dates using a feature that
> > automatically selects todays date and then 14 days in the future. Meaning
> > all records between today and 14 days in the future.
> >
> > Thanks.
Saturday, February 25, 2012
Better way to do this query
Is there a better way to do this query? I was hoping to do it with a CASE
and IF...
SELECT TOP 100 PERCENT CustomerNumber, InvoiceNumber, InvoiceDate, DocType,
AgingBucket,
CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
*
CASE
WHEN AgingBucket = 1 THEN
Amount
ELSE 0
END AS 'IsCurrent',
CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
*
CASE AgingBucket
WHEN 2 THEN Amount
ELSE 0
END AS '30 to 60',
CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
*
CASE AgingBucket
WHEN 3 THEN Amount
ELSE 0
END AS '60 to 90',
CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
*
CASE AgingBucket
WHEN 4 THEN Amount
ELSE 0
END AS '90 to 120',
CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
*
CASE AgingBucket
WHEN 5 THEN CURTRXAM
ELSE 0
END AS '120 to 150',
CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
*
CASE AgingBucket
WHEN 6 THEN Amount
ELSE 0
END AS '150 to 180',
CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
*
CASE
WHEN AgingBucket = 7 THEN Amount
ELSE 0
END AS 'Over 180'
FROM OpenInvoicesWhy are you using SELECT TOP 100 Percent in this query? You don't need to
specify that when you want all results returned.
Using a "WHEN" is similar to using an "IF" statement. You can specify
several when's in the same column case statement to cover various scenarios,
much like using an IF statement for several scenarios.
Are you trying to create an aggrigated report for your buckets, or do you
want a detailed list of each customer and their respective buckets?
You can sum, or use any other aggrigate function on these case columns to
return a report type query, but I'm still
by the "top 100 percent"
statement.
"JP" <jperlowin@.firstfleet.com> wrote in message
news:OJwKYMcHFHA.1860@.TK2MSFTNGP15.phx.gbl...
> Is there a better way to do this query? I was hoping to do it with a CASE
> and IF...
> SELECT TOP 100 PERCENT CustomerNumber, InvoiceNumber, InvoiceDate,
DocType,
> AgingBucket,
> CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
> *
> CASE
> WHEN AgingBucket = 1 THEN
> Amount
> ELSE 0
> END AS 'IsCurrent',
> CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
> *
> CASE AgingBucket
> WHEN 2 THEN Amount
> ELSE 0
> END AS '30 to 60',
> CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
> *
> CASE AgingBucket
> WHEN 3 THEN Amount
> ELSE 0
> END AS '60 to 90',
> CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
> *
> CASE AgingBucket
> WHEN 4 THEN Amount
> ELSE 0
> END AS '90 to 120',
> CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
> *
> CASE AgingBucket
> WHEN 5 THEN CURTRXAM
> ELSE 0
> END AS '120 to 150',
> CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
> *
> CASE AgingBucket
> WHEN 6 THEN Amount
> ELSE 0
> END AS '150 to 180',
> CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
> *
> CASE
> WHEN AgingBucket = 7 THEN Amount
> ELSE 0
> END AS 'Over 180'
> FROM OpenInvoices
>|||I'm using the TOP because query analyzer told me to, when I tried to create
a view based on the query. Originally I didn't use it.
I'm trying to get document level detail from this.
I have 7 CASE statements since I need 7 columns. Is there a way I can pivot
the data?
"SW" <simon.worth@.gmail.com> wrote in message
news:uKbt6acHFHA.2752@.TK2MSFTNGP12.phx.gbl...
> Why are you using SELECT TOP 100 Percent in this query? You don't need to
> specify that when you want all results returned.
> Using a "WHEN" is similar to using an "IF" statement. You can specify
> several when's in the same column case statement to cover various
scenarios,
> much like using an IF statement for several scenarios.
> Are you trying to create an aggrigated report for your buckets, or do you
> want a detailed list of each customer and their respective buckets?
> You can sum, or use any other aggrigate function on these case columns to
> return a report type query, but I'm still by the "top 100
percent"
> statement.
> "JP" <jperlowin@.firstfleet.com> wrote in message
> news:OJwKYMcHFHA.1860@.TK2MSFTNGP15.phx.gbl...
CASE
> DocType,
>|||Oh OK, I understand then, if you're doing it in a view with an order by,
then you need the top in there. That makes sense.
To pivot the results you can use the GROUP BY with CUBE and GROUP BY with
ROLLUP functions, but the data may not be displayed exactly as you want it.
There is a document on Books Online that goes into detail as well on
crosstab queries, but you would probably have to run it from a stored
procedure instead in order to accommodate the temp tables required. Here's
the link
mk:@.MSITStore:C:\Program%20Files\Microso
ft%20SQL%20Server\80\Tools\Books\acd
ata.chm::/ac_8_qd_14_04j7.htm
or here on MSDN
http://msdn.microsoft.com/library/d.../>
sz_0kro.asp
"JP" <jperlowin@.firstfleet.com> wrote in message
news:OsdDxtcHFHA.3648@.TK2MSFTNGP09.phx.gbl...
> I'm using the TOP because query analyzer told me to, when I tried to
create
> a view based on the query. Originally I didn't use it.
> I'm trying to get document level detail from this.
> I have 7 CASE statements since I need 7 columns. Is there a way I can
pivot
> the data?
> "SW" <simon.worth@.gmail.com> wrote in message
> news:uKbt6acHFHA.2752@.TK2MSFTNGP12.phx.gbl...
to
> scenarios,
you
to
> percent"
> CASE
>
and IF...
SELECT TOP 100 PERCENT CustomerNumber, InvoiceNumber, InvoiceDate, DocType,
AgingBucket,
CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
*
CASE
WHEN AgingBucket = 1 THEN
Amount
ELSE 0
END AS 'IsCurrent',
CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
*
CASE AgingBucket
WHEN 2 THEN Amount
ELSE 0
END AS '30 to 60',
CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
*
CASE AgingBucket
WHEN 3 THEN Amount
ELSE 0
END AS '60 to 90',
CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
*
CASE AgingBucket
WHEN 4 THEN Amount
ELSE 0
END AS '90 to 120',
CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
*
CASE AgingBucket
WHEN 5 THEN CURTRXAM
ELSE 0
END AS '120 to 150',
CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
*
CASE AgingBucket
WHEN 6 THEN Amount
ELSE 0
END AS '150 to 180',
CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
*
CASE
WHEN AgingBucket = 7 THEN Amount
ELSE 0
END AS 'Over 180'
FROM OpenInvoicesWhy are you using SELECT TOP 100 Percent in this query? You don't need to
specify that when you want all results returned.
Using a "WHEN" is similar to using an "IF" statement. You can specify
several when's in the same column case statement to cover various scenarios,
much like using an IF statement for several scenarios.
Are you trying to create an aggrigated report for your buckets, or do you
want a detailed list of each customer and their respective buckets?
You can sum, or use any other aggrigate function on these case columns to
return a report type query, but I'm still
by the "top 100 percent"statement.
"JP" <jperlowin@.firstfleet.com> wrote in message
news:OJwKYMcHFHA.1860@.TK2MSFTNGP15.phx.gbl...
> Is there a better way to do this query? I was hoping to do it with a CASE
> and IF...
> SELECT TOP 100 PERCENT CustomerNumber, InvoiceNumber, InvoiceDate,
DocType,
> AgingBucket,
> CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
> *
> CASE
> WHEN AgingBucket = 1 THEN
> Amount
> ELSE 0
> END AS 'IsCurrent',
> CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
> *
> CASE AgingBucket
> WHEN 2 THEN Amount
> ELSE 0
> END AS '30 to 60',
> CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
> *
> CASE AgingBucket
> WHEN 3 THEN Amount
> ELSE 0
> END AS '60 to 90',
> CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
> *
> CASE AgingBucket
> WHEN 4 THEN Amount
> ELSE 0
> END AS '90 to 120',
> CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
> *
> CASE AgingBucket
> WHEN 5 THEN CURTRXAM
> ELSE 0
> END AS '120 to 150',
> CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
> *
> CASE AgingBucket
> WHEN 6 THEN Amount
> ELSE 0
> END AS '150 to 180',
> CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
> *
> CASE
> WHEN AgingBucket = 7 THEN Amount
> ELSE 0
> END AS 'Over 180'
> FROM OpenInvoices
>|||I'm using the TOP because query analyzer told me to, when I tried to create
a view based on the query. Originally I didn't use it.
I'm trying to get document level detail from this.
I have 7 CASE statements since I need 7 columns. Is there a way I can pivot
the data?
"SW" <simon.worth@.gmail.com> wrote in message
news:uKbt6acHFHA.2752@.TK2MSFTNGP12.phx.gbl...
> Why are you using SELECT TOP 100 Percent in this query? You don't need to
> specify that when you want all results returned.
> Using a "WHEN" is similar to using an "IF" statement. You can specify
> several when's in the same column case statement to cover various
scenarios,
> much like using an IF statement for several scenarios.
> Are you trying to create an aggrigated report for your buckets, or do you
> want a detailed list of each customer and their respective buckets?
> You can sum, or use any other aggrigate function on these case columns to
> return a report type query, but I'm still
by the "top 100percent"
> statement.
> "JP" <jperlowin@.firstfleet.com> wrote in message
> news:OJwKYMcHFHA.1860@.TK2MSFTNGP15.phx.gbl...
CASE
> DocType,
>|||Oh OK, I understand then, if you're doing it in a view with an order by,
then you need the top in there. That makes sense.
To pivot the results you can use the GROUP BY with CUBE and GROUP BY with
ROLLUP functions, but the data may not be displayed exactly as you want it.
There is a document on Books Online that goes into detail as well on
crosstab queries, but you would probably have to run it from a stored
procedure instead in order to accommodate the temp tables required. Here's
the link
mk:@.MSITStore:C:\Program%20Files\Microso
ft%20SQL%20Server\80\Tools\Books\acd
ata.chm::/ac_8_qd_14_04j7.htm
or here on MSDN
http://msdn.microsoft.com/library/d.../>
sz_0kro.asp
"JP" <jperlowin@.firstfleet.com> wrote in message
news:OsdDxtcHFHA.3648@.TK2MSFTNGP09.phx.gbl...
> I'm using the TOP because query analyzer told me to, when I tried to
create
> a view based on the query. Originally I didn't use it.
> I'm trying to get document level detail from this.
> I have 7 CASE statements since I need 7 columns. Is there a way I can
pivot
> the data?
> "SW" <simon.worth@.gmail.com> wrote in message
> news:uKbt6acHFHA.2752@.TK2MSFTNGP12.phx.gbl...
to
> scenarios,
you
to
> percent"
> CASE
>
Better query?
I have a table from a 3rd party application that stores the time that
events occurred as the number of seconds since Jan 1 1970. I want to
perform an aggregate function on the values stored in each event, grouped
by date and present the sorted results. Here's what I have:
SELECT
DateAdd(dd, Time/86400, '1/1/1970 12:00:00 AM') AS PointDate,
Avg(Value)
FROM Data
GROUP BY DateAdd(dd, Time/86400, '1/1/1970 12:00:00 AM')
ORDER BY DateAdd(dd, Time/86400, '1/1/1970 12:00:00 AM') ASC
Is there an easier way to write this without duplicating the DateAdd
calculation three times' I can't use Stored Procedures (I'm not allowed
to modify the database in any way.)
-mdb>> have a table from a 3rd party application that stores the time that even
ts occurred as the number of seconds since 1970-01-01 <<
Oh yeah, that makes sense :) !! I always hated the term "legacy data"
when what we meant was "family curse from Hell!" instead.
Build a table like this:
CREATE TABLE FoobarDates
(foobar_date CHAR (10) NOT NULL,
start_second INTEGER NOT NULL,
finish_second INTEGER NOT NULL
PRIMARY KEY(start_second, finish_second ));
1) Avoid math in SQL-- it is designed to do JOINs. not algebra.
2) An ORDER BY on a SELECT is **meaningless** -- read any basic SQL
book. Indexes matter.
3) Use a spreadsheet to do the computations for the table. Easy and
faster than code.|||you can use a derived table Michael...
SELECT PointDate, Avg_Value = Avg( Value )
FROM (
SELECT PointDate, DateAdd(dd, Time/86400, '1/1/1970 12:00:00 AM'),
Value
FROM Data ) AS dt
GROUP BY PointDate
ORDER BY PointDate ASC
Tony
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Michael Bray" <mbray@.makeDIntoDot_ctiusaDcom> wrote in message
news:Xns9739BF2454556mbrayctiusacom@.207.46.248.16...
>I have a table from a 3rd party application that stores the time that
> events occurred as the number of seconds since Jan 1 1970. I want to
> perform an aggregate function on the values stored in each event, grouped
> by date and present the sorted results. Here's what I have:
> SELECT
> DateAdd(dd, Time/86400, '1/1/1970 12:00:00 AM') AS PointDate,
> Avg(Value)
> FROM Data
> GROUP BY DateAdd(dd, Time/86400, '1/1/1970 12:00:00 AM')
> ORDER BY DateAdd(dd, Time/86400, '1/1/1970 12:00:00 AM') ASC
> Is there an easier way to write this without duplicating the DateAdd
> calculation three times' I can't use Stored Procedures (I'm not allowed
> to modify the database in any way.)
> -mdb|||Sorry, that should be...
SELECT PointDate, Avg_Value = Avg( Value )
FROM (
SELECT PointDate = DateAdd(dd, Time/86400, '1/1/1970 12:00:00 AM'),
Value
FROM Data ) AS dt
GROUP BY PointDate
ORDER BY PointDate ASC
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:eaoeBd4CGHA.532@.TK2MSFTNGP15.phx.gbl...
> you can use a derived table Michael...
> SELECT PointDate, Avg_Value = Avg( Value )
> FROM (
> SELECT PointDate, DateAdd(dd, Time/86400, '1/1/1970 12:00:00 AM'),
> Value
> FROM Data ) AS dt
> GROUP BY PointDate
> ORDER BY PointDate ASC
> Tony
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Michael Bray" <mbray@.makeDIntoDot_ctiusaDcom> wrote in message
> news:Xns9739BF2454556mbrayctiusacom@.207.46.248.16...
>|||"--CELKO--" <jcelko212@.earthlink.net> wrote in
news:1135732983.997588.94360@.o13g2000cwo.googlegroups.com:
> Oh yeah, that makes sense :) !! I always hated the term "legacy data"
> when what we meant was "family curse from Hell!" instead.
As a matter of fact, this is NOT legacy Data. In fact, it is an
application that would not have existed 10 years ago, because the
technology that is represented by the data in the database didn't exist.
(I'm not going to mention the vendor, but it is an IP Telephony monitoring
system.)
The rest of your post sounds like a troll, so I'm not going to comment,
except for this comment.
-mdb|||"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in
news:eaoeBd4CGHA.532@.TK2MSFTNGP15.phx.gbl:
> you can use a derived table Michael...
>
Wow... you know I had always tried to do something similar, but I never
could get it to work because I didn't know that I had to include the 'AS
<alias>'. Thanks! Now I get to go back and re-write a bunch of stored
procedures in other databases I've worked on that I used memory tables in!
:)
-mdb
events occurred as the number of seconds since Jan 1 1970. I want to
perform an aggregate function on the values stored in each event, grouped
by date and present the sorted results. Here's what I have:
SELECT
DateAdd(dd, Time/86400, '1/1/1970 12:00:00 AM') AS PointDate,
Avg(Value)
FROM Data
GROUP BY DateAdd(dd, Time/86400, '1/1/1970 12:00:00 AM')
ORDER BY DateAdd(dd, Time/86400, '1/1/1970 12:00:00 AM') ASC
Is there an easier way to write this without duplicating the DateAdd
calculation three times' I can't use Stored Procedures (I'm not allowed
to modify the database in any way.)
-mdb>> have a table from a 3rd party application that stores the time that even
ts occurred as the number of seconds since 1970-01-01 <<
Oh yeah, that makes sense :) !! I always hated the term "legacy data"
when what we meant was "family curse from Hell!" instead.
Build a table like this:
CREATE TABLE FoobarDates
(foobar_date CHAR (10) NOT NULL,
start_second INTEGER NOT NULL,
finish_second INTEGER NOT NULL
PRIMARY KEY(start_second, finish_second ));
1) Avoid math in SQL-- it is designed to do JOINs. not algebra.
2) An ORDER BY on a SELECT is **meaningless** -- read any basic SQL
book. Indexes matter.
3) Use a spreadsheet to do the computations for the table. Easy and
faster than code.|||you can use a derived table Michael...
SELECT PointDate, Avg_Value = Avg( Value )
FROM (
SELECT PointDate, DateAdd(dd, Time/86400, '1/1/1970 12:00:00 AM'),
Value
FROM Data ) AS dt
GROUP BY PointDate
ORDER BY PointDate ASC
Tony
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Michael Bray" <mbray@.makeDIntoDot_ctiusaDcom> wrote in message
news:Xns9739BF2454556mbrayctiusacom@.207.46.248.16...
>I have a table from a 3rd party application that stores the time that
> events occurred as the number of seconds since Jan 1 1970. I want to
> perform an aggregate function on the values stored in each event, grouped
> by date and present the sorted results. Here's what I have:
> SELECT
> DateAdd(dd, Time/86400, '1/1/1970 12:00:00 AM') AS PointDate,
> Avg(Value)
> FROM Data
> GROUP BY DateAdd(dd, Time/86400, '1/1/1970 12:00:00 AM')
> ORDER BY DateAdd(dd, Time/86400, '1/1/1970 12:00:00 AM') ASC
> Is there an easier way to write this without duplicating the DateAdd
> calculation three times' I can't use Stored Procedures (I'm not allowed
> to modify the database in any way.)
> -mdb|||Sorry, that should be...
SELECT PointDate, Avg_Value = Avg( Value )
FROM (
SELECT PointDate = DateAdd(dd, Time/86400, '1/1/1970 12:00:00 AM'),
Value
FROM Data ) AS dt
GROUP BY PointDate
ORDER BY PointDate ASC
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:eaoeBd4CGHA.532@.TK2MSFTNGP15.phx.gbl...
> you can use a derived table Michael...
> SELECT PointDate, Avg_Value = Avg( Value )
> FROM (
> SELECT PointDate, DateAdd(dd, Time/86400, '1/1/1970 12:00:00 AM'),
> Value
> FROM Data ) AS dt
> GROUP BY PointDate
> ORDER BY PointDate ASC
> Tony
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Michael Bray" <mbray@.makeDIntoDot_ctiusaDcom> wrote in message
> news:Xns9739BF2454556mbrayctiusacom@.207.46.248.16...
>|||"--CELKO--" <jcelko212@.earthlink.net> wrote in
news:1135732983.997588.94360@.o13g2000cwo.googlegroups.com:
> Oh yeah, that makes sense :) !! I always hated the term "legacy data"
> when what we meant was "family curse from Hell!" instead.
As a matter of fact, this is NOT legacy Data. In fact, it is an
application that would not have existed 10 years ago, because the
technology that is represented by the data in the database didn't exist.
(I'm not going to mention the vendor, but it is an IP Telephony monitoring
system.)
The rest of your post sounds like a troll, so I'm not going to comment,
except for this comment.
-mdb|||"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in
news:eaoeBd4CGHA.532@.TK2MSFTNGP15.phx.gbl:
> you can use a derived table Michael...
>
Wow... you know I had always tried to do something similar, but I never
could get it to work because I didn't know that I had to include the 'AS
<alias>'. Thanks! Now I get to go back and re-write a bunch of stored
procedures in other databases I've worked on that I used memory tables in!
:)
-mdb
Better performance without an index?
I have a small table (600 rows) that is used in a query with a relatively large table (200,000 rows). Strangely, I get the best performance from the query by removing all indexes from the small table (including the primary key). This seems a little odd to me, and I'm wondering if this is a common scenario, and if there is some general rule that can be applied when indexing small tables.
I have tried changing the primary key index (clustered vs nonclustered) and adding other indexes to both tables, but I always get the best performance by removing all indexes from the small table. The performance difference is significant, execution time goes from 2 seconds to just over 1 second, and this is a query that gets executed quite a bit.
I can't delete the PK index as the table needs a PK for data integrity reasons. So I'm not sure how to achieve the non-index performance without removing the index.
Any suggestions would be much appreciated. By the way it's SQL Server 2005 Express.
Without indexes, you will always do a table scan. However, with such a small table, a table scan is probably much better than hopping through the index tree to get data. Though, I do not see how a clustered index would be a bad thing here. Can you show us some code (ddl for both table, including all keys/constraints)?
|||The small table:
create table EmployeeOffice (
EmployeeID uniqueidentifier not null,
OfficeID uniqueidentifier not null,
Sequence smallint not null,
CanAccessAllCases bit default 0 not null,
IsManager bit default 0 not null)
go
alter table EmployeeOffice
add constraint EmployeeOffice_PK primary key nonclustered (EmployeeID, OfficeID)
The large table:
create table CaseInfo (
CaseNumber int not null,
OfficeID uniqueidentifier not null,
EmployeeID uniqueidentifier null,
TeamID uniqueidentifier null,
... - there's about another 20 columns
InvoiceRequired" bit default 0 not null,
LastChanged" timestamp not null)
go
alter table CaseInfo
add constraint CaseInfo_PK primary key clustered (CaseNumber)
There's an Employee and an Office table as well with RI contraints between the four tables but they are not used in this particular query. The query is fairly large and involves other tables but this particular behaviour is specific to these two tables.
The query (simplified):
SELECT CaseNumber, InvoiceRequired,...
FROM CaseInfo
WHERE EmployeeID = 'BCA6F76E-A2EF-4FDA-AB38-061047ADBFDE'
or OfficeID in (Select OfficeID From EmployeeOffice Where EmployeeID = 'BCA6F76E-A2EF-4FDA-AB38-061047ADBFDE'
and (CanAccessAllCases=1 or IsManager=1))
In the test database ALL the records in the CaseInfo table have the same office, and the sub query returns no rows. The execution plan shows that when the index exists, it creates an "Index Spool" with 131,992 rows (which is the number of rows in CaseInfo where EmployeeID does not match. If I use a table hint to ignore the index on this table (or delete it) the "Index Spool" is not created.
|||
A few things:
1. I would rewrite the query to use join instead of subquery.
e.g.
Code Snippet
SELECT CaseNumber, InvoiceRequired
FROM CaseInfo ci JOIN EmployeeOffice eo ON ci.EmployeeID=eo.EmployeeID
WHERE ci.EmployeeID = 'BCA6F76E-A2EF-4FDA-AB38-061047ADBFDE'
union all
SELECT CaseNumber, InvoiceRequired
FROM CaseInfo ci JOIN EmployeeOffice eo ON ci.OfficeID=eo.OfficeID
WHERE eo.EmployeeID = 'BCA6F76E-A2EF-4FDA-AB38-061047ADBFDE'
and (eo.CanAccessAllCases|eo.IsManager=1)
2. Create an index on CaseInfo(EmployeeID,OfficeID)
3. Index spool is used to cache the data to allow faster lookup. This is not neccessarily a bad thing.
better performace with query
its k i got it :)Remove RTrim because sql server does it automatically and you should repleace NOT IN with NOT EXISTS and also replace the subquery to corelated query.
These changes will enhance query performace.
Riaz
I am comparing 2 tables and trying to retrieve data on any amendments made to the qty in one table and not in the other:
SELECT TRANSACTIONS.order_no, product, whse, unit, Sum(qty)
from TRANSACTIONS
INNER JOIN ORDERS on
ORDERS.order_no = TRANSACTIONS.order_no
where status = '6'
group by product, whse, TRANSACTIONS.order_no, unit,date_required
having
RTrim(TRANSACTIONS.order_no)+RTrim(product)+RTrim( whse)+RTrim(unit)+CAST(Sum(qty)AS varchar) not in
(select RTrim(d.order_no)+RTrim(product)+RTrim(d.warehouse )+RTrim(dbo.toProperCase(unit_of_sale))+CAST(Sum(a llocated_qty) AS varchar)
from det d
INNER JOIN head h on
h.order_no = d.order_no
where h.status = '6'
group by product, d.warehouse, d.order_no, d.unit_of_sale)
this SEEMS 2 wrk, but is there a quicker and better way of doin the above query?|||thanks :) I didnt they made a difference! :)|||Please do not edit out your original posts!
These changes will enhance query performace.
Riaz
I am comparing 2 tables and trying to retrieve data on any amendments made to the qty in one table and not in the other:
SELECT TRANSACTIONS.order_no, product, whse, unit, Sum(qty)
from TRANSACTIONS
INNER JOIN ORDERS on
ORDERS.order_no = TRANSACTIONS.order_no
where status = '6'
group by product, whse, TRANSACTIONS.order_no, unit,date_required
having
RTrim(TRANSACTIONS.order_no)+RTrim(product)+RTrim( whse)+RTrim(unit)+CAST(Sum(qty)AS varchar) not in
(select RTrim(d.order_no)+RTrim(product)+RTrim(d.warehouse )+RTrim(dbo.toProperCase(unit_of_sale))+CAST(Sum(a llocated_qty) AS varchar)
from det d
INNER JOIN head h on
h.order_no = d.order_no
where h.status = '6'
group by product, d.warehouse, d.order_no, d.unit_of_sale)
this SEEMS 2 wrk, but is there a quicker and better way of doin the above query?|||thanks :) I didnt they made a difference! :)|||Please do not edit out your original posts!
Subscribe to:
Posts (Atom)