July 1, 2009 at 6:46 pm
Can i ?
I have two fields in a table like this
A as INT ie data like 105
B as VARCHAR(75) ie data like 'Smith'
I need to get these like '105_Smith'
SO I guess its CAST(A AS VARCHAR) + '_' + B i n the for the formula in the properties part of design in the SQL server table.
If not please advise.
QUESTION : Can I place a constriant on the computed column so that the combinations are always unique ??? If so how ?
Thanks.. for being nice !
July 2, 2009 at 2:14 am
Hello,
You can create a Unique Constraint across multiple columns i.e. you can do this to the two Underlying Table Columns rather than a Computed Column.
Please see the Books Online Topic “UNIQUE Constraints“.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
July 2, 2009 at 2:43 am
Digs (7/1/2009)
SO I guess its CAST(A AS VARCHAR) + '_' + B i n the for the formula in the properties part of design in the SQL server table.
Hi,
try this method
create function ABCD
(
@a int,
@b-2 varchar(6)
)
returns varchar(15)
AS
begin
declare @RESULT varchar(15)
select @RESULT = cast(@A as varchar(5))+'_'+@B
return (@RESULT)
end
create table temp1
(
A smallint,
B varchar(5),
c as (dbo.ABCD(A,B))
)
insert into temp1
select 1,'A'
union all
select 2,'B'
select * from temp1
RESULT
ABc
1A1_A
2B2_B
ARUN SAS
July 2, 2009 at 3:59 pm
How much overhead am I placing on a table with a computered column?
Will it slow inserts down much ?
July 2, 2009 at 11:42 pm
Hello again,
Unless you persist the Computed Column, it is not physically stored, so it will not impact system efficiency (including Inserts).
Please see the Books Online Topic “Computed Columns“.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
July 4, 2009 at 6:40 pm
I gave it a go. I go the computed column working ok.
Buy I couldnt make it unique key or index as error message said ' something was not determinstic' or something..
Neither of the fields I was adding together as a string was an index or a PK ??
Any ideas ??
July 8, 2009 at 9:06 pm
Hello again,
As mentioned in my first reply. You can add the Unique Constraint to the combination of "real" columns in the Table that the Computed Column is built on. I would probably go for this option myself.
From the (partial) error message that you’ve quoted it sounds like you might have to persist the Computed Column if you really want to add the Unique Constraint to it.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply