Question on Limits -- sql server new users

Thanas
9/19/2006 2:31:50 PM

Thanks Arnie for your response, but what I am trying to find out is how
many different relationship can stem from one table. In other words,
how many cascades updates and delete can be triggered from one table.
For instance in Access there seems to be a limit of 32. This limits
seems to be the same limit with the number of index allowed. So as you
can see I am just trying to understand whether SQL Server 2005 has a
similar limitation and if it does what it is.

Thanks everyone for your patience
-Thanas

[quoted text, click to view]

Arnie Rowland
9/19/2006 4:44:18 PM

Nice script Hugo.

Now to populate all 1000 tables and DELETE from the first one to test the
CASCADE ...

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

[quoted text, click to view]

Hugo Kornelis
9/20/2006 1:21:35 AM

[quoted text, click to view]

Hi Thanas,

I used the script below to test these limits. Change the end value in
the WHILE statement, execute it, copy and paste the output in a new
query window and execute it there.

I had no trouble generating up to 1,000 foreign key constraints to the
same table. A test with 10,000 tables and 10,000 FK constraints all to
the same table is still running (3 minutes already) while I type this.

Don't expect much in terms of performance from a setup like this,
though. And better not ask SQL Server te render the execution plan for
the DELETE statement...

Here's the script I used:

declare @i int
set @i = 0
print 'use master
go
create database testFK
go
use testFK
go
create table t (a int not null primary key)
insert into t values(1)
go'
while @i < 10000
begin
set @i = @i + 1
print 'create table t'+cast(@i as varchar(20))+' (a int not null
primary key references t on update cascade on delete cascade)'
end
print 'go
delete from t
go
use master
go
drop database testFK
go'

--

Hugo Kornelis
9/20/2006 2:09:33 AM

[quoted text, click to view]

Hi Arnie,

Here's the adapted script. Let me know what you find. <eg>

declare @i int
set @i = 0
print 'use master
go
create database testFK
go
use testFK
go
create table t (a int not null primary key)
insert into t values(1)
go'
while @i < 10000
begin
set @i = @i + 1
print 'create table t'+cast(@i as varchar(20))+' (a int not null
primary key references t on update cascade on delete cascade)'
print 'insert into t'+cast(@i as varchar(20))+' values(1)'
end
print 'go
delete from t
go
use master
go
drop database testFK
go'

--

Hugo Kornelis
9/20/2006 2:12:16 AM

[quoted text, click to view]

Just finished, after 51 minutes and 18 seconds (according to the clock
in SSMS).

Result: error. Not while creating the tables and constraints, but while
deleting the single row from table T.

Msg 8623, Level 16, State 1, Line 1
The query processor ran out of internal resources and could not produce
a query plan. This is a rare event and only expected for extremely
complex queries or queries that reference a very large number of tables
or partitions. Please simplify the query. If you believe you have
received this message in error, contact Customer Support Services for
more information.

In the mean time, I found this in Books Online:

"The Database Engine does not have a predefined limit on either the
number of FOREIGN KEY constraints a table can contain that reference
other tables, or the number of FOREIGN KEY constraints that are owned by
other tables that reference a specific table.

"Nevertheless, the actual number of FOREIGN KEY constraints that can be
used is limited by the hardware configuration and by the design of the
database and application. We recommend that a table contain no more than
253 FOREIGN KEY constraints, and that it be referenced by no more than
253 FOREIGN KEY constraints. The effective limit for you may be more or
less depending on the application and hardware. Consider the cost of
enforcing FOREIGN KEY constraints when you design your database and
applications."

(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/1e068443-b9ea-486a-804f-ce7b6e048e8b.htm)

--

Thanas
9/20/2006 6:55:05 AM

I just want to thank everyone this has been very useful information. I
really do apprciate your time and effort.

-Thanas

[quoted text, click to view]

Hugo Kornelis
9/21/2006 12:11:23 AM

[quoted text, click to view]

Hi Thanas,

No problem - it's always fun to try to find new ways to break SQL
Server.

But I really hope that your real appplications will get nowhere near the
limits I've discovered. If they do, it's high time for a redesign!

--

Thanas
9/21/2006 8:39:37 AM

Nah, our design does even come close. Although we do have an Access
table that has some 32 foreign keys and it will grow. My manager
wanted me to find out what the limit was in SQL Server. I did my first
Access 97 to SQL Server 2005 migration last thursday and it went
perfectly. I love the new SqL SErver, it is so incredible fast.

-Thanas

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server new users

sql server new users : Question on Limits