 |
|
|
|
Next: SQL Query assistance
|
| Author |
Message |
External
Since: Apr 01, 2007
Posts: 4
|
(Msg. 1) Posted: Thu Sep 28, 2006 12:23 pm
Post subject: Column default values
Archived from groups: microsoft>public>sqlserver>programming (more info?)
|
|
|
Hello everyone,
I have the following problem I am not sure how to solve:
I am using sql 2005.
I have a database that has a product table (already with around 2000
products).
The product table has a price column.
I need to add another column called discount.
The discount is by default 5% of the price.
I am not sure how to implement the default.
Thanks,
Carly |
|
| Back to top |
|
 | |
|
Aaron Bertrand [SQL Serve
|
External
Since: Jan 10, 2008
Posts: 2166
|
(Msg. 2) Posted: Thu Sep 28, 2006 3:33 pm
Post subject: Re: Column default values [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
ALTER TABLE dbo.Products ADD Discount AS CONVERT(DECIMAL(12,2),
[price]*0.05);
"Carly" wrote in message
> Hello everyone,
>
> I have the following problem I am not sure how to solve:
> I am using sql 2005.
> I have a database that has a product table (already with around 2000
> products).
> The product table has a price column.
> I need to add another column called discount.
> The discount is by default 5% of the price.
> I am not sure how to implement the default.
>
> Thanks,
>
> Carly
> |
|
| Back to top |
|
| |
External
Since: Feb 17, 2004
Posts: 310
|
(Msg. 3) Posted: Thu Sep 28, 2006 4:28 pm
Post subject: Re: Column default values [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
|
|
| Back to top |
|
| |
|
Aaron Bertrand [SQL Serve
|
External
Since: Jan 10, 2008
Posts: 2166
|
(Msg. 4) Posted: Thu Sep 28, 2006 6:01 pm
Post subject: Re: Column default values [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
I think the problem is that the discount is not always going to be 5% for
every product in the table, that's just the default.
Neither a view alone, nor the solution I posted using a computed column,
solves that (if the goal is to populate a column with the discounted price
instead of the discount itself).
I think the best answer is to add a column with a default of 5% and then
create a view that calculates the discounted price based on that. This way,
you can override the default if you want...
A
"Anith Sen" wrote in message
> In general, since this value is always derivable, you'd use a computed
> column or a view rather than using another column in the base table.
>
> --
> Anith
> >> Stay informed about: Column default values |
|
| Back to top |
|
| |
External
Since: Apr 01, 2007
Posts: 4
|
(Msg. 5) Posted: Thu Sep 28, 2006 9:43 pm
Post subject: Re: Column default values [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
I am not sure if it is possible and how I can add a default constraint
that is something like [price]*0.05.
Please let me know what you think.
Thanks,
Carly
Aaron Bertrand [SQL Server MVP] wrote:
> I think the problem is that the discount is not always going to be 5% for
> every product in the table, that's just the default.
>
> Neither a view alone, nor the solution I posted using a computed column,
> solves that (if the goal is to populate a column with the discounted price
> instead of the discount itself).
>
> I think the best answer is to add a column with a default of 5% and then
> create a view that calculates the discounted price based on that. This way,
> you can override the default if you want...
>
> A
>
>
>
> "Anith Sen" wrote in message
>
> > In general, since this value is always derivable, you'd use a computed
> > column or a view rather than using another column in the base table.
> >
> > --
> > Anith
> > >> Stay informed about: Column default values |
|
| Back to top |
|
| |
External
Since: Feb 17, 2004
Posts: 310
|
(Msg. 6) Posted: Fri Sep 29, 2006 8:50 am
Post subject: Re: Column default values [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
>> Neither a view alone, nor the solution I posted using a computed column,
>> solves that (if the goal is to populate a column with the discounted
>> price instead of the discount itself).
Yeah, I see that now. Given that columns or column expressions are not
allowed in a default, he will have to work around with an additional
computed column/view as you said or a trigger (!!) during insert or make
sure the insert statements include the default value.
Seems like a fairly common scenario, perhaps there should be something
built-in to address such requirements like allowing simple
expressions/assertions or scalar udfs in defaults or some such.
--
Anith >> Stay informed about: Column default values |
|
| Back to top |
|
| |
External
Since: Jan 29, 2004
Posts: 1559
|
(Msg. 7) Posted: Fri Sep 29, 2006 9:47 am
Post subject: Re: Column default values [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
The column with the default value only holds the percentage (0.05) and then you calculate off of
that.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Carly" wrote in message
>I am not sure if it is possible and how I can add a default constraint
> that is something like [price]*0.05.
> Please let me know what you think.
>
> Thanks,
>
> Carly
>
> Aaron Bertrand [SQL Server MVP] wrote:
>> I think the problem is that the discount is not always going to be 5% for
>> every product in the table, that's just the default.
>>
>> Neither a view alone, nor the solution I posted using a computed column,
>> solves that (if the goal is to populate a column with the discounted price
>> instead of the discount itself).
>>
>> I think the best answer is to add a column with a default of 5% and then
>> create a view that calculates the discounted price based on that. This way,
>> you can override the default if you want...
>>
>> A
>>
>>
>>
>> "Anith Sen" wrote in message
>>
>> > In general, since this value is always derivable, you'd use a computed
>> > column or a view rather than using another column in the base table.
>> >
>> > --
>> > Anith
>> >
> >> Stay informed about: Column default values |
|
| Back to top |
|
| |
| Related Topics: | i couldnt take the column binding default values script th.. - Following Code is for taking table script through smo , i can take TABLE,PK ,FK, but i couldnt take the column binding default values script through SMO (EXEC sys.sp_bindefault @defname=N'[dbo].[def_BitTrue]', @objname=N'[dbo].[tTS_User].[IsActive]' ,....
Settings DateTimeOffset.MinValue and DateTimeOffset.MaxVal.. - Hi! I'm programming a table design in SQL Server 2008 and have two columns; PublishingStart and PublishingStop, both of which are defined by the DateTimeOffset data type. I would like to constrain the two columns with default values equivalent to the...
IDENTITY as Default values - I need some feedback on a tricky subject: Given: CREATE TABLE [TEST] ( [id] [bigint] IDENTITY (1, 1) NOT NULL , [id2] [bigint] NULL CONSTRAINT [DF__TEST__id2__7C104AB9] DEFAULT (dbo.IDENT('test')) [field] varchar NULL ) ON [PRIMARY] ....
Default constraints and values - Hi, Does anyone know why I am able to create a table such as follows? create table test (col1 nvarchar(1) default 'AA', col2 nvarchar(1) null) go Notice that I have a default value of 'AA' for a column that is defined as an nvarchar(1). SQL Server....
create table, default values - Hello, I'm trying recreate tables using the script that can be generated using enterprise manager. However the default values of columns are not generated. Can anyone advise how this can be remedied? Thanks Soc. |
|
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum
|
|
|
|
|
|
|
FAQ
Search
Profile
Private Messages
Log in
