Add days to all datetime columns in the database

Hi,
I got a request from our Sr. Director regarding demo data.
He's stated that demo databases tend to age with time. As
you enter orders they become older and older as time
passes.
Anyways, he's asking how difficult would it be to write a
SQL utility that asks the user for a number of days and
then ran through the ENTIRE database looking for EVERY
Date/Time column and add the number of days to the data
content of the field.
I've sort of created already somewhat a DML command that
will autogenerate for me the update statement, but is
there a way for me to prompt the user in Query Analyzer
for a value to set the variable? Does it have to be done
via command prompt?
I've copied below my DML statement that I've quickly
written up.
select 'update ' + OBJECT_NAME(id) + ' set ' + substring
(name,1,30) + ' = ' + name + ' + @.adddays' + ' from ' +
OBJECT_NAME(id)
from syscolumns
where name like '%Date%'
order by OBJECT_NAME(id)
TIA,
BettinaYou can make a template in Query Analyzer ...
You can insert tags like: <myTag, varchar(20), 'abc'> (Structure is:
<TagName, DataType, Default>)
Then just hit CTRL-SHIFT-M and it will prompt for replacement. Very handy.
You can also save your template in the <SQL Server
Root>\80\Tools\Templates\SQL Query Analyzer\ folder, as a .TQL file, and
when you click on the Templates tag in Query Analyzer it will be there for
you to use.
Or you could just make a stored procedure...
"bpdee" <anonymous@.discussions.microsoft.com> wrote in message
news:051601c3a3f6$91dcb9f0$a401280a@.phx.gbl...
> Hi,
> I got a request from our Sr. Director regarding demo data.
> He's stated that demo databases tend to age with time. As
> you enter orders they become older and older as time
> passes.
> Anyways, he's asking how difficult would it be to write a
> SQL utility that asks the user for a number of days and
> then ran through the ENTIRE database looking for EVERY
> Date/Time column and add the number of days to the data
> content of the field.
> I've sort of created already somewhat a DML command that
> will autogenerate for me the update statement, but is
> there a way for me to prompt the user in Query Analyzer
> for a value to set the variable? Does it have to be done
> via command prompt?
> I've copied below my DML statement that I've quickly
> written up.
> select 'update ' + OBJECT_NAME(id) + ' set ' + substring
> (name,1,30) + ' = ' + name + ' + @.adddays' + ' from ' +
> OBJECT_NAME(id)
> from syscolumns
> where name like '%Date%'
> order by OBJECT_NAME(id)
> TIA,
> Bettina
>
>|||In the databases I see you will pick up lots of things that don't correspond
to an SQL datetime data type.
If you get this syscolumns.xtype contain they system type of the column.
syscolumns.xtype in (58, 61) will pick all datetime columns.
"bpdee" <anonymous@.discussions.microsoft.com> wrote in message
news:051601c3a3f6$91dcb9f0$a401280a@.phx.gbl...
> Hi,
> I got a request from our Sr. Director regarding demo data.
> He's stated that demo databases tend to age with time. As
> you enter orders they become older and older as time
> passes.
> Anyways, he's asking how difficult would it be to write a
> SQL utility that asks the user for a number of days and
> then ran through the ENTIRE database looking for EVERY
> Date/Time column and add the number of days to the data
> content of the field.
> I've sort of created already somewhat a DML command that
> will autogenerate for me the update statement, but is
> there a way for me to prompt the user in Query Analyzer
> for a value to set the variable? Does it have to be done
> via command prompt?
> I've copied below my DML statement that I've quickly
> written up.
> select 'update ' + OBJECT_NAME(id) + ' set ' + substring
> (name,1,30) + ' = ' + name + ' + @.adddays' + ' from ' +
> OBJECT_NAME(id)
> from syscolumns
> where name like '%Date%'
> order by OBJECT_NAME(id)
> TIA,
> Bettina
>
>

add a column if product is in discrepancies table (was "sql query")

I have a transaction table (which contains details of all products on all orders)
and is have a discrepancies table which includes details on products that couldnt been found (hence error in stock count)...

Now I want to write a query returning details of all products from a specfic orders.. pretty simple

SELECT product, description, qty
from TRANSACTIONS
where order_no = 'xxx'

but i also want to add an extra boolean column (true if product is in discrepancies table and false if product is not in discrepancies table...will become checkbox at front end)... im not sure how to write this bit...

I know it would a transaction table LEFT JOIN to discrepancies table, but I do not know how to write syntax to add extra column...

Be garteful for any help :confused:

Thanksselect product
, description
, qty
, case when exists
( select 937
from discrepancies
where product_id = t.product_id )
then 'oui'
else 'non'
end as discrepancy_exists
from TRANSACTIONS as t
where order_no = 'xxx'|||thanks worked like a treat :)

ActualRebinds & ActualRewinds

Hi,
I have the following query:
use northwind
go
select c.companyname,o.orderdate, od.discount, p.productname from customers
c join orders o
on c.customerid=o.customerid
join [order details] od on o.orderid=od.orderid
join products p on od.productid=p.productid
where c.customerid like 'a%' and o.shipcountry='germany'
order by c.city
I get a sort operator in graphical execution plan that has:
ActualRebinds=1 and
ActualRewinds=0
I read about these two in BOL (Physical Operators) but I couldn't understand
that about my query. What does it show?
Thanks in advance,
Leila
On May 6, 1:09 pm, "Leila" <Lei...@.hotpop.com> wrote:
> Hi,
> I have the following query:
> use northwind
> go
> select c.companyname,o.orderdate, od.discount, p.productname from customers
> c join orders o
> on c.customerid=o.customerid
> join [order details] od on o.orderid=od.orderid
> join products p on od.productid=p.productid
> where c.customerid like 'a%' and o.shipcountry='germany'
> order by c.city
> I get a sort operator in graphical execution plan that has:
> ActualRebinds=1 and
> ActualRewinds=0
> I read about these two in BOL (Physical Operators) but I couldn't understand
> that about my query. What does it show?
> Thanks in advance,
> Leila
This link might be helpful.
http://msdn2.microsoft.com/en-us/library/ms191158.aspx
Regards,
Enrique Martinez
Sr. Software Consultant
|||Thanks Enrique,
This is exactly what I read in BOL. I cannot understand this:
A rebind means that one or more of the correlated parameters of the join
changed and the inner side must be reevaluated. A rewind means that none of
the correlated parameters changed and the prior inner result set may be
reused
How does the "correlated parameters of the join" can change during the
execution?
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1178476038.970481.125060@.u30g2000hsc.googlegr oups.com...
> On May 6, 1:09 pm, "Leila" <Lei...@.hotpop.com> wrote:
>
> This link might be helpful.
> http://msdn2.microsoft.com/en-us/library/ms191158.aspx
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>
|||Leila (Leilas@.hotpop.com) writes:
> use northwind
> go
> select c.companyname,o.orderdate, od.discount, p.productname from
> customers
> c join orders o
> on c.customerid=o.customerid
> join [order details] od on o.orderid=od.orderid
> join products p on od.productid=p.productid
> where c.customerid like 'a%' and o.shipcountry='germany'
> order by c.city
> I get a sort operator in graphical execution plan that has:
> ActualRebinds=1 and
> ActualRewinds=0
> I read about these two in BOL (Physical Operators) but I couldn't
> understand that about my query. What does it show?
Not much, it seems. Books Online says:
Unless an operator is on the inner side of a loop join, ActualRebinds
equals one and ActualRewinds equals zero.
In your case, you got these two for a Sort operator, so the result is to
be expected.
Interesting enough, I did not get any Sort operator when I ran your
query in my Northwind database on SQL 2005 SP2...
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Hi Erland,

> Interesting enough, I did not get any Sort operator when I ran your
> query in my Northwind database on SQL 2005 SP2...
I guess you have index on customers(city,companyname) that optimizer has
choosen that!

> In your case, you got these two for a Sort operator, so the result is to
> be expected.
That's ok! But I'd like to know the meaning of these two items. For example
I cannot understand this from BOL:
A rebind means that one or more of the correlated parameters of the join
changed and the inner side must be reevaluated. A rewind means that none of
the correlated parameters changed and the prior inner result set may be
reused
How does the "correlated parameters of the join" can change during the
execution?
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9928EDA6A1317Yazorman@.127.0.0.1...
> Leila (Leilas@.hotpop.com) writes:
> Not much, it seems. Books Online says:
> Unless an operator is on the inner side of a loop join, ActualRebinds
> equals one and ActualRewinds equals zero.
> In your case, you got these two for a Sort operator, so the result is to
> be expected.
> Interesting enough, I did not get any Sort operator when I ran your
> query in my Northwind database on SQL 2005 SP2...
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Leila (Leilas@.hotpop.com) writes:
> That's ok! But I'd like to know the meaning of these two items. For
> example I cannot understand this from BOL: A rebind means that one or
> more of the correlated parameters of the join changed and the inner side
> must be reevaluated. A rewind means that none of the correlated
> parameters changed and the prior inner result set may be reused
> How does the "correlated parameters of the join" can change during the
> execution?
I will have to admit that I'm quite much in the dark myself. It would
help to have a query where Actual Rebinds/Rewinds are non-zero (save for
sorting operations then). I've been trying to find such a query, but
since I don't know what I'm looking for, I have not been successful.
(But I did not spend the entire week looking. The week was busy, and when
I first tried, SQL Server did not want to cooperate at all. A corrupt
database, cause SQL Server to get a stalled scheduler already on
startup, and did not have the time to investigate that for a few
days.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||> It would
> help to have a query where Actual Rebinds/Rewinds are non-zero (save for
> sorting operations then). I've been trying to find such a query, but
> since I don't know what I'm looking for, I have not been successful.
Exactly my problem!
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns992FE759593D0Yazorman@.127.0.0.1...
> Leila (Leilas@.hotpop.com) writes:
> I will have to admit that I'm quite much in the dark myself. It would
> help to have a query where Actual Rebinds/Rewinds are non-zero (save for
> sorting operations then). I've been trying to find such a query, but
> since I don't know what I'm looking for, I have not been successful.
> (But I did not spend the entire week looking. The week was busy, and when
> I first tried, SQL Server did not want to cooperate at all. A corrupt
> database, cause SQL Server to get a stalled scheduler already on
> startup, and did not have the time to investigate that for a few
> days.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

ActualRebinds & ActualRewinds

Hi,
I have the following query:
use northwind
go
select c.companyname,o.orderdate, od.discount, p.productname from customers
c join orders o
on c.customerid=o.customerid
join [order details] od on o.orderid=od.orderid
join products p on od.productid=p.productid
where c.customerid like 'a%' and o.shipcountry='germany'
order by c.city
I get a sort operator in graphical execution plan that has:
ActualRebinds=1 and
ActualRewinds=0
I read about these two in BOL (Physical Operators) but I couldn't understand
that about my query. What does it show?
Thanks in advance,
LeilaOn May 6, 1:09 pm, "Leila" <Lei...@.hotpop.com> wrote:
> Hi,
> I have the following query:
> use northwind
> go
> select c.companyname,o.orderdate, od.discount, p.productname from customers
> c join orders o
> on c.customerid=o.customerid
> join [order details] od on o.orderid=od.orderid
> join products p on od.productid=p.productid
> where c.customerid like 'a%' and o.shipcountry='germany'
> order by c.city
> I get a sort operator in graphical execution plan that has:
> ActualRebinds=1 and
> ActualRewinds=0
> I read about these two in BOL (Physical Operators) but I couldn't understand
> that about my query. What does it show?
> Thanks in advance,
> Leila
This link might be helpful.
http://msdn2.microsoft.com/en-us/library/ms191158.aspx
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks Enrique,
This is exactly what I read in BOL. I cannot understand this:
A rebind means that one or more of the correlated parameters of the join
changed and the inner side must be reevaluated. A rewind means that none of
the correlated parameters changed and the prior inner result set may be
reused
How does the "correlated parameters of the join" can change during the
execution?
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1178476038.970481.125060@.u30g2000hsc.googlegroups.com...
> On May 6, 1:09 pm, "Leila" <Lei...@.hotpop.com> wrote:
>> Hi,
>> I have the following query:
>> use northwind
>> go
>> select c.companyname,o.orderdate, od.discount, p.productname from
>> customers
>> c join orders o
>> on c.customerid=o.customerid
>> join [order details] od on o.orderid=od.orderid
>> join products p on od.productid=p.productid
>> where c.customerid like 'a%' and o.shipcountry='germany'
>> order by c.city
>> I get a sort operator in graphical execution plan that has:
>> ActualRebinds=1 and
>> ActualRewinds=0
>> I read about these two in BOL (Physical Operators) but I couldn't
>> understand
>> that about my query. What does it show?
>> Thanks in advance,
>> Leila
>
> This link might be helpful.
> http://msdn2.microsoft.com/en-us/library/ms191158.aspx
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||Leila (Leilas@.hotpop.com) writes:
> use northwind
> go
> select c.companyname,o.orderdate, od.discount, p.productname from
> customers
> c join orders o
> on c.customerid=o.customerid
> join [order details] od on o.orderid=od.orderid
> join products p on od.productid=p.productid
> where c.customerid like 'a%' and o.shipcountry='germany'
> order by c.city
> I get a sort operator in graphical execution plan that has:
> ActualRebinds=1 and
> ActualRewinds=0
> I read about these two in BOL (Physical Operators) but I couldn't
> understand that about my query. What does it show?
Not much, it seems. Books Online says:
Unless an operator is on the inner side of a loop join, ActualRebinds
equals one and ActualRewinds equals zero.
In your case, you got these two for a Sort operator, so the result is to
be expected.
Interesting enough, I did not get any Sort operator when I ran your
query in my Northwind database on SQL 2005 SP2...
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Hi Erland,
> Interesting enough, I did not get any Sort operator when I ran your
> query in my Northwind database on SQL 2005 SP2...
I guess you have index on customers(city,companyname) that optimizer has
choosen that!
> In your case, you got these two for a Sort operator, so the result is to
> be expected.
That's ok! But I'd like to know the meaning of these two items. For example
I cannot understand this from BOL:
A rebind means that one or more of the correlated parameters of the join
changed and the inner side must be reevaluated. A rewind means that none of
the correlated parameters changed and the prior inner result set may be
reused
How does the "correlated parameters of the join" can change during the
execution?
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9928EDA6A1317Yazorman@.127.0.0.1...
> Leila (Leilas@.hotpop.com) writes:
>> use northwind
>> go
>> select c.companyname,o.orderdate, od.discount, p.productname from
>> customers
>> c join orders o
>> on c.customerid=o.customerid
>> join [order details] od on o.orderid=od.orderid
>> join products p on od.productid=p.productid
>> where c.customerid like 'a%' and o.shipcountry='germany'
>> order by c.city
>> I get a sort operator in graphical execution plan that has:
>> ActualRebinds=1 and
>> ActualRewinds=0
>> I read about these two in BOL (Physical Operators) but I couldn't
>> understand that about my query. What does it show?
> Not much, it seems. Books Online says:
> Unless an operator is on the inner side of a loop join, ActualRebinds
> equals one and ActualRewinds equals zero.
> In your case, you got these two for a Sort operator, so the result is to
> be expected.
> Interesting enough, I did not get any Sort operator when I ran your
> query in my Northwind database on SQL 2005 SP2...
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Leila (Leilas@.hotpop.com) writes:
> That's ok! But I'd like to know the meaning of these two items. For
> example I cannot understand this from BOL: A rebind means that one or
> more of the correlated parameters of the join changed and the inner side
> must be reevaluated. A rewind means that none of the correlated
> parameters changed and the prior inner result set may be reused
> How does the "correlated parameters of the join" can change during the
> execution?
I will have to admit that I'm quite much in the dark myself. It would
help to have a query where Actual Rebinds/Rewinds are non-zero (save for
sorting operations then). I've been trying to find such a query, but
since I don't know what I'm looking for, I have not been successful.
(But I did not spend the entire week looking. The week was busy, and when
I first tried, SQL Server did not want to cooperate at all. A corrupt
database, cause SQL Server to get a stalled scheduler already on
startup, and did not have the time to investigate that for a few
days.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||> It would
> help to have a query where Actual Rebinds/Rewinds are non-zero (save for
> sorting operations then). I've been trying to find such a query, but
> since I don't know what I'm looking for, I have not been successful.
Exactly my problem!
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns992FE759593D0Yazorman@.127.0.0.1...
> Leila (Leilas@.hotpop.com) writes:
>> That's ok! But I'd like to know the meaning of these two items. For
>> example I cannot understand this from BOL: A rebind means that one or
>> more of the correlated parameters of the join changed and the inner side
>> must be reevaluated. A rewind means that none of the correlated
>> parameters changed and the prior inner result set may be reused
>> How does the "correlated parameters of the join" can change during the
>> execution?
> I will have to admit that I'm quite much in the dark myself. It would
> help to have a query where Actual Rebinds/Rewinds are non-zero (save for
> sorting operations then). I've been trying to find such a query, but
> since I don't know what I'm looking for, I have not been successful.
> (But I did not spend the entire week looking. The week was busy, and when
> I first tried, SQL Server did not want to cooperate at all. A corrupt
> database, cause SQL Server to get a stalled scheduler already on
> startup, and did not have the time to investigate that for a few
> days.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Erland Sommarskog <esquel@.sommarskog.se> writes:
>I will have to admit that I'm quite much in the dark myself. It would
>help to have a query where Actual Rebinds/Rewinds are non-zero (save for
>sorting operations then). I've been trying to find such a query, but
>since I don't know what I'm looking for, I have not been successful.
OK, so here is an example where Actual Rewinds and Actual Rebinds are > 0.
I can't say that I understand exactly what is going on, but the
query has a Lazy Spool operator, and if you read about the Lazy Spool
operator in Books Online, you will see that it talks about rewinds and
rebinds. The query plan is in any case a disaster.
To try this, you first need to download the script for the Northgale
database, an inflated version of Northwind from
http://www.sommarskog.se/dynsearch/Northgale.sql.
The script first sets up some stuff, and then issues two query. It is
the first, slow, query which has the rewinds and the rebinds.
-- This is the setup.
SELECT *, GUID = newid() INTO Orders FROM Northgale..Orders
go
CREATE UNIQUE CLUSTERED INDEX clust ON Orders (CustomerID, OrderDate, EmployeeID, GUID)
ALTER TABLE Orders ADD CONSTRAINT pk_orders PRIMARY KEY (OrderID)
go
CREATE TABLE Orderlinks
(prevorderid int NOT NULL REFERENCES Orders(OrderID),
succorderid int NOT NULL REFERENCES Orders(OrderID),
filler char(16) NOT NULL DEFAULT ' ',
PRIMARY KEY (prevorderid, succorderid)
)
go
CREATE INDEX succorderid_ix ON Orderlinks(succorderid)
go
INSERT Orderlinks (prevorderid, succorderid)
SELECT a.OrderID, b.OrderID
FROM (SELECT OrderID, rn = row_number() OVER(ORDER BY GUID ASC)
FROM Orders) AS a
JOIN (SELECT OrderID, rn = row_number() OVER(ORDER BY GUID DESC)
FROM Orders) AS b ON a.rn = b.rn
WHERE a.rn <= 1000
go
UPDATE STATISTICS Orderlinks WITH FULLSCAN
go
DROP TABLE #temp1
CREATE TABLE #temp1
(CustomerID nchar(5) NOT NULL,
EmployeeID int NOT NULL,
minorderid int NOT NULL,
cnt int NOT NULL,
PRIMARY KEY(CustomerID, EmployeeID))
CREATE TABLE #temp2
(CustomerID nchar(5) NOT NULL,
EmployeeID int NOT NULL,
minorderid int NOT NULL,
cnt int NOT NULL,
PRIMARY KEY(CustomerID, EmployeeID))
go
-- Here starts the actual test.
SET STATISTICS IO ON
go
SELECT getdate()
go
-- Slow query.
INSERT #temp1(CustomerID, EmployeeID, minorderid, cnt)
SELECT O.CustomerID, O.EmployeeID, MIN(O.OrderID), COUNT(*)
FROM Orders O
WHERE EXISTS
(SELECT *
FROM Orderlinks L
WHERE O.OrderID IN (L.prevorderid, succorderid))
GROUP BY O.CustomerID, O.EmployeeID
go
SELECT getdate()
go
-- Logically the same query, but fast.
INSERT #temp2(CustomerID, EmployeeID, minorderid, cnt)
SELECT O.CustomerID, O.EmployeeID, MIN(O.OrderID), COUNT(DISTINCT O.OrderID)
FROM Orders O
JOIN Orderlinks L ON O.OrderID IN (L.prevorderid, succorderid)
GROUP BY O.CustomerID, O.EmployeeID
go
SELECT getdate()
go
SET STATISTICS IO OFF
Erland Sommarskog, Stockholm, esquel@.sommarskog.se

ActualRebinds & ActualRewinds

Hi,
I have the following query:
use northwind
go
select c.companyname,o.orderdate, od.discount, p.productname from customers
c join orders o
on c.customerid=o.customerid
join [order details] od on o.orderid=od.orderid
join products p on od.productid=p.productid
where c.customerid like 'a%' and o.shipcountry='germany'
order by c.city
I get a sort operator in graphical execution plan that has:
ActualRebinds=1 and
ActualRewinds=0
I read about these two in BOL (Physical Operators) but I couldn't understand
that about my query. What does it show?
Thanks in advance,
LeilaOn May 6, 1:09 pm, "Leila" <Lei...@.hotpop.com> wrote:
> Hi,
> I have the following query:
> use northwind
> go
> select c.companyname,o.orderdate, od.discount, p.productname from customer
s
> c join orders o
> on c.customerid=o.customerid
> join [order details] od on o.orderid=od.orderid
> join products p on od.productid=p.productid
> where c.customerid like 'a%' and o.shipcountry='germany'
> order by c.city
> I get a sort operator in graphical execution plan that has:
> ActualRebinds=1 and
> ActualRewinds=0
> I read about these two in BOL (Physical Operators) but I couldn't understa
nd
> that about my query. What does it show?
> Thanks in advance,
> Leila
This link might be helpful.
http://msdn2.microsoft.com/en-us/library/ms191158.aspx
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks Enrique,
This is exactly what I read in BOL. I cannot understand this:
A rebind means that one or more of the correlated parameters of the join
changed and the inner side must be reevaluated. A rewind means that none of
the correlated parameters changed and the prior inner result set may be
reused
How does the "correlated parameters of the join" can change during the
execution?
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1178476038.970481.125060@.u30g2000hsc.googlegroups.com...
> On May 6, 1:09 pm, "Leila" <Lei...@.hotpop.com> wrote:
>
> This link might be helpful.
> http://msdn2.microsoft.com/en-us/library/ms191158.aspx
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||Leila (Leilas@.hotpop.com) writes:
> use northwind
> go
> select c.companyname,o.orderdate, od.discount, p.productname from
> customers
> c join orders o
> on c.customerid=o.customerid
> join [order details] od on o.orderid=od.orderid
> join products p on od.productid=p.productid
> where c.customerid like 'a%' and o.shipcountry='germany'
> order by c.city
> I get a sort operator in graphical execution plan that has:
> ActualRebinds=1 and
> ActualRewinds=0
> I read about these two in BOL (Physical Operators) but I couldn't
> understand that about my query. What does it show?
Not much, it seems. Books Online says:
Unless an operator is on the inner side of a loop join, ActualRebinds
equals one and ActualRewinds equals zero.
In your case, you got these two for a Sort operator, so the result is to
be expected.
Interesting enough, I did not get any Sort operator when I ran your
query in my Northwind database on SQL 2005 SP2...
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,

> Interesting enough, I did not get any Sort operator when I ran your
> query in my Northwind database on SQL 2005 SP2...
I guess you have index on customers(city,companyname) that optimizer has
choosen that!

> In your case, you got these two for a Sort operator, so the result is to
> be expected.
That's ok! But I'd like to know the meaning of these two items. For example
I cannot understand this from BOL:
A rebind means that one or more of the correlated parameters of the join
changed and the inner side must be reevaluated. A rewind means that none of
the correlated parameters changed and the prior inner result set may be
reused
How does the "correlated parameters of the join" can change during the
execution?
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9928EDA6A1317Yazorman@.127.0.0.1...
> Leila (Leilas@.hotpop.com) writes:
> Not much, it seems. Books Online says:
> Unless an operator is on the inner side of a loop join, ActualRebinds
> equals one and ActualRewinds equals zero.
> In your case, you got these two for a Sort operator, so the result is to
> be expected.
> Interesting enough, I did not get any Sort operator when I ran your
> query in my Northwind database on SQL 2005 SP2...
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Leila (Leilas@.hotpop.com) writes:
> That's ok! But I'd like to know the meaning of these two items. For
> example I cannot understand this from BOL: A rebind means that one or
> more of the correlated parameters of the join changed and the inner side
> must be reevaluated. A rewind means that none of the correlated
> parameters changed and the prior inner result set may be reused
> How does the "correlated parameters of the join" can change during the
> execution?
I will have to admit that I'm quite much in the dark myself. It would
help to have a query where Actual Rebinds/Rewinds are non-zero (save for
sorting operations then). I've been trying to find such a query, but
since I don't know what I'm looking for, I have not been successful.
(But I did not spend the entire week looking. The week was busy, and when
I first tried, SQL Server did not want to cooperate at all. A corrupt
database, cause SQL Server to get a stalled scheduler already on
startup, and did not have the time to investigate that for a few
days.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> It would
> help to have a query where Actual Rebinds/Rewinds are non-zero (save for
> sorting operations then). I've been trying to find such a query, but
> since I don't know what I'm looking for, I have not been successful.
Exactly my problem!
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns992FE759593D0Yazorman@.127.0.0.1...
> Leila (Leilas@.hotpop.com) writes:
> I will have to admit that I'm quite much in the dark myself. It would
> help to have a query where Actual Rebinds/Rewinds are non-zero (save for
> sorting operations then). I've been trying to find such a query, but
> since I don't know what I'm looking for, I have not been successful.
> (But I did not spend the entire week looking. The week was busy, and when
> I first tried, SQL Server did not want to cooperate at all. A corrupt
> database, cause SQL Server to get a stalled scheduler already on
> startup, and did not have the time to investigate that for a few
> days.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog <esquel@.sommarskog.se> writes:
>I will have to admit that I'm quite much in the dark myself. It would
>help to have a query where Actual Rebinds/Rewinds are non-zero (save for
>sorting operations then). I've been trying to find such a query, but
>since I don't know what I'm looking for, I have not been successful.
OK, so here is an example where Actual Rewinds and Actual Rebinds are > 0.
I can't say that I understand exactly what is going on, but the
query has a Lazy Spool operator, and if you read about the Lazy Spool
operator in Books Online, you will see that it talks about rewinds and
rebinds. The query plan is in any case a disaster.
To try this, you first need to download the script for the Northgale
database, an inflated version of Northwind from
http://www.sommarskog.se/dynsearch/Northgale.sql.
The script first sets up some stuff, and then issues two query. It is
the first, slow, query which has the rewinds and the rebinds.
-- This is the setup.
SELECT *, GUID = newid() INTO Orders FROM Northgale..Orders
go
CREATE UNIQUE CLUSTERED INDEX clust ON Orders (CustomerID, OrderDate, Employ
eeID, GUID)
ALTER TABLE Orders ADD CONSTRAINT pk_orders PRIMARY KEY (OrderID)
go
CREATE TABLE Orderlinks
(prevorderid int NOT NULL REFERENCES Orders(OrderID),
succorderid int NOT NULL REFERENCES Orders(OrderID),
filler char(16) NOT NULL DEFAULT ' ',
PRIMARY KEY (prevorderid, succorderid)
)
go
CREATE INDEX succorderid_ix ON Orderlinks(succorderid)
go
INSERT Orderlinks (prevorderid, succorderid)
SELECT a.OrderID, b.OrderID
FROM (SELECT OrderID, rn = row_number() OVER(ORDER BY GUID ASC)
FROM Orders) AS a
JOIN (SELECT OrderID, rn = row_number() OVER(ORDER BY GUID DESC)
FROM Orders) AS b ON a.rn = b.rn
WHERE a.rn <= 1000
go
UPDATE STATISTICS Orderlinks WITH FULLSCAN
go
DROP TABLE #temp1
CREATE TABLE #temp1
(CustomerID nchar(5) NOT NULL,
EmployeeID int NOT NULL,
minorderid int NOT NULL,
cnt int NOT NULL,
PRIMARY KEY(CustomerID, EmployeeID))
CREATE TABLE #temp2
(CustomerID nchar(5) NOT NULL,
EmployeeID int NOT NULL,
minorderid int NOT NULL,
cnt int NOT NULL,
PRIMARY KEY(CustomerID, EmployeeID))
go
-- Here starts the actual test.
SET STATISTICS IO ON
go
SELECT getdate()
go
-- Slow query.
INSERT #temp1(CustomerID, EmployeeID, minorderid, cnt)
SELECT O.CustomerID, O.EmployeeID, MIN(O.OrderID), COUNT(*)
FROM Orders O
WHERE EXISTS
(SELECT *
FROM Orderlinks L
WHERE O.OrderID IN (L.prevorderid, succorderid))
GROUP BY O.CustomerID, O.EmployeeID
go
SELECT getdate()
go
-- Logically the same query, but fast.
INSERT #temp2(CustomerID, EmployeeID, minorderid, cnt)
SELECT O.CustomerID, O.EmployeeID, MIN(O.OrderID), COUNT(DISTINCT O.OrderID)
FROM Orders O
JOIN Orderlinks L ON O.OrderID IN (L.prevorderid, succorderid)
GROUP BY O.CustomerID, O.EmployeeID
go
SELECT getdate()
go
SET STATISTICS IO OFF
Erland Sommarskog, Stockholm, esquel@.sommarskog.se