Nonclustered indexes always include a row locator.
For a heap this will be an 8 byte RID (File:Page:Slot). On a table with a clustered index it will be the clustered index key column(s). And it will always be the copied values not a pointer to the values. This duplication of CI key values into all non clustered indexes is why it is often recommended that the CI key be narrow and not frequently updated.
In the table shown in the question the Clustered index key is a 4 byte integer and potentially may also include a 4 byte uniqueifier for any duplicate key values.
In your case as the NCIs are not declared as unique the CI key will be appended to the NCI key.
For unique non clustered indexes the CI key would be added as included column(s) in the leaf pages unless explicitly made part of the key.
See Kalen Delaney: More About Nonclustered Index Keys for some additional information about how you can see this for yourself.
With these 4 rows of data all three indexes only consume a single 8KB data page.
SELECT index_id,
index_level,
page_count,
record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('people'), NULL, NULL, 'DETAILED')
Returns
+----------+-------------+------------+--------------+
| index_id | index_level | page_count | record_count |
+----------+-------------+------------+--------------+
| 1 | 0 | 1 | 4 |
| 2 | 0 | 1 | 4 |
| 3 | 0 | 1 | 4 |
+----------+-------------+------------+--------------+
The additional page shown in use by sys.allocation_units.total_pages
is an IAM page. This is not used for storing data but just for tracking the pages and extents comprising the index.
The documentation often gives you an answer to such questions. Like in this case, too:
The operator classes text_pattern_ops, varchar_pattern_ops, and bpchar_pattern_ops support B-tree indexes on the types text, varchar,
and char respectively. The difference from the default operator
classes is that the values are compared strictly character by
character rather than according to the locale-specific collation
rules. This makes these operator classes suitable for use by queries
involving pattern matching expressions (LIKE or POSIX regular
expressions) when the database does not use the standard "C" locale.
As an example, you might index a varchar column like this:
CREATE INDEX test_index ON test_table (col varchar_pattern_ops);
Note that you should also create an index with the default operator class if you want queries involving ordinary <, <=, >, or >=
comparisons to use an index. Such queries cannot use the
xxx_pattern_ops operator classes. (Ordinary equality comparisons can
use these operator classes, however.) It is possible to create
multiple indexes on the same column with different operator classes.
The documentation goes on to say:
If you do use the C locale, you do not need the xxx_pattern_ops
operator classes, because an index with the default operator class is
usable for pattern-matching queries in the C locale.
You can check your locale as follows (it is likely to be UTF8 rather than "C"):
postgres=> show lc_collate;
lc_collate
-------------
en_GB.UTF-8
Best Answer
The best way for that is to use the sequence way which is auto increment facility, example:
so in that case you don't need to mention that id in the insert like:
also you can get the inserted id from the function
so here the id is auto last rec id + 1, you can see full reference about it from MySQL dev site: http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html