February 18, 2014 at 4:31 am
Can you explain it as in sql form as below
CREATE TABLE dbo.Example
(
col1 varchar(100) NULL,
);
GO
CREATE VIEW dbo.ExampleUnique
WITH SCHEMABINDING AS
SELECT e.col1
FROM dbo.Example AS e
WHERE e.col1 IS NOT NULL;
GO
CREATE UNIQUE CLUSTERED INDEX cuq
ON dbo.ExampleUnique (col1);
GO
February 18, 2014 at 4:32 am
Why 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?
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
February 18, 2014 at 4:40 am
Can you mention a example or a dummy script which create a indexed computed column?
February 18, 2014 at 4:56 am
Below script will work in this case or not
CREATE 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))
GO
CREATE UNIQUE INDEX TEST_UQ_IND_1 ON TEST_UQ (COL3)
GO
This also works for in my case
February 18, 2014 at 5:15 am
Yup, that's it. Should be less overhead than an indexed 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
February 18, 2014 at 5:20 am
Can you tell as above option effect on SOL performance ?
February 18, 2014 at 5:22 am
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
February 18, 2014 at 5:30 am
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
February 18, 2014 at 5:37 am
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
February 18, 2014 at 5:54 am
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
February 18, 2014 at 5:57 am
As per your opinion which approach should be use to resolve this issue ?
That will be not create problems in future.
February 18, 2014 at 7:00 am
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
February 18, 2014 at 7:03 am
Then what's good approach to do resolve this issue in sql server 2005?
suggestion pls.
February 18, 2014 at 11:29 am
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
February 18, 2014 at 10:40 pm
Thanks Tom and Gila for your valuable suggestions 🙂
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply