Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Apply Unique constraints on existing database Expand / Collapse
Author
Message
Posted Tuesday, February 18, 2014 5:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 7, 2014 7:01 AM
Points: 20, Visits: 47
Can you tell as above option effect on SOL performance ?
Post #1542498
Posted Tuesday, February 18, 2014 5:22 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 39,977, Visits: 36,340
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 2008, MVP
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

Post #1542499
Posted Tuesday, February 18, 2014 5:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:57 PM
Points: 7,745, Visits: 9,493
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
Post #1542501
Posted Tuesday, February 18, 2014 5:37 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 39,977, Visits: 36,340
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 2008, MVP
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

Post #1542503
Posted Tuesday, February 18, 2014 5:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:57 PM
Points: 7,745, Visits: 9,493
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
Post #1542511
Posted Tuesday, February 18, 2014 5:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 7, 2014 7:01 AM
Points: 20, Visits: 47
As per your opinion which approach should be use to resolve this issue ?
That will be not create problems in future.
Post #1542513
Posted Tuesday, February 18, 2014 7:00 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 39,977, Visits: 36,340
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 2008, MVP
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

Post #1542550
Posted Tuesday, February 18, 2014 7:03 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 7, 2014 7:01 AM
Points: 20, Visits: 47
Then what's good approach to do resolve this issue in sql server 2005?
suggestion pls.
Post #1542551
Posted Tuesday, February 18, 2014 11:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:57 PM
Points: 7,745, Visits: 9,493
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
Post #1542693
Posted Tuesday, February 18, 2014 10:40 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 7, 2014 7:01 AM
Points: 20, Visits: 47
Thanks Tom and Gila for your valuable suggestions
Post #1542865
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse