Need to convert cursor

I am new to SQL and have created a stored procedure for a .net web
application. Unfortunately I had to use a cursor in the stored
procedure, so it is taking several minutes to execute because it has to
read through about 15,000 records. There must be another way to do
what I'm trying to do without a cursor. I've tried temp tables and
case statements, but I can't seem to get them to work. I've been
trying to figure this out for over a week and I am just running into a
wall. Some expert advise would be much appreciated. My code is below.
Thank you in advance.

--Insert records into first temp table
DECLARE @.tempA TABLE
(
lnkey varchar(10),
AuditorIDvarchar(7)
)

INSERT INTO @.tempA

SELECT
LNKEY
,AuditorID

FROM
dbo.tblALPSLoans
WHERE AuditDate BETWEEN @.BegDate AND @.EndDate --parameters from my
application
AND AuditorID IN (SELECT LANID FROM dbo.tblEmployees WHERE ACTIONTYPE =
'ADDED')
AND AuditType = @.AuditType --parameter from my application

--Insert percentage value of Pre-Funding completes for each auditor
into temp table B
DECLARE @.tempB TABLE
(
LnkeyCount int,
AuditorIDvarchar(7)
)

INSERT INTO @.tempB

SELECT
ROUND(COUNT(LNKEY) * @.Percent/100, 0) AS 'LnkeyCount'
,AuditorID
FROM dbo.tblALPSLoans
WHERE AuditDate BETWEEN @.BegDate AND @.EndDate
AND AuditorID IN (SELECT LANID FROM dbo.tblEmployees WHERE ACTIONTYPE =
'ADDED')
GROUP BY AuditorID

/*Create cursor to loop through records and add a loan number to
tblinjectloans if the number of loans in tblinjectloans for each
auditor is less than the percentage value for each auditor from
@.tempB*/

DECLARE @.lnkey varchar(10)
DECLARE @.AuditorID varchar(7)
DECLARE @.var1int
DECLARE @.var2int
DECLARE @.sqlvarchar(4000)

DECLARE c1 CURSOR FOR
SELECT lnkey, auditorid
FROM @.TempA

OPEN c1

FETCH NEXT FROM c1
INTO @.LNKEY, @.AuditorID

WHILE @.@.FETCH_STATUS = 0
BEGIN

Select @.var1 = COUNT(Lnkey) from dbo.tblInjectLoans where
AuditorID=@.AuditorID
Select @.var2 = LnkeyCount from @.tempB where AuditorID=@.AuditorID
IF @.var1 < @.var2
Insert into dbo.tblInjectLoans
(lnkey, AuditorID)
Values (@.LNKEY, @.AuditorID)

FETCH NEXT FROM c1
INTO @.LNKEY, @.AuditorID

END

CLOSE c1
DEALLOCATE c1Patti wrote:

Quote:

Originally Posted by

I am new to SQL and have created a stored procedure for a .net web
application. Unfortunately I had to use a cursor in the stored
procedure, so it is taking several minutes to execute because it has to
read through about 15,000 records. There must be another way to do
what I'm trying to do without a cursor. I've tried temp tables and
case statements, but I can't seem to get them to work. I've been
trying to figure this out for over a week and I am just running into a
wall. Some expert advise would be much appreciated. My code is below.
Thank you in advance.
>
>
--Insert records into first temp table
DECLARE @.tempA TABLE
(
lnkey varchar(10),
AuditorIDvarchar(7)
)
>
INSERT INTO @.tempA
>
SELECT
LNKEY
,AuditorID
>
FROM
dbo.tblALPSLoans
WHERE AuditDate BETWEEN @.BegDate AND @.EndDate --parameters from my
application
AND AuditorID IN (SELECT LANID FROM dbo.tblEmployees WHERE ACTIONTYPE =
'ADDED')
AND AuditType = @.AuditType --parameter from my application
>
>
--Insert percentage value of Pre-Funding completes for each auditor
into temp table B
DECLARE @.tempB TABLE
(
LnkeyCount int,
AuditorIDvarchar(7)
)
>
INSERT INTO @.tempB
>
SELECT
ROUND(COUNT(LNKEY) * @.Percent/100, 0) AS 'LnkeyCount'
,AuditorID
FROM dbo.tblALPSLoans
WHERE AuditDate BETWEEN @.BegDate AND @.EndDate
AND AuditorID IN (SELECT LANID FROM dbo.tblEmployees WHERE ACTIONTYPE =
'ADDED')
GROUP BY AuditorID
>
>
>
/*Create cursor to loop through records and add a loan number to
tblinjectloans if the number of loans in tblinjectloans for each
auditor is less than the percentage value for each auditor from
@.tempB*/
>
DECLARE @.lnkey varchar(10)
DECLARE @.AuditorID varchar(7)
DECLARE @.var1int
DECLARE @.var2int
DECLARE @.sqlvarchar(4000)
>
>
DECLARE c1 CURSOR FOR
SELECT lnkey, auditorid
FROM @.TempA
>
OPEN c1
>
FETCH NEXT FROM c1
INTO @.LNKEY, @.AuditorID
>
WHILE @.@.FETCH_STATUS = 0
BEGIN
>
Select @.var1 = COUNT(Lnkey) from dbo.tblInjectLoans where
AuditorID=@.AuditorID
Select @.var2 = LnkeyCount from @.tempB where AuditorID=@.AuditorID
IF @.var1 < @.var2
Insert into dbo.tblInjectLoans
(lnkey, AuditorID)
Values (@.LNKEY, @.AuditorID)
>
>
FETCH NEXT FROM c1
INTO @.LNKEY, @.AuditorID
>
END
>
CLOSE c1
DEALLOCATE c1

Untested:

insert into tblInjectLoans (lnkey, AuditorID)
select lnkey, AuditorID
from tblALPSLoans al
where AuditDate between @.BegDate and @.EndDate
and AuditorID in (
select lanid
from tblEmployees
where actiontype = 'ADDED'
)
and AuditType = @.AuditType
and (select count(lnkey)
from tblInjectLoans il
where il.AuditorID = al.AuditorID
)
< (select round(count(lnkey) * @.Percent/100, 0)
from tblALPSLoans al2
where al2.AuditDate between @.BegDate and @.EndDate
and al2.AuditorID = al.AuditorID
)|||This was a great suggestion, thank you. I've actually been playing
with the code because right now it inserts every record from the
tblALPSLoans table into the tblinjectloans table for each auditor. But
I need it to insert just enough records for each auditor until the
percentage number for that auditor is met. So, for example, if auditor
A has 1 record in the tblInjectLoans table and his percentage number
(lnkeycount from @.tempb from my original code) is 3, then I need to
insert only 2 more records from the tblALPSLoans table into
tblInjectLoans. I was playing with Top N, but that isn't working for
me. Hopefully, this makes sense. Any ideas would be much appreciated.

Ed Murphy wrote:

Quote:

Originally Posted by

Patti wrote:
>

Quote:

Originally Posted by

I am new to SQL and have created a stored procedure for a .net web
application. Unfortunately I had to use a cursor in the stored
procedure, so it is taking several minutes to execute because it has to
read through about 15,000 records. There must be another way to do
what I'm trying to do without a cursor. I've tried temp tables and
case statements, but I can't seem to get them to work. I've been
trying to figure this out for over a week and I am just running into a
wall. Some expert advise would be much appreciated. My code is below.
Thank you in advance.

--Insert records into first temp table
DECLARE @.tempA TABLE
(
lnkey varchar(10),
AuditorIDvarchar(7)
)

INSERT INTO @.tempA

SELECT
LNKEY
,AuditorID

FROM
dbo.tblALPSLoans
WHERE AuditDate BETWEEN @.BegDate AND @.EndDate --parameters from my
application
AND AuditorID IN (SELECT LANID FROM dbo.tblEmployees WHERE ACTIONTYPE =
'ADDED')
AND AuditType = @.AuditType --parameter from my application

--Insert percentage value of Pre-Funding completes for each auditor
into temp table B
DECLARE @.tempB TABLE
(
LnkeyCount int,
AuditorIDvarchar(7)
)

INSERT INTO @.tempB

SELECT
ROUND(COUNT(LNKEY) * @.Percent/100, 0) AS 'LnkeyCount'
,AuditorID
FROM dbo.tblALPSLoans
WHERE AuditDate BETWEEN @.BegDate AND @.EndDate
AND AuditorID IN (SELECT LANID FROM dbo.tblEmployees WHERE ACTIONTYPE =
'ADDED')
GROUP BY AuditorID

/*Create cursor to loop through records and add a loan number to
tblinjectloans if the number of loans in tblinjectloans for each
auditor is less than the percentage value for each auditor from
@.tempB*/

DECLARE @.lnkey varchar(10)
DECLARE @.AuditorID varchar(7)
DECLARE @.var1int
DECLARE @.var2int
DECLARE @.sqlvarchar(4000)

DECLARE c1 CURSOR FOR
SELECT lnkey, auditorid
FROM @.TempA

OPEN c1

FETCH NEXT FROM c1
INTO @.LNKEY, @.AuditorID

WHILE @.@.FETCH_STATUS = 0
BEGIN

Select @.var1 = COUNT(Lnkey) from dbo.tblInjectLoans where
AuditorID=@.AuditorID
Select @.var2 = LnkeyCount from @.tempB where AuditorID=@.AuditorID
IF @.var1 < @.var2
Insert into dbo.tblInjectLoans
(lnkey, AuditorID)
Values (@.LNKEY, @.AuditorID)

FETCH NEXT FROM c1
INTO @.LNKEY, @.AuditorID

END

CLOSE c1
DEALLOCATE c1

>
Untested:
>
insert into tblInjectLoans (lnkey, AuditorID)
select lnkey, AuditorID
from tblALPSLoans al
where AuditDate between @.BegDate and @.EndDate
and AuditorID in (
select lanid
from tblEmployees
where actiontype = 'ADDED'
)
and AuditType = @.AuditType
and (select count(lnkey)
from tblInjectLoans il
where il.AuditorID = al.AuditorID
)
< (select round(count(lnkey) * @.Percent/100, 0)
from tblALPSLoans al2
where al2.AuditDate between @.BegDate and @.EndDate
and al2.AuditorID = al.AuditorID
)

|||Patti (pdavis269@.worldsavings.com) writes:

Quote:

Originally Posted by

This was a great suggestion, thank you. I've actually been playing
with the code because right now it inserts every record from the
tblALPSLoans table into the tblinjectloans table for each auditor. But
I need it to insert just enough records for each auditor until the
percentage number for that auditor is met. So, for example, if auditor
A has 1 record in the tblInjectLoans table and his percentage number
(lnkeycount from @.tempb from my original code) is 3, then I need to
insert only 2 more records from the tblALPSLoans table into
tblInjectLoans. I was playing with Top N, but that isn't working for
me. Hopefully, this makes sense. Any ideas would be much appreciated.

So does the original code you posted produce the correct result or
not? This is not clear to me.

A good idea for this type of questions, is that you post:

o CREATE TABLE statements for your tables, preferrably simplified to
the pertinent columns.
o INSERT statement with sample data.
o The desired result given the sample.

This make it easy to copy and paste and develop a tested solution. Also
the test data helps to clarify the verbal resitriction.

Note that the amount of sample data can be fairly small, but it should
be big enough to cover important cases.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||My original code does produce the correct result. Below I have
simplified my original code and have given some sample data. I hope
this helps clarifies what I am looking for. Thank you in advance.

/*This is how the tblInjectLoans table
looks like before I start my cursor.*/

LNKEY AuditorID
000001 lpAAAAA
000002 lpBBBBB
000003 lpCCCCC

/*I then need to find 3 percent of completed loans for each auditor and
insert it into
a temp table*/

INSERT INTO @.tempB

SELECT
ROUND(COUNT(LNKEY) * 3/100, 0) AS 'LnkeyCount'
,AuditorID
FROM dbo.tblALPSLoans
GROUP BY AuditorID

/*Results of @.TempB insert
AuditorID LnkeyCount
lpAAAAA 3
lpBBBBB 2
lpCCCCC 1
*/

/*Create cursor to loop through records and add a loan number to
tblinjectloans table if the number of loans in tblinjectloans for each

auditor is less than the LnkeyCount for each auditor from
@.tempB*/

DECLARE @.lnkey varchar(10)
DECLARE @.AuditorID varchar(7)
DECLARE @.var1 int
DECLARE @.var2 int
DECLARE @.sql varchar(4000)

DECLARE c1 CURSOR FOR
SELECT lnkey, auditorid
FROM @.TempA

OPEN c1

FETCH NEXT FROM c1
INTO @.LNKEY, @.AuditorID

WHILE @.@.FETCH_STATUS = 0
BEGIN

Select @.var1 = COUNT(Lnkey) from dbo.tblInjectLoans where
AuditorID=@.AuditorID
Select @.var2 = LnkeyCount from @.tempB where
AuditorID=@.AuditorID
IF @.var1 < @.var2
Insert into dbo.tblInjectLoans
(lnkey, AuditorID)
Values (@.LNKEY, @.AuditorID)

FETCH NEXT FROM c1
INTO @.LNKEY, @.AuditorID

END

CLOSE c1
DEALLOCATE c1

/*Desired results of tblInjectLoans when cursor is done
LNKEY AuditorID
00001 lpAAAAA
00005 lpAAAAA
00007 lpAAAAA
00002 lpBBBBB
00008 lpBBBBB
00003 lpCCCCC
*/

As you can see each auditor's count of loans is equal to the number of
LNKEYCount from @.TempB. This is my ultimate desired results. I need
loans added to the tblInjectLoans for each auditor until the total for
that auditor reaches their LNKEYCount from @.tempB. My original code
does produce these results. It just takes a long time to run.

Erland Sommarskog wrote:

Quote:

Originally Posted by

Patti (pdavis269@.worldsavings.com) writes:

Quote:

Originally Posted by

This was a great suggestion, thank you. I've actually been playing
with the code because right now it inserts every record from the
tblALPSLoans table into the tblinjectloans table for each auditor. But
I need it to insert just enough records for each auditor until the
percentage number for that auditor is met. So, for example, if auditor
A has 1 record in the tblInjectLoans table and his percentage number
(lnkeycount from @.tempb from my original code) is 3, then I need to
insert only 2 more records from the tblALPSLoans table into
tblInjectLoans. I was playing with Top N, but that isn't working for
me. Hopefully, this makes sense. Any ideas would be much appreciated.

>
So does the original code you posted produce the correct result or
not? This is not clear to me.
>
A good idea for this type of questions, is that you post:
>
o CREATE TABLE statements for your tables, preferrably simplified to
the pertinent columns.
o INSERT statement with sample data.
o The desired result given the sample.
>
This make it easy to copy and paste and develop a tested solution. Also
the test data helps to clarify the verbal resitriction.
>
Note that the amount of sample data can be fairly small, but it should
be big enough to cover important cases.
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||Patti (pdavis269@.worldsavings.com) writes:

Quote:

Originally Posted by

My original code does produce the correct result. Below I have
simplified my original code and have given some sample data. I hope
this helps clarifies what I am looking for. Thank you in advance.

You did not say which version of SQL Server you are using. The solution
below works on SQL 2000, but on SQL 2005 it should be possible to
all in one query.

First, add this column to @.TempA:

rowno int IDENTITY

No you can insert all rows at once with:

INSERT dbo.tblInjectLoans (lnkey, AuditorID)
SELECT a.lnkey, a.auditorid
FROM @.TempA a
JOIN @.TempB b ON a.AuthorID = b.AuthorIS
WHERE b.lnkey >
a.rowno + (SELECT COUNT(il.Lnkey)
FROM tblInjectLoans il
WHERE il.AuditorID = a.AuditorID)

Since you did not include a repro script (i.e. the CREATE TABLE and
INSERT statements I was asking for) this untest.

Note that the code as you have written is not deterministic in which
loans that goes to which auditor, but nor is it anything that can be
called random.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I apologize if I sound naive, but I am unfamiliar with what a repro
script is. I am hoping this is what you need:

tblInjectLoans:
CREATE TABLE [tblInjectLoans] (
[lnkey] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AuditorID] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[QualityAuditorID] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ID] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [PK_tblInjectLoans] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

tblALPSLoans:
CREATE TABLE [tblALPSLoans] (
[IDX] [int] IDENTITY (1, 1) NOT NULL ,
[LNKEY] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AuditorID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AuditDate] [datetime] NULL ,
[AuditType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_tblALPSLoans] PRIMARY KEY CLUSTERED
(
[IDX]
) ON [PRIMARY]
) ON [PRIMARY]
GO

I tried your suggestion and it is giving me all loans from temp table A
for each auditor where the count of loans from tblInjectLoans is
greater than the lnkeycount from temp table B. I do not want all loans
from temp table A, I want enough loans inserted until the lnkeycount
for each auditor is reached. Ex.

lnkeycount for Auditor lpAAAA = 3
lnkeycount for Auditor lpBBBB = 2
lnkeycount for Auditor lpCCCC = 1

Results:

LNKEY AuditorID
00001 lpAAAAA
00005 lpAAAAA
00007 lpAAAAA
00002 lpBBBBB
00008 lpBBBBB
00003 lpCCCCC

Thanks in advance!

Erland Sommarskog wrote:

Quote:

Originally Posted by

Patti (pdavis269@.worldsavings.com) writes:

Quote:

Originally Posted by

My original code does produce the correct result. Below I have
simplified my original code and have given some sample data. I hope
this helps clarifies what I am looking for. Thank you in advance.

>
You did not say which version of SQL Server you are using. The solution
below works on SQL 2000, but on SQL 2005 it should be possible to
all in one query.
>
First, add this column to @.TempA:
>
rowno int IDENTITY
>
No you can insert all rows at once with:
>
INSERT dbo.tblInjectLoans (lnkey, AuditorID)
SELECT a.lnkey, a.auditorid
FROM @.TempA a
JOIN @.TempB b ON a.AuthorID = b.AuthorIS
WHERE b.lnkey >
a.rowno + (SELECT COUNT(il.Lnkey)
FROM tblInjectLoans il
WHERE il.AuditorID = a.AuditorID)
>
>
Since you did not include a repro script (i.e. the CREATE TABLE and
INSERT statements I was asking for) this untest.
>
Note that the code as you have written is not deterministic in which
loans that goes to which auditor, but nor is it anything that can be
called random.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||Patti (pdavis269@.worldsavings.com) writes:

Quote:

Originally Posted by

I apologize if I sound naive, but I am unfamiliar with what a repro
script is. I am hoping this is what you need:

A repro script is something that reproduces something. The term is maybe
most commonly used in conjunction with bugs. That is, if you think that
you have found a bug, I would ask you for away to reproduce the problem.

In this case, I suggested a couple of posts back that you should post
CREATE TABLE statements for your tables and INSERT statements with
sample data, as well as the desired result give the sample. Calling
this a repro is maybe inaccurate. It is really a unit test, at least
if the sample data is well chosen.

You posted the table this time, but not the sample data. So I am sorry,
but I will not take any more stab at your problem. I would have to
guess too much. I'm sorry that the solution in my previous post did
not work. Maybe I misunderstood the requirements, maybe I made a
mistake when I composed the solution. Without test data, and without
the expected result from the test data, it is very difficult to write
a usable solution.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Patti (pdavis269@.worldsavings.com) writes:

Quote:

Originally Posted by

I apologize if I sound naive, but I am unfamiliar with what a repro
script is. I am hoping this is what you need:

By the way, you still have not said which version of SQL Server you are
using.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Need To CalcuThe Number Of Days Between The Current Date And A Stored Date

I need help with creating a query that compares the current date with a stored date field. If the difference between the two dates is greater or equal to 5 days for example, I need to be able to return these records. I am not sure if this can be done through a query alone but any help and suggestions would greatly be appreciated. Thanks in advance.yes it can be done through a query, but the standard sql for it will almost certainly not work in whatever database system you're using

date functions are notoriously non-standard, and each database system pretty much has its own proprietary functions

if you wouldn't mind mentioning which database system you're using, we could probably help you|||Nevermind I figured it out. I am using a SQL server 2000 database to query my data. I used the Datediff() function in conjunction with getdate(). See examples below for anyone else needing help with this topic.

DATEDIFF([day], dbo.table.field, GETDATE()) AS DAYS,
DATEDIFF([HOUR], dbo.table.field, GETDATE()) AS HOURS|||moved to SQL Server forum|||The solution you arrived at will work, but will not benefit from any indexing on your dbo.table.field column. If, instead, you used the DateAdd() function to find the date five days prior to the current date, then the optimizer will be able to use an index on dbo.table.field for comparisons:Where dbo.table.field >= DateAdd(day, -5, GetDate())

Need to build a search stored procedure

I have a few textboxes on a page that I would like to use as a search page and have clients shown in a gridview that meet the users entry into one or more of the textboxes.

I have ClientID, LastName, FirstName, Address, and Keywords. How would I build a stored procedure to allow me to do this?

This question has been answered several times in this forum. Please search for "dynamic where"/similar keywords.|||

What is the standard way of creating a search page of this sort?

|||

This thread discusses the SQL LIKE clause and dynamic queries... should be helpful:

http://forums.asp.net/thread/1529167.aspx

|||I searched dynamic where as well as many other keywords and have had no luck in finding any results. Can you offer any additional assistance?|||

SELECT *

FROM Table

WHERE (Column1 = @.col1 OR @.col1 IS NULL)

AND (Column2 = @.col2 OR @.col2 IS NULL)

AND (Column3 = @.col3 OR @.col3 IS NULL)

...

Need summary rows in a query, but how?

I have ORDERS which contain a number of ITEMS. Both are stored as rows in a
single table, tblOrders.
I would like to make a query that returns a list of items, then a total for
the order as a whole. Something like...
ORDER ID PART ID NAME QUANTITY PRICE NET
1000 1 widget 10 10 100
1000 2 gazeeza 5 5 25
125 < summary row
It appears this is the idea behind CUBE or ROLLUP, but as is typical, the
documentation on this is useless. Does anyone have any examples of how to
actually use this and get the output the way you want?
It appears you have to use GROUP BY for this sort of thing, and this brings
up another question. In the examples above, I want the grouping for the
summary to work only on the ORDER ID. However, as far as I can tell, in order
to get any output at all you need to list every column in the GROUP BY. This
kind of defeats the purpose in this case.
Any pointers?
Maury
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:1AFFFE59-A8F4-4BD1-8626-C9757BE6597C@.microsoft.com...

> ORDER ID PART ID NAME QUANTITY PRICE NET
> 1000 1 widget 10 10 100
> 1000 2 gazeeza 5 5 25
> 125 < summary row
>
That is not the result of a query. It's a report. It usually makes more
sense to use tools like reporting services for this kind of thing.
To do it in SQL you won't need CUBE/ROLLUP. UNION is probably more
appropriate:
SELECT order_id, tot, part_id, name, quantity, price, net
FROM
(SELECT 0 AS tot, order_id, part_id, name, quantity, price, net
FROM tbl_orders
UNION ALL
SELECT 1, order_id, NULL, NULL, NULL, NULL, SUM(net)
FROM tbl_orders
GROUP BY order_id) AS T
ORDER BY order_id, tot, part_id ;
Apparently your Order table is very denormalized. I hope and expect that you
are aware of that.
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
sql

Need summary rows in a query, but how?

I have ORDERS which contain a number of ITEMS. Both are stored as rows in a
single table, tblOrders.
I would like to make a query that returns a list of items, then a total for
the order as a whole. Something like...
ORDER ID PART ID NAME QUANTITY PRICE NET
1000 1 widget 10 10 100
1000 2 gazeeza 5 5 25
125 < summary row
It appears this is the idea behind CUBE or ROLLUP, but as is typical, the
documentation on this is useless. Does anyone have any examples of how to
actually use this and get the output the way you want?
It appears you have to use GROUP BY for this sort of thing, and this brings
up another question. In the examples above, I want the grouping for the
summary to work only on the ORDER ID. However, as far as I can tell, in orde
r
to get any output at all you need to list every column in the GROUP BY. This
kind of defeats the purpose in this case.
Any pointers?
Maury"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:1AFFFE59-A8F4-4BD1-8626-C9757BE6597C@.microsoft.com...

> ORDER ID PART ID NAME QUANTITY PRICE NET
> 1000 1 widget 10 10 100
> 1000 2 gazeeza 5 5 25
> 125 < summary row
>
That is not the result of a query. It's a report. It usually makes more
sense to use tools like reporting services for this kind of thing.
To do it in SQL you won't need CUBE/ROLLUP. UNION is probably more
appropriate:
SELECT order_id, tot, part_id, name, quantity, price, net
FROM
(SELECT 0 AS tot, order_id, part_id, name, quantity, price, net
FROM tbl_orders
UNION ALL
SELECT 1, order_id, NULL, NULL, NULL, NULL, SUM(net)
FROM tbl_orders
GROUP BY order_id) AS T
ORDER BY order_id, tot, part_id ;
Apparently your Order table is very denormalized. I hope and expect that you
are aware of that.
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Need summary rows in a query, but how?

I have ORDERS which contain a number of ITEMS. Both are stored as rows in a
single table, tblOrders.
I would like to make a query that returns a list of items, then a total for
the order as a whole. Something like...
ORDER ID PART ID NAME QUANTITY PRICE NET
1000 1 widget 10 10 100
1000 2 gazeeza 5 5 25
125 < summary row
It appears this is the idea behind CUBE or ROLLUP, but as is typical, the
documentation on this is useless. Does anyone have any examples of how to
actually use this and get the output the way you want?
It appears you have to use GROUP BY for this sort of thing, and this brings
up another question. In the examples above, I want the grouping for the
summary to work only on the ORDER ID. However, as far as I can tell, in order
to get any output at all you need to list every column in the GROUP BY. This
kind of defeats the purpose in this case.
Any pointers?
Maury"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:1AFFFE59-A8F4-4BD1-8626-C9757BE6597C@.microsoft.com...
> ORDER ID PART ID NAME QUANTITY PRICE NET
> 1000 1 widget 10 10 100
> 1000 2 gazeeza 5 5 25
> 125 < summary row
>
That is not the result of a query. It's a report. It usually makes more
sense to use tools like reporting services for this kind of thing.
To do it in SQL you won't need CUBE/ROLLUP. UNION is probably more
appropriate:
SELECT order_id, tot, part_id, name, quantity, price, net
FROM
(SELECT 0 AS tot, order_id, part_id, name, quantity, price, net
FROM tbl_orders
UNION ALL
SELECT 1, order_id, NULL, NULL, NULL, NULL, SUM(net)
FROM tbl_orders
GROUP BY order_id) AS T
ORDER BY order_id, tot, part_id ;
Apparently your Order table is very denormalized. I hope and expect that you
are aware of that.
Hope this helps.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Need stored procedure that shows one value for null and another for not null

I have a stored sprocedure with two parameters that currently searches a table for a matching record see SQL below:

**************************************************************

@.WorkOrderNum numeric(18,0)

,@.StackNum numeric(18,0)

AS

--This procedure is used in iFIX. It looks for matching

--Work Order Number / Stack Number combination.

SELECT

work_order_no

,stack_no

FROM

dbo.prod_data

WHERE

(dbo.prod_data.work_order_no = @.WorkOrderNum)

AND (dbo.prod_data.stack_no = @.StackNum)

******************************************************************************

What I need is a stored procedure that will look for a matching criteria and if it finds some it will return a value of "2", if it does not find any criteria it needs to return a value of "1". This value does not need to be stored, for display only.

Any help would be greatly appreciated

You could do below:

Code Snippet

select case when exists(

SELECT *

FROM

dbo.prod_data as p

WHERE

p.work_order_no = @.WorkOrderNum

AND p.stack_no = @.StackNum
) then 2 else 1 end as matched

|||

Thanks it worked perfectly

need stored procedure

Hi,
my table : tab1
id int
name varchar(50)
i want to write a stored procedure that returns the max(id) from the table.
how to create it.
thanks in advance
dnkHi
Use MAX() function
"DNKMCA" <dnk@.msn.com> wrote in message
news:exf%23s%23wnFHA.3564@.tk2msftngp13.phx.gbl...
> Hi,
> my table : tab1
> id int
> name varchar(50)
> i want to write a stored procedure that returns the max(id) from the
> table.
> how to create it.
> thanks in advance
> dnk
>sql

Need stored proc to make aliases to users...

I need a stored proc to make all aliases to users, then assign these newly created users to the same db's (which the aliases were assigned to).

Are there any other ways, or just to write a stored proc?

ANY suggestions accepted, just pls help, i need this for yesterday, and have not much experience in this :(Have a look at SP_ADDALIAS under BOL.

HTH|||Originally posted by Satya
Have a look at SP_ADDALIAS under BOL.

HTH

I mean I want to change the aliases to users, not to add an alias to a user.
(eg. you have a db, and there are 50 users and 10 aliases. You don't want to use aliases. So you have to make users from them.)

I need help in this, sorry is anybody misunderstood :)

So I have to drop all aliases, and create users with the same names, and assign them to the correct db-s.

But this have to work automatically...any scripts? commands?...etc

need stored proc to append table 1 to table 2

HI just wondering if there is a simple transact statement to use to copy the
contents of table one and append it to a second table. Each table has the
same columns and datatypes, thanks.
Paul G
Software engineer.
Here's the books online example from the INSERT...SELECT topic.
USE pubs
INSERT INTO mybooks
SELECT title_id
, title
, TYPE
FROM titles
WHERE TYPE = 'mod_cook'
|||Paul wrote:
> HI just wondering if there is a simple transact statement to use to
> copy the contents of table one and append it to a second table. Each
> table has the same columns and datatypes, thanks.
In order to have SQL Server create the destination table, use
SELECT...INTO
Select
id,
type,
name
Into
dbo.MyObjects
From
dbo.sysobjects
Where
id < 100
Select * from dbo.MyObjects
Drop Table dbo.MyObjects
David Gugick - SQL Server MVP
Quest Software
|||ok thanks.
Paul G
Software engineer.
"David Gugick" wrote:

> Paul wrote:
> In order to have SQL Server create the destination table, use
> SELECT...INTO
> Select
> id,
> type,
> name
> Into
> dbo.MyObjects
> From
> dbo.sysobjects
> Where
> id < 100
> Select * from dbo.MyObjects
> Drop Table dbo.MyObjects
>
> --
> David Gugick - SQL Server MVP
> Quest Software
>

need stored proc to append table 1 to table 2

HI just wondering if there is a simple transact statement to use to copy the
contents of table one and append it to a second table. Each table has the
same columns and datatypes, thanks.
--
Paul G
Software engineer.Here's the books online example from the INSERT...SELECT topic.
USE pubs
INSERT INTO mybooks
SELECT title_id
, title
, TYPE
FROM titles
WHERE TYPE = 'mod_cook'|||Paul wrote:
> HI just wondering if there is a simple transact statement to use to
> copy the contents of table one and append it to a second table. Each
> table has the same columns and datatypes, thanks.
In order to have SQL Server create the destination table, use
SELECT...INTO
Select
id,
type,
name
Into
dbo.MyObjects
From
dbo.sysobjects
Where
id < 100
Select * from dbo.MyObjects
Drop Table dbo.MyObjects
David Gugick - SQL Server MVP
Quest Software|||ok thanks.
--
Paul G
Software engineer.
"David Gugick" wrote:

> Paul wrote:
> In order to have SQL Server create the destination table, use
> SELECT...INTO
> Select
> id,
> type,
> name
> Into
> dbo.MyObjects
> From
> dbo.sysobjects
> Where
> id < 100
> Select * from dbo.MyObjects
> Drop Table dbo.MyObjects
>
> --
> David Gugick - SQL Server MVP
> Quest Software
>

need stored proc to append table 1 to table 2

HI just wondering if there is a simple transact statement to use to copy the
contents of table one and append it to a second table. Each table has the
same columns and datatypes, thanks.
--
Paul G
Software engineer.Here's the books online example from the INSERT...SELECT topic.
USE pubs
INSERT INTO mybooks
SELECT title_id
, title
, TYPE
FROM titles
WHERE TYPE = 'mod_cook'|||Paul wrote:
> HI just wondering if there is a simple transact statement to use to
> copy the contents of table one and append it to a second table. Each
> table has the same columns and datatypes, thanks.
In order to have SQL Server create the destination table, use
SELECT...INTO
Select
id,
type,
name
Into
dbo.MyObjects
From
dbo.sysobjects
Where
id < 100
Select * from dbo.MyObjects
Drop Table dbo.MyObjects
David Gugick - SQL Server MVP
Quest Software|||ok thanks.
--
Paul G
Software engineer.
"David Gugick" wrote:
> Paul wrote:
> > HI just wondering if there is a simple transact statement to use to
> > copy the contents of table one and append it to a second table. Each
> > table has the same columns and datatypes, thanks.
> In order to have SQL Server create the destination table, use
> SELECT...INTO
> Select
> id,
> type,
> name
> Into
> dbo.MyObjects
> From
> dbo.sysobjects
> Where
> id < 100
> Select * from dbo.MyObjects
> Drop Table dbo.MyObjects
>
> --
> David Gugick - SQL Server MVP
> Quest Software
>

need sql server tutorial site

hi all,
could anybody send me the tutorial site for learning sql server...
especially in stored procedure,functions,triggers.
thanx in advanceThere's several good sites out there:

SQLTeam.com
SQLServerCentral.com
sql-server-performance.com
sql-scripts.com
sqljunkies.com|||Books online is the better handy solution...

Need some suggestion

I have a stored procedure to retrieve records from a database, each record contains the following fields: merchant_id, shop_id, shop_sales_amount

each merchant can have several shops and the corresponding shop_sales_amount

the format of the report(detail section) is like this:

merchant1 $10000
merchant1-shop1 $5000
merchant1-shop2 $5000
merchant2 $1000
merchant2-shop1 $500
merchant2-shop2 $500
merchant3 $100
merchant3-shop1 $50
merchant3-shop2 $50
.....

merchant1 is group 1, merchant 2 is group 2, merchant 3 is group 3...etc, the amount following the merchant name is the total sales amount of each shop under the merchant, the ordering of the group is in descending order of this total sales amount...

It is quite complicated, seriously need some suggestions or hints, I am quite new to crystal reports. Hope someone could help me out, thanksSo you should create two Crystal report groups, Merchant (#1) and Shop (#2), an order on descending sales amount beneath these (using the record sort expert). Insert the sum of the sales amount grouped at the shop level (Insert Summary, field to summarize: shop_sales_amount; calculate this summary: Sum; summary location: group 2) and move from the group2 footer to the header.

Viola! (if I understand you correctly.)sql

Need some kind of run sp order

Hi all,
I have a report that uses three stored procs as 3 datasets. Two of these
stored procs further down the report are large select statements drawn over
a table which is populated with data by the first stored proc (i.e.
dataset). The problem is that when I preview the report, I know the first sp
has ran because the table is populated with data but the second and third
run but don't find any data.
Any takes on this one?
Regards
John.There is no guarantee of order. To do this you should use a subreport
instead.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John" <a@.b.c> wrote in message
news:OQ6VpFHWFHA.2768@.tk2msftngp13.phx.gbl...
> Hi all,
> I have a report that uses three stored procs as 3 datasets. Two of these
> stored procs further down the report are large select statements drawn
> over a table which is populated with data by the first stored proc (i.e.
> dataset). The problem is that when I preview the report, I know the first
> sp has ran because the table is populated with data but the second and
> third run but don't find any data.
> Any takes on this one?
> Regards
> John.
>|||While it is true that there is no 100% guaranteed order (it may change in
future releases), this is what you can do in RS 2000:
* make all 3 datasets use the same data source
* check "use transaction" on the data source properties dialog
The datasets will then get executed sequentially (within the same
transaction) in the order they are defined in the RDL (which is typically
the order in which they show up in the dataset drop-down list in report
designer).
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:eBke1wIWFHA.2256@.TK2MSFTNGP14.phx.gbl...
> There is no guarantee of order. To do this you should use a subreport
> instead.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "John" <a@.b.c> wrote in message
> news:OQ6VpFHWFHA.2768@.tk2msftngp13.phx.gbl...
>> Hi all,
>> I have a report that uses three stored procs as 3 datasets. Two of these
>> stored procs further down the report are large select statements drawn
>> over a table which is populated with data by the first stored proc (i.e.
>> dataset). The problem is that when I preview the report, I know the first
>> sp has ran because the table is populated with data but the second and
>> third run but don't find any data.
>> Any takes on this one?
>> Regards
>> John.
>

Need security advice on xp_cmdshell, bcp, xml procedure

I have a stored procedure that creates an xml file. It executes a SELECT
statement with the FOR XML clause and then writes the xml file using bcp and
xp_cmdshell. I am calling this procedure by passing it a parameter via ADO.
I have configured the SQL Server Agent with a proxy account so non-SysAdmin
can execute xp_cmdshell.

I'm concerned about giving non-SysAdmins execute on xp_cmdshell. I'm also
concerned about having to maintain the password on my proxy account when
that users' password changes.

Is there a better, more secure way to generate this xml file.

ThanksTerri (terri@.cybernets.com) writes:
> I have a stored procedure that creates an xml file. It executes a SELECT
> statement with the FOR XML clause and then writes the xml file using bcp
> and xp_cmdshell. I am calling this procedure by passing it a parameter
> via ADO. I have configured the SQL Server Agent with a proxy account so
> non-SysAdmin can execute xp_cmdshell.
> I'm concerned about giving non-SysAdmins execute on xp_cmdshell. I'm also
> concerned about having to maintain the password on my proxy account when
> that users' password changes.
> Is there a better, more secure way to generate this xml file.

I'm not really sure what you but it sounds like you do something like:

bcp "SELECT ... FOR XML" queryout outfile.bcp

This is not likely to work very well. ODBC will chop the XML document
after each 2033 character. See KB 275583.

So you would need to get the XML document to the client, and have the
client to create the file and put it where it belongs. Which probably
is better from a security perspective as well.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> This is not likely to work very well. ODBC will chop the XML document
> after each 2033 character. See KB 275583.
> So you would need to get the XML document to the client, and have the
> client to create the file and put it where it belongs. Which probably
> is better from a security perspective as well.

Thanks Erland,

I'm not using Query Analyzer so I don't think KB 275583 applies to me.

I'm calling the following procedure via ADO

CREATE PROCEDURE procGenerateXML
@.CheckRequestID int
AS
declare @.sql nvarchar(4000)
set @.sql= 'bcp "EXEC TestDB..proctest ' + CONVERT(varchar(8),@.ID) + '"' + '
queryout test.xml -SServer1 -T -c -r -t'
exec master..xp_cmdshell @.sql
GO

The procedure proctest looks like:

CREATE PROCEDURE proctest
@.ID int
AS
SELECT...
FROM...
WHERE...
FOR XML AUTO, ELEMENTS
GO

I then call the procedure like this
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim Param1
Dim ID As Integer
Dim provstr As String
Dim myfrm As Form
Dim dbs As Database
Set dbs = CurrentDb()
ID = Me.ID

cn.Provider = "sqloledb"
provstr = "Server=Server1;Database=TestDB;Trusted_Connection= Yes"
cn.Open provstr

Set cmd.ActiveConnection = cn
cmd.CommandText = "dbo.procGenerateXML"
cmd.CommandType = adCmdStoredProc
Set Param1 = cmd.CreateParameter("Input", adInteger, adParamInput)
cmd.Parameters.Append Param1
Param1.Value = ID
Set rs = cmd.Execute

I'm looking for guidance on the following:

-Can I use this xp_cmdshell method without giving my end users execute
permissions on xp_cmdshell and if not;
-Are there alternatives that don't use xp_cmdshell

Thanks|||Terri (terri@.cybernets.com) writes:
>> This is not likely to work very well. ODBC will chop the XML document
>> after each 2033 character. See KB 275583.
>>
>> So you would need to get the XML document to the client, and have the
>> client to create the file and put it where it belongs. Which probably
>> is better from a security perspective as well.
> Thanks Erland,
> I'm not using Query Analyzer so I don't think KB 275583 applies to me.

But you are using BCP which is implemented in ODBC. So I would definitely
encourage you to test to generate a large XML document, before you
ponder the issues with access to xp_cmdshell.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||If you are already calling this code from ADO, then instead of BCP and
all that you can just directly execute the FOR XML statement, loop
through each 2033 char return results and create the XML file from the
web server.

Erland Sommarskog wrote:
> Terri (terri@.cybernets.com) writes:
> >> This is not likely to work very well. ODBC will chop the XML document
> >> after each 2033 character. See KB 275583.
> >>
> >> So you would need to get the XML document to the client, and have the
> >> client to create the file and put it where it belongs. Which probably
> >> is better from a security perspective as well.
> > Thanks Erland,
> > I'm not using Query Analyzer so I don't think KB 275583 applies to me.
> But you are using BCP which is implemented in ODBC. So I would definitely
> encourage you to test to generate a large XML document, before you
> ponder the issues with access to xp_cmdshell.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||pb648174 (google@.webpaul.net) writes:
> If you are already calling this code from ADO, then instead of BCP and
> all that you can just directly execute the FOR XML statement, loop
> through each 2033 char return results and create the XML file from the
> web server.

Actually, if he would do it the simple-minded way, he would not get
2033-characters slices, as he is using SQLOLEDB(*) - he would get a binary
thingie instead.

I have not investigated it, but I believe the proper way to receive FOR
XML in ADO with SQLOLEDB is to use the Stream object.

But apart from that fine detail, I agree with you. Doing this from SQL
Server will be diffictul.

(*) If you use the MSDASQL provider, that is ODBC, then you would have
to as you say. But I would not recommend that.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks to those who replied, I'm going to investigate the ADO stream object.

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns970CB19EE5537Yazorman@.127.0.0.1...
> pb648174 (google@.webpaul.net) writes:
> > If you are already calling this code from ADO, then instead of BCP and
> > all that you can just directly execute the FOR XML statement, loop
> > through each 2033 char return results and create the XML file from the
> > web server.
> Actually, if he would do it the simple-minded way, he would not get
> 2033-characters slices, as he is using SQLOLEDB(*) - he would get a binary
> thingie instead.
> I have not investigated it, but I believe the proper way to receive FOR
> XML in ADO with SQLOLEDB is to use the Stream object.
> But apart from that fine detail, I agree with you. Doing this from SQL
> Server will be diffictul.
> (*) If you use the MSDASQL provider, that is ODBC, then you would have
> to as you say. But I would not recommend that.|||I did something like this in C# .NET recently via the native SQL OleDB
provider and had to do the looping action. I think that even when I
submitted the Query using Query Analyzer, I could see it coming back as
multiple rows... I could be wrong though.

NEED Query to Find out all Databases sizes

Hi,
I want to find out the SQL databases total size in SQL 2000 server, We have
around 55 databases on the server. Can anobody have the query or Stored proc
to find out the total space used in the server. Thanks
RBif exists (select * from sysobjects where id =
object_id(N'[dbo].[calcspace]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[calcspace]
GO
create procedure CalcSpace
/ ****************************************
********************************/
/* Stored Procedure: CalcSpace */
/* Creation Date: 1999-04-11 */
/* Copyright: - */
/* Written by: Sharon Dooley */
/* */
/* Purpose: <purpose of the script> */
/* A procedure to estimate the disk space requirements of a table. */
/* Refer to Books OnLine topic "Estimating the size of a table..." */
/* for a detailed description */
/* */
/* Input Parameters: <list any input parameters> */
/* @.table_name VARCHAR(30) Name of table to estimate */
/* @.num_rows INT Number of rows in the table */
/* */
/* Output Parameters: <list any output parameters> */
/* - */
/* */
/* Return Status: <list any return codes> */
/* - */
/* */
/* Usage: <a sample usage statement> */
/* EXEC CalcSpace 'MyTable', 10000 */
/* */
/* Other info: <other info for this SP> */
/* The is a direct copy from the CalcSpace stored procedure made by*/
/* Sharon Dooley, 1999-04-11. The only change is the added */
/* documentation header and a small bug fix mentioned below. */
/* */
/* Updates: <this section is used to track changes to the script> */
/* Date Author Purpose */
/* 2000-07-04 Magnus Andersson Changed @.sysstat from tinyint */
/* to int to prevent overflow */
/* scenario. Added documentation. */
/* */
/ ****************************************
********************************/
(@.table_name varchar(30)=null,-- name of table to estimate
@.num_rows int = 0) -- number of rows in the table
as
declare @.msg varchar(120)
-- Give usage statement if @.table_name is null
if @.table_name = null or @.num_rows = 0
begin
print 'Usage is:'
print ' calcspace table_name, no_of_rows'
print 'where table_name is the name of the table,'
print ' no_of_rows is the number of rows in the table,'
print ' '
return
end
declare @.num_fixed_col int,
@.fixed_data_size int,
@.num_variable_col int,
@.max_var_size int,
@.null_bitmap int,
@.variable_data_size int,
@.table_id int,
@.num_pages int,
@.table_size_in_bytes int,
@.table_size_in_meg real,
@.table_size_in_kbytes real,
@.sysstat int,
@.row_size int,
@.rows_per_page int,
@.free_rows_per_page int,
@.fillfactor int,
@.num_fixed_ckey_cols int,
@.fixed_ckey_size int,
@.num_variable_ckey_cols int,
@.max_var_ckey_size int,
@.cindex_null_bitmap int,
@.variable_ckey_size int,
@.cindex_row_size int,
@.cindex_rows_per_page int,
@.data_space_used int,
@.num_pages_clevel_0 int,
@.num_pages_clevel_1 int,
@.num_pages_clevel_x int,
@.num_pages_clevel_y int,
@.Num_CIndex_Pages int,
@.clustered_index_size_in_bytes int,
@.num_fixed_key_cols int,
@.fixed_key_size int,
@.num_variable_key_cols int,
@.max_var_key_size int,
@.index_null_bitmap int,
@.variable_key_size int,
@.nl_index_row_size int,
@.nl_index_rows_per_page int,
@.index_row_size int,
@.index_rows_per_page int,
@.free_index_rows_per_page int,
@.num_pages_level_0 int,
@.num_pages_level_1 int,
@.num_pages_level_x int,
@.num_pages_level_y int,
@.num_index_pages int,
@.nonclustered_index_size int,
@.total_num_nonclustered_index_pages int,
@.free_cindex_rows_per_page int,
@.tot_pages int
-- initialize variables
select @.num_fixed_col =0,
@.fixed_data_size =0,
@.num_variable_col =0,
@.max_var_size =0,
@.null_bitmap =0,
@.variable_data_size =0,
@.table_id =0,
@.num_pages =0,
@.table_size_in_bytes =0,
@.table_size_in_meg =0,
@.table_size_in_kbytes =0,
@.sysstat =0,
@.row_size =0,
@.rows_per_page =0,
@.num_fixed_ckey_cols =0,
@.fixed_ckey_size =0,
@.num_variable_ckey_cols =0,
@.max_var_ckey_size =0,
@.cindex_null_bitmap =0,
@.variable_ckey_size =0,
@.cindex_row_size =0,
@.cindex_rows_per_page =0,
@.data_space_used =0,
@.num_pages_clevel_0 =0,
@.num_pages_clevel_1 =0,
@.Num_CIndex_Pages =0,
@.clustered_index_size_in_bytes =0,
@.num_fixed_key_cols =0,
@.fixed_key_size =0,
@.num_variable_key_cols =0,
@.max_var_key_size =0,
@.index_null_bitmap =0,
@.variable_key_size =0,
@.nl_index_row_size =0,
@.nl_index_rows_per_page =0,
@.index_row_size =0,
@.index_rows_per_page =0,
@.free_index_rows_per_page =0,
@.num_pages_level_0 =0,
@.num_pages_level_1 =0,
@.num_pages_level_x =0,
@.num_pages_level_y =0,
@.num_index_pages =0,
@.nonclustered_index_size =0,
@.total_num_nonclustered_index_pages =0,
@.free_cindex_rows_per_page =0,
@.tot_pages =0
set nocount on
-- ****************************************
*****
-- MAKE SURE TABLE EXISTS
-- ****************************************
*****
select @.sysstat = sysstat,
@.table_id = id
from sysobjects where name = @.table_name
if @.sysstat & 7 not in (1,3)
begin
select @.msg = 'I can''t find the table '+@.table_name
print @.msg
return
end
-- ****************************************
*****
-- ESTIMATE SIZE OF TABLE
-- ****************************************
*****
-- get total number and total size of fixed-length columns
select @.num_fixed_col = count(name),
@.fixed_data_size = sum(length)
from syscolumns
where id= @.table_id and xtype in
(
select xtype from systypes where variable=0
)
if @.num_fixed_col= 0 --@.fixed_data_size is null. change to 0
select @.fixed_data_size=0
-- get total number and total maximum size of variable-length columns
select @.num_variable_col=count(name),
@.max_var_size= sum(length)
from syscolumns
where id= @.table_id and xtype in
(
select xtype from systypes where variable=1
)
if @.num_variable_col= 0 --@.max_var_size is null. change to 0
select @.max_var_size=0
-- get portion of the row used to manage column nullability
select @.null_bitmap=2+((@.num_fixed_col+7)/8)
-- determine space needed to store variable-length columns
-- this assumes all variable length columns will be 100% full
if @.num_variable_col = 0
select @.variable_data_size=0
else
select @.variable_data_size = 2 + (@.num_variable_col *2 )+
@.max_var_size
-- get row size
select @.row_size= @.fixed_data_size +
@.variable_data_size +
@.null_bitmap + 4 -- 4 represents the data row header
-- get number of rows per page
select @.rows_per_page = (8096) / (@.row_size+2)
-- If a clustered index is to be created on the table,
-- calculate the number of reserved free rows per page,
-- based on the fill factor specified.
-- If no clustered index is to be created, specify Fill_Factor as 100.
select @.fillfactor = 100 -- initialize it to the maximum
select @.free_rows_per_page = 0 --initialize to no free rows/page
select @.fillfactor=OrigFillFactor
from sysindexes
where id = @.table_id and indid=1 -- indid of 1 means the index is
clustered
if @.fillfactor<>0
-- a 0 fill factor ALMOST fills up the entire page, but not quite.
--The doc says that fill factor zero leaves 2 empty rows (keys)
--in each index page and no free rows in data pages of clustered
--indexes and leaf pages of non-clustered.
--We are working on the data pages in this section
select @.free_rows_per_page=8096 * ((100-@.fillfactor)/100)/@.row_size
-- get number of pages needed to store all rows
select @.num_pages = ceiling(convert(dec,@.num_rows) /
(@.rows_per_page-@.free_rows_per_page))
-- get storage needed for table data
select @.data_space_used=8192*@.num_pages
-- ****************************************
*****
-- COMPUTE SIZE OF CLUSTERED INDEX IF ONE EXISTS
-- ****************************************
*****
-- create a temporary table to contain columns in clustered index.
System table
-- sysindexkeys has a list of the column numbers contained in the index
select colid into #col_list
from sysindexkeys where id= @.table_id and indid=1 -- indid=1 means
clustered
if (select count(*) from #col_list) >0 -- do the following only if
clustered index exsists
begin
-- get total number and total maximum size of fixed-length columns in
clustered index
select @.num_fixed_ckey_cols=count(name),
@.fixed_ckey_size= sum(length)
from syscolumns
where id= @.table_id and xtype in
(
select xtype from systypes where variable=0
)
and colid in (select * from #col_list)
if @.num_fixed_ckey_cols= 0 --@.fixed_ckey_size is null. change to 0
select @.fixed_ckey_size=0
-- get total number and total maximum size of variable-length columns
in clustered index
select @.num_variable_ckey_cols=count(name),
@.max_var_ckey_size= sum(length)
from syscolumns
where id= @.table_id and xtype in
(
select xtype from systypes where variable=1
)
and colid in (select * from #col_list)
if @.num_variable_ckey_cols= 0 --@.max_var_ckey_size is null. change to
0
select @.max_var_ckey_size=0
-- If there are fixed-length columns in the clustered index,
-- a portion of the index row is reserved for the null bitmap.
Calculate its size:
if @.num_fixed_ckey_cols <> 0
select @.cindex_null_bitmap=2+((@.num_fixed_ckey_
cols + 7)/8)
else
select @.cindex_null_bitmap=0
-- If there are variable-length columns in the index, determine how
much
-- space is used to store the columns within the index row:
if @.num_variable_ckey_cols <> 0
select @.variable_ckey_size=2+(@.num_variable_cke
y_cols
*2)+@.max_var_ckey_size
else
select @.variable_ckey_size=0
-- Calculate the index row size
select @.cindex_row_size=@.fixed_ckey_size
+@.variable_ckey_size+@.cindex_null_bitmap
+1+8
--Next, calculate the number of index rows per page (8096 free bytes
per page):
select @.cindex_rows_per_page=(8096)/(@.cindex_row_size+2)
-- consider fillfactor
if @.fillfactor=0
select @.free_cindex_rows_per_page = 2
else
select @.free_cindex_rows_per_page= 8096 *
((100-@.fillfactor)/100)/@.cindex_row_size
-- Next, calculate the number of pages required to store
-- all the index rows at each level of the index.
select
@.num_pages_clevel_0=ceiling(convert(deci
mal,(@.data_space_used/8192))/(@.cinde
x_rows_per_page-@.free_cindex_rows_per_page))
select @.Num_CIndex_Pages=@.num_pages_clevel_0
select @.num_pages_clevel_x=@.num_pages_clevel_0
while @.num_pages_clevel_x <> 1
begin
select
@.num_pages_clevel_y=ceiling(convert(deci
mal,@.num_pages_clevel_x)/(@.cindex_ro
ws_per_page-@.free_cindex_rows_per_page))
select @.Num_CIndex_Pages=@.Num_CIndex_Pages+@.num
_pages_clevel_y
select @.num_pages_clevel_x=@.num_pages_clevel_y
end
end
-- ****************************************
*****
-- END CLUSTERED INDEX SECTION
-- ****************************************
*****
-- ****************************************
*****
-- BEGIN NON-CLUSTERED INDEX SECTION
-- ****************************************
*****
-- create temp table with non-clustered index info
select indid, colid into #col_list2
from sysindexkeys where id= @.table_id and indid<>1 -- indid=1 means
clustered
if (select count(*) from #col_list2) >0 -- do the following only if
non-clustered indexes exsist
begin
declare @.i int -- a counter variable
select @.i=1 -- initilize to 2, because 1 is id of clustered index
while @.i< 249 -- max number of non-clustered indexes
begin
select @.i=@.i+1 -- look for the next non-clustered index
-- reinitialize all numbers
select @.num_fixed_key_cols = 0,
@.fixed_key_size = 0,
@.num_variable_key_cols = 0,
@.max_var_key_size = 0,
@.index_null_bitmap = 0,
@.variable_key_size = 0,
@.nl_index_row_size = 0,
@.nl_index_rows_per_page = 0,
@.index_row_size = 0,
@.index_rows_per_page = 0,
@.free_index_rows_per_page = 0,
@.num_pages_level_0 = 0,
@.num_pages_level_x = 0,
@.num_pages_level_y = 0,
@.Num_Index_Pages = 0
-- get total number and total maximum size
-- of fixed-length columns in nonclustered index
select @.num_fixed_key_cols=count(name),
@.fixed_key_size= sum(length)
from syscolumns
where id= @.table_id and xtype in
(
select xtype from systypes where variable=0
)
and colid in (select colid from #col_list2 where indid=@.i)
if @.num_fixed_key_cols= 0 --@.fixed_key_size is null. change to 0
select @.fixed_key_size=0
-- get total number and total maximum size of variable-length columns
in index
select @.num_variable_key_cols=count(name),
@.max_var_key_size= sum(length)
from syscolumns
where id= @.table_id and xtype in
(
select xtype from systypes where variable=1
)
and colid in (select colid from #col_list2 where indid=@.i)
if @.num_variable_key_cols= 0 --@.max_var_key_size is null. change to
0
select @.max_var_key_size=0
if @.num_fixed_key_cols = 0 and @.num_variable_key_cols = 0 --there is
no index
continue
-- If there are fixed-length columns in the non-clustered index,
-- a portion of the index row is reserved for the null bitmap.
Calculate its size:
if @.num_fixed_key_cols <> 0
select @.index_null_bitmap=2+((@.num_fixed_key_co
ls + 7)/8)
else
select @.index_null_bitmap=0
-- If there are variable-length columns in the index, determine how
much
-- space is used to store the columns within the index row:
if @.num_variable_key_cols <> 0
select @.variable_key_size=2+(@.num_variable_key_
cols
*2)+@.max_var_key_size
else
select @.variable_key_size=0
-- Calculate the non-leaf index row size
select @.nl_index_row_size=@.fixed_key_size
+@.variable_key_size+@.index_null_bitmap+1
+8
--Next, calculate the number of non-leaf index rows per page (8096
free bytes per page):
select @.nl_index_rows_per_page=(8096)/(@.nl_index_row_size+2)
-- Next, calculate the leaf index row size
select @.index_row_size=@.cindex_row_size + @.fixed_key_size +
@.variable_key_size+@.index_null_bitmap+1
-- Next, calculate the number of leaf level index rows per page
select @.index_rows_per_page = 8096/(@.index_row_size + 2)
-- Next, calcuate the number of reserved free index rows/page based
on fill factor
if @.fillfactor=0
-- a 0 fill factor ALMOST fills up the entire page, but not quite.
--The doc says that fill factor zero leaves 2 empty rows (keys)
--in each index page and no free rows in data pages of clustered
--indexes and leaf pages of non-clustered.
--We are working on the non-clustered index pages in this section
select @.free_index_rows_per_page=0
else
select @.free_index_rows_per_page= 8096 *
((100-@.fillfactor)/100)/@.index_row_size
-- Next, calculate the number of pages required to store
-- all the index rows at each level of the index.
select
@.num_pages_level_0=ceiling(convert(decim
al,@.num_rows)/@.index_rows_per_page-@.
free_index_rows_per_page)
select @.Num_Index_Pages=@.num_pages_level_0
select @.num_pages_level_x=@.num_pages_level_0
while @.num_pages_level_x <> 1
begin
select
@.num_pages_level_y=ceiling(convert(decim
al,@.num_pages_level_x)/@.nl_index_row
s_per_page)
select @.Num_Index_Pages=@.Num_Index_Pages+@.num_p
ages_level_y
select @.num_pages_level_x=@.num_pages_level_y
end
select
@.total_num_nonclustered_index_pages=@.tot
al_num_nonclustered_index_pages+@.Num
_Index_Pages
end
end
-- ****************************************
*****
-- END NON-CLUSTERED INDEX SECTION
-- ****************************************
*****
-- display numbers
select @.tot_pages=@.num_pages + @.Num_CIndex_Pages +
@.total_num_nonclustered_index_pages
select @.table_size_in_bytes= 8192*@.tot_pages
select @.table_size_in_kbytes= @.table_size_in_bytes/1024.0
select @.table_size_in_meg= str(@.table_size_in_kbytes/1000.0,17,2)
select substring(@.table_name,1,20) as 'Table Name',
convert(varchar(10),@.table_size_in_meg) as 'MB Estimate',
@.tot_pages as 'Total Pages',
@.num_pages as '#Data Pgs',
@.Num_CIndex_Pages as '#Clustered Idx Pgs',
@.total_num_nonclustered_index_pages as '#NonClustered Idx Pgs'|||email me and i'll send you the text file...|||I thought CalcSpace was a cool proc, so just for kicks, I thought I would
try it on an existing table.
The table has 7million rows and currently consumes (with indexes) 4.5gb.
The script CalcSpace estimated it would need 510mb... I didn't
investigate why this returned such a low number, or why my table is
consuming such a high number (when compared to the estimate)
Here's another method that might work for you. It uses 2 existing system
procedures. It's a little backwards, converting the strings to int's, but
it works.
Create table #tblSize (name sysname,rows int,reserved varchar(10),data
varchar(10),idx varchar(10),used varchar(10))
insert into #tblSize EXEC sp_MSforeachtable @.command1='sp_spaceused ''?'''
-- select * from #tblSize order by rows desc
select sum(cast(substring(reserved,1,charindex(
'KB',data,1)-1) as
int) ),sum(cast(substring(data,1,charindex('K
B',data,1)-1) as int) +
cast(substring(idx,1,charindex('KB',idx,
1)-1) as int) ) as used
from #tblSize
Bill
"rb" <srbssr@.yahoo.com> wrote in message
news:eswxO7D9EHA.368@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I want to find out the SQL databases total size in SQL 2000 server, We
> have
> around 55 databases on the server. Can anobody have the query or Stored
> proc
> to find out the total space used in the server. Thanks
> RB
>
>

NEED Query to Find out all Databases sizes

Hi,
I want to find out the SQL databases total size in SQL 2000 server, We have
around 55 databases on the server. Can anobody have the query or Stored proc
to find out the total space used in the server. Thanks
RB
if exists (select * from sysobjects where id =
object_id(N'[dbo].[calcspace]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[calcspace]
GO
create procedure CalcSpace
/************************************************** **********************/
/* Stored Procedure: CalcSpace*/
/* Creation Date: 1999-04-11*/
/* Copyright: -*/
/* Written by: Sharon Dooley*/
/**/
/* Purpose: <purpose of the script>*/
/*A procedure to estimate the disk space requirements of a table.*/
/*Refer to Books OnLine topic "Estimating the size of a table..." */
/*for a detailed description*/
/**/
/* Input Parameters: <list any input parameters>*/
/*@.table_nameVARCHAR(30)Name of table to estimate*/
/*@.num_rowsINTNumber of rows in the table*/
/**/
/* Output Parameters: <list any output parameters>*/
/*-*/
/**/
/* Return Status: <list any return codes>*/
/*-*/
/**/
/* Usage: <a sample usage statement>*/
/*EXEC CalcSpace 'MyTable', 10000*/
/**/
/* Other info: <other info for this SP>*/
/*The is a direct copy from the CalcSpace stored procedure made by*/
/*Sharon Dooley, 1999-04-11. The only change is the added */
/*documentation header and a small bug fix mentioned below.*/
/**/
/* Updates: <this section is used to track changes to the script>*/
/* Date Author Purpose*/
/* 2000-07-04Magnus AnderssonChanged @.sysstat from tinyint */
/* to int to prevent overflow */
/* scenario. Added documentation.*/
/* */
/************************************************** **********************/
(@.table_namevarchar(30)=null,-- name of table to estimate
@.num_rowsint = 0) -- number of rows in the table
as
declare @.msgvarchar(120)
--Give usage statement if @.table_name is null
if @.table_name = null or @.num_rows = 0
begin
print 'Usage is:'
print ' calcspace table_name, no_of_rows'
print 'where table_name is the name of the table,'
print ' no_of_rows is the number of rows in the table,'
print ' '
return
end
declare@.num_fixed_colint,
@.fixed_data_sizeint,
@.num_variable_colint,
@.max_var_sizeint,
@.null_bitmapint,
@.variable_data_sizeint,
@.table_idint,
@.num_pagesint,
@.table_size_in_bytesint,
@.table_size_in_megreal,
@.table_size_in_kbytesreal,
@.sysstatint,
@.row_sizeint,
@.rows_per_pageint,
@.free_rows_per_pageint,
@.fillfactorint,
@.num_fixed_ckey_cols int,
@.fixed_ckey_size int,
@.num_variable_ckey_cols int,
@.max_var_ckey_size int,
@.cindex_null_bitmapint,
@.variable_ckey_sizeint,
@.cindex_row_sizeint,
@.cindex_rows_per_pageint,
@.data_space_usedint,
@.num_pages_clevel_0int,
@.num_pages_clevel_1int,
@.num_pages_clevel_xint,
@.num_pages_clevel_yint,
@.Num_CIndex_Pagesint,
@.clustered_index_size_in_bytesint,
@.num_fixed_key_cols int,
@.fixed_key_size int,
@.num_variable_key_cols int,
@.max_var_key_size int,
@.index_null_bitmapint,
@.variable_key_sizeint,
@.nl_index_row_sizeint,
@.nl_index_rows_per_pageint,
@.index_row_sizeint,
@.index_rows_per_pageint,
@.free_index_rows_per_pageint,
@.num_pages_level_0int,
@.num_pages_level_1int,
@.num_pages_level_xint,
@.num_pages_level_yint,
@.num_index_pagesint,
@.nonclustered_index_sizeint,
@.total_num_nonclustered_index_pagesint,
@.free_cindex_rows_per_pageint,
@.tot_pagesint
-- initialize variables
select@.num_fixed_col=0,
@.fixed_data_size=0,
@.num_variable_col=0,
@.max_var_size=0,
@.null_bitmap=0,
@.variable_data_size=0,
@.table_id=0,
@.num_pages=0,
@.table_size_in_bytes=0,
@.table_size_in_meg=0,
@.table_size_in_kbytes=0,
@.sysstat=0,
@.row_size=0,
@.rows_per_page=0,
@.num_fixed_ckey_cols =0,
@.fixed_ckey_size =0,
@.num_variable_ckey_cols =0,
@.max_var_ckey_size =0,
@.cindex_null_bitmap=0,
@.variable_ckey_size=0,
@.cindex_row_size=0,
@.cindex_rows_per_page=0,
@.data_space_used=0,
@.num_pages_clevel_0=0,
@.num_pages_clevel_1=0,
@.Num_CIndex_Pages=0,
@.clustered_index_size_in_bytes=0,
@.num_fixed_key_cols =0,
@.fixed_key_size =0,
@.num_variable_key_cols =0,
@.max_var_key_size =0,
@.index_null_bitmap=0,
@.variable_key_size=0,
@.nl_index_row_size=0,
@.nl_index_rows_per_page=0,
@.index_row_size=0,
@.index_rows_per_page=0,
@.free_index_rows_per_page=0,
@.num_pages_level_0=0,
@.num_pages_level_1=0,
@.num_pages_level_x=0,
@.num_pages_level_y=0,
@.num_index_pages=0,
@.nonclustered_index_size=0,
@.total_num_nonclustered_index_pages=0,
@.free_cindex_rows_per_page=0,
@.tot_pages=0
set nocount on
--*********************************************
-- MAKE SURE TABLE EXISTS
--*********************************************
select @.sysstat = sysstat,
@.table_id = id
from sysobjects where name = @.table_name
if @.sysstat & 7 not in (1,3)
begin
select @.msg = 'I can''t find the table '+@.table_name
print @.msg
return
end
--*********************************************
-- ESTIMATE SIZE OF TABLE
--*********************************************
-- get total number and total size of fixed-length columns
select @.num_fixed_col = count(name),
@.fixed_data_size = sum(length)
from syscolumns
where id= @.table_id and xtype in
(
select xtype from systypes where variable=0
)
if @.num_fixed_col= 0 --@.fixed_data_size is null. change to 0
select @.fixed_data_size=0
-- get total number and total maximum size of variable-length columns
select @.num_variable_col=count(name),
@.max_var_size= sum(length)
from syscolumns
where id= @.table_id and xtype in
(
select xtype from systypes where variable=1
)
if @.num_variable_col= 0 --@.max_var_size is null. change to 0
select @.max_var_size=0
-- get portion of the row used to manage column nullability
select @.null_bitmap=2+((@.num_fixed_col+7)/8)
-- determine space needed to store variable-length columns
-- this assumes all variable length columns will be 100% full
if @.num_variable_col = 0
select @.variable_data_size=0
else
select @.variable_data_size = 2 + (@.num_variable_col *2 )+
@.max_var_size
-- get row size
select @.row_size= @.fixed_data_size +
@.variable_data_size +
@.null_bitmap + 4 -- 4 represents the data row header
-- get number of rows per page
select @.rows_per_page = (8096) / (@.row_size+2)
-- If a clustered index is to be created on the table,
-- calculate the number of reserved free rows per page,
-- based on the fill factor specified.
-- If no clustered index is to be created, specify Fill_Factor as 100.
select @.fillfactor = 100 -- initialize it to the maximum
select @.free_rows_per_page = 0 --initialize to no free rows/page
select @.fillfactor=OrigFillFactor
from sysindexes
where id = @.table_id and indid=1 -- indid of 1 means the index is
clustered
if @.fillfactor<>0
-- a 0 fill factor ALMOST fills up the entire page, but not quite.
--The doc says that fill factor zero leaves 2 empty rows (keys)
--in each index page and no free rows in data pages of clustered
--indexes and leaf pages of non-clustered.
--We are working on the data pages in this section
select @.free_rows_per_page=8096 * ((100-@.fillfactor)/100)/@.row_size
-- get number of pages needed to store all rows
select @.num_pages = ceiling(convert(dec,@.num_rows) /
(@.rows_per_page-@.free_rows_per_page))
-- get storage needed for table data
select @.data_space_used=8192*@.num_pages
--*********************************************
-- COMPUTE SIZE OF CLUSTERED INDEX IF ONE EXISTS
--*********************************************
-- create a temporary table to contain columns in clustered index.
System table
-- sysindexkeys has a list of the column numbers contained in the index
select colid into #col_list
from sysindexkeys where id= @.table_id and indid=1 -- indid=1 means
clustered
if (select count(*) from #col_list) >0 -- do the following only if
clustered index exsists
begin
-- get total number and total maximum size of fixed-length columns in
clustered index
select @.num_fixed_ckey_cols=count(name),
@.fixed_ckey_size= sum(length)
from syscolumns
where id= @.table_id and xtype in
(
select xtype from systypes where variable=0
)
and colid in (select * from #col_list)
if @.num_fixed_ckey_cols= 0 --@.fixed_ckey_size is null. change to 0
select @.fixed_ckey_size=0
-- get total number and total maximum size of variable-length columns
in clustered index
select @.num_variable_ckey_cols=count(name),
@.max_var_ckey_size= sum(length)
from syscolumns
where id= @.table_id and xtype in
(
select xtype from systypes where variable=1
)
and colid in (select * from #col_list)
if @.num_variable_ckey_cols= 0 --@.max_var_ckey_size is null. change to
0
select @.max_var_ckey_size=0
-- If there are fixed-length columns in the clustered index,
-- a portion of the index row is reserved for the null bitmap.
Calculate its size:
if @.num_fixed_ckey_cols <> 0
select @.cindex_null_bitmap=2+((@.num_fixed_ckey_cols + 7)/8)
else
select @.cindex_null_bitmap=0
-- If there are variable-length columns in the index, determine how
much
-- space is used to store the columns within the index row:
if @.num_variable_ckey_cols <> 0
select @.variable_ckey_size=2+(@.num_variable_ckey_cols
*2)+@.max_var_ckey_size
else
select @.variable_ckey_size=0
-- Calculate the index row size
select @.cindex_row_size=@.fixed_ckey_size
+@.variable_ckey_size+@.cindex_null_bitmap+1+8
--Next, calculate the number of index rows per page (8096 free bytes
per page):
select @.cindex_rows_per_page=(8096)/(@.cindex_row_size+2)
-- consider fillfactor
if @.fillfactor=0
select @.free_cindex_rows_per_page = 2
else
select @.free_cindex_rows_per_page= 8096 *
((100-@.fillfactor)/100)/@.cindex_row_size
-- Next, calculate the number of pages required to store
-- all the index rows at each level of the index.
select
@.num_pages_clevel_0=ceiling(convert(decimal,(@.data _space_used/8192))/(@.cindex_rows_per_page-@.free_cindex_rows_per_page))
select @.Num_CIndex_Pages=@.num_pages_clevel_0
select @.num_pages_clevel_x=@.num_pages_clevel_0
while @.num_pages_clevel_x <> 1
begin
select
@.num_pages_clevel_y=ceiling(convert(decimal,@.num_p ages_clevel_x)/(@.cindex_rows_per_page-@.free_cindex_rows_per_page))
select @.Num_CIndex_Pages=@.Num_CIndex_Pages+@.num_pages_cle vel_y
select @.num_pages_clevel_x=@.num_pages_clevel_y
end
end
--*********************************************
-- END CLUSTERED INDEX SECTION
--*********************************************
--*********************************************
-- BEGIN NON-CLUSTERED INDEX SECTION
--*********************************************
-- create temp table with non-clustered index info
select indid, colid into #col_list2
from sysindexkeys where id= @.table_id and indid<>1 -- indid=1 means
clustered
if (select count(*) from #col_list2) >0 -- do the following only if
non-clustered indexes exsist
begin
declare @.i int -- a counter variable
select @.i=1 -- initilize to 2, because 1 is id of clustered index
while @.i< 249 -- max number of non-clustered indexes
begin
select @.i=@.i+1 -- look for the next non-clustered index
-- reinitialize all numbers
select @.num_fixed_key_cols = 0,
@.fixed_key_size = 0,
@.num_variable_key_cols = 0,
@.max_var_key_size = 0,
@.index_null_bitmap = 0,
@.variable_key_size = 0,
@.nl_index_row_size = 0,
@.nl_index_rows_per_page = 0,
@.index_row_size = 0,
@.index_rows_per_page = 0,
@.free_index_rows_per_page = 0,
@.num_pages_level_0 = 0,
@.num_pages_level_x = 0,
@.num_pages_level_y = 0,
@.Num_Index_Pages = 0
-- get total number and total maximum size
-- of fixed-length columns in nonclustered index
select @.num_fixed_key_cols=count(name),
@.fixed_key_size= sum(length)
from syscolumns
where id= @.table_id and xtype in
(
select xtype from systypes where variable=0
)
and colid in (select colid from #col_list2 where indid=@.i)
if @.num_fixed_key_cols= 0 --@.fixed_key_size is null. change to 0
select @.fixed_key_size=0
-- get total number and total maximum size of variable-length columns
in index
select @.num_variable_key_cols=count(name),
@.max_var_key_size= sum(length)
from syscolumns
where id= @.table_id and xtype in
(
select xtype from systypes where variable=1
)
and colid in (select colid from #col_list2 where indid=@.i)
if @.num_variable_key_cols= 0 --@.max_var_key_size is null. change to
0
select @.max_var_key_size=0
if @.num_fixed_key_cols = 0 and @.num_variable_key_cols = 0 --there is
no index
continue
-- If there are fixed-length columns in the non-clustered index,
-- a portion of the index row is reserved for the null bitmap.
Calculate its size:
if @.num_fixed_key_cols <> 0
select @.index_null_bitmap=2+((@.num_fixed_key_cols + 7)/8)
else
select @.index_null_bitmap=0
-- If there are variable-length columns in the index, determine how
much
-- space is used to store the columns within the index row:
if @.num_variable_key_cols <> 0
select @.variable_key_size=2+(@.num_variable_key_cols
*2)+@.max_var_key_size
else
select @.variable_key_size=0
-- Calculate the non-leaf index row size
select @.nl_index_row_size=@.fixed_key_size
+@.variable_key_size+@.index_null_bitmap+1+8
--Next, calculate the number of non-leaf index rows per page (8096
free bytes per page):
select @.nl_index_rows_per_page=(8096)/(@.nl_index_row_size+2)
-- Next, calculate the leaf index row size
select @.index_row_size=@.cindex_row_size + @.fixed_key_size +
@.variable_key_size+@.index_null_bitmap+1
-- Next, calculate the number of leaf level index rows per page
select @.index_rows_per_page = 8096/(@.index_row_size + 2)
-- Next, calcuate the number of reserved free index rows/page based
on fill factor
if @.fillfactor=0
-- a 0 fill factor ALMOST fills up the entire page, but not quite.
--The doc says that fill factor zero leaves 2 empty rows (keys)
--in each index page and no free rows in data pages of clustered
--indexes and leaf pages of non-clustered.
--We are working on the non-clustered index pages in this section
select @.free_index_rows_per_page=0
else
select @.free_index_rows_per_page= 8096 *
((100-@.fillfactor)/100)/@.index_row_size
-- Next, calculate the number of pages required to store
-- all the index rows at each level of the index.
select
@.num_pages_level_0=ceiling(convert(decimal,@.num_ro ws)/@.index_rows_per_page-@.free_index_rows_per_page)
select @.Num_Index_Pages=@.num_pages_level_0
select @.num_pages_level_x=@.num_pages_level_0
while @.num_pages_level_x <> 1
begin
select
@.num_pages_level_y=ceiling(convert(decimal,@.num_pa ges_level_x)/@.nl_index_rows_per_page)
select @.Num_Index_Pages=@.Num_Index_Pages+@.num_pages_level _y
select @.num_pages_level_x=@.num_pages_level_y
end
select
@.total_num_nonclustered_index_pages=@.total_num_non clustered_index_pages+@.Num_Index_Pages
end
end
--*********************************************
-- END NON-CLUSTERED INDEX SECTION
--*********************************************
-- display numbers
select @.tot_pages=@.num_pages + @.Num_CIndex_Pages +
@.total_num_nonclustered_index_pages
select @.table_size_in_bytes= 8192*@.tot_pages
select @.table_size_in_kbytes= @.table_size_in_bytes/1024.0
select @.table_size_in_meg= str(@.table_size_in_kbytes/1000.0,17,2)
select substring(@.table_name,1,20) as 'Table Name',
convert(varchar(10),@.table_size_in_meg) as 'MB Estimate',
@.tot_pages as 'Total Pages',
@.num_pages as '#Data Pgs',
@.Num_CIndex_Pages as '#Clustered Idx Pgs',
@.total_num_nonclustered_index_pages as '#NonClustered Idx Pgs'
|||email me and i'll send you the text file...
|||I thought CalcSpace was a cool proc, so just for kicks, I thought I would
try it on an existing table.
The table has 7million rows and currently consumes (with indexes) 4.5gb.
The script CalcSpace estimated it would need 510mb... I didn't
investigate why this returned such a low number, or why my table is
consuming such a high number (when compared to the estimate)
Here's another method that might work for you. It uses 2 existing system
procedures. It's a little backwards, converting the strings to int's, but
it works.
Create table #tblSize (name sysname,rows int,reserved varchar(10),data
varchar(10),idx varchar(10),used varchar(10))
insert into #tblSize EXEC sp_MSforeachtable @.command1='sp_spaceused ''?'''
-- select * from #tblSize order by rows desc
select sum(cast(substring(reserved,1,charindex('KB',data, 1)-1) as
int) ),sum(cast(substring(data,1,charindex('KB',data,1) -1) as int) +
cast(substring(idx,1,charindex('KB',idx,1)-1) as int) ) as used
from #tblSize
Bill
"rb" <srbssr@.yahoo.com> wrote in message
news:eswxO7D9EHA.368@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I want to find out the SQL databases total size in SQL 2000 server, We
> have
> around 55 databases on the server. Can anobody have the query or Stored
> proc
> to find out the total space used in the server. Thanks
> RB
>
>

Need Performance Tips - Zip Code Locator

I have a stored

procedure used to lookup ad's similar to ebay. We want to allow the customer to

search based on their zip code in relation to the items location. This query

works but it takes too long. We only want to return the top 100 records. It also

uses indexed searching for the main search terms. Any ideas what we can do to

improve performance?

CREATE PROCEDURE Search
@.SearchText

varchar(200),
@.CategoryID int = Null,
@.TxtDesc bit = 0,
@.PriceMin money

= 0,
@.PriceMax money = 250000,
@.ZipCode varchar(5) = Null,
@.Distance

smallint = 0,
@.ManMul int = 1000,
@.ModMul int = 1000,
@.TitMul int =

100,
@.DesMul int = 1
AS
BEGIN
DECLARE @.CenterLat float
DECLARE

@.CenterLon float

-- Earth Radius In Miles
DECLARE @.EarthRadius

float
SET @.EarthRadius = 3958.76

-- Determine Lat/Lon For User's Zip

Code
SELECT @.CenterLat = Lat,
@.CenterLon = Long
FROM

List_ZipCodes
WHERE Zip_Code = @.ZipCode

DECLARE @.CntXAxis

float
DECLARE @.CntYAxis float
DECLARE @.CntZAxis float

SET @.CntXAxis

= cos(radians(@.CenterLat)) * cos(radians(@.CenterLon))
SET @.CntYAxis =

cos(radians(@.CenterLat)) * sin(radians(@.CenterLon))
SET @.CntZAxis =

sin(radians(@.CenterLat))

SELECT

TOP(100)
C.Classified_ID,
Manufacturer,
Model,
Title
FROM

Classifieds C
INNER JOIN Classifieds_Categories ON C.Classified_ID =

Classifieds_Categories.Classified_ID
LEFT OUTER JOIN

FREETEXTTABLE(Classifieds, (Manufacturer), @.SearchText)AS f1 ON C.Classified_ID

= f1.[Key]
LEFT OUTER JOIN FREETEXTTABLE(Classifieds, (Model), @.SearchText)AS

f2 ON C.Classified_ID = f2.[Key]
LEFT OUTER JOIN FREETEXTTABLE(Classifieds,

(Title), @.SearchText)AS f3 ON C.Classified_ID = f3.[Key]
INNER JOIN

List_ZipCodes AS ZC ON ZC.Zip_Code = C.Shipping_FromZip
WHERE

((COALESCE(f1.[Rank], 0)* @.ManMul) + (COALESCE(f2.[Rank], 0)* @.ModMul) +

(COALESCE(f3.[Rank], 0) * @.TitMul)) > 500
AND Active = 1
AND

Price_Asking >= @.PriceMin
AND Price_Asking <= @.PriceMax
AND

(Category_ID = COALESCE(@.CategoryID, Category_ID) OR Category_ID IN (SELECT

Category_ID FROM List_Categories WHERE Parent_Category_ID = @.CategoryID))
AND

Shipping_FromZip Is Not Null
AND (@.EarthRadius * acos((cos(radians(ZC.Lat))

* cos(radians(ZC.Long)))*@.CntXAxis + (cos(radians(ZC.Lat)) *

sin(radians(ZC.Long)))*@.CntYAxis + (sin(radians(ZC.Lat)))*@.CntZAxis)) <=

@.Distance
ORDER BY ((COALESCE(f1.[Rank], 0)* @.ManMul) + (COALESCE(f2.[Rank],

0)* @.ModMul) + (COALESCE(f3.[Rank], 0) * @.TitMul)) DESC
END

The ultimate cost for this query is coming from the FTS query. Basically, sqlserver will have to wait for the result to come back for each freetexttable() before it can join to the base table. And also, the resultset returned from the freetexttable() is not indexed. So, if there are lots of data in the resultset, the main query can take a long time to join. In case you have lots of data return from FTS, you might want to create a temp/working table to hold the data. You can then index them before joining with the base table.

Need optimization

Dear Advance,

I used one stored procedure to retrive 3 different result set. and in the codebehind i seperate it. means from the dataset i seperate three different datatable and then show my data as my need.

but the main problem is ... after retriving the datafrom the database i have to user foreach loop to bind the coulmns data to my different custom class.
example:

foreach (DataRow oDrow in MyDataTable.Rows) {

oClass=new Class();

oClass.Name1=oDrow["Name1"] .toString();

oClass.Name2=oDrow["Name2"] .toString();
...

}

1. so my first question is there any optimization possible ?

2. my result set is too loong ... so should keep just one hit to database or hit more than one time

Currently i am optimizing my web application. in the previous version 1 have to hit the database 3/4 times for different purposes. but now it hits only one time... but it takes time in the codebehind to perform different operation.

Any Suggestion

Starting point: Asynchronous SQL

SqlCommand.BeginExecuteReader()

ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.NETDEVFX.v20.en/cpref4/html/O_T_System_Data_SqlClient_SqlCommand_BeginExecuteReader.htm