SQL Server Code,Tips and Tricks, Performance Tuning

You can run the following function (OBJECTPROPERTY(object_id, N'TableIsFake') ) on an object and it will return 1 if the table is fake and 0 otherwise.
What does BOL say? The table is not real. It is materialized internally on demand by SQL Server. So does this mean it is a table valued function?
Well one way to find out.

Run this

USE AdventureWorks;
GO
SELECT name, object_id, type_desc,OBJECT_DEFINITION(object_id) as object_definition
FROM sys.objects
WHERE OBJECTPROPERTY(object_id, N'TableIsFake') = 1
ORDER BY type_desc, name;
GO


And we get back a table valued function in the resultset

name: ufnGetContactInformation
object_id: 439672614
type_desc: SQL_TABLE_VALUED_FUNCTION
object_definition; CREATE FUNCTION .... (I truncated the rest)


Now create another function

CREATE FUNCTION [dbo].[ufnGetSomeTable]()
RETURNS @SomeTable TABLE (
[ContactID] int PRIMARY KEY NOT NULL)
BEGIN
INSERT @SomeTable VALUES(1)
INSERT @SomeTable VALUES(2)

RETURN
END
GO

Run this to make sure it works
SELECT * FROM ufnGetSomeTable()

run the same query again

USE AdventureWorks;
GO
SELECT name, object_id, type_desc,OBJECT_DEFINITION(object_id) as object_definition
FROM sys.objects
WHERE OBJECTPROPERTY(object_id, N'TableIsFake') = 1
ORDER BY type_desc, name;
GO


And yes we get 2 functions back ;-)

Now we will create some temp tables to see if those are fake

CREATE TABLE #testFakeTable1234 (id int)
CREATE TABLE ##testFakeTable1234 (id int)

Now run this and you will see that those are real and not fake

USE tempdb;
GO
SELECT name, object_id, type_desc,OBJECTPROPERTY(object_id, N'TableIsFake') as IsFake
FROM sys.objects
WHERE name LIKE '#testFakeTable1234%'
OR name LIKE '##testFakeTable1234%'
ORDER BY type_desc, name;
GO

DROP TABLE #testFakeTable1234,##testFakeTable1234


Now create this function

USE AdventureWorks;
GO

CREATE FUNCTION dbo.fn_nums(@n AS BIGINT) RETURNS TABLE
AS
RETURN
WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1) ,
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L0)
SELECT n FROM Nums WHERE n <= @n;

Test it out to make sure it works
SELECT * FROM dbo.fn_nums(2)


Run the following 2 queries

USE AdventureWorks;
GO

SELECT name, object_id, type_desc,OBJECTPROPERTY(object_id, N'TableIsFake') IsFake, OBJECTPROPERTY(object_id, N'IsTableFunction') IsTableFunction
FROM sys.objects
WHERE OBJECTPROPERTY(object_id, N'TableIsFake') OBJECTPROPERTY(object_id, N'IsTableFunction')
ORDER BY type_desc, name;


USE AdventureWorks;
GO

SELECT name, object_id, type_desc,OBJECTPROPERTY(object_id, N'TableIsFake') IsFake,
OBJECTPROPERTY(object_id, N'IsInlineFunction') IsInlineFunction
FROM sys.objects
WHERE OBJECTPROPERTY(object_id, N'TableIsFake') OBJECTPROPERTY(object_id, N'IsInlineFunction')
ORDER BY type_desc, name;


So a SQL_table valued function is a fake table but a SQL inline table valued function is not a fake table????
So there you have it, confused? Good!