Maximum Capacity Specifications in SQL Server 2005

Question

Maximum Capacity Specifications in SQL Server 2005

Steve Jones - SSC Editor

SSC Guru

Points: 736063
More actions
October 15, 2004 at 2:33 pm

#59534

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 1

Steve,

Thanks for your table and especially research and comments. It will be very helpful for me. Developers sometimes do ask those questions of us.

Yelena

Regards,Yelena Varsha

Steve Jones - SSC Editor SSC Guru Points: 736063 More actions · Answer 2

You're welcome. Sorry I couldn't get more detailed, but since it's Beta, I'm sure MS doesn't want to publish too much.

Maurice Pelchat Mr or Mrs. 500 Points: 546 More actions · Answer 3

You should specify that varchar(max) and nvarchar(max) are new features specific only to SQL2005 and that they make appears as if the row hold more that 8096 rows.

Varchar(max) and nvarchar(max) are the easy way to strings larger than 8000 bytes in T-SQL. In fact they are similar to text data except that string manipulations are allowed on them.

Maurice Pelchat Mr or Mrs. 500 Points: 546 More actions · Answer 4

Maurice Pelchat

Mr or Mrs. 500

Points: 546

November 9, 2005 at 4:05 pm

#603140

Oups.. "as if the row hold more than 8096 bytes"

aragorn SSC Enthusiast Points: 137 More actions · Answer 5

I haven't been able to find max capacity specs for Analysis Services 2005. Has anyone else seen them?

I thought I saw somewhere that any constraints have been effectively lifted.

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 6

One spec that's missing from this list is the max RAM - both Standard and Enterprise can use as much as the OS can support, either 32- or 64-bit. The new Workgroup Edition supports up to 3GB RAM just like 2000 Standard.

GrumpyOldDBA SSC Rookie Points: 41 More actions · Answer 7

Errrr, I'm guessing that was a slip William, as SQL200 Standard can only support 2GB (http://msdn2.microsoft.com/en-us/library/aa933149(SQL.80).aspx).

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 8

That's right, only 2000 Enterprise can use more than 2GB. Sorry about that.

matt stockham SSCrazy Eights Points: 9942 More actions · Answer 9

Re. max row size, standard varchar and nvarchar can exceed the row limits in 2005

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

Very handy for me as I'm converting some columns from varchar(4000) to nvarchar.

BuckWoody Ten Centuries Points: 1077 More actions · Answer 10

You have a few errors here. Number one, the page you're referring to in this article IS NOT from Microsoft, and isn't authoritative. The actual page is at:

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

And is both authoritative and correct.

Second, the BOL team does not just "cut and paste" from earlier versions. Each topic is reviewed by a development team for each release and modified accordingly. The site you're referencing IS a cut and paste, but it is not kept up to date as BOL is. BOL is updated several times a year, and Microsoft works hard to make sure it's correct. There are 48,000 pages in Books Online, and it is translated into over 9 languages, and subject to legal terms and conditions in hundreds of countries.

Third, are there errors in BOL? Of course. But each page has a section for feedback, which automatically opens a bug to the writer where you can ask about changing it. If you're correct, the writer changes the topic and the site is updated, making it better for all of us. We all depend on SQL Server to have accurate documentation, so it's best if we try and make it better all the time.

Buck Woody
MCDBA, MCSE, Novell and Sun Certified

Greg Charles SSC-Forever Points: 45403 More actions · Answer 11

FYI: There is a maximum capacity spec page in the RTM BOL. Keep in mind that Steve's article was published in October 2004.

Greg

Jags2001 SSCommitted Points: 1529 More actions · Answer 12

What would it take MS to increase "REFERENCES per table" from 253? (This is the number of foreign keys that a table can be referred to). In a large DW or OLTP, this is way too small. Oracle is unlimited and for that they have some bragging rights.

JA

BuckWoody Ten Centuries Points: 1077 More actions · Answer 13

Actually Oracle doesn't have those bragging rights. Microsoft has unlimited references per table. From the BOL note (not the page this article has referenced, but the REAL Books Online):

^{http://msdn2.microsoft.com/en-us/library/ms143432.aspx}

⁴Although a table can contain an unlimited number of FOREIGN KEY constraints, the recommended maximum is 253. Depending on the hardware configuration hosting SQL Server, specifying additional foreign key constraints may be expensive for the query optimizer to process.

So it's just a matter of query processor optimizations, not a hard limit.

Buck Woody
MCDBA, MCSE, Novell and Sun Certified

BuckWoody Ten Centuries Points: 1077 More actions · Answer 14

Ah - my mistake - you're referring to the REFERENCES, not REFERENCES TO! My bad.

Buck Woody
MCDBA, MCSE, Novell and Sun Certified