I do it all the time, but I don't really like it. However, there are many reasons why you can limit everything to a single database.
When I write a SQL statement, I ALWAYS use fully-qualified table names such as: databaseName.dbo.tableName I do this even if I'm in the default database specified by the DSN. This way, I never have to worry about what database is the default. The only problems I ran across are: 1. If two databases contain views, or stored procs, that reference each other at the same time. You may have a problem when you try to script these objects to move to a different server. 2. ER applications, such as ER/Studio, may not like, or support, multi-database ERDs. I know ER/Studio should support it in the future. Right now, the work-around is to replace "." with "_" such as databaseName_dbo_tableName. Then, right before creating the objects, you generate the DDL and replace the "_" with ".". Kind of a hack, but I guess it works. M!ke -----Original Message----- From: Scott Mulholland [mailto:[EMAIL PROTECTED] Sent: Friday, November 19, 2004 9:03 AM To: CF-Talk Subject: Database design question In theory is there any downside to joining cross database. I'm using sql server and was considering having my user data in one database and my content data in an other. In some cases I would need to do something like this as an example: 2 databases (MS SQL Server for arguments sake): USERS and CONTENT datasource=CONTENT select a.title, u.firstname, u.lastname from articles a INNER JOIN USERS.dbo.accounts u ON a.insertby = u.account_id Is there any performance hit or other reasons to avoid having them as separate databases? Thanks, Scott --- [This E-mail scanned for viruses by Declude Virus] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:184959 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
