Create Numeric Sequence ID

Can this be written without tmp tables? (tmp tables simulate real tables)
Needed: an extra column indicating the correct sequence based on the order by
condition of databasename,appname.
create table #tmp1(appname varchar(50) null,databasename varchar(50),comment
varchar(200),active bit null,id int identity(1,1) not null)
insert into #tmp1(appname,databasename,comment,active) Select
'EDIBU','Archived','x','1'
insert into #tmp1(appname,databasename,comment,active) Select
'ASNTransfer','ASND','x','1'
insert into #tmp1(appname,databasename,comment,active) Select
'atcentral.exe','ATCentral','x','1'
insert into #tmp1(appname,databasename,comment,active) Select
'AtCentral.exe','OrderEntry','x','1'
insert into #tmp1(appname,databasename,comment,active) Select
'ATOMS.dbo.insTDemand','ATSystemProcessing','x','1 '
insert into #tmp1(appname,databasename,comment,active) Select
'ATOMS.dbo.spConvertFSCOs','EDID','x','1'
create table #tmp (idx int identity(1,1),appname varchar(50) null,
databasename varchar(50) null,comment varchar(200) null,active bit null,id
int null)
insert into #tmp(appname,databasename,comment,active,id)
select * from #tmp1 order by databasename,appname
select * from #tmp
drop table #tmp1
drop table #tmp
Regards,
Jamie
Sure...
SELECT
t1.appname,
t1.databasename,
t1.comment,
t1.active,
t1.id,
count(*)
FROM #tmp1 t1
JOIN #tmp1 t2 ON
t2.databasename <= t1.databasename
and
(t2.databasename < t1.databasename
or t2.appname <= t1.appname)
GROUP BY
t1.appname,
t1.databasename,
t1.comment,
t1.active,
t1.id
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:32FD08CE-A2A8-4BE3-93D0-0F9D740536DA@.microsoft.com...
> Can this be written without tmp tables? (tmp tables simulate real tables)
> Needed: an extra column indicating the correct sequence based on the order
> by
> condition of databasename,appname.
> create table #tmp1(appname varchar(50) null,databasename
> varchar(50),comment
> varchar(200),active bit null,id int identity(1,1) not null)
> insert into #tmp1(appname,databasename,comment,active) Select
> 'EDIBU','Archived','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'ASNTransfer','ASND','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'atcentral.exe','ATCentral','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'AtCentral.exe','OrderEntry','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'ATOMS.dbo.insTDemand','ATSystemProcessing','x','1 '
> insert into #tmp1(appname,databasename,comment,active) Select
> 'ATOMS.dbo.spConvertFSCOs','EDID','x','1'
> create table #tmp (idx int identity(1,1),appname varchar(50) null,
> databasename varchar(50) null,comment varchar(200) null,active bit null,id
> int null)
> insert into #tmp(appname,databasename,comment,active,id)
> select * from #tmp1 order by databasename,appname
> select * from #tmp
> drop table #tmp1
> drop table #tmp
> --
> Regards,
> Jamie
|||Which version of SS are you using?
select
appname, databasename, comment, active,
(
select count(*)
from dbo.t1 as b
where b.appname < a.appname
or (b.appname = a.appname and b.databasename <= a.databasename)
) as rn
from
dbo.t1 as a
order by
rn
-- 2005
select
appname, databasename, comment, active,
row_number() over(order by appname, databasename) as rn
from
dbo.t1
order by
rn
go
AMB
"thejamie" wrote:

> Can this be written without tmp tables? (tmp tables simulate real tables)
> Needed: an extra column indicating the correct sequence based on the order by
> condition of databasename,appname.
> create table #tmp1(appname varchar(50) null,databasename varchar(50),comment
> varchar(200),active bit null,id int identity(1,1) not null)
> insert into #tmp1(appname,databasename,comment,active) Select
> 'EDIBU','Archived','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'ASNTransfer','ASND','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'atcentral.exe','ATCentral','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'AtCentral.exe','OrderEntry','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'ATOMS.dbo.insTDemand','ATSystemProcessing','x','1 '
> insert into #tmp1(appname,databasename,comment,active) Select
> 'ATOMS.dbo.spConvertFSCOs','EDID','x','1'
> create table #tmp (idx int identity(1,1),appname varchar(50) null,
> databasename varchar(50) null,comment varchar(200) null,active bit null,id
> int null)
> insert into #tmp(appname,databasename,comment,active,id)
> select * from #tmp1 order by databasename,appname
> select * from #tmp
> drop table #tmp1
> drop table #tmp
> --
> Regards,
> Jamie
|||In 2005 can do this: (looking for a 2000 solution still)
select
b.rownum,a.id, a.databasename, a.appname
from
#tmp1a
inner join
(
SELECT ROW_NUMBER () OVER (ORDER BY databasename,appname) AS rowNum, ID
FROM #tmp1
) as b
on a.[id] = b.[id]
order by b.rownum
Regards,
Jamie
"thejamie" wrote:

> Can this be written without tmp tables? (tmp tables simulate real tables)
> Needed: an extra column indicating the correct sequence based on the order by
> condition of databasename,appname.
> create table #tmp1(appname varchar(50) null,databasename varchar(50),comment
> varchar(200),active bit null,id int identity(1,1) not null)
> insert into #tmp1(appname,databasename,comment,active) Select
> 'EDIBU','Archived','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'ASNTransfer','ASND','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'atcentral.exe','ATCentral','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'AtCentral.exe','OrderEntry','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'ATOMS.dbo.insTDemand','ATSystemProcessing','x','1 '
> insert into #tmp1(appname,databasename,comment,active) Select
> 'ATOMS.dbo.spConvertFSCOs','EDID','x','1'
> create table #tmp (idx int identity(1,1),appname varchar(50) null,
> databasename varchar(50) null,comment varchar(200) null,active bit null,id
> int null)
> insert into #tmp(appname,databasename,comment,active,id)
> select * from #tmp1 order by databasename,appname
> select * from #tmp
> drop table #tmp1
> drop table #tmp
> --
> Regards,
> Jamie
|||Alejandro,
Just a minor correction... (looking for databasename,appname order rather
than the other way around)
select
appname, databasename, comment, active,
(
select count(*)
from migrationdata as b
where b.databasename < a.databasename
or ( b.databasename = a.databasename and b.appname <= a.appname)
) as rn
from
migrationdata as a
order by
rn
and it looks like the one below works too but with only 172 records in my
actual database, there is no way to be sure at this point.
select
appname, databasename, comment, active,
(
select count(*)
from migrationdata as b
where b.databasename < a.databasename
or ( b.databasename < a.databasename and b.appname <= a.appname)
) as rn
from
migrationdata as a
order by
rn
Regards,
Jamie
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Which version of SS are you using?
> select
> appname, databasename, comment, active,
> (
> select count(*)
> from dbo.t1 as b
> where b.appname < a.appname
> or (b.appname = a.appname and b.databasename <= a.databasename)
> ) as rn
> from
> dbo.t1 as a
> order by
> rn
> -- 2005
> select
> appname, databasename, comment, active,
> row_number() over(order by appname, databasename) as rn
> from
> dbo.t1
> order by
> rn
> go
>
> AMB
> "thejamie" wrote:
|||Hi thejamie,

> Just a minor correction... (looking for databasename,appname order rather
> than the other way around)
You got it.

> and it looks like the one below works too but with only 172 records in my
> actual database, there is no way to be sure at this point.
> select
> appname, databasename, comment, active,
> (
> select count(*)
> from migrationdata as b
> where b.databasename < a.databasename
> or ( b.databasename < a.databasename and b.appname <= a.appname)
> ) as rn
> from
> migrationdata as a
> order by
> rn
It could be working because of the data you have right now, but that is not
the way to proceed when you need a tie breaker.
Example:
declare @.t table (
databasename varchar(50),
appname varchar(50)
)
insert into @.t values('db1', 'app1')
insert into @.t values('db1', 'app2')
select
appname, databasename,
(
select
count(*)
from
@.t as b
where
b.databasename < a.databasename
or ( b.databasename = a.databasename and b.appname <= a.appname)
) as rn
from
@.t as a
order by
rn
-- wrong result
select
appname, databasename,
(
select
count(*)
from
@.t as b
where
b.databasename < a.databasename
or ( b.databasename < a.databasename and b.appname <= a.appname)
) as rn
from
@.t as a
order by
rn
go
AMB
"thejamie" wrote:
[vbcol=seagreen]
> Alejandro,
> Just a minor correction... (looking for databasename,appname order rather
> than the other way around)
> select
> appname, databasename, comment, active,
> (
> select count(*)
> from migrationdata as b
> where b.databasename < a.databasename
> or ( b.databasename = a.databasename and b.appname <= a.appname)
> ) as rn
> from
> migrationdata as a
> order by
> rn
> and it looks like the one below works too but with only 172 records in my
> actual database, there is no way to be sure at this point.
> select
> appname, databasename, comment, active,
> (
> select count(*)
> from migrationdata as b
> where b.databasename < a.databasename
> or ( b.databasename < a.databasename and b.appname <= a.appname)
> ) as rn
> from
> migrationdata as a
> order by
> rn
>
> --
> Regards,
> Jamie
>
> "Alejandro Mesa" wrote:
|||Toward a better understanding of traditional ranking queries:
http://beyondsql.blogspot.com/2007/06/dataphor-sql-visualizing-ranking-query.html
|||Thanks Steve.
Regards,
Jamie
"Steve Dassin" wrote:

> Toward a better understanding of traditional ranking queries:
> http://beyondsql.blogspot.com/2007/06/dataphor-sql-visualizing-ranking-query.html
>
>
|||Yep, missed the tie breaker... thanks
Regards,
Jamie
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Hi thejamie,
>
> You got it.
>
> It could be working because of the data you have right now, but that is not
> the way to proceed when you need a tie breaker.
> Example:
> declare @.t table (
> databasename varchar(50),
> appname varchar(50)
> )
> insert into @.t values('db1', 'app1')
> insert into @.t values('db1', 'app2')
> select
> appname, databasename,
> (
> select
> count(*)
> from
> @.t as b
> where
> b.databasename < a.databasename
> or ( b.databasename = a.databasename and b.appname <= a.appname)
> ) as rn
> from
> @.t as a
> order by
> rn
> -- wrong result
> select
> appname, databasename,
> (
> select
> count(*)
> from
> @.t as b
> where
> b.databasename < a.databasename
> or ( b.databasename < a.databasename and b.appname <= a.appname)
> ) as rn
> from
> @.t as a
> order by
> rn
> go
>
> AMB
>
> "thejamie" wrote:

Create Numeric Sequence ID

Can this be written without tmp tables? (tmp tables simulate real tables)
Needed: an extra column indicating the correct sequence based on the order b
y
condition of databasename,appname.
create table #tmp1(appname varchar(50) null,databasename varchar(50),comment
varchar(200),active bit null,id int identity(1,1) not null)
insert into #tmp1(appname,databasename,comment,activ
e) Select
'EDIBU','Archived','x','1'
insert into #tmp1(appname,databasename,comment,activ
e) Select
'ASNTransfer','ASND','x','1'
insert into #tmp1(appname,databasename,comment,activ
e) Select
'atcentral.exe','ATCentral','x','1'
insert into #tmp1(appname,databasename,comment,activ
e) Select
'AtCentral.exe','OrderEntry','x','1'
insert into #tmp1(appname,databasename,comment,activ
e) Select
'ATOMS.dbo. insTDemand','ATSystemProcessing','x','1'
insert into #tmp1(appname,databasename,comment,activ
e) Select
'ATOMS.dbo.spConvertFSCOs','EDID','x','1'
create table #tmp (idx int identity(1,1),appname varchar(50) null,
databasename varchar(50) null,comment varchar(200) null,active bit null,id
int null)
insert into #tmp(appname,databasename,comment,active
,id)
select * from #tmp1 order by databasename,appname
select * from #tmp
drop table #tmp1
drop table #tmp
Regards,
JamieSure...
SELECT
t1.appname,
t1.databasename,
t1.comment,
t1.active,
t1.id,
count(*)
FROM #tmp1 t1
JOIN #tmp1 t2 ON
t2.databasename <= t1.databasename
and
(t2.databasename < t1.databasename
or t2.appname <= t1.appname)
GROUP BY
t1.appname,
t1.databasename,
t1.comment,
t1.active,
t1.id
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:32FD08CE-A2A8-4BE3-93D0-0F9D740536DA@.microsoft.com...
> Can this be written without tmp tables? (tmp tables simulate real tables)
> Needed: an extra column indicating the correct sequence based on the order
> by
> condition of databasename,appname.
> create table #tmp1(appname varchar(50) null,databasename
> varchar(50),comment
> varchar(200),active bit null,id int identity(1,1) not null)
> insert into #tmp1(appname,databasename,comment,activ
e) Select
> 'EDIBU','Archived','x','1'
> insert into #tmp1(appname,databasename,comment,activ
e) Select
> 'ASNTransfer','ASND','x','1'
> insert into #tmp1(appname,databasename,comment,activ
e) Select
> 'atcentral.exe','ATCentral','x','1'
> insert into #tmp1(appname,databasename,comment,activ
e) Select
> 'AtCentral.exe','OrderEntry','x','1'
> insert into #tmp1(appname,databasename,comment,activ
e) Select
> 'ATOMS.dbo. insTDemand','ATSystemProcessing','x','1'
> insert into #tmp1(appname,databasename,comment,activ
e) Select
> 'ATOMS.dbo.spConvertFSCOs','EDID','x','1'
> create table #tmp (idx int identity(1,1),appname varchar(50) null,
> databasename varchar(50) null,comment varchar(200) null,active bit null,id
> int null)
> insert into #tmp(appname,databasename,comment,active
,id)
> select * from #tmp1 order by databasename,appname
> select * from #tmp
> drop table #tmp1
> drop table #tmp
> --
> Regards,
> Jamie|||Which version of SS are you using?
select
appname, databasename, comment, active,
(
select count(*)
from dbo.t1 as b
where b.appname < a.appname
or (b.appname = a.appname and b.databasename <= a.databasename)
) as rn
from
dbo.t1 as a
order by
rn
-- 2005
select
appname, databasename, comment, active,
row_number() over(order by appname, databasename) as rn
from
dbo.t1
order by
rn
go
AMB
"thejamie" wrote:

> Can this be written without tmp tables? (tmp tables simulate real tables)
> Needed: an extra column indicating the correct sequence based on the order
by
> condition of databasename,appname.
> create table #tmp1(appname varchar(50) null,databasename varchar(50),comme
nt
> varchar(200),active bit null,id int identity(1,1) not null)
> insert into #tmp1(appname,databasename,comment,activ
e) Select
> 'EDIBU','Archived','x','1'
> insert into #tmp1(appname,databasename,comment,activ
e) Select
> 'ASNTransfer','ASND','x','1'
> insert into #tmp1(appname,databasename,comment,activ
e) Select
> 'atcentral.exe','ATCentral','x','1'
> insert into #tmp1(appname,databasename,comment,activ
e) Select
> 'AtCentral.exe','OrderEntry','x','1'
> insert into #tmp1(appname,databasename,comment,activ
e) Select
> 'ATOMS.dbo. insTDemand','ATSystemProcessing','x','1'
> insert into #tmp1(appname,databasename,comment,activ
e) Select
> 'ATOMS.dbo.spConvertFSCOs','EDID','x','1'
> create table #tmp (idx int identity(1,1),appname varchar(50) null,
> databasename varchar(50) null,comment varchar(200) null,active bit null,id
> int null)
> insert into #tmp(appname,databasename,comment,active
,id)
> select * from #tmp1 order by databasename,appname
> select * from #tmp
> drop table #tmp1
> drop table #tmp
> --
> Regards,
> Jamie|||Alejandro,
Just a minor correction... (looking for databasename,appname order rather
than the other way around)
select
appname, databasename, comment, active,
(
select count(*)
from migrationdata as b
where b.databasename < a.databasename
or ( b.databasename = a.databasename and b.appname <= a.appname)
) as rn
from
migrationdata as a
order by
rn
and it looks like the one below works too but with only 172 records in my
actual database, there is no way to be sure at this point.
select
appname, databasename, comment, active,
(
select count(*)
from migrationdata as b
where b.databasename < a.databasename
or ( b.databasename < a.databasename and b.appname <= a.appname)
) as rn
from
migrationdata as a
order by
rn
Regards,
Jamie
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Which version of SS are you using?
> select
> appname, databasename, comment, active,
> (
> select count(*)
> from dbo.t1 as b
> where b.appname < a.appname
> or (b.appname = a.appname and b.databasename <= a.databasename)
> ) as rn
> from
> dbo.t1 as a
> order by
> rn
> -- 2005
> select
> appname, databasename, comment, active,
> row_number() over(order by appname, databasename) as rn
> from
> dbo.t1
> order by
> rn
> go
>
> AMB
> "thejamie" wrote:
>|||Hi thejamie,

> Just a minor correction... (looking for databasename,appname order rather
> than the other way around)
You got it.

> and it looks like the one below works too but with only 172 records in my
> actual database, there is no way to be sure at this point.
> select
> appname, databasename, comment, active,
> (
> select count(*)
> from migrationdata as b
> where b.databasename < a.databasename
> or ( b.databasename < a.databasename and b.appname <= a.appname)
> ) as rn
> from
> migrationdata as a
> order by
> rn
It could be working because of the data you have right now, but that is not
the way to proceed when you need a tie breaker.
Example:
declare @.t table (
databasename varchar(50),
appname varchar(50)
)
insert into @.t values('db1', 'app1')
insert into @.t values('db1', 'app2')
select
appname, databasename,
(
select
count(*)
from
@.t as b
where
b.databasename < a.databasename
or ( b.databasename = a.databasename and b.appname <= a.appname)
) as rn
from
@.t as a
order by
rn
-- wrong result
select
appname, databasename,
(
select
count(*)
from
@.t as b
where
b.databasename < a.databasename
or ( b.databasename < a.databasename and b.appname <= a.appname)
) as rn
from
@.t as a
order by
rn
go
AMB
"thejamie" wrote:
[vbcol=seagreen]
> Alejandro,
> Just a minor correction... (looking for databasename,appname order rather
> than the other way around)
> select
> appname, databasename, comment, active,
> (
> select count(*)
> from migrationdata as b
> where b.databasename < a.databasename
> or ( b.databasename = a.databasename and b.appname <= a.appname)
> ) as rn
> from
> migrationdata as a
> order by
> rn
> and it looks like the one below works too but with only 172 records in my
> actual database, there is no way to be sure at this point.
> select
> appname, databasename, comment, active,
> (
> select count(*)
> from migrationdata as b
> where b.databasename < a.databasename
> or ( b.databasename < a.databasename and b.appname <= a.appname)
> ) as rn
> from
> migrationdata as a
> order by
> rn
>
> --
> Regards,
> Jamie
>
> "Alejandro Mesa" wrote:
>|||Toward a better understanding of traditional ranking queries:
[url]http://beyondsql.blogspot.com/2007/06/dataphor-sql-visualizing-ranking-query.html[
/url]|||Thanks Steve.
--
Regards,
Jamie
"Steve Dassin" wrote:

> Toward a better understanding of traditional ranking queries:
> http://beyondsql.blogspot.com/2007/...ry.htm
l
>
>|||Yep, missed the tie breaker... thanks
--
Regards,
Jamie
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Hi thejamie,
>
> You got it.
>
> It could be working because of the data you have right now, but that is no
t
> the way to proceed when you need a tie breaker.
> Example:
> declare @.t table (
> databasename varchar(50),
> appname varchar(50)
> )
> insert into @.t values('db1', 'app1')
> insert into @.t values('db1', 'app2')
> select
> appname, databasename,
> (
> select
> count(*)
> from
> @.t as b
> where
> b.databasename < a.databasename
> or ( b.databasename = a.databasename and b.appname <= a.appname)
> ) as rn
> from
> @.t as a
> order by
> rn
> -- wrong result
> select
> appname, databasename,
> (
> select
> count(*)
> from
> @.t as b
> where
> b.databasename < a.databasename
> or ( b.databasename < a.databasename and b.appname <= a.appname)
> ) as rn
> from
> @.t as a
> order by
> rn
> go
>
> AMB
>
> "thejamie" wrote:
>

Create Numeric Sequence ID

Can this be written without tmp tables? (tmp tables simulate real tables)
Needed: an extra column indicating the correct sequence based on the order by
condition of databasename,appname.
create table #tmp1(appname varchar(50) null,databasename varchar(50),comment
varchar(200),active bit null,id int identity(1,1) not null)
insert into #tmp1(appname,databasename,comment,active) Select
'EDIBU','Archived','x','1'
insert into #tmp1(appname,databasename,comment,active) Select
'ASNTransfer','ASND','x','1'
insert into #tmp1(appname,databasename,comment,active) Select
'atcentral.exe','ATCentral','x','1'
insert into #tmp1(appname,databasename,comment,active) Select
'AtCentral.exe','OrderEntry','x','1'
insert into #tmp1(appname,databasename,comment,active) Select
'ATOMS.dbo.insTDemand','ATSystemProcessing','x','1'
insert into #tmp1(appname,databasename,comment,active) Select
'ATOMS.dbo.spConvertFSCOs','EDID','x','1'
create table #tmp (idx int identity(1,1),appname varchar(50) null,
databasename varchar(50) null,comment varchar(200) null,active bit null,id
int null)
insert into #tmp(appname,databasename,comment,active,id)
select * from #tmp1 order by databasename,appname
select * from #tmp
drop table #tmp1
drop table #tmp
--
Regards,
JamieSure...
SELECT
t1.appname,
t1.databasename,
t1.comment,
t1.active,
t1.id,
count(*)
FROM #tmp1 t1
JOIN #tmp1 t2 ON
t2.databasename <= t1.databasename
and
(t2.databasename < t1.databasename
or t2.appname <= t1.appname)
GROUP BY
t1.appname,
t1.databasename,
t1.comment,
t1.active,
t1.id
--
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:32FD08CE-A2A8-4BE3-93D0-0F9D740536DA@.microsoft.com...
> Can this be written without tmp tables? (tmp tables simulate real tables)
> Needed: an extra column indicating the correct sequence based on the order
> by
> condition of databasename,appname.
> create table #tmp1(appname varchar(50) null,databasename
> varchar(50),comment
> varchar(200),active bit null,id int identity(1,1) not null)
> insert into #tmp1(appname,databasename,comment,active) Select
> 'EDIBU','Archived','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'ASNTransfer','ASND','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'atcentral.exe','ATCentral','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'AtCentral.exe','OrderEntry','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'ATOMS.dbo.insTDemand','ATSystemProcessing','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'ATOMS.dbo.spConvertFSCOs','EDID','x','1'
> create table #tmp (idx int identity(1,1),appname varchar(50) null,
> databasename varchar(50) null,comment varchar(200) null,active bit null,id
> int null)
> insert into #tmp(appname,databasename,comment,active,id)
> select * from #tmp1 order by databasename,appname
> select * from #tmp
> drop table #tmp1
> drop table #tmp
> --
> Regards,
> Jamie|||Which version of SS are you using?
select
appname, databasename, comment, active,
(
select count(*)
from dbo.t1 as b
where b.appname < a.appname
or (b.appname = a.appname and b.databasename <= a.databasename)
) as rn
from
dbo.t1 as a
order by
rn
-- 2005
select
appname, databasename, comment, active,
row_number() over(order by appname, databasename) as rn
from
dbo.t1
order by
rn
go
AMB
"thejamie" wrote:
> Can this be written without tmp tables? (tmp tables simulate real tables)
> Needed: an extra column indicating the correct sequence based on the order by
> condition of databasename,appname.
> create table #tmp1(appname varchar(50) null,databasename varchar(50),comment
> varchar(200),active bit null,id int identity(1,1) not null)
> insert into #tmp1(appname,databasename,comment,active) Select
> 'EDIBU','Archived','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'ASNTransfer','ASND','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'atcentral.exe','ATCentral','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'AtCentral.exe','OrderEntry','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'ATOMS.dbo.insTDemand','ATSystemProcessing','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'ATOMS.dbo.spConvertFSCOs','EDID','x','1'
> create table #tmp (idx int identity(1,1),appname varchar(50) null,
> databasename varchar(50) null,comment varchar(200) null,active bit null,id
> int null)
> insert into #tmp(appname,databasename,comment,active,id)
> select * from #tmp1 order by databasename,appname
> select * from #tmp
> drop table #tmp1
> drop table #tmp
> --
> Regards,
> Jamie|||In 2005 can do this: (looking for a 2000 solution still)
select
b.rownum,a.id, a.databasename, a.appname
from
#tmp1a
inner join
(
SELECT ROW_NUMBER () OVER (ORDER BY databasename,appname) AS rowNum, ID
FROM #tmp1
) as b
on a.[id] = b.[id]
order by b.rownum
--
Regards,
Jamie
"thejamie" wrote:
> Can this be written without tmp tables? (tmp tables simulate real tables)
> Needed: an extra column indicating the correct sequence based on the order by
> condition of databasename,appname.
> create table #tmp1(appname varchar(50) null,databasename varchar(50),comment
> varchar(200),active bit null,id int identity(1,1) not null)
> insert into #tmp1(appname,databasename,comment,active) Select
> 'EDIBU','Archived','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'ASNTransfer','ASND','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'atcentral.exe','ATCentral','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'AtCentral.exe','OrderEntry','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'ATOMS.dbo.insTDemand','ATSystemProcessing','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'ATOMS.dbo.spConvertFSCOs','EDID','x','1'
> create table #tmp (idx int identity(1,1),appname varchar(50) null,
> databasename varchar(50) null,comment varchar(200) null,active bit null,id
> int null)
> insert into #tmp(appname,databasename,comment,active,id)
> select * from #tmp1 order by databasename,appname
> select * from #tmp
> drop table #tmp1
> drop table #tmp
> --
> Regards,
> Jamie|||Alejandro,
Just a minor correction... (looking for databasename,appname order rather
than the other way around)
select
appname, databasename, comment, active,
(
select count(*)
from migrationdata as b
where b.databasename < a.databasename
or ( b.databasename = a.databasename and b.appname <= a.appname)
) as rn
from
migrationdata as a
order by
rn
and it looks like the one below works too but with only 172 records in my
actual database, there is no way to be sure at this point.
select
appname, databasename, comment, active,
(
select count(*)
from migrationdata as b
where b.databasename < a.databasename
or ( b.databasename < a.databasename and b.appname <= a.appname)
) as rn
from
migrationdata as a
order by
rn
Regards,
Jamie
"Alejandro Mesa" wrote:
> Which version of SS are you using?
> select
> appname, databasename, comment, active,
> (
> select count(*)
> from dbo.t1 as b
> where b.appname < a.appname
> or (b.appname = a.appname and b.databasename <= a.databasename)
> ) as rn
> from
> dbo.t1 as a
> order by
> rn
> -- 2005
> select
> appname, databasename, comment, active,
> row_number() over(order by appname, databasename) as rn
> from
> dbo.t1
> order by
> rn
> go
>
> AMB
> "thejamie" wrote:
> > Can this be written without tmp tables? (tmp tables simulate real tables)
> > Needed: an extra column indicating the correct sequence based on the order by
> > condition of databasename,appname.
> >
> > create table #tmp1(appname varchar(50) null,databasename varchar(50),comment
> > varchar(200),active bit null,id int identity(1,1) not null)
> >
> > insert into #tmp1(appname,databasename,comment,active) Select
> > 'EDIBU','Archived','x','1'
> > insert into #tmp1(appname,databasename,comment,active) Select
> > 'ASNTransfer','ASND','x','1'
> > insert into #tmp1(appname,databasename,comment,active) Select
> > 'atcentral.exe','ATCentral','x','1'
> > insert into #tmp1(appname,databasename,comment,active) Select
> > 'AtCentral.exe','OrderEntry','x','1'
> > insert into #tmp1(appname,databasename,comment,active) Select
> > 'ATOMS.dbo.insTDemand','ATSystemProcessing','x','1'
> > insert into #tmp1(appname,databasename,comment,active) Select
> > 'ATOMS.dbo.spConvertFSCOs','EDID','x','1'
> >
> > create table #tmp (idx int identity(1,1),appname varchar(50) null,
> > databasename varchar(50) null,comment varchar(200) null,active bit null,id
> > int null)
> > insert into #tmp(appname,databasename,comment,active,id)
> > select * from #tmp1 order by databasename,appname
> > select * from #tmp
> >
> > drop table #tmp1
> > drop table #tmp
> >
> > --
> > Regards,
> > Jamie|||Hi thejamie,
> Just a minor correction... (looking for databasename,appname order rather
> than the other way around)
You got it.
> and it looks like the one below works too but with only 172 records in my
> actual database, there is no way to be sure at this point.
> select
> appname, databasename, comment, active,
> (
> select count(*)
> from migrationdata as b
> where b.databasename < a.databasename
> or ( b.databasename < a.databasename and b.appname <= a.appname)
> ) as rn
> from
> migrationdata as a
> order by
> rn
It could be working because of the data you have right now, but that is not
the way to proceed when you need a tie breaker.
Example:
declare @.t table (
databasename varchar(50),
appname varchar(50)
)
insert into @.t values('db1', 'app1')
insert into @.t values('db1', 'app2')
select
appname, databasename,
(
select
count(*)
from
@.t as b
where
b.databasename < a.databasename
or ( b.databasename = a.databasename and b.appname <= a.appname)
) as rn
from
@.t as a
order by
rn
-- wrong result
select
appname, databasename,
(
select
count(*)
from
@.t as b
where
b.databasename < a.databasename
or ( b.databasename < a.databasename and b.appname <= a.appname)
) as rn
from
@.t as a
order by
rn
go
AMB
"thejamie" wrote:
> Alejandro,
> Just a minor correction... (looking for databasename,appname order rather
> than the other way around)
> select
> appname, databasename, comment, active,
> (
> select count(*)
> from migrationdata as b
> where b.databasename < a.databasename
> or ( b.databasename = a.databasename and b.appname <= a.appname)
> ) as rn
> from
> migrationdata as a
> order by
> rn
> and it looks like the one below works too but with only 172 records in my
> actual database, there is no way to be sure at this point.
> select
> appname, databasename, comment, active,
> (
> select count(*)
> from migrationdata as b
> where b.databasename < a.databasename
> or ( b.databasename < a.databasename and b.appname <= a.appname)
> ) as rn
> from
> migrationdata as a
> order by
> rn
>
> --
> Regards,
> Jamie
>
> "Alejandro Mesa" wrote:
> > Which version of SS are you using?
> >
> > select
> > appname, databasename, comment, active,
> > (
> > select count(*)
> > from dbo.t1 as b
> > where b.appname < a.appname
> > or (b.appname = a.appname and b.databasename <= a.databasename)
> > ) as rn
> > from
> > dbo.t1 as a
> > order by
> > rn
> >
> > -- 2005
> > select
> > appname, databasename, comment, active,
> > row_number() over(order by appname, databasename) as rn
> > from
> > dbo.t1
> > order by
> > rn
> > go
> >
> >
> > AMB
> >
> > "thejamie" wrote:
> >
> > > Can this be written without tmp tables? (tmp tables simulate real tables)
> > > Needed: an extra column indicating the correct sequence based on the order by
> > > condition of databasename,appname.
> > >
> > > create table #tmp1(appname varchar(50) null,databasename varchar(50),comment
> > > varchar(200),active bit null,id int identity(1,1) not null)
> > >
> > > insert into #tmp1(appname,databasename,comment,active) Select
> > > 'EDIBU','Archived','x','1'
> > > insert into #tmp1(appname,databasename,comment,active) Select
> > > 'ASNTransfer','ASND','x','1'
> > > insert into #tmp1(appname,databasename,comment,active) Select
> > > 'atcentral.exe','ATCentral','x','1'
> > > insert into #tmp1(appname,databasename,comment,active) Select
> > > 'AtCentral.exe','OrderEntry','x','1'
> > > insert into #tmp1(appname,databasename,comment,active) Select
> > > 'ATOMS.dbo.insTDemand','ATSystemProcessing','x','1'
> > > insert into #tmp1(appname,databasename,comment,active) Select
> > > 'ATOMS.dbo.spConvertFSCOs','EDID','x','1'
> > >
> > > create table #tmp (idx int identity(1,1),appname varchar(50) null,
> > > databasename varchar(50) null,comment varchar(200) null,active bit null,id
> > > int null)
> > > insert into #tmp(appname,databasename,comment,active,id)
> > > select * from #tmp1 order by databasename,appname
> > > select * from #tmp
> > >
> > > drop table #tmp1
> > > drop table #tmp
> > >
> > > --
> > > Regards,
> > > Jamie|||Toward a better understanding of traditional ranking queries:
http://beyondsql.blogspot.com/2007/06/dataphor-sql-visualizing-ranking-query.html|||Thanks Steve.
--
Regards,
Jamie
"Steve Dassin" wrote:
> Toward a better understanding of traditional ranking queries:
> http://beyondsql.blogspot.com/2007/06/dataphor-sql-visualizing-ranking-query.html
>
>|||Yep, missed the tie breaker... thanks
--
Regards,
Jamie
"Alejandro Mesa" wrote:
> Hi thejamie,
> > Just a minor correction... (looking for databasename,appname order rather
> > than the other way around)
> You got it.
> > and it looks like the one below works too but with only 172 records in my
> > actual database, there is no way to be sure at this point.
> >
> > select
> > appname, databasename, comment, active,
> > (
> > select count(*)
> > from migrationdata as b
> > where b.databasename < a.databasename
> > or ( b.databasename < a.databasename and b.appname <= a.appname)
> > ) as rn
> > from
> > migrationdata as a
> > order by
> > rn
> It could be working because of the data you have right now, but that is not
> the way to proceed when you need a tie breaker.
> Example:
> declare @.t table (
> databasename varchar(50),
> appname varchar(50)
> )
> insert into @.t values('db1', 'app1')
> insert into @.t values('db1', 'app2')
> select
> appname, databasename,
> (
> select
> count(*)
> from
> @.t as b
> where
> b.databasename < a.databasename
> or ( b.databasename = a.databasename and b.appname <= a.appname)
> ) as rn
> from
> @.t as a
> order by
> rn
> -- wrong result
> select
> appname, databasename,
> (
> select
> count(*)
> from
> @.t as b
> where
> b.databasename < a.databasename
> or ( b.databasename < a.databasename and b.appname <= a.appname)
> ) as rn
> from
> @.t as a
> order by
> rn
> go
>
> AMB
>
> "thejamie" wrote:
> > Alejandro,
> > Just a minor correction... (looking for databasename,appname order rather
> > than the other way around)
> >
> > select
> > appname, databasename, comment, active,
> > (
> > select count(*)
> > from migrationdata as b
> > where b.databasename < a.databasename
> > or ( b.databasename = a.databasename and b.appname <= a.appname)
> > ) as rn
> > from
> > migrationdata as a
> > order by
> > rn
> >
> > and it looks like the one below works too but with only 172 records in my
> > actual database, there is no way to be sure at this point.
> >
> > select
> > appname, databasename, comment, active,
> > (
> > select count(*)
> > from migrationdata as b
> > where b.databasename < a.databasename
> > or ( b.databasename < a.databasename and b.appname <= a.appname)
> > ) as rn
> > from
> > migrationdata as a
> > order by
> > rn
> >
> >
> > --
> > Regards,
> > Jamie
> >
> >
> > "Alejandro Mesa" wrote:
> >
> > > Which version of SS are you using?
> > >
> > > select
> > > appname, databasename, comment, active,
> > > (
> > > select count(*)
> > > from dbo.t1 as b
> > > where b.appname < a.appname
> > > or (b.appname = a.appname and b.databasename <= a.databasename)
> > > ) as rn
> > > from
> > > dbo.t1 as a
> > > order by
> > > rn
> > >
> > > -- 2005
> > > select
> > > appname, databasename, comment, active,
> > > row_number() over(order by appname, databasename) as rn
> > > from
> > > dbo.t1
> > > order by
> > > rn
> > > go
> > >
> > >
> > > AMB
> > >
> > > "thejamie" wrote:
> > >
> > > > Can this be written without tmp tables? (tmp tables simulate real tables)
> > > > Needed: an extra column indicating the correct sequence based on the order by
> > > > condition of databasename,appname.
> > > >
> > > > create table #tmp1(appname varchar(50) null,databasename varchar(50),comment
> > > > varchar(200),active bit null,id int identity(1,1) not null)
> > > >
> > > > insert into #tmp1(appname,databasename,comment,active) Select
> > > > 'EDIBU','Archived','x','1'
> > > > insert into #tmp1(appname,databasename,comment,active) Select
> > > > 'ASNTransfer','ASND','x','1'
> > > > insert into #tmp1(appname,databasename,comment,active) Select
> > > > 'atcentral.exe','ATCentral','x','1'
> > > > insert into #tmp1(appname,databasename,comment,active) Select
> > > > 'AtCentral.exe','OrderEntry','x','1'
> > > > insert into #tmp1(appname,databasename,comment,active) Select
> > > > 'ATOMS.dbo.insTDemand','ATSystemProcessing','x','1'
> > > > insert into #tmp1(appname,databasename,comment,active) Select
> > > > 'ATOMS.dbo.spConvertFSCOs','EDID','x','1'
> > > >
> > > > create table #tmp (idx int identity(1,1),appname varchar(50) null,
> > > > databasename varchar(50) null,comment varchar(200) null,active bit null,id
> > > > int null)
> > > > insert into #tmp(appname,databasename,comment,active,id)
> > > > select * from #tmp1 order by databasename,appname
> > > > select * from #tmp
> > > >
> > > > drop table #tmp1
> > > > drop table #tmp
> > > >
> > > > --
> > > > Regards,
> > > > Jamie

Create index before or after insert?

I have a number of remote sql servers from which I collect data. Said data i
s
inserted into #temp tables, such as for example:
CREATE TABLE dbo.#tmp
(
x int not null primary key
, y int not null
)
INSERT INTO dbo.#tmp (x, y)
SELECT x, y FROM server.db.dbo.table
For sync'ing this data with local tables it is adventageous to have an index
on y, x:
CREATE NONCLUSTERED INDEX IX_tmp
ON dbo.#tmp (y, x)
Does anyone see any reason to create that index before or after inserting
the records? I.e. is scenario (A) or (B) below better?
(A) 1. Create temp table; 2. Define index; 3. Insert records
(B) 1. Create temp table; 2. Insert records; 3. Create index
My gut tells me that it's a wash, and keeping the index definition with the
table (in code) is better for maintainability, but there's probably no
performance benefit either way. Or maybe there is a performance benefit to
one that I can't think of?
Comments?
Thanks - KenKH wrote:
> I have a number of remote sql servers from which I collect data. Said
> data is inserted into #temp tables, such as for example:
> CREATE TABLE dbo.#tmp
> (
> x int not null primary key
> , y int not null
> )
> INSERT INTO dbo.#tmp (x, y)
> SELECT x, y FROM server.db.dbo.table
> For sync'ing this data with local tables it is adventageous to have
Temp table usage in stored procedures can be a source of recompilation.
To avoid recompiles (which are costly), you should try to avoid
interleaving DML and DDL statements related to temp tables. Therefore,
you are better off defining all your temp tables up front and creating
indexes on them before inserting or otherwise manipulating data in the
tables.
David Gugick
Imceda Software
www.imceda.com|||If you are doing Bulk Insert of a large number of records at once...
Then drop and recreate the indices. I quote from Books OnLine, From the
Bulk Insert Entry.
"If nonclustered indexes are also present on the table, drop these before
copying data into the table. It is generally faster to bulk copy data into a
table without nonclustered indexes, and then to re-create the nonclustered
indexes, rather than bulk copy data into a table with the nonclustered
indexes in place."
The only exception is when you have a clustered Index on teh table, AND you
have the luxury of pre-sorting the data in in the same Order as they will be
in the Clusterd Index (Therefore Inserting the records in Clustered Index
Order). In THis special case, leave the CLustered Index on the table during
the Insert.
"David Gugick" wrote:

> KH wrote:
> Temp table usage in stored procedures can be a source of recompilation.
> To avoid recompiles (which are costly), you should try to avoid
> interleaving DML and DDL statements related to temp tables. Therefore,
> you are better off defining all your temp tables up front and creating
> indexes on them before inserting or otherwise manipulating data in the
> tables.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>