Mirrored Records?

Hi I need some SQL script help. Need script to delete all table rows that
are duplicates in mirror image. Table has 2 columns, ColumnA and ColumnB.
Row1: ColumnA = x, ColumnB = y
Row2: ColumnA = y, ColumnB = x
Those 2 rows are exactly the same for me. Need a script that will delete
Row2 and all other rows where they're mirrored duplicates.
Thanks DarenDaren,
Once you do this, you should consider putting a constraint on the
table to enforce ColumnA <= ColumnB so this doesn't happen again.
It's a bad model if there are two ways of representing the same facts.
delete from T as T1
where ColumnA > ColumnB
and exists (
select * from T as T2
where T2.ColumnA = T1.ColumnB
and T2.ColumnB = T1.ColumnA
)
If you want to delete only these "mirror duplicates"
when they exist for the same customer/transaction/whatever,
you will need something like
delete from T as T1
where ColumnA > ColumnB
and exists (
select * from T as T2
where T2.customer = T1.customer
and T2.ColumnA = T1.ColumnB
and T2.ColumnB = T1.ColumnA
)
Steve Kass
Drew University
Daren Hawes wrote:

>Hi I need some SQL script help. Need script to delete all table rows that
>are duplicates in mirror image. Table has 2 columns, ColumnA and ColumnB.
>Row1: ColumnA = x, ColumnB = y
>Row2: ColumnA = y, ColumnB = x
>Those 2 rows are exactly the same for me. Need a script that will delete
>Row2 and all other rows where they're mirrored duplicates.
>Thanks Daren
>
>|||Thanks. I am a little with T and T2
Do I need to create a new Table? I have added the actual names below.
Column a = FromID ; Column b = ToID
CREATE TABLE [dbo].[tbl_Matrix] (
[FareID] [int] IDENTITY (1, 1) NOT NULL ,
[FromID] [int] NULL ,
[ToID] [int] NULL ,
[PriceCode] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tbl_Matrix] ADD
CONSTRAINT [PK_tbl_Matrix] PRIMARY KEY CLUSTERED
(
[FareID]
) ON [PRIMARY]
GO
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23eKPQBxZFHA.2212@.TK2MSFTNGP14.phx.gbl...
> Daren,
> Once you do this, you should consider putting a constraint on the
> table to enforce ColumnA <= ColumnB so this doesn't happen again.
> It's a bad model if there are two ways of representing the same facts.
> delete from T as T1
> where ColumnA > ColumnB
> and exists (
> select * from T as T2
> where T2.ColumnA = T1.ColumnB
> and T2.ColumnB = T1.ColumnA
> )
> If you want to delete only these "mirror duplicates"
> when they exist for the same customer/transaction/whatever,
> you will need something like
> delete from T as T1
> where ColumnA > ColumnB
> and exists (
> select * from T as T2
> where T2.customer = T1.customer
> and T2.ColumnA = T1.ColumnB
> and T2.ColumnB = T1.ColumnA
> )
> Steve Kass
> Drew University
>
> Daren Hawes wrote:
>|||Try the following untested DELETE statement:
DELETE FROM tbl_Matrix
WHERE EXISTS
(SELECT *
FROM tbl_Matrix AS T
WHERE T.fromid = tbl_Matrix.fromid
AND T.toid = tbl_Matrix.toid
AND T.fareid < tbl_Matrix.fareid)
Now make FromID and ToID not nullable and add a unqiue constraint on those
two columns.
David Portas
SQL Server MVP
--|||Thanks for the reply Dave (I'm working with Daren),
Tryed that 1, deleted nothing, modified it to this...
DELETE FROM tbl_Matrix
WHERE EXISTS
(SELECT *
FROM tbl_Matrix AS T
WHERE T.fromid = tbl_Matrix.toid
AND T.toid = tbl_Matrix.fromid)
...and it deleted everything.
This is a copy of the script we've used to create the table's data, perhaps
if we wrote this script a bit better, wouldn't need another script to clean
it up...
declare @.Counter int
declare @.Counter2 int
select @.Counter=1
select @.Counter2=1
while @.Counter < 136
begin
while @.Counter2 < 136
Begin
Insert into dbo.tbl_Matrix (FromID,ToID)
Values (@.Counter,@.Counter2)
set @.Counter2 = @.Counter2 + 1
End
set @.Counter = @.Counter + 1
set @.Counter2 = 1
end
Regards,
Offal Eater
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:xZ6dndpRhvr8_gPfRVn-sw@.giganews.com...
> Try the following untested DELETE statement:
> DELETE FROM tbl_Matrix
> WHERE EXISTS
> (SELECT *
> FROM tbl_Matrix AS T
> WHERE T.fromid = tbl_Matrix.fromid
> AND T.toid = tbl_Matrix.toid
> AND T.fareid < tbl_Matrix.fareid)
> Now make FromID and ToID not nullable and add a unqiue constraint on those
> two columns.
> --
> David Portas
> SQL Server MVP
> --
>|||Thanks for the reply Dave (I'm working with Daren),
Not having any luck with these scripts, I've included the script we've used
to create the table. Perhaps if we wrote this differently, wouldn't have
need for the 'clean-up' script.
declare @.Counter int
declare @.Counter2 int
select @.Counter=1
select @.Counter2=1
while @.Counter < 136
begin
while @.Counter2 < 136
Begin
Insert into dbo.tbl_Matrix (FromID,ToID)
Values (@.Counter,@.Counter2)
set @.Counter2 = @.Counter2 + 1
End
set @.Counter = @.Counter + 1
set @.Counter2 = 1
end
Any feedback would be great.
Thx,
Offal Eater
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:xZ6dndpRhvr8_gPfRVn-sw@.giganews.com...
> Try the following untested DELETE statement:
> DELETE FROM tbl_Matrix
> WHERE EXISTS
> (SELECT *
> FROM tbl_Matrix AS T
> WHERE T.fromid = tbl_Matrix.fromid
> AND T.toid = tbl_Matrix.toid
> AND T.fareid < tbl_Matrix.fareid)
> Now make FromID and ToID not nullable and add a unqiue constraint on those
> two columns.
> --
> David Portas
> SQL Server MVP
> --
>|||T1 and T2 are table aliases. You need to compare each
row of T with the rest of T, and to distinguish the "this row"
table and the "rest of" table, you need the aliases:
Since FareID is the primary key, this will do
delete from tbl_Matrix as T1
where ToID > FromID
and exists (
select * from tbl_Matrix as T2
where T2.FromID = T1.ToID
and T2.ToID = T1.FromID
)
This will arbitrarily remove the one of the two mirror duplicates
for which ToID < FromID. If you want to remove, say, the
one with the smaller FareID, you could do this instead:
delete from tbl_Matrix as T1
where exists (
select * from tbl_Matrix as T2
where T2.FromID = T1.ToID
and T2.ToID = T1.FromID
and T2.FareID > T1.FareID
)
Seeing your table now, I'd recommend you change the primary
key to (FromID, ToID), and add a table constraint to enforce
FromID < ToID, or at least put a UNIQUE constraint on
(FromID, ToID), and make those two columns NOT NULL (if
not all columns). I don't see much use to the column FareID,
actually, but removing it might mess up other thing you rely on.
There could be other problems, too. You could have many rows
with the same FromID, ToID pair in the same order, but with
different FareID values. So you might want to do this:
delete from tbl_Matrix as T1
where exists (
select * from tbl_Matrix as T2
where (
T2.FromID = T1.ToID
and T2.ToID = T1.FromID
) or (
T2.FromID = T1.FromID
and T2.ToID = T1.ToID
)
and T2.FareID > T1.FareID
)
If you have rows where FromID = ToID, you can delete them
separately. I assume those would make no sense, but you have
nothing to prevent that from occurring right now.
SK
Daren Hawes wrote:

>Thanks. I am a little with T and T2
>Do I need to create a new Table? I have added the actual names below.
>Column a = FromID ; Column b = ToID
>CREATE TABLE [dbo].[tbl_Matrix] (
> [FareID] [int] IDENTITY (1, 1) NOT NULL ,
> [FromID] [int] NULL ,
> [ToID] [int] NULL ,
> [PriceCode] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL
> ) ON [PRIMARY]
>GO
>ALTER TABLE [dbo].[tbl_Matrix] ADD
> CONSTRAINT [PK_tbl_Matrix] PRIMARY KEY CLUSTERED
> (
> [FareID]
> ) ON [PRIMARY]
>GO
>
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:%23eKPQBxZFHA.2212@.TK2MSFTNGP14.phx.gbl...
>
>
>|||> Tryed that 1, deleted nothing, modified it to this...
I ran your script and it didn't generate any duplicates so there is nothing
to delete - the combination of (FromID, ToID) is already unique (18225
rows). If you have something different in your data then please post a few
INSERT statements to generate some sample data that we can use to test it
out.

> This is a copy of the script we've used to create the table's data,
> perhaps if we wrote this script a bit better, wouldn't need another script
> to clean it up...
I posted a different solution to this in reply to Daren's post earlier
today.
David Portas
SQL Server MVP
--|||Here's a much better way to create the table. It uses
just one insert and no loops, and has much less chance of
off-by-one errors:
-- create a temporary table full of integers
declare @.Ints table (
IntVal int primary key
)
insert into @.Ints
select OrderID-10247
from Northwind..Orders
where OrderID-10247 between 1 and 136
-- Insert everything at once:
insert into dbo.tbl_Matrix(FromID, ToID)
select N1.IntVal, N2.IntVal
from @.Ints as N1 join @.Ints as N2
on N1.IntVal between 1 and 136
and N2.IntVal between N1.IntVal and 136
-- or N2.IntVal between N1.IntVal+1 and 136
If you want loops, but no mirror duplicates, replace
set @.Counter2 = 1
with
set @.Counter2 = @.Counter
This will continue to give you rows where FromID = ToID. If you don't
want those, start @.Counter2 one higher. Here's a guess, cleaned up
a bit, but I'd never trust this as much as the first suggestion I gave.
declare @.Counter int
declare @.Counter2 int
select @.Counter=0
while @.Counter < 135
begin
set @.Counter = @.Counter + 1
set @.Counter2 = @.Counter
while @.Counter2 < 135
Begin
set @.Counter2 = @.Counter2 + 1
Insert into dbo.tbl_Matrix (FromID,ToID)
Values (@.Counter,@.Counter2)
End
end
SK
Offal Eater wrote:

>Thanks for the reply Dave (I'm working with Daren),
>Not having any luck with these scripts, I've included the script we've used
>to create the table. Perhaps if we wrote this differently, wouldn't have
>need for the 'clean-up' script.
>declare @.Counter int
>declare @.Counter2 int
>select @.Counter=1
>select @.Counter2=1
>while @.Counter < 136
>begin
> while @.Counter2 < 136
> Begin
> Insert into dbo.tbl_Matrix (FromID,ToID)
> Values (@.Counter,@.Counter2)
> set @.Counter2 = @.Counter2 + 1
> End
>set @.Counter = @.Counter + 1
>set @.Counter2 = 1
>end
>Any feedback would be great.
>Thx,
>Offal Eater
>"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
>news:xZ6dndpRhvr8_gPfRVn-sw@.giganews.com...
>
>
>|||David,
I don't think the OP wants both (a, b) and (b, a). The 18225 rows
from the script are all (a,b) where a and b are between 1 and 135.
One way to eliminate the "mirror duplicates" is to generate only pairs
where a <= b (or perhaps a < b). There should be either 9180 or
9045 rows, depending on whether = is included.
SK
David Portas wrote:

>I ran your script and it didn't generate any duplicates so there is nothing
>to delete - the combination of (FromID, ToID) is already unique (18225
>rows). If you have something different in your data then please post a few
>INSERT statements to generate some sample data that we can use to test it
>out.
>
>
>I posted a different solution to this in reply to Daren's post earlier
>today.
>
>

Mirror database-How do I remove it from being the mirror

I was trying to test mirroring and now would like to delete the mirror database but it says I need to remove database mirroring first. I deleted the endpoint and cannot figure out how to remove the mirroring. Can someone please help.

Hi Pamela,

Deleting the endpoint does not remove mirroring, only disconnects it. You can remove mirroring by executing the T-SQL statement below. Note, however that since you removed the endpoint, you may need to execute the statement on both partners in order to remove the two copies of DB:

ALTER DATABASE <your_db_name> SET PARTNER OFF.

Thanks,

Kaloian.

millions records archiving and delete

The iussue:

Sql 2K
I have to keep in the database the data from the last 3 months.
Every day I have to load 2 millions records in the database.
So every day I have to export (in an other database as historical data
container) and delete the 2 millions records inserted 3 month + one day ago.

The main problem is that delete operation take a while...involving
transaction log.

The question are:
1) How can I improve this operation (export/delete)
2) If we decide to migrate to SQL 2005, may we use some feature, as
"partitioning" to resolve the problems ? In oracle I can use the "truncate
partition" statement, but in sql 2005, I'm reading, it cant be done.
This becouse we can think to create a partition on the last three mounts to
split data. The partitioning function can be dinamic or containing a
function that says "last 3 months ?" I dont think so.

May you help us
thank you

MastinoMassimo (mastino@.hotmail.it) writes:

Quote:

Originally Posted by

Sql 2K
I have to keep in the database the data from the last 3 months.
Every day I have to load 2 millions records in the database. So every
day I have to export (in an other database as historical data container)
and delete the 2 millions records inserted 3 month + one day ago.
>
The main problem is that delete operation take a while...involving
transaction log.
>
The question are:
1) How can I improve this operation (export/delete)
2) If we decide to migrate to SQL 2005, may we use some feature, as
"partitioning" to resolve the problems ? In oracle I can use the
"truncate partition" statement, but in sql 2005, I'm reading, it cant be
done. This becouse we can think to create a partition on the last three
mounts to split data. The partitioning function can be dinamic or
containing a function that says "last 3 months ?" I dont think so.

Permit me to start with SQL 2005. There you have partitioned tables,
and in a case like yours you would set up the table with let's say
four partitions, with the month as the partitioning column. To delete
old rows, you would simply take that table out of the partition
table, and then drop table that table. You in the same manner, shift
in a new table for the next month. Here I said month, but you have one
partition per day, and have 90 partitions if you like - whether this
is a good idea I don't know.

Note that partitioned tables are only available in the Enterprise
(and Developer) Edition of SQL 2005.

In SQL 2005, you would use partitioned views (and here 90 paritions
would definitely go beyond what is manageable). One table per month
and then they are united in a view with a UNION ALL statement. At
a new month you would run a job that dropped the table from four
months back, and create a new table. Notice that you can load directly
to the new, and data should turn in the right place.

See also Stefan Delmarco's article on partitioned views:
http://www.fotia.co.uk/fotia/FA.02...edViews.01.aspx
--
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|||First, thank you for the answer, Erland, it's not the first time you help me
!

Quote:

Originally Posted by

Permit me to start with SQL 2005. There you have partitioned tables,
and in a case like yours you would set up the table with let's say
four partitions, with the month as the partitioning column. To delete
old rows, you would simply take that table out of the partition
table, and then drop table that table. You in the same manner, shift
in a new table for the next month. Here I said month, but you have one
partition per day, and have 90 partitions if you like - whether this
is a good idea I don't know.

Reading and reading over the internet, I found that I can transfer the data
to be dropped with a:

ALTER TABLE SWITCH...

and I can also make partition function dynamic:

http://msdn2.microsoft.com/en-us/library/aa964122.aspx
now I'm studying hard for the solution, the problems to resolve are many.

Quote:

Originally Posted by

>
Note that partitioned tables are only available in the Enterprise
(and Developer) Edition of SQL 2005.
>
In SQL 2005, you would use partitioned views (and here 90 paritions
would definitely go beyond what is manageable). One table per month
and then they are united in a view with a UNION ALL statement. At
a new month you would run a job that dropped the table from four
months back, and create a new table. Notice that you can load directly
to the new, and data should turn in the right place.
>
See also Stefan Delmarco's article on partitioned views:
http://www.fotia.co.uk/fotia/FA.02...edViews.01.aspx

I do not want, and I cannot use partitioned wiews, I have to delete what we
do not need any more.

Quote:

Originally Posted by

>
>
--
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

Thank you

Massimo / Mastino|||Mastino (mastino@.hotmail.it) writes:

Quote:

Originally Posted by

I do not want, and I cannot use partitioned wiews, I have to delete what
we do not need any more.

Why would partitioned views prevent that? When it's time to delete old data,
you first redefine the view, so that the tables to be dropped are not
in the view any more. Then deleting is just dropping the table.

--
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|||I will evaluate this way too, but we have to work with millions records
tables.
Thanx

"Erland Sommarskog" <esquel@.sommarskog.seha scritto nel messaggio
news:Xns98E5ED0FE4612Yazorman@.127.0.0.1...

Quote:

Originally Posted by

Mastino (mastino@.hotmail.it) writes:

Quote:

Originally Posted by

I do not want, and I cannot use partitioned wiews, I have to delete what
we do not need any more.

>
Why would partitioned views prevent that? When it's time to delete old

data,

Quote:

Originally Posted by

you first redefine the view, so that the tables to be dropped are not
in the view any more. Then deleting is just dropping the table.
>
>
--
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

|||On Feb 28, 12:20 am, "Massimo" <mast...@.hotmail.itwrote:

Quote:

Originally Posted by

The iussue:
>
Sql 2K
I have to keep in the database the data from the last 3 months.
Every day I have to load 2 millions records in the database.
So every day I have to export (in an other database as historical data
container) and delete the 2 millions records inserted 3 month + one day ago.
>
The main problem is that delete operation take a while...involving
transaction log.
>
The question are:
1) How can I improve this operation (export/delete)
2) If we decide to migrate to SQL 2005, may we use some feature, as
"partitioning" to resolve the problems ? In oracle I can use the "truncate
partition" statement, but in sql 2005, I'm reading, it cant be done.
This becouse we can think to create a partition on the last three mounts to
split data. The partitioning function can be dinamic or containing a
function that says "last 3 months ?" I dont think so.
>
May you help us
thank you
>
Mastino

Just out of curiosity, do you have to log the delete operation? You
can truncate the tables but that is not logged.