-- seach for 
-- tests of deterministic and precise on CLR methods
-- at the end of this file to see the scipts for det/precise stuff


create DATABASE Chapter3




use Chapter3
go

drop function Add2
drop function Add3
drop function Add5
drop function Add6

drop function ui
go


create table R1
(
	id char(1),
	other as dbo.Repeat(3, id)
)

insert into R1 values('A')
insert into R1 values('B')
insert into R1 values('B')

select * from R1

create procedure ui2
as
declare @id uniqueidentifier
set @id = newid()
return 3


create table test
(
	id int
)

drop procedure u3

create procedure u3(@guid nchar(100) OUTPUT)
as
set @guid = CONVERT(nchar(100), newid())
return 0


declare @g nchar(100)
set @g = ''
exec u3 @g output
print @g
exec u3 @g output
print @g


CREATE FUNCTION GenerateGuid2 ()
RETURNS uniqueidentifier
AS
BEGIN
return dbo.NEWID2()
END

select * from sys.assembly_modules
select * from sys.objects where object_id = 20466030334
select * from sys.objects

select * from sys.procedures

select * from sys.extended_properties

drop table t2

drop function cu

create function cu(@i int) returns char(20)
as
begin
return current_user
end

drop table t6

create table t6
(
id int,
stuff as dbo.cu(id) PERSISTED UNIQUE 
)

create table t2
(
id int,
stuff as dbo.NEWID() UNIQUE,
other as (convert(char(30), id) + current_user as UNIQUE)
)

drop table t2

create table t2
(
id int,
stuff as dbo.GenerateGuid2() PERSISTED UNIQUE
)



insert into t2 values (1)

select * from t2

print dbo.GenerateGuid2()

drop table t4


create table t4
(
id int,
stuff as dbo.Add6(id) PERSISTED UNIQUE
)

select * from t4 where stuff = dbo.Add5(id)


declare @i int
exec @i = u3
print @i


create Function T3 (@repeat int, @str nchar(1))
returns int
as
BEGIN
return rand()
END

print dbo.T3(10, '1')

print dbo.ui('asdf')



create table tA2
(
id int,
stuff as dbo.Add2(id) PERSISTED UNIQUE
)
create table tA3
(
id int,
stuff as dbo.Add3(id) PERSISTED UNIQUE
)
create table tA5
(
id int,
stuff as dbo.Add5(id) PERSISTED UNIQUE
)
create table tA6
(
id int,
stuff as dbo.Add6(id) PERSISTED UNIQUE
)


CREATE FUNCTION AddFloat(@a float, @b float) RETURNS float AS BEGIN RETURN @a + @b END

drop table T10

create table T10
(
	id1 float,
	id2 float
)

drop view V10

create View V10 
with SCHEMABINDING
as
select T10.id1, dbo.AddFloat(T10.id1, T10.id2) stuff from dbo.T10


create CLUSTERED index I10 on dbo.V10 (id1)


-- ****************************************************************

create function imprecise(@x float)
returns float
with schemabinding
as
begin
return @x * 2
end

Drop table testq


create table testQ(id int identity primary key, col1 float, col2 as
dbo.imprecise(col1))

create index myIdx on testQ(col2)

drop table testq1
drop index myIdx1 on testq1


-- this works
create table testQ1A(id int identity primary key, col1 float, col2 as
dbo.imprecise(col1) )


-- this works
create table testQ1(id int identity primary key, col1 float, col2 as
dbo.imprecise(col1) PERSISTED)

-- This will work with no error because col2 is persisted
create index myIdx1 on testQ1(col2)

-- this fails
create index myIdx1A on testQ1A(col2)
--Cannot use imprecise computed column 'col2' as an index or statistics key column in table 'testQ1A' because the column is not persisted.





drop function nondet
drop index myidx2 on testq2

create function nondet(@x int)
returns char(30)
with schemabinding
as
begin
return current_user
end



create table testQ2(id int identity primary key, col1 float, col2 as
dbo.nondet(col1))

create index myIdx2 on testQ2(col2)

drop table testq3

create table testQ3(id int identity primary key, col1 float, col2 as
dbo.nondet(col1) PERSISTED)

create index myIdx3 on testQ3(col2)



-- tests of deterministic and precise on CLR methods
-- try out CLR methods
-- method deterministic precise
Add2  false false
Add3  true  false
Add5  false true
Add6  true  true

drop function Add3
-- this does not work, syntax error
CREATE Function Add3(@i int) 
returns int
 with schemabinding
 AS EXTERNAL NAME funcmutate:[Chapter3.Functions]::Add3

-- not deterministic, not precise
-- this works
create table testT2(id int identity primary key, col1 float, col2 as
dbo.Add2(col1))
go

-- this fails
-- Computed column 'col2' in table 'testT2P' cannot be persisted because the column is non-deterministic.
create table testT2P(id int identity primary key, col1 float, col2 as
dbo.Add2(col1) PERSISTED)
go



-- deterministic, not precise
-- this works
create table testT3(id int identity primary key, col1 float, col2 as
dbo.Add3(col1))
go

-- this works
create table testT3P(id int identity primary key, col1 float, col2 as
dbo.Add3(col1) PERSISTED)
go


-- not deterministic, precise
-- this works
create table testT5(id int identity primary key, col1 float, col2 as
dbo.Add5(col1))
go

-- this fails
create table testT5P(id int identity primary key, col1 float, col2 as
dbo.Add5(col1) PERSISTED)
go
-- Computed column 'col2' in table 'testT5P' cannot be persisted because the column is non-deterministic.


-- deterministic, precise
-- this works
create table testT6(id int identity primary key, col1 float, col2 as
dbo.Add6(col1))
go

-- works
create table testT6P(id int identity primary key, col1 float, col2 as
dbo.Add6(col1) PERSISTED)
go


-- now creat indexes


-- not deterministic, not precise
create index idxT2 on testT2(col2)
-- Column 'col2' in table 'testT2' cannot be used in an index or statistics or as a partition key because it is non-deterministic.

-- fails, could not create table testT2P
create index idxT2P on testT2P(col2)



-- deterministic, not precise
create index idxT3 on testT3(col2)
-- Cannot use imprecise computed column 'col2' as an index or statistics key column in table 'testT3' because the column is not persisted.

-- this works
create index idxT3P on testT3P(col2)


-- not deterministic, precise
create index idxT5 on testT5(col2)
-- Column 'col2' in table 'testT5' cannot be used in an index or statistics or as a partition key because it is non-deterministic.

-- fails becuause testT5P could not be created
create index idxT5P on testT5P(col2)


-- deterministic, precise

-- this fails
create index idxT6 on testT6(col2)
-- Cannot use imprecise computed column 'col2' as an index or statistics key column in table 'testT6' because the column is not persisted.

-- this works
create index idxT6P on testT6P(col2)



-- now make some T-SQL functions and just set the props for
-- different kinds of determinism and precision
drop table Test2A
drop table Test2AP
drop table Test3A
drop table Test3AP
drop table Test5A
drop table Test5AP
drop table Test6A
drop table Test6AP


drop table TestT2A
drop table TestT2PA
drop table TestT3A
drop table TestT3PA
drop table TestT5A
drop table TestT5PA
drop table TestT6A
drop table TestT6PA


drop function Add2A
drop function Add3A
drop function Add5A
drop function Add6A

-- deterministic false, precise false
create function Add2A(@i int)
returns int
with schemabinding
as
BEGIN
declare @f float
set @f = 3
declare @u char(100)
set @u = current_user
declare @r int
if(@u = 'a')
set @r = @i + 3
else
set @r = @i + @f
return @r
END
go

drop FUNCTION add3A

-- deterministic true, precise false
create function Add3A(@i int)
returns int
with schemabinding
as
BEGIN
declare @f float
set @f = 3
return @i + @f
END
go

drop function Add5a
go
-- deterministic false, precise true
create function Add5A(@i int)
returns int
with schemabinding
as
BEGIN
return @i + @@ERROR
End
go
-- deterministic true, precise true
create function Add6A(@i int)
returns int
with schemabinding
as
BEGIN
return @i + 6
END
go


drop view FuncProps
-- check function properties
select * from FuncProps

Create view FuncProps
as
select 'Add2A Det' Func,
objectproperty(object_id('Add2A'),'IsDeterministic') Det,
objectproperty(object_id('Add2A'),'IsPrecise') Pre,
objectproperty(object_id('Add2A'),'IsSchemaBound') Sch
UNION
select 'Add3A Det' Func, 
objectproperty(object_id('Add3A'),'IsDeterministic') Det,
objectproperty(object_id('Add3A'),'IsPrecise') Pre,
objectproperty(object_id('Add3A'),'IsSchemaBound') Sch
UNION
select 'Add5A Det' Func,
objectproperty(object_id('Add5A'),'IsDeterministic') Det,
objectproperty(object_id('Add5A'),'IsPrecise') Pre,
objectproperty(object_id('Add5A'),'IsSchemaBound') Sch
UNION
select 'Add6A Det' Func,
objectproperty(object_id('Add6A'),'IsDeterministic') Det,
objectproperty(object_id('Add6A'),'IsPrecise') Pre,
objectproperty(object_id('Add6A'),'IsSchemaBound') Sch

UNION
select 'Add2 Det' Func,
objectproperty(object_id('Add2'),'IsDeterministic') Det,
objectproperty(object_id('Add2'),'IsPrecise') Pre,
objectproperty(object_id('Add2'),'IsSchemaBound') Sch
UNION
select 'Add3 Det' Func,
objectproperty(object_id('Add3'),'IsDeterministic') Det,
objectproperty(object_id('Add3'),'IsPrecise') Pre,
objectproperty(object_id('Add3'),'IsSchemaBound') Sch
UNION
select 'Add5 Det' Func,
objectproperty(object_id('Add5'),'IsDeterministic') Det,
objectproperty(object_id('Add5'),'IsPrecise') Pre,
objectproperty(object_id('Add5'),'IsSchemaBound') Sch
UNION
select 'Add6 Det' Func,
objectproperty(object_id('Add6'),'IsDeterministic') Det,
objectproperty(object_id('Add6'),'IsPrecise') Pre,
objectproperty(object_id('Add6'),'IsSchemaBound') Sch
go

-- build tables

-- not deterministic, not precise
-- this works
create table testT2A(id int identity primary key NONCLUSTERED , col1 float, col2 as
dbo.Add2A(col1))
go

-- this fails
-- Computed column 'col2' in table 'testT2P' cannot be persisted because the column is non-deterministic.
create table testT2PA(id int identity primary key NONCLUSTERED , col1 float, col2 as
dbo.Add2A(col1) PERSISTED)
go



-- deterministic, not precise
-- this works
create table testT3A(id int identity primary key NONCLUSTERED, col1 float, col2 as
dbo.Add3A(col1))
go

-- this works
create table testT3PA(id int identity primary key NONCLUSTERED, col1 float, col2 as
dbo.Add3A(col1) PERSISTED)
go


-- not deterministic, precise
-- this works
create table testT5A(id int identity primary key NONCLUSTERED , col1 float, col2 as
dbo.Add5A(col1))
go

-- this fails
create table testT5PA(id int identity primary key NONCLUSTERED, col1 float, col2 as
dbo.Add5(col1) PERSISTED)
go
-- Computed column 'col2' in table 'testT5P' cannot be persisted because the column is non-deterministic.


-- deterministic, precise
-- this works
create table testT6A(id int identity primary key NONCLUSTERED , col1 float, col2 as
dbo.Add6A(col1))
go

-- works
create table testT6PA(id int identity primary key NONCLUSTERED , col1 float, col2 as
dbo.Add6A(col1) PERSISTED)
go


-- now creat indexes


-- not deterministic, not precise
create index idxT2A on testT2A(col2)
-- Column 'col2' in table 'testT2' cannot be used in an index or statistics or as a partition key because it is non-deterministic.

-- fails, could not create table testT2P
create index idxT2PA on testT2PA(col2)



-- deterministic, not precise
create index idxT3A on testT3A(col2)
-- Cannot use imprecise computed column 'col2' as an index or statistics key column in table 'testT3' because the column is not persisted.

-- this works
create index idxT3PA on testT3PA(col2)


-- not deterministic, precise
create index idxT5A on testT5A(col2)
-- Column 'col2' in table 'testT5' cannot be used in an index or statistics or as a partition key because it is non-deterministic.

-- fails becuause testT5P could not be created
create index idxT5PA on testT5PA(col2)


-- deterministic, precise

-- this fails
create index idxT6A on testT6A(col2)
-- Cannot use imprecise computed column 'col2' as an index or statistics key column in table 'testT6' because the column is not persisted.


-- this works
create index idxT6PA on testT6PA(col2)


-- check where

Create table source
(
id int,
data char(10)
)

select source.data from source, testT3A where source.id = testT3A.col2

create PROCEDURE setupIView
as
SET ANSI_NULLS on
SET ANSI_WARNINGS on
SET ARITHABORT on
set CONCAT_NULL_YIELDS_NULL on
SET QUOTED_IDENTIFIER OFF
go

setupIView

drop view VtestT2A
drop view VtestT2PA
drop view VtestT3A
drop view VtestT3PA
drop view VtestT5A
drop view VtestT5PA
drop view VtestT6A
drop view VtestT6PA

-- not deterministic, not precise
CREATE VIEW VtestT2A
as
select testT2A.id, testT2A.col1, testT2A.col2 from testT2A

select * from VtestT2A

create clustered index cidxT2A on testT2A(col2)
--Column 'col2' in table 'testT2' cannot be used in an index or statistics or as a partition key because it is non-deterministic.



-- deterministic, not precise
CREATE VIEW VtestT3A
as
select testT3A.id, testT3A.col1, testT3A.col2 from testT3A

select * from VtestT3A

create clustered index cidxT3A on testT3A(col2)
-- Cannot use imprecise computed column 'col2' as an index or statistics key column in table 'testT3A' because the column is not persisted.




-- not deterministic, precise
CREATE VIEW VtestT5A
as
select testT5A.id, testT5A.col1, testT5A.col2 from testT5A

select * from VtestT5A

create clustered index cidxT5A on testT5A(col2)
-- olumn 'col2' in table 'testT5A' cannot be used in an index or statistics or as a partition key because it is non-deterministic.


-- deterministic, precise
CREATE VIEW VtestT6A
as
select testT6A.id, testT6A.col1, testT6A.col2 from testT6A

select * from VtestT6A

create clustered index cidxT6A on testT6A(col2)
-- Cannot use imprecise computed column 'col2' as an index or statistics key column in table 'testT6A' because the column is not persisted.


-- persisted columns

CREATE VIEW VtestT2PA
as
select testT2PA.id, testT2PA.col1, testT2PA.col2 from testT2PA
-- this table could not be created
select * from VtestT2PA

create clustered index cidxT2PA on testT2PA(col2)



-- deterministic, not precise
CREATE VIEW VtestT3PA
as
select testT3PA.id, testT3PA.col1, testT3PA.col2 from testT3PA

select * from VtestT3PA

create clustered index cidxT3PA on testT3PA(col2)



-- table does not exist
-- not deterministic, precise
CREATE VIEW VtestT5PA
as
select testT5PA.id, testT5PA.col1, testT5PA.col2 from testT5PA

select * from VtestT5PA

create clustered index cidxT5PA on testT5PA(col2)
-- olumn 'col2' in table 'testT5A' cannot be used in an index or statistics or as a partition key because it is non-deterministic.


-- deterministic, precise
CREATE VIEW VtestT6PA
as
select testT6PA.id, testT6PA.col1, testT6PA.col2 from testT6PA

select * from VtestT6PA

create clustered index cidxT6PA on testT6PA(col2)

