February 4, 2009 at 3:18 am
Hi
I have a task: to make a new database with some of the tables in my actual database, but I have some sp that use tables in my old database.
How do you suggest to manage them?
To harcode the database? oldDB.dbo.table or to use dynamic sql?
There is a problem that the aplication's old database might change(the name, not structure), so I think that a dynamic solution is better...but this new database is a "heavy" one, the performance is a must.
I really don't know how is better...I thought to store the posible databases in a tables and to extract the name from it, but I will need a dynamic sql also..
Any ideas?
10x
February 4, 2009 at 7:28 am
You have other options as well.
YOu could create a view in the new database and access the view. Then if there are any changes to the other database you change the reference in the view.
Or
You can use SYNONYMS and if a change is made just change what the SYNONYM points to.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 4, 2009 at 8:05 am
Hmmm
really new ideas...
Does it worth using a view if the tables contain lots of records? and if the table is always growing ?
I haven't heard of the second option. I will search to see what you does that mean
February 4, 2009 at 8:15 am
I looked at synonyms a little and they seem a good option.
But I have the same question as for views: do they work good with big tables?
And you suggest that i create the synonym at the begining of the sp an droping it at the end?
Because if it is so I think that some time will be lost...
I'm waiting for your response because it seems interesting,but I don't know to many about this
February 4, 2009 at 8:26 am
I think the SYNONYM is really the way to go. The number of the rows in the table won't affect the usefulness of the SYNONYM.
I would not Create and Drop the Synonym in the SP. You create it once and then drop and re-create it if the source table changes.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 4, 2009 at 9:00 am
my tables contain data for activities that happen daily in a company,that's why it is so big...
Records are inserted at every hour. Are synonyms still a solution?
February 4, 2009 at 9:10 am
Yes Synonyms are probably the best option. A Synonym is basically just an alias for or a pointer to another object.
Think about it like a nickname. A person may be named Jonathan but go by Jon. Whichever one you use refers to the same person. A SYnonym basically says tableA can be also known as tableB and they both refer to the same object.
Synonyms were added specifically for cases like yours. You have an object you want to access and the location of that object may change so you create a synonym and just change the synonym when you change the object.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply