SQLServerCentral.com / SQL Server 2008 / SQL Server Newbies / Apply Unique constraints on existing database / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 02 Aug 2015 16:56:45 GMT20RE: Apply Unique constraints on existing databasehttp://www.sqlservercentral.com/Forums/Topic1542081-1292-1.aspxThanks Tom and Gila for your valuable suggestions :-)Tue, 18 Feb 2014 22:40:35 GMTashu.sajwanRE: Apply Unique constraints on existing databasehttp://www.sqlservercentral.com/Forums/Topic1542081-1292-1.aspx[quote][b]ashu.sajwan (2/18/2014)[/b][hr]Then what's good approach to do resolve this issue in sql server 2005?suggestion pls.[/quote]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 [code="sql"]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)GOCREATE UNIQUE INDEX TEST_UQ_IND_1 ON TEST_UQ (COL3)GO[/code]Tue, 18 Feb 2014 11:29:49 GMTTomThomsonRE: Apply Unique constraints on existing databasehttp://www.sqlservercentral.com/Forums/Topic1542081-1292-1.aspxThen what's good approach to do resolve this issue in sql server 2005?suggestion pls.Tue, 18 Feb 2014 07:03:30 GMTashu.sajwanRE: Apply Unique constraints on existing databasehttp://www.sqlservercentral.com/Forums/Topic1542081-1292-1.aspx[quote][b]TomThomson (2/18/2014)[/b][hr]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)? [/quote]Then you define the calculated column to ensure that can't happen. Something like (off the top of my head) CASE WHEN &lt;unique column&gt; IS NULL THEN 'PK: ' + &lt;pk column&gt; ELSE 'uq:' + &lt;unique column&gt; 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.Tue, 18 Feb 2014 07:00:04 GMTGilaMonsterRE: Apply Unique constraints on existing databasehttp://www.sqlservercentral.com/Forums/Topic1542081-1292-1.aspxAs per your opinion which approach should be use to resolve this issue ?That will be not create problems in future.Tue, 18 Feb 2014 05:57:41 GMTashu.sajwanRE: Apply Unique constraints on existing databasehttp://www.sqlservercentral.com/Forums/Topic1542081-1292-1.aspx[quote][b]GilaMonster (2/18/2014)[/b][hr]Yup, that's it. Should be less overhead than an indexed view.[/quote]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.Tue, 18 Feb 2014 05:54:58 GMTTomThomsonRE: Apply Unique constraints on existing databasehttp://www.sqlservercentral.com/Forums/Topic1542081-1292-1.aspx[quote][b]TomThomson (2/18/2014)[/b][hr][quote][b]GilaMonster (2/18/2014)[/b][hr]Why clustered? This isn't typically where you would put the clustered index.[/quote]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[/quote]I didn't notice it was a view.Tue, 18 Feb 2014 05:37:58 GMTGilaMonsterRE: Apply Unique constraints on existing databasehttp://www.sqlservercentral.com/Forums/Topic1542081-1292-1.aspx[quote][b]GilaMonster (2/18/2014)[/b][hr]Why clustered? This isn't typically where you would put the clustered index.[/quote]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:typosTue, 18 Feb 2014 05:30:55 GMTTomThomsonRE: Apply Unique constraints on existing databasehttp://www.sqlservercentral.com/Forums/Topic1542081-1292-1.aspxOther than what I just said one message back, not really. Too open ended, not enough information. Test under expected volumes and see.Tue, 18 Feb 2014 05:22:38 GMTGilaMonsterRE: Apply Unique constraints on existing databasehttp://www.sqlservercentral.com/Forums/Topic1542081-1292-1.aspxCan you tell as above option effect on SOL performance ?Tue, 18 Feb 2014 05:20:07 GMTashu.sajwanRE: Apply Unique constraints on existing databasehttp://www.sqlservercentral.com/Forums/Topic1542081-1292-1.aspxYup, that's it. Should be less overhead than an indexed view.Tue, 18 Feb 2014 05:15:56 GMTGilaMonsterRE: Apply Unique constraints on existing databasehttp://www.sqlservercentral.com/Forums/Topic1542081-1292-1.aspxBelow script will work in this case or notCREATE TABLE TEST_UQ (COL1 INT IDENTITY(1,1) PRIMARY KEY, COL2 VARCHAR(10) NULL,COL3 AS (CASE WHEN COL2 IS NULL THEN CAST(COL1 AS VARCHAR(10)) ELSE COL2 END))GOCREATE UNIQUE INDEX TEST_UQ_IND_1 ON TEST_UQ (COL3)GOThis also works for in my caseTue, 18 Feb 2014 04:56:03 GMTashu.sajwanRE: Apply Unique constraints on existing databasehttp://www.sqlservercentral.com/Forums/Topic1542081-1292-1.aspxCan you mention a example or a dummy script which create a indexed computed column?Tue, 18 Feb 2014 04:40:39 GMTashu.sajwanRE: Apply Unique constraints on existing databasehttp://www.sqlservercentral.com/Forums/Topic1542081-1292-1.aspxWhy a view, rather than just an indexed computed column? It'll work, but....Do you not have a column that is already unique in the table, eg the primary key columns?Tue, 18 Feb 2014 04:32:24 GMTGilaMonsterRE: Apply Unique constraints on existing databasehttp://www.sqlservercentral.com/Forums/Topic1542081-1292-1.aspxCan you explain it as in sql form as belowCREATE TABLE dbo.Example(col1 varchar(100) NULL,);GOCREATE VIEW dbo.ExampleUniqueWITH SCHEMABINDING ASSELECT e.col1FROM dbo.Example AS eWHERE e.col1 IS NOT NULL;GOCREATE UNIQUE CLUSTERED INDEX cuqON dbo.ExampleUnique (col1);GOTue, 18 Feb 2014 04:31:23 GMTashu.sajwanRE: Apply Unique constraints on existing databasehttp://www.sqlservercentral.com/Forums/Topic1542081-1292-1.aspxI already did.[quote][b]GilaMonster (2/17/2014)[/b][hr]For 2005, define a computed column on ISNULL(&lt;column that you want to be unique&gt;,&lt;the primary key column of the table&gt;) and put a unique index on that computed column.[/quote]Tue, 18 Feb 2014 04:26:50 GMTGilaMonsterRE: Apply Unique constraints on existing databasehttp://www.sqlservercentral.com/Forums/Topic1542081-1292-1.aspxCan you please share other approach to do this easiest way in sql server 2005?Tue, 18 Feb 2014 04:22:36 GMTashu.sajwanRE: Apply Unique constraints on existing databasehttp://www.sqlservercentral.com/Forums/Topic1542081-1292-1.aspxWhy clustered? This isn't typically where you would put the clustered index.Tue, 18 Feb 2014 04:14:50 GMTGilaMonsterRE: Apply Unique constraints on existing databasehttp://www.sqlservercentral.com/Forums/Topic1542081-1292-1.aspxYour suggestion works for meThanks TomTue, 18 Feb 2014 03:36:20 GMTashu.sajwanRE: Apply Unique constraints on existing databasehttp://www.sqlservercentral.com/Forums/Topic1542081-1292-1.aspx[quote][b]ashu.sajwan (2/17/2014)[/b][hr]... col1 varchar(Max) NULL,...CREATE UNIQUE CLUSTERED INDEX cuqON dbo.ExampleUnique (col1);It shows error: Column 'col1' in table 'dbo.ExampleUnique' is of a type that is invalid for use as a key column in an index.Any suggestions[/quote]The reason for the failure is that columns of lob type (nvarchar(max), varchar(max), varbinary(max), ntext, text, image) can't be key columns in an index. So for example varchar(768) is OK, but varchar(MAX) isn't.Incidentally, I think this is a very good technique given that your version of SQL Server doesn't support filtered indexes.Tue, 18 Feb 2014 03:27:10 GMTTomThomsonRE: Apply Unique constraints on existing databasehttp://www.sqlservercentral.com/Forums/Topic1542081-1292-1.aspxPrevious section is works fine when 'col1 int NULL'Mon, 17 Feb 2014 22:04:26 GMTashu.sajwanRE: Apply Unique constraints on existing databasehttp://www.sqlservercentral.com/Forums/Topic1542081-1292-1.aspxI am creating CLUSTERED INDEX by running below query on sql server 2005 CREATE TABLE dbo.Example( col1 varchar(Max) NULL,);GOCREATE VIEW dbo.ExampleUniqueWITH SCHEMABINDING ASSELECT e.col1FROM dbo.Example AS eWHERE e.col1 IS NOT NULL;GOCREATE UNIQUE CLUSTERED INDEX cuqON dbo.ExampleUnique (col1);GOIt shows error: Column 'col1' in table 'dbo.ExampleUnique' is of a type that is invalid for use as a key column in an index.Any suggestionsMon, 17 Feb 2014 21:51:46 GMTashu.sajwanRE: Apply Unique constraints on existing databasehttp://www.sqlservercentral.com/Forums/Topic1542081-1292-1.aspx[quote][b]GilaMonster (2/17/2014)[/b][hr]Yes.[/quote]It's easier with varchar, nvarchar, and varbinary since it's generally not too difficult to convert the primary key to one of those types; other column types could be an issue.And there's another complication - you need some way of ensuring that the value you convery the primary key into when the column in question has a null never clashes with some other value in that column, which may turn out to be nontrivial.Mon, 17 Feb 2014 11:23:19 GMTTomThomsonRE: Apply Unique constraints on existing databasehttp://www.sqlservercentral.com/Forums/Topic1542081-1292-1.aspxYes.Mon, 17 Feb 2014 07:13:42 GMTGilaMonsterRE: Apply Unique constraints on existing databasehttp://www.sqlservercentral.com/Forums/Topic1542081-1292-1.aspxCan i apply this approach for column which is varchar type?Mon, 17 Feb 2014 07:03:33 GMTashu.sajwanRE: Apply Unique constraints on existing databasehttp://www.sqlservercentral.com/Forums/Topic1542081-1292-1.aspxWith a hell of a lot of difficulty (and a computed column). The filtered index is 2008 and above, which since this was posted in the SQL 2008 forum we assumed you were using.For 2005, define a computed column on ISNULL(&lt;column that you want to be unique&gt;,&lt;the primary key column of the table&gt;) and put a unique index on that computed column.Mon, 17 Feb 2014 06:55:47 GMTGilaMonsterRE: Apply Unique constraints on existing databasehttp://www.sqlservercentral.com/Forums/Topic1542081-1292-1.aspxHow can i do it with sql server 2005Mon, 17 Feb 2014 06:51:43 GMTashu.sajwanRE: Apply Unique constraints on existing databasehttp://www.sqlservercentral.com/Forums/Topic1542081-1292-1.aspxYes i need this thanks for your helpMon, 17 Feb 2014 06:48:09 GMTashu.sajwanRE: Apply Unique constraints on existing databasehttp://www.sqlservercentral.com/Forums/Topic1542081-1292-1.aspxI assume that you meant that the table should have many records with null value in this column, but the rest of the values should be unique. If I'm correct then you can try and use filtered unique index on that table (the filter would be of course on the null value).AdiMon, 17 Feb 2014 06:43:48 GMTAdi Cohn-120898Apply Unique constraints on existing databasehttp://www.sqlservercentral.com/Forums/Topic1542081-1292-1.aspxI want to apply unique constraints on existing database,but column accepts null values how can i set unique constraints on that column without lose of data. helps me outMon, 17 Feb 2014 06:33:32 GMTashu.sajwan