Sql Auto grow: field

Showing posts with label field. Show all posts

Tuesday, March 27, 2012

automaticly create a record's field

I used a field as the record's number,how can I get a automaticly created
number field (it can inrease automaticly) when I insert a record into a
table?Refer to the IDENTITY property in BOL
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"authorking" <authorking2002@.hotmail.com> wrote in message
news:uKU01w2CFHA.3732@.TK2MSFTNGP14.phx.gbl...
>I used a field as the record's number,how can I get a automaticly created
>number field (it can inrease automaticly) when I insert a record into a
>table?
>|||First, let's clarify some concepts. In SQL, rows are identified by a Key not
by a "record number". In fact the concept of a record number is quite alien
to the relational database model. The Key is part of your data - it is some
subset of the attributes that uniquely identify a row.
What you are asking for is called a *surrogate* or *artificial* key. SQL
Server provides the IDENTITY feature as a mechanism for an artifically
generated, surrogate key so take a look at IDENTITY in Books Online.
IDENTITY is not a substitute for the natural key of your table. It is just a
surrogate for that key and may be used in foreign key references. Many times
you won't need IDENTITY at all. If you aren't familiar with some of these
key concepts then look them up in a book on relational database
fundamentals.
Hope this helps.
David Portas
SQL Server MVP
--

Automaticaly popluating Current Date in a Db Field

Hi,
I have a field in he db called LogDate. I have made it a timestamp type. Is
there a formula or a default value i can set for this field in the table
design view so that everytime a record is added, this field is automaticalll
y
populated with teh current date?
Thanks
--
pmudDid youy read anything in the SQL Server documentation about the timestamp
datatype? In spite of its poor name, it has nothing to do with date or
time.
Try the following instead:
CREATE TABLE dbo.MyStuff
(
StuffName NVARCHAR(32) PRIMARY KEY,
CreatedDate SMALLDATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
GO
INSERT dbo.MyStuff(StuffName) SELECT N'foo';
WAITFOR DELAY '00:00:01';
INSERT dbo.MyStuff(StuffName) SELECT N'bar';
GO
SELECT StuffName, CreatedDate FROM dbo.MyStuff;
DROP TABLE dbo.MyStuff;
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:19D4287C-0ED6-401A-AE61-9B0F0FB5EDAB@.microsoft.com...
> Hi,
> I have a field in he db called LogDate. I have made it a timestamp type.
> Is
> there a formula or a default value i can set for this field in the table
> design view so that everytime a record is added, this field is
> automaticallly
> populated with teh current date?
> Thanks
> --
> pmud|||First, you need to define the field as a datetime data type. The timestamp
data type is used for concurrency checking using optomistic locking and does
not represent an actual date and time.
I typically use a default value of CURRENT_TIMESTAMP to put the current date
and time in a field.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:19D4287C-0ED6-401A-AE61-9B0F0FB5EDAB@.microsoft.com...
> Hi,
> I have a field in he db called LogDate. I have made it a timestamp type.
> Is
> there a formula or a default value i can set for this field in the table
> design view so that everytime a record is added, this field is
> automaticallly
> populated with teh current date?
> Thanks
> --
> pmud|||Hi Aaron and Geoff,
Thanks for the reply. I made it a datetiem, and in the default value, I used
the function, GetDate() , and it worked. :)
pmud
"Geoff N. Hiten" wrote:

> First, you need to define the field as a datetime data type. The timestam
p
> data type is used for concurrency checking using optomistic locking and do
es
> not represent an actual date and time.
> I typically use a default value of CURRENT_TIMESTAMP to put the current da
te
> and time in a field.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:19D4287C-0ED6-401A-AE61-9B0F0FB5EDAB@.microsoft.com...
>
>|||Good. Just FYI, getdate() is the T-SQL specific function. The ANSI
(cross-platform) standard is CURRENT_TIMESTAMP. They are absolutely
equivalent for SQL Server, except using getdate() will provoke the "Wrath of
CELKO" (tm). :)
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:996A9A56-738B-4C38-8BE3-F89BBB68BBD2@.microsoft.com...
> Hi Aaron and Geoff,
> Thanks for the reply. I made it a datetiem, and in the default value, I
> used
> the function, GetDate() , and it worked. :)
>
> --
> pmud
>
> "Geoff N. Hiten" wrote:
>|||Hi geoff,
I had saved my tiable with GetDate() as the default value adn data type as
datetime. Now I changed teh default value to CURRETNT_TIMESTAMP as suggested
by you, but when i click on Save , the default value automatically changes t
o
GetDate() . What do you suggest for this? Can this be a problem?
Thanks
--
pmud
"Geoff N. Hiten" wrote:

> Good. Just FYI, getdate() is the T-SQL specific function. The ANSI
> (cross-platform) standard is CURRENT_TIMESTAMP. They are absolutely
> equivalent for SQL Server, except using getdate() will provoke the "Wrath
of
> CELKO" (tm). :)
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:996A9A56-738B-4C38-8BE3-F89BBB68BBD2@.microsoft.com...
>
>|||> I had saved my tiable with GetDate() as the default value adn data type as
> datetime. Now I changed teh default value to CURRETNT_TIMESTAMP as
> suggested
> by you, but when i click on Save , the default value automatically changes
> to
> GetDate() . What do you suggest for this? Can this be a problem?
This is Enterprise Manager playing tricks on you. My suggestion is to stop
using Enterprise Manager for these things, use Query Analyzer and learn the
T-SQL equivalent(s).
A|||Ok. Thanks Aaron. I will try through Quey Analyzer.
--
pmud
"Aaron Bertrand [SQL Server MVP]" wrote:

> This is Enterprise Manager playing tricks on you. My suggestion is to sto
p
> using Enterprise Manager for these things, use Query Analyzer and learn th
e
> T-SQL equivalent(s).
> A
>
>|||> Ok. Thanks Aaron. I will try through Quey Analyzer.
If you use Enterprise Manager to "verify" be aware that it may still want to
convert CURRENT_TIMESTAMP to GETDATE().

Automatically update datetime field in a database table.

hi e'body:

I have some database tables, and each one of them have a creation_date and modified_date in them. I was trying to figure out a way where when a row in one of these tables is changed using Enterprise Manager (Database -> Tables -> select table -> right click -> select all rows -> change a field in a row inside a table), is there a way apart from triggers, such that the "modified_date" column for that row get changed to 'getdate()' (rather picks up the current datetime).

thanks in advance.Here is the generic trigger I use to record who modified a record and when:
CREATE TRIGGER TR_[TABLENAME]_U ON dbo.[TABLENAME]
FOR UPDATE
AS
set nocount on
update [TABLENAME]
set Modified = getdate(),
Modifier = isnull(inserted.Modifier, (convert(nvarchar(50),suser_sname())))
from [TABLENAME]
inner join Inserted on TABLENAME.PKey = Inserted.PKey
set nocount offNote that this trigger is for update only. For inserts, you should have default values defined on the table.

automatically update another field based on other parts of the record

Sql is not a strong point with me so I'm just going to throw this out there. I have a stored procedure that updates the quantity in my 'CartItems' table. Is there a way to have something else happen within the stored procedure that will update another field based on other parts of the record? There is a 'lineTotal' field that I need to equal the 'pounds * itemSell' fields which are both fields within this record.

CREATE PROCEDURE UpdateCartItem
(
@.cartItemID Int,
@.newQuantity numeric(9)
)
AS
UPDATE CartItems Set quantity = @.newQuantity
WHERE cartItemID = @.cartItemID
GO

sure. you can update as many fields as you want within the UPDATE statement.

CREATE PROCEDURE UpdateCartItem
(
@.cartItemID Int,
@.newQuantity numeric(9)
)
AS

SET NOCOUNT ON

UPDATE
CartItems
Set
quantity = @.newQuantity
,lineTotal =pounds * itemSell
WHERE
cartItemID = @.cartItemID

SET NOCOUNT OFF

|||Or make the lineTotal a computed column. Just modify the table and tell it the lineTotal column is equal to pounds*itemSell.sql

Sunday, March 25, 2012

Automatically increasing field definition by SQL

How can I create/define a field so it'll be of the automatically increasing type with a SQL sentence? If it must be done during table creation, that's cool too.
ThanksCreate table a
(
name varchar2(100)
);

Alter table a
modify name varchar2(200);|||Are you asking how to create a column that will increase in value, or increase in size? If you are looking to create something analagous to Oracle's rowid, the syntax is different for each database engine, so you'll have to tell us which engine you are using for us to give you one answer.

-PatP|||It's on ACCESS.|||Originally posted by anat_sher
It's on ACCESS. That's helpful, but are you looking for an MS-Access AUTONUMBER (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/off2000/html/acconWhichTypeAutoNumberFieldCreate.asp) column, or a TEXT column that will increase in length each time you do something?

-PatP|||AUTONUMBER please..

It's not on ACCESS really, it's on a SQL server. But I figured it's about he same. No?|||create table tableA
(
id INTEGER IDENTITY(1, 1)
...
)

Thursday, March 22, 2012

automatical calculation of a field value

Automatic updating of datetime field

I need to automatically update a datetime field for a record to the current time whenever the record is updated.

create table t (
id bigint identity(1,1) not null primary key,
name varchar(50),
value varchar(50),
ts datetime not null default getutcdate()
)
go
insert t (name, value) values ('fred', 'bob')
go
update t set value='robert' where id=1 and name='fred'
go

One option would be to use an instead of update trigger.

create trigger update_t on t
instead of update as
update t set ts=getutcdate(),name=inserted.name, value=inserted.value from t inner join inserted on t.id=inserted.id
go

update t set value='dick' where id=1 and name='fred'
go

Sounds like I've solved my own problem, heh? Well, here's the catch ... you can't know the names of the other columns at the time you write the trigger. I.e. you only know that there is a ts field that needs to be updated internally, otherwise you want the update to do the same thing it would normally do.

Any ideas?...also, you don't know what database server it is going to be running on, so it has to be platform-independent.

...and it needs to be fully compatible with the Mayan calendar as well.

...oh yeah, and the final code must be a palindrome that reads the same way forwards as backwards! Yeah, that's it! What a kick-ass application design! Whooooo-eeeeeeee!sql

Sunday, March 11, 2012

Automatic Field Entry

Hi,

I'm creating a database using SQL Server 2005 Express Edition (Comes with Visual Web Developer). The table which I am creating has the following Fields - all don't allow nulls:

ID
UserId
Date
Description

(UserId is a foreign key to asp_net_Users as I am supporting user accounts)

Basically what I need to do is create a page where I as an Administrator can log onto and enter just the text for the field Description. Then once I upload this I wish all users to visit the site and view this Description on a page however with it also listing the Administrator who wrote it along with the Date. I wish both of these fields to be added automatically (UserId to display the User Name and the Date to display the date and time with which the Description was added - However these need to be editable by the Administrator if he/she wishes to change them).

Can anyone point me in the right direction on the steps needed to create this scenario?

Thanks for any help

Daniel

I'm a bit confused on how you plan on getting the UserID? Is this actually coming from the membership provider in asp.net or are you storing it somewhere else? As far as the Date field you can set the default value of that field on the SQL side to getDate(). This will populate the field with the timestamp whenever you enter in a row into the database. Even though you set a default value you can always pass in your own date/time to your UPDATE statement if you want if you need to edit it.

|||

wtroom:
I'm a bit confused on how you plan on getting the UserID? Is this actually coming from the membership provider in asp.net or are you storing it somewhere else? As far as the Date field you can set the default value of that field on the SQL side to getDate(). This will populate the field with the timestamp whenever you enter in a row into the database. Even though you set a default value you can always pass in your own date/time to your UPDATE statement if you want if you need to edit it.

To be fully accurate, getDate() returns a datetime datatype value that is set to the current date and time.

It's dangerous to mix in the word "timestamp" into such a discussion because timestamp is also a sql server datatype. A timestamp datatype does NOT contain a date or a time and so cannot be used for this purpose.

|||

Hi,

Thanks for your help. The UserId is coming from the Membership provider in asp.net.

I'm relatively new to ASP.NET and am struggling to implement the getDate() on the SQL side. I have created the SQL Datasource to return the table and have then tried to add a WHERE statement.

I assume that the column needs to Date and the Operator = however I am unsure on the control and parameter properties. I have played around and tried Control and QueryString for the Control property and then added getDate() to the Parameter Property Default Value however this just breaks the Details view which is attached to the SQL Datasource.

Any more help would be greatly appreciated.

Thanks

Daniel

|||thanks for your help but I managed to fix this problem.

for the username I created a label with the Id UserIdValue and deleted the text and turned visible to false.

I then added code for the label onload

 Protected Sub UserIdValue_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles UserIdValue.Load UserIdValue.Text = Membership.GetUser().UserName.ToString() End Sub

This made the label display my username when logged on.

The next part for the detailsview code for iteminserting I added

Protected Sub DetailsView1_ItemInserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewInsertEventArgs) Handles DetailsView1.ItemInserting
e.Values("Writtenby") = Membership.GetUser().UserName
e.Values("Date") = DateTime.Now
End Sub

I then deleted Writtenby and Date fields from the details view.

Now when I insert and enter only title and description the Writtenby and Date fields are automatically updated.

Daniel

Automatic Date Field

Hi, I am in the process of building a table and would
like one of the rows to contain a date time field that
is automatically populated with date/time once the
submit button is hit. I can do this in access with
selecting Data Type = Date/Time and default value
= Date()
SQL ver 2K sp3.
TIA for any pointers
JohnUse GETDATE() OR CURRENT_TIMESTAMP
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Johnny" <use@.stamp.co.uk> schrieb im Newsbeitrag
news:429c1d1f$1_1@.mk-nntp-2.news.uk.tiscali.com...
> Hi, I am in the process of building a table and would
> like one of the rows to contain a date time field that
> is automatically populated with date/time once the
> submit button is hit. I can do this in access with
> selecting Data Type = Date/Time and default value
> = Date()
> SQL ver 2K sp3.
> TIA for any pointers
> John
>|||"Johnny" <use@.stamp.co.uk> wrote in message
news:429c1d1f$1_1@.mk-nntp-2.news.uk.tiscali.com...
> Hi, I am in the process of building a table and would
> like one of the rows to contain a date time field that
> is automatically populated with date/time once the
> submit button is hit. I can do this in access with
> selecting Data Type = Date/Time and default value
> = Date()
> SQL ver 2K sp3.
> TIA for any pointers
> John
Thanks Jens, Unfortunatley that makes the whole column into the same date.
John

Saturday, February 25, 2012

Automate "Week Ending" Date

How do i automate, by stored procedure, generating the "week ending" date using a field data.

------------------

CREATE TABLE [dbo].[t_Work_Hours] (
[WorkHoursID_PK] [int] IDENTITY (1, 1) NOT NULL ,
[PeopleID_FK] [int] NOT NULL ,
[JobID_FK] [int] NULL ,
[StartTime] [datetime] NULL ,
[EndTime] [datetime] NULL ,
[Title] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WeekEnding] [datetime] NULL ,
CONSTRAINT [PK_t_Work_Hours] PRIMARY KEY CLUSTERED
(
[WorkHoursID_PK]
) ON [PRIMARY] ,
CONSTRAINT [FK_t_Work_Hours_t_Work_People] FOREIGN KEY
(
[PeopleID_FK]
) REFERENCES [dbo].[t_Work_People] (
[PeopleID_PK]
)
) ON [PRIMARY]
GO
-----------------

What i have tried has'nt even been close to a solution.
UPDATE dbo.t_Work_Hours
SET WeekEnding = DATETIME ( ? , StartTime) . I have no idea where to go!

--------------------
WorkHoursID_PK, PeopleID_FK, JobID_FK, StartTime, EndTime, Title, WeekEnding
7, 40, 3, 11/1/2005 6:00:00 AM, 11/1/2005 4:30:00 PM, J, (NULL)
8, 43, 3, 10/31/2005 6:00:00 AM, 10/31/2005 4:30:00 PM, F, 11/6/2005 11:59:00 PM
9, 43, 3, 11/1/2005 6:00:00 AM, 11/1/2005 4:30:00 PM, F, 11/6/2005 11:59:00 PM
------------------If you insist on performing an UPDATE on the field, then some variation of the statement below should suffice:

...SET WeekEnding =dateadd(day, 8-datepart(dw, StartTime), StartTime)...

I would make WeekEnd a computed column like this:

alter table add Weekend as dateadd(day, 8-datepart(dw, StartTime), StartTime)|||Should be in here somewhere

http://weblogs.sqlteam.com/brettk/archive/2005/06/02/5528.aspx|||thanks a bunch

automagical calculation of a field value

Hi All,
I have a table "AggregatedSales" which has a column called
"AggregatedSales.YearSum".
This column should contain a sum which can be derived directly from another
table called "Sales" by summing up all values of "Sales.Price" WHERE
"Sales.Year" = 2005.
Is it possible to have MS SQL Server automatically calculate the value for
"AggregatedSales.YearSum" when a new data record is inserted? I would like
to add a formula to the column "AggregatedSales.YearSum" that calculates the
value for this field depending on a SELECT-Statement with a SUM() - is that
or such like possible?
Any hints are highly appreciated!
Cheers, JanHi
You will need to do this though a trigger on the table where the insert gets
done.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jan Rsner" <jan.roesner@.web.de> wrote in message
news:egvP8h45FHA.2984@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have a table "AggregatedSales" which has a column called
> "AggregatedSales.YearSum".
> This column should contain a sum which can be derived directly from
> another
> table called "Sales" by summing up all values of "Sales.Price" WHERE
> "Sales.Year" = 2005.
> Is it possible to have MS SQL Server automatically calculate the value for
> "AggregatedSales.YearSum" when a new data record is inserted? I would like
> to add a formula to the column "AggregatedSales.YearSum" that calculates
> the
> value for this field depending on a SELECT-Statement with a SUM() - is
> that
> or such like possible?
> Any hints are highly appreciated!
> Cheers, Jan
>|||Hi Jan
You may possibly want to consider using a view for AggregatedSales!
John
"Jan R?sner" wrote:

> Hi All,
> I have a table "AggregatedSales" which has a column called
> "AggregatedSales.YearSum".
> This column should contain a sum which can be derived directly from anothe
r
> table called "Sales" by summing up all values of "Sales.Price" WHERE
> "Sales.Year" = 2005.
> Is it possible to have MS SQL Server automatically calculate the value for
> "AggregatedSales.YearSum" when a new data record is inserted? I would like
> to add a formula to the column "AggregatedSales.YearSum" that calculates t
he
> value for this field depending on a SELECT-Statement with a SUM() - is tha
t
> or such like possible?
> Any hints are highly appreciated!
> Cheers, Jan
>
>|||"Jan Rsner" <jan.roesner@.web.de> wrote in message
news:egvP8h45FHA.2984@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have a table "AggregatedSales" which has a column called
> "AggregatedSales.YearSum".
> This column should contain a sum which can be derived directly from
> another
> table called "Sales" by summing up all values of "Sales.Price" WHERE
> "Sales.Year" = 2005.
> Is it possible to have MS SQL Server automatically calculate the value for
> "AggregatedSales.YearSum" when a new data record is inserted? I would like
> to add a formula to the column "AggregatedSales.YearSum" that calculates
> the
> value for this field depending on a SELECT-Statement with a SUM() - is
> that
> or such like possible?
> Any hints are highly appreciated!
> Cheers, Jan
>
Create a view rather than a summary table. You can consider making it an
indexed view. You should avoid storing calculated results whenever possible.
David Portas
SQL Server MVP
--

Auto-incremented fields error

I am trying to insert into a SQL Server table from an Oracle database. This table has an auto-incremented field, and when I try to insert into this table I get the following error:

Code Snippet

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL
into column '<column_name>', table '<my_table>'; column does not allow nulls.
UPDATE fails.[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has
been terminated. (SQL State: 23000; SQL Code: 515)

When I turn off all the triggers in the database, I don't get this error. But that is just a test environment, the production environment will need to have those triggers activated.

Any ideas on what is going on here?

Thanks.

Have a look at whether the following KB article (PRB: Guarantee @.@.IDENTITY Value on a Per Table Basis) is relevant to your case.|||

The most common cause of this issue is partial insertion into a table with non-null columns. If you have a table with (not null) columns, you must specify a value for that column on insert or it will generate this error. Make sure that either your inserts always apply values to these columns or the columns are nullable. Autoincrementing columns and columns with default values should not cause this issue, so you can ignore those as well.

Hope that helps,

John

autoincremental field

How can we give serial numbers to the records in a tableUse RowNumber() function. Check
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_3quq.asp?frame=true
for details.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"vinesh" <vineshk@.visitomega.com> wrote in message
news:%23sgjfUJcEHA.2520@.TK2MSFTNGP12.phx.gbl...
> How can we give serial numbers to the records in a table
>

Auto-increment PK - Use MS or Grow your own ?

SQL 2000

I thought I would throw this out there for some feedback from others.

I'd like to know if you feel using MS auto-increment field is a good
solution these days or should one grow their own ?

Thanks,

Me.Definitely use SQL Servers to auto-increment the primary key field.
Then there is no chance of duplicates.|||I can give you a definite Maybe.

I've used both depending on:

* Do you care that numbers may be missing in the sequence if insert
transactions get rolled back?
* Are you making the Primary Key the table's clustered index?
* Do you need to populate the value of that key into other tables within
the same procedure?

<csomberg@.dwr.com> wrote in message
news:1109115558.223984.69950@.z14g2000cwz.googlegro ups.com...
> SQL 2000
> I thought I would throw this out there for some feedback from others.
> I'd like to know if you feel using MS auto-increment field is a good
> solution these days or should one grow their own ?
> Thanks,
> Me.|||This question is bound to start a few of the regulars off on one of
their favorite arguements. Still to add my tuppence worth, I'd say it's
OK to use it under some circumstances. When and where depends entirely
on the solution you are providing and the data you are working with.

Surrogate keys in my opinion are valid and often much more simple than
a primary key of several columns. Having said that, you do get
instances with 'gaps' in the number sequence so this may not be
something you want. You could go down the route of generating a
surrogate key yourself (your original question), but I would hesitate
if IDENTITY offers you the same advantages as you may introduce more
problems depending on your approach. If possible and where logical, use
a properly defined primary key, but use your head and decide what is
most appropriate.

I know full well that some of the regulars will not agree with me and
that some will. However, it is my opinion and they are entitled to
theirs. I would suggest looking up 'Occams Razor' for anyone who
disagrees ( http://pespmc1.vub.ac.be/ASC/OCCAM'_RAZOR.html )

Going back to your question, if you 'grow your own' then this seems
reasonable if you are doing something that using IDENTITY doesn't allow
you. If it's the same, then why bother ?

Ryan

csomberg@.dwr.com wrote:
> SQL 2000
> I thought I would throw this out there for some feedback from others.
> I'd like to know if you feel using MS auto-increment field is a good
> solution these days or should one grow their own ?
> Thanks,
> Me.|||If you want an artificial key then use the feature provided - it's the
most efficient method. The harder question is, do you really want an
artificial key?

--
David Portas
SQL Server MVP
--|||(csomberg@.dwr.com) writes:
> I thought I would throw this out there for some feedback from others.
> I'd like to know if you feel using MS auto-increment field is a good
> solution these days or should one grow their own ?

Depends. If you need consecutive numbers, forget about IDENTITY. If you
want high scalability and don't want to have a hot spot on the current
key value, use IDENTITY.

Another situation where IDENTITY is difficult is when you insert many rows
in one table, and then need to know the values for inserts into a child
table.

If none of this applies, it's a toss-up. IDENTITY is somewhat simpler to
use, but there are some gotchas in odd situations. Rolling your own
is simple as well.

A general remark is that whatever method you use, don't use it for
every table. For "top" concepts like customers, orders, products it
may be inevitable. But for derived concepts that refers to other
concepts, there is rarely any need for artificial keys.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On 23 Feb 2005 01:16:27 -0800, "Ryan" <ryanofford@.hotmail.com> wrote:

>This question is bound to start a few of the regulars off on one of
>their favorite arguements. Still to add my tuppence worth, I'd say it's

Don't you mean "tupple's worth"? <g>|||On 22 Feb 2005 15:39:18 -0800, csomberg@.dwr.com wrote:

>SQL 2000
>I thought I would throw this out there for some feedback from others.
>I'd like to know if you feel using MS auto-increment field is a good
>solution these days or should one grow their own ?
>Thanks,
>Me.

I often use a mix.

As has been pointed out here, if you need to ensure there no gaps, IDENTITY is
not your friend. To me, however, the kind of numbering that needs no gaps
should be a logical key, not a physical/surrogate key, so that's no issue.

Where I have found IDENTITY to be limiting are as follows.

1. I want to be able to merge data sets from 2 or more separate databases
without using a proprietary replication system. In this case, I like to use
some kind of variation on the GUID. Using the global key as the primary key
keeps merge processes simpler than if the global key is used in addition to a
local primary key

2. I need to generate sequences of master-detail sets in stored procedures.

In case #2, the problem is that we want to try to use set operations, not
cursors, and there's no good way to figure out the IDs of the master records
created in one query, so you can use them to create matching details in a
subsequent query. On the other hand, if you have a shared counter, you obtain
a count of the master records to be added, get the current counter value, and
update the counter, adding the master-count to its value. Reading and
updating the counter can be in its own short transaction to reduce blocking
overhead, since we should not care if we add a gap, but don't end up adding
the records with those keys.|||"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
news:1f4q11d4hnd9sa440mst33f4chnis0ffma@.4ax.com...
> On 23 Feb 2005 01:16:27 -0800, "Ryan" <ryanofford@.hotmail.com> wrote:
>>This question is bound to start a few of the regulars off on one of
>>their favorite arguements. Still to add my tuppence worth, I'd say it's
> Don't you mean "tupple's worth"? <g
From the definition of the DIF file format:
It uses the terms vector and TUPLE.
You may generally interpret vector as column and tuple as row.

From the Mary Poppins Sound Track:
With TUPPENCE for paper and strings
You can have your own set of wings
With your feet on the ground
You're a bird in a flight
With your fist holding tight
To the string of your kite

I hope I've cleared that up,

Oh my ... It seems I'm out of Pinot.|||On Thu, 24 Feb 2005 05:02:55 GMT, "David Rawheiser" <rawhide58@.hotmail.com>
wrote:

>"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
>news:1f4q11d4hnd9sa440mst33f4chnis0ffma@.4ax.com...
>> On 23 Feb 2005 01:16:27 -0800, "Ryan" <ryanofford@.hotmail.com> wrote:
>>
>>>This question is bound to start a few of the regulars off on one of
>>>their favorite arguements. Still to add my tuppence worth, I'd say it's
>>
>> Don't you mean "tupple's worth"? <g>
>From the definition of the DIF file format:
> It uses the terms vector and TUPLE.
> You may generally interpret vector as column and tuple as row.
>From the Mary Poppins Sound Track:
> With TUPPENCE for paper and strings
> You can have your own set of wings
> With your feet on the ground
> You're a bird in a flight
> With your fist holding tight
> To the string of your kite
>I hope I've cleared that up,
>Oh my ... It seems I'm out of Pinot.

LOL|||Oh no ! I'm going to be humming that all day now ! :-)

> From the Mary Poppins Sound Track:
> With TUPPENCE for paper and strings
> You can have your own set of wings
> With your feet on the ground
> You're a bird in a flight
> With your fist holding tight
> To the string of your kite|||>> I'd like to know if you feel using MS auto-increment field is a good
solution these days or should one grow their own ? <<

What did you want to use it for?

It is fine for adding a reference number to a cursor, which is a
sequential file structure. But you would never use it inside the
schema for anything. That would that your data model is all screwed up
and has the PHYSICAL state of the machine mixed with the LOGICAL data
model.

Newbies often use IDENTITY and the like to substitute for the pointer
chains they had in IDMS, IMS, TOTAL and other pre-RDBMS databases.

if they are really screwed up, they use them for keys and do not have
natural keys. There is no way to verifiy or validate the data and the
schema loses data integrity.

If I told you that the best key is the 17 digit Hebrew number which God
assigned to all things in creation, you would think I was nuts. There
is no magic universal key; you actually have to do some work when you
design a schema.

We have a lot of problems with terminology on this one, so let me get
that out of the way.

There is no such thing as a "universal, one-size-fits-all" key. Just
as no two sets of entities are the same, the attributes that make them
unique have to be found in the reality of the data. Here is my
classification of types of keys:

1) A natural key is a subset of attributes which occur in a table and
act as a unique identifier. They are seen by the user. You can go to
the external reality and verify them. you would also like to have some
validation rule. Example: UPC codes on consumer goods (read the
package barcode) and validate them with a check digit or a
manufacturer's website, geographical co-ordinates (get a GPS).

2) An artificial key is an extra attribute added to the table which is
seen by the user. It does not exist in the external reality, but can
be verified for syntax or check digits inside itself.

Example: the open codes in the UPC scheme which a user can assign to
his own stuff. The check digits still work, but you have to verify
them inside your own enterprise.

If you have to construct a key yourself, it takes time to deisgn them,
to invetn a validation rule, etc.

3) An "exposed physical locator" is not based on attributes in the data
model and is exposed to user. There is no way to predict it or verify
it. The system obtains a value thru some physical process in the
storage hardware totally unrelated to the logical data model. Example:
IDENTITY columns, other proprietary, non-relaitonal auto-numbering
devices.

Technically, these are not really keys at all, sinc they are attributes
of the PHYSICAL storage and are not even part of the LOGICAL data
model. But they are handy for lazry, non-RDBMS programmers who don't
want to research or think! This is the worst way to program in SQL.

4) A surrogate key is system generated to replace the actual key behind
the covers where the user never sees it. It is based on attributes in
the table. Example: Teradata hashing algorithms, pointer chains.

The fact that you can never see it or use it for DELETE and UPDATE or
create it for INSERT is vital. When users can get to them, they will
screw up the data integrity by getting the real keys and these physical
locators out of synch. The system must maintain them.

** Notice that people get "exposed physical locator" and surrogate
mixed up; they are totally different concepts. **

An appeal to authority, with a quote from Dr. Codd: "..Database users
may cause the system to generate or delete a surrogate, but they have
no control over its value, nor is its value ever displayed to them
..."(Dr. Codd in ACM TODS, pp 409-410) and Codd, E. (1979), Extending
the database relational model to capture more meaning. ACM
Transactions on Database Systems, 4(4). pp. 397-434.

This means that a surrogate ought to act like an index; created by the
user, managed by the system and NEVER seen by a user. That means never
used in queries, DRI or anything else that a user does.

Codd also wrote the following:

"There are three difficulties in employing user-controlled keys as
permanent surrogates for entities.

(1) The actual values of user-controlled keys are determined by users
and must therefore be subject to change by them (e.g. if two companies
merge, the two employee databases might be combined with the result
that some or all of the serial numbers might be changed.).

(2) Two relations may have user-controlled keys defined on distinct
domains (e.g. one uses social security, while the other uses employee
serial numbers) and yet the entities denoted are the same.

(3) It may be necessary to carry information about an entity either
before it has been assigned a user-controlled key value or after it has
ceased to have one (e.g. and applicant for a job and a retiree).

These difficulties have the important consequence that an equi-join on
common key values may not yield the same result as a join on common
entities. A solution - proposed in part [4] and more fully in [14] -
is to introduce entity domains which contain system-assigned
surrogates. Database users may cause the system to generate or delete
a surrogate, but they have no control over its value, nor is its value
ever displayed to them...." (Codd in ACM TODS, pp 409-410).

References

Codd, E. (1979), Extending the database relational model to capture
more meaning. ACM Transactions on Database Systems, 4(4). pp. 397-434

The steps for finding a key are

1) Look for an industry standard and the trusted source that maintains
it.

2) Look for a natural key in the attributes. Example: (longitude,
latitude) makes a good key for a geographical location.

3) If you must design a new identifier, plan it carefully -- especially
if people will see and use it. You have to be able to validate it in
application programs, so you need a regular expression, other syntax
rule and/or check digits. You have to be able to be verify in the
reality of the model or with a trusted source.

Auto-increment key id field help

I have a key field ID (int) for a table. How can I auto incremnt the key so that when I insert a new record I can get a new key. I should not have to know the next available number in my table. What settings do I use for the field.

Thanks in advance
MoonWa

Check out the IDENTITY property.

Auto-Increment

I'm creating a new SQL 2005 Express database and want to have a Customers
table. How do I auto-increment the CustomerID field? Programming in VB.Net
2005.
I'm new to SQL, so go easy on me please ;-)
TIA, Burt
===============================
There's nothing so permanent as
a temporary solution - Me.
===============================Check out IDENTITY in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Burtamus" <burtamus2003@.REMOVETHISyahoo.com> wrote in message
news:efRoosvYGHA.3392@.TK2MSFTNGP03.phx.gbl...
I'm creating a new SQL 2005 Express database and want to have a Customers
table. How do I auto-increment the CustomerID field? Programming in VB.Net
2005.
I'm new to SQL, so go easy on me please ;-)
TIA, Burt
===============================
There's nothing so permanent as
a temporary solution - Me.
===============================|||>> How do I auto-increment the CustomerID field [sic]? Programming in VB.Net2005.
<<
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS.
What you want is a relational key that you can verify and validate.
Auto-incrementing has to do with the internal state of the hardware and
not the data model. This is why there are industry standard codes, why
websites use email addreses, etc.|||> Auto-incrementing has to do with the internal state of the hardware and
> not the data model.
And what about all that Date and Codd say on SURROGATE KEYS then?
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1145391251.621343.288390@.u72g2000cwu.googlegroups.com...
> Let's get back to the basics of an RDBMS. Rows are not records; fields
> are not columns; tables are not files; there is no sequential access or
> ordering in an RDBMS.
> What you want is a relational key that you can verify and validate.
> Auto-incrementing has to do with the internal state of the hardware and
> not the data model. This is why there are industry standard codes, why
> websites use email addreses, etc.
>|||>> And what about all that Date and Codd say on SURROGATE KEYS then? <<
Codd defined them as created by the systrem, and NEVER exposed to the
user. Think of indexes or hashing as the same kind of creature. I
assume that Date feels the same way, but he seldom gets even that close
to implementation considerations.|||> Codd defined them as created by the systrem, and NEVER exposed to the
> user.
You DO NOT need to expose a SURROGATE KEY to a USER!!!

> Think of indexes or hashing as the same kind of creature. I
> assume that Date feels the same way, but he seldom gets even that close
> to implementation considerations.
Thats your interpretation and not anybody elses. Its the view point of a
purist without regard for practical application of the logical model.
The use of the IDENTITY property as a SURROGATE KEY is fine so long as you
consider the implementation of your logical model specifically if the
database is in a distributed environment.
The use of surrogates with an application (note: the database is just one
component of your application) helps us get round many problems such as a)
performance, b) where the NATURAL KEY (note, there is no such thing as a
relational key) is composite and c) where the NATURAL KEY may or is prone to
changing which would cause dramatic concurrency and consistency problems.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1145454973.944139.253670@.e56g2000cwe.googlegroups.com...
> Codd defined them as created by the systrem, and NEVER exposed to the
> user. Think of indexes or hashing as the same kind of creature. I
> assume that Date feels the same way, but he seldom gets even that close
> to implementation considerations.
>|||--CELKO-- wrote:
> Codd defined them as created by the systrem, and NEVER exposed to the
> user. Think of indexes or hashing as the same kind of creature. I
> assume that Date feels the same way, but he seldom gets even that close
> to implementation considerations.
I think you assume wrong. Date agrees with most of the rest of us, that
a surrogate key (like all keys) is part of the logical model. He says
so explicitly in Introduction to Database Systems and differentiates
them from tuple IDs, which are indeed an implementation feature.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Auto-Increasement field?

Hi!

I'm using Microsoft SQL Server Management Studio to design a table with two fields:

id (int)

file (text)

I set 'id' to be primary. I try to add a row to this table but it asks me for a custom value for 'id'. I want it simply to auto-assign a uniqe value for it. How to do this please?

In the table designer, set the Identity Specification to Is_Identity = Yes.

Also, I recommend NOT using [ID] as the column name. A good standard is to use the TableName and ID, so a table named MyTable would have it's IDENTITY column named MyTableID.

Friday, February 24, 2012

Autogenerating Numbers for a primary key field, "studyId," in a tablebut with a few

I have a question on autogenerating numbers for a primary key field, "studyID," in a table—but with a few twists.

We want studyID to be automatically generated as a 5-digit number. Additionally, we have two study sites and would like the studyIDs pertaining to the first site to begin with a 1 and StudyIDs associated with our second site to start with a 2. When we begin entering data, we will enter either a 1 or 2 in a field called, "Site." Upon entering that 1 or 2, we would like at that moment for Access to instantly autogenerate the appropriate studyID for that site and put it in the "StudyID" field. We want the very first number generated for each site to end in a 1 (10001 and 20001).

Here’s the range of values we want our StudyIDs to be (this is to be our validation rule as well):

10001-19999 for Site 1

20001-29999 for Site 2

Your suggestions are VERY VERY WELCOME! THANKS!

If all sites were in seprate databases or at least tables it would be easy just set "identity increment" =1 and "identity seed" = [side prefix]0001 during table creation, but you need maintain all of this in one table so probably trigger is the only solution.

Tomek

|||

This is the fundamental problem with autoincrementing pk fields. You can't really do this in one table. You could create 2 tables and use the post above, then join them into 1 table for output; but, realistically you can't accomplish this goal in 1 table with 1 autogenerated number with arbitrary insert order and more than one logical grouping of keyspaces.

It is generally suggested that if you can possibly avoid it, you should not use autoincrementing pk fields -- but rather use primary keys or clustered keys based on the content of the data itself if you can guarantee uniqueness. If you cannot, it may be advantageous in the long run to avoid duplicate rows by using a count in your table.

e.g. First Last

John Gordon

becomes

First Last Count

John Gordon 2

In the short run, you could split your table into two and join them to report, but in the long run, you may want to consider the limitations this design imposes on the index space of your data and the possibility for growth.

Hope that helps,

John

Tuesday, March 27, 2012

automaticly create a record's field

Automaticaly popluating Current Date in a Db Field

Automatically update datetime field in a database table.

automatically update another field based on other parts of the record

Sunday, March 25, 2012

Automatically increasing field definition by SQL

Thursday, March 22, 2012

automatical calculation of a field value

automatical calculation of a field value

automatical calculation of a field value

Automatic updating of datetime field

Sunday, March 11, 2012

Automatic Field Entry

Automatic Date Field

Saturday, February 25, 2012

Automate "Week Ending" Date

automagical calculation of a field value

Auto-incremented fields error

autoincremental field

Auto-increment PK - Use MS or Grow your own ?

Auto-increment key id field help

Auto-Increment

Auto-Increasement field?

Friday, February 24, 2012

Autogenerating Numbers for a primary key field, "studyId," in a tablebut with a few

Sql Auto grow

Blog Archive

About Me