Dear all, I have a question about a large data table. I have a table with more than 400 million records, I would like to hear any Idea how to manage with it? Any idea will be helpful for me. Thanking you in advanced for your effort Gogolo.
I'm not sure I understand your question. Are you asking: - How to **ALTER** the table (adding/removing columns, constraints, indexes etc) - What to do with the table in order to get optimal performance in the database. In the first case - I agree with @Håkan Winther. Don't use any GUI tools to alter the table, it will cause unnecessary headache and possibly gigantic transaction logs etc. In the second case, it will depend largely on what's in the table, how much transactions there are, how often it's selected from etc.
Related to ALTER for adding/removing columns to the table I will probably a little bit in contrast with @Håkan Winther and @Magnus Ahlkvist. To Add or Remove columns from very large table it is better to create a new table with more/less columns and then INSERT SELECT data to it from the original table and then DROP the original table and rename the new one, which is approach which also the SSMS uses. It is better from 2 things. if you use ALTER directly on that table, ALL data will be read and then written back with added/removed columns. If adding a column you will receive page splits and also large amounts of data written to transaction log as all th changes will be logged. Wen you create a new table, you will populate it by BULK INSERT which is minimally logged operation (if you are using a FULL RECOVERY mode, you can temporarily switch to BULK mode for that purposes). Also your data will be correctly aligned without any page splits and will be placed sequentially. The only pro of this approach is, that you need double the size in the data file group. But on the other side the first approach requires large amount of data in TLog.
First question is, are you having performance problems with the table now? If so, what are they? You may simply need one or more indexes. The most common cause of performance problems is the code, not the structure. Have you looked at the code being run against the table? Is it avoiding common issues like functions on columns in the WHERE clause and stuff like that? Finally, if all of this has been carefully addressed, and I would do these things first, you can look at addressing performance issues with hardware. If the data structure supports it, you can look into partitioning where you split the storage up across multiple disk drives in order to distribute access. That's one of the hardest things to do and only helps when you can distribute the data and purchase the hardware to support it.