May 2, 2019 at 5:15 am
Hi...Im working on SQL Server, So I am using a SQL Server database with many tables that store large bits of information that is searchable. These tabled, columns are full text enabled.
My issue is I do not need the data in the tables. I only query the catalog but never the actual data.
Is it possible to run a job that somehow deletes the data from the table but keeps the catalog in place, so that technically it is still searchable?
This will save a lot of storage space and make the DB more manageable.
May 2, 2019 at 11:42 am
Well first you should not forget that IF you would populate a FT Index and delete the data afterwards: You are aware that you still need the space initially to have base data to populate your FTI from? If so, great. The next "big news" is your FTI suffers from index fragmentation, if you attempt to rebuild the FTI you need the data in the table first, it does not rebuild from it's own Data Files but from the DB.
In theory(actually in practice, however you'll see your vision will most likely stay a theory) you can control the population of FTIs so in theory(now this part is real theory) you can do something like this:
However this process out of my mind you would have to do for at least the following Scenarios:
And let's not forget this scenario is as far away from what Microsoft did intend with FTI as possible so if you go that route you're far away from any possible support from Microsoft or these forums (the last part is a guess).
You also might want to consider that you only need to query the FTI because the FT Catalog is based off a table or think of it differently: Instead tons of users having to scan the actual table (SELECT * from table where column like 'value' will return essentially the same as an FTI on the table unless the expression is filtered out), they can crawl the FTI instead and the table is free'd from many potential table locks. If you don't need data to be stored on Disk, consider In-Memory tables with no persistence.
May 3, 2019 at 9:11 pm
One possibility would be to generate scripts of the database and all the tables etc., then use the script to create a new empty database.
(In SQL Server Management Studio, right-click the database, select Tasks, then Generate Scripts...)
May 8, 2019 at 8:24 pm
why do you want to delete the data but keep the schema? a database without data is just a useless empty box
***The first step is always the hardest *******
October 10, 2023 at 6:45 am
This was removed by the editor as SPAM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply