May 3, 2013 at 10:41 am
Hello Everyone
I was suggested that I use a different way of generating some IDs. This column is not any part of a Pri Key, only a way to identify an item, kind of like a part number.
The other person suggested that I use 'FakeID_00001', 'FakeID_00002', 'FakeID_0000n' etc.... There are other IDs that have already been generated that are all numbers, even though the column is text. So I wanted to use an all number ID, even though it is a text data type column, on the idea that one day, someone may want to convert the IDs to an actual numeric value. Mine are '548921412345', '154879612345', '889789712345', etc....
The other person said his would be indexed better than mine since the repeating values were at the beginning of the text string. And my repeating values are at the end of the string. Is that true? I would think that it would not matter, since the index is going to use the entire value of the string.
Thank you in advance for all your suggestions, comments and assistance
Andrew SQLDBA
May 3, 2013 at 2:29 pm
One thing to keep in mind is fragmentation. When new ID's are increasing at the end, page splits will be far less than trying to insert a value randomly in the index.
May 6, 2013 at 4:08 pm
If the numeric portion is random anyway, definitely use all numeric characters if possible -- you're quite right: prefixing the random numbers with a fixed string will NOT help the index lookup in any way.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply