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 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)
...
)

AutoGenerate No. in SQL Server 2000

Hi
I would like to create an Autogenerate function which has to do the following

autogenerate field type is varchar(10). in that first 2 characters are purely character string. remaining will be numbers

i'll pass the following parameters into the function
1. tablename
2. columnname
3. 2 character string that has to build the first 2 characters
eg: functionname(emp, empid, 'EM')

Here the function has to execute and return the generated no.
eg: EM1 - IF RECORDS NOT AVAIL IN THE TABLE
EM5 - IF ALREADY RECORDS ARE AVAIL &THE MAX RECORD NO IS EM4.

In this functioin i've to pass any tablename and corresponding field with the 2 character build string... Already i tried. Few problems are there in passing the tablename as parameter...

Anybody help me in that...

Thanks in advance...Hi,

What were the problems you got?

Madhivanan|||Hi,

I would like to create an Autogenerate Nos. (total length is varchar(10), in that first 2 letters purely characters, remainings are numerical value).
Value has to start from 1. ie., if records not avail then...'PT1', if records avail then... 'PT4', 'PT5'... like that will be there... i'll pass the Tablename, Columnname and buildstring as parameter. The return value will be autogenerated no(varhchar(10)).

Function calling will be
eg: functionname(tablename, columnname, 'PT')

Give me the solution.

Thx...|||Hi,

Inside the function write something like this

Declare @.AutoGen varchar(10)

if (select count(*) from @.t) =0
select @.AutoGen = 'PT1'
else
select @.AutoGen = 'PT'+convert(varchar(8),max(right(id,len(id)-2))+1) from tableName

Madhivanan|||Hi,
Actually i've got a problem to passing the Tablename as parameter.
Its not working... U try to create a real function in SQL server 2000 with
a table, test it, then... give me reply. then only u can get what'll be the problem...

Tx in advance...|||You will need to use dynamic SQL to do this by building your SQL Statement as a string and then EXECuting it.

That said, do NOT do this. The way you are generating these codes goes against good principles of design, and my crystal ball tells me that this will be an endless source of trouble.

I strongly urge you to reconsider your design.|||Hi...

Anybody got the Autogenerate Nos. Function in SQL Server 2000.

Already i mentioned the problems in the same thread...

Help me

Tx in Advance...|||Easy to do! Without your data I can't test this, but something as simple as:CREATE PROCEDURE fubar
@.pcTable sysname
, @.pcColumn sysname
, @.pcPrefix CHAR(2)
, @.pcResult CHAR(10) OUTPUT
AS

if 'foo' = @.pcTable AND 'bar' = @.pcColumn
SELECT @.pcResult = @.pcResult + Coalesce(Replace(' ', '0'
, Str((SELECT Max(Convert(INT, SubString(foo.bar, 3, 8))
FROM foo) + 1, 8)), '00000001')
else if 'baz' = @.pcTable and 'bat' = @.pcColumn
SELECT @.pcResult = @.pcResult + Coalesce(Replace(' ', '0'
, Str((SELECT Max(Convert(INT, SubString(baz.bat, 3, 8))
FROM baz) + 1, 8)), '00000001')
else SELECT @.pcResult = Repeat('?', 10)

RETURN-PatP|||Sorry...

In ur procedure why u checked the table name. I don't want to check the table name and column names. i'll just pass the table and column names with build string (2 character string). Thats all. The function has to return a no from the specific given table. (if rows are not avail) --> 'PT1'

if rows avail--> 'PT5' (for example already the maximum value is PT4).

It should be for all table... I've no. of tables. For each and every table i can not check it like ur procedure coding... got it...?

Tx... Reconsider it and give the better solution...|||You're trying to do something that SQL doesn't do well because it has other features that work SO much better. I'm sorry you don't like my solution, even though it does exactly what you asked for and relatively efficiently at that.

Maybe someone else has a better answer. Personally, I'd just suggest that you simply use a better solution such as an IDENTITY column that would sidestep the need for this nonsense.

-PatP|||select 'PT'+convert(varchar(8),max(isnull(right(id,len(id )-2),0))+1) from tableName

Hi,

I would like to create an Autogenerate Nos. (total length is varchar(10), in that first 2 letters purely characters, remainings are numerical value).
Value has to start from 1. ie., if records not avail then...'PT1', if records avail then... 'PT4', 'PT5'... like that will be there... i'll pass the Tablename, Columnname and buildstring as parameter. The return value will be autogenerated no(varhchar(10)).

Function calling will be
eg: functionname(tablename, columnname, 'PT')

Give me the solution.

Thx...|||select 'PT'+convert(varchar(8),max(isnull(right(id,len(id )-2),0))+1) from @.tableName

here @.tablename is the parameter. if i give like this its giving an error...
please try to do the function, check it and give me the reply.

Tx in advance...|||Hi...

Anybody is having good solution for my question regarding this autogenerate nos. in SQL server 2000. Question is avail in this same thread...

Good suggestions and Good & Efficient Solutions are welcome

Tx in advance.|||PatP gave you a good & efficient solution. If you don't understand, it is not due to not having the right solution.

Autoexist when queying

Hi all,

I think it's a simple question but i can't seem to get it right:

I have a parent child dimension, with key Object, and an attribute Type. Every Object has a type, consider this example

Object Type
--
Top Model
Child 1 Portfolio
Child 2 PortFolio

When i run this query:

select
time.month.members on 0,
objects.object.members * objects.[type].members on 1
from [Dream2007]

I get this on Axis 0:
Top Model
Top Portfolio
Child 1 Model
Child 1 Portfolio
Child 2 Model
Child 2 Portfolio

How can i het the following result with only the existing combinations?:

Top Model
Child 1 Portfolio
Child 2 PortFolio

I tried non empty, but that doesn't work because they can be null. I also have an attribute relationship between Object and Type.

Any ideas?

TIA,

GJ

Maybe there's some issue with the attribute relationships - you can compare with the Adventure Works Organization parent-child dimension, where each Organization has a Currency Code:

>>

select {} on 0,

[Organization].[Organization].[Organization].Members

* [Organization].[Currency Code].[Currency Code].Members on 1

from [Adventure Works]

-

AdventureWorks Cycle USD
Australia AUD
Canadian Division CAD
Central Division USD
European Operations EUR
France EUR
Germany EUR
North America Operations USD
Northeast Division USD
Northwest Division USD
Pacific Operations AUD
Southeast Division USD
Southwest Division USD
USA Operations USD

>>

|||

I tried to set the Cardinality of the relationship to one, but this did not change it.

Actually, when i looked at it more closely, higher members have their own type, and the types of their children too. So the top object has all the types of all members, the leaf members for the PC hierarchy have only their own type (and the [all] type if i set IsAggretable to true) I only checked the top members at first, so it seemd as if it was the crossproduct of all members from both hierarchies.

Is there any way to disable this behaviour, so a member would only have the type it gets from the dimtabel row, not all its children? Or is this conceptually impossible?

I have a lot of caluculations that need to apply to a specific type of member only. I could scope on Level.Ordinal, but then i would have to keep track of which types of objects exist on which levels.

Regards,

GJ

|||

Going back to the Adventure Works Organization dimension, I think this query illustrates the issue which you raise:

>>

select {} on 0,

[Organization].[Organizations].Members

* [Organization].[Currency Code].[Currency Code].Members on 1

from [Adventure Works]

AdventureWorks Cycle AUD
AdventureWorks Cycle CAD
AdventureWorks Cycle EUR
AdventureWorks Cycle USD
European Operations EUR
France EUR
Germany EUR
North America Operations CAD
North America Operations USD
Canadian Division CAD
USA Operations USD
Central Division USD
Northeast Division USD
Northwest Division USD
Southeast Division USD
Southwest Division USD
Pacific Operations AUD
Australia AUD

>>

Comparing it to the earlier query, each member is located in both the dimension key attribute hierarchy: [Organization].[Organization], and in the parent-child hierarchy: [Organization].[Organizations]. And in the attribute hierachy, each member is only associated with a single currency. Can you give an idea of what scoped calculations you need; and could these be translated to the Adventure Works Organization dimension?

|||

I tried it with the key hierarchy, not the PC, and indead now get the results i expected.

I guess it would be logical for members in a PC hierarchy to have the attributes of it's descendants too for rolling up additive measures, because else there would be nowhere for these aggregates to go, or they wouldn't show up in queries.

The problem we have is we're looking to migrate a forecasting app we built on AS2000 to 2005. we have loads of calculated members like this one that all build upon each other

CREATE MEMBER CURRENTCUBE.[PP].[Calcs200].[WO_component_ML_maandbedrag] AS
iif(Object.CurrentMember.Level Is Object.Levels(7),
Iif(Object.CurrentMember.Properties("ObjectType") = "WO_component",
Iif(Object.CurrentMember.Properties("ObjectLevel") = "8",
iif(Tijd.CurrentMember Is Tijd.Members.Item(Val(Object.Currentmember.Properties("ObjectStartDateOrdinal"))) ,
0 + (0 + Val([Object].Properties("Component_Maandbedrag"))),
0 + (Tijd.Members.Item(Val(Object.Currentmember.Properties("ObjectStartDateOrdinal"))), [PP].[WO_component_ML_maandbedrag] )
),
0),
0),
iif(Object.CurrentMember.Level.Ordinal < 7,
0 + SUM(Filter(Descendants(Object.CurrentMember, Object.levels(7)), Object.CurrentMember.Properties("ObjectLevel") = "8" AND Object.CurrentMember.Properties("ObjectType") = "WO_component"), ([PP].[WO_component_ML_maandbedrag]) ) ,
0 + (Ancestor(Object.CurrentMember, Object.levels(7)), [PP].[WO_component_ML_maandbedrag] )
)
), SOLVE_ORDER= 11 , FORMAT_STRING='#,##.##' ;

Where we use iif(Object.Currentmember.Level or Object.Currentmember.Properties to make sure the calcs get done (only) on the right spot.

So we were pretty excited about Scope, because then we wouldn't have to use Currentmember and Filter all the time, this would eliminate all the extra things we have to do to solve performace.

Now i'm trying to understand how this works exactly, but i'm still having trouble getting my head around 2005. 2000 was alot easier in this respect.

I don't think adventureworks has anything that comes near, but i'll look into it.

Regards, GJ

|||

Unfortunately, I can't quite comprehend all the logic in the calculated member above. But for the problem of filtering members of a parent-child hierarchy with a specific property value, using "Autoexist" behavior - here's an Adventure Works query which returns members of the [Organization].[Organizations] hierarchy whose Currency Code is "CAD" - and it excludes any ancestors, since their own Currency Code isn't "CAD":

>>

select {} on 0,

Generate([Organization].[Organizations].Members,

{([Organization].[Organizations].CurrentMember,

StrToMember("[Organization].[Organization].&["

+ [Organization].[Organizations].Properties("Key")

+ "]"),

[Organization].[Currency Code].[CAD])}) on 1

from [Adventure Works]

Canadian Division Canadian Division CAD

>>

|||

PS: maybe I did learn something at last month's SQL PASS in Seattle - my memory just flashed back to a session where the use of LinkMember() in lieu of StrToMember() was mentioned (blog link below); so here's a cleaner version of the query:

select {} on 0,
Generate([Organization].[Organizations].Members,
{([Organization].[Organizations].CurrentMember,
LinkMember([Organization].[Organizations].CurrentMember,
[Organization].[Organization]),
[Organization].[Currency Code].[CAD])}) on 1
from [Adventure Works]

http://sqljunkies.com/WebLog/mosha/archive/2006/11/16.aspx

>>

SQL PASS in Seattle: What has the UDM Done For You Lately by Dan Bulos

.. 'bad' MDX functions for AS2005. StrToMember is one of them..we agreed that in that particular scenario the script indeed could've been rewritten to use LinkMember..

>>

Auto_Increment?

Hi all.

I'm trying to transfer a mysql file to mssql. Is there a field type of auto increment in mssql.
If not is there anyway to make a field auto increment in the SQL Studio express?

Thanks in advance for help with this!
Ron

In Microsoft SQL, you set the column's "Identity Specification" equal to Yes

Auto Number Data Type?

Is there a data type like Access's auto number that will automatically assign a record a number? I transfered a database from Access 2000 to SQL 2000 and it did not preserve the autonumber datatype.Identity is the SQL Server equivalent to the Access autonumber.|||When I open a table in design view and I change a datatype I do not see Identity in the list.|||Identity is property not a data type.
Chose a INT type for instance, then (in the bottom of the design screen - columns panel) specify if that's an identity column, the seed and increment eventually.

Originally posted by aaronshover
When I open a table in design view and I change a datatype I do not see Identity in the list.|||Thanks, that helped. I got it working now.

Auto increment

How do I do to give a column an integer type and setting it to auto increment
so that I don't have to give this value every time I insert something in the
table?
You might want to read about identity columns on the BOL.
Aramid
On Wed, 6 Apr 2005 02:59:08 -0700, "Joachim"
<Joachim@.discussions.microsoft.com> wrote:

>How do I do to give a column an integer type and setting it to auto increment
>so that I don't have to give this value every time I insert something in the
>table?

Auto increment

How do I do to give a column an integer type and setting it to auto incremen
t
so that I don't have to give this value every time I insert something in the
table?You might want to read about identity columns on the BOL.
Aramid
On Wed, 6 Apr 2005 02:59:08 -0700, "Joachim"
<Joachim@.discussions.microsoft.com> wrote:

>How do I do to give a column an integer type and setting it to auto increme
nt
>so that I don't have to give this value every time I insert something in th
e
>table?

Auto increment

How do I do to give a column an integer type and setting it to auto incremen
t
so that I don't have to give this value every time I insert something in the
table?Hi
See an IDENTITY property
CREATE TABLE #Test
(
col1 INT NOT NULL IDENTITY(1,1),
col2 CHAR(1)
)
INSERT INTO #Test (col2) VALUES ('A')
"Joachim" <Joachim@.discussions.microsoft.com> wrote in message
news:319993D2-2CE4-47BE-A8C7-BB2064E6EFB0@.microsoft.com...
> How do I do to give a column an integer type and setting it to auto
increment
> so that I don't have to give this value every time I insert something in
the
> table?

Auto increment

How do I do to give a column an integer type and setting it to auto increment
so that I don't have to give this value every time I insert something in the
table?You might want to read about identity columns on the BOL.
Aramid
On Wed, 6 Apr 2005 02:59:08 -0700, "Joachim"
<Joachim@.discussions.microsoft.com> wrote:
>How do I do to give a column an integer type and setting it to auto increment
>so that I don't have to give this value every time I insert something in the
>table?

auto identity for each Type

Hello,

I am working on an accounting system using VB.NET and sql server 2005 as a database. the application should be used by multiple users.
i have a the following structure:
Voucher: ID (primary), Date,TypeID, ReferenceCode, ....
Type: ID, Code, Name. (the user can add new type anytime!)
(Ex: PV- payment voucher, JV - Journal Voucher ,...)

When adding a voucher the user will choose a type, according to this type (for each year) a counter will be increminted.
for example: PV1, PV2...PV233,... the other type will have its separate counter JV1, JV2 ,...JV4569,..
I am using the sqlTransaction cause i am doing other operations that should be transactional with the insertion of the Voucher.

The question is :
What is the best solution to generate a counter for each type?(With code sample)

Thanks.do you really need to have the 'PV' and 'JV' before each value? if you could use ints, then you could use identity columns. That's the standard way of doing this.

You can always tack on a JV or PV in the front end if that's the way your boss wants it to look in a report or something.

from BOL:

IDENTITY

Indicates that the new column is an identity column. When a new row is added to the table, Microsoft® SQL Server™ provides a unique, incremental value for the column. Identity columns are commonly used in conjunction with PRIMARY KEY constraints to serve as the unique row identifier for the table. The IDENTITY property can be assigned to tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0) columns. Only one identity column can be created per table. Bound defaults and DEFAULT constraints cannot be used with an identity column. You must specify both the seed and increment or neither. If neither is specified, the default is (1,1).|||if you were using mysql, this functionality (starting a new auto_increment within each type group) is built in

it's impossible to do this with an IDENTITY column

you will have to generate your own numbers, and i would recommend very strongly against it|||the counter in the question is the ReferenceCode in the Voucher table
Voucher: ID (primary), Date,TypeID, ReferenceCode.
so for each added voucher and according to the TypeID a the reference code will be generated. let say the last counter for the PV type is 230 so the referenceCode will be PV231. if the Type is JV and the last counter is 566 then the ReferenceCode will be JV567 and so on.
We don't have to forget that we are working in a multi user enviroment, and the Reference Code should be unique .|||put the JV or PV in another field and concatenate it in the front end. smart numbers are stupid and loved by the accounting types. this kind of things slow down joins and causes other kinds of pain. i have not seen smart numbers in a project for five years and that was a legacy foxpro app.

Auto generate String

I have an ID Field in a table ContactInformation. This ID field is char(4)
type. And for this I want to auto generate strings starting from '0001'
onwards:
0001
0002
9998
9999
A001
A002
Z999
Thanks.You can't auto generate strings like an Identity() does. You have to create
a stored procedure that you call to get the next value and use that in the
Insert.
Andrew J. Kelly SQL MVP
"SQL Newbie" <SQL Newbie@.discussions.microsoft.com> wrote in message
news:97A8948F-934A-4DE9-A3F4-125C1847C3AD@.microsoft.com...
>I have an ID Field in a table ContactInformation. This ID field is char(4)
> type. And for this I want to auto generate strings starting from '0001'
> onwards:
> 0001
> 0002
> 9998
> 9999
> A001
> A002
> Z999
> Thanks.|||This seems like a very loopy and bizarre numbering scheme.
If you could allow A000 instead of jumping from 9999 to A001, there might be
an easier way to do this (if you also allow a peripheral IDENTITY column),
e.g.
create table dbo.foo
(
int_id int identity(1,1),
id as convert(char(4),
CASE WHEN int_id < 10000 THEN RIGHT('0000'+RTRIM(int_id),4)
ELSE CHAR(CONVERT(INT,LEFT(RTRIM(int_id),2))+
54) +
RIGHT('000'+RTRIM(int_id),3)
END)
)
SET NOCOUNT ON;
WHILE 1=1
BEGIN
BEGIN TRAN
INSERT foo DEFAULT VALUES
COMMIT
END
Don't forget to stop the above loop after a few minutes! Then run SELECT *
FROM foo and you will see that it closely matches your requirements, until
you hit somewhere around 36,000 rows. If you can't have the extra column
then you could easily use a stored procedure to handle inserts into the
eventual table, by first inserting into this table and then grabbing the row
that matches scope_identity(), and inserting into the "real" table. However
that would serialize inserts, would break on a multi-insert statement (or
BULK INSERT or bcp), and would require you to prevent ad hoc direct inserts
to the table. In addition, I have no idea what you expect to do once you
get past ~36,000 rows, where your CHAR(4) "numbering" scheme breaks. It
will loop around and perform lower case a000 -> z999 and then, at some
point, the upper bound will exceed the capacity of CHAR() and the inserts
will stop working. You can stop it at Z999 (and produce errors thereafter,
at least for the next 36,000 rows) if you apply a unique constraint to the
id column (provided you are in a case insensitive collation).
Have fun. Glad I'm not directly involved, I would probably move on to a
different gig. Your requirements seem to have been drafted by someone who
does not understand how databases work and, in particular, the weaknesses of
rolling your own string generator to create the same kind of meaningless
surrogate key that could easily be handled by identity alone, where the
system justtakes care of itself and you don't have any of these constraints,
extra space requirements and logical headaches...
"SQL Newbie" <SQL Newbie@.discussions.microsoft.com> wrote in message
news:97A8948F-934A-4DE9-A3F4-125C1847C3AD@.microsoft.com...
>I have an ID Field in a table ContactInformation. This ID field is char(4)
> type. And for this I want to auto generate strings starting from '0001'
> onwards:
> 0001
> 0002
> 9998
> 9999
> A001
> A002
> Z999
> Thanks.|||Hi,
I would fully agree with Aaron that you will pretty soon reach your limit. I
believe one thing that you might wanna do is to increase char(4) to a higher
value depending on how many inserts you want. This way you can also avoid an
additional over head of including alphabets in your ID field.
Its is always a good idea to conduct a capacity planning (as to how many
such id you would need) well in advance before coding such things.
I believe you need this particular format (i.e. to say that ID should have
to have these many characters which is not a very uncommon situation.) and
padding the left side characters with zero is often time used. But once agai
n
these do have limits to the max number they can avhieve.
Hope this helps.
"SQL Newbie" wrote:

> I have an ID Field in a table ContactInformation. This ID field is char(4)
> type. And for this I want to auto generate strings starting from '0001'
> onwards:
> 0001
> 0002
> 9998
> 9999
> A001
> A002
> Z999
> Thanks.|||Why? Do these values have a special meaning in your data model?
ML|||> I believe you need this particular format (i.e. to say that ID should have
> to have these many characters which is not a very uncommon situation.) and
> padding the left side characters with zero is often time used.
But aside from the weird rollover scheme used here (rolling to A -> Z after
the first 9,999 rows), this could easily be achieved through a view or
stored procedure, without storing this unnecessary padding in the database.
Let's say we could get by with just
00001
00002
...
00578
...
09999
...
99999
Then you could easily use an IDENTITY column and then create a view like:
CREATE VIEW dbo.bar
AS
SELECT RIGHT('00000'+RTRIM(id_column)) FROM some_table|||Thank you very much Aaron.
"Aaron Bertrand [SQL Server MVP]" wrote:

> But aside from the weird rollover scheme used here (rolling to A -> Z afte
r
> the first 9,999 rows), this could easily be achieved through a view or
> stored procedure, without storing this unnecessary padding in the database
.
> Let's say we could get by with just
> 00001
> 00002
> ...
> 00578
> ...
> 09999
> ...
> 99999
> Then you could easily use an IDENTITY column and then create a view like:
> CREATE VIEW dbo.bar
> AS
> SELECT RIGHT('00000'+RTRIM(id_column)) FROM some_table
>
>