finding missing number

Hi Guys,
I am using sql server 2000 and i want to find missing
number between 1 and 1000 in a table.
what is the query for that?
pls advice me.
RGDS
BijuAssuming you have another table called Numbers that contains all the
required numbers:
SELECT num
FROM Numbers
WHERE NOT EXISTS
(SELECT *
FROM YourTable
WHERE num = Numbers.num)
AND num BETWEEN 1 AND 1000
David Portas
SQL Server MVP
--|||If there's only one Num Missing, this will work...
Select Num - 1
From <TableName> T
Where Num Between 1 And 1001
And Not Exists
(Select * From <TableName>
Where Num = T.Num - 1)
If there's a possibility of multiple Sequential numbers missing,
asin
1
2
3
6
7
...
then Use David's solution
"bijupg" wrote:

> Hi Guys,
> I am using sql server 2000 and i want to find missing
> number between 1 and 1000 in a table.
> what is the query for that?
> pls advice me.
> RGDS
> Biju
>|||Or use SQL Server 2000's nice TABLE variable to create a control table:
-- Use the Edit menu's 'Replace Template Parameters...' command to replace
the your_table/your_field values
DECLARE @.control TABLE ( control_no INT PRIMARY KEY )
DECLARE @.i INT
SET NOCOUNT ON
SET @.i = 1
-- Add control numbers to temp table
WHILE @.i Between 1 And 1000
BEGIN
INSERT @.control VALUES( @.i )
SET @.i = @.i + 1
END
SET NOCOUNT OFF
-- List missing values
SELECT t.*
FROM @.control t
LEFT JOIN <your_table, SYSNAME, > c ON t.control_no = c.<your_field,
SYSNAME, >
WHERE c.control_no Is Null|||If you want the starting of each gap, you can do:
SELECT nbr + 1
FROM tbl
WHERE NOT EXISTS( SELECT *
FROM tbl t1
WHERE t1.nbr = tbl.nbr + 1 )
AND nbr <= 1000 ;
If you want the start & end of each set of missing numbers:
SELECT t1.Nbr + 1 AS "start",
MIN( t2.Nbr ) - 1 AS "end"
FROM tbl t1
INNER JOIN tbl t2
ON t1.Nbr < t2.Nbr
GROUP BY t1.Nbr
HAVING MIN( t2.Nbr ) - t1.Nbr > 1;
If you want to list all the missing numbers, following the suggestions to
use a table of sequential numbers.
Anith

Finding all SQL users permissions and access on every server and DB?

Hello team, I have learned quite a bit from everyone here. I have decided to post a question for you guys and see what the best method is. Our Sr. DBA has asked me to get a list of all of our users permissions on all of our databases. We have about 20 SQL servers some of which have more databases and instances than I have fingers and toes. Can anyone recommend any solution to this problem? Perhaps there is a script I can download/write which will help me on this? Thanks in advance.

Sorry if I didn't provide enough information on my first post. Listed below are the requirements of items I need to put in an excel sheet. Thanks!

I need to gather:

Who has admin access to what servers

What are the log in accounts and what is it used for in SQL

Which users have which rights to which databases

Thanks again.

|||

Are all servers running SQL Server 2005, or do you have a mix of versions? This list can get to be pretty long very easily. Anyway, assuming you have only SQL Server 2005 servers, here are some pointers:

1) For admin access, you can query members of sysadmin and, additionally, you can query grantees of CONTROL SERVER. Check the sys.server_role_members and sys.server_permissions catalogs.

2) For login accounts, check the sys.server_principals catalog. What they are used for in SQL is something that isn't known to the database - the semantics of use of a certain entity are not captured anywhere - only its capabilities are. So you can find out what those logins can do, but not necessarily what they are meant to do.

3) Database owners and sysadmins aside, you need to list the users from sys.database_principals. Their permissions are stored in sys.database_permissions. For permissions inherited from groups or roles, you have to do separate queries - not a trivial task.

I'm not sure how helpful this information will be to you, even if you go to the trouble of getting it - I expect you will get way more data than any single person will actually want to analyze. It would be simpler to think of what you want to get out of this information and search for that particular thing specifically.

Thanks

Laurentiu

|||

Thank you very much for the considerate answer, I'll look into those and my appologies for not mentioning we are only running SQL 2000 exclusively. We will upgrade to 2005 in about 1-3 months.

|||

Ok, on SQL Server 2000 you would do something similar, but you will need to use the old catalogs. I don't know their names by heart, I just remember sysprotects was storing the permissions, but you can search Books Online and it should contain this information. If you can't figure out how to query the old catalogs, let us know.

Thanks

Laurentiu

|||

Hi Laurentiu.

I have a big problem and I would like to ask you via e-mail. I cannot find your e-mail, so please write to me on gg@.moj.pl.

I hope you will contact me. Thank you very much.

Jack.

|||

Ok so this is what I have done so far:

SELECT DISTINCT syslogins.loginname, syslogins.dbname,
@.@.SERVERNAME AS 'ServerName'
FROM sysprotects, syslogins
WHERE sysadmin = 1

BUILTIN\Administrators master HMIS-MMCSPADDEN\JDELOCAL
sa master HMIS-MMCSPADDEN\JDELOCAL

I believe this is giving me the information for the users who can connect to our SQL server, but what about the database and table access rights? Where would I find those? Thanks in advance.

|||

Note that you can drop the sysprotects from your statement, as you are not selecting anything from it.

For database permissions, you can use syspermissions: http://msdn2.microsoft.com/en-us/library/aa260459(SQL.80).aspx. You can ignore sysprotects, as you can query syspermissions instead, but here is the link for it as well: http://msdn2.microsoft.com/en-us/library/aa260449(SQL.80).aspx

For database users, you can use sysusers: http://msdn2.microsoft.com/en-us/library/aa260459(SQL.80).aspx.

For role memberships in a database, you can check sysmembers: http://msdn2.microsoft.com/en-us/library/aa260440(SQL.80).aspx

The article for syslogins is here: http://msdn2.microsoft.com/en-us/library/aa260424(SQL.80).aspx.

And, to browse all system catalogs in SQL Server 2000, you can start from here: http://msdn2.microsoft.com/en-us/library/aa260604(SQL.80).aspx.

Thanks

Laurentiu

|||Wow thanks!

Finding all SQL users permissions and access on every server and DB?

Hello team, I have learned quite a bit from everyone here. I have decided to post a question for you guys and see what the best method is. Our Sr. DBA has asked me to get a list of all of our users permissions on all of our databases. We have about 20 SQL servers some of which have more databases and instances than I have fingers and toes. Can anyone recommend any solution to this problem? Perhaps there is a script I can download/write which will help me on this? Thanks in advance.

Sorry if I didn't provide enough information on my first post. Listed below are the requirements of items I need to put in an excel sheet. Thanks!

I need to gather:

Who has admin access to what servers

What are the log in accounts and what is it used for in SQL

Which users have which rights to which databases

Thanks again.

|||

Are all servers running SQL Server 2005, or do you have a mix of versions? This list can get to be pretty long very easily. Anyway, assuming you have only SQL Server 2005 servers, here are some pointers:

1) For admin access, you can query members of sysadmin and, additionally, you can query grantees of CONTROL SERVER. Check the sys.server_role_members and sys.server_permissions catalogs.

2) For login accounts, check the sys.server_principals catalog. What they are used for in SQL is something that isn't known to the database - the semantics of use of a certain entity are not captured anywhere - only its capabilities are. So you can find out what those logins can do, but not necessarily what they are meant to do.

3) Database owners and sysadmins aside, you need to list the users from sys.database_principals. Their permissions are stored in sys.database_permissions. For permissions inherited from groups or roles, you have to do separate queries - not a trivial task.

I'm not sure how helpful this information will be to you, even if you go to the trouble of getting it - I expect you will get way more data than any single person will actually want to analyze. It would be simpler to think of what you want to get out of this information and search for that particular thing specifically.

Thanks

Laurentiu

|||

Thank you very much for the considerate answer, I'll look into those and my appologies for not mentioning we are only running SQL 2000 exclusively. We will upgrade to 2005 in about 1-3 months.

|||

Ok, on SQL Server 2000 you would do something similar, but you will need to use the old catalogs. I don't know their names by heart, I just remember sysprotects was storing the permissions, but you can search Books Online and it should contain this information. If you can't figure out how to query the old catalogs, let us know.

Thanks

Laurentiu

|||

Hi Laurentiu.

I have a big problem and I would like to ask you via e-mail. I cannot find your e-mail, so please write to me on gg@.moj.pl.

I hope you will contact me. Thank you very much.

Jack.

|||

Ok so this is what I have done so far:

SELECT DISTINCT syslogins.loginname, syslogins.dbname,
@.@.SERVERNAME AS 'ServerName'
FROM sysprotects, syslogins
WHERE sysadmin = 1

BUILTIN\Administrators master HMIS-MMCSPADDEN\JDELOCAL
sa master HMIS-MMCSPADDEN\JDELOCAL

I believe this is giving me the information for the users who can connect to our SQL server, but what about the database and table access rights? Where would I find those? Thanks in advance.

|||

Note that you can drop the sysprotects from your statement, as you are not selecting anything from it.

For database permissions, you can use syspermissions: http://msdn2.microsoft.com/en-us/library/aa260459(SQL.80).aspx. You can ignore sysprotects, as you can query syspermissions instead, but here is the link for it as well: http://msdn2.microsoft.com/en-us/library/aa260449(SQL.80).aspx

For database users, you can use sysusers: http://msdn2.microsoft.com/en-us/library/aa260459(SQL.80).aspx.

For role memberships in a database, you can check sysmembers: http://msdn2.microsoft.com/en-us/library/aa260440(SQL.80).aspx

The article for syslogins is here: http://msdn2.microsoft.com/en-us/library/aa260424(SQL.80).aspx.

And, to browse all system catalogs in SQL Server 2000, you can start from here: http://msdn2.microsoft.com/en-us/library/aa260604(SQL.80).aspx.

Thanks

Laurentiu

|||Wow thanks!

Find top 10 tables which have worst statistics

hi guys,
I use following command to find out statistic of table, but how do I find t
op 10 tables which have worst statistics in a database?
DBCC SHOW_STATISTICS ( table , target )Kevin,
Maybe using SQL-DMO...I think you'd have a little bit more control of the ou
tput there.
However the timing of when the statistics were last updated might provide yo
u with some insight as to which tables may have the worst statistics.
This query might get you started off of the timing of the last stats update
(not fully tested):
SELECT 'Index Name' = i.name,
'Statistics Date' = STATS_DATE(i.id, i.indid)
FROM sysobjects o, sysindexes i
WHERE o.id = i.id and STATS_DATE(i.id, i.indid) IS NOT NULL and i.name NOT
LIKE '_WA%'
and i.name NOT LIKE '%sys%'
ORDER BY 2
Do you have the automatic update statistics set for the database?
HTH
Jerry
"Kevin" <pearl_77@.hotmail.com> wrote in message news:%235WH%23mGxFHA.916@.TK2
MSFTNGP10.phx.gbl...
hi guys,
I use following command to find out statistic of table, but how do I find t
op 10 tables which have worst statistics in a database?
DBCC SHOW_STATISTICS ( table , target )|||yes, I have automatic update statistics set on databases.
but I dont' think that will guarantee statistics to be perfect.
I just want to find out which tables have worst statistics. I found an arti
cle in following link, it's very helpful.
http://www.sqlservercentral.com/scr...utions/1069.asp
thanks
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message news:ur44dEHxFHA.27
92@.tk2msftngp13.phx.gbl...
Kevin,
Maybe using SQL-DMO...I think you'd have a little bit more control of the ou
tput there.
However the timing of when the statistics were last updated might provide yo
u with some insight as to which tables may have the worst statistics.
This query might get you started off of the timing of the last stats update
(not fully tested):
SELECT 'Index Name' = i.name,
'Statistics Date' = STATS_DATE(i.id, i.indid)
FROM sysobjects o, sysindexes i
WHERE o.id = i.id and STATS_DATE(i.id, i.indid) IS NOT NULL and i.name NOT
LIKE '_WA%'
and i.name NOT LIKE '%sys%'
ORDER BY 2
Do you have the automatic update statistics set for the database?
HTH
Jerry
"Kevin" <pearl_77@.hotmail.com> wrote in message news:%235WH%23mGxFHA.916@.TK2
MSFTNGP10.phx.gbl...
hi guys,
I use following command to find out statistic of table, but how do I find t
op 10 tables which have worst statistics in a database?
DBCC SHOW_STATISTICS ( table , target )

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 table/indexes size

Hi, guys!
Is there any way that I can find out how much space each table (along
with its indexes) is ocupying on disk?
If that's impossible, how can I find out which tables occupy most space
in my database? I'm running out of disk space quickly and I need to do
some clean-up, but do not know where to start.
Thank you.look up sp_spaceused
"FireStarter" <d@.d.com> wrote in message
news:eGADPsVMFHA.4028@.tk2msftngp13.phx.gbl...
> Hi, guys!
> Is there any way that I can find out how much space each table (along with
> its indexes) is ocupying on disk?
> If that's impossible, how can I find out which tables occupy most space in
> my database? I'm running out of disk space quickly and I need to do some
> clean-up, but do not know where to start.
>
> Thank you.|||See sp_spaceused in BOL.
Example:
use northwind
go
exec sp_spaceused orders
go
AMB
"FireStarter" wrote:
> Hi, guys!
> Is there any way that I can find out how much space each table (along
> with its indexes) is ocupying on disk?
> If that's impossible, how can I find out which tables occupy most space
> in my database? I'm running out of disk space quickly and I need to do
> some clean-up, but do not know where to start.
>
> Thank you.
>|||Thank you all! Just what I needed!
FireStarter

Filters on the data

Hi guys,

I wanted to ask you for help as I am struggling with it second evening
already...
I have got tables DEVICES and PARTS.
One device can consist of multiple parts.

But...

I have also another table - FILTERS (id int, type int, is_not int,
phrase varchar(40))
where:id - just id,
type - filter type - can be 1 - for devices and 2 for parts,
is_not - says if the phrase has to be in a description (0) or must not
be there (1)
phrase - word to found in the description

My trouble is when I want to apply three filters at once:
1. Find devices with description containing PHRASE
2. Find parts with description containing PHRASE
3. Find devices with description NOT containing PHRASE

Query selecting parts and devices is like:

SELECT device.id, part.id
FROM DEVICE JOIN PARTS
WHERE ...

What I did is:

SELECT device_id, part_id FROM (
SELECT device_id, part_id FROM (
SELECT device_id, part_id FROM (
QUERY
) a
WHERE NOT EXISTS (SELECT 1 FROM FILTERS WHERE is_not=0 AND type=1)
OR EXISTS (SELECT 1 FROM FILTERS WHERE is_not=0 AND type=1 AND
device_desc LIKE '%' + phrase + '%')
) b
WHERE NOT EXISTS (SELECT 1 FROM FILTERS WHERE is_not=0 AND type=2)
OR EXISTS (SELECT 1 FROM FILTERS WHERE is_not=0 AND type=2 AND
part_desc LIKE '%' + phrase + '%')
) c
WHERE NOT EXISTS (SELECT 1 FROM FILTERS WHERE is_not>0 AND type=1)
OR NOT EXISTS (SELECT 1 FROM FILTERS WHERE is_not>0 AND type=1 AND
device_desc LIKE '%' + phrase + '%')

It works, but very slow. In DEVICES tables is 2 milion rows and in
PARTS is 3 millions.

I turned SET STATISTICS IO ON, and they show that FILTERS are being
asked veeery often.

It must be more efficient way to acheve this but I must be blind.

Thanks fo any advices,
KucolOn 27 Oct 2006 14:47:17 -0700, kucol wrote:

Quote:

Originally Posted by

>Hi guys,
>
>I wanted to ask you for help as I am struggling with it second evening
>already...

(snip)

Hi Kucol,

I noticed that you have posted the same message to
microsoft.public.sqlserver.programming as well. Please do not multipost
in the future - had I not checked the other groups first, I might now
have spent time to duplicate an answer you've already gotten elsewhere.
I prefer to spend my time answering questions that have not been
answered yet.

I'll keep an eye on the discussion in .programming and chime in if I
feel I have anything new to add.

--
Hugo Kornelis, SQL Server MVP