CODEWITHSUNDEEP
sql-serverms-accessvba
namko
namko

Reputation: 647

Unique index does not appear in SQL Server

I have views in SQL Server that I have linked to Access as tables. When I create unique index on the views for example like this:

  CurrentProject.connection.Execute "CREATE UNIQUE INDEX PK ON viewTable (BookID);"

I can see that the linked table which is actually a view in SQL Server has primary key symbol next to it. However, if I go to the view in SQL Server, even though the field ID and InfoID are there it looks like there is no unique index created.

Upvotes: 1

Views: 332

Answers (1)

Albert D. Kallal
Albert D. Kallal

Reputation: 49204

What you see is correct.

That “index” creating is ONLY for the Access client. It does NOT create an index on the server side.

Keep in mind that you do NOT want to use an “indexed” view in SQL in 99% of cases.

SQL server will utilize ALL and ANY existing indexes that exist on the server side. Again: you do NOT need to index a view if the base table has the appropriate indexing.

When you execute that command, then ONLY the client is “informed” that the column is in fact a PK. In ANY and all other cases, use of the index command does ZERO of use for Access, and does ZERO of use on sql server side.

And for your given example, you do not need that index command UNLESS you want the view to be updatable.

When you use the Access interface to link to a SQL view, then Access will prompt you for the PK column. So you ONLY need that “index” command if you create the liked table with VBA code. When you create a linked table with Access code, then it does NOT prompt you for the PK. And without a PK defined, then Access will treat that view as read only.

So the ONLY case you need to execute that command is if the liked table was created with code as opposed to the Access interface.

Your example of the command does NOT I fact show up on the server side, and is ONLY to tell Access (client side) which column to use as the PK.

Any indexing you setup on sql server in the “base” tables will ALWAYS be used by the view. You can certainly index a view on sql server, but ONLY if you going to index some data that is not already indexed in the base table, or you have a massive data set, and you want to create a view on that massive data set with an index for performance reasons. Unless that view is somehow special limiting the data from the base tables on sql server, then such indexing is of no use, and as noted, SQL server will ALWAYS use indexing from the base table(s) for that viewed (even when the view has no indexes).

I suspect that the view in question comes from a base table on SQL server, and if that base table has index (such as on the PK), then the view does not need nor require an index.

In your example, the create index command ONLY serves to tell access what column is to be used for the PK. If you don’t tell access what column to use for the PK, then the view will be read only.

So if you bring up that view in the sql server design tools, then the PK of the table(s) involved DOES show. A view on sql server does NOT have the concept of a PK. Since the view does not have a PK (it uses the ones from the base table), then Access cannot guess or know or tell what column is to be used as the PK. (sql server knows, but Access does not). So in these cases, then you have to execute your index command, or use the Access interface which will prompt you for the PK column when you create the link to the view.

So that index command is not sent to sql server – it ONLY use is to “tell” or “inform” Access client side what column is to be used as the PK column – nothing more, nothing less.

I should also point out that while you can create indexes for views on sql server, you still cannot define a PK in the view. So even if you did create an index on the sql view, creating of such index(s) still does not allow you to define a PK.

Upvotes: 3

Related Questions