Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mrsaif
Starting Member
43 Posts |
 Posted - 2006-05-22 : 08:31:22
|
| I want to know the difference between indexing on varchar and numeric value. What are the disadvantages of using indexing on varchar.disadvantages of using too much indexes on the table.Muhammad Saifullah |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-05-22 : 08:35:10
|
| Have you had a look at indexes in bol?A varchar index will need to use the collation and is often bigger than a numeric.A lot of indexes will slow down inserts and updates.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
CSK
Constraint Violating Yak Guru
489 Posts |
Posted - 2006-05-22 : 08:39:40
|
| hi Muhammad SaifullahFirst Which index u r going to put in varchar or numericclustered index Varchar the pointer should rearrange the rows by physicaly, so it's take time to rearrange the rows. so inserting time is going to be high (not too much) but retreving is too highnumeric so inserting time is going to be low but retreving is too high non clustered index Varchar & numeric There may be a pointer for each and every record. it's logically sorts the record. so there is no time taken for insert. but it retrives the record fastlyKK |
|
|
|
mrsaif
Starting Member
43 Posts |
Posted - 2006-05-22 : 09:13:58
|
| Thanks KK. for you help What if: I join 4 tables in a query whith inner joins and each table have defined 2 varchar nonclusted indexes what is the effect of the index now? and 2 of the table have more than 40000 records, Is the indexing help me in this situation? or i have to go for other solution.Muhammad Saifullah |
|
|
|
CSK
Constraint Violating Yak Guru
489 Posts |
Posted - 2006-05-22 : 09:25:54
|
| Welcome mrsaif!Is your table has clustered index..?is that available your where clause..?Right now ur index will support some waht. first u will do the query execution plan (Ctrl+l). then find out is it nonclustered index seek or non clustered index scan. if it's scan means u need to improve ur index. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-05-22 : 10:05:34
|
quote:
Originally posted by CSKclustered index Varcharthe pointer should rearrange the rows by physicaly, so it's take time to rearrange the rows.numericso inserting time is going to be low
The clustered index will cause the data pages to be sorted whether it's a varchar or numeric column in the index. The numeric will (probably) be slightly faster due to not needing the code pages.It is the page splitting that causes the problems and there will not be much difference due to the datatype.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Indexing on varchar?