0

I am allowed to create/manipulate objects in one database but not in another.

The Table I am interested in - let's call it DBNoModify.dbo.SourceTable has 2 fields Name & Description.

I would like a fulltext index on the concatenation (Name + ' ' + Description) of these but the catalog and index must reside in my separate database.

What are the options?

Hugh Jones
  • 2,706
  • 19
  • 30
  • 1
    Ask someone that *can* alter the database. If you can't change the other database, there's nothing you can do to it. There's no getting around that; Security is there for a reason. – Thom A Apr 18 '19 at 08:24
  • I have the necessary permission to alter the database but for reasons of politics I should not. – Hugh Jones Apr 18 '19 at 08:35
  • @larnu and - there is a solution, of course, which is to take a copy of the table into my database. I am hoping to avoid that – Hugh Jones Apr 18 '19 at 08:41
  • 1
    I wouldn't really class that as a solution; as soon as the other table is updated then your version is stale and therefore may not be fit for purpose. But my point still stands; if you don't have permissions (either my policy or security) to alter the database, then you need to get permission to do so or get someone who does to implement the requirement. Like I said, security and policies are there for a reason. if you can't obtain permission, or the resource, then you can only advise your manager why the table doesn't have a `FULLTEXT` index, and it's up to them to make the decision. – Thom A Apr 18 '19 at 08:49
  • @larnu - so in your view, that is the ONLY available approach? – Hugh Jones Apr 18 '19 at 09:01
  • 2
    Architecturally, it would be, let's say "awkward" to allow an index of any kind in a different database than where the original object resides. Each database has its own transaction log, and there's a reason for that. Data will need to be copied one way or another. You could consider using full-text indexing from something other than SQL Server (like Lucene) to read the original data directly -- that's still copying data in some form, but at least it wouldn't keep the base data around redundantly. – Jeroen Mostert Apr 18 '19 at 09:11
  • Effectively, yes, Hugh Jones. @JeroenMostert gives a good explanation of why the functionality you're asking for doesn't exist. I would suggest that if you talk to whomever is responsible for the other database will be more than happy to listen to you; and discuss the matter to improve things for both you and them. If not, that is an internal problem, unfortunately. If not having the `FULLTEXT INDEX` is that big of an impact, then you would then need to escalate to your management to make the final (un)informed decision. – Thom A Apr 18 '19 at 09:27
  • @Larnu - LOL - I have presented them with 2 options - the 'Snapshot' (i.e. a copy) or 'Materialsed View'. An English Pound to a pinch of pig-poo says they don't like either. – Hugh Jones Apr 18 '19 at 09:34

0 Answers0