6

I am trying to understand how the indexes are managed for below common data types: 1- Numeric (Integer, Decimals) 2- String (Varchar, Char) 3- DateTime

I have few questions:

1- How the indexes are stored for different data types? For example if have Numeric data like (100, 101, 100, 900, 700) & string data like (AAA,BBB,CCC,AAA,ABC) how the numericcolumn & stringcolumn index will store this data on disk?

2- Is there any different between the retrieval of a Numeric & String indexes? For example if i try the SELECT statements on numericIndexed column & on sringIndexes columns how they are retrieved?

3- Is the indexes are stored different by SQL Server & Oracle? OR they use the same logic? Regards.

Yaqub Ahmad
  • 291
  • 2
  • 12

2 Answers2

10

For SQL Server, an index entry is stored pretty much the same way as a normal record except for the row header. Also, index and record entries are never on the same page

Now, anything I add to this would be copy/paste of articles by cleverer folk than me, so I'll refer you to Paul Randal: Inside the Storage Engine: Anatomy of a record

Also, all indexes in SQL Server are B-Tree. Tables with clustered indexes in SQL Server are the same as Oracle Index Organised tables

As for a SELECT, you have to consider if an index is "covering" or not usually. This means the entire SELECT can be satisfied from the index only. Otherwise it may not be used or generate key lookups. For more, see

Community
  • 1
gbn
  • 70,237
  • 8
  • 167
  • 244
8

There are a number of different index types in different database systems, for the purposes of this reply though I'm mostly going to refer you the wikipedia discussion on B-Tree indexes because that is almost certainly the index type you mean. (nb to commenters I know that the trees aren't strictly b-trees but that doesn't matter for this discussion) This type of index will be created by sql statements such as

create index idx_t1_c1 on t1(c1);

In a B-Tree index the data is stored in a ordered tree, where the highest (or root) pages just have pointers to lower level branches which in turn have pointers either to lower branches or the lowest level pages(or blocks) that themselves hold pointers to the table pages that have rows that contain the indexed value. Retrieving indexed data via an index then becomes a matter of walking the tree down from the root via pointers all the way to the leaf(s) containing the value you are interested in. If you then need to go to the table you have direct access to the pages you need rather than having to scan the whole table. This mechanism is entirely independent of the datatype of the indexed column. The internals of Oracle vs SQL Server are different and protected by IPR, but the logic is essentially the same. The efficiencies come because typically the depth of the index will be quite low (2 or 3 levels for the vast majority of typical segment sizes) and the number of leaves containing values of interest will also be low so you are likely to be able to filter for the data you are interested in in only a handful of i/o operations rather than scanning the entire object.

If you really are interested in on disk storage then the commands below can be used to dump representations of the pages to disk for inspection. If you do this and are not deeply familiar with data structures from some formal information technology education you may regret it :) 

ALTER SYSTEM DUMP DATAFILE <n> BLOCK MIN <x> MAX <y> ; -- oracle file n, block x->y
DBCC PAGE (DBID,FILENUM,PAGENUM,<LEVEL>); -- mssql  -- level 0 to 3 increasing detail
Niall Litchfield
  • 1,079
  • 5
  • 6