Hi All
I am looking for a bit of advice.
I am in the process of creating a database in which it has been decided that
all primary keys are going to varchar(40). Not my decision, but anyway.
When inserting into each table it will be possible to specify a value for
the primary, but if not specified a value should be auto-generated. That
means that the values in the primary key field can be a mixture of both
numbers and letters, but if auto-generated it should just be a number.
What be the best way to make this autogenerated values if no value is being
specified in the insert?
TIA
KlausDepends really, if auto-generated does it just need to be a number? Any old
number, or a specific format and range?
I always recommend putting a surrogate key on the tables and use that as the
foriegn key and inside the application (not for display purposes, but for
use as the value in a listbox for instance), that can be a int column with
the IDENTITY property, not null and have a unique constraint on it.
You could set the value of the primary key to that if not specified, that
would save calculating a new unique number.
Otherwise, you could use an 'instead of' trigger, for example...
Instead of using MAX, you could take the value from a table that holds the
last number used.
create table testtrg (
mycol int not null unique
)
go
insert testtrg ( mycol ) values ( 1 )
go
create trigger trgTestTrg on testtrg instead of insert
as
begin
if @.@.rowcount = 0
return
declare @.nextid int
begin tran
set @.nextid = ( select max( mycol )
from testtrg with (tablockx) )
set @.nextid = isnull( @.nextid, 0 ) + 1
insert testtrg values( @.nextid )
commit tran
end
go
-- Note, inserting 1 but it already exists so should give a key violation,
-- but the instead of trigger code kicks in and gives the next id.
select * from testtrg
insert testtrg ( mycol ) values( 1 )
select * from testtrg
insert testtrg ( mycol ) values( 1 )
select * from testtrg
insert testtrg ( mycol ) values( 1 )
select * from testtrg
go
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Klaus" <Klaus@.discussions.microsoft.com> wrote in message
news:FE28E558-F88F-4A9F-9AAA-40837A9966E9@.microsoft.com...
> Hi All
> I am looking for a bit of advice.
> I am in the process of creating a database in which it has been decided
> that
> all primary keys are going to varchar(40). Not my decision, but anyway.
> When inserting into each table it will be possible to specify a value for
> the primary, but if not specified a value should be auto-generated. That
> means that the values in the primary key field can be a mixture of both
> numbers and letters, but if auto-generated it should just be a number.
> What be the best way to make this autogenerated values if no value is
> being
> specified in the insert?
> TIA
> Klaus
>|||Thanks a lot, Tony. That was very helpfull.
I will create a unique field on each of my tables. The value for this will
be auto-generated using identity. A trigger will then keep an eye on the
inserts. If no value is being specified for the Primary key, the Identity
value will be copied into the varchar(40) primary key field.
-- Klaus
"Tony Rogerson" wrote:
> Depends really, if auto-generated does it just need to be a number? Any ol
d
> number, or a specific format and range?
> I always recommend putting a surrogate key on the tables and use that as t
he
> foriegn key and inside the application (not for display purposes, but for
> use as the value in a listbox for instance), that can be a int column with
> the IDENTITY property, not null and have a unique constraint on it.
> You could set the value of the primary key to that if not specified, that
> would save calculating a new unique number.
> Otherwise, you could use an 'instead of' trigger, for example...
> Instead of using MAX, you could take the value from a table that holds the
> last number used.
> create table testtrg (
> mycol int not null unique
> )
> go
>
> insert testtrg ( mycol ) values ( 1 )
> go
>
> create trigger trgTestTrg on testtrg instead of insert
> as
> begin
> if @.@.rowcount = 0
> return
>
> declare @.nextid int
>
> begin tran
>
> set @.nextid = ( select max( mycol )
> from testtrg with (tablockx) )
>
> set @.nextid = isnull( @.nextid, 0 ) + 1
>
> insert testtrg values( @.nextid )
>
> commit tran
>
> end
> go
>
> -- Note, inserting 1 but it already exists so should give a key violation
,
> -- but the instead of trigger code kicks in and gives the next id.
> select * from testtrg
> insert testtrg ( mycol ) values( 1 )
> select * from testtrg
> insert testtrg ( mycol ) values( 1 )
> select * from testtrg
> insert testtrg ( mycol ) values( 1 )
> select * from testtrg
> go
>
>
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Klaus" <Klaus@.discussions.microsoft.com> wrote in message
> news:FE28E558-F88F-4A9F-9AAA-40837A9966E9@.microsoft.com...
>
>|||The second part of Tony's point should not be lost. You should use that int
key as the FK for relationships with other tables. If you need to show your
client the benefit of using an int instead of a varchar(40). Load up a
couple of tables with some test data. Perform join's using varchar(40) as
the keys and then the same using int as the key. The performance difference
is noticable.
So use an int (or even bigint) PK, put a unique constraint on the
varchar(40) column and for all business logic purposes, the varchar(40) fiel
d
is the "key". But behind the scenes in the database the far more efficient
int is the key.
John Scragg
"Klaus" wrote:
> Thanks a lot, Tony. That was very helpfull.
> I will create a unique field on each of my tables. The value for this will
> be auto-generated using identity. A trigger will then keep an eye on the
> inserts. If no value is being specified for the Primary key, the Identity
> value will be copied into the varchar(40) primary key field.
> -- Klaus
> "Tony Rogerson" wrote:
>|||Also, if you need the data in a varchar(40) field you can use a calculated
column (if they dont need to enter it).
I concurr with Tony & John, if you're doing joins, definately use the INT
field as the joining field, joining on varchar fields gets very slow at
medium to high data volumes.
create table ( id int identity(1,1) primary key , myPK AS cast( ID as
varchar(40)) )
"John Scragg" <JohnScragg@.discussions.microsoft.com> wrote in message
news:EDB86798-0F96-415A-9D8D-733ED2E0CA02@.microsoft.com...
> The second part of Tony's point should not be lost. You should use that
int
> key as the FK for relationships with other tables. If you need to show
your
> client the benefit of using an int instead of a varchar(40). Load up a
> couple of tables with some test data. Perform join's using varchar(40) as
> the keys and then the same using int as the key. The performance
difference
> is noticable.
> So use an int (or even bigint) PK, put a unique constraint on the
> varchar(40) column and for all business logic purposes, the varchar(40)
field
> is the "key". But behind the scenes in the database the far more
efficient
> int is the key.
> John Scragg
> "Klaus" wrote:
>
will
Identity
Any old
as the
for
with
that
the
violation,
decided
anyway.
value for
That
both
number.
is
Showing posts with label varchar. Show all posts
Showing posts with label varchar. Show all posts
Saturday, February 25, 2012
Autoincrement in varchar value
hi Guys,
I've tangled in serious problem,
Is there any way in which we can autoincrement in
varchar value like i've one column in my table called PayCardId that should
be in 9 digit say '900001@.@.@.', now requirement is this to increment in same
format but it should automatically save in Database, for example
900001@.@.@.
.
.
900100@.@.@.
900101@.@.@.
900102@.@.@.
When it completes it's hundred series it should come in
thousand series like 901001@.@.@. and then
901002@.@.@.
901003@.@.@.
so on and so for
I'm sure there should be any way to
increment this, but i'm not able to think it this time, Please help me ASAP
Any help would be appriciated
ThanksHi
I am not sure whether this code will help you. if this is the logic. u can
cast the final value as string and insert into database.
CREATE TABLE #TempTable (CustID VARCHAR(9))
INSERT INTO #TempTable (CustID) VALUES ('900001ABC')
INSERT INTO #TempTable (CustID) VALUES ('900002XYZ')
INSERT INTO #TempTable (CustID) VALUES ('900003ABC')
DECLARE @.intNewValue INT
SELECT @.intNewValue = CAST(LEFT(MAX(CustID),6) AS INT) FROM #TempTable
SELECT @.intNewValue
SET @.intNewValue = @.intNewValue + 1
SELECT @.intNewValue
Thanks,
Ciju
"Manish Sukhija" wrote:
> hi Guys,
> I've tangled in serious problem,
> Is there any way in which we can autoincrement i
n
> varchar value like i've one column in my table called PayCardId that shoul
d
> be in 9 digit say '900001@.@.@.', now requirement is this to increment in sam
e
> format but it should automatically save in Database, for example
> 900001@.@.@.
> .
> .
> 900100@.@.@.
> 900101@.@.@.
> 900102@.@.@.
> When it completes it's hundred series it should come i
n
> thousand series like 901001@.@.@. and then
> 901002@.@.@.
> 901003@.@.@.
> so on and so for
> I'm sure there should be any way to
> increment this, but i'm not able to think it this time, Please help me ASA
P
> Any help would be appriciated
> Thanks
>|||Thanks a lot Ciju, it was realy helpful for me, i've got a good idea from
this code
thanks a lot again for giving quick response,
may god bless you
"Manish Sukhija" wrote:
> hi Guys,
> I've tangled in serious problem,
> Is there any way in which we can autoincrement i
n
> varchar value like i've one column in my table called PayCardId that shoul
d
> be in 9 digit say '900001@.@.@.', now requirement is this to increment in sam
e
> format but it should automatically save in Database, for example
> 900001@.@.@.
> .
> .
> 900100@.@.@.
> 900101@.@.@.
> 900102@.@.@.
> When it completes it's hundred series it should come i
n
> thousand series like 901001@.@.@. and then
> 901002@.@.@.
> 901003@.@.@.
> so on and so for
> I'm sure there should be any way to
> increment this, but i'm not able to think it this time, Please help me ASA
P
> Any help would be appriciated
> Thanks
>|||If you can touch on the table design yet..
then try this.
create TABLE [char_increment] (
[a] AS cast(id_num as varchar) + '@.@.@.',
[id_num] [bigint] IDENTITY (900000, 1) NOT NULL ,
[fname] [varchar] (20)
)
insert into char_increment (fname)
values ('a')
insert into char_increment (fname)
values ('b')
insert into char_increment (fname)
values ('c')
insert into char_increment (fname)
values ('d')
I've tangled in serious problem,
Is there any way in which we can autoincrement in
varchar value like i've one column in my table called PayCardId that should
be in 9 digit say '900001@.@.@.', now requirement is this to increment in same
format but it should automatically save in Database, for example
900001@.@.@.
.
.
900100@.@.@.
900101@.@.@.
900102@.@.@.
When it completes it's hundred series it should come in
thousand series like 901001@.@.@. and then
901002@.@.@.
901003@.@.@.
so on and so for
I'm sure there should be any way to
increment this, but i'm not able to think it this time, Please help me ASAP
Any help would be appriciated
ThanksHi
I am not sure whether this code will help you. if this is the logic. u can
cast the final value as string and insert into database.
CREATE TABLE #TempTable (CustID VARCHAR(9))
INSERT INTO #TempTable (CustID) VALUES ('900001ABC')
INSERT INTO #TempTable (CustID) VALUES ('900002XYZ')
INSERT INTO #TempTable (CustID) VALUES ('900003ABC')
DECLARE @.intNewValue INT
SELECT @.intNewValue = CAST(LEFT(MAX(CustID),6) AS INT) FROM #TempTable
SELECT @.intNewValue
SET @.intNewValue = @.intNewValue + 1
SELECT @.intNewValue
Thanks,
Ciju
"Manish Sukhija" wrote:
> hi Guys,
> I've tangled in serious problem,
> Is there any way in which we can autoincrement i
n
> varchar value like i've one column in my table called PayCardId that shoul
d
> be in 9 digit say '900001@.@.@.', now requirement is this to increment in sam
e
> format but it should automatically save in Database, for example
> 900001@.@.@.
> .
> .
> 900100@.@.@.
> 900101@.@.@.
> 900102@.@.@.
> When it completes it's hundred series it should come i
n
> thousand series like 901001@.@.@. and then
> 901002@.@.@.
> 901003@.@.@.
> so on and so for
> I'm sure there should be any way to
> increment this, but i'm not able to think it this time, Please help me ASA
P
> Any help would be appriciated
> Thanks
>|||Thanks a lot Ciju, it was realy helpful for me, i've got a good idea from
this code
thanks a lot again for giving quick response,
may god bless you
"Manish Sukhija" wrote:
> hi Guys,
> I've tangled in serious problem,
> Is there any way in which we can autoincrement i
n
> varchar value like i've one column in my table called PayCardId that shoul
d
> be in 9 digit say '900001@.@.@.', now requirement is this to increment in sam
e
> format but it should automatically save in Database, for example
> 900001@.@.@.
> .
> .
> 900100@.@.@.
> 900101@.@.@.
> 900102@.@.@.
> When it completes it's hundred series it should come i
n
> thousand series like 901001@.@.@. and then
> 901002@.@.@.
> 901003@.@.@.
> so on and so for
> I'm sure there should be any way to
> increment this, but i'm not able to think it this time, Please help me ASA
P
> Any help would be appriciated
> Thanks
>|||If you can touch on the table design yet..
then try this.
create TABLE [char_increment] (
[a] AS cast(id_num as varchar) + '@.@.@.',
[id_num] [bigint] IDENTITY (900000, 1) NOT NULL ,
[fname] [varchar] (20)
)
insert into char_increment (fname)
values ('a')
insert into char_increment (fname)
values ('b')
insert into char_increment (fname)
values ('c')
insert into char_increment (fname)
values ('d')
Friday, February 24, 2012
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.
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.
Sunday, February 12, 2012
Auto join in SQL Query
I've got the following table :
CREATE TABLE M6_CHECK
(
ID_QUOTE VARCHAR(20),
ID_ITEM VARCHAR(20),
BEGIN_DATE DATETIME,
END_DATE DATETIME
)
This table contains the following data :
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q1','I1','01/01/2001','12/31/2001');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q1','I2','01/01/2001','12/31/2001');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q1','I3','01/01/2001','12/31/2001');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q2','I1','01/01/2000','12/31/2000');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q2','I4','01/01/2000','12/31/2000');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q3','I2','01/01/2000','12/31/2000');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q3','I5','01/01/2000','12/31/2000');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q4','I3','01/01/2000','12/31/2000');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q4','I6','01/01/2000','12/31/2000');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q5','I4','01/01/2001','12/31/2001');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q6','I5','01/12/2000','12/31/2001');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q7','I6','01/31/2001','12/31/2001')
I am trying to find a SQL query which would return the following recordset :
ID_ITEM ID_QUOTE_1 END_DATE_1 ID_QUOTE_2 BEGIN_DATE_2
----
I5 Q3 12/31/2000 Q6 01/12/2000
I6 Q4 12/31/2000 Q7 01/31/2001
Let me explain :
The SQL Query should return every ID_ITEM for which 2 consecutives quotes
don't verify the following rule :
end date previous quote = begin date next quote - 1 day
It looks like I should make an auto join in the query but I couldn't make it
:(
Thanks in advance for your help,
AlexSELECT t1.ID_ITEM,
t1.ID_QUOTE as ID_QUOTE_1,
t1.END_DATE as END_DATE_1,
t2.ID_QUOTE as ID_QUOTE_2,
t1.BEGIN_DATE as BEGIN_DATE_2
FROM M6_CHECK t1
INNER JOIN M6_CHECK t2 ON t1.ID_ITEM=t2.ID_ITEM
AND t1.BEGIN_DATE<t2.BEGIN_DATE
AND DATEADD(day,1,t1.END_DATE)<>t2.BEGIN_DATE
ORDER BY t1.ID_QUOTE,t1.ID_ITEM,t1.BEGIN_DATE|||typo..
t1.BEGIN_DATE as BEGIN_DATE_2
should be
t2.BEGIN_DATE as BEGIN_DATE_2|||This doesn't show up in the test data, but
more correctly I think it should be this
SELECT t1.ID_ITEM,
t1.ID_QUOTE as ID_QUOTE_1,
t1.END_DATE as END_DATE_1,
t2.ID_QUOTE as ID_QUOTE_2,
t2.BEGIN_DATE as BEGIN_DATE_2
FROM M6_CHECK t1
INNER JOIN M6_CHECK t2 ON t1.ID_ITEM=t2.ID_ITEM
AND t1.BEGIN_DATE<t2.BEGIN_DATE
AND DATEADD(day,1,t1.END_DATE)<>t2.BEGIN_DATE
AND t2.BEGIN_DATE=(SELECT MIN(t3.BEGIN_DATE)
FROM M6_CHECK t3
WHERE t3.ID_ITEM=t1.ID_ITEM
AND
t3.BEGIN_DATE>t1.BEGIN_DATE)
ORDER BY t1.ID_ITEM,t1.ID_QUOTE,t1.BEGIN_DATE|||It's almost the good result. The problem is that if you add the following
record
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q9','I3','01/01/2001','12/31/2001');
This record should appear in the result, and with your query it does not :(
Damn it !!
Thanks a lot for your help !!
Alex|||What result are you expecting? Q1 -> Q9 or Q9 ->Q1 or both?
Try this
SELECT t1.ID_ITEM,
t1.ID_QUOTE as ID_QUOTE_1,
t1.END_DATE as END_DATE_1,
t2.ID_QUOTE as ID_QUOTE_2,
t2.BEGIN_DATE as BEGIN_DATE_2
FROM M6_CHECK t1
INNER JOIN M6_CHECK t2 ON t1.ID_ITEM=t2.ID_ITEM
AND t1.ID_QUOTE<t2.ID_QUOTE
AND t1.BEGIN_DATE<=t2.BEGIN_DATE
AND DATEADD(day,1,t1.END_DATE)<>t2.BEGIN_DATE
ORDER BY t1.ID_ITEM,t1.ID_QUOTE,t1.BEGIN_DATE
CREATE TABLE M6_CHECK
(
ID_QUOTE VARCHAR(20),
ID_ITEM VARCHAR(20),
BEGIN_DATE DATETIME,
END_DATE DATETIME
)
This table contains the following data :
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q1','I1','01/01/2001','12/31/2001');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q1','I2','01/01/2001','12/31/2001');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q1','I3','01/01/2001','12/31/2001');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q2','I1','01/01/2000','12/31/2000');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q2','I4','01/01/2000','12/31/2000');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q3','I2','01/01/2000','12/31/2000');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q3','I5','01/01/2000','12/31/2000');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q4','I3','01/01/2000','12/31/2000');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q4','I6','01/01/2000','12/31/2000');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q5','I4','01/01/2001','12/31/2001');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q6','I5','01/12/2000','12/31/2001');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q7','I6','01/31/2001','12/31/2001')
I am trying to find a SQL query which would return the following recordset :
ID_ITEM ID_QUOTE_1 END_DATE_1 ID_QUOTE_2 BEGIN_DATE_2
----
I5 Q3 12/31/2000 Q6 01/12/2000
I6 Q4 12/31/2000 Q7 01/31/2001
Let me explain :
The SQL Query should return every ID_ITEM for which 2 consecutives quotes
don't verify the following rule :
end date previous quote = begin date next quote - 1 day
It looks like I should make an auto join in the query but I couldn't make it
:(
Thanks in advance for your help,
AlexSELECT t1.ID_ITEM,
t1.ID_QUOTE as ID_QUOTE_1,
t1.END_DATE as END_DATE_1,
t2.ID_QUOTE as ID_QUOTE_2,
t1.BEGIN_DATE as BEGIN_DATE_2
FROM M6_CHECK t1
INNER JOIN M6_CHECK t2 ON t1.ID_ITEM=t2.ID_ITEM
AND t1.BEGIN_DATE<t2.BEGIN_DATE
AND DATEADD(day,1,t1.END_DATE)<>t2.BEGIN_DATE
ORDER BY t1.ID_QUOTE,t1.ID_ITEM,t1.BEGIN_DATE|||typo..
t1.BEGIN_DATE as BEGIN_DATE_2
should be
t2.BEGIN_DATE as BEGIN_DATE_2|||This doesn't show up in the test data, but
more correctly I think it should be this
SELECT t1.ID_ITEM,
t1.ID_QUOTE as ID_QUOTE_1,
t1.END_DATE as END_DATE_1,
t2.ID_QUOTE as ID_QUOTE_2,
t2.BEGIN_DATE as BEGIN_DATE_2
FROM M6_CHECK t1
INNER JOIN M6_CHECK t2 ON t1.ID_ITEM=t2.ID_ITEM
AND t1.BEGIN_DATE<t2.BEGIN_DATE
AND DATEADD(day,1,t1.END_DATE)<>t2.BEGIN_DATE
AND t2.BEGIN_DATE=(SELECT MIN(t3.BEGIN_DATE)
FROM M6_CHECK t3
WHERE t3.ID_ITEM=t1.ID_ITEM
AND
t3.BEGIN_DATE>t1.BEGIN_DATE)
ORDER BY t1.ID_ITEM,t1.ID_QUOTE,t1.BEGIN_DATE|||It's almost the good result. The problem is that if you add the following
record
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q9','I3','01/01/2001','12/31/2001');
This record should appear in the result, and with your query it does not :(
Damn it !!
Thanks a lot for your help !!
Alex|||What result are you expecting? Q1 -> Q9 or Q9 ->Q1 or both?
Try this
SELECT t1.ID_ITEM,
t1.ID_QUOTE as ID_QUOTE_1,
t1.END_DATE as END_DATE_1,
t2.ID_QUOTE as ID_QUOTE_2,
t2.BEGIN_DATE as BEGIN_DATE_2
FROM M6_CHECK t1
INNER JOIN M6_CHECK t2 ON t1.ID_ITEM=t2.ID_ITEM
AND t1.ID_QUOTE<t2.ID_QUOTE
AND t1.BEGIN_DATE<=t2.BEGIN_DATE
AND DATEADD(day,1,t1.END_DATE)<>t2.BEGIN_DATE
ORDER BY t1.ID_ITEM,t1.ID_QUOTE,t1.BEGIN_DATE
Auto incremented integer primary keys vs varchar primary keys
Hi,
I have recently been looking at a database and wondered if anyone can tell me what the advantages are supporting a unique collumn, which can essentially be seen as the primary key, with an identity seed integer primary key.
For example:
id [unique integer auto incremented primary key - not null],
ClientCode [unique index varchar - not null],
name [varchar null],
surname [varchar null]
isn't it just better to use ClientCode as the primary key straight of because when one references the above table, it can be done easier with the ClientCode since you dont have to do a lookup on the ClientCode everytime.
Regards
Mike
Why does everyone confuse a "primary key" and a "unique constraint"? They are totally different things. A PK is UNIQUE and ORDER of a table, which is also a "unique constraint". The PK should be the field (NOT fields) you use to LINK the table to other tables.
I have been doing databases for a very long time and I have several rules, which I have developed over the years on creating tables:
EVERY table has an integer id field, even if it is not needed right now, it will be later -- integer searching is 10,000 times faster than character searching and the indexes are much smaller.
NEVER key tables on data the user can change -- If you use ClientCode in your example above, and you change ClientCode, you would need to change EVERY SINGLE table which links the table using ClientCode.
|||
First of all Tom I have to disagree with the "ORDER of a table" statement. The order of a table is based on the clustered index butI do agree in most cases the primary key is the clustered key but this is not mandatory.
Most of the times when people tend to use non integer fields as a primary key it is because there is a suitable natural key for a table. I prefer natural keys if they fulfill the requirements for a primary key and that is unique, narrow and static (one might say ever increasing but then we would be back at the point of the clustered index story). Your example clearly shows the static requirement.
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
|||That is true. A PK is usually a clustered index, although it is not required to be, in SQL Server. Other database engines may require it to be clustered. Generally, the PK is a clustered index, because it is field most searched, it improves performance by having the table ordered by the PK.In the case where "ClientCode" can NEVER EVER change, other than speed, there is nothing wrong with using it. However, I personally would not use it.
|||Hi,
Thanks for your replies, I appreciate it. I know about the performance bonus that one gets by using a numeric primary key vs a varchar but I was curious to know the possible advantages of why one would sometimes not use it especially since the 'ClientCode' is not controlled within my database and is actually a primary key from a table in another database that I am pulling data from.
Personally I have always been using an integer autoincremented primary key and placed an unique index on the 'ClientCode' but I became curious to know why people don't always do it after seeing some other databases where they use the 'ClientCode' as a primary key.
Regards
Mike
|||
The are several different arguments around natural keys vs artificial or surrogate keys. The one Wesley mentioned where you don't have a column that's practical to use as a key. And the issues you are aware of in terms of performance. And another argument is that what is a natural key for a table may change over time - business needs and rules change, external sources may change, etc. If you use an artificial key (generated number in your case), that won't change in terms of being the unique identifier. You can do a search on the two terms and find many articles that debate this. It's one of those ongoing, long standing debates in the relational database world. Some people just prefer one over the other depending on which side of the "debate" they believe in.
-Sue
|||In your case where ClientCode is not controlled inside you database, that qualifies as "user data" and I would never use that field to link to other tables in my database. I would create an internal key to use as the FK.What happens to your data if ClientCode is a varchar(10) now and the vendor decides to change it to a long? Or worse, allowes duplicates at some point.
|||Hi there,
Thank you all for your valuable input. I can understand why this might have been an ongoing debate since some people prefer simplicity over performance etc and I, for one, tend to be more of an 'performance' person so I will keep on using an seeded primary key.
It was good to hear the opinion of everyone here.
Thanks a lot
Mike
Auto incremented integer primary keys vs varchar primary keys
Hi,
I have recently been looking at a database and wondered if anyone can tell me what the advantages are supporting a unique collumn, which can essentially be seen as the primary key, with an identity seed integer primary key.
For example:
id [unique integer auto incremented primary key - not null],
ClientCode [unique index varchar - not null],
name [varchar null],
surname [varchar null]
isn't it just better to use ClientCode as the primary key straight of because when one references the above table, it can be done easier with the ClientCode since you dont have to do a lookup on the ClientCode everytime.
Regards
Mike
Why does everyone confuse a "primary key" and a "unique constraint"? They are totally different things. A PK is UNIQUE and ORDER of a table, which is also a "unique constraint". The PK should be the field (NOT fields) you use to LINK the table to other tables.
I have been doing databases for a very long time and I have several rules, which I have developed over the years on creating tables:
EVERY table has an integer id field, even if it is not needed right now, it will be later -- integer searching is 10,000 times faster than character searching and the indexes are much smaller.
NEVER key tables on data the user can change -- If you use ClientCode in your example above, and you change ClientCode, you would need to change EVERY SINGLE table which links the table using ClientCode.
|||
First of all Tom I have to disagree with the "ORDER of a table" statement. The order of a table is based on the clustered index butI do agree in most cases the primary key is the clustered key but this is not mandatory.
Most of the times when people tend to use non integer fields as a primary key it is because there is a suitable natural key for a table. I prefer natural keys if they fulfill the requirements for a primary key and that is unique, narrow and static (one might say ever increasing but then we would be back at the point of the clustered index story). Your example clearly shows the static requirement.
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
|||That is true. A PK is usually a clustered index, although it is not required to be, in SQL Server. Other database engines may require it to be clustered. Generally, the PK is a clustered index, because it is field most searched, it improves performance by having the table ordered by the PK.In the case where "ClientCode" can NEVER EVER change, other than speed, there is nothing wrong with using it. However, I personally would not use it.
|||Hi,
Thanks for your replies, I appreciate it. I know about the performance bonus that one gets by using a numeric primary key vs a varchar but I was curious to know the possible advantages of why one would sometimes not use it especially since the 'ClientCode' is not controlled within my database and is actually a primary key from a table in another database that I am pulling data from.
Personally I have always been using an integer autoincremented primary key and placed an unique index on the 'ClientCode' but I became curious to know why people don't always do it after seeing some other databases where they use the 'ClientCode' as a primary key.
Regards
Mike
|||
The are several different arguments around natural keys vs artificial or surrogate keys. The one Wesley mentioned where you don't have a column that's practical to use as a key. And the issues you are aware of in terms of performance. And another argument is that what is a natural key for a table may change over time - business needs and rules change, external sources may change, etc. If you use an artificial key (generated number in your case), that won't change in terms of being the unique identifier. You can do a search on the two terms and find many articles that debate this. It's one of those ongoing, long standing debates in the relational database world. Some people just prefer one over the other depending on which side of the "debate" they believe in.
-Sue
|||In your case where ClientCode is not controlled inside you database, that qualifies as "user data" and I would never use that field to link to other tables in my database. I would create an internal key to use as the FK.What happens to your data if ClientCode is a varchar(10) now and the vendor decides to change it to a long? Or worse, allowes duplicates at some point.
|||Hi there,
Thank you all for your valuable input. I can understand why this might have been an ongoing debate since some people prefer simplicity over performance etc and I, for one, tend to be more of an 'performance' person so I will keep on using an seeded primary key.
It was good to hear the opinion of everyone here.
Thanks a lot
Mike
Auto incremented integer primary keys vs varchar primary keys
Hi,
I have recently been looking at a database and wondered if anyone can tell me what the advantages are supporting a unique collumn, which can essentially be seen as the primary key, with an identity seed integer primary key.
For example:
id [unique integer auto incremented primary key - not null],
ClientCode [unique index varchar - not null],
name [varchar null],
surname [varchar null]
isn't it just better to use ClientCode as the primary key straight of because when one references the above table, it can be done easier with the ClientCode since you dont have to do a lookup on the ClientCode everytime.
Regards
Mike
Why does everyone confuse a "primary key" and a "unique constraint"? They are totally different things. A PK is UNIQUE and ORDER of a table, which is also a "unique constraint". The PK should be the field (NOT fields) you use to LINK the table to other tables.
I have been doing databases for a very long time and I have several rules, which I have developed over the years on creating tables:
EVERY table has an integer id field, even if it is not needed right now, it will be later -- integer searching is 10,000 times faster than character searching and the indexes are much smaller.
NEVER key tables on data the user can change -- If you use ClientCode in your example above, and you change ClientCode, you would need to change EVERY SINGLE table which links the table using ClientCode.
|||
First of all Tom I have to disagree with the "ORDER of a table" statement. The order of a table is based on the clustered index butI do agree in most cases the primary key is the clustered key but this is not mandatory.
Most of the times when people tend to use non integer fields as a primary key it is because there is a suitable natural key for a table. I prefer natural keys if they fulfill the requirements for a primary key and that is unique, narrow and static (one might say ever increasing but then we would be back at the point of the clustered index story). Your example clearly shows the static requirement.
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
|||That is true. A PK is usually a clustered index, although it is not required to be, in SQL Server. Other database engines may require it to be clustered. Generally, the PK is a clustered index, because it is field most searched, it improves performance by having the table ordered by the PK.In the case where "ClientCode" can NEVER EVER change, other than speed, there is nothing wrong with using it. However, I personally would not use it.
|||Hi,
Thanks for your replies, I appreciate it. I know about the performance bonus that one gets by using a numeric primary key vs a varchar but I was curious to know the possible advantages of why one would sometimes not use it especially since the 'ClientCode' is not controlled within my database and is actually a primary key from a table in another database that I am pulling data from.
Personally I have always been using an integer autoincremented primary key and placed an unique index on the 'ClientCode' but I became curious to know why people don't always do it after seeing some other databases where they use the 'ClientCode' as a primary key.
Regards
Mike
|||
The are several different arguments around natural keys vs artificial or surrogate keys. The one Wesley mentioned where you don't have a column that's practical to use as a key. And the issues you are aware of in terms of performance. And another argument is that what is a natural key for a table may change over time - business needs and rules change, external sources may change, etc. If you use an artificial key (generated number in your case), that won't change in terms of being the unique identifier. You can do a search on the two terms and find many articles that debate this. It's one of those ongoing, long standing debates in the relational database world. Some people just prefer one over the other depending on which side of the "debate" they believe in.
-Sue
|||In your case where ClientCode is not controlled inside you database, that qualifies as "user data" and I would never use that field to link to other tables in my database. I would create an internal key to use as the FK.What happens to your data if ClientCode is a varchar(10) now and the vendor decides to change it to a long? Or worse, allowes duplicates at some point.
|||Hi there,
Thank you all for your valuable input. I can understand why this might have been an ongoing debate since some people prefer simplicity over performance etc and I, for one, tend to be more of an 'performance' person so I will keep on using an seeded primary key.
It was good to hear the opinion of everyone here.
Thanks a lot
Mike
Subscribe to:
Posts (Atom)