Sybase Blog -Anything About Sybase ASE,REP,IQ.

Sunday, February 6, 2011

Default,Rules and User Defined Datatype

Question
=======
Consider a table creation script
create table table1(id int not null,
cost smallmoney default $10
)
will it allow null value in column "cost"?

Answer
======
No


Source (Unleashed)
Question
=======
How to find all tables and columns which are bind with a rule/default?
Answer
======
select "table"=o.name, "column"=c.name,"user"=user_name(uid),"rule"=object_name(domain)
from sysobjects o,syscolumns c
where o.id=c.id
and object_name(c.domain)='rule/default name'

Sequence of events when using datatypes
=================================
sp_addtype ssn_type, 'char(9)', 'not null'
go

create rule ssn_rule as
@ssn between '001111' and '1111111'
or @ssn = 'N/A'
go

create default ssn_default as
'N/A'

sp_bindrule ssn_rule,ssn_type
sp_bindefault ssn_default,ssn_type

create table ssn_table
(ssn ssn_type, name varchar(30))
go
==================
A rule or default bound explicitly to a column override a rule or default bound to a datatype. A subsequent bind to the datatype replaces the bind to the column as long as the column and datatype have the same rule or default prior to modification.

Renaming Objects

(Source - Unleashed)
Question
=======
If name of table is changed then stored procedure and views would work or will change to invalid state?

Answer
======
Even though the name of table has changed, objects like views and procedures that refer to that table by name are not affected by the change in the name. That is because SQL-based objects like Views and Procedures are stored both as text in the syscomments table and pre-parsed query tree identifying related objects by ID instead of Name. When a table name changes, a dependent view still works because the objectID of the table (stored in sysobjects) does not change.