Performance issues related to Index

Question

Post reply

Performance issues related to Index

devesh.v19

Grasshopper

Points: 23
More actions
November 19, 2009 at 11:46 pm

#216971

This is my very first post in this forum, so Hi to everybody around here, Hope to enjoy learning some sql tips from some great Pros in the forum.:-)

Question:

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:unsure:

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?:alien:

DROP TABLE tb1

Any help shall be appreciated

Thanks

shank;-)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

Well, when you run that the table will be unavailable until that has completed. Is that what you were asking?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

devesh.v19 Grasshopper Points: 23 More actions · Answer 2

Hi Gail,

Actually I am in the designing phase of a database, I am new to SQL server 2005. This code was only a sample code to let other people understand of the problem, In reality I have over 10-15 of columns per table and I have over 30 of such tables in the database. 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 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

Please help

Thanks

Shank

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 3

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.

Exactly what are you trying to achieve here?

Are you saying that each table will have a different value in the computed column?

Or is this a way of uniquely identifying rows entered at different locations?

Why bother with a computed, persisted column? Why not make it a real column and enforce the required value with a default and column constraint. You will then have no problems in declaring it as a primary key.

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 what columns are you joining on? Are they indexed? If not, you are likely to encounter performance problems, where SQL Server is likely to scan the whole table to find the row(s) it needs, rather than going to them directly.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 4

devesh.v19 (11/20/2009)

In reality I have over 10-15 of columns per table and I have over 30 of such tables in the database. 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.

Eh, what? That's an odd situation.

You say you're in the designing phase of the database. Can't you change the design to something a little more normal?

So, I was mainly asking whether or should I drop the primary key constraint on the identity column and create a 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

Hard to say without seeing some of the queries.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

devesh.v19 Grasshopper Points: 23 More actions · Answer 5

Ian Scarlett (11/20/2009)

Exactly what are you trying to achieve here?

Are you saying that each table will have a different value in the computed column?

Or is this a way of uniquely identifying rows entered at different locations?

Actually I am trying to create a unique identification for every row per table.

Why bother with a computed, persisted column? Why not make it a real column and enforce the required value with a default and column constraint. You will then have no problems in declaring it as a primary key.

I am using a computed column so that I can get an auto generated unique identification per row/table. Is there some bypass of getting an auto generated identification without having to use a computed column

So what columns are you joining on? Are they indexed? If not, you are likely to encounter performance problems, where SQL Server is likely to scan the whole table to find the row(s) it needs, rather than going to them directly.

Joins are made between primary key and foreign keys, the primary keys are these computed column( which are yet not indexed) as for the foreign key is concerned they are also not indexed.

mgodinez SSC Enthusiast Points: 114 More actions · Answer 6

devesh.v19 (11/20/2009)

Ian Scarlett (11/20/2009)

Exactly what are you trying to achieve here?

Are you saying that each table will have a different value in the computed column?

Or is this a way of uniquely identifying rows entered at different locations?

Actually I am trying to create a unique identification for every row per table.

Why bother with a computed, persisted column? Why not make it a real column and enforce the required value with a default and column constraint. You will then have no problems in declaring it as a primary key.

I am using a computed column so that I can get an auto generated unique identification per row/table. Is there some bypass of getting an auto generated identification without having to use a computed column

hesitant to chime in here, seeing how i'm very green w/ sql.

but are you trying to auto increment the primary key? IDENTITY (1,1) PRIMARY KEY

mike g.

seattle, wa