Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Apply Unique constraints on existing database


Apply Unique constraints on existing database

Author
Message
ashu.sajwan
ashu.sajwan
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 51
Can you tell as above option effect on SOL performance ?
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47305 Visits: 44392
Other than what I just said one message back, not really. Too open ended, not enough information. Test under expected volumes and see.


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


TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10743 Visits: 12019
GilaMonster (2/18/2014)
Why clustered? This isn't typically where you would put the clustered index.

not clustered isn't a sensible option - a view has to have a clustered unique index before it can have a non-clustered index, and as there's only one column in this view that first index does the job and there's no point in adding a non-clustered index.
And in a one colum view, how can this not be typically where you would put the clustered index?
edit:typos

Tom

GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47305 Visits: 44392
TomThomson (2/18/2014)
GilaMonster (2/18/2014)
Why clustered? This isn't typically where you would put the clustered index.

not clustered isn't a sensible option - a view has to have a clustered unique index before it can have a non-clustered index, and as there's only one column in this view that first index does the job and there's no point in adding a non-clustered index.
And in a one colum view, how can this not be typically where you would put the clustered index?
edit:typos


I didn't notice it was a view.


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


TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10743 Visits: 12019
GilaMonster (2/18/2014)
Yup, that's it. Should be less overhead than an indexed view.

The snag is that it only works if you are lucky. Neither "it'll go faster but it may not work" nor "it'll go faster but you will have an error management nightmare to code around" is something I would consider a recommendation, and those are the best that can be set f this technique unless you have some business rules that preclude the problems arising, and the OP has mentioned no such rules.

What happens if that nullable column containts the string '12345' or '42 ' or anything else which will test equal to the result of casting some integer to varchar(10)? the identity colum primary key is going to hit that value some time, and if the nullable column is null in the row which hits that identity value that would be a constraint volation, so the insert fails. There's an interesting bit of error management to do there. What happens if you've inserted the row with PK 37812 , which happened to have null in the nullable column, and later on you want to put the string '37812 ' somewhere in the nullable colum? The update or insert would cause a constraint violation, so it won't happen - another interesting bit of error management to write.

Effectively what you are doing is attempting to enforce a rule that says "the non-null values in this column must be unique" by imposing a rule that says "the non-null values in this coumn must not be unique and the null values in this column may not occur in any row where casting the primary key to varchar(10) would result in a string that tests equal to one of the values that is either already in this column or is going to be inserted in this column in the future", which looks to me like a very nasty kludge unless you know of some solid business rules that preclude the error consitions from arising.

Tom

ashu.sajwan
ashu.sajwan
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 51
As per your opinion which approach should be use to resolve this issue ?
That will be not create problems in future.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47305 Visits: 44392
TomThomson (2/18/2014)
What happens if that nullable column containts the string '12345' or '42 ' or anything else which will test equal to the result of casting some integer to varchar(10)?


Then you define the calculated column to ensure that can't happen. Something like (off the top of my head)
CASE WHEN <unique column> IS NULL THEN 'PK: ' + <pk column> ELSE 'uq:' + <unique column> END or similar to ensure that there won't be overlap between the values.

Yes, it takes a little bit of thought to get it right, but it's certainly not an error management nightmare.


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


ashu.sajwan
ashu.sajwan
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 51
Then what's good approach to do resolve this issue in sql server 2005?
suggestion pls.
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10743 Visits: 12019
ashu.sajwan (2/18/2014)
Then what's good approach to do resolve this issue in sql server 2005?
suggestion pls.

Unless there's a large number of NULLs (many times more entries in the column are NULL than are not), do as Gail suggests - her last post explains how to avoid the problems i was worying about with that method.

So the example you previously posted would change to add those distinguishing prefixes, so something like

CREATE TABLE TEST_UQ (
COL1 INT IDENTITY(1,1) PRIMARY KEY
, COL2 VARCHAR(10) NULL
, COL3 AS CASE WHEN COL2 IS NULL THEN 'P'+CAST(COL1 AS VARCHAR(10)) ELSE 'U'+COL2 END)
GO

CREATE UNIQUE INDEX TEST_UQ_IND_1 ON TEST_UQ (COL3)
GO



Tom

ashu.sajwan
ashu.sajwan
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 51
Thanks Tom and Gila for your valuable suggestions :-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search