Find the size of an index row

Hey Guys,
I would like to enumerate all the indexes in a table and find the size of
the index, when I mean the size not the total space occupied but the size of
each row of the index. Eg: Table EMP has id bigint, name varchar(64),
address varchar(64), phone bigint, zip int, etc. Index 1 (id, name), Index
2(zip, phone), Index 3(phone) Each row in this table could occupy 8+64+64+8+4
bytes. So each index associated with each row would occupy 8+64 + 8+8 + 8.
That's what I want. This would be used in later calculating the required
size based on number of records to be inserted.
yodarules wrote:
> Hey Guys,
> I would like to enumerate all the indexes in a table and find the
> size of the index, when I mean the size not the total space occupied
> but the size of each row of the index. Eg: Table EMP has id bigint,
> name varchar(64), address varchar(64), phone bigint, zip int, etc.
> Index 1 (id, name), Index 2(zip, phone), Index 3(phone) Each row in
> this table could occupy 8+64+64+8+4 bytes. So each index associated
> with each row would occupy 8+64 + 8+8 + 8. That's what I want. This
> would be used in later calculating the required size based on number
> of records to be inserted.
See "Estimating the Size of a Table with a Clustered Index" in BOL.
David Gugick
Quest Software
www.quest.com
|||Thanks David,
But thats not what I want, as I have explained in my post earlier. This
tells you the method to calcualte the space, which is what I might do when I
try to achieve my final goal. But the immediate goal is to dynamically find
the size of each non-clustered index on that table. The reason why I want to
do it dynamically is that, if I add or remove indexes, my SP should be able
to work with that rather than a hard coded value.
"David Gugick" wrote:

> yodarules wrote:
> See "Estimating the Size of a Table with a Clustered Index" in BOL.
> --
> David Gugick
> Quest Software
> www.quest.com
>
|||A couple of thoughts on your question.
1) SQL Server stores everything in 8KB pages and stores pages in 8 page
extents. Based on the fill factor and size of your index SQL Server will fit
as many rows from your index within the page as it can.
2) A non-clustered index always contains a reference to the key of the
clustered index or a pointer to a place in the heap if no clustered index
exists, in addition to the data in the non-clustered index. Additionally, a
varchar field does not always take up all 8 bytes. Varchars are variable
length and only stored with the bytes taken up by the value within the field.
So, your example is not actually accurate in how big a row of the index (and
it is further dependent on whether the index is clustered or non-clustered)
would be.
I'm not sure what exactly you are trying to do. But, in SQL 2000 you can
use DBCC SHOWCONTIG to see how many pages and extents an index takes up. It
will also show you if there is fragmentation in those pages and extents. In
SQL 2005 this command is replaced by the DMV dm_db_index_physical_stats.
dm_db_index_physical_stats also provides values for min,avg and max record
size. Which might be what you are looking for. I don't know if you can find
that data for SQL 2000.
The article in BOL that David pointed you to is a good illustration of how
that calculation would be done manually for a particular table and its
indexes.
HTH
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"David Gugick" wrote:

> yodarules wrote:
> See "Estimating the Size of a Table with a Clustered Index" in BOL.
> --
> David Gugick
> Quest Software
> www.quest.com
>
|||I totally understand and am aware of the points mentioned. The main reason
why I want to do this to for size estimation. When we archive and delete
some records we store the number of rows deleted. When we reload the data
back, we want to do a space estimation and see if there's enough space to do
this operation. Its actaully much more complicated than what I have just
explained, but thats the idea. I don't want to do spaceused before and after
for various other reasons and this seems to be the best case for us. If I
cannot dynamically find index sizes, then I have to hard code it some where,
again we are happy with an approximation, hence don't really care about
varchar, etc. Actually the tables I'm interested in doing this don't have
varchar.
"Ryan Powers" wrote:
[vbcol=seagreen]
> A couple of thoughts on your question.
> 1) SQL Server stores everything in 8KB pages and stores pages in 8 page
> extents. Based on the fill factor and size of your index SQL Server will fit
> as many rows from your index within the page as it can.
> 2) A non-clustered index always contains a reference to the key of the
> clustered index or a pointer to a place in the heap if no clustered index
> exists, in addition to the data in the non-clustered index. Additionally, a
> varchar field does not always take up all 8 bytes. Varchars are variable
> length and only stored with the bytes taken up by the value within the field.
> So, your example is not actually accurate in how big a row of the index (and
> it is further dependent on whether the index is clustered or non-clustered)
> would be.
> I'm not sure what exactly you are trying to do. But, in SQL 2000 you can
> use DBCC SHOWCONTIG to see how many pages and extents an index takes up. It
> will also show you if there is fragmentation in those pages and extents. In
> SQL 2005 this command is replaced by the DMV dm_db_index_physical_stats.
> dm_db_index_physical_stats also provides values for min,avg and max record
> size. Which might be what you are looking for. I don't know if you can find
> that data for SQL 2000.
> The article in BOL that David pointed you to is a good illustration of how
> that calculation would be done manually for a particular table and its
> indexes.
> HTH
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "David Gugick" wrote:
|||Okay.
So, it sounds like you are using sql 2000, and you are looking to
programmatically access the indexes, the columns that make up those indexes
and the size of the columns. So, you can estimate size. I could be jumping
to the wrong conclusions.
There are some undocumented SPs that could help you get there.
The following all seem to give you parts of what you want,
sp_MStablespace
sp_MSindexspace
sp_MShelpindex
sp_MShelpcolumns
sp_columns_rowset
For specifics about using any/ all of the above. Follow this link.
http://www.sql-server-performance.co...umented_sp.asp
Note that since these are undocumented, they won't necessarily work in SQL
2005.
HTH
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"yodarules" wrote:
[vbcol=seagreen]
> I totally understand and am aware of the points mentioned. The main reason
> why I want to do this to for size estimation. When we archive and delete
> some records we store the number of rows deleted. When we reload the data
> back, we want to do a space estimation and see if there's enough space to do
> this operation. Its actaully much more complicated than what I have just
> explained, but thats the idea. I don't want to do spaceused before and after
> for various other reasons and this seems to be the best case for us. If I
> cannot dynamically find index sizes, then I have to hard code it some where,
> again we are happy with an approximation, hence don't really care about
> varchar, etc. Actually the tables I'm interested in doing this don't have
> varchar.
> "Ryan Powers" wrote:

Find the size of an index row

Hey Guys,
I would like to enumerate all the indexes in a table and find the size of
the index, when I mean the size not the total space occupied but the size of
each row of the index. Eg: Table EMP has id bigint, name varchar(64),
address varchar(64), phone bigint, zip int, etc. Index 1 (id, name), Index
2(zip, phone), Index 3(phone) Each row in this table could occupy 8+64+64+8+
4
bytes. So each index associated with each row would occupy 8+64 + 8+8 + 8.
That's what I want. This would be used in later calculating the required
size based on number of records to be inserted.yodarules wrote:
> Hey Guys,
> I would like to enumerate all the indexes in a table and find the
> size of the index, when I mean the size not the total space occupied
> but the size of each row of the index. Eg: Table EMP has id bigint,
> name varchar(64), address varchar(64), phone bigint, zip int, etc.
> Index 1 (id, name), Index 2(zip, phone), Index 3(phone) Each row in
> this table could occupy 8+64+64+8+4 bytes. So each index associated
> with each row would occupy 8+64 + 8+8 + 8. That's what I want. This
> would be used in later calculating the required size based on number
> of records to be inserted.
See "Estimating the Size of a Table with a Clustered Index" in BOL.
David Gugick
Quest Software
www.quest.com|||Thanks David,
But thats not what I want, as I have explained in my post earlier. This
tells you the method to calcualte the space, which is what I might do when I
try to achieve my final goal. But the immediate goal is to dynamically find
the size of each non-clustered index on that table. The reason why I want t
o
do it dynamically is that, if I add or remove indexes, my SP should be able
to work with that rather than a hard coded value.
"David Gugick" wrote:

> yodarules wrote:
> See "Estimating the Size of a Table with a Clustered Index" in BOL.
> --
> David Gugick
> Quest Software
> www.quest.com
>|||A couple of thoughts on your question.
1) SQL Server stores everything in 8KB pages and stores pages in 8 page
extents. Based on the fill factor and size of your index SQL Server will fi
t
as many rows from your index within the page as it can.
2) A non-clustered index always contains a reference to the key of the
clustered index or a pointer to a place in the heap if no clustered index
exists, in addition to the data in the non-clustered index. Additionally, a
varchar field does not always take up all 8 bytes. Varchars are variable
length and only stored with the bytes taken up by the value within the field
.
So, your example is not actually accurate in how big a row of the index (and
it is further dependent on whether the index is clustered or non-clustered)
would be.
I'm not sure what exactly you are trying to do. But, in SQL 2000 you can
use DBCC SHOWCONTIG to see how many pages and extents an index takes up. It
will also show you if there is fragmentation in those pages and extents. In
SQL 2005 this command is replaced by the DMV dm_db_index_physical_stats.
dm_db_index_physical_stats also provides values for min,avg and max record
size. Which might be what you are looking for. I don't know if you can fin
d
that data for SQL 2000.
The article in BOL that David pointed you to is a good illustration of how
that calculation would be done manually for a particular table and its
indexes.
HTH
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"David Gugick" wrote:

> yodarules wrote:
> See "Estimating the Size of a Table with a Clustered Index" in BOL.
> --
> David Gugick
> Quest Software
> www.quest.com
>|||I totally understand and am aware of the points mentioned. The main reason
why I want to do this to for size estimation. When we archive and delete
some records we store the number of rows deleted. When we reload the data
back, we want to do a space estimation and see if there's enough space to do
this operation. Its actaully much more complicated than what I have just
explained, but thats the idea. I don't want to do spaceused before and afte
r
for various other reasons and this seems to be the best case for us. If I
cannot dynamically find index sizes, then I have to hard code it some where,
again we are happy with an approximation, hence don't really care about
varchar, etc. Actually the tables I'm interested in doing this don't have
varchar.
"Ryan Powers" wrote:
[vbcol=seagreen]
> A couple of thoughts on your question.
> 1) SQL Server stores everything in 8KB pages and stores pages in 8 page
> extents. Based on the fill factor and size of your index SQL Server will
fit
> as many rows from your index within the page as it can.
> 2) A non-clustered index always contains a reference to the key of the
> clustered index or a pointer to a place in the heap if no clustered index
> exists, in addition to the data in the non-clustered index. Additionally,
a
> varchar field does not always take up all 8 bytes. Varchars are variable
> length and only stored with the bytes taken up by the value within the fie
ld.
> So, your example is not actually accurate in how big a row of the index (
and
> it is further dependent on whether the index is clustered or non-clustered
)
> would be.
> I'm not sure what exactly you are trying to do. But, in SQL 2000 you can
> use DBCC SHOWCONTIG to see how many pages and extents an index takes up.
It
> will also show you if there is fragmentation in those pages and extents.
In
> SQL 2005 this command is replaced by the DMV dm_db_index_physical_stats.
> dm_db_index_physical_stats also provides values for min,avg and max record
> size. Which might be what you are looking for. I don't know if you can f
ind
> that data for SQL 2000.
> The article in BOL that David pointed you to is a good illustration of how
> that calculation would be done manually for a particular table and its
> indexes.
> HTH
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "David Gugick" wrote:
>|||Okay.
So, it sounds like you are using sql 2000, and you are looking to
programmatically access the indexes, the columns that make up those indexes
and the size of the columns. So, you can estimate size. I could be jumping
to the wrong conclusions.
There are some undocumented SPs that could help you get there.
The following all seem to give you parts of what you want,
sp_MStablespace
sp_MSindexspace
sp_MShelpindex
sp_MShelpcolumns
sp_columns_rowset
For specifics about using any/ all of the above. Follow this link.
http://www.sql-server-performance.c...cumented_sp.asp
Note that since these are undocumented, they won't necessarily work in SQL
2005.
HTH
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"yodarules" wrote:
[vbcol=seagreen]
> I totally understand and am aware of the points mentioned. The main reaso
n
> why I want to do this to for size estimation. When we archive and delete
> some records we store the number of rows deleted. When we reload the data
> back, we want to do a space estimation and see if there's enough space to
do
> this operation. Its actaully much more complicated than what I have just
> explained, but thats the idea. I don't want to do spaceused before and af
ter
> for various other reasons and this seems to be the best case for us. If I
> cannot dynamically find index sizes, then I have to hard code it some wher
e,
> again we are happy with an approximation, hence don't really care about
> varchar, etc. Actually the tables I'm interested in doing this don't have
> varchar.
> "Ryan Powers" wrote:
>

Find the size of an index row

Hey Guys,
I would like to enumerate all the indexes in a table and find the size of
the index, when I mean the size not the total space occupied but the size of
each row of the index. Eg: Table EMP has id bigint, name varchar(64),
address varchar(64), phone bigint, zip int, etc. Index 1 (id, name), Index
2(zip, phone), Index 3(phone) Each row in this table could occupy 8+64+64+8+4
bytes. So each index associated with each row would occupy 8+64 + 8+8 + 8.
That's what I want. This would be used in later calculating the required
size based on number of records to be inserted.yodarules wrote:
> Hey Guys,
> I would like to enumerate all the indexes in a table and find the
> size of the index, when I mean the size not the total space occupied
> but the size of each row of the index. Eg: Table EMP has id bigint,
> name varchar(64), address varchar(64), phone bigint, zip int, etc.
> Index 1 (id, name), Index 2(zip, phone), Index 3(phone) Each row in
> this table could occupy 8+64+64+8+4 bytes. So each index associated
> with each row would occupy 8+64 + 8+8 + 8. That's what I want. This
> would be used in later calculating the required size based on number
> of records to be inserted.
See "Estimating the Size of a Table with a Clustered Index" in BOL.
--
David Gugick
Quest Software
www.quest.com|||Thanks David,
But thats not what I want, as I have explained in my post earlier. This
tells you the method to calcualte the space, which is what I might do when I
try to achieve my final goal. But the immediate goal is to dynamically find
the size of each non-clustered index on that table. The reason why I want to
do it dynamically is that, if I add or remove indexes, my SP should be able
to work with that rather than a hard coded value.
"David Gugick" wrote:
> yodarules wrote:
> > Hey Guys,
> >
> > I would like to enumerate all the indexes in a table and find the
> > size of the index, when I mean the size not the total space occupied
> > but the size of each row of the index. Eg: Table EMP has id bigint,
> > name varchar(64), address varchar(64), phone bigint, zip int, etc.
> > Index 1 (id, name), Index 2(zip, phone), Index 3(phone) Each row in
> > this table could occupy 8+64+64+8+4 bytes. So each index associated
> > with each row would occupy 8+64 + 8+8 + 8. That's what I want. This
> > would be used in later calculating the required size based on number
> > of records to be inserted.
> See "Estimating the Size of a Table with a Clustered Index" in BOL.
> --
> David Gugick
> Quest Software
> www.quest.com
>|||A couple of thoughts on your question.
1) SQL Server stores everything in 8KB pages and stores pages in 8 page
extents. Based on the fill factor and size of your index SQL Server will fit
as many rows from your index within the page as it can.
2) A non-clustered index always contains a reference to the key of the
clustered index or a pointer to a place in the heap if no clustered index
exists, in addition to the data in the non-clustered index. Additionally, a
varchar field does not always take up all 8 bytes. Varchars are variable
length and only stored with the bytes taken up by the value within the field.
So, your example is not actually accurate in how big a row of the index (and
it is further dependent on whether the index is clustered or non-clustered)
would be.
I'm not sure what exactly you are trying to do. But, in SQL 2000 you can
use DBCC SHOWCONTIG to see how many pages and extents an index takes up. It
will also show you if there is fragmentation in those pages and extents. In
SQL 2005 this command is replaced by the DMV dm_db_index_physical_stats.
dm_db_index_physical_stats also provides values for min,avg and max record
size. Which might be what you are looking for. I don't know if you can find
that data for SQL 2000.
The article in BOL that David pointed you to is a good illustration of how
that calculation would be done manually for a particular table and its
indexes.
HTH
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"David Gugick" wrote:
> yodarules wrote:
> > Hey Guys,
> >
> > I would like to enumerate all the indexes in a table and find the
> > size of the index, when I mean the size not the total space occupied
> > but the size of each row of the index. Eg: Table EMP has id bigint,
> > name varchar(64), address varchar(64), phone bigint, zip int, etc.
> > Index 1 (id, name), Index 2(zip, phone), Index 3(phone) Each row in
> > this table could occupy 8+64+64+8+4 bytes. So each index associated
> > with each row would occupy 8+64 + 8+8 + 8. That's what I want. This
> > would be used in later calculating the required size based on number
> > of records to be inserted.
> See "Estimating the Size of a Table with a Clustered Index" in BOL.
> --
> David Gugick
> Quest Software
> www.quest.com
>|||I totally understand and am aware of the points mentioned. The main reason
why I want to do this to for size estimation. When we archive and delete
some records we store the number of rows deleted. When we reload the data
back, we want to do a space estimation and see if there's enough space to do
this operation. Its actaully much more complicated than what I have just
explained, but thats the idea. I don't want to do spaceused before and after
for various other reasons and this seems to be the best case for us. If I
cannot dynamically find index sizes, then I have to hard code it some where,
again we are happy with an approximation, hence don't really care about
varchar, etc. Actually the tables I'm interested in doing this don't have
varchar.
"Ryan Powers" wrote:
> A couple of thoughts on your question.
> 1) SQL Server stores everything in 8KB pages and stores pages in 8 page
> extents. Based on the fill factor and size of your index SQL Server will fit
> as many rows from your index within the page as it can.
> 2) A non-clustered index always contains a reference to the key of the
> clustered index or a pointer to a place in the heap if no clustered index
> exists, in addition to the data in the non-clustered index. Additionally, a
> varchar field does not always take up all 8 bytes. Varchars are variable
> length and only stored with the bytes taken up by the value within the field.
> So, your example is not actually accurate in how big a row of the index (and
> it is further dependent on whether the index is clustered or non-clustered)
> would be.
> I'm not sure what exactly you are trying to do. But, in SQL 2000 you can
> use DBCC SHOWCONTIG to see how many pages and extents an index takes up. It
> will also show you if there is fragmentation in those pages and extents. In
> SQL 2005 this command is replaced by the DMV dm_db_index_physical_stats.
> dm_db_index_physical_stats also provides values for min,avg and max record
> size. Which might be what you are looking for. I don't know if you can find
> that data for SQL 2000.
> The article in BOL that David pointed you to is a good illustration of how
> that calculation would be done manually for a particular table and its
> indexes.
> HTH
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "David Gugick" wrote:
> > yodarules wrote:
> > > Hey Guys,
> > >
> > > I would like to enumerate all the indexes in a table and find the
> > > size of the index, when I mean the size not the total space occupied
> > > but the size of each row of the index. Eg: Table EMP has id bigint,
> > > name varchar(64), address varchar(64), phone bigint, zip int, etc.
> > > Index 1 (id, name), Index 2(zip, phone), Index 3(phone) Each row in
> > > this table could occupy 8+64+64+8+4 bytes. So each index associated
> > > with each row would occupy 8+64 + 8+8 + 8. That's what I want. This
> > > would be used in later calculating the required size based on number
> > > of records to be inserted.
> >
> > See "Estimating the Size of a Table with a Clustered Index" in BOL.
> >
> > --
> > David Gugick
> > Quest Software
> > www.quest.com
> >|||Okay.
So, it sounds like you are using sql 2000, and you are looking to
programmatically access the indexes, the columns that make up those indexes
and the size of the columns. So, you can estimate size. I could be jumping
to the wrong conclusions.
There are some undocumented SPs that could help you get there.
The following all seem to give you parts of what you want,
sp_MStablespace
sp_MSindexspace
sp_MShelpindex
sp_MShelpcolumns
sp_columns_rowset
For specifics about using any/ all of the above. Follow this link.
http://www.sql-server-performance.com/ac_sql_server_2000_undocumented_sp.asp
Note that since these are undocumented, they won't necessarily work in SQL
2005.
HTH
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"yodarules" wrote:
> I totally understand and am aware of the points mentioned. The main reason
> why I want to do this to for size estimation. When we archive and delete
> some records we store the number of rows deleted. When we reload the data
> back, we want to do a space estimation and see if there's enough space to do
> this operation. Its actaully much more complicated than what I have just
> explained, but thats the idea. I don't want to do spaceused before and after
> for various other reasons and this seems to be the best case for us. If I
> cannot dynamically find index sizes, then I have to hard code it some where,
> again we are happy with an approximation, hence don't really care about
> varchar, etc. Actually the tables I'm interested in doing this don't have
> varchar.
> "Ryan Powers" wrote:
> > A couple of thoughts on your question.
> >
> > 1) SQL Server stores everything in 8KB pages and stores pages in 8 page
> > extents. Based on the fill factor and size of your index SQL Server will fit
> > as many rows from your index within the page as it can.
> > 2) A non-clustered index always contains a reference to the key of the
> > clustered index or a pointer to a place in the heap if no clustered index
> > exists, in addition to the data in the non-clustered index. Additionally, a
> > varchar field does not always take up all 8 bytes. Varchars are variable
> > length and only stored with the bytes taken up by the value within the field.
> > So, your example is not actually accurate in how big a row of the index (and
> > it is further dependent on whether the index is clustered or non-clustered)
> > would be.
> >
> > I'm not sure what exactly you are trying to do. But, in SQL 2000 you can
> > use DBCC SHOWCONTIG to see how many pages and extents an index takes up. It
> > will also show you if there is fragmentation in those pages and extents. In
> > SQL 2005 this command is replaced by the DMV dm_db_index_physical_stats.
> > dm_db_index_physical_stats also provides values for min,avg and max record
> > size. Which might be what you are looking for. I don't know if you can find
> > that data for SQL 2000.
> >
> > The article in BOL that David pointed you to is a good illustration of how
> > that calculation would be done manually for a particular table and its
> > indexes.
> >
> > HTH
> >
> > --
> > Ryan Powers
> > Clarity Consulting
> > http://www.claritycon.com
> >
> >
> > "David Gugick" wrote:
> >
> > > yodarules wrote:
> > > > Hey Guys,
> > > >
> > > > I would like to enumerate all the indexes in a table and find the
> > > > size of the index, when I mean the size not the total space occupied
> > > > but the size of each row of the index. Eg: Table EMP has id bigint,
> > > > name varchar(64), address varchar(64), phone bigint, zip int, etc.
> > > > Index 1 (id, name), Index 2(zip, phone), Index 3(phone) Each row in
> > > > this table could occupy 8+64+64+8+4 bytes. So each index associated
> > > > with each row would occupy 8+64 + 8+8 + 8. That's what I want. This
> > > > would be used in later calculating the required size based on number
> > > > of records to be inserted.
> > >
> > > See "Estimating the Size of a Table with a Clustered Index" in BOL.
> > >
> > > --
> > > David Gugick
> > > Quest Software
> > > www.quest.com
> > >

find missing/deleted records?

I have 2 tables say table1 and table2 with the same structure. Each record
is identified by a field 'SerialNo'. Now there should be a total of 500000
records in both tables with serialno from 1 to 500000. Either a record is in
table1 or table2. I want to find records (or SerialNo's) that are in
neither table (if deleted by accident etc). What would be the sql query?
I'm using SQL 6.5
thx
One way I can think of is,
populate a number table with entries 1 to 50000
and query
SELECT SlNo FROM tblNumbers WHERE
SlNo NOT IN(SELECT SlNo FROM table1 UNION
SELECT SlNo FROM Table2)
Roji. P. Thomas
SQL Server Programmer
"Mansoor Azam" <mansoorb@.shoa.net> wrote in message
news:2h5n48F8ei13U1@.uni-berlin.de...
> I have 2 tables say table1 and table2 with the same structure. Each record
> is identified by a field 'SerialNo'. Now there should be a total of 500000
> records in both tables with serialno from 1 to 500000. Either a record is
in
> table1 or table2. I want to find records (or SerialNo's) that are in
> neither table (if deleted by accident etc). What would be the sql query?
> I'm using SQL 6.5
> thx
>
|||Mansoor,
Normalisation and DRI would suggest that you NOT have 2 tables.
e.g. How do you enforce a "no duplication" constraint easily ?
That said UNION and OUTER JOIN are useful in this case. eg:
create table One
(
SerialNo int
)
create table Two
(
SerialNo int
)
insert One values ( 1 )
insert One values ( 3 )
insert One values ( 4 )
insert One values ( 8 )
insert One values ( 16 )
insert Two values ( 2 )
insert Two values ( 7 )
insert Two values ( 14 )
insert Two values ( 15 )
insert Two values ( 18 )
Select a.SerialNo
from
( Select SerialNo
from One
Union
Select SerialNo
from Two
) a
left outer join
( Select SerialNo
from One
Union
Select SerialNo
from Two
) b
on b.SerialNo = a.SerialNo + 1
where b.SerialNo is NULL
Will return only 1 row, unless you have gaps
Regards
AJ
"Mansoor Azam" <mansoorb@.shoa.net> wrote in message news:2h5n48F8ei13U1@.uni-berlin.de...
> I have 2 tables say table1 and table2 with the same structure. Each record
> is identified by a field 'SerialNo'. Now there should be a total of 500000
> records in both tables with serialno from 1 to 500000. Either a record is in
> table1 or table2. I want to find records (or SerialNo's) that are in
> neither table (if deleted by accident etc). What would be the sql query?
> I'm using SQL 6.5
> thx
>
|||Create a numbers table first, if you don't already have a table that
contains every number:
http://www.bizdatasolutions.com/tsql/tblnumbers.asp
SELECT N.number
FROM Numbers AS N
LEFT JOIN Table1 AS T1
ON N.number = T1.serialno
LEFT JOIN Table2 AS T2
ON N.number = T2.serialno
WHERE T1.serialno IS NULL
AND T2.serialno IS NULL
AND N.number BETWEEN 1 AND 50000
David Portas
SQL Server MVP

find missing/deleted records?

I have 2 tables say table1 and table2 with the same structure. Each record
is identified by a field 'SerialNo'. Now there should be a total of 500000
records in both tables with serialno from 1 to 500000. Either a record is in
table1 or table2. I want to find records (or SerialNo's) that are in
neither table (if deleted by accident etc). What would be the sql query?
I'm using SQL 6.5
thxOne way I can think of is,
populate a number table with entries 1 to 50000
and query
SELECT SlNo FROM tblNumbers WHERE
SlNo NOT IN(SELECT SlNo FROM table1 UNION
SELECT SlNo FROM Table2)
--
Roji. P. Thomas
SQL Server Programmer
"Mansoor Azam" <mansoorb@.shoa.net> wrote in message
news:2h5n48F8ei13U1@.uni-berlin.de...
> I have 2 tables say table1 and table2 with the same structure. Each record
> is identified by a field 'SerialNo'. Now there should be a total of 500000
> records in both tables with serialno from 1 to 500000. Either a record is
in
> table1 or table2. I want to find records (or SerialNo's) that are in
> neither table (if deleted by accident etc). What would be the sql query?
> I'm using SQL 6.5
> thx
>|||Mansoor,
Normalisation and DRI would suggest that you NOT have 2 tables.
e.g. How do you enforce a "no duplication" constraint easily ?
That said UNION and OUTER JOIN are useful in this case. eg:
create table One
(
SerialNo int
)
create table Two
(
SerialNo int
)
insert One values ( 1 )
insert One values ( 3 )
insert One values ( 4 )
insert One values ( 8 )
insert One values ( 16 )
insert Two values ( 2 )
insert Two values ( 7 )
insert Two values ( 14 )
insert Two values ( 15 )
insert Two values ( 18 )
Select a.SerialNo
from
( Select SerialNo
from One
Union
Select SerialNo
from Two
) a
left outer join
( Select SerialNo
from One
Union
Select SerialNo
from Two
) b
on b.SerialNo = a.SerialNo + 1
where b.SerialNo is NULL
Will return only 1 row, unless you have gaps
Regards
AJ
"Mansoor Azam" <mansoorb@.shoa.net> wrote in message news:2h5n48F8ei13U1@.uni-berlin.de...
> I have 2 tables say table1 and table2 with the same structure. Each record
> is identified by a field 'SerialNo'. Now there should be a total of 500000
> records in both tables with serialno from 1 to 500000. Either a record is in
> table1 or table2. I want to find records (or SerialNo's) that are in
> neither table (if deleted by accident etc). What would be the sql query?
> I'm using SQL 6.5
> thx
>|||Create a numbers table first, if you don't already have a table that
contains every number:
http://www.bizdatasolutions.com/tsql/tblnumbers.asp
SELECT N.number
FROM Numbers AS N
LEFT JOIN Table1 AS T1
ON N.number = T1.serialno
LEFT JOIN Table2 AS T2
ON N.number = T2.serialno
WHERE T1.serialno IS NULL
AND T2.serialno IS NULL
AND N.number BETWEEN 1 AND 50000
--
David Portas
SQL Server MVP
--

find missing/deleted records?

I have 2 tables say table1 and table2 with the same structure. Each record
is identified by a field 'SerialNo'. Now there should be a total of 500000
records in both tables with serialno from 1 to 500000. Either a record is in
table1 or table2. I want to find records (or SerialNo's) that are in
neither table (if deleted by accident etc). What would be the sql query?
I'm using SQL 6.5

thxMansoor,

Normalisation and DRI would suggest that you NOT have 2 tables.
e.g. How do you enforce a "no duplication" constraint easily ?

That said UNION and OUTER JOIN are useful in this case. eg:

create table One
(
SerialNo int
)
create table Two
(
SerialNo int
)

insert One values ( 1 )
insert One values ( 3 )
insert One values ( 4 )
insert One values ( 8 )
insert One values ( 16 )
insert Two values ( 2 )
insert Two values ( 7 )
insert Two values ( 14 )
insert Two values ( 15 )
insert Two values ( 18 )

Select a.SerialNo
from
( Select SerialNo
from One
Union
Select SerialNo
from Two
) a
left outer join
( Select SerialNo
from One
Union
Select SerialNo
from Two
) b
on b.SerialNo = a.SerialNo + 1
where b.SerialNo is NULL

Will return only 1 row, unless you have gaps

Regards
AJ

"Mansoor Azam" <mansoorb@.shoa.net> wrote in message news:2h5n48F8ei13U1@.uni-berlin.de...
> I have 2 tables say table1 and table2 with the same structure. Each record
> is identified by a field 'SerialNo'. Now there should be a total of 500000
> records in both tables with serialno from 1 to 500000. Either a record is in
> table1 or table2. I want to find records (or SerialNo's) that are in
> neither table (if deleted by accident etc). What would be the sql query?
> I'm using SQL 6.5
> thx|||Create a numbers table first, if you don't already have a table that
contains every number:
http://www.bizdatasolutions.com/tsql/tblnumbers.asp

SELECT N.number
FROM Numbers AS N
LEFT JOIN Table1 AS T1
ON N.number = T1.serialno
LEFT JOIN Table2 AS T2
ON N.number = T2.serialno
WHERE T1.serialno IS NULL
AND T2.serialno IS NULL
AND N.number BETWEEN 1 AND 50000

--
David Portas
SQL Server MVP
--

find missing/deleted records?

I have 2 tables say table1 and table2 with the same structure. Each record
is identified by a field 'SerialNo'. Now there should be a total of 500000
records in both tables with serialno from 1 to 500000. Either a record is in
table1 or table2. I want to find records (or SerialNo's) that are in
neither table (if deleted by accident etc). What would be the sql query?
I'm using SQL 6.5
thxOne way I can think of is,
populate a number table with entries 1 to 50000
and query
SELECT SlNo FROM tblNumbers WHERE
SlNo NOT IN(SELECT SlNo FROM table1 UNION
SELECT SlNo FROM Table2)
Roji. P. Thomas
SQL Server Programmer
"Mansoor Azam" <mansoorb@.shoa.net> wrote in message
news:2h5n48F8ei13U1@.uni-berlin.de...
> I have 2 tables say table1 and table2 with the same structure. Each record
> is identified by a field 'SerialNo'. Now there should be a total of 500000
> records in both tables with serialno from 1 to 500000. Either a record is
in
> table1 or table2. I want to find records (or SerialNo's) that are in
> neither table (if deleted by accident etc). What would be the sql query?
> I'm using SQL 6.5
> thx
>|||Mansoor,
Normalisation and DRI would suggest that you NOT have 2 tables.
e.g. How do you enforce a "no duplication" constraint easily ?
That said UNION and OUTER JOIN are useful in this case. eg:
create table One
(
SerialNo int
)
create table Two
(
SerialNo int
)
insert One values ( 1 )
insert One values ( 3 )
insert One values ( 4 )
insert One values ( 8 )
insert One values ( 16 )
insert Two values ( 2 )
insert Two values ( 7 )
insert Two values ( 14 )
insert Two values ( 15 )
insert Two values ( 18 )
Select a.SerialNo
from
( Select SerialNo
from One
Union
Select SerialNo
from Two
) a
left outer join
( Select SerialNo
from One
Union
Select SerialNo
from Two
) b
on b.SerialNo = a.SerialNo + 1
where b.SerialNo is NULL
Will return only 1 row, unless you have gaps
Regards
AJ
"Mansoor Azam" <mansoorb@.shoa.net> wrote in message news:2h5n48F8ei13U1@.uni-berlin.de...[vbc
ol=seagreen]
> I have 2 tables say table1 and table2 with the same structure. Each record
> is identified by a field 'SerialNo'. Now there should be a total of 500000
> records in both tables with serialno from 1 to 500000. Either a record is
in
> table1 or table2. I want to find records (or SerialNo's) that are in
> neither table (if deleted by accident etc). What would be the sql query?
> I'm using SQL 6.5
> thx
>[/vbcol]|||Create a numbers table first, if you don't already have a table that
contains every number:
http://www.bizdatasolutions.com/tsql/tblnumbers.asp
SELECT N.number
FROM Numbers AS N
LEFT JOIN Table1 AS T1
ON N.number = T1.serialno
LEFT JOIN Table2 AS T2
ON N.number = T2.serialno
WHERE T1.serialno IS NULL
AND T2.serialno IS NULL
AND N.number BETWEEN 1 AND 50000
David Portas
SQL Server MVP
--

find avg no. of rows in a page and no. of pages in a table ?

Id like to find the avg no. of rows in a page of a table along with total
number of pages.
ThanksHi Hassan
What version?
In SQL 2005, number of rows is stored in sys.partitions and number of pages
is stored in sys.allocation_units. The whole storage picture is a bit
different in SQL 2005, because of partitioning, and because of the ways that
special data (LOB and row_overflow) is kept track of. So take a look at
those two views in BOL and look at the columns they contain. Try to join
them to get the info you need, and then post back if you get stuck
In SQL 2000, everything is in sysindexes.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Hassan" <hassan@.test.com> wrote in message
news:%23k7zHFLTIHA.280@.TK2MSFTNGP03.phx.gbl...
> Id like to find the avg no. of rows in a page of a table along with total
> number of pages.
> Thanks