If Else Condition

Hi guys,

I have 2 tables which are connected to each other by casenumber:

CodeTable

CaseNumber OldCode

001 05

002 05

003 05

004 05

005 06

ConnectionTable

CaseNumber ConnectionType

001 G

001 H

001 N

002 M

002 H

003 G

003 H

003 I

003 N

003 N

004 X

004 N

--

With following Mapping Condition:

OldCode NewCode

05 (with connectionType G) GE

05 (with ConnectionType H) GP

05 (With ConnectionType I) GPE

05 (With ConnectionType G OR H) GPE

--

With the following Rules:

1. Search for connection type that is mapped and disregard all other connection types.

2. If none of the mapped types are connected to the case, map as follows : 05 to GPE

3. If multiple types are connected to the case, map as follows: 05 to GPE.

I would like to map the old code to the new code on the CodeTable without getting duplicate CaseNumber.

My code:

SELECT DISTINCT c1.CaseNumber,

CASE WHEN c1.OldCode = '05' THEN

CASE c2.ConnectionType WHEN 'G' THEN 'GE'

WHEN 'H' THEN 'GP'

WHEN 'I' THEN 'GPE'

ELSE 'GPE' END END AS NewCode,

FROM CodeTable AS c1

LEFT JOIN(SELECT MIN(ConnectionType) [ConnectionType],CaseNumber

FROM ConnectionTable GROUP BY CaseNumber) AS c2

ON (c1.CaseNumber = c2.CaseNumber)

I did not get all answer correctly because let say i have Casenumber 001 with connection G, H, and N; as given in the rule, I need to map the multiple types to GPE, but what i get is GE (which is OldCode 'G'). This is probably because i select MIN from connectionType and the first one i get is G, that's why i get GE for the new code instead of GPE.

I hope you guys will help me on this. Thanks so much!!!

Jul.

If I understand your requirements correctly, perhaps something like this:

Code Snippet

SET NOCOUNT ON

DECLARE @.Codes table
( CaseNumber varchar(10),
OldCode varchar(5)
)

INSERT INTO @.Codes VALUES ( '001', '05' )
INSERT INTO @.Codes VALUES ( '002', '05' )
INSERT INTO @.Codes VALUES ( '003', '05' )
INSERT INTO @.Codes VALUES ( '004', '05' )
INSERT INTO @.Codes VALUES ( '005', '05' )
INSERT INTO @.Codes VALUES ( '006', '05' )
INSERT INTO @.Codes VALUES ( '007', '05' )
INSERT INTO @.Codes VALUES ( '008', '06' )
INSERT INTO @.Codes VALUES ( '009', '06' )

DECLARE @.Connections table
( CaseNumber varchar(10),
ConnectionType varchar(5)
)

INSERT INTO @.Connections VALUES ( '001', 'G' )
INSERT INTO @.Connections VALUES ( '001', 'H' )
INSERT INTO @.Connections VALUES ( '001', 'N' )
INSERT INTO @.Connections VALUES ( '002', 'M' )
INSERT INTO @.Connections VALUES ( '003', 'G' )
INSERT INTO @.Connections VALUES ( '003', 'H' )
INSERT INTO @.Connections VALUES ( '003', 'I' )
INSERT INTO @.Connections VALUES ( '003', 'N' )
INSERT INTO @.Connections VALUES ( '003', 'N' )
INSERT INTO @.Connections VALUES ( '004', 'G' )
INSERT INTO @.Connections VALUES ( '005', 'H' )
INSERT INTO @.Connections VALUES ( '006', 'I' )
INSERT INTO @.Connections VALUES ( '007', 'G' )
INSERT INTO @.Connections VALUES ( '008', 'H' )

SELECT DISTINCT
c1.CaseNumber,
c1.OldCode,
NewCode = CASE
WHEN ( c1.OldCode = '05' ) THEN
CASE
WHEN ( dt.CaseCount = 1 ) THEN
CASE WHEN ( c2.ConnectionType = 'G' ) THEN 'GE'
WHEN ( c2.ConnectionType = 'H' ) THEN 'GP'
ELSE 'GPE'
END
WHEN ( dt.CaseCount <> 1 ) OR ( dt.CaseNumber IS NULL ) THEN 'GPE'
END
ELSE 'GPE'
END
FROM @.Codes c1
LEFT JOIN (SELECT
CaseNumber,
CaseCount = count( CaseNumber )
FROM @.Connections
GROUP BY CaseNumber
) dt
ON c1.CaseNumber = dt.CaseNumber
LEFT JOIN @.Connections c2
ON c1.CaseNumber = c2.CaseNumber

CaseNumber OldCode NewCode
- - -
001 05 GPE
002 05 GPE
003 05 GPE
004 05 GE
005 05 GP
006 05 GPE
007 05 GE
008 06 GPE
009 06 GPE

|||

Thanks Arnie,

CaseNumber ConnectionType

001 G

001 H

001 N

002 M

002 H

003 G

003 H

003 I

003 N

003 N

004 X

004 N

The appropriate result is supposed to be:

CaseNumber NewCode
001 GPE (Because there are multiple types refer rul 3)
002 GP (Because H is in there, and ignore M because M is not

in one of the mapping criteria of code 05) [refer to rule 1.)
003 GPE (Because there are multiple types refer rule 3)
004 GPE (Because none of the mapped types are connected to the case -- refer rule 2)

Can you help me on this please? thanks........

|||

Hi Jul

Hope this suits your every requirement, I have done few modification to the code Arnie. Assuming that u require something similar like this.

Regards

Vijai K

SET NOCOUNT ON

Declare @.final table(

CaseNumber varchar(10),

NewCode varchar(5)

);

DECLARE @.Codes table(

CaseNumber varchar(10),

OldCode varchar(5)

)

INSERT INTO @.Codes VALUES ( '001', '05' )

INSERT INTO @.Codes VALUES ( '002', '05' )

INSERT INTO @.Codes VALUES ( '003', '05' )

INSERT INTO @.Codes VALUES ( '004', '05' )

INSERT INTO @.Codes VALUES ( '005', '05' )

INSERT INTO @.Codes VALUES ( '006', '05' )

INSERT INTO @.Codes VALUES ( '007', '05' )

INSERT INTO @.Codes VALUES ( '008', '06' )

INSERT INTO @.Codes VALUES ( '009', '06' )

INSERT INTO @.Codes VALUES ( '010', '05' )

DECLARE @.Connections table(

CaseNumber varchar(10),

ConnectionType varchar(5)

)

INSERT INTO @.Connections VALUES ( '001', 'G' )

INSERT INTO @.Connections VALUES ( '001', 'H' )

INSERT INTO @.Connections VALUES ( '001', 'N' )

INSERT INTO @.Connections VALUES ( '002', 'M' )

INSERT INTO @.Connections VALUES ( '002', 'H' )

INSERT INTO @.Connections VALUES ( '003', 'G' )

INSERT INTO @.Connections VALUES ( '003', 'H' )

INSERT INTO @.Connections VALUES ( '003', 'I' )

INSERT INTO @.Connections VALUES ( '003', 'N' )

INSERT INTO @.Connections VALUES ( '003', 'N' )

INSERT INTO @.Connections VALUES ( '004', 'X' )

INSERT INTO @.Connections VALUES ( '004', 'N' )

INSERT INTO @.Connections VALUES ( '005', 'H' )

INSERT INTO @.Connections VALUES ( '005', 'X' )

INSERT INTO @.Connections VALUES ( '005', 'N' )

INSERT INTO @.Connections VALUES ( '006', 'I' )

INSERT INTO @.Connections VALUES ( '007', 'G' )

INSERT INTO @.Connections VALUES ( '007', 'N' )

INSERT INTO @.Connections VALUES ( '008', 'H' )

INSERT INTO @.Connections VALUES ( '009', 'H' )

INSERT INTO @.Connections VALUES ( '010', 'G' )

INSERT INTO @.Connections VALUES ( '010', 'X' )

insert into @.final

SELECT DISTINCT

c1.CaseNumber,

NewCode = CASE

WHEN ( c1.OldCode = '05' ) THEN

CASE

WHEN ( dt.CaseCount = 1 ) OR ( dt.CaseCount = 2 ) THEN

CASE WHEN ( c2.ConnectionType = 'G' ) THEN 'GE'

WHEN ( c2.ConnectionType = 'H' ) THEN 'GP'

ELSE 'GPE'

END

WHEN ( dt.CaseCount > 2 ) OR ( dt.CaseNumber IS NULL ) THEN 'GPE'

END

ELSE 'GPE'

END

FROM @.Codes c1

LEFT JOIN (SELECT CaseNumber, CaseCount = count(CaseNumber)

FROM @.Connections

GROUP BY CaseNumber) dt

ON c1.CaseNumber = dt.CaseNumber

LEFT JOIN @.Connections c2

ON c1.CaseNumber = c2.CaseNumber

delete from @.final where Newcode = 'GPE' and casenumber in( select Casenumber from @.final group by casenumber having count(*) >1 )

select * from @.final

|||Here's a guess. It's probably not quite there yet, but it may give you some ideas.

Code Snippet

DECLARE @.Codes table
( CaseNumber varchar(10),
OldCode varchar(5)
)

INSERT INTO @.Codes VALUES ( '001', '05' )
INSERT INTO @.Codes VALUES ( '002', '05' )
INSERT INTO @.Codes VALUES ( '003', '05' )
INSERT INTO @.Codes VALUES ( '004', '05' )
INSERT INTO @.Codes VALUES ( '005', '05' )
INSERT INTO @.Codes VALUES ( '006', '05' )
INSERT INTO @.Codes VALUES ( '007', '05' )
INSERT INTO @.Codes VALUES ( '008', '06' )
INSERT INTO @.Codes VALUES ( '009', '06' )

DECLARE @.Connections table
( CaseNumber varchar(10),
ConnectionType varchar(5)
)

INSERT INTO @.Connections VALUES ( '001', 'G' )
INSERT INTO @.Connections VALUES ( '001', 'H' )
INSERT INTO @.Connections VALUES ( '001', 'N' )
INSERT INTO @.Connections VALUES ( '002', 'M' )
INSERT INTO @.Connections VALUES ( '003', 'G' )
INSERT INTO @.Connections VALUES ( '003', 'H' )
INSERT INTO @.Connections VALUES ( '003', 'I' )
INSERT INTO @.Connections VALUES ( '003', 'N' )
INSERT INTO @.Connections VALUES ( '003', 'N' )
INSERT INTO @.Connections VALUES ( '004', 'G' )
INSERT INTO @.Connections VALUES ( '005', 'H' )
INSERT INTO @.Connections VALUES ( '006', 'I' )
INSERT INTO @.Connections VALUES ( '007', 'G' )
INSERT INTO @.Connections VALUES ( '008', 'H' )

DECLARE @.MappedTypes table (
ID int,
OldCode varchar(5),
mappedType varchar(5)
)
INSERT INTO @.MappedTypes VALUES (0, '05', 'G')
INSERT INTO @.MappedTypes VALUES (1, '05', 'H')
INSERT INTO @.MappedTypes VALUES (2, '05', 'I')

DECLARE @.Mapping table (
OldCode varchar(5),
Connections varchar(10),
Bitmask varbinary(16),
newCode varchar(5)
)
INSERT INTO @.Mapping VALUES ( '05', 'G', 0x1, 'GE')
INSERT INTO @.Mapping VALUES ( '05', 'H', 0x2, 'GP')
INSERT INTO @.Mapping VALUES ( '05', 'I', 0x4, 'GPE')
INSERT INTO @.Mapping VALUES ( '05', NULL, NULL, 'GPE');
-- last row indicates code to use when there is some
-- mapped value present, but when the particular collection
-- of mapped values does not have a specific mapping.

with B(CaseNumber, Bitmask, OldCode) as (
select
C.CaseNumber,
sum(distinct coalesce(power(2,ID),0)) as Bitmask,
D.OldCode

from @.Connections as C
join @.Codes as D
on D.CaseNumber = C.CaseNumber
join @.MappedTypes AS M
on C.ConnectionType = M.mappedType
and D.OldCode = M.OldCode
group by C.CaseNumber, D.OldCode
)
select
CaseNumber,
coalesce(newCode,(select newCode from @.Mapping where Bitmask is null))
from B
left outer join @.Mapping as M
on M.OldCode = B.OldCode
and M.Bitmask = B.Bitmask

Steve Kass
Drew University
http://www.stevekass.com
|||

Jul,

I looked at this problem again, and came to the conclusion that using a Mapping table would be useful. I think that this satisfies your rules and matches your expected output.

Code Snippet

SET NOCOUNT ON

DECLARE @.Codes table
( CaseNumber varchar(10),
OldCode varchar(5)
)

INSERT INTO @.Codes VALUES ( '001', '05' )
INSERT INTO @.Codes VALUES ( '002', '05' )
INSERT INTO @.Codes VALUES ( '003', '05' )
INSERT INTO @.Codes VALUES ( '004', '05' )
INSERT INTO @.Codes VALUES ( '005', '06' )

DECLARE @.Connections table
( CaseNumber varchar(10),
ConnectionType varchar(5)
)

INSERT INTO @.Connections VALUES ( '001', 'G' )
INSERT INTO @.Connections VALUES ( '001', 'H' )
INSERT INTO @.Connections VALUES ( '001', 'N' )
INSERT INTO @.Connections VALUES ( '002', 'H' )
INSERT INTO @.Connections VALUES ( '002', 'M' )
INSERT INTO @.Connections VALUES ( '003', 'G' )
INSERT INTO @.Connections VALUES ( '003', 'H' )
INSERT INTO @.Connections VALUES ( '003', 'I' )
INSERT INTO @.Connections VALUES ( '003', 'N' )
INSERT INTO @.Connections VALUES ( '003', 'N' )
INSERT INTO @.Connections VALUES ( '004', 'N' )
INSERT INTO @.Connections VALUES ( '004', 'X' )

DECLARE @.CodeMap table
( OldCode char(2),
OldType char(1),
NewCode varchar(3)
)

INSERT INTO @.CodeMap VALUES ( '05', 'G', 'GE' )
INSERT INTO @.CodeMap VALUES ( '05', 'H', 'GP' )

SELECT
c.CaseNumber,
NewCode = CASE
WHEN count( m.NewCode ) = 1 AND min( m.NewCode ) IS NOT NULL THEN min( m.NewCode )
ELSE 'GPE'
END
FROM @.Codes c
LEFT JOIN @.Connections c2
ON c.CaseNumber = c2.CaseNumber
LEFT JOIN @.CodeMap m
ON c2.ConnectionType = m.OldType
GROUP BY c.CaseNumber

CaseNumber NewCode
- -
001 GPE
002 GP
003 GPE
004 GPE
005 GPE

|||

The following Code BY Arnie is the most appropriate answer. Thank's guys!

SET NOCOUNT ON

DECLARE @.Codes table
( CaseNumber varchar(10),
OldCode varchar(5)
)

INSERT INTO @.Codes VALUES ( '001', '05' )
INSERT INTO @.Codes VALUES ( '002', '05' )
INSERT INTO @.Codes VALUES ( '003', '05' )
INSERT INTO @.Codes VALUES ( '004', '05' )
INSERT INTO @.Codes VALUES ( '005', '06' )

DECLARE @.Connections table
( CaseNumber varchar(10),
ConnectionType varchar(5)
)

INSERT INTO @.Connections VALUES ( '001', 'G' )
INSERT INTO @.Connections VALUES ( '001', 'H' )
INSERT INTO @.Connections VALUES ( '001', 'N' )
INSERT INTO @.Connections VALUES ( '002', 'H' )
INSERT INTO @.Connections VALUES ( '002', 'M' )
INSERT INTO @.Connections VALUES ( '003', 'G' )
INSERT INTO @.Connections VALUES ( '003', 'H' )
INSERT INTO @.Connections VALUES ( '003', 'I' )
INSERT INTO @.Connections VALUES ( '003', 'N' )
INSERT INTO @.Connections VALUES ( '003', 'N' )
INSERT INTO @.Connections VALUES ( '004', 'N' )
INSERT INTO @.Connections VALUES ( '004', 'X' )

DECLARE @.CodeMap table
( OldCode char(2),
OldType char(1),
NewCode varchar(3)
)

INSERT INTO @.CodeMap VALUES ( '05', 'G', 'GE' )
INSERT INTO @.CodeMap VALUES ( '05', 'H', 'GP' )

SELECT
c.CaseNumber,
NewCode = CASE
WHEN count( m.NewCode ) = 1 AND min( m.NewCode ) IS NOT NULL THEN min( m.NewCode )
ELSE 'GPE'
END
FROM @.Codes c
LEFT JOIN @.Connections c2
ON c.CaseNumber = c2.CaseNumber
LEFT JOIN @.CodeMap m
ON c2.ConnectionType = m.OldType
GROUP BY c.CaseNumber

CaseNumber NewCode
- -
001 GPE
002 GP
003 GPE
004 GPE
005 GPE

|||

Arnie, seems like it does not work if instead of having

INSERT INTO @.Connections VALUES ( '001', 'G' )
INSERT INTO @.Connections VALUES ( '001', 'H' )
INSERT INTO @.Connections VALUES ( '001', 'N' )

but i'm having these values:

INSERT INTO @.Connections VALUES ( '001', 'H' )
INSERT INTO @.Connections VALUES ( '001', 'H' )
INSERT INTO @.Connections VALUES ( '001', 'N' )

On the rules given, if I have H (without G or I) then map to 'GP'

If i have multiple matched connections let say 'H' AND 'G' (or 'H' AND 'I') then map to 'GPE'.

But i have a case where the connection in the case is like the values above, which is H, H, and N. When i tried your code, it gives me 'GPE', what i need is 'GE'. Everything else works perfect. I am really a beginner in sql, hope you can help. thanks.

|||

Of course, I overlooked that possiblitilty. Thanks for bringing it to my attention.

If you add 'DISTINCT' to the count( m.NewCode ) it 'should' take care of that situation.

Code Snippet

NewCode = CASE
WHEN count( DISTINCT m.NewCode ) = 1 AND min( m.NewCode ) IS NOT NULL THEN min( m.NewCode )

And this 'should' handle any additional 'OldCodes' just by inserting rows into the Mapping table. (Unless your rules change substaintially.)

|||

Awesome........Thanks so much!!!!!!!!

Julia

|||A tip 'o the hat to Steve Kass for nudging me to consider a mapping table...

Identity Seed

Hi guys,
Please is it possible in SQL SERVER any version (Prefferably 2005) to set my Identity seed to the current year so that when we are in 2008 it continues from 2008 ?

Best Regards

I'm not too sure why you want to use current year as the seed. Anyways, AFAIK this is not possible.

Can you explain your problem so that we can help you better ?

|||

websyd:

set my Identity seed to the current year so that when we are in 2008 it continues from 2008

This defeats the purpose of an identiy column, Identiy columns are "dataless", they have no intrinsic meaning other than to uniquely identify the row within a table. And you can't do it, anyway.

What are you trying to do?

Identity Range Problem

Hi guys - A client just encountered a problem where he could not insert
into a table because of pk constraints. The database is a replica of a merge
publication with automatic identity range with settings:
Range at Pub : 1000
Range at Sub : 1000
Thresh.: 80
When I checked what the next identity was going to be, it returned '12'
but it should really be over 1000. As a matter of fact, it seems that all
tables seem to be using the Publisher's range.
Does anybody know what could have caused this?
What are the side-effects if I reseed the tables?
Thanks - Maer
You probably have a check constraint in place which is limiting the range of
values which can be inserted. Automatic identity range management had a
nasty habit of lingering after the subscription was dropped you might be
running into this - if so I would delete this constraint or adjust it.
I don't really understand what you mean by replica? Do you mean its a
subscriber, or you someone made a replica of the publication database
(through a backup perhaps).
You might also want to review this article -
http://www.simple-talk.com/2005/07/05/replication/
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Maer" <maer@.auditleverage.com> wrote in message
news:%23VIoj$%23NGHA.420@.tk2msftngp13.phx.gbl...
> Hi guys - A client just encountered a problem where he could not
> insert into a table because of pk constraints. The database is a replica
> of a merge publication with automatic identity range with settings:
> Range at Pub : 1000
> Range at Sub : 1000
> Thresh.: 80
> When I checked what the next identity was going to be, it returned '12'
> but it should really be over 1000. As a matter of fact, it seems that all
> tables seem to be using the Publisher's range.
> Does anybody know what could have caused this?
> What are the side-effects if I reseed the tables?
> Thanks - Maer
>
|||Hi Hilary - Thanks for your response. I should have said subscriber
instead of replica.
It turned out that the client was still in SP 1 and I heard there were
some issues with identity ranges prior to SP 3. So we applied the latest SP
and dropped all subscriptions and re-subscribed. So far it seems to be
working.
It is good to know the issue with triggers so that this is one more
thing to check if that happens again.
Thanks - Maer
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uCviJ1BOGHA.2628@.TK2MSFTNGP15.phx.gbl...
> You probably have a check constraint in place which is limiting the range
> of values which can be inserted. Automatic identity range management had a
> nasty habit of lingering after the subscription was dropped you might be
> running into this - if so I would delete this constraint or adjust it.
> I don't really understand what you mean by replica? Do you mean its a
> subscriber, or you someone made a replica of the publication database
> (through a backup perhaps).
> You might also want to review this article -
> http://www.simple-talk.com/2005/07/05/replication/
>
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Maer" <maer@.auditleverage.com> wrote in message
> news:%23VIoj$%23NGHA.420@.tk2msftngp13.phx.gbl...
>

Identity or GUID?

OK Guys,
We are considering replacing the standard IDENTITY column with the UNIQUEIDE
NTIFIER column for all our primary keys.
Good? Bad? Crazy? WTF!?
Any thoughts on index clustering, performance, portability, managability, et
c... would be appreciated.
RobertHi
http://www.sql-server-performance.c...red_indexes.asp
"rmg66" <rgwathney__xXx__primepro.com> wrote in message news:eZPZpnWiGHA.428
4@.TK2MSFTNGP05.phx.gbl...
OK Guys,
We are considering replacing the standard IDENTITY column with the UNIQUEIDE
NTIFIER column for all our primary keys.
Good? Bad? Crazy? WTF!?
Any thoughts on index clustering, performance, portability, managability, et
c... would be appreciated.
Robert|||From what I have read and understand Indexes which gets built on GUID wud be
pretty heavy and so it might have a performance hit.
http://www.thescripts.com/forum/thread82632.html -- this might help.
Best Regards
Vadivel
http://vadivel.blogspot.com
"rmg66" wrote:

> OK Guys,
> We are considering replacing the standard IDENTITY column with the UNIQUEI
DENTIFIER column for all our primary keys.
> Good? Bad? Crazy? WTF!?
> Any thoughts on index clustering, performance, portability, managability,
etc... would be appreciated.
> Robert
>|||Hi Robert,
Why? The only reason I can think is that you are moving more to a
distributed database architecture and you want to guarentee that the
surrogate key (its not really a primary key, the primary key is part of your
data) is unique across databases.
You can still use IDENTITY but encode a site ID into the schema.
NEWID() is random in its generation so the insert will be random across your
index so, you will cause additional IO because the data will be more spread
across the disk (array) so, you might end up with more locking contention
too.
In a word - don't do it.
Oh, also - its a lot harder to debug and 'see' guids when you are working
with the data under DBA mode ;).
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"rmg66" <rgwathney__xXx__primepro.com> wrote in message
news:eZPZpnWiGHA.4284@.TK2MSFTNGP05.phx.gbl...
OK Guys,
We are considering replacing the standard IDENTITY column with the
UNIQUEIDENTIFIER column for all our primary keys.
Good? Bad? Crazy? WTF!?
Any thoughts on index clustering, performance, portability, managability,
etc... would be appreciated.
Robert|||2005 will have a new function called:
newsequentialid
http://msdn2.microsoft.com/en-us/library/ms189786.aspx
for 2000
http://www.sqldev.net/xp/xpguid.htm
is an option to overcome the "randomness" of NEWID()
There is a performance hit for using NEWID() in 2000. I won't deny that.
However... .
The big advantage of using GUIDS is that I can
Create my Relationships OUTSIDE of tsql code, aka, (for me) inside DotNet
code.
Read my previous post at:
http://groups.google.com/group/micr...be27c58c993dab7
I don't know if there is a super correct answer.
It depends on what you got going on.
Personally, me and my company are making great strides to get the business
logic OUT OF THE Database, and into the business layer.
See
http://www.codeproject.com/gen/desi...sinessLogic.asp
for more info
Most times, I'm going with some kind of GUID usage, but not the NEWID stuff.
If replication is in your plans, then you need to seriously consider
abandoning IDENTITY's.
But you should research and judge for yourself.
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:%23ih$yCXiGHA.3904@.TK2MSFTNGP02.phx.gbl...
> Hi Robert,
> Why? The only reason I can think is that you are moving more to a
> distributed database architecture and you want to guarentee that the
> surrogate key (its not really a primary key, the primary key is part of
your
> data) is unique across databases.
> You can still use IDENTITY but encode a site ID into the schema.
> NEWID() is random in its generation so the insert will be random across
your
> index so, you will cause additional IO because the data will be more
spread
> across the disk (array) so, you might end up with more locking contention
> too.
> In a word - don't do it.
> Oh, also - its a lot harder to debug and 'see' guids when you are working

> with the data under DBA mode ;).
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
SQL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "rmg66" <rgwathney__xXx__primepro.com> wrote in message
> news:eZPZpnWiGHA.4284@.TK2MSFTNGP05.phx.gbl...
> OK Guys,
> We are considering replacing the standard IDENTITY column with the
> UNIQUEIDENTIFIER column for all our primary keys.
> Good? Bad? Crazy? WTF!?
> Any thoughts on index clustering, performance, portability, managability,
> etc... would be appreciated.
> Robert
>
>|||My organization is in the midst of trying to replace our UNIQUEIDENTIFIER
clusetered primary keys with IDENTITY fields. Two reasons: 1) making the
clustered index a UNIQUEIDENTIFIER field increases the size of all the
nonclustered indexes; and 2) UNIQUEIDENTIFIER fields generated with the
NEWID() function are not sequential, so your joins will be much less
efficient.
Oh, and I forgot the last one: changing back is a pain!
"rmg66" <rgwathney__xXx__primepro.com> wrote in message
news:eZPZpnWiGHA.4284@.TK2MSFTNGP05.phx.gbl...
OK Guys,
We are considering replacing the standard IDENTITY column with the
UNIQUEIDENTIFIER column for all our primary keys.
Good? Bad? Crazy? WTF!?
Any thoughts on index clustering, performance, portability, managability,
etc... would be appreciated.
Robert|||> I don't know if there is a super correct answer.
> It depends on what you got going on.
> Personally, me and my company are making great strides to get the business
> logic OUT OF THE Database, and into the business layer.
>
You've missed the boat Sloan, the current thinking is to put the business
logic back into the database because its centralised and easier to manage -
plus you get better resource usage through cached execution code etc...
Google Jim Gray and look up some of his thinking on this.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"sloan" <sloan@.ipass.net> wrote in message
news:%23UU$cfXiGHA.3408@.TK2MSFTNGP05.phx.gbl...
> 2005 will have a new function called:
> newsequentialid
> http://msdn2.microsoft.com/en-us/library/ms189786.aspx
> for 2000
> http://www.sqldev.net/xp/xpguid.htm
> is an option to overcome the "randomness" of NEWID()
>
> There is a performance hit for using NEWID() in 2000. I won't deny that.
> However... .
> The big advantage of using GUIDS is that I can
> Create my Relationships OUTSIDE of tsql code, aka, (for me) inside DotNet
> code.
>
> Read my previous post at:
> http://groups.google.com/group/micr...be27c58c993dab7
>
> I don't know if there is a super correct answer.
> It depends on what you got going on.
> Personally, me and my company are making great strides to get the business
> logic OUT OF THE Database, and into the business layer.
> See
> http://www.codeproject.com/gen/desi...sinessLogic.asp
> for more info
>
> Most times, I'm going with some kind of GUID usage, but not the NEWID
> stuff.
> If replication is in your plans, then you need to seriously consider
> abandoning IDENTITY's.
> But you should research and judge for yourself.
>
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:%23ih$yCXiGHA.3904@.TK2MSFTNGP02.phx.gbl...
> your
> your
> spread
>
> SQL
>|||Why are you considering this? What gains do you expect, or why do you
think you need GUIDs? Aren't Identities working for you?
Have you seen the presentation of Kimberly Tripp on Index Optimization?
(see
http://www.microsoft.com/uk/technet...aspx?videoid=29)
In a fragment of it, she discusses the use of GUIDs as the clustered
index key, and how this use can cause massive fragmentation and (as a
result) abysmal performance.
BTW: there are different opinions about the policy to use an Identity
(or other surrogate key) as the Primary Key for each table. My personal
opinion is, that such a key should never be the first choice. So IMO you
should not have such a policy. One should always try to find a natural
key, and only choose a surrogate key if no useful natural key is found,
or for performance reasons (which means the natural key would still be
an alternate key, enforced with a Unique constraint).
HTH,
Gert-Jan

> rmg66 wrote:
> OK Guys,
> We are considering replacing the standard IDENTITY column with the
> UNIQUEIDENTIFIER column for all our primary keys.
> Good? Bad? Crazy? WTF!?
> Any thoughts on index clustering, performance, portability,
> managability, etc... would be appreciated.
> Robert
>|||Not trying to pick a fight, Tony, but you're one of the few that I've
seen advocate this. I haven't read any of Jim Gray's stuff, but I'll
take a look at it. It seems odd to me to put business logic back into
the database because of the issues of object-relational impedance,
scalability, and general performance considerations.
I prefer clean seperation, myself; I'm even beginning tto question the
need for stored procs because of the failure to seperate business logic
from data retrieval.
Stu
Tony Rogerson wrote:
> You've missed the boat Sloan, the current thinking is to put the business
> logic back into the database because its centralised and easier to manage
-
> plus you get better resource usage through cached execution code etc...
> Google Jim Gray and look up some of his thinking on this.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a S
QL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "sloan" <sloan@.ipass.net> wrote in message
> news:%23UU$cfXiGHA.3408@.TK2MSFTNGP05.phx.gbl...|||Well, that's great if you're married to Sql Server.
I love Sql Server, don't get me wrong, its my bread and butter.
But you can't guarantee you'll always be in a Sql Server world.
And I don't think I've "missed the boat".
My DataLayer objects return:
IDataReader's
DataSets (typed and untyped)
XmlDocuments
Scalars
voids (or nothings... as in, just make sure what I called worked)
Because I have a good DataLayer, I can switch out the backend database at
any given moment.
Yeah, there will be some issues, but not as drastic as complicated business
logic in my tsql.
The database is usually the bottleneck of any well designed system.
And the quicker I get in and get out, the better.
I'll take a look at Jim Gray's stuff. (is this the same Jim Gray who does
interviews for espn/nba?)
That's fine to say "There are other options out there, which have BL in the
database"
But "you missed the boat", ... thats a little strong for for advocating
another opinion in favor of what I and alot of others have proposed.
Perhaps the experience of having my company merge with another company, with
diffrent RDBMS systems has influenced me somewhat.
I'll stick with the good DataLayer design for now.
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:OHgQiOaiGHA.4284@.TK2MSFTNGP05.phx.gbl...
business
> You've missed the boat Sloan, the current thinking is to put the business
> logic back into the database because its centralised and easier to
manage -
> plus you get better resource usage through cached execution code etc...
> Google Jim Gray and look up some of his thinking on this.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
SQL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "sloan" <sloan@.ipass.net> wrote in message
> news:%23UU$cfXiGHA.3408@.TK2MSFTNGP05.phx.gbl...
that.
DotNet
http://groups.google.com/group/micr...993dab7

business
contention
working
a
managability,
>

Identity column jumps indefinitely

Guys,

Iam new to this forum, Hello to all...
Iam facing a problem in my application. Have recently noticed that my primary key column which is an " identity " with increment 1 being set.
But now iam noticing a various jumps in the number instead of 1. The numbers in the jump is not consistent.
Has anyone faced this kinda problem.
???many people have seen this situation

those who are using identity columns only to provide identity (uniqueness) do not see a problem at all

those who are concerned about gaps in the sequence of numbers, as representing a problem for their application, should re-design their application so that they don't rely on identity columns|||I agree with your comments not to use the identity on the application.
But in my case, i dont delete the records, it automatically jumps the numbers.
say for example a record is created with number 301 today morning
during afternoon there is another new record with number 899.
But why this jump is happening. Iam curious to know about it...|||yes, i'm curious too

when it gets close to the 2-billion number, you may want to have a look at it again

:)|||This is old info, off the top of head, but as I remember it:

Basically, the Identity feature 'grabs' a block of numbers, and doles them out. Not sure what the default is. Assume 100 (1-100). When the first insert happens, it gives out '1'.
For performance, the server grabs them in bunches, and saves the next, (101), so it doesn't need to keep getting locks for each insert. If the db server goes down, the next record will get '101'.

Don't use identity for consecutive numbering.

Jay Grubb
Technical Consultant
OpenLink Software
Web: http://www.openlinksw.com:
Product Weblogs:
Virtuoso: http://www.openlinksw.com/weblogs/virtuoso
UDA: http://www.openlinksw.com/weblogs/uda
Universal Data Access & Virtual Database Technology Providers