Views

Question

Post reply

Views

Steve Jones - SSC Editor

SSC Guru

Points: 734418
More actions
December 11, 2008 at 9:33 pm

#63542

Comments posted to this topic are about the item Views

Viewing 13 posts - 1 through 12 (of 12 total)

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

Henrik Staun Poulsen SSCertifiable Points: 6550 More actions · Answer 1

except if you use a Table Value UDF (User Defined Function), as this example shows:

http://www.sqlservercentral.com/articles/Dynamic+SQL/65154/

Best regards,

Henrik Staun Poulsen

http://www.stovi.com

Shaun McGuile SSCarpal Tunnel Points: 4111 More actions · Answer 2

9% chose 30,000 - these people should not be anywhere near a keyboard never mind a database. 😀

31% picked 1024 - These people have no google skills. 😀

54% - got it right which implies, as I was one of them, that I know more than some people.;)

6% - went for 32 these I would forgive for trying to be sensible. Like who adds more than 32 tables to a query - typing the SQL for the joins would be so fraught with risk.:P

Well we should all realise that putting 256 tables into a Select is a bad idea to start with anyway....

Does putting 20 tables into a view and then using 20 such views count as 400 tables or just 20?

--Shaun

Hiding under a desk from SSIS Implemenation Work :crazy:

Arshad Ali-556241 Say Hey Kid Points: 706 More actions · Answer 3

More details on "Maximum Capacity Specifications for SQL Server" can be found on the below given link:

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

With Thanks and Regards
Arshad Ali
Microsoft India

My Blog - http://arshadali.blogspot.com/[/url]

Scott Arendt SSCrazy Eights Points: 8218 More actions · Answer 4

Shaun McGuile (12/12/2008)
Does putting 20 tables into a view and then using 20 such views count as 400 tables or just 20?

In SQL Server 2000 (not sure about 2005 or 2008), it would count as 400. We used to have tables for each month and then views that covered an entire year and I would run into the limit often when trying to pull some data from them.

Scott

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 5

Actually that's a distribution I would expect. this is a trivia question/answer, and unless you'd read Henrik's article the other day, it would be easy to have 1024, a logical number, or 30,000 stick in your head.

30,000 has been thrown around a lot as its' the limit for sparse columns, someone might remember reading that.

1024, lots of 1024 limits in computing.

32 nesting levels in a trigger, might get confused, but that seems like a low, albeit more practical to read, number of tables in a trigger.

You can google if you want for answers, but it somewhat defeats the idea. You know it or you don't. If you google, then you are semi-cheating yourself on the score.

Jerry Hung SSChampion Points: 12968 More actions · Answer 6

I guessed wrong (1024), such a natural number.... 😛

It's really a trivia. no Google beforehand

I still remember the discussion about max # of nonclustered indexes (which is 249), and how Microsoft came to that number is just... unknown. It's not 256, or 255.

http://msdn.microsoft.com/en-us/library/ms189280(SQL.90).aspx

The maximum number of nonclustered indexes that can be created per table is 249.

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

Shaun McGuile SSCarpal Tunnel Points: 4111 More actions · Answer 7

My Total guess =

1 Clustered + 249 Non Clustered + 6 spare slots (for internal use) = 256 indexes 😀

How's that sound Jerry?

--Shaun

Hiding under a desk from SSIS Implemenation Work :crazy:

Anipaul SSC-Insane Points: 24681 More actions · Answer 8

Actually you can guess with the options. There should have been options with 128 and 512 which would make this question more interesting.

Jon Summers Mr or Mrs. 500 Points: 579 More actions · Answer 9

Books OnLine for 2008:

Tables per SELECT statement : Limited only by available resources

Regards, Jon Summers

Henrik Staun Poulsen SSCertifiable Points: 6550 More actions · Answer 10

j.summers (1/4/2010)
Books OnLine for 2008:
Tables per SELECT statement : Limited only by available resources

Thank you very much for sharing this observation.

Best regards,

Henrik

TomThomson SSC Guru Points: 104773 More actions · Answer 11

This is one of those cases where MS contradicts itself, BoL says in one place that the limit is 256 and in another place that there it's limited only be available resources. That makes it a fun question.

http://msdn.microsoft.com/en-us/library/ms177634.aspx says there's a limit of 256.

http://msdn.microsoft.com/en-us/library/ms143432.aspx says "Limited only by available resources".

And when I went to click on 256 I somehow clicked on 32. Looks like I'm as careless as MS is!

Tom

kapil_kk SSC-Insane Points: 21316 More actions · Answer 12

Nice Question..

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/