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) A socialist is someone who will give you the shirt off *someone else's* back.
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
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