SQL Server Performance

Which is best while Filtering

Discussion in 'SQL Server 2008 General DBA Questions' started by shakeebgenii, Jun 3, 2009.

  1. shakeebgenii New Member

    Hi,
    I have a table with a column ID [PK (int) IDENTITY] and second Code [varchar(50)]. Now my question is I want to filter records which is better to filter it by PK or by varchar(50) column.
    Thanks in Advance
  2. FrankKalis Moderator

    Welcome to the forum!
    It depends...
    Looking at such a table, I would guess it is a lookup table with not so many rows. In such cases I tend to add a UNIQUE constraint on the string column, which internally creates an index for behind the scenes.
    Any relevant stored procedure accepts a (in your case) varchar(50) parameter with the string, which I internally resolve to the PK value inside the procedure and from there on work only with the PK value.

    Judging only from your table fragment however it would be "better" to filter on the PK instead of the varchar(50), because there is no index on the varchar(50) column, so SQL Server will scan the table, while it can use the index enforcing the PRIMARY KEY constraint, when you filter on the PK.

Share This Page