Hi, Why only 249 non-clustered index can be created in sybase? Any
reason?

On 08-Aug-2011 13:44, Subind wrote:
The index ID column is a 2-byte 'smallint' meaning it can count no
further then 255.
Since 255 is used to indicate text/image columns that 254 non-clustered
indexes at most. I guess they cut it off at 249 so as to keep some
values in reserve for special purposes, should they ever come up.
I must say I've never seen a real-life system with more than 100
indexes per table (I think the maximum I've seen is 20-30). The limit
of 249 seems good enough for most purposes.

--

HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks& Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob (AT) NO (DOT) SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------

On Aug 8, 6:28*pm, Rob V
<r... (AT) DO (DOT) NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY> wrote:
smallint is two byte == [-32768, 32767]

But checking dbcc serverlimits reveals


Max number of indexes on a
table : 250

And since indid 0 == table, this leaves 249.
Strictly, 1 is a clustered index on APL so you can have at most 248
NCI.

That is not correct.

sysindexes.indid is a TINYINT. one byte. That means 0 to 255.

__0 is the Heap (in DPL/DRL: always; in APL: only if you don't have a
Clustered Index)
__1 is the Clustered index (never for DPL/DRL, as there is no such
thing)
255 is the Text/UniText/Image Chain, if there are any such column in
the table

DBCC SERVERLIMITS is referring to NonClustered indices, including the
Placement Index, excluding the Clustered index. That 250 is a hard
limit, even though 254 NCIs is possible in indid.

Any table with more than 7 indices is grossly un-normalised.

Anyone implementing more than 20 NCIs in a table has holes in their
head.

On Oct 16, 12:20*am, Derek Asirvadem <derek.asirva... (AT) gmail (DOT) com>
wrote:

Correction. It refers to CIs and NCI (including the PI).

That means one CI and up to 249 NCIs (including the PI)