Delete Duplicate Rec

HI EveryOne

I am facing one prblem that is :
How can be delete duplicate records from table(Database) using query and Sub Query. If any one knows then PLz Forward me at
amit_mant@.rediffmail.com

Quote:

Originally Posted by amitmant

HI EveryOne

I am facing one prblem that is :
How can be delete duplicate records from table(Database) using query and Sub Query. If any one knows then PLz Forward me at
amit_mant@.rediffmail.com

this is the Qry to delete duplicate records from table(Database)

===================QRY======================
delete from table1 where Field1=(select field1 from table1 group by field1 having count(field1)>1)|||This way you will delete all records that have duplicates but if you wish to save single instance it is dangerous proposition.|||

Quote:

Originally Posted by iburyak

This way you will delete all records that have duplicates but if you wish to save single instance it is dangerous proposition.

Can you explain the concept of single instance it is dangerous proposition.sql

Delete any records in table 1 that are not in table 2 (tables joined on 2 columns)

I have two tables. The primary key for both tables is comprised of 2 columns.

How do I delete any records in table 1 that are not in table 2.

e.g.

if this was the data in the 2 tables

Table 1

col1 col2 col 3

0 0 0

0 1 0

Table 2

col1 col2 col3

0 0 0

I would like to delete the records containg the values 0, 1, 0 from table 1 because the is no record in table 2 with the values 0, 1 as the primary key.

Hi,

You can do this as follow (by suggesting that COL1 and COL2 are the key columns):

DELETE FROM table1
FROM table1
LEFT OUTER JOIN table2
ON table1.COL1 = table2.col1
AND table1.COL2 = table2.col2
WHERE table2.COL1 IS NULL

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||Thanks a million. It seems strange to me the way the From caluse is included twice but it works!|||

the following query will do..

Delete from table1 Where Not Exists(Select 1 From table2 Where table1.Col1=Table2.Col1 and Table1.Col2=Table2.Col2)

Delete and insert data in same transaction!

Hi!
I try to add records in one table from a VB6 com+ component using ADO
(provider=SQLOLEDB, SQL-server2000).
Basically what I try to do is to
1. Start a transaction
2. Delete old records in one table
3. Add new values in the same table
4. commit transaction
The problem is that I get a duplicate key when I insert my new data in
step3. If I commit the data between step 2 and 3 everything works fine. If
step 4 then fails I will end up with no data in the table which I don't want
(i mean that is what transaction is used for).
This must be a pretty common scenario so I hope there will be a solution
that will not force me to commit the transaction in the middle.
Regards
/HansHi Hans,
as you descibed the scenario should be fine, allowing the transaction
to commit without problems, perhaps there is a logical problem in your
code. Could you please post the code here. This would make error
searching much easier for us.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Hans wrote:
> Hi!
> I try to add records in one table from a VB6 com+ component using ADO
> (provider=SQLOLEDB, SQL-server2000).
> Basically what I try to do is to
> 1. Start a transaction
> 2. Delete old records in one table
> 3. Add new values in the same table
> 4. commit transaction
> The problem is that I get a duplicate key when I insert my new data in
> step3. If I commit the data between step 2 and 3 everything works fine. If
> step 4 then fails I will end up with no data in the table which I don't wa
nt
> (i mean that is what transaction is used for).
> This must be a pretty common scenario so I hope there will be a solution
> that will not force me to commit the transaction in the middle.
> Regards
> /Hans
Do you mean you want to delete and then insert new row(s) with the same
key values? That may not be an optimal solution since you could
accomplish the same thing with an UPDATE. The following works for me.
If this example doesn't help then please post some code so that we can
reproduce the problem.
CREATE TABLE tbl (x INT PRIMARY KEY);
INSERT INTO tbl(x) VALUES (1);
BEGIN TRAN;
DELETE FROM tbl WHERE x=1;
INSERT INTO tbl(x) VALUES (1);
COMMIT TRAN;
SELECT x FROM tbl;
I recommend you put the DELETE/INSERT code in a stored procedure and
execute the proc from your VB code.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> The problem is that I get a duplicate key when I insert my new data in
> step3.
This indicates that INSERTs are occurring on a different connection that the
DELETE and not within the same transaction context. You can run a SQL
Profiler trace to see the actual behavior.
Note that ADO is particularly nasty about opening additional connections
behind your back. It is important to include 'SET NOCOUNT ON' in
procs/scripts and process all results returned so that connections can be
reused.
Hope this helps.
Dan Guzman
SQL Server MVP
"Hans" <hansb@.sorry.nospam.com> wrote in message
news:OXTNpYnSGHA.2156@.tk2msftngp13.phx.gbl...
> Hi!
> I try to add records in one table from a VB6 com+ component using ADO
> (provider=SQLOLEDB, SQL-server2000).
> Basically what I try to do is to
> 1. Start a transaction
> 2. Delete old records in one table
> 3. Add new values in the same table
> 4. commit transaction
> The problem is that I get a duplicate key when I insert my new data in
> step3. If I commit the data between step 2 and 3 everything works fine. If
> step 4 then fails I will end up with no data in the table which I don't
> want
> (i mean that is what transaction is used for).
> This must be a pretty common scenario so I hope there will be a solution
> that will not force me to commit the transaction in the middle.
> Regards
> /Hans
>
>|||Hi Jens, David and Dan!
Thanks for your replies.
Here is the code. The code is used to store default values for a user. The
table have fields for which user it is (idUser), which field (idfld) and
some other fields about the default values. The code is most likely not the
most efficient (the "IN" operator is slow but we are talking about pretty
small tables here with a couple of 1000 records) but it is only executed a
couple of times/year for a normal user (and it only takes like 100
milliseconds to excecute as it is). The key in the table is idUser (user id)
and idfld (Field id) together and I'm not sure if there is a way to update
or add in one single SQL-statement and also delete records where I earlier
had defaultvalues but where the user no longer want to have default values.
Therfor I delete all defaultvalues for the current user for the current
table (I join in another table which holds the table id). For example there
may be 5 rows before the save and maybe only 3 rows left in the table after
the update.
If OS_WIN2000 Then
Set rs = CreateObject("ADODB.Recordset")
Set con = CreateObject("ADODB.Connection")
Else
Set rs = CtxCreateObject("ADODB.Recordset")
Set con = CtxCreateObject("ADODB.Connection")
End If
con.Open GetConnectionString()
'Use transaction
con.BeginTrans
rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic
rs.LockType = adLockOptimistic
'idtbl and idUser is already singlequoted
For i = LBound(userList) To UBound(userList)
sSQL = "Delete from " & TSP("vmo_base_defaultvalues") & " where iduser="
& userList(i) & " and "
sSQL = sSQL & " idfld in (select vmo_base_field.idfld from " &
TSP("vmo_base_defaultvalues") & ","
sSQL = sSQL & TSP("vmo_base_field") & " where
vmo_base_field.idfld=vmo_base_defaultvalues.idfld "
sSQL = sSQL & " and vmo_base_field.idtbl = " & idTBL & ")"
rs.Open sSQL, con
'If I commit here it works OK but I want to commit after the entire
operation is finished
For l = LBound(sArg) To UBound(sArg) Step 4
If sArg(l) <> "" And sArg(l + 1) <> "" Then
sSQL = "Insert into " & TSP("vmo_base_defaultvalues") & "
(idUser,idfld,vValue,datevalue, deftype) " & _
"values (" & _
userList(i) & "," & _
sArg(l) & "," & _
sArg(l + 1) & "," & _
sArg(l + 2) & "," & _
sArg(l + 3) & ")"
rs.Open sSQL, con
End If
Next l
Next i
con.CommitTrans
Regards
/Hans|||The main problem is that you are using recordsets when no data are returned.
Additional connections are probably acquired for the subsequent INSERT
statements and outside scope of the first transaction.
The example below shows how to use Command objects for these DML statements.
I would also suggest using parameters instead of concatenating literal
values. Parameters are more secure, eliminate the need to quote values,
escape quotes, format dates, etc.
Set con = CreateObject("ADODB.Connection")
Set cmd = CreateObject("ADODB.Command")
con.Open GetConnectionString()
cmd.ActiveConnection = con
con.BeginTrans
'idtbl and idUser is already singlequoted
For i = LBound(userList) To UBound(userList)
sSQL = "SET NOCOUNT ON Delete from " & _
TSP("vmo_base_defaultvalues") & _
" where iduser=" & _
userList(i) & " and "
sSQL = sSQL & " idfld in (select vmo_base_field.idfld from " &
TSP("vmo_base_defaultvalues") & ","
sSQL = sSQL & TSP("vmo_base_field") & _
" where vmo_base_field.idfld=vmo_base_defaultvalues.idfld "
sSQL = sSQL & " and vmo_base_field.idtbl = " & idTBL & ")"
cmd.CommandText = sSQL
cmd.Execute
For l = LBound(sArg) To UBound(sArg) Step 4
If sArg(l) <> "" And sArg(l + 1) <> "" Then
sSQL = "SET NOCOUNT ON Insert into " & _
TSP("vmo_base_defaultvalues") & _
"(idUser,idfld,vValue,datevalue, deftype) " & _
"values (" & _
userList(i) & "," & _
sArg(l) & "," & _
sArg(l + 1) & "," & _
sArg(l + 2) & "," & _
sArg(l + 3) & ")"
cmd.CommandText = sSQL
cmd.Execute
End If
Next l
Next i
con.CommitTrans
Hope this helps.
Dan Guzman
SQL Server MVP
"Hans" <hansb@.sorry.nospam.com> wrote in message
news:%23%23EoJT2SGHA.1148@.TK2MSFTNGP10.phx.gbl...
> Hi Jens, David and Dan!
> Thanks for your replies.
> Here is the code. The code is used to store default values for a user. The
> table have fields for which user it is (idUser), which field (idfld) and
> some other fields about the default values. The code is most likely not
> the
> most efficient (the "IN" operator is slow but we are talking about pretty
> small tables here with a couple of 1000 records) but it is only executed a
> couple of times/year for a normal user (and it only takes like 100
> milliseconds to excecute as it is). The key in the table is idUser (user
> id)
> and idfld (Field id) together and I'm not sure if there is a way to update
> or add in one single SQL-statement and also delete records where I earlier
> had defaultvalues but where the user no longer want to have default
> values.
> Therfor I delete all defaultvalues for the current user for the current
> table (I join in another table which holds the table id). For example
> there
> may be 5 rows before the save and maybe only 3 rows left in the table
> after
> the update.
> If OS_WIN2000 Then
> Set rs = CreateObject("ADODB.Recordset")
> Set con = CreateObject("ADODB.Connection")
> Else
> Set rs = CtxCreateObject("ADODB.Recordset")
> Set con = CtxCreateObject("ADODB.Connection")
> End If
> con.Open GetConnectionString()
> 'Use transaction
> con.BeginTrans
> rs.CursorLocation = adUseClient
> rs.CursorType = adOpenStatic
> rs.LockType = adLockOptimistic
> 'idtbl and idUser is already singlequoted
> For i = LBound(userList) To UBound(userList)
> sSQL = "Delete from " & TSP("vmo_base_defaultvalues") & " where
> iduser="
> & userList(i) & " and "
> sSQL = sSQL & " idfld in (select vmo_base_field.idfld from " &
> TSP("vmo_base_defaultvalues") & ","
> sSQL = sSQL & TSP("vmo_base_field") & " where
> vmo_base_field.idfld=vmo_base_defaultvalues.idfld "
> sSQL = sSQL & " and vmo_base_field.idtbl = " & idTBL & ")"
> rs.Open sSQL, con
> 'If I commit here it works OK but I want to commit after the entire
> operation is finished
> For l = LBound(sArg) To UBound(sArg) Step 4
> If sArg(l) <> "" And sArg(l + 1) <> "" Then
> sSQL = "Insert into " & TSP("vmo_base_defaultvalues") & "
> (idUser,idfld,vValue,datevalue, deftype) " & _
> "values (" & _
> userList(i) & "," & _
> sArg(l) & "," & _
> sArg(l + 1) & "," & _
> sArg(l + 2) & "," & _
> sArg(l + 3) & ")"
> rs.Open sSQL, con
> End If
> Next l
> Next i
> con.CommitTrans
>
> Regards
> /Hans
>|||Thanks Dan for the tip!
Yes that seems to fix the problem. The code is pretty old and written for
Oracle in the first place where I did not have any problems with the
transaction (so I guess the real problem is inside the oledb provider). Yes
you are right using parameters is much safer but at least I don't like
touching code that has been working for years, well at least for other
databases than SQL-server :-)
/Hans

Delete all records in tblA not matching in tblB

I'm hoping someone can tell me how to construct a stored procedure that
deletes all records in tblA not matching the PK in tblB

This gives me the recordset of all records in tblA with no matching
records in tblB (ID is the PK in tblB)

SELECT a.ID
FROM dbo.tblB b
RIGHT OUTER JOIN dbo.tblA a ON b.ID = a.ID
WHERE
b.ID IS NULL

thanks,
lqDELETE FROM tblA
WHERE NOT EXISTS
(SELECT *
FROM tblB
WHERE tblB.id = tblA.id)

(Untested. Make sure you have a current backup and test it out for
yourself first.)

--
David Portas
SQL Server MVP
--|||Thanks for that. I have never used NOT EXISTS before. lq

Delete all records in Table 1 where related record has value in Ta

I believe this should achieve what you want to do, just replace 5 with
whatever value you want:
DELETE a FROM table1 a JOIN table2 b ON (a.ID_Table2 = b.id) AND (b.value =
5)
Adam J Warne, MCDBA
"Imager" wrote:

> Simple query for you query gurus here:
> Given two tables, related by ID_Adjacency, how can I delete all rows in
> table 1, where the related row in table 2 has some given value? ie.: with
> the following two tables, delete all records from Table1, where the relate
d
> record in Table 2 has a "Value" field with a value of 2.
>
> CREATE TABLE [dbo].[Table2] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Value] [tinyint] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Table1] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [ID_Table2] [int] NOT NULL ,
> [Value] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table2] WITH NOCHECK ADD
> CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table1] WITH NOCHECK ADD
> CONSTRAINT [PK_SourceTable] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table1] ADD
> CONSTRAINT [FK_Table1_Table2] FOREIGN KEY
> (
> [ID_Table2]
> ) REFERENCES [dbo].[Table2] (
> [ID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> GO
>
>On Tue, 16 Aug 2005 03:39:02 -0700, Adam Warne wrote:
>I believe this should achieve what you want to do, just replace 5 with
>whatever value you want:
>DELETE a FROM table1 a JOIN table2 b ON (a.ID_Table2 = b.id) AND (b.value = 5)[/col
or]
Or the more protable ANSI-stnadard version:
DELETE FROM table1
WHERE EXISTS (SELECT *
FROM table2
WHERE table2.ID = table1.ID_Table2
AND table2.value = 5)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks chaps.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:5mf4g19hbk4rtf9khjg7t428kllhgeudsc@.
4ax.com...
> On Tue, 16 Aug 2005 03:39:02 -0700, Adam Warne wrote:
>
> Or the more protable ANSI-stnadard version:
> DELETE FROM table1
> WHERE EXISTS (SELECT *
> FROM table2
> WHERE table2.ID = table1.ID_Table2
> AND table2.value = 5)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)sql

Delete all records from a Database

Is there a way to delete all records in a SQL Server 2005 database? A function or a script?

use delete or truncate command to delete the records

Delete from <tablename>

don;t specify the condition in delete query, it will delete all records

or

truncate table <table name>

|||

Hi

can u try this one

sqlconnection con= new sqlconnection();

con.Open();

cmd =newSqlCommand("Delete <table name>", con);

int rows = cmd.ExecuteNonQuery();

if (rows != 0)

{

lblMsg.Text =" Row(s) Deleted Successfully... ";

}

|||

There is not a single command to do what you are asking. You can run something like this in Query Analyzer then cut and paste the results into Query Analyzer and run them all at once.

SELECT 'truncate table ' + table_name
FROM information_schema.TABLES
WHERE table_type = 'BASE TABLE'

|||

I dont want to delete all rows from one table! I want to delete all rows from all tables at once!

|||

I know there isnt a single command. I was wontering if anyone had implemented a script for this. The one you propose is a good idea but wont function if the tables are related with keys. You must first truncate tables with foreing keys. If we can think a way of sorting the results it will work...

|||

to generate a script you have required to find the all tables in ur databases,

you can find all the tables of ur database by using

select*from sysobjectswheretype='u'

then you required to write a cursor, which is taking the name of table and append the table name to the delete query,

its like

fetch from --

declare @.query varchar(1000)

{

@.query=@.query + " Delete from " @.yourTableName"

}

then write

Exec(@.query)

this will delete all the rows from all the tables

|||

this is the script

DECLARE DeleteRecordsCURSORFOR

selectnamefrom sysobjectswheretype='u'

DECLARE @.queryvarchar(1000)

DECLARE @.tableNamevarchar(100)

DECLARE @.TEMPVARCHAR(100)

set @.query='';

set @.TEMP='';

OPEN DeleteRecords;

FETCH NEXTFROM DeleteRecordsinto @.tableNameWHILE@.@.FETCH_STATUS= 0

BEGIN

set @.TEMP=' Delete from '+@.tableNameset @.query= @.query+ @.TEMP

FETCH NEXTFROM DeleteRecordsinto @.tableName

END

Print(@.query)

EXEC(@.query)

CLOSE DeleteRecords;

DEALLOCATE DeleteRecords;

GO

|||Thanks for the script, although the constraint issue remains!

delete 8 million records - fastest way?

This summary is not available. Please click here to view the post.

delete 8 million records - fastest way?

This summary is not available. Please click here to view the post.

delete 8 million records - fastest way?

This summary is not available. Please click here to view the post.

Delete 1 month old records

I need to set up a job which will run the last day of every month to
keep the data in the table for the current month only and delete
everyhthing else (older than 1 month records would be deleted). see the
sample case:
create table #t (a datetime)
insert into #t values (getdate())
insert into #t values ('9/1/2005')
insert into #t values ('8/20/2005')
insert into #t values ('8/8/2005')
insert into #t values ('7/3/2005')
insert into #t values ('6/1/2005')
insert into #t values ('4/1/2004')
delete from #t
where a < DATEADD(MONTH,-1,CURRENT_TIMESTAMP)
My final result in this case would be only two records (9/2005).
Eeverything else should be deleted. My DELETE stament is not working bc
of the time poartion I guess. Can you correct this SQL?
Thanks for your help.
*** Sent via Developersdex http://www.examnotes.net ***delete from #t
where a < cast(month(current_timestamp) as varchar) + '-1-' +
cast(year(current_timestamp) as varchar)
--Brian
(Please reply to the newsgroups only.)
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:O90arSyrFHA.1204@.TK2MSFTNGP15.phx.gbl...
>I need to set up a job which will run the last day of every month to
> keep the data in the table for the current month only and delete
> everyhthing else (older than 1 month records would be deleted). see the
> sample case:
> create table #t (a datetime)
> insert into #t values (getdate())
> insert into #t values ('9/1/2005')
> insert into #t values ('8/20/2005')
> insert into #t values ('8/8/2005')
> insert into #t values ('7/3/2005')
> insert into #t values ('6/1/2005')
> insert into #t values ('4/1/2004')
>
> delete from #t
> where a < DATEADD(MONTH,-1,CURRENT_TIMESTAMP)
> My final result in this case would be only two records (9/2005).
> Eeverything else should be deleted. My DELETE stament is not working bc
> of the time poartion I guess. Can you correct this SQL?
> Thanks for your help.
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Try:
delete from #t
where a < convert (char (8), DATEADD(MONTH,-1,CURRENT_TIMESTAMP) , 112)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:O90arSyrFHA.1204@.TK2MSFTNGP15.phx.gbl...
I need to set up a job which will run the last day of every month to
keep the data in the table for the current month only and delete
everyhthing else (older than 1 month records would be deleted). see the
sample case:
create table #t (a datetime)
insert into #t values (getdate())
insert into #t values ('9/1/2005')
insert into #t values ('8/20/2005')
insert into #t values ('8/8/2005')
insert into #t values ('7/3/2005')
insert into #t values ('6/1/2005')
insert into #t values ('4/1/2004')
delete from #t
where a < DATEADD(MONTH,-1,CURRENT_TIMESTAMP)
My final result in this case would be only two records (9/2005).
Eeverything else should be deleted. My DELETE stament is not working bc
of the time poartion I guess. Can you correct this SQL?
Thanks for your help.
*** Sent via Developersdex http://www.examnotes.net ***|||Try,
delete #t
where a < cast(convert(varchar(6), getdate(), 112) + '01' as datetime)
AMB
"Test Test" wrote:

> I need to set up a job which will run the last day of every month to
> keep the data in the table for the current month only and delete
> everyhthing else (older than 1 month records would be deleted). see the
> sample case:
> create table #t (a datetime)
> insert into #t values (getdate())
> insert into #t values ('9/1/2005')
> insert into #t values ('8/20/2005')
> insert into #t values ('8/8/2005')
> insert into #t values ('7/3/2005')
> insert into #t values ('6/1/2005')
> insert into #t values ('4/1/2004')
>
> delete from #t
where a < DATEADD(MONTH,-1,CURRENT_TIMESTAMP)
> My final result in this case would be only two records (9/2005).
> Eeverything else should be deleted. My DELETE stament is not working bc
> of the time poartion I guess. Can you correct this SQL?
> Thanks for your help.
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Oops, gotta chop off that month part:
delete from #t
where a < convert (char (6), DATEADD(MONTH,-1,CURRENT_TIMESTAMP) , 112) +
'01'
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23dwJ3cyrFHA.3440@.TK2MSFTNGP10.phx.gbl...
Try:
delete from #t
where a < convert (char (8), DATEADD(MONTH,-1,CURRENT_TIMESTAMP) , 112)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:O90arSyrFHA.1204@.TK2MSFTNGP15.phx.gbl...
I need to set up a job which will run the last day of every month to
keep the data in the table for the current month only and delete
everyhthing else (older than 1 month records would be deleted). see the
sample case:
create table #t (a datetime)
insert into #t values (getdate())
insert into #t values ('9/1/2005')
insert into #t values ('8/20/2005')
insert into #t values ('8/8/2005')
insert into #t values ('7/3/2005')
insert into #t values ('6/1/2005')
insert into #t values ('4/1/2004')
delete from #t
where a < DATEADD(MONTH,-1,CURRENT_TIMESTAMP)
My final result in this case would be only two records (9/2005).
Eeverything else should be deleted. My DELETE stament is not working bc
of the time poartion I guess. Can you correct this SQL?
Thanks for your help.
*** Sent via Developersdex http://www.examnotes.net ***|||It works! Thanks for all your help!
*** Sent via Developersdex http://www.examnotes.net ***

Delete + Log file

A) When i run a delete against a table that has 50 million records based
upon a where clause , what entries does the T log file hold ? Does it log 50
million delete statements along with 50 million inserts just incase it needs
to rollback.
B) Also if there is a clustered index on the coulimn thats part of the where
clause, what does the Tlog file hold ?
C) If there was a clustered index but not part of the column in the where
clause, what does the Tlog contain ?
D) During the time the delete is occuring, does it go ahead and start
deleting entries from the data pages in the data files or does it first log
entries in the Log file and then deletes ?
E) Finally if i did a backup log during the time the delete is occuring (
Say i noticed that the table with (nolock) option was decrementing ) and
then restored the logs on another database , will part of the deletes be
reflected on the other database if i specify the (nolock) option since the
delete did not complete
I am just trying to understand what entries the TLog contains.. I would
appreciate if you could provide answers to all the 5 parts. I am using SQL
2000
Thank youIf you want to see what is in the tran log you can run
this before you do a log backup:
select * from ::fn_dblog(null,null)
I will attempt to answer your questions
a) It logs 50 million delete statements (if you need to
delete everything out of a table and don't need the
ability to rollback, you might use truncate table as it is
faster because it is minimally logged)
b)I don't believe it matters if there is a clustered index
or not in regards to the T-log
c)I don't believe it matters if there is a clustered index
or not in regards to the T-log
d) Everything hits the log first, it hits the data when
the T-log does a checkpoint
e)That one I'm not sure on, would have to test that one or
maybe someone has tried this before that reads these
newsgroups, I would guess that if you tried backing up the
log during this transaction, it would wait until the tran
was finished, selecting from it with a nolog would only
show you, not the T-log, the data while in the transaction.
Again, not sure on that one.
HTH
Ray Higdon MCSE, MCDBA, CCNA
>--Original Message--
>A) When i run a delete against a table that has 50
million records based
>upon a where clause , what entries does the T log file
hold ? Does it log 50
>million delete statements along with 50 million inserts
just incase it needs
>to rollback.
>
>B) Also if there is a clustered index on the coulimn
thats part of the where
>clause, what does the Tlog file hold ?
>C) If there was a clustered index but not part of the
column in the where
>clause, what does the Tlog contain ?
>D) During the time the delete is occuring, does it go
ahead and start
>deleting entries from the data pages in the data files or
does it first log
>entries in the Log file and then deletes ?
>E) Finally if i did a backup log during the time the
delete is occuring (
>Say i noticed that the table with (nolock) option was
decrementing ) and
>then restored the logs on another database , will part of
the deletes be
>reflected on the other database if i specify the (nolock)
option since the
>delete did not complete
>I am just trying to understand what entries the TLog
contains.. I would
>appreciate if you could provide answers to all the 5
parts. I am using SQL
>2000
>Thank you
>
>.
>|||--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it community
of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OuJAVQ#VDHA.392@.TK2MSFTNGP11.phx.gbl...
> A) When i run a delete against a table that has 50 million records based
> upon a where clause , what entries does the T log file hold ? Does it log
50
> million delete statements along with 50 million inserts just incase it
needs
> to rollback.
>
The log holds a copy of the record which was deleted...
> B) Also if there is a clustered index on the coulimn thats part of the
where
> clause, what does the Tlog file hold ?
NO change, the log has the copy of the deleted record.
> C) If there was a clustered index but not part of the column in the where
> clause, what does the Tlog contain ?
no change.
> D) During the time the delete is occuring, does it go ahead and start
> deleting entries from the data pages in the data files or does it first
log
> entries in the Log file and then deletes ?
>
Logging occurs first.
> E) Finally if i did a backup log during the time the delete is occuring (
> Say i noticed that the table with (nolock) option was decrementing ) and
> then restored the logs on another database , will part of the deletes be
> reflected on the other database if i specify the (nolock) option since the
> delete did not complete
>
After the restore, and recovery has run either all of the records will be
present or none of them...Each statement is a transaction.
> I am just trying to understand what entries the TLog contains.. I would
> appreciate if you could provide answers to all the 5 parts. I am using SQL
> 2000
> Thank you
>
>|||Thanks so to answer 5, where you said
"After the restore, and recovery has run either all of the records will be
present or none of them...Each statement is a transaction."
What if i restored log with standby mode so users can read from this standby
database, will i see some deletes in effect with (nolock)
Thanks once again to you all
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:eTPtkTCWDHA.2008@.TK2MSFTNGP11.phx.gbl...
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it
community
> of SQL Server professionals.
> www.sqlpass.org
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:OuJAVQ#VDHA.392@.TK2MSFTNGP11.phx.gbl...
> > A) When i run a delete against a table that has 50 million records based
> > upon a where clause , what entries does the T log file hold ? Does it
log
> 50
> > million delete statements along with 50 million inserts just incase it
> needs
> > to rollback.
> >
> The log holds a copy of the record which was deleted...
> >
> > B) Also if there is a clustered index on the coulimn thats part of the
> where
> > clause, what does the Tlog file hold ?
> NO change, the log has the copy of the deleted record.
>
> >
> > C) If there was a clustered index but not part of the column in the
where
> > clause, what does the Tlog contain ?
> no change.
> >
> > D) During the time the delete is occuring, does it go ahead and start
> > deleting entries from the data pages in the data files or does it first
> log
> > entries in the Log file and then deletes ?
> >
> Logging occurs first.
> > E) Finally if i did a backup log during the time the delete is occuring
(
> > Say i noticed that the table with (nolock) option was decrementing ) and
> > then restored the logs on another database , will part of the deletes be
> > reflected on the other database if i specify the (nolock) option since
the
> > delete did not complete
> >
> After the restore, and recovery has run either all of the records will be
> present or none of them...Each statement is a transaction.
> > I am just trying to understand what entries the TLog contains.. I would
> > appreciate if you could provide answers to all the 5 parts. I am using
SQL
> > 2000
> >
> > Thank you
> >
> >
> >
>sql

Delete - Exists - problem

Can anyone tell me why the following deletes all the records instead of
simply the ones returned in the "Exists" clause?
The select in the exists by itself returns 131 records, however when run in
the following context it deletes all 4474 that are in the Shades table.'
Delete FROM #TMP_SHADE WHERE EXISTS
(select DISTINCT(OLD_SHADE_ID) from #TMP_CHANGEDSHADES
where OLD_SHADE_ID NOT in (select SHADE_ID
from SHADE_SUC))
The idea is to delete any shade records in #tmp_shade where they do not
exist in shade_SUC
ThanksDan,

> Can anyone tell me why the following deletes all the records instead of
> simply the ones returned in the "Exists" clause?
Because you are not correlating the tables. If at least one row in table
#TMP_CHANGEDSHADES meet the condition, all rows from table #TMP_SHADE will b
e
deleted. It should be something like:
Delete
FROM #TMP_SHADE
WHERE EXISTS
(
select DISTINCT(OLD_SHADE_ID)
from #TMP_CHANGEDSHADES
where #TMP_CHANGEDSHADES.col1 = #TMP_SHADE.col1
and OLD_SHADE_ID NOT in (select SHADE_ID from SHADE_SUC)
)
AMB
"Dan" wrote:

> Can anyone tell me why the following deletes all the records instead of
> simply the ones returned in the "Exists" clause?
> The select in the exists by itself returns 131 records, however when run i
n
> the following context it deletes all 4474 that are in the Shades table.?
?
> Delete FROM #TMP_SHADE WHERE EXISTS
> (select DISTINCT(OLD_SHADE_ID) from #TMP_CHANGEDSHADES
> where OLD_SHADE_ID NOT in (select SHADE_ID
> from SHADE_SUC))
> The idea is to delete any shade records in #tmp_shade where they do not
> exist in shade_SUC
> Thanks
>|||Delete from X where Exists (Y)
will delete all rows in X if Y is true and nothing if Y is false.
try something like
Delete from X where Y_id in (select distinct(Y_id) from Y)
I hope you get the idea.
Regards,
Nishant
"Dan" wrote:

> Can anyone tell me why the following deletes all the records instead of
> simply the ones returned in the "Exists" clause?
> The select in the exists by itself returns 131 records, however when run i
n
> the following context it deletes all 4474 that are in the Shades table.?
?
> Delete FROM #TMP_SHADE WHERE EXISTS
> (select DISTINCT(OLD_SHADE_ID) from #TMP_CHANGEDSHADES
> where OLD_SHADE_ID NOT in (select SHADE_ID
> from SHADE_SUC))
> The idea is to delete any shade records in #tmp_shade where they do not
> exist in shade_SUC
> Thanks
>|||Great Alejandro! - Thank you for the pointer
Dan
"Alejandro Mesa" wrote:
> Dan,
>
> Because you are not correlating the tables. If at least one row in table
> #TMP_CHANGEDSHADES meet the condition, all rows from table #TMP_SHADE will
be
> deleted. It should be something like:
> Delete
> FROM #TMP_SHADE
> WHERE EXISTS
> (
> select DISTINCT(OLD_SHADE_ID)
> from #TMP_CHANGEDSHADES
> where #TMP_CHANGEDSHADES.col1 = #TMP_SHADE.col1
> and OLD_SHADE_ID NOT in (select SHADE_ID from SHADE_SUC)
> )
>
> AMB
>
>
> "Dan" wrote:
>sql

delete

I have a need to delete records from a table, since the
table has FK constraints, it doesn't allow me to issue
delete.
I want to delete records from this table and repopulate
it from the backup. Since I don't want to manually
check all the dependencies, Is there any way by which
I can just delete from this table and still maintain
all dependencies with other tables? I don't want to
drop so that I don't have to create indexes, etc.
Thanks for your help!
Asok
Have you tried either dropping the FK constraints or
alter table x nocheck constraint all
then try the delete
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Asok" <anonymous@.discussions.microsoft.com> wrote in message
news:1b13c01c44fe2$f931cc10$a601280a@.phx.gbl...
> I have a need to delete records from a table, since the
> table has FK constraints, it doesn't allow me to issue
> delete.
> I want to delete records from this table and repopulate
> it from the backup. Since I don't want to manually
> check all the dependencies, Is there any way by which
> I can just delete from this table and still maintain
> all dependencies with other tables? I don't want to
> drop so that I don't have to create indexes, etc.
> Thanks for your help!
> Asok
|||Hi Wayne,
I think this is what I was looking for. I think it will
work, but after that when I restore the data in this
table, will it maintain the constraints? Do you have any
idea about it? I want to make sure before I fiddle with
this table as it has lot of dependecies associated.
If it was just couple of them, I would have taken a
chance.
Thank you for your time,
-Asok

>--Original Message--
>Have you tried either dropping the FK constraints or
>alter table x nocheck constraint all
>then try the delete
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Mariner, Charlotte, NC
>www.mariner-usa.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>"Asok" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1b13c01c44fe2$f931cc10$a601280a@.phx.gbl...
>
>.
>

delete

I have a need to delete records from a table, since the
table has FK constraints, it doesn't allow me to issue
delete.
I want to delete records from this table and repopulate
it from the backup. Since I don't want to manually
check all the dependencies, Is there any way by which
I can just delete from this table and still maintain
all dependencies with other tables? I don't want to
drop so that I don't have to create indexes, etc.
Thanks for your help!
AsokHave you tried either dropping the FK constraints or
alter table x nocheck constraint all
then try the delete
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Asok" <anonymous@.discussions.microsoft.com> wrote in message
news:1b13c01c44fe2$f931cc10$a601280a@.phx
.gbl...
> I have a need to delete records from a table, since the
> table has FK constraints, it doesn't allow me to issue
> delete.
> I want to delete records from this table and repopulate
> it from the backup. Since I don't want to manually
> check all the dependencies, Is there any way by which
> I can just delete from this table and still maintain
> all dependencies with other tables? I don't want to
> drop so that I don't have to create indexes, etc.
> Thanks for your help!
> Asok|||Hi Wayne,
I think this is what I was looking for. I think it will
work, but after that when I restore the data in this
table, will it maintain the constraints? Do you have any
idea about it? I want to make sure before I fiddle with
this table as it has lot of dependecies associated.
If it was just couple of them, I would have taken a
chance.
Thank you for your time,
-Asok

>--Original Message--
>Have you tried either dropping the FK constraints or
>alter table x nocheck constraint all
>then try the delete
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Mariner, Charlotte, NC
>www.mariner-usa.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>"Asok" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1b13c01c44fe2$f931cc10$a601280a@.phx
.gbl...
>
>.
>

delete

I have a need to delete records from a table, since the
table has FK constraints, it doesn't allow me to issue
delete.
I want to delete records from this table and repopulate
it from the backup. Since I don't want to manually
check all the dependencies, Is there any way by which
I can just delete from this table and still maintain
all dependencies with other tables? I don't want to
drop so that I don't have to create indexes, etc.
Thanks for your help!
AsokHave you tried either dropping the FK constraints or
alter table x nocheck constraint all
then try the delete
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Asok" <anonymous@.discussions.microsoft.com> wrote in message
news:1b13c01c44fe2$f931cc10$a601280a@.phx.gbl...
> I have a need to delete records from a table, since the
> table has FK constraints, it doesn't allow me to issue
> delete.
> I want to delete records from this table and repopulate
> it from the backup. Since I don't want to manually
> check all the dependencies, Is there any way by which
> I can just delete from this table and still maintain
> all dependencies with other tables? I don't want to
> drop so that I don't have to create indexes, etc.
> Thanks for your help!
> Asok|||Hi Wayne,
I think this is what I was looking for. I think it will
work, but after that when I restore the data in this
table, will it maintain the constraints? Do you have any
idea about it? I want to make sure before I fiddle with
this table as it has lot of dependecies associated.
If it was just couple of them, I would have taken a
chance.
Thank you for your time,
-Asok
>--Original Message--
>Have you tried either dropping the FK constraints or
>alter table x nocheck constraint all
>then try the delete
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Mariner, Charlotte, NC
>www.mariner-usa.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>"Asok" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1b13c01c44fe2$f931cc10$a601280a@.phx.gbl...
>> I have a need to delete records from a table, since the
>> table has FK constraints, it doesn't allow me to issue
>> delete.
>> I want to delete records from this table and repopulate
>> it from the backup. Since I don't want to manually
>> check all the dependencies, Is there any way by which
>> I can just delete from this table and still maintain
>> all dependencies with other tables? I don't want to
>> drop so that I don't have to create indexes, etc.
>> Thanks for your help!
>> Asok
>
>.
>

Delay in inserting records continuously into the Database

Dear All,
I am having a process which continuously inserts a record into a single
table with the following table schema:
CREATE TABLE [MessageLog] (
[MessageIndex] [int] IDENTITY (1, 1) NOT NULL ,
[SegmentCode] [smallint] NULL ,
[SetID] [smallint] NULL ,
[ScripCode] [int] NULL ,
[ClientCode] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UserID] [int] NULL ,
[OrderNumber] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TimeStamp1] [int] NULL ,
[MessageType] [int] NULL ,
[ValidityTime] [int] NULL ,
[Message] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
I have written an SP to update this table from my code. Most of the time to
execute this SP it is taking around 1 to 2 ms, but some time (10 out of 5000
inserts) it takes around 75, 100 or even 200 milliseconds also.
Database Properites:
General:
Maintanence Plan -> None
Collation Name -> SQL_Latin1_General_CP1_C1_AS
Both for Data file and Log File:
Automatic Grow File -> Set to Yes
File Growth -> By 10 Percent
Maximum File Size -> Unrestricted File Growth
Recovery Model -> Simple
Auto Update Statistics -> Set to True
Auto Create Statistics -> Set to True
All the other settings are set to false
Compatibility Level -> Database Compatibility Level 80
Can any one help me out to get a consistent of 1 to 2 ms every time?
Thanks and Regards,
PeriSome possible causes may be:
1)The logic organising data and then passes it to SQL Server to insert
2)triggers on the table
3)Complex foreign keys
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Peri" <Peri@.newsgroups.nospam> wrote in message
news:eIuYzCcaGHA.3740@.TK2MSFTNGP03.phx.gbl...
> Dear All,
> I am having a process which continuously inserts a record into a single
> table with the following table schema:
> CREATE TABLE [MessageLog] (
> [MessageIndex] [int] IDENTITY (1, 1) NOT NULL ,
> [SegmentCode] [smallint] NULL ,
> [SetID] [smallint] NULL ,
> [ScripCode] [int] NULL ,
> [ClientCode] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [UserID] [int] NULL ,
> [OrderNumber] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TimeStamp1] [int] NULL ,
> [MessageType] [int] NULL ,
> [ValidityTime] [int] NULL ,
> [Message] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> I have written an SP to update this table from my code. Most of the time
to
> execute this SP it is taking around 1 to 2 ms, but some time (10 out of
5000
> inserts) it takes around 75, 100 or even 200 milliseconds also.
> Database Properites:
> General:
> Maintanence Plan -> None
> Collation Name -> SQL_Latin1_General_CP1_C1_AS
> Both for Data file and Log File:
> Automatic Grow File -> Set to Yes
> File Growth -> By 10 Percent
> Maximum File Size -> Unrestricted File Growth
> Recovery Model -> Simple
> Auto Update Statistics -> Set to True
> Auto Create Statistics -> Set to True
> All the other settings are set to false
> Compatibility Level -> Database Compatibility Level 80
> Can any one help me out to get a consistent of 1 to 2 ms every time?
> Thanks and Regards,
> Peri
>|||Hi Peri,
Thank you for using MSDN Managed Newsgroup Support.
You may need to use the SQL Profiler to trace your SP running and check why
your SQL execution will take more time than usual. Basically, you may check
the CPU usage, disk reads and writes event.
Please note that:
Performance issues can be difficult to troubleshoot and resolve in a
newsgroup setting due to the number of variables and the amount of time
required to narrow down possible causes and observe the effects. We will
assist as best as we can, but you may wish to consider contacting CSS for a
more timely resolution for these type issues.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/defaul...US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com
for regional support phone numbers.
Thank you for your patience and understanding.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>From: "Peri" <Peri@.newsgroups.nospam>
>Subject: Delay in inserting records continuously into the Database
>Date: Thu, 27 Apr 2006 11:36:14 +0530
>Lines: 49
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
>X-RFC2646: Format=Flowed; Original
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2869
>Message-ID: <eIuYzCcaGHA.3740@.TK2MSFTNGP03.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.programming
>NNTP-Posting-Host: btnl-tn-dsl-dynamic-177.10.144.59.touchtelindia.net
59.144.10.177
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP03.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.programming:595672
>X-Tomcat-NG: microsoft.public.sqlserver.programming
>Dear All,
>I am having a process which continuously inserts a record into a single
>table with the following table schema:
>CREATE TABLE [MessageLog] (
> [MessageIndex] [int] IDENTITY (1, 1) NOT NULL ,
> [SegmentCode] [smallint] NULL ,
> [SetID] [smallint] NULL ,
> [ScripCode] [int] NULL ,
> [ClientCode] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [UserID] [int] NULL ,
> [OrderNumber] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TimeStamp1] [int] NULL ,
> [MessageType] [int] NULL ,
> [ValidityTime] [int] NULL ,
> [Message] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>GO
>I have written an SP to update this table from my code. Most of the time
to
>execute this SP it is taking around 1 to 2 ms, but some time (10 out of
5000
>inserts) it takes around 75, 100 or even 200 milliseconds also.
>Database Properites:
>General:
>Maintanence Plan -> None
>Collation Name -> SQL_Latin1_General_CP1_C1_AS
>Both for Data file and Log File:
>Automatic Grow File -> Set to Yes
>File Growth -> By 10 Percent
>Maximum File Size -> Unrestricted File Growth
>Recovery Model -> Simple
>Auto Update Statistics -> Set to True
>Auto Create Statistics -> Set to True
>All the other settings are set to false
>Compatibility Level -> Database Compatibility Level 80
>Can any one help me out to get a consistent of 1 to 2 ms every time?
>Thanks and Regards,
>Peri
>
>|||On Thu, 27 Apr 2006 11:36:14 +0530, "Peri" <Peri@.newsgroups.nospam>
wrote:

>Most of the time to
>execute this SP it is taking around 1 to 2 ms, but some time (10 out of 500
0
>inserts) it takes around 75, 100 or even 200 milliseconds also.
Perhaps the delay is waiting for more disl file space to be allocated,
either data or log. Log in particular can be a problem as the process
must wait for completion, and the log is always growing (and growing,
and growing...)
Roy Harvey
Beacon Falls, CT

del dupes

record# cat product
1 fish halibut
2 fish halibut
I need a script to find all records with duplicates similar to the scenario
above and delete the duplicates, in this case record#2. Thank you.Try:
;with x
as
(
select
*
, row_number () over (partition by cat, product order by cat product)
as row_num
from
MyTable
)
delete x
where
row_num > 1
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:31A254C8-43FE-4FCE-9DB8-0B06FF18B6DB@.microsoft.com...
record# cat product
1 fish halibut
2 fish halibut
I need a script to find all records with duplicates similar to the scenario
above and delete the duplicates, in this case record#2. Thank you.|||> I need a script to find all records with duplicates similar to the
> scenario
> above and delete the duplicates, in this case record#2. Thank you.
Same question was posted and answered in .programming yesterday. Have a
look.

del dupes

record# cat product
1 fish halibut
2 fish halibut
I need a script to find all records with duplicates similar to the scenario
above and delete the duplicates, in this case record#2. Thank you.
Try:
;with x
as
(
select
*
, row_number () over (partition by cat, product order by cat product)
as row_num
from
MyTable
)
delete x
where
row_num > 1
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:31A254C8-43FE-4FCE-9DB8-0B06FF18B6DB@.microsoft.com...
record# cat product
1 fish halibut
2 fish halibut
I need a script to find all records with duplicates similar to the scenario
above and delete the duplicates, in this case record#2. Thank you.
|||> I need a script to find all records with duplicates similar to the
> scenario
> above and delete the duplicates, in this case record#2. Thank you.
Same question was posted and answered in .programming yesterday. Have a
look.

del dupes

record# cat product
1 fish halibut
2 fish halibut
I need a script to find all records with duplicates similar to the scenario
above and delete the duplicates, in this case record#2. Thank you.Try:
;with x
as
(
select
*
, row_number () over (partition by cat, product order by cat product)
as row_num
from
MyTable
)
delete x
where
row_num > 1
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:31A254C8-43FE-4FCE-9DB8-0B06FF18B6DB@.microsoft.com...
record# cat product
1 fish halibut
2 fish halibut
I need a script to find all records with duplicates similar to the scenario
above and delete the duplicates, in this case record#2. Thank you.|||> I need a script to find all records with duplicates similar to the
> scenario
> above and delete the duplicates, in this case record#2. Thank you.
Same question was posted and answered in .programming yesterday. Have a
look.

degrading performance on one table

Hello, we use one table in our SQL2k SP3a server for storing all sorts of
parameters. See the description below. It contains about 150.000 records.
What we see happening over the day is that the performance on accessing this
table deteriorates. When joins are made with other tables that use the
parameter table, they get slow too. It is very fast when SQL is freshly
started, but after a day or two spurious locks show up (I assume because of
the lack of response), and at somepoint I can't even do a select count (*)
anymore. Takes forever. There are no locks when I do this, I just wait
forever. Restarting SQL solved the problem, after that it is as fast as
ever!
This is REALLY puzzling us. We've run traces, etc- nothing to indicate any
particular problem. Index has been defragged, to no avail.
Are we missing something obvious? Pointers as to where to look?
René
CREATE TABLE [dbo].[parameter] (
[entity_name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[entity_id] [int] NOT NULL ,
[name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[value] [varchar] (4096) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[parameter] WITH NOCHECK ADD
CONSTRAINT [PK_parameter] PRIMARY KEY CLUSTERED
(
[entity_name],
[entity_id],
[name]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GOYou mentioned that you did reindex.
Do you do any deletes and updates on the table?
You mentioned no locks on the table when you run count(*). Is the server
performing bad for other tables at that time? Are there any open
transactions (run dbcc opentran). how about DBCC SHOWCONTIG (tablename)
"René" <rene.de.vries/atsign/kexdotnl> wrote in message
news:u3hHznsUDHA.1816@.TK2MSFTNGP09.phx.gbl...
> Hello, we use one table in our SQL2k SP3a server for storing all sorts of
> parameters. See the description below. It contains about 150.000 records.
> What we see happening over the day is that the performance on accessing
this
> table deteriorates. When joins are made with other tables that use the
> parameter table, they get slow too. It is very fast when SQL is freshly
> started, but after a day or two spurious locks show up (I assume because
of
> the lack of response), and at somepoint I can't even do a select count (*)
> anymore. Takes forever. There are no locks when I do this, I just wait
> forever. Restarting SQL solved the problem, after that it is as fast as
> ever!
> This is REALLY puzzling us. We've run traces, etc- nothing to indicate any
> particular problem. Index has been defragged, to no avail.
> Are we missing something obvious? Pointers as to where to look?
> René
> CREATE TABLE [dbo].[parameter] (
> [entity_name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
> ,
> [entity_id] [int] NOT NULL ,
> [name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [value] [varchar] (4096) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[parameter] WITH NOCHECK ADD
> CONSTRAINT [PK_parameter] PRIMARY KEY CLUSTERED
> (
> [entity_name],
> [entity_id],
> [name]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
>|||Did you look at the query execution plan using one or more typical
"slowed-down" queries? Are the correct indexes being used? How about update
statistics? Is there tempdb issue 'cause it seems OK after service restart?
Any Perfmon findings on memory, processor, disk I/O counters?
Richard
"René" <rene.de.vries/atsign/kexdotnl> wrote in message
news:u3hHznsUDHA.1816@.TK2MSFTNGP09.phx.gbl...
> Hello, we use one table in our SQL2k SP3a server for storing all sorts of
> parameters. See the description below. It contains about 150.000 records.
> What we see happening over the day is that the performance on accessing
this
> table deteriorates. When joins are made with other tables that use the
> parameter table, they get slow too. It is very fast when SQL is freshly
> started, but after a day or two spurious locks show up (I assume because
of
> the lack of response), and at somepoint I can't even do a select count (*)
> anymore. Takes forever. There are no locks when I do this, I just wait
> forever. Restarting SQL solved the problem, after that it is as fast as
> ever!
> This is REALLY puzzling us. We've run traces, etc- nothing to indicate any
> particular problem. Index has been defragged, to no avail.
> Are we missing something obvious? Pointers as to where to look?
> René
> CREATE TABLE [dbo].[parameter] (
> [entity_name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
> ,
> [entity_id] [int] NOT NULL ,
> [name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [value] [varchar] (4096) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[parameter] WITH NOCHECK ADD
> CONSTRAINT [PK_parameter] PRIMARY KEY CLUSTERED
> (
> [entity_name],
> [entity_id],
> [name]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
>|||I did have a look at temdb, and there was 98% unused space.. But in total it
is currently only
The query plans for a typical query looks ok, In fact, right after a
restart, that query is really fast - 0 second responses. The indexes look
okay, we experimented earlier with different indexes.
There is no change in load on CPU, memory of disk I/Owhen the performance
goed bad... CPU (2) are at maxed out at 50, sometimes peeking when a
full-text query is requested.
"Richard Ding" <dingr@.cleanharbors.com> wrote in message
news:uIE7$0sUDHA.360@.TK2MSFTNGP11.phx.gbl...
> Did you look at the query execution plan using one or more typical
> "slowed-down" queries? Are the correct indexes being used? How about
update
> statistics? Is there tempdb issue 'cause it seems OK after service
restart?
> Any Perfmon findings on memory, processor, disk I/O counters?
>
> Richard
> "René" <rene.de.vries/atsign/kexdotnl> wrote in message
> news:u3hHznsUDHA.1816@.TK2MSFTNGP09.phx.gbl...
> > Hello, we use one table in our SQL2k SP3a server for storing all sorts
of
> > parameters. See the description below. It contains about 150.000
records.
> >
> > What we see happening over the day is that the performance on accessing
> this
> > table deteriorates. When joins are made with other tables that use the
> > parameter table, they get slow too. It is very fast when SQL is freshly
> > started, but after a day or two spurious locks show up (I assume because
> of
> > the lack of response), and at somepoint I can't even do a select count
(*)
> > anymore. Takes forever. There are no locks when I do this, I just wait
> > forever. Restarting SQL solved the problem, after that it is as fast as
> > ever!
> >
> > This is REALLY puzzling us. We've run traces, etc- nothing to indicate
any
> > particular problem. Index has been defragged, to no avail.
> >
> > Are we missing something obvious? Pointers as to where to look?
> >
> > René
> >
> > CREATE TABLE [dbo].[parameter] (
> > [entity_name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL
> > ,
> > [entity_id] [int] NOT NULL ,
> > [name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [value] [varchar] (4096) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> > ) ON [PRIMARY]
> > GO
> >
> > ALTER TABLE [dbo].[parameter] WITH NOCHECK ADD
> > CONSTRAINT [PK_parameter] PRIMARY KEY CLUSTERED
> > (
> > [entity_name],
> > [entity_id],
> > [name]
> > ) WITH FILLFACTOR = 90 ON [PRIMARY]
> > GO
> >
> >
>