3

This might sound like an amateur question, but I just need to be 100% sure and I haven't found anything on it online.

It is considered good practice having an "ever increasing index". Now, does that rule also apply to NVARCHAR and VARCHAR?

Let's say I insert "abc", then "def" and then "ghi". Is this considered "increasing"?

Note: I am well aware that it is bad convention to use an NVARCHAR and VARCHAR as index.

2

If I understand your question:
It is a good practice to have an ever increased index - that prevents page splits - read about this issue if you are not familiar with it. It is correct also for char values. To make it simple - sql server will fill page after page without having to insert rows in the middle. Think about it as filling phone book - it is much easier to fill it in abc order than randomly insert names.
So the order "abc", then "def" and then "ghi" is ever increasing.
Don't know why do you think that using char columns as indexes is a bad practice - if you need them there you should create them. Yes, creating logic that will enforce ever increasing char insertion is difficult and sometimes impossible, but it still shouldn't prevent you from creating indexes on those columns.
With all that said if you need to enforce ever increasing clustered index you should probably use numbers(identity or sequence)

  • Thanks. It is exactly a clustered index I have in mind. Why the other mindset for clustered indexes though? – Mathias Lykkegaard Lorenzen Jan 6 '16 at 16:34
  • Not sure if it works the same in SQL Server (but I would guess so), but in MySQL having the table clustered by a varchar column means that all secondary indexes need to contain the varchar as a pointer to actual row, so they grow quite a bit compared to a nonclustered table, where a numerical rowid is enough to locate the row in a heap. And a string comparisons are more costly than integer ones, even more so when taking collations into consideration. So there are real performance implications for both CPU and data size/IO which you need to think of. But if you need it use it unless too slow. – jkavalik Jan 7 '16 at 6:10
  • The other mindset is because you can only create one clustered index, and as @jkavalik said every NC index will have CL values in his leaf level pages, you can enforce uniqueness using ever increasing int, but I wouldn't say that creating CL index as a char value is a no go. It might help if you could explain what is that table for and how you can possibly enforce ever increasing insertion there. – Michael Cherevko Jan 7 '16 at 6:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.