May 15, 2017 at 3:58 am
Good Morning All,
Can I Have some opinions please.
Have 2 large tables I need to 'clean', 1 with 265263333 rows and one with 772459834 on a mirrored production system with 30 mins of downtime approved.
To delete is a no go.
To truncate is a possibility, there are no foreign keys to either table and they both have an identity column which needs to be reseeded.
As above a Drop & Create is also an option.
Thoughts please.
Thanks
Stephen
May 15, 2017 at 4:01 am
Do you need to keep any of the data in these tables?
May 15, 2017 at 4:03 am
Nope, it can all go, just need the identity column to be continuous.
May 15, 2017 at 4:20 am
Is the identity column and Identity (1,1) column if you script out the table.
I think TRUNCATE is your best option, but test first.
If you go to the PROPERTIES of the table ID column.
If the Identity,Identity Seed and, Identity Increment are all 1 then it will automatically be incremental.
May 15, 2017 at 4:26 am
Yes, the initial identity is 1,1 I'll need to reseed this after to start again at my last max value.
May 15, 2017 at 4:40 am
I think you know what you have to do already.
Just TEST and make sure all is well.
May 15, 2017 at 5:51 am
1) Why is a DELETE a no-go?
2) You didn't give us enough information. The key missing component is how many rows will remain in each table. Take it to the extreme: a) if you keep all BUT one row then clearly a DELETE is optimal and b) if you keep ONLY one row then clearly a copy/truncate/rename process is optimal. Where do you fall between those two? Another missing element is what is the indexing on each table?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 15, 2017 at 6:52 am
TheSQLGuru - Monday, May 15, 2017 5:51 AM1) Why is a DELETE a no-go?2) You didn't give us enough information. The key missing component is how many rows will remain in each table. Take it to the extreme: a) if you keep all BUT one row then clearly a DELETE is optimal and b) if you keep ONLY one row then clearly a copy/truncate/rename process is optimal. Where do you fall between those two? Another missing element is what is the indexing on each table?
The desired outcome is empty tables, which i inferred with the 'clean' apologies for any ambiguity.
There is a Primary Key on each table that is the identity that will need to be reseeded.
May 15, 2017 at 6:55 am
stephen.plant - Monday, May 15, 2017 3:58 AMGood Morning All,Can I Have some opinions please.
Have 2 large tables I need to 'clean', 1 with 265263333 rows and one with 772459834 on a mirrored production system with 30 mins of downtime approved.
To delete is a no go.
To truncate is a possibility, there are no foreign keys to either table and they both have an identity column which needs to be reseeded.
As above a Drop & Create is also an option.
Thoughts please.
Thanks
Stephen
Truncate will reseed the table for you. Note that truncate requires elevated privileges on the table or the schema
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 15, 2017 at 8:01 am
stephen.plant - Monday, May 15, 2017 6:52 AMTheSQLGuru - Monday, May 15, 2017 5:51 AM1) Why is a DELETE a no-go?2) You didn't give us enough information. The key missing component is how many rows will remain in each table. Take it to the extreme: a) if you keep all BUT one row then clearly a DELETE is optimal and b) if you keep ONLY one row then clearly a copy/truncate/rename process is optimal. Where do you fall between those two? Another missing element is what is the indexing on each table?
The desired outcome is empty tables, which i inferred with the 'clean' apologies for any ambiguity.
There is a Primary Key on each table that is the identity that will need to be reseeded.
If the IDENTITY column is the PK and you need to reseed to the previous MAX after truncation, the question of WHY do you need to do the reseed arises. Is the information from that IDENTITY based PK used anywhere else for anything at all? For example, are you using this as a feeder/staging table to populate permanent tables?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 15, 2017 at 8:16 am
Jeff Moden - Monday, May 15, 2017 8:01 AMstephen.plant - Monday, May 15, 2017 6:52 AMTheSQLGuru - Monday, May 15, 2017 5:51 AM1) Why is a DELETE a no-go?2) You didn't give us enough information. The key missing component is how many rows will remain in each table. Take it to the extreme: a) if you keep all BUT one row then clearly a DELETE is optimal and b) if you keep ONLY one row then clearly a copy/truncate/rename process is optimal. Where do you fall between those two? Another missing element is what is the indexing on each table?
The desired outcome is empty tables, which i inferred with the 'clean' apologies for any ambiguity.
There is a Primary Key on each table that is the identity that will need to be reseeded.If the IDENTITY column is the PK and you need to reseed to the previous MAX after truncation, the question of WHY do you need to do the reseed arises. Is the information from that IDENTITY based PK used anywhere else for anything at all? For example, are you using this as a feeder/staging table to populate permanent tables?
Aah the magic question, these tables do feed another part of a system that is not our responsibility and yes they do use the values in the identity column as I have just found out.
May 15, 2017 at 8:36 am
turn on identity insert for the table when loading
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 15, 2017 at 10:19 am
As a general rule with large staging tables, I truncate if there are no indexes, primary keys, etc that would have to be dropped during the table load. If these are created in the process, I drop and recreate the table. Not sure what use you are making of the identity column further down in the process if it resets, but it's not impossible that it has a purpose. If it's not being forwarded to the next table, it may not be necessary.
May 15, 2017 at 1:34 pm
The major difference between delete and truncate is that delete is a standard SQL command, but it does logging so that the deletion can be backed out. Truncation, on the other hand, is an old Sybase extension that moves a pointer to the very start of the physical storage of the data, so the storage can be overwritten. It doesn't do any logging. It also doesn't do any porting that was important to you.
You might want to reconsider using identity as a key. It can never be a proper relational key since it's a count of physical insertion attempts on one machine, to one table in one particular SQL product. You'd be better off using the CREATE SEQUENCE structure instead. Essentially, this creates a generator for well-controlled numeric values in an increasing sequence. It is not a table property! Since its external it can be referenced in other places in your SQL.
Please post DDL and follow ANSI/ISO standards when asking for help.
May 15, 2017 at 1:45 pm
Truncate logs the page deallocations
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 49 total)
You must be logged in to reply to this topic. Login to reply