December 1, 2007 at 2:11 am
Hai,
I have a table in one db and and I want to reference it into another table which is in some other db on the same server.
For example:
1. Table name in first database is "MyTable".
2. Referencing table name in second database is "ReferenceTable".
3. I have created one synonym for "MyTable" in second database.
4. Next I have created one function "CheckReferencingColumn", which returns the status of the record present in the table in first db.
5. Added check constraint to "ReferencingColumn" column of "ReferenceTable".
like:
Alter table ReferenceTable Add constraint FK_MyTable_ActualColumn
check(dbo.CheckReferencingColumn(ReferencingColumn) = 1)
6. This works fine for me, but I am not sure that it gives any performance issues in use in long running of database.
can any one please suggest a better way of doing this.
Thanks & Regards,
Kiran.Y
Regards,
-Kiran
December 1, 2007 at 9:14 am
- IMO referencing tables should reside on the same database for consistency and/or unit of recovery.
If you did split for performance reasons(split files), check out using filegroups.
- if you split relationship over a number of db, you'll have to work out
a DRP procedure to also check interdatabase relationships.
For use at upgrade time, modification time, recovery time !
- On the same server you can use cross database ownership so sqlserver can handle security in a better way. Check bol for more info.
You can also use the 4 part name like
select ...
from db1.schema.table T
inner join db2.schema.table2 T2
on T.colx = T2.coly
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 1, 2007 at 11:44 am
What is the function doing? I tend to agree with the advice above, but I'm curious why you have two databases. Also, hard to tell if there is a performance issue without more information.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply