How to script adding a field to a table

Hi - I would like to know what sql to run in Query Analyzer to add two
fields to an existing table (I know you can do this in Enterprise
Manager - but I'd like to be able to send a script to someone to let it
happen automatically).
Column Name: IDCreated
DataType: DateTime
Length: 8
Allow Nulls: False
Default Value: (getdate())
and
Column Name: ChangeNum
DataType: bigint
Length: 8
Allow Nulls: False
Identity: Yes
Identity Seed: 45
Identity Increment: 1
Thanks for any help,
Mark
*** Sent via Developersdex http://www.examnotes.net ***Do it in EM, and press "Save Change Script" button before exiting the window
s, and you will get the
script served on a silver plate (almost).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mark" <anonymous@.devdex.com> wrote in message news:en82DkCyFHA.3000@.TK2MSFTNGP12.phx.gbl..
.
> Hi - I would like to know what sql to run in Query Analyzer to add two
> fields to an existing table (I know you can do this in Enterprise
> Manager - but I'd like to be able to send a script to someone to let it
> happen automatically).
> Column Name: IDCreated
> DataType: DateTime
> Length: 8
> Allow Nulls: False
> Default Value: (getdate())
> and
> Column Name: ChangeNum
> DataType: bigint
> Length: 8
> Allow Nulls: False
> Identity: Yes
> Identity Seed: 45
> Identity Increment: 1
> Thanks for any help,
> Mark
> *** Sent via Developersdex http://www.examnotes.net ***|||Here ya go:
create table MyTable
(
PK int primary key
)
go
alter table MyTable
add
IDCreated datetime not null
constraint DF1_Myatble default (getdate())
, ChangeNum bigint not null identity (45, 1)
go
drop table MyTable
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Mark" <anonymous@.devdex.com> wrote in message
news:en82DkCyFHA.3000@.TK2MSFTNGP12.phx.gbl...
Hi - I would like to know what sql to run in Query Analyzer to add two
fields to an existing table (I know you can do this in Enterprise
Manager - but I'd like to be able to send a script to someone to let it
happen automatically).
Column Name: IDCreated
DataType: DateTime
Length: 8
Allow Nulls: False
Default Value: (getdate())
and
Column Name: ChangeNum
DataType: bigint
Length: 8
Allow Nulls: False
Identity: Yes
Identity Seed: 45
Identity Increment: 1
Thanks for any help,
Mark
*** Sent via Developersdex http://www.examnotes.net ***|||Thinking about it, go with Tom's suggestion. EM often does these things in a
less than optimal way.
Often you see EM creating a new table, copy data etc etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
news:%23xDANtCyFHA.1856@.TK2MSFTNGP12.phx.gbl...
> Do it in EM, and press "Save Change Script" button before exiting the wind
ows, and you will get
> the script served on a silver plate (almost).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Mark" <anonymous@.devdex.com> wrote in message news:en82DkCyFHA.3000@.TK2MS
FTNGP12.phx.gbl...
>|||Like this:
ALTER TABLE table_name ADD idcreated DATETIME NOT NULL
CONSTRAINT df_table_name_idcreated
DEFAULT CURRENT_TIMESTAMP ;
ALTER TABLE table_name ADD changenum BIGINT NOT NULL
IDENTITY(45,1) ;
Usually, when you add an IDENTITY column you will want to add a unique
or primary key constraint on that column. Although that's not
mandatory, IDENTITY itself won't prevent duplicates in all
circumstances because the auto-generated value can be overridden or the
seed can be changed. Also, IDENTITY is typically referenced by a
foreign key, for which a constraint is required.
Depending on your requirements you can add a constraint like this:
ALTER TABLE table_name
ADD CONSTRAINT ak_table_name_change_num UNIQUE (changenum) ;
David Portas
SQL Server MVP
--|||> Often you see EM creating a new table, copy data etc etc.
and sometimes it is the optimal way, is it not?

How To Retrive Data If There Are Large No. Of Datas In Table(by Minimum Time Conusum

MY PROJECT IS HOSPITAL MANAGEMENT SYSTEM
AND THERE ARE ABOUT 700 PATIENT DAILY VISIT THE HOSPITAL/ ADDING NEW PATIENT(LARGE HOSPITAL).
SO, LARGE NUMBER OF PATIENTS ARE THERE. NOW SUPPOSE AFTER FEW YEARS THERE ARE ABOUT 70000000 PATIENTS RECORDS IN DATABASE , THEN TO FETCHNIG SOME PATIENT'S DETAILS IS VERY TIME CONSUMING [ BY SELECT * FROM PATIENT]
THEN WHAT SHOULD I DO? ANY ONE HAVE ANY IDEA?

AND ONE MORE THING I WOULD LIKE TO KNOW THAT HOW THE IMAGES WILL STORE IN DATABASE ACCESS?
PLS REPLY SOON

RAHUL PRAJAPATI
rahul_nadiad@.yahoo.com

Quote:

Originally Posted by RAHULPRAJAPATI

MY PROJECT IS HOSPITAL MANAGEMENT SYSTEM
AND THERE ARE ABOUT 700 PATIENT DAILY VISIT THE HOSPITAL/ ADDING NEW PATIENT(LARGE HOSPITAL).
SO, LARGE NUMBER OF PATIENTS ARE THERE. NOW SUPPOSE AFTER FEW YEARS THERE ARE ABOUT 70000000 PATIENTS RECORDS IN DATABASE , THEN TO FETCHNIG SOME PATIENT'S DETAILS IS VERY TIME CONSUMING [ BY SELECT * FROM PATIENT]
THEN WHAT SHOULD I DO? ANY ONE HAVE ANY IDEA?

AND ONE MORE THING I WOULD LIKE TO KNOW THAT HOW THE IMAGES WILL STORE IN DATABASE ACCESS?
PLS REPLY SOON

RAHUL PRAJAPATI
rahul_nadiad@.yahoo.com

Hi there,

The structure of the database isteslf can play a major role is enabling fast access, heard of database normalization? Why not use database normalization to restructure your current database.

In relational database theory, normalization is the process of restructuring the logical data model of a database to eliminate redundancy, organize data efficiently, reduce repeating data and to reduce the potential for anomalies during data operations. Data normalization also may improve data consistency and simplify future extension of the logical data model. The formal classifications used for describing a relational database's level of normalization are called normal forms

Kindly refer to below link for further reading & understanding, hope it helps.

http://en.wikipedia.org/wiki/Database_normalization
You can use Binary large object (BLOB technique in order to be able to safe graphics and other related material in database.

Good luck & Take care.|||Hi Sashi,
I am also a newbie to these things.
My understanding is that, data normalization eases the adding the data and managing the constraints.
But normalisation results in more number of tables than without (or lower level of) normalisation.
So doesnt it mean that it slows the data access as it requires to join more number of tables.

Please correct me if I am wrong.

-Subhash.

Quote:

Originally Posted by sashi

Hi there,

The structure of the database isteslf can play a major role is enabling fast access, heard of database normalization? Why not use database normalization to restructure your current database.

In relational database theory, normalization is the process of restructuring the logical data model of a database to eliminate redundancy, organize data efficiently, reduce repeating data and to reduce the potential for anomalies during data operations. Data normalization also may improve data consistency and simplify future extension of the logical data model. The formal classifications used for describing a relational database's level of normalization are called normal forms

Kindly refer to below link for further reading & understanding, hope it helps.

http://en.wikipedia.org/wiki/Database_normalization
You can use Binary large object (BLOB technique in order to be able to safe graphics and other related material in database.

Good luck & Take care.

How to Re-Structure Form After adding an Identity

I'd created a dataform from a table stored in a SQL database, after that I changed the primary key field to be an identity, refreshed the dataset but when I execute the program, it raises an exception when I try to save the record after adding a new one asking me for the value of this field. What can I do to be sure it will be created automatically?

Move the thread here in order to get more helpful answers.

|||

Hi Juan,

I'm not sure why this got moved here, it seems like a problem with the DataSet definition...

It would help to have the actual error you're getting, but my guess is that the field you added didn't acutally get the Identity proprty set correctly, so it is not generating new values automatically. When you try to add a new record, there is no value specified for your PK so you get the error. Verify that the field works correctly outside of your DataSet, if it doesn't, then you have a problem with the table, if it does, then there is something wrong with your DataSet.

Mike

|||Ok. I've got the answer. After change the datatype in the table as IDENTITY, and refresh the dataset, the identity atribute in the dataset field does not change automatically. Thanks Mike