January 26, 2011 at 8:27 am
I need to write a script to delete any rows where the rec_date is older than 365 days
Can anyone help?
Here is my table structure:
CREATE TABLE [dbo].[forms_holder](
[object_id] [int] IDENTITY(1,1) NOT NULL,
[form_family] [char](1) NOT NULL,
[file_name] [varchar](20) NOT NULL,
[language] [char](2) NOT NULL,
[unit] [varchar](50) NOT NULL,
[state] [varchar](50) NOT NULL,
[rec_date] [datetime] NOT NULL,
[generated_file] [varbinary](max) NOT NULL,
PRIMARY KEY CLUSTERED
(
[object_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
January 26, 2011 at 8:34 am
Not trying to be snarky, but what have you tried? What do you think the query should look like? I ask because this is a fairly basic type of query and giving the answer straight away won't help you learn.
If you don't know I'll give a hint, I'd use DATEADD to get the date to delete before..
CEWII
January 26, 2011 at 8:48 am
select * FROM forms_holder
WHERE rec_date <= DateAdd("d",-365,Date())
the error I get is 'Date' is not a recognized built-in function name.
January 26, 2011 at 8:56 am
Jpotucek (1/26/2011)
select * FROM forms_holderWHERE rec_date <= DateAdd("d",-365,Date())
the error I get is 'Date' is not a recognized built-in function name.
2 things.. the d shouldn't need the double quotes and date() should be GETDATE(). Then just change select * to DELETE and you should be good.. You were close, good work.
CEWII
January 26, 2011 at 9:22 am
You really need to look up GetDate() in Books Online before you use it. Changing Date() to GetDate() will make SQL do what you tell it, but it may end up with results you're not expecting.
Run this code to see what I'm talking about:
Create Table dbo.#MyDates (MyID int Identity(1,1) NOT NULL, MyDateTime datetime);
Insert into dbo.#MyDates (MyDateTime)
SELECT '2011-01-02'
UNION ALL
SELECT '2011-01-02 19:18:27.093'
UNION ALL
SELECT '2011-01-03'
UNION ALL
SELECT '2011-01-03 19:18:27.093';
Delete FROM dbo.#MyDates
WHERE MyDateTime < Select DateAdd(dd,-24,GetDate());
Select *
From dbo.#MyDates;
Drop Table dbo.#MyDates
What do you expect the results to be? How are the results different or the same from what you expected?
January 26, 2011 at 10:12 am
Thank you!! I changed my query to
DELETE FROM forms_holder
WHERE rec_date <= DateAdd(d,-365,GETDate())
related (I think) question. I questioned the developer as to why he wanted the rows deleted and he said that his queries against this table were taking too long..
I did a simple select * from ... against this table before and after my delete and it took 10 mins to return a couple hundred rows before my delete and more than 3 mins to return 37 rows after my delete..
not sure where to start looking as to why it is taking so long to query against this one table.. ????
January 26, 2011 at 10:27 am
Look at the table size (width and length), see if the table has indexes (too few or too many?), look at the Dev's query, check Profiler and PerfMon for any other issues that might be causing problems.
But never delete rows from a table just because a developer asks you to. That's solving a symptom, not a problem. And if you deleted from production, you could get into a lot of trouble over it.
I know I'm vague on what to check, but each db is different and has different performance issues. So I advise googling on SQL Server Performance Tuning, and also reading Books Online on the subject. That will get you started.
January 26, 2011 at 10:35 am
As mentioned, be very careful deleting from production.
- Get approval
- Have solid backups
- Save records to be deleted in another table for quick recovery if needed
- Run delete in a transaction to verify results before committing
January 26, 2011 at 10:36 am
The delete was against a dev box.. I wouldn't delete in Stage or Production without first finding out why.... which is why I started asking questions.
I get a Wait type of ASYNC_NETWORK_IO when I try and query against this table in all 3 environments (DEV, Stage and Prod) - googled that but none of it really makes sense to me..
the table structure is listed above and there are no indexes on this small table
January 26, 2011 at 10:47 am
Jpotucek (1/26/2011)
the table structure is listed above and there are no indexes on this small table
How many rows in the table? Can you upload some sample data (make sure to alter anything that's considered private or proprietary information) so we can test for ourselves?
EDIT: Also, how long is it taking for these records to come back? Is the box within your firewall (where you're at) or at another corporate location?
January 26, 2011 at 10:54 am
Jpotucek (1/26/2011)
the table structure is listed above and there are no indexes on this small table
Another thing I should point out. Look back at your table structure. See this line:
PRIMARY KEY CLUSTERED
That is an index. It's a clustered index built upon your primary key. Anything in your DDL that says "NonClustered" or "Clustered" is an index.
So, your table has 1 index, not none. Which means it's not a heap table and that's good. But if object_id isn't what you're querying on, then you may need more indexes.
Are you having trouble with other queries on other tables? Or is it just this one table giving you trouble?
Any triggers we should be aware of?
January 26, 2011 at 11:00 am
no triggers and this is the only table that is displaying this behaviour.
copied the table to another instance and am getting the same results. 4 minutes to return the results (137 rows) of a Select * From..
January 26, 2011 at 11:06 am
Can you log directly into the server to test the server side response?
Have you tried a column list for the query to see if that changes?
Everything I'm finding on your wait type says the problem is not with SQL Server. That the issue is related to hardware, software, or network on the client side.
Again, please provide the Developer's query, his run time, and some sample data in the form of an INSERT script if you wish us to assist your troubleshooting. It really will help us help you better.
January 26, 2011 at 11:09 am
Please try
SELECT [object_id]
, [form_family]
, [file_name]
, [language]
, [unit]
, [state]
, [rec_date]
FROM forms_holder
I.e. leave out the last column.
Also, please check the table size in MB.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);January 26, 2011 at 11:12 am
mister.magoo (1/26/2011)
Please trySELECT [object_id]
, [form_family]
, [file_name]
, [language]
, [unit]
, [state]
, [rec_date]
FROM forms_holder
I.e. leave out the last column.
Also, please check the table size in MB.
I believe you and I are on the same page, Magoo.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply