Table Valued Function with primary key and spatial index - is this possible?

Question

Table Valued Function with primary key and spatial index - is this possible?

olibbhq

SSCarpal Tunnel

Points: 4034
More actions
January 30, 2017 at 4:39 am

#326467

Good morning,
I have tried to find documentation of it but have been unable to. Can someone please tell me if it is possible to add a spatial index to a table valued function?
A primary key is possible and I have implemented this.
Many Thanks,
Oliver

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

How do you add a primary key to a function? That simply makes no sense.
Maybe you're talking about adding a primary key to a table variable inside a function. The main problem with this approach is that you're using a multi-statement table-valued function. Those functions can be 40+ times slower than inline table-valued functions or direct queries.
Remember, a function is a set of code. It's not data, so it cannot have indexes on its own. It will take the indexes from the tables that are being called from it.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

olibbhq - Monday, January 30, 2017 4:39 AM
Good morning,
I have tried to find documentation of it but have been unable to. Can someone please tell me if it is possible to add a spatial index to a table valued function?
A primary key is possible and I have implemented this.
Many Thanks,
Oliver

How about you tell us what problem you are trying to solve and perhaps we can give you a viable solution.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

drew.allen SSC Guru Points: 76985 More actions · Answer 3

Also, if you create an ITVF, SQL will inline the code. That is, it will replace the function call with the body of the code from the function. That means that it will automatically use any applicable index(es) on the table(s). If it's not using an index that you expect, then you're probably better off trying to figure that out rather than trying to create a new index.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA