Is there a limit to the number of tables in a SQL Server database AND view?
limit, sql-server-2008, view
Solution
Well, for limits, you can look at Maximum Capacity Specifications for SQL Server
As another option I would look at using partitioned tables and partitioned indexes.
One of the important things to note here is
The data of partitioned tables and indexes is divided into units that can be spread across more than one filegroup in a database. The data is partitioned horizontally, so that groups of rows are mapped into individual partitions
Which basically states that you can spread the partitions accross various logical file groups. This should provide you with coniderable performance improvements.
Problem
I'm working with a database on SQL Server Standard edition that loads data every day - performance of the `SQLBulkInsert` is slowing down as the table grows and indexing I/O kicks in (even with disable/rebuild, its getting slower) So, an alternative suggested to me was to create a view that references each one of the daily tables (or the last 30 for example). Should just be a case of `SELECT * FROM x UNION ALL SELECT * FROM y...` Is there a limit to the number of tables that can be included, or the length of the view definition? AND Is there a limit to the number of tables in a database? OR - is there a better way to do this (without spending any money or I'd move to SQL Server Enterprise and use partitioned tables!)