Anyone know table limits in multi-schema environment?

My product is growing rapidly and currently I have a db for each client with identical schema. Of course maintenance is pretty hard. I was thinking of using a shared db but having a schema for each client (sql 2005) - I have almost 100 tables in the schema which means with just 10 clients the db would pass 1000 tables. My gut is telling me this ain't going to fly!

any ideas? and if it does work ... any thoughts on updating the internal schemas for each client?

thanks

-c

You definately can get over 1000 tables, since certain complex ERP systems and such have that just on their own, and that was even in older version of SQL Server.

According to :http://msdn2.microsoft.com/en-us/library/aa933149(SQL.80).aspx

The amount of tables is actualy only limited by the amount of objects (which means all triggers, tables, stored procedures, etc count toward this limit). The limit is... 2,147,483,647

Good luck busting that. Oh, and thats SQL Server 2000, was too lazy to find the 2005 specs :)|||

Sounds responable to me. I was going off this article from MS that suggested no more than 100 tables per client schema in a single db but they don't specify why :)

http://msdn2.microsoft.com/en-us/library/aa479086.aspx

Any idea how to do updates to the schema? my current thinking is to get my app to login as each schema owner and execute the update script.

thanks

-c

ANSI-89 DB JOINS

Help...I have a DB I'm working with that I know doesn't work with the
ANSI-92 JOIN SYNTAX...I'm not sure how much this limits my ability to
deal with the following situation, so I'm soliciting the help of a
guru...I apologize for the lack of scripted table structure, but this
database is embedded in an application that I have no true schema for.
I have a crude diagram of the tables and some of the relationships, but
I've managed to have manually mapped some of the fields in the tables
I'm working with.

What I have is a table(A) that I need to join with 10 other
tables....I'm joining on an identifier in the (A) that may exist many
times in any of the other 10 tables...and may not be in ANY of the
tables.

When I run this query:

SELECT
SAMPLES.PK_SampleUID,UDFSAMPLEDATA02.AlphaData,UDF SAMPLEDATA01.AlphaData,UDFSAMPLEDATA03.AlphaData,
UDFSAMPLEDATA05.AlphaData, UDFSAMPLEDATA06.AlphaData,
UDFSAMPLEDATA07.AlphaData, UDFSAMPLEDATA08.AlphaData,
UDFSAMPLEDATA09.AlphaData,UDFSAMPLEDATA10.AlphaDat a

FROM SAMPLES, UDFSAMPLEDATA01
,UDFSAMPLEDATA02,UDFSAMPLEDATA03,UDFSAMPLEDATA05,U DFSAMPLEDATA06
,UDFSAMPLEDATA07 ,
UDFSAMPLEDATA08, UDFSAMPLEDATA09, UDFSAMPLEDATA10

WHERE UDFSAMPLEDATA02.AlphaData<>' ' AND
UDFSAMPLEDATA01.FK_SampleUID=SAMPLES.PK_SampleUID AND
UDFSAMPLEDATA02.FK_SampleUID=SAMPLES.PK_SampleUID AND
UDFSAMPLEDATA03.FK_SampleUID= SAMPLES.PK_SampleUID AND
UDFSAMPLEDATA05.FK_SampleUID = SAMPLES.PK_SampleUID AND
UDFSAMPLEDATA06.FK_SampleUID = SAMPLES.PK_SampleUID AND
UDFSAMPLEDATA07.FK_SampleUID = SAMPLES.PK_SampleUID AND
UDFSAMPLEDATA08.FK_SampleUID = SAMPLES.PK_SampleUID AND
UDFSAMPLEDATA09.FK_SampleUID=SAMPLES.PK_SampleUID AND
UDFSAMPLEDATA10.FK_SampleUID = SAMPLES.PK_SampleUID

I return what appears to be the gazillion COMBINATIONS of all the
fields in all the tables...they query doesn't even finish before the
ODBC driver I'm working with crashes my VBscript...

Is there some way to take the multiple returned rows from a join and
work them all into ONE row per identifier?
Any help I can garner would just make my week!

TIA!

JA database that doesn't support the ANSI92 syntax may have its own
proprietary syntax for outer joins. Otherwise, you can use UNION in
place of an outer join, assuming UNION and EXISTS are supported:

CREATE TABLE T1 (x INTEGER PRIMARY KEY)
CREATE TABLE T2 (x INTEGER PRIMARY KEY)

INSERT INTO T1 (x) VALUES (1)
INSERT INTO T1 (x) VALUES (2)
INSERT INTO T2 (x) VALUES (1)

SELECT T1.x, T2.x
FROM T1, T2
WHERE T1.x = T2.x
UNION ALL
SELECT T1.x, NULL
FROM T1
WHERE NOT EXISTS
(SELECT *
FROM T2
WHERE T2.x = T1.x) ;

--
David Portas
SQL Server MVP
--|||Thanks for the direction David - I'll start working with this...I'm
sure I'll have more questions as I go.

j

David Portas wrote:
> A database that doesn't support the ANSI92 syntax may have its own
> proprietary syntax for outer joins. Otherwise, you can use UNION in
> place of an outer join, assuming UNION and EXISTS are supported:
> CREATE TABLE T1 (x INTEGER PRIMARY KEY)
> CREATE TABLE T2 (x INTEGER PRIMARY KEY)
> INSERT INTO T1 (x) VALUES (1)
> INSERT INTO T1 (x) VALUES (2)
> INSERT INTO T2 (x) VALUES (1)
> SELECT T1.x, T2.x
> FROM T1, T2
> WHERE T1.x = T2.x
> UNION ALL
> SELECT T1.x, NULL
> FROM T1
> WHERE NOT EXISTS
> (SELECT *
> FROM T2
> WHERE T2.x = T1.x) ;
> --
> David Portas
> SQL Server MVP
> --|||You're welcome, but you'll probably find better help in a group or
forum dedicated to the database you are using. This is a Microsoft SQL
Server group.

--
David Portas
SQL Server MVP
--