1

I have an extremely large table on which most queries use a WHERE clause to filter a column containing an ID number. Unfortunately, this ID number is stored as nvarchar(255). Will re-casting as bigint (it will not fit as int) help query performance?

Note - this ID value is not unique so a primary key cannot be assigned. Does this mean I cannot use a clustered index here? The table currently contains 0 clustered indexes.

Luke Girvin
  • 13,221
  • 9
  • 64
  • 84
Yoav24
  • 316
  • 4
  • 16
  • What do you mean by "re-casting as bigint"? At run time or (hopefully...) by changing the table definition? – Martin Smith Apr 08 '15 at 17:35
  • If a field is only ever going to contain a numeric value, **yes**, change the table definition to make that field the appropriate numeric type. You should always [choose the correct data type for your fields](https://sqlblog.org/2009/10/12/bad-habits-to-kick-choosing-the-wrong-data-type) – alroc Apr 08 '15 at 17:43
  • Martin Smith, I mean changing the table definition and updating all existing values – Yoav24 Apr 08 '15 at 18:00

1 Answers1

3

Of course a bigint, which occupies 8 bytes, will be much faster than your nvarchar(255), which may occupy up to 512 bytes if you use all 255 characters. As per Martin's comment, if this ID nowadays fits in a bigint, it means it has no more than 19 digits (I guess you don't have negative IDs), so it may actually occupy up to 40 bytes (2 + 2 * 19). More about this here.

You can create indexes on fields which are not unique, even a clustered one. Regarding this, you may probably find this question useful: Do clustered indexes have to be unique?

Community
  • 1
  • 1
Andrew
  • 7,602
  • 2
  • 34
  • 42
  • 1
    `nvarchar(255)` occupies *up to* 512 bytes. The most that would actually be taken up storing a `bigint` type value is `2 + DATALENGTH(N'-9223372036854775808')` which is `42` – Martin Smith Apr 08 '15 at 17:27