Delete duplicate entries from tables in my database using Query Analyzer

Hello,

How can I delete duplicate entries from tables in my database using Query Analyzer, as there are many duplicate entries in my tables, I want to delete them.

Thanks in advance,
Uday.Does this table contains any unique key or any other key field?|||Hi,

There is seperate id for each entries but duplicate entries have the same id number.

Thanks in advance,
Uday.|||One solution could be adding identity column to this and then deleting the non relevent data.|||You can move all the duplicate ones into a separate temp table using GROUP BY HAVING COUNT(*)>1

Then you delete them using the same clause can use a SELECT DISTINCT to copy them back from the temp table.

Of course if your table is small you can just copy the lot and do a SELECT DISTINCT back!

Delete doesn''t delete rows, but @@ROWCOUNT says it did

I ran the following query in Query Analyzer on a machine running SQL Server 2000. I'm attempting to delete from a linked server running SQL Server 2005:

DELETE FROM sql2005.production.dbo.products
WHERE vendor='Foo'
AND productId NOT IN
(
SELECT productId FROM sql2000.staging.dbo.fooProductList
)

The status message (and @.@.ROWCOUNT) told me 8 rows were affected, but nothing was actually deleted; when I ran a SELECT with the same criteria as the DELETE, all 8 rows are still there. So, once more I tried the DELETE command. This time it told me 7 rows were affected; when I ran the SELECT again, 5 of the rows were still there. Finally, after running this exact same DELETE query 5 times, I was able to remove all 8 rows. Each time it would tell me that a different number of rows had been deleted, and in no case was that number accurate.

I've never seen anything like this before. Neither of the tables involved were undergoing any other changes. There's no replication going on, or anything else that should introduce any delays. And I run queries like this all day, involving every thinkable combination of 2000 and 2005 servers, that don't give me any trouble.

Does anyone have suggestions on what might cause this sort of behavior?
just wondering if any of these options were enable

1- SET ROWCOUNT=1
2- Any Trigger on 2005 tables?
|||

Most likely this effect is due to triggers.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Delete doesn''t delete rows, but @@ROWCOUNT says it did

I ran the following query in Query Analyzer on a machine running SQL Server 2000. I'm attempting to delete from a linked server running SQL Server 2005:

DELETE FROM sql2005.production.dbo.products
WHERE vendor='Foo'
AND productId NOT IN
(
SELECT productId FROM sql2000.staging.dbo.fooProductList
)

The status message (and @.@.ROWCOUNT) told me 8 rows were affected, but nothing was actually deleted; when I ran a SELECT with the same criteria as the DELETE, all 8 rows are still there. So, once more I tried the DELETE command. This time it told me 7 rows were affected; when I ran the SELECT again, 5 of the rows were still there. Finally, after running this exact same DELETE query 5 times, I was able to remove all 8 rows. Each time it would tell me that a different number of rows had been deleted, and in no case was that number accurate.

I've never seen anything like this before. Neither of the tables involved were undergoing any other changes. There's no replication going on, or anything else that should introduce any delays. And I run queries like this all day, involving every thinkable combination of 2000 and 2005 servers, that don't give me any trouble.

Does anyone have suggestions on what might cause this sort of behavior?
just wondering if any of these options were enable

1- SET ROWCOUNT=1
2- Any Trigger on 2005 tables?
|||

Most likely this effect is due to triggers.

Jens K. Suessmeyer

http://www.sqlserver2005.de

sql

Delete doesn''t delete rows, but @@ROWCOUNT says it did

I ran the following query in Query Analyzer on a machine running SQL Server 2000. I'm attempting to delete from a linked server running SQL Server 2005:

DELETE FROM sql2005.production.dbo.products
WHERE vendor='Foo'
AND productId NOT IN
(
SELECT productId FROM sql2000.staging.dbo.fooProductList
)

The status message (and @.@.ROWCOUNT) told me 8 rows were affected, but nothing was actually deleted; when I ran a SELECT with the same criteria as the DELETE, all 8 rows are still there. So, once more I tried the DELETE command. This time it told me 7 rows were affected; when I ran the SELECT again, 5 of the rows were still there. Finally, after running this exact same DELETE query 5 times, I was able to remove all 8 rows. Each time it would tell me that a different number of rows had been deleted, and in no case was that number accurate.

I've never seen anything like this before. Neither of the tables involved were undergoing any other changes. There's no replication going on, or anything else that should introduce any delays. And I run queries like this all day, involving every thinkable combination of 2000 and 2005 servers, that don't give me any trouble.

Does anyone have suggestions on what might cause this sort of behavior?
just wondering if any of these options were enable

1- SET ROWCOUNT=1
2- Any Trigger on 2005 tables?
|||

Most likely this effect is due to triggers.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Defrag on SAN disk ?

I ran the standard Windows Disk Defrag analyzer on my SQL Servers, and wow is
there lots of fragmentation, BUT, they are all on SAN disk, so my question
is, will there be any value in running the defrag ?
I do plan to run it when SQL is not running, that sounds like a good idea.
Jim,
Interesting question. Who is the SAN vendor? SANs store data differently
to "normal" file systems. Blocks do not get overwritten (usually), but a
new block gets written when data changes. So, I'm not entirely sure what
would happen if you ran a disk defrag tool on a SAN volume.
I would first of all make sure that you don't have any SQL Server
fragmentation using DBCC SHOWCONTIG. If this is all OK, then consider
talking to your infrastructure team about this, failing that speak to
the SAN vendor.
I don't think I would want to defrag a SAN, but I'm not 100% sure.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Jim Trowbridge wrote:
> I ran the standard Windows Disk Defrag analyzer on my SQL Servers, and wow is
> there lots of fragmentation, BUT, they are all on SAN disk, so my question
> is, will there be any value in running the defrag ?
> I do plan to run it when SQL is not running, that sounds like a good idea.
>
|||I agree. FWIW, our EMC/Dell Engineer told us defragging was not necessary
on our CX series.
Mark Allison wrote:[vbcol=seagreen]
> Jim,
> Interesting question. Who is the SAN vendor? SANs store data
> differently to "normal" file systems. Blocks do not get overwritten
> (usually), but a new block gets written when data changes. So, I'm
> not entirely sure what would happen if you ran a disk defrag tool on
> a SAN volume.
> I would first of all make sure that you don't have any SQL Server
> fragmentation using DBCC SHOWCONTIG. If this is all OK, then consider
> talking to your infrastructure team about this, failing that speak to
> the SAN vendor.
> I don't think I would want to defrag a SAN, but I'm not 100% sure.
>
> Jim Trowbridge wrote:
|||Tell your engineer he is full of sxxx<g>. While a large amount of cache may
abstract some aspects of data being read and written to disk there is always
the fact fragmentation can lead to pages that are not as full as you would
like. If the page is half empty on disk it will be half empty when read
into the sql server data cache as well. This means you can only have half
the amount of data or indexes in cache at any one time. It also means lots
more I/O's (even if they are logical) and that means lots more cpu ect.
Andrew J. Kelly SQL MVP
"Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
news:ehM3E8ioEHA.2784@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> I agree. FWIW, our EMC/Dell Engineer told us defragging was not necessary
> on our CX series.
>
> Mark Allison wrote:
a
>
|||I was answering the file-system fragmentation question, not the data and
index fragmentation question. I did not mean to imply one shouldn't handle
the database fragmentation if the data stores are on a SAN. OK, that being
said, I shot an email to our Engineer and asked again about running a
windows defragmentation on our SAN and he said absolutely keep it defragged
with hard disk defragmentation tool, so regardless of _what_ I was talking
about, I was still wrong. :-O
Thanks Andrew. It's probably beer-thirty for me anyway.
Eric
Andrew J. Kelly wrote:[vbcol=seagreen]
> Tell your engineer he is full of sxxx<g>. While a large amount of
> cache may abstract some aspects of data being read and written to
> disk there is always the fact fragmentation can lead to pages that
> are not as full as you would like. If the page is half empty on disk
> it will be half empty when read into the sql server data cache as
> well. This means you can only have half the amount of data or
> indexes in cache at any one time. It also means lots more I/O's
> (even if they are logical) and that means lots more cpu ect.
>
> "Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
> news:ehM3E8ioEHA.2784@.TK2MSFTNGP14.phx.gbl...
|||Have one for me too<g>.
Andrew J. Kelly SQL MVP
"Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
news:%230lCGploEHA.868@.TK2MSFTNGP10.phx.gbl...
> I was answering the file-system fragmentation question, not the data and
> index fragmentation question. I did not mean to imply one shouldn't
handle
> the database fragmentation if the data stores are on a SAN. OK, that
being
> said, I shot an email to our Engineer and asked again about running a
> windows defragmentation on our SAN and he said absolutely keep it
defragged
> with hard disk defragmentation tool, so regardless of _what_ I was talking
> about, I was still wrong. :-O
> Thanks Andrew. It's probably beer-thirty for me anyway.
> Eric
>
> Andrew J. Kelly wrote:
>
|||"Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
news:%230lCGploEHA.868@.TK2MSFTNGP10.phx.gbl...
> I was answering the file-system fragmentation question, not the data and
> index fragmentation question. I did not mean to imply one shouldn't
handle
> the database fragmentation if the data stores are on a SAN. OK, that
being
> said, I shot an email to our Engineer and asked again about running a
> windows defragmentation on our SAN and he said absolutely keep it
defragged
> with hard disk defragmentation tool, so regardless of _what_ I was talking
> about, I was still wrong. :-O
>
Not necessarily.
Windows defrag may do nothing on the SAN. Oh, the SAN will report it done,
etc, but it may virtualize away the actions and no real difference will
happen.
Again, it depends a lot on the SAN.

> Thanks Andrew. It's probably beer-thirty for me anyway.
> Eric

Defrag on SAN disk ?

I ran the standard Windows Disk Defrag analyzer on my SQL Servers, and wow is
there lots of fragmentation, BUT, they are all on SAN disk, so my question
is, will there be any value in running the defrag ?
I do plan to run it when SQL is not running, that sounds like a good idea.Jim,
Interesting question. Who is the SAN vendor? SANs store data differently
to "normal" file systems. Blocks do not get overwritten (usually), but a
new block gets written when data changes. So, I'm not entirely sure what
would happen if you ran a disk defrag tool on a SAN volume.
I would first of all make sure that you don't have any SQL Server
fragmentation using DBCC SHOWCONTIG. If this is all OK, then consider
talking to your infrastructure team about this, failing that speak to
the SAN vendor.
I don't think I would want to defrag a SAN, but I'm not 100% sure.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Jim Trowbridge wrote:
> I ran the standard Windows Disk Defrag analyzer on my SQL Servers, and wow is
> there lots of fragmentation, BUT, they are all on SAN disk, so my question
> is, will there be any value in running the defrag ?
> I do plan to run it when SQL is not running, that sounds like a good idea.
>|||I agree. FWIW, our EMC/Dell Engineer told us defragging was not necessary
on our CX series.
Mark Allison wrote:
> Jim,
> Interesting question. Who is the SAN vendor? SANs store data
> differently to "normal" file systems. Blocks do not get overwritten
> (usually), but a new block gets written when data changes. So, I'm
> not entirely sure what would happen if you ran a disk defrag tool on
> a SAN volume.
> I would first of all make sure that you don't have any SQL Server
> fragmentation using DBCC SHOWCONTIG. If this is all OK, then consider
> talking to your infrastructure team about this, failing that speak to
> the SAN vendor.
> I don't think I would want to defrag a SAN, but I'm not 100% sure.
>
> Jim Trowbridge wrote:
>> I ran the standard Windows Disk Defrag analyzer on my SQL Servers,
>> and wow is there lots of fragmentation, BUT, they are all on SAN
>> disk, so my question is, will there be any value in running the
>> defrag ? I do plan to run it when SQL is not running, that sounds like a
>> good
>> idea.|||Tell your engineer he is full of sxxx<g>. While a large amount of cache may
abstract some aspects of data being read and written to disk there is always
the fact fragmentation can lead to pages that are not as full as you would
like. If the page is half empty on disk it will be half empty when read
into the sql server data cache as well. This means you can only have half
the amount of data or indexes in cache at any one time. It also means lots
more I/O's (even if they are logical) and that means lots more cpu ect.
--
Andrew J. Kelly SQL MVP
"Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
news:ehM3E8ioEHA.2784@.TK2MSFTNGP14.phx.gbl...
> I agree. FWIW, our EMC/Dell Engineer told us defragging was not necessary
> on our CX series.
>
> Mark Allison wrote:
> > Jim,
> >
> > Interesting question. Who is the SAN vendor? SANs store data
> > differently to "normal" file systems. Blocks do not get overwritten
> > (usually), but a new block gets written when data changes. So, I'm
> > not entirely sure what would happen if you ran a disk defrag tool on
> > a SAN volume.
> > I would first of all make sure that you don't have any SQL Server
> > fragmentation using DBCC SHOWCONTIG. If this is all OK, then consider
> > talking to your infrastructure team about this, failing that speak to
> > the SAN vendor.
> >
> > I don't think I would want to defrag a SAN, but I'm not 100% sure.
> >
> >
> > Jim Trowbridge wrote:
> >> I ran the standard Windows Disk Defrag analyzer on my SQL Servers,
> >> and wow is there lots of fragmentation, BUT, they are all on SAN
> >> disk, so my question is, will there be any value in running the
> >> defrag ? I do plan to run it when SQL is not running, that sounds like
a
> >> good
> >> idea.
>|||I was answering the file-system fragmentation question, not the data and
index fragmentation question. I did not mean to imply one shouldn't handle
the database fragmentation if the data stores are on a SAN. OK, that being
said, I shot an email to our Engineer and asked again about running a
windows defragmentation on our SAN and he said absolutely keep it defragged
with hard disk defragmentation tool, so regardless of _what_ I was talking
about, I was still wrong. :-O
Thanks Andrew. It's probably beer-thirty for me anyway.
Eric
Andrew J. Kelly wrote:
> Tell your engineer he is full of sxxx<g>. While a large amount of
> cache may abstract some aspects of data being read and written to
> disk there is always the fact fragmentation can lead to pages that
> are not as full as you would like. If the page is half empty on disk
> it will be half empty when read into the sql server data cache as
> well. This means you can only have half the amount of data or
> indexes in cache at any one time. It also means lots more I/O's
> (even if they are logical) and that means lots more cpu ect.
>
> "Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
> news:ehM3E8ioEHA.2784@.TK2MSFTNGP14.phx.gbl...
>> I agree. FWIW, our EMC/Dell Engineer told us defragging was not
>> necessary on our CX series.
>>
>> Mark Allison wrote:
>> Jim,
>> Interesting question. Who is the SAN vendor? SANs store data
>> differently to "normal" file systems. Blocks do not get overwritten
>> (usually), but a new block gets written when data changes. So, I'm
>> not entirely sure what would happen if you ran a disk defrag tool on
>> a SAN volume.
>> I would first of all make sure that you don't have any SQL Server
>> fragmentation using DBCC SHOWCONTIG. If this is all OK, then
>> consider talking to your infrastructure team about this, failing
>> that speak to the SAN vendor.
>> I don't think I would want to defrag a SAN, but I'm not 100% sure.
>>
>> Jim Trowbridge wrote:
>> I ran the standard Windows Disk Defrag analyzer on my SQL Servers,
>> and wow is there lots of fragmentation, BUT, they are all on SAN
>> disk, so my question is, will there be any value in running the
>> defrag ? I do plan to run it when SQL is not running, that sounds
>> like a good
>> idea.|||Have one for me too<g>.
Andrew J. Kelly SQL MVP
"Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
news:%230lCGploEHA.868@.TK2MSFTNGP10.phx.gbl...
> I was answering the file-system fragmentation question, not the data and
> index fragmentation question. I did not mean to imply one shouldn't
handle
> the database fragmentation if the data stores are on a SAN. OK, that
being
> said, I shot an email to our Engineer and asked again about running a
> windows defragmentation on our SAN and he said absolutely keep it
defragged
> with hard disk defragmentation tool, so regardless of _what_ I was talking
> about, I was still wrong. :-O
> Thanks Andrew. It's probably beer-thirty for me anyway.
> Eric
>
> Andrew J. Kelly wrote:
> > Tell your engineer he is full of sxxx<g>. While a large amount of
> > cache may abstract some aspects of data being read and written to
> > disk there is always the fact fragmentation can lead to pages that
> > are not as full as you would like. If the page is half empty on disk
> > it will be half empty when read into the sql server data cache as
> > well. This means you can only have half the amount of data or
> > indexes in cache at any one time. It also means lots more I/O's
> > (even if they are logical) and that means lots more cpu ect.
> >
> >
> > "Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
> > news:ehM3E8ioEHA.2784@.TK2MSFTNGP14.phx.gbl...
> >> I agree. FWIW, our EMC/Dell Engineer told us defragging was not
> >> necessary on our CX series.
> >>
> >>
> >> Mark Allison wrote:
> >> Jim,
> >>
> >> Interesting question. Who is the SAN vendor? SANs store data
> >> differently to "normal" file systems. Blocks do not get overwritten
> >> (usually), but a new block gets written when data changes. So, I'm
> >> not entirely sure what would happen if you ran a disk defrag tool on
> >> a SAN volume.
> >> I would first of all make sure that you don't have any SQL Server
> >> fragmentation using DBCC SHOWCONTIG. If this is all OK, then
> >> consider talking to your infrastructure team about this, failing
> >> that speak to the SAN vendor.
> >>
> >> I don't think I would want to defrag a SAN, but I'm not 100% sure.
> >>
> >>
> >> Jim Trowbridge wrote:
> >> I ran the standard Windows Disk Defrag analyzer on my SQL Servers,
> >> and wow is there lots of fragmentation, BUT, they are all on SAN
> >> disk, so my question is, will there be any value in running the
> >> defrag ? I do plan to run it when SQL is not running, that sounds
> >> like a good
> >> idea.
>|||"Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
news:%230lCGploEHA.868@.TK2MSFTNGP10.phx.gbl...
> I was answering the file-system fragmentation question, not the data and
> index fragmentation question. I did not mean to imply one shouldn't
handle
> the database fragmentation if the data stores are on a SAN. OK, that
being
> said, I shot an email to our Engineer and asked again about running a
> windows defragmentation on our SAN and he said absolutely keep it
defragged
> with hard disk defragmentation tool, so regardless of _what_ I was talking
> about, I was still wrong. :-O
>
Not necessarily.
Windows defrag may do nothing on the SAN. Oh, the SAN will report it done,
etc, but it may virtualize away the actions and no real difference will
happen.
Again, it depends a lot on the SAN.
> Thanks Andrew. It's probably beer-thirty for me anyway.
> Eric

Define a Key - "select * from "

Is there a way to define a key that puts the text 'SELECT * FROM '
into the Query Analyzer window?
I must type this about 50 times a day but cannot see a simple way of
defining a key to write it for me..
(tools/customize) seems to execute everything you put in there rather
than leave it on the screen for me to add table names etc to.

thanks for your time...On Jul 9, 10:39 am, Stagnight1 <stagnig...@.yahoo.comwrote:

Quote:

Originally Posted by

Is there a way to define a key that puts the text 'SELECT * FROM '
into the Query Analyzer window?
I must type this about 50 times a day but cannot see a simple way of
defining a key to write it for me..
(tools/customize) seems to execute everything you put in there rather
than leave it on the screen for me to add table names etc to.
>
thanks for your time...

You can assign it to Ctrl-V by putting it on the clipboard.|||Stagnight1 wrote:

Quote:

Originally Posted by

Is there a way to define a key that puts the text 'SELECT * FROM '
into the Query Analyzer window?
I must type this about 50 times a day but cannot see a simple way of
defining a key to write it for me..
(tools/customize) seems to execute everything you put in there rather
than leave it on the screen for me to add table names etc to.

I haven't tried it, but
http://www.regsoft.com/keys/
others may be available
http://www.google.com/search?hl=en&...G=Google+Search
Andrew

Default templates in Query Analyzer

Hi all,

I've installed SQL Server 2000(SP2). The thing is in Query
Analyzer Templates tab, I can see all the templates folders
(eg: Attach and Detach Database, Create Database, etc) but
I can't seem to expand them. Looks like there's nothing in
it. I've specify my templates directory path under Options
but still there's no templates under those folders. Can
anyone help me?

Thank you in advance.
..In my Options settings, templates are specified to be in
C:\Program\Microsoft SQL Server\80\Tools\Templates\SQL Query Analyzer

The .tsql files are in subdirectories under that catalog.
Works fine with the Insert Templates menu option.|||Yep...works fine with the Insert Templates menu option. I've installed SP2, checked the templates file path in OPTIONS, and all the *.tql files are in C:\Program Files\Microsoft SQL Server\80\Tools\Templates\SQL Query Analyzer folder...
Anyone know what is going on?|||Where in Q.A. is that Templates tab where you can't expand?
I couldn't find it. If guide me , I'll have a look how it works at my machine.|||Can you post a snapshot of where you are having the problem in query analyzer ?|||Attached will be a screenshot of the problem...|||Try the following:

Change the directory option for the default template directory. See what it displays - then change it back.

Create your own template - save it in a template directory like create database and save it in the parent directory as well.

The template in the parent directory should appear directory under your templates folder in qa and under the create database folder - you may have to right on the folder templates and create database and refresh.

Let me know what happens.|||Hi rnealejr and all,

Tried changing the directory option for the default template directory. When I did a refresh, all the folders under Templates are gone. Changed back the templates defaulti path, same problem...folders that I can't expand.

Created my own template, save it to the templates directory under "Create Database", did a refresh, same result...folders that I can't expand even I did a couple of refreshes.

Any suggestions?

Thank you|||Create a template file in the parent directory - you should see the template file along with all the folders - let me know. Also, did you install the following service pack SQL2KSP2.exe ? And of course, the inevitable question - did you try to reinstall ?|||Created a template file in the parent directory, but still the same. Yes, I've installed SP2. I haven't tried to reinstalling yet.

Thank you|||Well since you have verified that it is not a folder mapping issue and no known documentation exists detailing this issue - I would reinstall.

Good luck.