>From what I can tell it is 900 bytes. Let me see if I can find the official >MS link.
Wes From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Joe D'Souza Sent: Thursday, April 22, 2010 1:14 PM To: arslist@ARSLIST.ORG Subject: Re: Indexing violations in the Remedy database.. ** Guillaume, I'll need to find information such as this for MS-SQL 2008. If you (or any of you readers) happen to have that handy, please share.. Cheers Joe -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org]on Behalf Of Guillaume Rheault Sent: Thursday, April 22, 2010 1:27 PM To: arslist@ARSLIST.ORG Subject: Re: Indexing violations in the Remedy database.. ** hi Joe, I can't tell for SQL Server or big blue (DB2), however her's the story for oracle In Oracle, the maximum length of the index is determined primarily by the block size parameter DB_BLOCK_SIZE: If 2K block size then maximum index key length=758 If 4K block size then maximum index key length=1578 If 8K block size then maximum index key length=3218 If 16K block size then maximum index key length=6498 How the maximum index key length is measured by? Maximum index key length=Total index length (Sum of width of all indexed column+the number of indexed columns)+Length of the key(2 bytes)+ROWID(6 bytes)+the length of the rowid(1 byte) The index key size is limited by the value of db_block_size, because a key value may not span multiple blocks. So, based on the size of the block, the size of the index depends. In fact, it is required that any index block must contain at least TWO index entries per block. So we can say that the maximum key length for an index will be less than half of the DB_BLOCK_SIZE. But we know that in a block there also needed space for PCTFREE, INITRANS and space for block overhead(Block Header,ROW Directory, Table Directory, etc). After considering these bytes the actual space that can be used for the Index key is actually just over 1/3 of the DB_BLOCK_SIZE. I don't know if SQL Server or big blue's maximum index size depends on the equivalent of oracle's block size, which is essentially a data storage parameter, but that would be something to check... In Oracle 10g, we have indexes that have numerous columns in our ITSM app, for instance for the "APR:Approver Lookup" form, and we have not run into issues, probably we have a large block size Guillaume ________________________________ From: Action Request System discussion list(ARSList) [arsl...@arslist.org] on behalf of Joe D'Souza [jdso...@shyle.net] Sent: Wednesday, April 21, 2010 7:47 PM To: arslist@ARSLIST.ORG Subject: Indexing violations in the Remedy database.. ** I need to collaborate with some of you SQL pro's out here to work with me to fetch a list of forms that may have indexes defined in Remedy that may violate a very basic rule in most standard RDBMS's. Indexes in most RDBMS's (Oracle, MS-SQL, Informix etc) that a Remedy server might be using, need to have indexed fields of less than 255 characters in length to the best of my knowledge. First of all I want to CONFIRM that this rule is true for all RDBMS's.. If it is true, then I ran a query to find any fields that may be indexed and violating this basic rule.. I can share this query with you if you want to join me on a collaborated effort to list all schemas that have fields that are indexed that may indeed be greater than 254 characters. Any volunteers? I have a ready query I can share with you.. Joe _attend WWRUG10 www.wwrug.com ARSlist: "Where the Answers Are"_ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: "Where the Answers Are"
