none
Best data type for index and Full-Text Search RRS feed

  • Question

  • I'm setting up a full text search in sql server 2014 and the table I want to search has a uniqueidentifier for a primary key, however, I can add an int column and use that as an index for the full text search?  Would be int be a better data type to use?

    Thanks for any advise you can offer.

    Saturday, September 7, 2019 3:51 PM

Answers

  • Found the answer.  Use the smallest data type possible so in my case int.

    It must be indexed unique and for best performance a clustered index.  So I kept my uniqueidentifier column as the application depends on it, but I added an int column identity and made it the primary key.  I noticed that when inserting 800K+ rows the time with from 49 seconds down to 11 after making this change.

    • Marked as answer by moondaddy Saturday, September 7, 2019 5:43 PM
    Saturday, September 7, 2019 5:42 PM

All replies

  • I'm setting up a full text search in sql server 2014 and the table I want to search has a uniqueidentifier for a primary key, however, I can add an int column and use that as an index for the full text search?  Would be int be a better data type to use?

    Thanks for any advise you can offer.

    It depends...

    Like most questions related to architecture the answer is "IT DEPENDS", and without fully familiar with your system we cannot say yes or no with absolute certainty.

    With that being said, in first glance I do not find a reason which is directly related to having "FULL Text Search" according to your description to add another column instead of using the existing uniqueidentifier primary key.

    Note! It is a different discussion if you should use integer or Guid as your primary index and in this discussion the answer is (can you guess?) it depends. In short, in this discussion which is outside the scope of your question, you should remember that primary key defines the (1) lookup value used by the nonclustered indexes, (2) physical order or rows, (3) and it can be used to answer a query - which is a huge parameter.


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Saturday, September 7, 2019 5:11 PM
    Moderator
  • Found the answer.  Use the smallest data type possible so in my case int.

    It must be indexed unique and for best performance a clustered index.  So I kept my uniqueidentifier column as the application depends on it, but I added an int column identity and made it the primary key.  I noticed that when inserting 800K+ rows the time with from 49 seconds down to 11 after making this change.

    • Marked as answer by moondaddy Saturday, September 7, 2019 5:43 PM
    Saturday, September 7, 2019 5:42 PM