| Author |
Topic |
|
weitzhandler
Yak Posting Veteran
64 Posts |
 Posted - 2010-09-05 : 05:20:48
|
| Hello!When you create a view of one table (i.e. SELECT * FROM Table), it marks the table's identity column as an identity col in the view as well.I have a few that selects columns from several nested tables, but they are all related in a main identity column I select as well, but SQL Server doesn't mark this column as identity, is there a way to do it?Shimmy |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-09-05 : 06:16:24
|
My first question is: "Why?".
N 56°04'39.26"E 12°55'05.63" |
 |
|
|
weitzhandler
Yak Posting Veteran
64 Posts |
Posted - 2010-09-05 : 07:07:09
|
| Cuz Entity Framework is looking for the IDENTITY columnShimmy |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-09-05 : 09:58:22
|
I am confused.FIrst you write "SQL Server doesn't mark..." and now you write "Cuz Entity Framework".I can't tell about Cuz products and how they work (Google returned nothing), but SQL Server doesn't "mark" a column as an identity column in a view.So, my question is, do you need the IDENTITY column, or the primary key column(s)?
N 56°04'39.26"E 12°55'05.63" |
|
|
|
weitzhandler
Yak Posting Veteran
64 Posts |
Posted - 2010-09-05 : 10:06:44
|
| SQL does mark columns as identity as long as the view consist of one table.My question is if that could also be with multiple-table views.Shimmy |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-06 : 03:25:58
|
| "SQL does mark columns as identity as long as the view consist of one table."I'm not understanding something here, and I expect others to.There is no such thing as an IDENTITY property on a column in a VIEW. So we just need you to explain what you mean by "SQL does mark columns as identity" please. |
|
|
|
weitzhandler
Yak Posting Veteran
64 Posts |
Posted - 2010-09-06 : 04:11:38
|
| There IS such a thing.Wanna see? In SSMS Right click the ID column on a single-table view, you'll see in the properties "Identity = True".That's what I am looking for.Why in multi-tabled views it doesn't work.Shimmy |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-06 : 06:34:52
|
| Its the property of the underlying table's column, not the View.As you yourself said, it serves no purpose in a multi-table view.If you need the IDENTITY of a record after insert use scope_identity() - but make sure it refers to the correct Insert - perhaps IDENT_CURRENT('MyTable') will suit you better if you are doing multi-table inserts.Perhaps better you describe what problem you are trying to solve, rather than the solution you want! I'm just guessing what you are trying to do |
|
|
|
weitzhandler
Yak Posting Veteran
64 Posts |
Posted - 2010-09-06 : 07:21:36
|
| I don't need it for inserts, I am not even gonna use insert to that table, I just need to determine if the column is used as an ID column.Is there a way to do it in a multi-tabled view?Shimmy |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-06 : 12:36:47
|
| Only if you can discover what the underlying Table + Column is - then you can check if that column has IDENTITY attribute, or not.You can view the Source Code for a VIEW - which will enable you to discover which table a given column comes from. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-09-06 : 13:37:22
|
There is INFORMATION_SCHEMA.VIEW_COLUMN_USAGE view to play with.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2010-09-06 : 14:11:39
|
| It doesn't work because there can only be a single identity column on a table. A view is a virtual table - and in a multi-table view that has multiple identity columns, which one should be selected?When you have a view of a single table - the underlying properties of the table can be exposed. As soon as another table is included, that property cannot be exposed because there is no way to determine which one is the identity for that virtual table.Jeff |
|
|
|
|
Mark columns as Identity in multi-table views?