UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> View Key Fields And Permissions, SQL Server 2012    
 
   
haresfur
post Nov 23 2017, 09:35 PM
Post#1



Posts: 221
Joined: 4-April 12
From: Bendigo, Australia


I'm confused about using SQL Server views with Access and have 2 questions. I am trying to create a view that essentially flattens part of my database for easier data retrieval in ad-hoc queries.

First, When I try to attach the view in Access it asks for the unique record identifier fields. I'm joining 7 tables and I think this would be the key fields for the underlying tables, correct? But I have also excluded most of those because the user doesn't need to see them. What am I supposed to do?

Second, I would like the results returned as a read-only snapshot. My understanding is that this may or not be the case with a view (please correct me if I'm wrong). I'm unclear how to do that.

Thanks.

--------------------
-- Evan
Go to the top of the page
 
nvogel
post Nov 26 2017, 06:05 AM
Post#2



Posts: 811
Joined: 26-January 14
From: London, UK


Every view ought to have a key. Keys for a view should be implicit in the view definition, i.e. you need to include the necessary columns so that the data in the view always contains unique rows. Keys can also be made explicit by defining a unique index on the view. Unique indexes for views are usually defined only when needed for performance optimization reasons. The key constraints on the underlying tables are normally sufficient to guarantee uniqueness.

Use the command DENY INSERT,UPDATE,DELETE ON ... to prevent updates to views.

In SQL Server views are always up-to-date with the current state of data in the tables. There is no concept of view "snapshots" as in Oracle and other DBMSs. You can use SQL Server temporal tables or change tracking to see data in its previous state.
Go to the top of the page
 
haresfur
post Nov 26 2017, 04:55 PM
Post#3



Posts: 221
Joined: 4-April 12
From: Bendigo, Australia


Thank you.

Where my key fields are foreign keys in other tables, does it make a difference in terms of performance whether I include the field from the primary table or the FK table? The PK table should already be indexed on it, but maybe the optimizer would do better if the field from other table was used.

--------------------
-- Evan
Go to the top of the page
 
haresfur
post Nov 26 2017, 05:17 PM
Post#4



Posts: 221
Joined: 4-April 12
From: Bendigo, Australia


I'm having trouble figuring out how to use the DENY statement. I guess it wraps around the Select somehow, but I don't get the syntax. Thanks.

--------------------
-- Evan
Go to the top of the page
 
AlbertKallal
post Nov 26 2017, 06:10 PM
Post#5


UtterAccess VIP
Posts: 2,551
Joined: 12-April 07
From: Edmonton, Alberta Canada


Keep in mind that when you link such a view from Access, then one most certainly would and could and should select the primary key of the given table.

And that WILL show as the PK in the access linked table. (flip the linked table into design view – ignore the read only prompt – if you choose a PK, then it will show as such in Access).

If there is more than one table then of course choosing the PK from the “main” table makes sense if there are child records in that query.

You don’t have to choose a PK during linking and can just hit “ok” at the prompt “asking” for a PK. However that makes the view read-only. And if you then flip the linked table into design view – you not need a PK set.

So a linked view does show and require a PK if you going to update that table. As you pointed out many views are updatable – but only if you choose and set the PK during the linking process.

I can’t say off the top of my head the “rules” for queries that are updatable with multiple tables – but the rules are similar to an access query with multiple tables (which also can be updatable).

In fact I find usually just including the PK of the main table allows the view to be updatable, whereas with a Access query, you often have to include the PK from both tables, and the FK – (at least that’s what I remember).

However, choosing additional keys (PK of all tables, and the FK) may well impove the updatablity of that view – I can’t say for certain.

edit:
Of course if you doing inserts in place of just a update - then you need to include the FK's from the child table(s) and provide a value.
There are some cases in which the FK will be inserted when editing the table directly - but I can't remember if this ever applies to linked views.

Regards
Albert D. Kallal (Access MVP, 2003-2017)
Edmonton, Alberta Canada
kallal@msn.com
Go to the top of the page
 
AlbertKallal
post Nov 27 2017, 04:57 AM
Post#6


UtterAccess VIP
Posts: 2,551
Joined: 12-April 07
From: Edmonton, Alberta Canada


Yes, something in missing in your case.

My best guess is the DSN you use is set as read only.

Or the default schema of "dbo" has some kind of special permissions.

I never not seen the PK copy over to Access and show up in the linked table as a PK.

In other words, I am un-able to re-produce you issue. In all my cases, access sees the PK value. This is for both views, and that of linked tables. And that of views even with more then one table.

So this is standard behaviour for linked views to SQL server.


And there no mention of this issue "anywhere" on the internet. Try any article - you see the PK copy over such as this one:

https://www.mssqltips.com/sqlservertip/1490...rom-SQL-server/


So “something” is strange here. As noted, I never not had the PK copy over for a view.

So I would check:
The DSN you use to link views. (read only).

Some permissions issue.

So something is rather different here. I done this for a considerable length of time.
And note the screen shots in the above article – once again the PK carries over.

So for over 10+ years of doing this, never seen the PK not copy over from views into Access – and they are usually updateable just like tables.

Regards,
Albert D. Kallal (Access MVP, 2003-2017)
Edmonton, Alberta Canada
Go to the top of the page
 
AlbertKallal
post Nov 27 2017, 06:27 AM
Post#7


UtterAccess VIP
Posts: 2,551
Joined: 12-April 07
From: Edmonton, Alberta Canada


Excellent!
(because I then had to spend some time figuring this issue out).


You note in the comments that follow, some point that that even when linking views, the PK is picked up by Access.

A quick google suggests that if you created the link via code, then Access does not pick up the PK. This makes sense since you never get the prompt to select the PK.

So this means you have to delete the linked table/view, and re-create it from the Access UI, (external data – ODBC database), then you DO get a user prompt to select the PK. This will result the in PK being seen.

So if the links to views are created by code, and not the UI – then you not see the PK.

Some “suggest” that then a re-link or re-fresh will lose the PK setting (it does not for me).

So for code that creates a link to a table – it picks up the PK.

So for code that creates a link to a view – it does NOT pick up the PK.

And some links suggest that if you re-link (and ALSO) point to another database, then this can cause this effect (however I not seen it).

So if you using “code” to add or create the one link to the view, then you never get/see that final prompt to select the PK column for the view.

So it would seem it “depends” on how you ORIGIONAL link the table.

This seems to quite much explain this issue.

You can simply try adding the link again with the Access UI – (and keep the existing one). Now compare both in the UI by flipping into design mode.

So it would seem that the behaviour depends on how the table link was created. With larger applications, I have a “wee bit” of code that adds the one link – this thus results in MANY views not having the PK. I “dare” say with 600 table links in a larger application, it is “very” rare that views are required to be up-datable – they are used for performance for reports, and searching screens.

So I never really did “think about” or “absorb” this issue. I just always seen the PK appear when using Access UI to create the link.

I thus am much thankful for you posting back with “certainty” of what you were seeing.

Do feel free to follow up – but the culprit seems to be using code vs the Access UI to create the link.

(and if re-link code deletes the links during a re-link, then again the PK will be lost - my re-link code does NOT delete the links - only refreshes them).

Regards,
Albert D. Kallal (Access MVP, 2003-2017)
Edmonton, Alberta Canada
kallal@msn.com
Go to the top of the page
 
nvogel
post Nov 27 2017, 08:10 AM
Post#8



Posts: 811
Joined: 26-January 14
From: London, UK


To be clear, my original reply was only to recommend that the view should have a key. I was not commenting on whether Access could see and display that key in the UI (personally I would be unlikely to use Access UI to view the structure of a SQL Server table or view). I don't really have anything more to add to the interesting comments of Colin and Albert.
Go to the top of the page
 
AlbertKallal
post Nov 27 2017, 11:53 AM
Post#9


UtterAccess VIP
Posts: 2,551
Joined: 12-April 07
From: Edmonton, Alberta Canada


Like you said – “rare” use the ODBC manager to add a table. I much also admit this is the case for me!

And as noted, for tables it finds the PK anyway.

And since it been “many” moons since you used the ODBC manager, the reason why you see the wrong tables is this panel during the ODBC create DSN process.

Just remember to NEVER hit enter key during the panels that pop up - this is because “enter” means finish key – not go to next panel.

On about the 3rd panel, then you get this one, and have to “change” the default database to the one in question.



So if you hit “enter” during any of the above panels, then that means “finish” and you miss the panel in which you have to change the database to the one in question.

Regards,
Albert D. Kallal (Access MVP, 2003-2017)
Edmonton, Alberta Canada
Go to the top of the page
 
haresfur
post Nov 27 2017, 04:55 PM
Post#10



Posts: 221
Joined: 4-April 12
From: Bendigo, Australia


This has all been interesting and educational, if a bit confusing. My take-away:

If I do not assign a primary key when linking to the view through ODBC, then it will be read only. That solves my immediate issue of making sure that the user (sometimes me) doesn't inadvertently mess things up in the database when using the view. It seems like there should be to ensure the view is read only on the sqlServer side, though.

As a side note, there was a mention of assigning a primary key to the view in sqlServer. I didn't see how to do that in SqlServer Management Studio. After creating my view, if I hit "run" it returns the proper number of records but indicates that it is read only. Once again it is fine for this purpose but I'm not entirely clear on why it is not updateable.

I'm still not clear on whether to include the PK fields in the view or the joined FK fields in the tables that contain the majority of the other fields included. Does it make a difference?

--------------------
-- Evan
Go to the top of the page
 
AlbertKallal
post Nov 27 2017, 09:32 PM
Post#11


UtterAccess VIP
Posts: 2,551
Joined: 12-April 07
From: Edmonton, Alberta Canada


QUOTE
I'm still not clear on whether to include the PK fields in the view or the joined FK fields in the tables that contain the majority of the other fields included. Does it make a difference?


My apologies for going somewhat off topic - however it was VERY useful in regards to the PK issue and read-only.

In your case, no - you really don't care much if you include the PK or even the FK values - since your need is for read only, then this issue is a NON issue and you have zero worries.

And this choice will not change nor effect performance. There really no such thing as a local index on a linked table (the index and it possible large amount of data is NOT created nor stored local to my knowledge).

So you can rest easy - such choices don't effect performance in any way I am aware of.

Regards,
Albert D. Kallal (Access MVP, 2003-2017)
Edmonton, Alberta Canada
kallal@msn.com
Go to the top of the page
 
nvogel
post Nov 28 2017, 04:25 AM
Post#12



Posts: 811
Joined: 26-January 14
From: London, UK


I certainly disagree that keys are ever a non-issue! A key has two complementary purposes: integrity and identification. Irrespective of how often the data changes, practically speaking users normally need some way to identify information correctly in order to make use of the data. Keys in views are just as important as keys in tables. I think it should be obvious that showing duplicate data to users and expecting them to figure out what that means is probably not a good idea!

In SQL Server, to create uniqueness constraints on a view you have to use a CREATE INDEX statement. Usually this is not necessary as the key(s) are implicit if you include the right columns in your view definition. Indexes on views are primarily a performance tuning feature rather than a data integrity feature. There are also some restrictions on the use of indexes on views and not all views can be indexed.
Go to the top of the page
 
AlbertKallal
post Nov 28 2017, 01:43 PM
Post#13


UtterAccess VIP
Posts: 2,551
Joined: 12-April 07
From: Edmonton, Alberta Canada


@ridders wrote:
CODE
Do you think it would be feasible to somehow define a PK field if linking a view via code.


You can define the PK, but the REAL problem is you can’t deterne te PK. So you can set the PK during a re-link, but the REAL problem is to determine the PK. You can set, but can’t determine.
If you original create the link MANUALLY, then from that point on, using code to re-link the tables will retain the PK.

So you really don’t need special code here. You only need special code if you add the view link in code. But you can NOT determine the PK unless you at ONE TIME already created that link.

Keep in mind you can’t “guess” or “know” what the PK is for a view, since Access does not even know – that’s why YOU have to make the selection.

The view does NOT have a PK defined. It is you the HUMAN that makes this choice. So your relink code is does not determine the PK but can ONLY RETAIN the choice you made in the past.

So you are RETAINING a choice, not determining what the PK value of a view is since they don’t have one!

However, to answer your question – yes you can set + create the PK value in code – but you have to use DDL (SQL) to do this.

And you have to “know” the PK you want to set beforehand.

What this means is you need to “grab” the existing PK value from the linked view before you delete that view. And as noted you ****only**** need to do this if your re-link code deletes the link.

And you can ONLY get this information if you as a human made that choice at one time during the linking process.

So you are RETAINING a choice – not making a choice about the PK.

You can’t really use code to “guess” or “know” or “determine” the PK value since it not known and Access cannot even determine this.

And a “view” does not really have a PK – you have to set one in Access (since the view does not have such a setting).

I mean, it would be great if we could set this in SQL server – but I don’t believe the feature exists (to say what column in a view is to be the PK). For sure if only one table is in the view, then such a choice is easy – but with multiple tables, then this becomes an issue.

So you setting the PK value based on that “one time human” choice that was made – not on the basis of a view having a PK which it does not have defined.

So if you DID make that choice at one time, and you need to re-create the link (not a re-fresh), then yes, you can set the PK in code (but this means you saved, or grabbed that information and saved that choice the human made at one time).

There are a number of examples, and I even have such code someplace (can’t remember where). So you needing code that sets the PK.

So if you saved or grabbed or retained the human choice, then ONLY AFTER you know this choice, then yes, in code for a linked view you can set the PK.

However, it is somewhat “moot” to use code to set the PK for a view since the ONLY way you going to get what the PK value was is by examining the existing linked view that retained the human choice in the first place.

Since you MUST retain this human choice, then I would simply adopt the coding standard to NOT delete such links and only re-fresh them.

I mean if some views have a PK set, and some don’t, then your code can’t know which views to guess or use a PK for since we might have NOT wanted a PK to make the table read only.

So I think the best solution here is to not delete the table links during a re-link process. This means you should not need to change your existing table linking code to manage and retain the user choice of a PK for views.


Regards,
Albert D. Kallal (Access MVP, 2003-2017)
Edmonton, Alberta Canada
kallal@msn.com
Go to the top of the page
 
AlbertKallal
post Nov 28 2017, 02:19 PM
Post#14


UtterAccess VIP
Posts: 2,551
Joined: 12-April 07
From: Edmonton, Alberta Canada


@nvogel wrote:
QUOTE
n SQL Server, to create uniqueness constraints on a view you have to use a CREATE INDEX statement.


Ok sure, but that index is only required if the view needs some uniqueness beyond what the base tables have.

If the base table has some unique setting for a given column, then that will remain in effect for the view. I just wanted to point out that you are 100% correct about creating unique things for the view, but I wanted to stress that VERY often these settings are to be done in the base tables and those indexes etc. carry over to the view.

In most cases, you want to create such indexes on the base table – not the view.

You in general don’t need nor want to create the index on the view.

Any unique index in the base table, or any PK setting in the base table used for that view is retained, can carried over and 100% used in the view.

So if you have an index on city in the base table, then you don’t need one for city in the view.

In other words, you don’t create the index on the view to make a column unique, but on the base table (if possible).

And if such an index already exists, then you find you can’t enter duplicates into such a row – using a view to edit such data does not change this issue one bit.

And same goes for the PK etc.

The problem and issue here is that when you create a view on SQL server you don’t define the PK values for that view. Now if only one table is in the view, then obvious that table also contains the PK but you NEVER did define the PK for that view.

You THUS as a result can’t say to a view, please give me the PK of that view – (at least with multiple tables you can’t). Now in theory I suppose at some point in time SQL may WELL make a determine of which table and PK is to be used for the update, but as far as I know, there not a feature in SQL server to get or ask to be “told” what the PK of a view is.

The above quite much explains why you can only update columns from ONE table from multiple tables in a view (because the SQL created to update the view is restricted to the ONE row from ONE table in that view – and SQL likely does use the PK at some point in time).

When you create a view, then all of the indexing, the PK and virtually everything from the original tables is utilized.

So you don’t need to create an index on columns in a view if such indexes already exist in the original table. You don’t gain performance by doing this. In fact all of the existing query planning, high speed indexing is the SAME as if you had just submitted the view as a raw query string to SQL server.

So the view changes ZERO – ABSOLUTE zero in terms of how SQL server will process that query. All indexing, optimizing etc. is used for a view is used as much as if you had just typed in that SQL into the SQL manager, or sent it as a pass-though from Access. Views in this context and regards is the SAME result. - All the features and indexing etc. of the base tables are used.

So you don’t need or want to create a unique index on a column in the view if the base table ALREADY has such an index.

The only reason to add an index to a view is to create some kind of “sub set” of data and some kind of additional index that would improve performance. This usually means some kind of compounded index, or an expression that limits the data set to a sub set.

Any kind of “general” index such as “unique” or simply an index to speed things up is to be created in the base table – not in the view.

Any index in any column from the base table is 100% fully used by SQL sever for the view. The view does not by magic change this issue.

So I cannot stress this issue of existing base table index and optimizing that exists will ALSO be used in a view or any simple raw SQL sent to the server (sending raw SQL to SQL server, or using a view does not change this issue).


If you editing data based on that view, and you try to enter a PK that already exists, or enter data into a column that has a unique index, then you get an error and you don’t need any kind of create index or anything in this regards for the view (it inherited all features from the original base table).

So you don’t need to setup any special index in the view.

And you don’t need any create index commands run in Access either.

Access does not really create indexes for linked tables – the indexing remains on SQL server (not local).

So it not clear what context you mean by creating a index on the view - in most cases it not required.

Regards,
Albert D. Kallal (Access MVP, 2003-2017)
Edmonton, Alberta Canada
kallal@msn.com
Go to the top of the page
 
nvogel
post Nov 28 2017, 04:35 PM
Post#15



Posts: 811
Joined: 26-January 14
From: London, UK


Alex, You are right that in most cases you don't need to create indexes on views. You are not right that an index on an underlying table will always be used for queries against a view. Some views can benefit significantly from additional indexes, for example if the view includes a GROUP BY. You can also use an index on a view to define additional uniqueness constraints that do not apply to the base tables. In that case the view constraint applies to updates on those base tables, not just to updates on the view.

However, I believe the more important point is that you should include columns in a view that make up a key for that view. A view that contains duplicate data is a poorly designed view just as surely as a table that contains duplicate data is a poorly designed table. In relational terms views and tables are logically the same thing - both are relations.
Go to the top of the page
 
haresfur
post Nov 28 2017, 04:49 PM
Post#16



Posts: 221
Joined: 4-April 12
From: Bendigo, Australia


I don't think I explained my questions well enough. Here is a snapshot that I think will help.

Attached File  WlView.JPG ( 200.31K )Number of downloads: 4


In this view, WaterLevel is my 'primary' table and the others provide supporting information. BoreID and WlDate make up a natural key for the Waterlevel table - the user doesn't really need the WaterLevelID for constructing ad-hoc queries. From the discussion, above, I need to include it if I want the view to be updateable (more on that in a second). For that matter,the user doesn't need the BoreElevationID, either.

The other FK fields, BoreID, BoreElevationID, and ReviewFlag are included from the WaterLevel table record but I could have included them from the other tables where they are primary keys. Is one way or the other preferable in terms of performance or for other reasons?

So from the answers, it appears that I should be able to update the WaterLevel table by attaching the view in Access and defining WaterLevelID as the primary key, correct? However if I hit the execute button in SQL Server Management Studio, the cells are designated read only. I don't know if that means they are read-only in the view or just in the results of executing the view, as shown. That gets me back to how you make a view updatable or read only in SQL Server. I still don't get that part.

--------------------
-- Evan
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    17th December 2017 - 03:16 AM