how to control the number of row?

i want to contorl the output number of row with a certain number when select data from table that not exceed the number with my setting.

For example,if the result is exceed the 500 rows, then i just require 200 rows, that what is the command of this function?

Thanks for any help...

You can try something like:

SELECT top 500
...
from TableName

another more archaic form is to execute

SET ROWCOUNT 500

However, this will also limit the number of records update or inserted, deleted etc. Turn off the limit by:

SET ROWCOUNT 0

The TOP option is in general the better option. Look the two up in books online.

sql

How to control layout of report parameters

I am trying to have lay 3 report parameters in one single row and have
teh next line have just one. In other words, I want to control the
layout of these parameters. How can I do that? I am using Sql Server
2000 Reporting Services.
Also, I wanted to create a checkbox type report parameter.
Thanks.You have no control over this (with any version). You can create your own
front end but if you are using Report Manager then you are pretty much
stuck. There are a few things you can do with style sheets but nothing like
this.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"kulsum" <arif.kulsum@.gmail.com> wrote in message
news:1157051938.689515.234850@.i42g2000cwa.googlegroups.com...
>I am trying to have lay 3 report parameters in one single row and have
> teh next line have just one. In other words, I want to control the
> layout of these parameters. How can I do that? I am using Sql Server
> 2000 Reporting Services.
>
> Also, I wanted to create a checkbox type report parameter.
>
> Thanks.
>|||OK. Thanks Bruce.
Bruce L-C [MVP] wrote:
> You have no control over this (with any version). You can create your own
> front end but if you are using Report Manager then you are pretty much
> stuck. There are a few things you can do with style sheets but nothing like
> this.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "kulsum" <arif.kulsum@.gmail.com> wrote in message
> news:1157051938.689515.234850@.i42g2000cwa.googlegroups.com...
> >I am trying to have lay 3 report parameters in one single row and have
> > teh next line have just one. In other words, I want to control the
> > layout of these parameters. How can I do that? I am using Sql Server
> > 2000 Reporting Services.
> >
> >
> > Also, I wanted to create a checkbox type report parameter.
> >
> >
> > Thanks.
> >

how to concat/pivot rows to column?

hello,

I'm wondering how it's possible to have a select statement resultant rows concatenated into one row and column.
For example:
select letter from alphabet_table
a
b
c
d
e
...
26 rows returned.

Other than a cursor, how would I write a query to return the following:
row1: abcdefghijkl...

thanks in advance!There are a number of ways, none of which is truly generic (ie there isn't a "one size fits all" choice). Without understanding both what lead you to want to concatenate these values (and what rules you use to concatenate them), and what you will do with the concatenated result, I can't give you much useful advice.

-PatP|||Originally posted by Pat Phelan
There are a number of ways, none of which is truly generic (ie there isn't a "one size fits all" choice). Without understanding both what lead you to want to concatenate these values (and what rules you use to concatenate them), and what you will do with the concatenated result, I can't give you much useful advice.

-PatP

PatP, thanks for your reply. After posting I realize I should have included more information.
Here's more specifics:
CREATE TABLE [elements] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[name] [varchar] (50) NOT NULL ,
[description] [varchar] (50) NULL ,
[code] [varchar] (5000) NOT NULL ,
[ord] [int] NOT NULL
) ON [PRIMARY]
GO

elements.code contains html tags, such as table, tr, td. I am using a stored procedure to build html code based on an input parameter. The parameter matches the 'name' column.
so to build a table, i would select the code and order by the ord column. the result is similar to the following:
<table width="100%" border="0">
<tr>
<td>
</td>
<td>
</td>
<td>
</td>
</tr>
</table>
(10 rows).
I would like to query the table based on the parameter passed to return the same results, except in one record:
<table width="100%" border="0"><tr><td></td><td></td><td></td></tr></table>
(1 row).

hope this helps clear it up|||That helps a bunch. The biggest problem that I see is that you can't allow your html table definition to exceed 4000 characters if you use 16 bit characters (aka UTF-8), or 8000 characters if you use 8 bit (OEM) characters. This could be a real problem for complex pages.

With that said, I'd start with:CREATE FUNCTION dbo.tableDef(@.name AS VARCHAR(50) RETURNS VARCHAR(8000) AS BEGIN
DECLARE
@.c VARCHAR(5000)
, @.r VARCHAR(8000)

SELECT @.r = ''
DECLARE z1 CURSOR FOR SELECT [code]
FROM [elements]
WHERE name = @.name
ORDER BY ord

OPEN z1
FETCH z1 INTO @.c

WHILE 0 = @.@.fetch_status
BEGIN
SET @.r = @.r + @.c
FETCH z1 INTO @.c
END

CLOSE z1
DEALLOCATE z1

RETURN @.r
END-PatP|||Oh yeah, usage would help, wouldn't it ? Sorry!SELECT [name], dbo.tableDef([name])
FROM [elements]
GROUP BY [name]-PatP|||Originally posted by Pat Phelan
Oh yeah, usage would help, wouldn't it ? Sorry!SELECT [name], dbo.tableDef([name])
FROM [elements]
GROUP BY [name]-PatP

many thanks, Pat. i was hoping there was a 'simpler' method of reaching this goal. sometimes i wish i could rewrite ms's implementation of the ansi select to include special tricks.
like: select + * from blah would concat results. ;)

i'll let you know how it works, i'm not too worried about the 4/8k character limit, i can always have a couple of columns.

thanks again.

How to compare values in different rows?

Hi

I’ve a table like this (in SQL Server 2000).

v1 v2
2 8
7 10
11 15

13 17

v1 value of a row must be grater than v2 value of previous row, other wise I need to display that row, in above example, I need to display 2nd & 4th rows.

please advise

Thanks

In order to best get this to work you need another row to explicitly sequence the data -- such as ROW_ID or something. The otherwise, you can use Transact SQL extensions, but without an explicit sequence of some kind, the results will be volatile / unpredictable. It would probably be best to avoid the extensions and stick to a CTE here if you can.

Code Snippet

declare @.ex table(id int, v1 int, v2 int)
insert @.ex
select 1, 2, 8 union all
select 2, 7, 10 union all
select 3, 11,15 union all
select 4, 13,17
--select * from @.ex

select a.id, a.v1, a.v2
from @.ex a
join @.ex b
on a.id - 1 = b.id
and a.v1 <= b.v2

/*
id v1 v2
-- -- --
2 7 10
4 13 17
*/

|||

Do you have a field in the table that will indicate what is the 'previous row'? (Something like an IDENTITY field, or datetime of entry, etc.)

|||

The Table is

ID v1 v2
1 2 8
2 7 10
3 11 15

4 13 17

|||

Here it is,

Code Snippet

Create Table #data (

[ID] INT ,

[v1] INT ,

[v2] INT

);

Insert Into #data Values('1','2','8');

Insert Into #data Values('2','7','10');

Insert Into #data Values('3','11','15');

Insert Into #data Values('4','13','17');

Select

Down.*

from

#data Up

Join #data down On Up.Id = Down.Id-1

Where

Up.V2>=down.V1

|||

As long as you have that ID field, you can use the query

Code Snippet

CREATE TABLE prevRowTest (

ID INT,

v1 INT,

v2 INT

)

INSERT INTO prevRowTest VALUES (1,2,8)

INSERT INTO prevRowTest VALUES (2,7,10)

INSERT INTO prevRowTest VALUES (3,11,15)

INSERT INTO prevRowTest VALUES (4,13,17)

SELECT prt.ID, prt.v1, prt.v2

FROM prevRowTest prt JOIN

prevRowTest prev ON prt.id = (prev.ID + 1)

WHERE prt.v1 <= prev.v2

|||

Thanks a lot.

I got my Answer

With Regards

Vijay

How to compare the previous row with current row in SQL Server 2000

Hi,
I have to compare the previous row with current row in a table in SQL Server 2000. Please help me how to do this in a optimized way. Table contains nearly 30 columns and rows count is more than 10 digits.Rows in a result set have no ipmlicit order, so you have to define what "previous" means for this to make any sense. If you mean the previous row in an arbitrary result set (like SELECT * FROM mytable), then your question is meaningless in a SQL context.

-PatP|||My problem is by taking any result set order by like (SELECT * from my table ORDER By Primary key column)
and then Comparing 1st row with 2nd
2nd row with 3rd
3rd row with 4th and so on...|||CREATE TABLE #patp (
patpId INT NOT NULL
CONSTRAINT XPKpatp PRIMARY KEY (patpId)
, baggage VARCHAR(20) NOT NULL
)

INSERT INTO #patp (patpId, baggage)
SELECT 1, 'One' UNION
SELECT 2, 'Two' UNION
SELECT 3, 'Three' UNION
SELECT 5, 'Five' UNION
SELECT 7, 'Seven' UNION
SELECT 11, 'Eleven' UNION
SELECT 13, 'Thirteen' UNION
SELECT 17, 'Seventeen' UNION
SELECT 19, 'Nineteen' UNION
SELECT 23, 'Twenty-Three'

SELECT *
FROM #patp AS a
JOIN #patp AS b
ON (b.patpId = (SELECT Max(z.patpId)
FROM #patp AS z
WHERE z.patpId < a.patpId))-PatP|||Thank you for your response.

Exactly my problem is:

Main Table

AccountNo Version Status Qunatity LastVersion
1 8 I 0 1

History Table

AccountNo Version Status Qunatity LastVersion
1 2 I 0 0
1 3 I 0 0
1 4 I 1 0
1 5 A 1 0
1 6 A 1 0
1 7 I 0 0
1 8 I 0 1

Child Table

AccountNo DocumentID Version Flag LastVersion
1 10 2 0 0
1 11 2 1 0
1 10 3 0 1
1 11 3 1 1

Here I have to filter the duplicate records in History table and child table

Scenario is:

I have to compare first row(2 nd version) with 2 nd row(3 rd version),
if status = I and all columns matches excluding version and LastVersion
then compare in child table for same accountNo for same version,
i.e., 2 version rows with 3 rd version rows for same account no
and documentID, if matches then delete 2nd version row in history and
child tables, like that I have to compare each row. This is only for status I.
finally I have to rearrange all versions and main table will get updated with
the row with higher version in the history table for same accountNo.

Finally result is:

Main Table

AccountNo Version Status Qunatity LastVersion
1 6 I 0 1

History Table (deleted 3 and 8 the version in the original history table,
rearranged versions and last version as 1 for last record

AccountNo Version Status Qunatity LastVersion
1 2 I 0 0
1 3 I 1 0
1 4 A 1 0
1 5 A 1 0
1 6 I 0 1

Child Table

AccountNo DocumentID Version Flag Lastversion
1 10 2 0 1
1 11 2 1 1

Problem: I used cursors to fetch records and compare one by one.
that table is having huge data. Only for retrieving all records it will take
30 min. So totally it is taking nearly 2 hrs. It is having morethan 30 columns.
So please could you tell me how to optimize this.|||Hello every one,

Please help me regarding this issue.
I used cursors to delete the duplicate records.
Nearly for 1000 records it is taking more than 1 hr.
I have record count more than 10 digit.
How to optimize this??|||Yeah, cursors are to be avoided in SQL server, as you are finding out. Use set-based logic to remove your duplicates instead.
Give us the DDL creation script for your table, and the fields you want to comprise a unique key.|||Did you read the hint link at the top of the forum?

Also the order of database has no meaning.

Do you have an IDENTITY Column or a datetime column for when the row was added?

Read the Hint sticky and post some info.

How to commit a transaction even when trigger fails

I have a row that, when updated, triggers a stored procedure. However, if this stored procedure fails, the row is never updated at all.

I know with SQL 2000, you can specify on on_event argument of AFTER so that the transaction is actually "committed" before the trigger goes off, but is there a similar way to do this with SQL 7? I can't seem to find a way to have the "trigger" action take effect regardless if whatever action the trigger causes fails or not.

EdCould you issue a commit right before calling the sp?

how to combine text data rows into single row?

Hi!!!!

Need some quick help on SQL.....DTS packages.

I’ve loaded data from text files into a table which is in the following format.....

Seq_No

Row_No

Data

1

1

ABC

2

2

DEF

3

3

GHI

4

4

JKL

5

1

MNO

6

2

PQR

7

3

STU

8

4

VWX

Every four lines correspond to one single logical record. So, all the data in every 4 rows have to be combined into one big string which’ll then be loaded into one col of another temporary table .....structure below.....

Record_No

Data

1

ABCDEFGHIJKL

2

MNOPQRSTUVWX

I’m looking for a way to achieve this WITHOUT using Cursors (as it’ll slow down performance)......

Any suggestions will be highly appreciated.

what data type is the Data column?|||

One way is to use the CLR Aggregate function for string concatenation shown in the BOL

http://msdn2.microsoft.com/en-us/library/ms254508.aspx

and then run a query like this

select ((Seq_No-1) / 4)+1 as Record_No, dbo.Concatenate(Data) as Data
into dest
from source
group by ((Seq_No-1) / 4)+1
go

select * from dest
go

|||

hi pramy,

please refer to this post

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=586833&SiteID=1

regards

|||

hi Derek,

thanks for ur reply. the data type is of varchar.

|||

pramy, you still need help on this issue? if not please mark an answer.

thanks,

derek

|||

hi all,

thanks for ur response.

Derek, i've got the solution for my problem and the query is as given below.

select 'data' = convert(varchar(100),(a.data)+''+(b.data)+''+(c.data)+''+(d.data))

from test_data a,test_data b,test_data c,test_data d

where ((b.seqno=a.seqno+1 and b.rowno=a.rowno+1)and(c.seqno=b.seqno+1 and c.rowno=b.rowno+1)and(d.seqno=c.seqno+1 and d.rowno=c.rowno+1))

how to combine text data rows into single row?

Hi!!!!

Need some quick help on SQL.....DTS packages.

I’ve loaded data from text files into a table which is in the following format.....

Seq_No

Row_No

Data

1

1

ABC

2

2

DEF

3

3

GHI

4

4

JKL

5

1

MNO

6

2

PQR

7

3

STU

8

4

VWX

Every four lines correspond to one single logical record. So, all the data in every 4 rows have to be combined into one big string which’ll then be loaded into one col of another temporary table .....structure below.....

Record_No

Data

1

ABCDEFGHIJKL

2

MNOPQRSTUVWX

I’m looking for a way to achieve this WITHOUT using Cursors (as it’ll slow down performance)......

Any suggestions will be highly appreciated.

what data type is the Data column?|||

One way is to use the CLR Aggregate function for string concatenation shown in the BOL

http://msdn2.microsoft.com/en-us/library/ms254508.aspx

and then run a query like this

select ((Seq_No-1) / 4)+1 as Record_No, dbo.Concatenate(Data) as Data
into dest
from source
group by ((Seq_No-1) / 4)+1
go

select * from dest
go

|||

hi pramy,

please refer to this post

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=586833&SiteID=1

regards

|||

hi Derek,

thanks for ur reply. the data type is of varchar.

|||

pramy, you still need help on this issue? if not please mark an answer.

thanks,

derek

|||

hi all,

thanks for ur response.

Derek, i've got the solution for my problem and the query is as given below.

select 'data' = convert(varchar(100),(a.data)+''+(b.data)+''+(c.data)+''+(d.data))

from test_data a,test_data b,test_data c,test_data d

where ((b.seqno=a.seqno+1 and b.rowno=a.rowno+1)and(c.seqno=b.seqno+1 and c.rowno=b.rowno+1)and(d.seqno=c.seqno+1 and d.rowno=c.rowno+1))

How to combine Multiple Dataset result in each row of the report

Hi,

I need to produce a report in MS SQL Server Reporting Service 2005 which has some date fields & need to pass these date fields to find out the no_of_User on that date from another database. For example

Itemid

Availabilty_Start_Dt

Availabilty_End_Dt

User_On_St_DT (this info is in another DB & need to pass Availabilty_Start_Dt )

User_On_ED_DT (this info is in another DB & need to pass Availabilty_Start_Dt )

I am facing two problem here. Since hereis two different dataset needed how can I combine the dataset result to produce each row of the report.

REally appreciate if anyone can show me some light on it.

Regards,

write a strored procedure in the backend and try to write the two queries and combine the results and store in a temp table.

use the stored procedure in the reporting services. Backend is the best solution for it.

How to combine different row into one row

Dear all,
I got a table with different columns of same client id as
follows:
e.g.
ClientID Name Age Country
1 Peter
1 32
1 China
Now I want to combine these three rows into one rows as
follows
ClientID Name Age Country
1 Peter 32 China
How can I do that by SQL statements. Just simple SQL
statements.
Not sotred procedures.
Thankshon123456 wrote:
> Dear all,
> I got a table with different columns of same client id as
> follows:
> e.g.
> ClientID Name Age Country
> 1 Peter
> 1 32
> 1 China
>
> Now I want to combine these three rows into one rows as
> follows
> ClientID Name Age Country
> 1 Peter 32 China
> How can I do that by SQL statements. Just simple SQL
> statements.
> Not sotred procedures.
>
> Thanks
What do the spaces in your example represent? What is the key of this
table? If those missing values are always just empty then do:
SELECT clientid,
MAX(name) AS name,
MAX(age) AS age,
MAX(country) AS country
FROM your_table
GROUP BY clientid ;
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
--|||Hi
CREATE TABLE #test (col1 INT, col2 CHAR(1),col3 INT,col4 CHAR(1))
INSERT INTO #Test VALUES (1,'a',NULL,NULL)
INSERT INTO #Test VALUES (1,NULL,32,NULL)
INSERT INTO #Test VALUES (1,NULL,NULL,'b')
SELECT MAX(col1),MAX(col2),MAX(col3),MAX(col4)
FROM #test
--or
SELECT col1,
MAX(CASE WHEN col2 IS NOT NULL THEN col2 end)as col2,
MAX(CASE WHEN col3 IS NOT NULL THEN col3 end)as col3,
MAX(CASE WHEN col4 IS NOT NULL THEN col4 end)as col4
FROM #test
GROUP BY col1
"hon123456" <peterhon321@.yahoo.com.hk> wrote in message
news:1140524835.788163.177040@.g43g2000cwa.googlegroups.com...
> Dear all,
> I got a table with different columns of same client id as
> follows:
> e.g.
> ClientID Name Age Country
> 1 Peter
> 1 32
> 1 China
>
> Now I want to combine these three rows into one rows as
> follows
> ClientID Name Age Country
> 1 Peter 32 China
> How can I do that by SQL statements. Just simple SQL
> statements.
> Not sotred procedures.
>
> Thanks
>|||SELECT ClientID, MIN(Name) as Name, Min(Age) as Age, Min(Country) as
Country
FROM Table
GROUP BY ClientID
Stu|||Thanks for you all.

How to color alternate row ?

Hey all...
I would like to apply back ground color to alternate row so is it possible ?? how ?
ThanksSection expert, color tab, click background color formula box and enter something like

if recordnumber mod 2 = 0 then crSilver else crNoColor|||Thank you very much JaganEllis...

It works for me ...|||But when I export my report in excel sheet it gives me file without background color so how can I get same background color if I export my report in EXCEL?

Thanks|||How are you doing the export?
I believe you can choose to export just the data, or the formatting too.
Certainly works OK for me when I choose File.Export.Export Report and choose the format as the non Data only Excel option, to the application.|||I am exporting manually and what I am doing in crystal report is after previewing my report I go to File menu -> Print -> Export

And to export in Excel I have 2 options to choose from so first one Excel 8.0 (XLS) and 2nd one is Excel 8.0 (XLS) (Extended)

But in none of them I am able to export format too..|||Did you select Data only option? Dont Choose that option

how to clean all messages of a queue ?

How to clean all messages of a queue ?

Online books says it can be done as below:
RECEIVE * FROM ExpenseQueue

But it only delete a row every time,and I found all the three statement only delete a row:

RECEIVE * FROM ExpenseQueue
RECEIVE TOP(1) * FROM ExpenseQueue
RECEIVE TOP(n) * FROM ExpenseQueue

The RECEIVE statement (as it exists today) only fetches messages belonging to a single conversation group at a time. So the only way to get rid of all messages is to call RECEIVE in a loop:

WHILE (1)
BEGIN
RECEIVE * FROM ExpenseQueue
IF (@.@.ROWCOUNT = 0)
BREAK
END

Are you just looking at means to clean up the queue, or do you really want to fetch multiple conversation groups in a single batch. If it is the latter, could you explain your requirements further to help us improve the product in future versions?

Thanks,
Rushi
--
Developer, Service Broker Team, SQL Server Engine
Microsoft Corporation

|||Rushi's answer assumes that you indeed just want to get rid of the messages and leave the conversations active. If you also want to clean up the conversations - say you have run a bunch of tests and found your application doesn't work correctly - you might want to try one of these scripts which work in ascending dgrees of severity. If you truely don't care at all about the messages or conversations, try the last one:

-- End conversations that are in an error state

declare @.handle uniqueidentifier

declare conv cursor for select conversation_handle from sys.conversation_endpoints where state = 'ER'

open conv

fetch NEXT FROM conv into @.handle

while @.@.FETCH_STATUS = 0

Begin

END Conversation @.handle with cleanup

fetch NEXT FROM conv into @.handle

End

close conv

deallocate conv

-- End conversations that are half closed

declare @.handle uniqueidentifier

declare conv cursor for select conversation_handle from sys.conversation_endpoints where state = 'DI'

open conv

fetch NEXT FROM conv into @.handle

while @.@.FETCH_STATUS = 0

Begin

END Conversation @.handle with cleanup

fetch NEXT FROM conv into @.handle

End

close conv

deallocate conv

-- End all conversations

declare @.handle uniqueidentifier

declare conv cursor for select conversation_handle from sys.conversation_endpoints

open conv

fetch NEXT FROM conv into @.handle

while @.@.FETCH_STATUS = 0

Begin

END Conversation @.handle with cleanup

fetch NEXT FROM conv into @.handle

End

close conv

deallocate conv

|||scripts of Roger_MS can solve my problem, but seems it clean all messages of all queues. If I want to clean a queue only, should I use my Queue_Name to replace sys.conversation_endpoints ? Just as

declare conv cursor for select conversation_handle from Queue_Name

And I think it is convenient to use a statement like "alter queue XXX with cleanup" .

Thank you.|||That would clean up one end of the conversations in the queue. Keep in mind that conversations always involve 2 queues so if you want to clean up the conversations you will have to clean up both queues. This is definitely not something you want to do regularly. Clearing out a queue is equivalent to truncating a table - there will often be valuable data destroyed if you do this so this would generally be something you do only during development.

How to choose the hardware for SQL Server wit large data

Hi,
I have a large data base and data keeps adding daily. I have the current
number of rows and row size. How do i calculate the hardware power required
for such data? is there any method to calculate that? using which i may go
for server with 10 processcer with 1tb hard drive and some 5gb ram.
Below is my data detail.
Table Current Rows Daily addition Chars per Row
table 1 6,900,000 10000 30
table 2 4,000,000 5000 85
table 3 4,000,000 5000 35
table 4 2,000,000 2500 30
table 5 20,000 2500 65
table 6 4,000,000 5000 105
Please help me.
Regards,
Kiran7,000,000 is not a big table in database ...
I had one back in 2002 with more than 150,000,000 rows (in an automatic
partitionned view - ok) (Hardware : 2 processors, < 40GBytes).
By the way, you need to evaluate more points:
* peek number of simultaneous connections / peek value of 'count per minute'
* mean size of row data stored and returned
...
Sl.
"N Kiran Kumar" <N Kiran Kumar@.discussions.microsoft.com> a écrit dans le
message de news: 46BB099A-37CB-4D25-9728-1110CB9804CD@.microsoft.com...
> Hi,
> I have a large data base and data keeps adding daily. I have the
> current
> number of rows and row size. How do i calculate the hardware power
> required
> for such data? is there any method to calculate that? using which i may go
> for server with 10 processcer with 1tb hard drive and some 5gb ram.
> Below is my data detail.
> Table Current Rows Daily addition Chars per Row
> table 1 6,900,000 10000 30
> table 2 4,000,000 5000 85
> table 3 4,000,000 5000 35
> table 4 2,000,000 2500 30
> table 5 20,000 2500 65
> table 6 4,000,000 5000 105
> Please help me.
> Regards,
> Kiran
>
>|||Hi
Along with other posts!!
You should also talk to your hardware supplier! The hardware does depend on
what you are doing with your database and usually that tends to change over
time with new releases/requirements etc... Looking at the current hardware
and performance counters you may get some idea, whatever you come up with may
have to be compromised by budget.
As far as disc space goes you will probably need to intellegently place your
data, therefore look for more spindles (or the ability to add more) rather
than fewer discs with higher capacity.
This site and these articles may help
http://www.sql-server-performance.com/articles/per/hardware_planning_p1.aspx
John
"N Kiran Kumar" wrote:
> Hi,
> I have a large data base and data keeps adding daily. I have the current
> number of rows and row size. How do i calculate the hardware power required
> for such data? is there any method to calculate that? using which i may go
> for server with 10 processcer with 1tb hard drive and some 5gb ram.
> Below is my data detail.
> Table Current Rows Daily addition Chars per Row
> table 1 6,900,000 10000 30
> table 2 4,000,000 5000 85
> table 3 4,000,000 5000 35
> table 4 2,000,000 2500 30
> table 5 20,000 2500 65
> table 6 4,000,000 5000 105
> Please help me.
> Regards,
> Kiran
>
>

How to choose the hardware for SQL Server wit large data

Hi,
I have a large data base and data keeps adding daily. I have the current
number of rows and row size. How do i calculate the hardware power required
for such data? is there any method to calculate that? using which i may go
for server with 10 processcer with 1tb hard drive and some 5gb ram.
Below is my data detail.
Table Current Rows Daily addition Chars per Row
table 1 6,900,000 10000 30
table 2 4,000,000 5000 85
table 3 4,000,0005000 35
table 4 2,000,000 2500 30
table 5 20,000 2500 65
table 6 4,000,000 5000 105
Please help me.
Regards,
Kiran
Hi
Along with other posts!!
You should also talk to your hardware supplier! The hardware does depend on
what you are doing with your database and usually that tends to change over
time with new releases/requirements etc... Looking at the current hardware
and performance counters you may get some idea, whatever you come up with may
have to be compromised by budget.
As far as disc space goes you will probably need to intellegently place your
data, therefore look for more spindles (or the ability to add more) rather
than fewer discs with higher capacity.
This site and these articles may help
http://www.sql-server-performance.com/articles/per/hardware_planning_p1.aspx
John
"N Kiran Kumar" wrote:

> Hi,
> I have a large data base and data keeps adding daily. I have the current
> number of rows and row size. How do i calculate the hardware power required
> for such data? is there any method to calculate that? using which i may go
> for server with 10 processcer with 1tb hard drive and some 5gb ram.
> Below is my data detail.
> Table Current Rows Daily addition Chars per Row
> table 1 6,900,000 10000 30
> table 2 4,000,000 5000 85
> table 3 4,000,0005000 35
> table 4 2,000,000 2500 30
> table 5 20,000 2500 65
> table 6 4,000,000 5000 105
> Please help me.
> Regards,
> Kiran
>
>