Yet Another DBA (1/14/2015)
Actually it depends:-P
Then you will get the expected error.
After recreating the table with ANSI_DEFAULTS OFF, when tried to insert the records, you will see the below error.
Msg 515, Level 16, State 2, Server AA\BBYY, Line 1
Cannot insert the value NULL into column 'myid', table 'LocalWork.dbo.mytable'; column does not allow nulls. INSERT fails.
Its like setting the COLUMN to NOT NULL and trying to insert NULL, which of-course, SQL has to do its job and stop and warn the user who is trying to insert NULL.
(correct me if I am wrong) But the behaviour of the COUNT(*) will remains the same, it is just used to count all the rows (NULL or NOT NULL does not matter). COUNT(*) works on the data which is already in the table and it just returns the count. It does not worries about the SETings done in that specific connection. Count(*) is the "returning the result" part where as setting the ANSI_DEFAULTS OFF at the time of table creation and trying to insert data after table created is the "creation part" (the other half).
(and this another age old debate, like usage of collation, if there is no any SET option mentioned then the SSC'ian has to consider the default values 😉 )
//-edit; Added last to lines and fixed some typoes
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.