Remove / Delete Table with millions of records and no indexes

Question

0.00/5 (No votes)

See more:

I have a table with hundred million records and which has no indexed created there.

Now i need to remove the table from production without interrupting the database.

I have tried following but it all fails,

1.) Removing constraints
2.) Truncate table
3.) Creating index for faster processing it all fails

Is there any method for quick deleting of the data?

Posted 10-Jun-15 4:35am

Hetal Jariwala

Add a Solution

Comments

ZurdoDev 10-Jun-15 10:46am

The quickest way is to truncate the data.

TRUNCATE TABLE Table1

However, you'll get an error with any other method so if something isn't working, share the error so we can help.

Hetal Jariwala 11-Jun-15 2:09am

I don't need table i have tried to drop table but it's taking too much time and also other process are getting stuck when i'm firing the query

ZurdoDev 11-Jun-15 7:29am

Then truncate it and then drop it. I don't see any other choice.

Michael_Davies 10-Jun-15 13:14pm

Try DROP and then CREATE if you still need the table.

Hetal Jariwala 11-Jun-15 2:05am

I don't need table i have tried to drop table but it's taking too much time and also other process are getting stuck when i'm firing the query

Michael_Davies 11-Jun-15 2:40am

You may not be able to get a quicker delete but you can control the resource usage and be kinder to other processes by deleting in chunks; get the record count and loop deleting say 100 records at a time using LIMIT.

CHill60 11-Jun-15 15:45pm

Could it be an issue with your transaction logging? Is there enough space on the server for example?

Hetal Jariwala 29-Aug-15 6:41am

Client told to stop the site and my problem is solved!

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sreekanth Mothukuru · Accepted Answer · 2015-06-16T21:19:00

Create another table with the same schema as that of your original table and this time with all indexing set.

Once everything is ready execute a quick table rename statements on your original table to something else and rename the new table with the original table name.

This renaming tables doesn't impact performance at all.

Here are the steps to follow:
1) Create a new table "Table_A_New" with same schema as that of original table "Table_A"
2) Add required indexing to table "Table_A_New"
3) Remove any constraints on "Table_A"
4) Execute rename statement on table "Table_A"
EXEC sp_rename 'Table_A', 'Table_A_Old';
5) Execute rename statement on new table "Table_A_New" to original name
EXEC sp_rename 'Table_A_New', 'Table_A';
6) Add constraints back to "Table_A"