Is there a limit to the number of tables in a SQL Server database AND view?

Question

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!)

Answer 1

SQL Server doesn't have a table limit. Rather, it has an object limit (of which tables are a type of object). So, in effect, the sum of all objects (indexes, views, tables, procs, etc...) can't exceed 2 billion-ish (2,147,483,647 to be pedantic).

There is no hard limit to the amount of joins (or unions) you can have in a single query. The limitation will be hardware related.

Answer 2

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.