create function dbo.udf_isLessThanMaxA (@Col int)
returns bit
as
begin
declare @return bit

select	@Return = case when @Col < max(ColA) then 1 else 0 end
from	dbo.TableA

return @Return
end;
go

set nocount on;
go

-- create and populate first table
create table dbo.tableA (colA int);
go
insert into dbo.tableA
	select 1 union select 2

-- now create second table that has check constaint that inserted valued must be less than a column in first table (tableA)	
create table dbo.tableB (colB int constraint chkCount check (dbo.udf_isLessThanMaxA(colB) = 1))

-- now try to violate check by inserting value greater than 2 (which is max in first table)
-- * FAILS
insert into dbo.tableB
	values (3)
	
-- try to insert value that is less than max in first table
-- * SUCCEEDS
insert into dbo.tableB
	values(1)



-- cleanup
drop table dbo.tableA
drop table dbo.tableB
drop function dbo.udf_isLessThanMaxA