October 23, 2020 at 5:07 pm
While debugging something, I came across a problem that Table-valued-function allegedly has primary key on it. The query below produces 2 records.
select k.name,
o.name,
o.type_desc,
k.type_desc
from sys.objects o join sys.key_constraints k
on o.object_id = k.parent_object_id
where k.type = 'PK'
and o.type_desc = 'SQL_TABLE_VALUED_FUNCTION'
But if I find these 2 TVF's in SSMS and script them in Create or Modify modes, I can't find any Primary Key.
October 23, 2020 at 5:13 pm
They're Views, not TVFs. Views don't have PKs.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 23, 2020 at 5:31 pm
They are views? But I specified o.type_desc = 'SQL_TABLE_VALUED_FUNCTION' in Where clause.
October 23, 2020 at 5:49 pm
They are views? But I specified o.type_desc = 'SQL_TABLE_VALUED_FUNCTION' in Where clause.
Sorry, I thought you were referring to sys.objects and sys.key_constraints.
TVFs obviously don't have PKs. Not sure why it would appear that they do.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 23, 2020 at 10:46 pm
It would be helpful if you could give us the definitions of all the objects involved.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
October 24, 2020 at 4:27 pm
A multi-statement table valued function can be setup with a primary key constraint - in fact, you can define default values, computed columns, column constraints and index options in the table definition for a multi-statement TVF.
These cannot be defined on an inline-table valued function and will not show up with any constraints.
Based on the syntax, I am now wondering if using these constraints materializes the data in some way. It appears that you can define indices with a specified fill factor, statistics, locking, etc... I would think that would only work if the index is materialized in some way that can then be utilized in the outer query.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply