March 16, 2009 at 1:08 pm
Basic situation: Poorly designed DB that I'm in the process of fixing. I have to take baby steps, though. Key is to do this with minimal impact.
So, this particular issue is that I have about 5 unique indexes that exceed the storage limitations due to the usage of nvarchar columns. No big deal, I say, BOL says I can define these using INCLUDE to get past the storage limitations. Alas, tests show that while I can define them with INCLUDE columns, the uniqueness constraint does NOT check the included columns.
My little test:
CREATE TABLE UniqueTest (
PROFILE_ID NUMERIC(19) NOT NULL,
THING_NAME NVARCHAR(255) NOT NULL,
ATTRIBUTE_NAME NVARCHAR(255) NOT NULL,
VIEWABLE NCHAR(1) NOT NULL
CONSTRAINT UniqueTest_CK1 CHECK (VIEWABLE IN ('Y', 'N')),
);
CREATE UNIQUE INDEX UniqueTest_UQ
ON UniqueTest (PROFILE_ID)
INCLUDE
(THING_NAME,
ATTRIBUTE_NAME) ;
INSERT INTO UniqueTest (
PROFILE_ID,
THING_NAME ,
ATTRIBUTE_NAME,
VIEWABLE
)
VALUES
(
3,
REPLICATE('FGHIJ', 20),
REPLICATE('MNOPQ', 20),
'Y')
--OK, try again
INSERT INTO UniqueTest (
PROFILE_ID,
THING_NAME ,
ATTRIBUTE_NAME,
VIEWABLE
)
VALUES
(
3,
REPLICATE('FGHIJ', 20),
REPLICATE('MNOPQ', 20),
'Y')
--Fails as expected
INSERT INTO UniqueTest (
PROFILE_ID,
THING_NAME ,
ATTRIBUTE_NAME,
VIEWABLE
)
VALUES
(
4,
REPLICATE('FGHIJ', 20),
REPLICATE('MNOPQ', 20),
'Y')
--Does not fail - expected
INSERT INTO UniqueTest (
PROFILE_ID,
THING_NAME ,
ATTRIBUTE_NAME,
VIEWABLE
)
VALUES
(
3,
REPLICATE('ABDCE', 20),
REPLICATE('12345', 20),
'Y')
--Fails, not good
So other than browbeating the original developers, any (DB-based, non-trigger) options other than modifying the columns?
March 16, 2009 at 1:12 pm
Not really. The reason indexes can't enforce (or even contain) too many bytes of data is structural, and critical to database performance.
You can make either triggers (probably a bad idea) or the insert procs (better idea if possible) enforce it. That's going to be your best option, so far as I know.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 16, 2009 at 2:52 pm
Included fields are only a gain for data access. They will not be used for data correctness checs within UNIQUE INDEXES.
I confirm GSquared to use a procedure which enforces the uniqueness of the data.
Greets
Flo
March 24, 2009 at 12:29 am
A side comment from what these fine gentlemen have said .. do you need NVARCHAR? I find some developers don't know the difference between VARCHAR and NVARCHAR and just choose the one at random. If there is no uni-code data in your field then I would look into changing that to varchar type and your are below the 900 byte limit.
Thanks.
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 24, 2009 at 12:34 am
Yes, we need unicode. We have international/multilingual customers so it's a must.
March 24, 2009 at 7:22 am
aa nuts :P. Aaa well thought I should mention it, I have fixed lots of developers over the past year for this small mistake. :hehe:
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply