Timeline for Speed of Searching Text vs INT/BIGINT - SQL Server 2012
Current License: CC BY-SA 3.0
12 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Nov 5, 2018 at 16:59 | comment | added | Milo LaMar | Regarding collision, as long as you've written something to handle many-results, you should be fine. But the real world table may be 1 billion rows or less through its lifetime. And a single BIGINT would leave about 1 in ten billion chance of a collision. You might consider doing a modulo down to a single BIGINT and test that timing. | |
| Feb 11, 2014 at 23:42 | comment | added | Anti-weakpasswords | Does your conversion from "variable length text" to 16 bytes worth of binary data potentially result in collisions? That could lead to incorrect results, or more results than expected. | |
| Feb 11, 2014 at 18:51 | answer | added | Nicholas Post | timeline score: 3 | |
| Feb 11, 2014 at 18:32 | comment | added | Nicholas Post | The text version will be eventually standardized to a certain format, but will be variable length and include a-Z 0-9 and some special chars -_()@#. | |
| Feb 11, 2014 at 18:14 | history | edited | marc_s | CC BY-SA 3.0 |
Product is officially called **SQL Server** (and not MS SQL, MSSQL MS-SQL or anything like that....)
|
| Feb 11, 2014 at 18:02 | comment | added | Jon Seigel | Is the "text" version of the key supposed to be only 0-9, a-f (i.e., hex representation of a number), or does it contain any alphanumeric? | |
| Feb 11, 2014 at 17:46 | comment | added | Nicholas Post | Well that is what I will end up doing either way, I am just trying to get some guidance on which path to take so I don't have to go back and make major changes partway through the project. | |
| Feb 11, 2014 at 17:33 | comment | added | Zane | @NicholasPost you seem to have a pretty good test scenario already built. I suggest you build a mock up and try them out. | |
| Feb 11, 2014 at 17:31 | review | First posts | |||
| Feb 11, 2014 at 17:56 | |||||
| Feb 11, 2014 at 17:26 | comment | added | Nicholas Post | Well that's the issue, it's a system I am building, so technically it doesn't exist yet. I'm sure I'm not the first person to ask if there is a difference between searching for INT or BIGINT. I am just looking to see if anyone already has first hand experience with the two types and knows if there are any potential drawbacks, as well as if I am over complicating things. | |
| Feb 11, 2014 at 17:16 | comment | added | Aaron Bertrand | Did you try it? You are in a much better position to test your scenario, since you have your schema, your data, and your queries to run on your hardware and with your usage patterns. Asking us which is more efficient just means we have to somehow attempt to replicate your exact environment. | |
| Feb 11, 2014 at 17:14 | history | asked | Nicholas Post | CC BY-SA 3.0 |