November 19, 2009 at 11:46 pm
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;-)
November 20, 2009 at 1:45 am
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
November 20, 2009 at 2:09 am
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
November 20, 2009 at 3:03 am
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.
November 20, 2009 at 5:48 am
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
November 20, 2009 at 6:17 am
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.
November 24, 2009 at 5:14 pm
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply