Eliminating Duplicates

I have a table with 580 rows of duplicate on total of 1101 rows. The
duplicates are in 3 columns that were supposed to be primary key columns
(Developers forgot to create the primary key in development-production is
OK).
I figured the duplicates with a query but I don't know how to delete them
from the table.
Can anyone help ? Thanks.Hi,
See this:-
http://www.sqlteam.com/item.asp?ItemID=3331
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6256
http://support.microsoft.com/defaul...kb;en-us;139444
Thanks
Hari
SQL Server MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:DAF8776C-7644-4B3A-83BC-4C6B02FF2607@.microsoft.com...
>I have a table with 580 rows of duplicate on total of 1101 rows. The
> duplicates are in 3 columns that were supposed to be primary key columns
> (Developers forgot to create the primary key in development-production is
> OK).
> I figured the duplicates with a query but I don't know how to delete them
> from the table.
> Can anyone help ? Thanks.

Eliminating Duplicates

I have a table with 580 rows of duplicate on total of 1101 rows. The
duplicates are in 3 columns that were supposed to be primary key columns
(Developers forgot to create the primary key in development-production is
OK).
I figured the duplicates with a query but I don't know how to delete them
from the table.
Can anyone help ? Thanks.
Hi,
See this:-
http://www.sqlteam.com/item.asp?ItemID=3331
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6256
http://support.microsoft.com/default...b;en-us;139444
Thanks
Hari
SQL Server MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:DAF8776C-7644-4B3A-83BC-4C6B02FF2607@.microsoft.com...
>I have a table with 580 rows of duplicate on total of 1101 rows. The
> duplicates are in 3 columns that were supposed to be primary key columns
> (Developers forgot to create the primary key in development-production is
> OK).
> I figured the duplicates with a query but I don't know how to delete them
> from the table.
> Can anyone help ? Thanks.

Eliminating Duplicates

I have a table with 580 rows of duplicate on total of 1101 rows. The
duplicates are in 3 columns that were supposed to be primary key columns
(Developers forgot to create the primary key in development-production is
OK).
I figured the duplicates with a query but I don't know how to delete them
from the table.
Can anyone help ? Thanks.Hi,
See this:-
http://www.sqlteam.com/item.asp?ItemID=3331
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6256
http://support.microsoft.com/default.aspx?scid=kb;en-us;139444
Thanks
Hari
SQL Server MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:DAF8776C-7644-4B3A-83BC-4C6B02FF2607@.microsoft.com...
>I have a table with 580 rows of duplicate on total of 1101 rows. The
> duplicates are in 3 columns that were supposed to be primary key columns
> (Developers forgot to create the primary key in development-production is
> OK).
> I figured the duplicates with a query but I don't know how to delete them
> from the table.
> Can anyone help ? Thanks.

Eliminate Rows with Redundant Columns

I would like my query to return the KeyID from row 4 but from only one of
the first 3 rows where the address data is redundant. Since use of the key
eliminates the DISTINCT operator, is there another method?
DECLARE @.tAddress TABLE (KeyID int, Address varchar(10), City varchar(10),
ST varchar(2))
INSERT @.tAddress
SELECT 1, '100 Main', 'Boston', 'MA'
UNION ALL
SELECT 2, '100 Main', 'Boston', 'MA'
UNION ALL
SELECT 3, '100 Main', 'Boston', 'MA'
UNION ALL
SELECT 4, '200 Main', 'Boston', 'MA'
Thanks!SELECT Address, City, ST, MIN(KeyID)
FROM @.tAddress
GROUP BY Address, City, ST
Or, if you really only wanted the ID column:
SELECT MIN(KeyID)
FROM @.tAddress
GROUP BY Address, City, ST
Roy Harvey
Beacon Falls, CT
On Wed, 14 Jun 2006 09:47:32 -0700, "Mike Harbinger"
<MikeH@.Cybervillage.net> wrote:

>I would like my query to return the KeyID from row 4 but from only one of
>the first 3 rows where the address data is redundant. Since use of the key
>eliminates the DISTINCT operator, is there another method?
>DECLARE @.tAddress TABLE (KeyID int, Address varchar(10), City varchar(10),
>ST varchar(2))
>INSERT @.tAddress
>SELECT 1, '100 Main', 'Boston', 'MA'
>UNION ALL
>SELECT 2, '100 Main', 'Boston', 'MA'
>UNION ALL
>SELECT 3, '100 Main', 'Boston', 'MA'
>UNION ALL
>SELECT 4, '200 Main', 'Boston', 'MA'
>Thanks!
>|||Simple and elegant; I should have seen that. Many thanks Roy!
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:csf092h8em6sabqjre5jf7jsbmrhroovtc@.
4ax.com...
> SELECT Address, City, ST, MIN(KeyID)
> FROM @.tAddress
> GROUP BY Address, City, ST
> Or, if you really only wanted the ID column:
> SELECT MIN(KeyID)
> FROM @.tAddress
> GROUP BY Address, City, ST
> Roy Harvey
> Beacon Falls, CT
> On Wed, 14 Jun 2006 09:47:32 -0700, "Mike Harbinger"
> <MikeH@.Cybervillage.net> wrote:
>

Efficient way of purging old data?

I have a table with no primary key.

The are 6 columns. The first four columns represent one object. There is then a status and a datetime (Updated).

A new record is added into the table whenever a status changes (I need to keep the old status as well).

The problem I have is how to efficiently purge old data.

I need to keep all records with a datetime within the last week.

I also need to ensure that I at least one status is kept for each combination of the first four columns.

The table contains over 150,000 records. About 1,000 statuses change per day.

I want to purge the data once a week - delete about 7,000 records.

This is the query I wrote

delete from table1 where Updated <
(select max(Updated) from table1 t1 where table1.col1 = t1.col1 and table1.col2 = t1.col2
and table1.col3 = t1.col3 and table1.col4 = t1.col4 group by col1, col2, col3, col4)
and Updated < GetDate() - 7

This seems to be fairly fast - records only change a couple of times a week, so the subquery only returns 3 or 4 records. The subquery will be called on almost all the records in the table though.

Can anyone see a more efficient way of doing this?

Well, I'd use a dereived table myself:

delete from table1 t1
inner join
(select col1, col2, col3, col4, max(Updated) as MaxUpdated
from table1
group by col1, col2, col3, col4) t2 on t1.col1 = t2.col1 and t1.col2 = t2.col2 and t1.col3 = t2.col3 and t1.col4 = t2.col4 and t1.Updated = t2.MaxUpdated
where t1.Updated < GetDate() - 7

That usually seems to perform faster then a subquery, but you'd have to try it for yourself to know for sure.

Efficiency Question

In terms of the time a query would take to return, is there any difference
if a table has, say, 200 records and 100 columns vs. 100 records and 200
columns? Ultimately, I'm trying to figure out if speed needs to be a
consideration in determining whether to orient a new table horizontally or
vertically.
Thanks,
James
Size of the data returned rather than number of columns and rows is a more
relevant statistic but overall performance depends on other factors (such as
indexing) that are at least as important.

> Ultimately, I'm trying to figure out if speed needs to be a
> consideration in determining whether to orient a new table horizontally or
> vertically.
I would suggest that you're going about your table design the wrong way!
Normalize your design in (at minimum) Third Normal Form to start with. Once
you have done that you can test performance and make any optimizations
necesssary. Correct design doesn't normally involve choosing between
"horizontal" and "vertical" orientations of data.
David Portas
SQL Server MVP
|||Well, the design of this table is relatively simple and straightforward...if
we had even reasonable hardware. I've been having to design tables around
poor hardware performance...trying to keep as little data as possible in any
given table, in order to prevent a meltdown.
The design was fine until the hardware hit the proverbial wall and
everything started crashing when even a moderate size query was run. So
instead of sinking a few thousand dollars into new hardware, they sank a few
thousand dollars into man hours, trying to solve the problem with intellect.
So far it's been relatively unsuccessful.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:DJOdnT3qqPhoSmbdRVn-ow@.giganews.com...
> Size of the data returned rather than number of columns and rows is a more
> relevant statistic but overall performance depends on other factors (such
as[vbcol=seagreen]
> indexing) that are at least as important.
or
> I would suggest that you're going about your table design the wrong way!
> Normalize your design in (at minimum) Third Normal Form to start with.
Once
> you have done that you can test performance and make any optimizations
> necesssary. Correct design doesn't normally involve choosing between
> "horizontal" and "vertical" orientations of data.
> --
> David Portas
> SQL Server MVP
> --
>
|||> In terms of the time a query would take to return, is there any difference
> if a table has, say, 200 records and 100 columns vs. 100 records and 200
> columns? Ultimately, I'm trying to figure out if speed needs to be a
> consideration in determining whether to orient a new table horizontally or
> vertically.
Your table design should be driven by normalizing the entities you are
trying to represent, not optimizing number of columns vs. number of rows.
If performance is a problem due to hardware constraints, fix the hardware.
Anything you do in the context of the above will be a bandaid at best, and
will only serve to yield *different* performance problems down the line,
IMHO.
http://www.aspfaq.com/
(Reverse address to reply.)

Efficiency Question

In terms of the time a query would take to return, is there any difference
if a table has, say, 200 records and 100 columns vs. 100 records and 200
columns? Ultimately, I'm trying to figure out if speed needs to be a
consideration in determining whether to orient a new table horizontally or
vertically.
Thanks,
JamesSize of the data returned rather than number of columns and rows is a more
relevant statistic but overall performance depends on other factors (such as
indexing) that are at least as important.
> Ultimately, I'm trying to figure out if speed needs to be a
> consideration in determining whether to orient a new table horizontally or
> vertically.
I would suggest that you're going about your table design the wrong way!
Normalize your design in (at minimum) Third Normal Form to start with. Once
you have done that you can test performance and make any optimizations
necesssary. Correct design doesn't normally involve choosing between
"horizontal" and "vertical" orientations of data.
--
David Portas
SQL Server MVP
--|||Well, the design of this table is relatively simple and straightforward...if
we had even reasonable hardware. I've been having to design tables around
poor hardware performance...trying to keep as little data as possible in any
given table, in order to prevent a meltdown.
The design was fine until the hardware hit the proverbial wall and
everything started crashing when even a moderate size query was run. So
instead of sinking a few thousand dollars into new hardware, they sank a few
thousand dollars into man hours, trying to solve the problem with intellect.
So far it's been relatively unsuccessful.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:DJOdnT3qqPhoSmbdRVn-ow@.giganews.com...
> Size of the data returned rather than number of columns and rows is a more
> relevant statistic but overall performance depends on other factors (such
as
> indexing) that are at least as important.
> > Ultimately, I'm trying to figure out if speed needs to be a
> > consideration in determining whether to orient a new table horizontally
or
> > vertically.
> I would suggest that you're going about your table design the wrong way!
> Normalize your design in (at minimum) Third Normal Form to start with.
Once
> you have done that you can test performance and make any optimizations
> necesssary. Correct design doesn't normally involve choosing between
> "horizontal" and "vertical" orientations of data.
> --
> David Portas
> SQL Server MVP
> --
>|||> In terms of the time a query would take to return, is there any difference
> if a table has, say, 200 records and 100 columns vs. 100 records and 200
> columns? Ultimately, I'm trying to figure out if speed needs to be a
> consideration in determining whether to orient a new table horizontally or
> vertically.
Your table design should be driven by normalizing the entities you are
trying to represent, not optimizing number of columns vs. number of rows.
If performance is a problem due to hardware constraints, fix the hardware.
Anything you do in the context of the above will be a bandaid at best, and
will only serve to yield *different* performance problems down the line,
IMHO.
--
http://www.aspfaq.com/
(Reverse address to reply.)

Efficiency Question

In terms of the time a query would take to return, is there any difference
if a table has, say, 200 records and 100 columns vs. 100 records and 200
columns? Ultimately, I'm trying to figure out if speed needs to be a
consideration in determining whether to orient a new table horizontally or
vertically.
Thanks,
JamesSize of the data returned rather than number of columns and rows is a more
relevant statistic but overall performance depends on other factors (such as
indexing) that are at least as important.

> Ultimately, I'm trying to figure out if speed needs to be a
> consideration in determining whether to orient a new table horizontally or
> vertically.
I would suggest that you're going about your table design the wrong way!
Normalize your design in (at minimum) Third Normal Form to start with. Once
you have done that you can test performance and make any optimizations
necesssary. Correct design doesn't normally involve choosing between
"horizontal" and "vertical" orientations of data.
David Portas
SQL Server MVP
--|||Well, the design of this table is relatively simple and straightforward...if
we had even reasonable hardware. I've been having to design tables around
poor hardware performance...trying to keep as little data as possible in any
given table, in order to prevent a meltdown.
The design was fine until the hardware hit the proverbial wall and
everything started crashing when even a moderate size query was run. So
instead of sinking a few thousand dollars into new hardware, they sank a few
thousand dollars into man hours, trying to solve the problem with intellect.
So far it's been relatively unsuccessful.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:DJOdnT3qqPhoSmbdRVn-ow@.giganews.com...
> Size of the data returned rather than number of columns and rows is a more
> relevant statistic but overall performance depends on other factors (such
as
> indexing) that are at least as important.
>
or[vbcol=seagreen]
> I would suggest that you're going about your table design the wrong way!
> Normalize your design in (at minimum) Third Normal Form to start with.
Once
> you have done that you can test performance and make any optimizations
> necesssary. Correct design doesn't normally involve choosing between
> "horizontal" and "vertical" orientations of data.
> --
> David Portas
> SQL Server MVP
> --
>|||> In terms of the time a query would take to return, is there any difference
> if a table has, say, 200 records and 100 columns vs. 100 records and 200
> columns? Ultimately, I'm trying to figure out if speed needs to be a
> consideration in determining whether to orient a new table horizontally or
> vertically.
Your table design should be driven by normalizing the entities you are
trying to represent, not optimizing number of columns vs. number of rows.
If performance is a problem due to hardware constraints, fix the hardware.
Anything you do in the context of the above will be a bandaid at best, and
will only serve to yield *different* performance problems down the line,
IMHO.
http://www.aspfaq.com/
(Reverse address to reply.)

effect of more column in where clause

what are the effects of having number of columns in WHERE clause, ie: if we use more columns in where clause, what will be its impact on performance.

this is important for me to design queries

having a lot of unneccessary where clauses will surely slowdown the query if there are.

the best thing that you can do is to streamline the logic into its simplest form

|||Perhaps I misunderstand the question; my normal experience is that if the additional columns contribute to filtering out unwanted records that the additional columns in the where clause usually enhance performance.|||

yeah thats right but a poorly written where clause will definetely slowdown performance.

The trick is to use the best suited functions and keywords in the where clause

here some useful link:

http://www.sql-server-performance.com/transact_sql.asp

regards,

joey

|||

Hi joeydj,

what do you mean by "the trick is to use the best suited where clause by making use of the powerful sql server functions"?

AMB

|||

oh sorry. got a grammar problem

here i have it corrected

"The trick is to use the best suited functions and keywords in the where clause"

hmmm... thats better..

example:

select ... from

where x=1 and x=2 and x=7 and x=9

maybe written as

where x in (1,2,7,9)

|||

Joey is normally better than that; please cut him some slack. I think what he means is

select ... from

where x=1 OR x=2 OR x=7 OR x=9

maybe written as

where x in (1,2,7,9)

|||

hahaha. not been here for sometime.

hmmm thanks kent.

|||

Not to mention that there is no performance benefit in choosing one of these as opposed to the other, anyway. (There is a readability benefit, and the two forms will behave differently if you carelessly tack on AND <another condition> to the query.

Steve Kass

Drew University

http://www.stevekass.com

|||

Can you give an example of the choices you have to make? Usually if you add "more columns in where clause", you change the meaning of the query (but not always), and the first goal of designing a query is for it to ask the right question...

Steve Kass

Drew University

http://www.stevekass.com

|||

One thing that people sometimes forget is that a full table scan can sometimes be better than an indexed scan. If you are ultimately going to read every block of data from disk via an index scan then you might want to forgo the index. Modern databases with good statistics can usually establish an execution plan that is "good enough" but it can still pay to understand your data.

Here is a quick example.

Let's say I have a database of people and for some reason their geneder in overwhelmingly biased in one direction. An optimizer might look at the number of unique values for gender and assume a 50/50 split in the data. It might make good sence to always use an index on gender to access the rows of the table from the optimizer's best guess. However, that might not be the case in practice.

Let's say the table is 100k records with :

90% F

10% M

To find the men an indexed lookup on gender is probably a good thing. On the other hand an indexed lookup to find women will result in more disk IO and slower performance than a full table scan (not counting your network).

Knowing this distribution ahead of time might lead someone looking for all women to do something like

Select * from employees where gender + '' = 'F'

|||

thanks for all your views.

let me give u a specific example.

my database has "branch name" field in all the tables. and we have seperate copy of database for each branch. so a particular branch user will connect to his branch, which has records only for that branch.

in this situation, there is no need to filter the records again with "branch name", but if there is no performance issue , i wish to include it in the WHERE clause to be 100% sure that all the records that the query output does have the same branch name.

|||

In your situation. I think you could add "branch name" column without perfomance issue. But you need to create index or statictics for this column. As a result query optimizer understands that "branch name" same for all records and doesn't use them for plan.

But it any case your could check query plans and only after this decide

|||

This is a second good example of what I am talking about.

If your database is physically segragated by branch already then you want to make sure you know what the optimizer is doing when you add a "failsafe"

Where BranchId = 10

or alternatively

Where BranchName = 'Downtown'

to your queries.

If adding that clause causes the optimizer to include an index on BranchId as part of the execution plan then you will hurt your performance as a result of extra disk i/o and memory use. This might or might not be a concern for your infrastructure. Though it sounds like if you are physically partitioning your database by branch then you might be concerned about database performance.

Although it might seem counter intuative, it might be a good idea to experiment with:

Where BranchId + 0 = 10

or alternatively

Where BranchName + '' = 'Downtown'

This would ensure than no index on BranchId could be used in the execution plan.

|||

Dear AMERMSAMER,

The bottom line is that having more columns in the WHERE clause does not necessarily slow down performance and may, in fact, actually improve performance. It all depends on the indexes on the table and the "selectivity" of the columns in the WHERE. If there is a unique key and all of the columns in the key are "covered" by the WHERE clause, no more may be needed. SQL will try to optimize the query by using these columns where available.

effect of more column in where clause

what are the effects of having number of columns in WHERE clause, ie: if we use more columns in where clause, what will be its impact on performance.

this is important for me to design queries

having a lot of unneccessary where clauses will surely slowdown the query if there are.

the best thing that you can do is to streamline the logic into its simplest form

|||Perhaps I misunderstand the question; my normal experience is that if the additional columns contribute to filtering out unwanted records that the additional columns in the where clause usually enhance performance.|||

yeah thats right but a poorly written where clause will definetely slowdown performance.

The trick is to use the best suited functions and keywords in the where clause

here some useful link:

http://www.sql-server-performance.com/transact_sql.asp

regards,

joey

|||

Hi joeydj,

what do you mean by "the trick is to use the best suited where clause by making use of the powerful sql server functions"?

AMB

|||

oh sorry. got a grammar problem

here i have it corrected

"The trick is to use the best suited functions and keywords in the where clause"

hmmm... thats better..

example:

select ... from

where x=1 and x=2 and x=7 and x=9

maybe written as

where x in (1,2,7,9)

|||

Joey is normally better than that; please cut him some slack. I think what he means is

select ... from

where x=1 OR x=2 OR x=7 OR x=9

maybe written as

where x in (1,2,7,9)

|||

hahaha. not been here for sometime.

hmmm thanks kent.

|||

Not to mention that there is no performance benefit in choosing one of these as opposed to the other, anyway. (There is a readability benefit, and the two forms will behave differently if you carelessly tack on AND <another condition> to the query.

Steve Kass

Drew University

http://www.stevekass.com

|||

Can you give an example of the choices you have to make? Usually if you add "more columns in where clause", you change the meaning of the query (but not always), and the first goal of designing a query is for it to ask the right question...

Steve Kass

Drew University

http://www.stevekass.com

|||

One thing that people sometimes forget is that a full table scan can sometimes be better than an indexed scan. If you are ultimately going to read every block of data from disk via an index scan then you might want to forgo the index. Modern databases with good statistics can usually establish an execution plan that is "good enough" but it can still pay to understand your data.

Here is a quick example.

Let's say I have a database of people and for some reason their geneder in overwhelmingly biased in one direction. An optimizer might look at the number of unique values for gender and assume a 50/50 split in the data. It might make good sence to always use an index on gender to access the rows of the table from the optimizer's best guess. However, that might not be the case in practice.

Let's say the table is 100k records with :

90% F

10% M

To find the men an indexed lookup on gender is probably a good thing. On the other hand an indexed lookup to find women will result in more disk IO and slower performance than a full table scan (not counting your network).

Knowing this distribution ahead of time might lead someone looking for all women to do something like

Select * from employees where gender + '' = 'F'

|||

thanks for all your views.

let me give u a specific example.

my database has "branch name" field in all the tables. and we have seperate copy of database for each branch. so a particular branch user will connect to his branch, which has records only for that branch.

in this situation, there is no need to filter the records again with "branch name", but if there is no performance issue , i wish to include it in the WHERE clause to be 100% sure that all the records that the query output does have the same branch name.

|||

In your situation. I think you could add "branch name" column without perfomance issue. But you need to create index or statictics for this column. As a result query optimizer understands that "branch name" same for all records and doesn't use them for plan.

But it any case your could check query plans and only after this decide

|||

This is a second good example of what I am talking about.

If your database is physically segragated by branch already then you want to make sure you know what the optimizer is doing when you add a "failsafe"

Where BranchId = 10

or alternatively

Where BranchName = 'Downtown'

to your queries.

If adding that clause causes the optimizer to include an index on BranchId as part of the execution plan then you will hurt your performance as a result of extra disk i/o and memory use. This might or might not be a concern for your infrastructure. Though it sounds like if you are physically partitioning your database by branch then you might be concerned about database performance.

Although it might seem counter intuative, it might be a good idea to experiment with:

Where BranchId + 0 = 10

or alternatively

Where BranchName + '' = 'Downtown'

This would ensure than no index on BranchId could be used in the execution plan.

|||

Dear AMERMSAMER,

The bottom line is that having more columns in the WHERE clause does not necessarily slow down performance and may, in fact, actually improve performance. It all depends on the indexes on the table and the "selectivity" of the columns in the WHERE. If there is a unique key and all of the columns in the key are "covered" by the WHERE clause, no more may be needed. SQL will try to optimize the query by using these columns where available.