view combines tables from multiple dbs

• smv929

  SSCrazy

  Points: 2889

  More actions

  February 1, 2007 at 7:34 am

  #176686

  Is the following view the best way for 1) combining identical tables from 14 identical databases and 2) inserting a db name field as the first column so you can know which database a record came from? Server is SS 2000 sp4.

  CREATE view vw_item as  

  select *

  from ( 

       select 'db1' as site, * from db1.dbo.item  with (nolock)

       union all 

       select 'db2' as site, * from db2.dbo.item   with (nolock)

       union all 

       select 'db3' as site, * from db3.dbo.item   with (nolock)

       union all 

       .....etc......

  ) item

  It works ok, but I do notice on occassion that there is a performance hit when the view is used in a complex query. The Site field is very important; it can't be indexed. I've never had success with an indexed view. Thanks.

  
  smv929

• Sugesh Kumar

  One Orange Chip

  Points: 27311

  More actions

  February 1, 2007 at 7:41 am

  #687099

  wats the number of rows returned by each database. i hope u r trying to create a view that will for sure hit ur sql performance.

  Cheers,
  Sugeshkumar Rajendran
  SQL Server MVP
  http://sugeshkr.blogspot.com

• Paul Paiva

  SSCrazy

  Points: 2237

  More actions

  February 1, 2007 at 10:22 am

  #687159

  How important is it that the query returns "live" data?  If you can tolerate some latency, you might try writing a stored procedure that fills a table.  The table can be indexed so that your query is efficient.  Then you can create a job to kick off the proc every so often when you need the data to be refreshed.

  Another advantage to the proc idea is that if the number of databases you include in your query changes, you can write some embedded SQL to intuit which is the correct list of databases and fill your table accordingly.

  Paul

  - Paul

  http://paulpaivasql.blogspot.com/

• Robert Davis

  One Orange Chip

  Points: 28027

  More actions

  February 2, 2007 at 11:27 am

  #687504

  Can you modify the tables? If you add a column to the tables indicating the database it is in (or better yet an integer value that references a table with the names of the database in it), those values can be indexed in the table and in the view.

  For example:

  Create

  Table Test1 (TestID int identity primary key, TestValue varchar(5) not null default ('hdiak'))

   

  Insert

  Into Test1 Default Values

   

  Alter Table Test1 Add DBName sysname not null Constraint DF_DBName Default(db_name());

  Create

  Index ix__Test1__DBName On Test1(DBName);

   

  Alter

  Table Test1 Add DBID smallint not null Constraint DF_DBID Default(12);

  Create

  Index ix__Test1__DBID On Test1(DBID);

   

  Select

  * From Test1

  Drop

  table Test1

  ---

  My blog: SQL Soldier[/url]
  SQL Server Best Practices: SQL Server Best Practices
  Twitter: @SQLSoldier
  My book: Pro SQL Server 2008 Mirroring[/url]
  Microsoft Certified Master: SQL Server, Data Platform MVP
  Database Engineer at BlueMountain Capital Management[/url]

• smv929

  SSCrazy

  Points: 2889

  More actions

  February 5, 2007 at 9:24 am

  #687806

  The view is used by a number of reports and programs. The number of rows returned by these queries vary from one record to 10 thousand on average.

  The database containing these tables and views is actually used for reporting. So I could add a site field to each table and index it. The problem is that this would probably affect many stored procedures, reports, programs and DTS programs.

  Thanks to all for your suggestions. Keep them coming if anyone has any further input.

   

  
  smv929