asked
How the indexes are managed?
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 numeric column & string column 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 Numeric Indexed column & on String Indexed columns how they are retrieved? 3- Is the indexes are stored different by SQL Server & Oracle? OR they use the same logic? Regards.
The main differences you're going to see for storage and retrieval of the different data types is not so much the data itself, but the size of the data. Since all index keys are stored in 8K pages, the size of the key (the column or columns that define the index) affects how much data you're going to get on to a page. The less data on a page, the less efficient the index is for retrieval. So for example, an integer (8 bytes) is going to be more efficient than NVARCHAR(50) (100 bytes). But, you can't just go with the smallest sized data either because that affects how selective the index is. For example, simply storing a bit would take up the least amount of space on the index, but you would only have two values for that index, so it would be completely useless for retrieving data.
To see details on data types, check out the [Books Online entry on Data Types][1]. You can find details on the storage needs for each data type there. As far as storage and retrieval goes, the storage engine of SQL Server works in data pages (8 kilobyte). When you run a select, the storage engine will retrieve the data from the pages that store the information you have requested. The method for accessing the data will depend on indexes, table design, statistics among other criteria. I am not so sure about Oracle storage, but would imagine that it would retieve the data in a similar manner, with the method used also depending upon certain criteria (whatever they may be). [1]:
http://msdn.microsoft.com/en-us/library/ms187594.aspx
