May 21, 2010 at 1:11 pm
If you had a single table like this:
CREATE TABLE PERSON (
[PersonId] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](50) NOT NULL,
[LastName] [nvarchar](50) NOT NULL,
[FullName] AS (([FirstName]+' ')+[LastName])
a calculated field is easy. What I really want is something like this:
CREATE TABLE SURNAME (
[SurNameId] [int] IDENTITY(1,1) NOT NULL,
[SurName] [nvarchar](50) NOT NULL)
CREATE TABLE PERSON (
[PersonId] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](50) NOT NULL,
[SurNameId] int NOT NULL,
[FullName] AS (([FirstName]+' ')+SURNAME.[SurName])
obviously this specific example is somewhat overzealous normalization, but this was the easiest way to express the problem. What's I'm attempting to do is make the database more friendly to O/R mappers and a view will cause them to not generate update code. I could create a trigger to update the field and persist the data but I was attempting to avoid that.
Is something like this possible in SQL server?
May 21, 2010 at 1:20 pm
You can create view for that
May 21, 2010 at 1:41 pm
Yes I know, but as I noted above if I do that O/R mappers will not generate update code because views aren't updatable (in SQL server). I could also simple change the results of the O/R mapper after the generation to use a view for the lookup, but that means that change must be repeated each time the code is generated. Not a big deal if you're nearing the end of development, a much bigger deal early on.
The actual data design is more complex than the example above, it's merely a simple way to get to the root of the problem. The desire is to define display constructs in the SQL code that both encourage normalization and maintain ease of use with development tools.
May 21, 2010 at 1:47 pm
you can update view in SQL Server. Read about "instead of" triggers
BTW, what you're trying to do is looking very wrong...
May 21, 2010 at 1:49 pm
Also, you can create trigger on insert/update which will look up your surname...
May 21, 2010 at 2:12 pm
As elutin points out, you have options, but if you definitively want a calculated field that accesses data from another table, you have to create a function and then call it with the calculated field. The function can access multiple tables, but is schema bound once you add it to the table definition (You can't change the function after the fact unless you remove it from the table definition) and the values cannot be persisted. (Meaning it will run once per row every time you select it out of the table). Depending on usage, this is obviously not ideal.
May 21, 2010 at 2:13 pm
Yes, I know I can create a trigger, yes I know I can create a view. A trigger means the data is persisted, the view tells the O/R mappers not to generate update code. Yes, I know I can do this by hand. What I am looking for is an alternative that won't interfere with automated code generation. I have dozens and dozens of tables, not just two as in the example, and this is a recurring issue. We want to keep the data normalized to maintain integrity, but still allow round-tripping code with O/R mappers. Persisting the data with a trigger means an order of magnitude increase in storage, obtainable but undesirable.
If a calculated field with two tables isn't possible, fine, but that's the question, not how can I do it other ways.
May 21, 2010 at 2:33 pm
Thank you.
So something like:
FullName As (Firstname + ' ' + dbo.SurnameLookup(SurnameId))
is what you're suggesting? Sounds doable. Long term the performance impact can be measured and troublesome lookups and be manually replaced with views after the fact. We specifically DON'T want the data persisted to that's fine.
May 21, 2010 at 3:19 pm
For sake of completeness the solution was:
CREATE TABLE SURNAME (
[SurNameId] [int] IDENTITY(1,1) NOT NULL,
[SurName] [nvarchar](50) NOT NULL)
create function [dbo].[SurnameLookup](@SurNameId int)
returns nvarchar(50)
as
BEGIN
Return (Select SurName from SURNAME where SurNameId = @SurNameId)
END
CREATE TABLE PERSON (
[PersonId] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](50) NOT NULL,
[SurNameId] int NOT NULL,
[FullName] AS (([FirstName]+' ')+dbo.SurnameLookup(SurNameid))
Thanks for the information.
May 21, 2010 at 3:19 pm
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply