Why @@MAX_CONNECTIONS is Nondeterministic Functions

Question

Why @@MAX_CONNECTIONS is Nondeterministic Functions

parthi-1705

SSCrazy Eights

Points: 9277
More actions
May 3, 2011 at 7:11 am

#235629

Hi,
@@MAX_CONNECTIONS:
Returns the maximum number of simultaneous user connections allowed on an instance of SQL Server.
This means that user connection has been configure by using sp_configure.Once we are configured then it is not going to change until we change manually.Then why it is said as Nondeterministic Functions.
Thanks
Parthi

Viewing 5 posts - 1 through 4 (of 4 total)

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

HowardW One Orange Chip Points: 29109 More actions · Answer 1

Because you can change it manually...

Anything that can ever return a different value without you providing a different set of parameters is non-deterministic by definition.

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 2

If so then we can change the default language also which is @@LANGUAGE ,if we run this it will return as us_english or what ever it has been configured,if so then it should also be a Nondeterministic Functions but it is not then how cum @@max_connections alone 😀 😀 :w00t: :w00t:

Thanks
Parthi

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

parthi-1705 (5/3/2011)
If so then we can change the default language also which is @@LANGUAGE ,if we run this it will return as us_english or what ever it has been configured,if so then it should also be a Nondeterministic Functions but it is not then how cum @@max_connections alone 😀 😀 :w00t: :w00t:

@@Language is non-deterministic.

From Books Online:

All the configuration, cursor, metadata, security, and system statistical functions are nondeterministic.

@@Language is a configuration function, it's clearly listed under that category in BoL (see Configuration Functions (Transact-SQL) )

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 4

Thanks for that point.I am wondering i missed that line.:-D 😀 .I just have glance on Deterministic and Nondeterministic Functions there i suddenly found that @@Max_Connections as NonDeterministic Functions and was wondering that how cum? even though if we run as many times it will will return same as 32767 which we can configured with sp_configure.Thats why i got more confused and posted here.And finally it is clear that All the configuration, cursor, metadata, security, and system statistical functions are nondeterministic..

Once again thanks for the valid point.

Thanks
Parthi