SQL Server Performance

Performance issues with indexing?

Discussion in 'SQL Server 2005 General Developer Questions' started by shankbond, Nov 19, 2009.

  1. shankbond New Member

    Hi,
    I have a table having two columns; one is having primary key constraint and is of data type INT with IDENTITY, The other column is simply a computed column which is simply adding a character value in front of the primary key data type so that every table can have a unique identification per row.

    So, will there be some performance issues when I execute the following query

    CREATE TABLE tb1
    (
    columna INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    columnb AS ('RQ'+CONVERT([varchar](8),[columna],(0))) PERSISTED
    );
    ALTER TABLE tb1
    DROP CONSTRAINT PK__tb1__61074EC2 --name of the primary key constraint

    ALTER TABLE tb1
    ADD CONSTRAINT UK_tb1_columnb UNIQUE CLUSTERED (columnb)--cannot have a clustered index primary key since it is a computed column is there some way I can add a primary key to it?

    DROP TABLE tb1

  2. Adriaan New Member

    As said on your earlier thread - the 'RQ' bit is completely redundant and would be better handled on the client end.
    But to respond to the "need" for a unique index ... Because the 'RQ' bit is redundant, and because the remaining bit is copied directly from a PK identity column, a unique index would be redundant as well (if you could create one, which you can't).
  3. shankbond New Member

    Hi Adriaan,
    [quote user="Adriaan"]As said on your earlier thread - the 'RQ' bit is completely redundant and would be better handled on the client end.[/quote]


    Unfortnately the primary key indexing was done on the identity column, but the identity column is referenced rarely even while making the joins the computed column is used, badly designed database.

    So, I was mainly asking whether or should I drop the primary key constraint on the identity column and create a new clustered index column on the computed one, will that cause any bottlenecks in queries, increase any efficiency ,what should I watch for? while doing this.

    As I have read everywhere that indexes do increase the performance.
    Please help
  4. Adriaan New Member

    Since the number itself is completely arbitrary, I wonder why you would need to add initials to it.
    Do you need an auto-numbering system with initials? Note that identity columns can have gaps in the numbering; for instance when you're generating invoice numbers, that could be an issue.
    You'll still need the identity column as the "narrow" primary key that is the ideal index to speed up all operations.
  5. shankbond New Member

    Hi Adriaan,
    [quote user="Adriaan"]Do you need an auto-numbering system with initials?[/quote]
    Yes, exactly and about the gaps, its okay if there are gaps in the numbering but there should be no duplicate values in the column.
  6. Adriaan New Member

    Then all you need to do is to have the client application print the initials before the number from the identity column.
  7. shankbond New Member

    [quote user="Adriaan"]As said on your earlier thread [/quote]
    apologies for starting a new thread almost forgot about the earlier thread[^o)]

Share This Page