🚀 go-pugleaf

[posted and mailed, please reply in news]

[Note: I've set followups to comp.databases.ms-sqlserver only.]

^-^ (mortalnightmare@hotmail.com) writes:
> I have a table called dc_weighbridge_MAIN that that has to have an
> unique id called [ticket] but "ticket" also has to allow null values...
> so i set the ticket field to allow null fields, but to check for
> duplicate entries I created a view called "check_duplicates" wich a form
> will be based on as soon as this function is working....  in the view I
> added all the records but made "ticket" an unique index... 
> 
> Now when I try to delete or add any records in my table (not even using
> the view) I get the error: DELETE failed because the following SET
> options have incorrect settings: 'ARITHABORT' 

In able to access an indexed view, there are seven settings that must apply. 
All the following must be on: ANSI_NULLS (P), ANSI_WARNINGS (R), 
ANSI_PADDING (C), QUOTED_IDENTIFIER (P), ARITHABORT (R), 
CONCAT_NULL_YIELDS_NULL (R). And this setting must be OFF: 
NUMERIC_ROUNDABORT (R).

The parantheses refers to where you control these settings. R is run-time. P 
means that the setting is saved with the stored procedure and C that it is 
saved by table column.

The default for these settings vary with context. With DB-Library all 
settings are OFF by default. With OLE DB and ODBC all settings are ON by 
default, save NUMERIC_ROUNDABORT and ARITHABORT. However, when you use OSQL 
or Enterprise Manager, also QUOTED_IDENTIFIER is OFF.

What happens when the indexed view cannot be used, depends on how you try to 
use it. If it is just a SELECT statement that can be resolved in other way, 
you will only get another query plan (which is probably a less good one). If 
you try to update through the view, you get an error message.


-- 
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

It 's not a good idea to allow NULLs on a column that should be part of a
unique index,
because in that case with sqlserver NULL = NULL and therefor there can be
only one.

Check this :
create table T_test1 (col1 int null, col2 varchar(100) NULL)
go
create unique index Ux_T_test1 on T_test1 (col1)
go

insert into T_Test1 (col1, col2) values (1,'test1')
go
insert into T_Test1 (col1, col2) values (2,'test2')
go
insert into T_Test1 (col1, col2) values (3,'test3')
go
-- this one is ok
insert into T_Test1 (col2) values ('Test firts col1-null')
go
-- this one will fail
insert into T_Test1 (col2) values ('Test second col2-null')
go
insert into T_Test1 (col1, col2) values (4,'test4')
go

select * from T_test1
go

drop table T_test1
go


Jobi
"^-^" <mortalnightmare@hotmail.com> wrote in message
news:b7ito9$5kt$1@ctb-nnrp2.saix.net...
> Hi there...
>
> I have a table called dc_weighbridge_MAIN that that has to have an unique
id
> called [ticket] but "ticket" also has to allow null values... so i set the
> ticket field to allow null fields, but to check for duplicate entries I
> created a view called "check_duplicates" wich a form will be based on as
> soon as this function is working....  in the view I added all the records
> but made "ticket" an unique index...
>
> Now when I try to delete or add any records in my table (not even using
the
> view) I get the error: DELETE failed because the following SET options
have
> incorrect settings: 'ARITHABORT'
>
> What's wrong?  What I want to achieve is: When my capturer inserts a
record
> that has an "ticket" no there may be no duplicates, and the form should
warn
> you of this when you try to add the record to the sql server, if the
> capturer adds a record that doesn't have a "Ticket" then it will use
another
> form that will be based on another view similar to this one that will be
> based on another "Unique ID" called [Heat_nr]
>
> Plesae help, Thanks alot in advance :)
>
> ^-^
>
>

Jobi (johan.bijnensNo_Spa_M@ugine-alz.arcelor.com) writes:
> It 's not a good idea to allow NULLs on a column that should be part of
> a unique index, because in that case with sqlserver NULL = NULL and
> therefor there can be only one. 

And sometimes this is what you want. 

Sometimes it is not, and then you need to implement the constraint in
some other way.

One is to use a trigger.

Another is to create an view over the non-NULL values, and then add a unique 
index on the view, and it is this the technique that the original is using.

Just for the record, in the database I have been working with for the last
years, I have tables where only one NULL value is permitted in the unique
index (that column is typical a second or third column in the natural 
primary key). And I have tables where the rule is unique-when-not-null. 
Of historic reasons I cannot use indexed views, so I use triggers instead.

-- 
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp