August 2, 2007 at 12:33 pm
I was playing around with the new DMV views and found some indexes with a NULL name and an Index_ID of 0. So I decided to check it out and found the type_Desc of index_ID is Heap. I had about 76 objects with this description and wrote a query to find the names of those objects, figuring they were all non-indexed tables.
Imagine my surprise when several user defined functions ended up in the list.
Can someone verify I ran correct query? Does anyone have an explanation for this?
Select Distinct so.Name, si.*
from sys.indexes si
join sys.objects so
on si.Object_ID = so.Object_id
where si.index_Id = 0
Thanks,
August 3, 2007 at 2:05 am
Hi,
your query is correct. For multistatement table-valued functions a hidden physical table is created in SQL Server, in the same database. This seems to be available only while the return data is queryable. But the index type on this table will depend on the type of the return table you specify. You can actually specify a clustered index on the table that is returned by the function. An example is when you create a primary key in the table definition in the function definition. In the following example the ft2 will have a heap table created for it, ft3 will have a table that is clustered.
CREATE FUNCTION ft2 ( )
RETURNS @table TABLE
( a INT
, b NCHAR(100) DEFAULT N'aaaaaaaaaaaaaa'
)
BEGIN
INSERT INTO @table ( a )
VALUES ( 1 )
RETURN
END
GO
SELECT name, type_desc
FROM sys.indexes
WHERE object_id = OBJECT_ID('ft2')
GO
CREATE FUNCTION ft3 ( )
RETURNS @table TABLE
( a INT PRIMARY KEY
, b NCHAR(100) DEFAULT N'aaaaaaaaaaaaaa'
)
BEGIN
INSERT INTO @table ( a )
VALUES ( 1 )
RETURN
END
GO
SELECT name, type_desc
FROM sys.indexes
WHERE object_id = OBJECT_ID('ft3')
to see when the table data is actually allocated have a look at the sys.sysidxstats table (via the admin connection)
Regards,
Andras
August 3, 2007 at 3:59 am
So essentially anything saved to the system that uses a table variable could potentially be listed in sys.indexes as a type "Heap" then.
Hmmm. I suppose that makes sense. Would be nice if MS would document that sort of thing, though so people don't get confused by it.
August 3, 2007 at 4:24 am
It does not seem to affect table variables; the only place I've seen this is with multi statement table valued functions.
Andras
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply