February 1, 2016 at 1:37 pm
I have a transnational table which every time dropped and created in a job to get updated data.
Earlier there was no index, but now one non-unique, non-clustered index was added. What should be the best practice while dropping and creating the table again? Should i drop an index, delete the table instead of dropping and insert the data into empty table?
Thank for valuable suggestions.
February 1, 2016 at 2:05 pm
sqlinterset (2/1/2016)
I have a transnational table which every time dropped and created in a job to get updated data.Earlier there was no index, but now one non-unique, non-clustered index was added. What should be the best practice while dropping and creating the table again? Should i drop an index, delete the table instead of dropping and insert the data into empty table?
Thank for valuable suggestions.
How about just truncating the table?
TRUNCATE TABLE schema_name.table_name;
This will remove all data from the table almost as fast as dropping it and recreating the table.
Losing indexes is one reason why dropping and recreating tables in the scope of an automated process is usually a bad idea. Permissions is another one.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 1, 2016 at 2:06 pm
As an aside, it is a best practice in SQL Server for all tables to have a clustered index and a primary key. Any reason why this table should not have both?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 1, 2016 at 2:47 pm
Orlando Colamatteo (2/1/2016)
As an aside, it is a best practice in SQL Server for all tables to have a clustered index and a primary key. Any reason why this table should not have both?
It depends on the usage of the table. The one exception to this is for staging tables. You might not need a clustered index at all. It wouldn't hurt, but if it's just a staging area, it doesn't need one. I prefer to have clustered indexes on everything as well, but this is an exception. If the data you're importing is large, it might be worth it to have a staging database that's in simple recovery mode, but that's another topic.
Orlando's point above is a good one. Instead of dropping and recreating it every time, truncate it and clear it out. If it's a reporting table, you'll probably want to rebuild the indexes after you load it back up.
February 1, 2016 at 3:01 pm
Ed Wagner (2/1/2016)
Orlando Colamatteo (2/1/2016)
As an aside, it is a best practice in SQL Server for all tables to have a clustered index and a primary key. Any reason why this table should not have both?It depends on the usage of the table. The one exception to this is for staging tables. You might not need a clustered index at all. It wouldn't hurt, but if it's just a staging area, it doesn't need one.
It depends on what you're doing with the data after it is loaded. If a CI is helping a join to a downstream table sometimes it's cheaper to have it on the table before loading than to build it after, e.g. if the incoming dataset is already sorted in the CI order.
I prefer to have clustered indexes on everything as well, but this is an exception. If the data you're importing is large, it might be worth it to have a staging database that's in simple recovery mode, but that's another topic.
It all depends. Best practices are just that. They are guidance. That is why I asked why the table should not have both a CI and a PK, to see if the OP had considered it but leaving room for the possibility that it might be fine.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 1, 2016 at 3:08 pm
Orlando Colamatteo (2/1/2016)
sqlinterset (2/1/2016)
I have a transnational table which every time dropped and created in a job to get updated data.Earlier there was no index, but now one non-unique, non-clustered index was added. What should be the best practice while dropping and creating the table again? Should i drop an index, delete the table instead of dropping and insert the data into empty table?
Thank for valuable suggestions.
How about just truncating the table?
TRUNCATE TABLE schema_name.table_name;This will remove all data from the table almost as fast as dropping it and recreating the table.
Losing indexes is one reason why dropping and recreating tables in the scope of an automated process is usually a bad idea. Permissions is another one.
In most cases I would agree with this. However, there are some exceptions, and this may be one.
If a lot of data has to be inserted in the table directly after creating it (and before using it) - e.g. for a staging table or similar -, then the total load process will often be faster if you create nonclustered indexes after loading the data.
Note that this is for nonclustered indexes only. The clustered index should always be created before loading, and any nonclustered indexes that are used to enforce uniqueness are probably also better created before loading.
February 1, 2016 at 3:42 pm
Hugo Kornelis (2/1/2016)
Orlando Colamatteo (2/1/2016)
sqlinterset (2/1/2016)
I have a transnational table which every time dropped and created in a job to get updated data.Earlier there was no index, but now one non-unique, non-clustered index was added. What should be the best practice while dropping and creating the table again? Should i drop an index, delete the table instead of dropping and insert the data into empty table?
Thank for valuable suggestions.
How about just truncating the table?
TRUNCATE TABLE schema_name.table_name;This will remove all data from the table almost as fast as dropping it and recreating the table.
Losing indexes is one reason why dropping and recreating tables in the scope of an automated process is usually a bad idea. Permissions is another one.
In most cases I would agree with this. However, there are some exceptions, and this may be one.
If a lot of data has to be inserted in the table directly after creating it (and before using it) - e.g. for a staging table or similar -, then the total load process will often be faster if you create nonclustered indexes after loading the data.
Note that this is for nonclustered indexes only. The clustered index should always be created before loading, and any nonclustered indexes that are used to enforce uniqueness are probably also better created before loading.
That is why I said usually. In asking the follow-up question
As an aside, it is a best practice in SQL Server for all tables to have a clustered index and a primary key. Any reason why this table should not have both?
I was thinking if the nonclustered was a good candidate to become the clustered it could be a win-win.
Sometimes it is the right thing to do, namely dropping and re-adding tables or managing indexes in the context of a batch process, but it's risky for several other reasons besides just the batch process itself. Dropping and recreating tables or indexes inside an automated process could conflict with something a DBA or developer does while trying to tune the system, themselves adding new or tweaking existing indexes. The automated process could simply come back tomorrow and revert their changes, or worse the table could end up with effectively too many indexes. Researching a system where batch processes issue DDL can be difficult, confusing, and just plain waste a bunch of people's time. This is a supporting reason why it's usually makes it a bad idea.
I am trying to leave the door open for the OP to provide more info. There are lots of options here and without more input it's impossible to say anything concrete.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 1, 2016 at 6:38 pm
Yes, the bottom line is that we don't know the usage of the table. Sqlinterest, you have a number of us interested in your situation. Would you care to post how you're going to use the table? I think e can curtail the rabbit holes and bring it back to your question. 😉
February 2, 2016 at 2:31 pm
Hugo Kornelis (2/1/2016)
The clustered index should always be created before loading
And usually (well, that word - "usually" 🙂 ) a perfect candidate for a clustered PK on a staging table would be an identity column populated "behind the scene".
It might be quite useful when you need to preserve the order of imported records.
_____________
Code for TallyGenerator
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply