September 23, 2010 at 10:14 am
Assume I have a customer data stored in the customer table for various clients, suddenly one client wants to segregate their customer data from this table. Can anyone help how to achieve this logic in SQL Server databases. Please help as soon as possible....
September 23, 2010 at 10:31 am
saravanansankarank (9/23/2010)
As per the database schema, we use to store the customer data in the customer table for all clients. Suddenly one of our client wanted to store his customer data separately without disturbing the current table structure. Can anyone help how to achieve this logic in SQL Server databases? Please help as soon as possible....
September 23, 2010 at 4:35 pm
saravanansankarank (9/23/2010)
Assume I have a customer data stored in the customer table for various clients, suddenly one client wants to segregate their customer data from this table. Can anyone help how to achieve this logic in SQL Server databases.
I would do CTAS to copy desired data out of that "community" Customer table into a "private" Customer table with identical design then I'll just delete the affected data from the original table.
Finally, create proper indexes in new table.
If percentaje of rows moved out of original table is high you may want to reorganize original table and indexes. Rebuilding the table's clustered index - assuming there is one - would do the trick.
I assume some changes on the application side will be needed - only you know what is needed in this departments.
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 23, 2010 at 7:10 pm
I should not create a separate table ...how can we achieve without creating any new table
September 24, 2010 at 7:51 am
You can't segregate the data within a table. If your client has to be completely isolated, then you have to move them to another table. The only other option you have is to use some type of security mechanism that limits the parameters that a user can pass to the system (best approach is to simply only expose certain parameters to certain users). But even that is not "isolating" the data.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 24, 2010 at 1:22 pm
saravanansankarank (9/23/2010)
I should not create a separate table ...how can we achieve without creating any new table
well... you can't have bacon without killing the pig meaning, you can't have it both ways. 😀
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply