Manage huge amount of data

Requirement:
1 billion records to be inserted every day
180 days data to be maintained
180 * 1 billion records = approx no of records = defines the size of the
database
Need to design the database/process to maintain so huge data?
- RHi Rakesh,
Need more info like an idea of the number of tables, size of rows, querying
which will reflect what indexes are required etc...
You will need some good kit though, lots of disks; what sort of fault
tolerance are you looking at because backups are going to be a problem.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Rakesh" <Rakesh@.discussions.microsoft.com> wrote in message
news:CE1870BB-68DB-47B8-A1B8-EFE8C30FB333@.microsoft.com...
> Requirement:
> 1 billion records to be inserted every day
> 180 days data to be maintained
> 180 * 1 billion records = approx no of records = defines the size of the
> database
> Need to design the database/process to maintain so huge data?
>
> - R|||> 1 billion records to be inserted every day
> 180 days data to be maintained
> 180 * 1 billion records = approx no of records = defines the size of the
> database
With an average row size of 100 bytes, this calculates to about 18 TB of
usable space not including index overhead. You'll also need to sustain a
rate of over 10 thousand inserts per second 24x7.
Very large tables are often partitioned for manageability reasons and to
address backup issues like Tony mentioned. Attention to detail is very
important. Unless you have experience working with very large databases, I
suggest you engage consultants with VLDB experience to help you out. We can
help you with specific questions but a project of such magnitude requires
dedicated resources with specialized experience.
Hope this helps.
Dan Guzman
SQL Server MVP
"Rakesh" <Rakesh@.discussions.microsoft.com> wrote in message
news:CE1870BB-68DB-47B8-A1B8-EFE8C30FB333@.microsoft.com...
> Requirement:
> 1 billion records to be inserted every day
> 180 days data to be maintained
> 180 * 1 billion records = approx no of records = defines the size of the
> database
> Need to design the database/process to maintain so huge data?
>
> - R

making record read-only

Hi,

i want to make a certain records read-only using stored procedure. something like:

Update dbo.Articles
SET record = "read-only"
WHERE
ArticleID = @.ArticleID

(you know what i mean)

the reason i want to do that is that im publishing articles and i want to lock the articles once they're published so no body can change them unless they're unpublished.

of course i aslo want to know how to make a record read-write again.

ThanxAnswer: you can not. Row level security is not part of SQL.

What yo ucould do is a Trigger that blos and rolld back the transaction when a row has a specific value (read only flag).

But then - why the heck dont you handle this in the frontend application? Just dont let the user delete.|||well i wanted i did that in the front end.. just wanted to do it at the backend to ensure data integrity.

Thank you|||::just wanted to do it at the backend to ensure data integrity.

Sadly, this is nonsense, as this does not touch the issue of data integrity. Relational integrity has nothing to do with being able to delete something or not.

Now, in the rare case this needs to be blocked, a trigger that blows the transaction is about the only way to go :-)sql

making an item repeat

i have created a sql database with a vb.net front end. i have records that
databse that have dates attached. i want each record to repeat in a given
time frame according to a field in the datarow. is it possible.
someone please help my ship is sinking.
hero281Sorry.
Please provide DDL and sample data.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"hero281" wrote:
> i have created a sql database with a vb.net front end. i have records that
> databse that have dates attached. i want each record to repeat in a given
> time frame according to a field in the datarow. is it possible.
> someone please help my ship is sinking.
> hero281|||"Alejandro Mesa" wrote:
> Sorry.
> Please provide DDL and sample data.
> http://www.aspfaq.com/etiquette.asp?id=5006
>
> AMB
> "hero281" wrote:
> > i have created a sql database with a vb.net front end. i have records that
> > databse that have dates attached. i want each record to repeat in a given
> > time frame according to a field in the datarow. is it possible.
> >
> > someone please help my ship is sinking.
> >
> > hero281
here is a sample layout of the database.
column format:
id taskname duedate freq
autonumber clean floors 06/01/06 30
" " " "
i would like to duplicate this task based on the freq.|||"hero281" <hero281@.discussions.microsoft.com> wrote in message
news:4A89D9EA-A1C0-4389-A53C-818FDB384648@.microsoft.com...
>
> "Alejandro Mesa" wrote:
> > Sorry.
> >
> > Please provide DDL and sample data.
> > http://www.aspfaq.com/etiquette.asp?id=5006
> >
> >
> > AMB
> >
> > "hero281" wrote:
> >
> > > i have created a sql database with a vb.net front end. i have records
that
> > > databse that have dates attached. i want each record to repeat in a
given
> > > time frame according to a field in the datarow. is it possible.
> > >
> > > someone please help my ship is sinking.
> > >
> > > hero281
>
> here is a sample layout of the database.
> column format:
> id taskname duedate freq
> autonumber clean floors 06/01/06 30
> " " " "
> i would like to duplicate this task based on the freq.
>
It's still not clear what you want.
Do you want something like
1 Clean Floor 06/01/06 30
1 Clean Floor 06/02/06 30
.
.
.
1 Clean Floor 06/30/06 30
?
Not really sure what you're doing then with the freq?
What if it's not the same as the number of days in the month?
>
>|||hero281,
A table of numbers will be very handy here.
Why should I consider using an auxiliary numbers table?
http://www.aspfaq.com/show.asp?id=2516
select
[id],
taskname,
duedate,
freq
from
dbo.t1
inner join
dbo.number as n
on n.number <= t1.freq
go
AMB
"hero281" wrote:
>
> "Alejandro Mesa" wrote:
> > Sorry.
> >
> > Please provide DDL and sample data.
> > http://www.aspfaq.com/etiquette.asp?id=5006
> >
> >
> > AMB
> >
> > "hero281" wrote:
> >
> > > i have created a sql database with a vb.net front end. i have records that
> > > databse that have dates attached. i want each record to repeat in a given
> > > time frame according to a field in the datarow. is it possible.
> > >
> > > someone please help my ship is sinking.
> > >
> > > hero281
>
> here is a sample layout of the database.
> column format:
> id taskname duedate freq
> autonumber clean floors 06/01/06 30
> " " " "
> i would like to duplicate this task based on the freq.
>
>
>

Making a dynamic table (view)?

I would like to create a view or something that functions like a table that
contains like 4 years of records (1 record per day). Each record only has
that date in it. That's it. Simple enough, except I don't want this to be
a real table because it will be different day by day/year by year and it
needs to be relatively efficient. SQL2000.
What I have so far is:
begin
declare @.currdate SmallDateTime;
Set @.CurrDate='01/01/'+Cast(Year(GetDate())-1 as Char(4))
Create Table #temp(
aDate SmallDateTime
)
While year(@.CurrDate)<=Year(GetDate())+2
begin
Set @.CurrDate=DateAdd(d,1,@.CurrDate)
Insert Into #temp (aDate) values(@.CurrDate);
end
Select * from #Temp
Drop Table #Temp
end
The problem I see is the use of a temporary table and how that will impact
the server when it gets really loaded. Besides, this is just a procedure, I
need this to function "as if" it were a table to query against.
Any ideas?Why a temporary and not a real one?, you will not hurt your server for havin
g
one, instead, you can make life easier.
Why should I consider using an auxiliary calendar table?
http://www.aspfaq.com/show.asp?id=2519
AMB
"Jon Glazer" wrote:

> I would like to create a view or something that functions like a table tha
t
> contains like 4 years of records (1 record per day). Each record only has
> that date in it. That's it. Simple enough, except I don't want this to b
e
> a real table because it will be different day by day/year by year and it
> needs to be relatively efficient. SQL2000.
> What I have so far is:
> begin
> declare @.currdate SmallDateTime;
> Set @.CurrDate='01/01/'+Cast(Year(GetDate())-1 as Char(4))
> Create Table #temp(
> aDate SmallDateTime
> )
> While year(@.CurrDate)<=Year(GetDate())+2
> begin
> Set @.CurrDate=DateAdd(d,1,@.CurrDate)
> Insert Into #temp (aDate) values(@.CurrDate);
> end
> Select * from #Temp
> Drop Table #Temp
> end
> The problem I see is the use of a temporary table and how that will impact
> the server when it gets really loaded. Besides, this is just a procedure,
I
> need this to function "as if" it were a table to query against.
> Any ideas?
>
>|||Lets just say from a learning perspective, how would I achieve what I wish
to here?
Jon
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:5DC6B54C-311C-4E6E-A07B-1A864041DBE2@.microsoft.com...
> Why a temporary and not a real one?, you will not hurt your server for
> having
> one, instead, you can make life easier.
> Why should I consider using an auxiliary calendar table?
> http://www.aspfaq.com/show.asp?id=2519
>
> AMB
>
> "Jon Glazer" wrote:
>|||Why should I consider using an auxiliary numbers table?
http://www.aspfaq.com/show.asp?id=2516
Example:
use northwind
go
select
identity(int , 0, 1) as number
into
number
from
sysobjects as a
cross join
sysobjects as b
go
alter table number
add constraint pk_number primary key (number)
go
create function ufn_function1 (
@.sd datetime,
@.ed datetime
)
returns table
as
return (
select
dateadd(day, n.number, @.sd) as col_the_date
from
number as n
where
n.number <= datediff(day, @.sd, @.ed)
)
go
select
*
from
ufn_function1('20050128', '20050204')
order by
1
go
drop function ufn_function1
go
drop table number
go
AMB
"Jon Glazer" wrote:

> Lets just say from a learning perspective, how would I achieve what I wish
> to here?
> Jon
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:5DC6B54C-311C-4E6E-A07B-1A864041DBE2@.microsoft.com...
>
>|||You can't generate data out of nothing. Two alternatives to using an actual
table would be to use a table-valued function containing a loop or to use a
view with a large UNION statement and one SELECT for each date. Either of
those structures would get materialized as data the moment they were used,
so they would offer no obvious benefits over using a table.
Auxiliary tables (Calendars, Numbers, etc) are a standard technique for
doing this sort of thing. After all, even 10 years of dates in a Calendar
table is still less than 4000 rows.
David Portas
SQL Server MVP
--|||If you must...
select
dateadd(year,- 1,dateadd(day,datediff(day,0,getdate()),
0)-datepart(dy,getdate
()))
+digit as dt
from (
select 1 + unit.digit +
10 * ten.digit +
100 * hundred.digit +
1000 * thousand.digit as [number]
from (
select 0 union select 1 union select 2 union select 3 union
select 4 union select 5 union select 6 union select 7 union
select 8 union select 9) as unit(digit)
cross join (
select 0 union select 1 union select 2 union select 3 union
select 4 union select 5 union select 6 union select 7 union
select 8 union select 9) as ten(digit)
cross join (
select 0 union select 1 union select 2 union select 3 union
select 4 union select 5 union select 6 union select 7 union
select 8 union select 9) as hundred(digit)
cross join (
select 0 union select 1 union select 2 ) as thousand(digit)
) numbers(digit)
where digit <= datediff(day,dateadd(year,-1,
dateadd(day,datediff(day,0,getdate()),0)
-datepart(dy,getdate()))
,dateadd(year,3,getdate()-datepart(dy,getdate())))
order by digit
-oj
"Jon Glazer" <jglazer.delete.me@.adconn.com> wrote in message
news:SoxKd.62801$re1.24174@.fe2.columbus.rr.com...
>I would like to create a view or something that functions like a table that
>contains like 4 years of records (1 record per day). Each record only has
>that date in it. That's it. Simple enough, except I don't want this to be
>a real table because it will be different day by day/year by year and it
>needs to be relatively efficient. SQL2000.
> What I have so far is:
> begin
> declare @.currdate SmallDateTime;
> Set @.CurrDate='01/01/'+Cast(Year(GetDate())-1 as Char(4))
> Create Table #temp(
> aDate SmallDateTime
> )
> While year(@.CurrDate)<=Year(GetDate())+2
> begin
> Set @.CurrDate=DateAdd(d,1,@.CurrDate)
> Insert Into #temp (aDate) values(@.CurrDate);
> end
> Select * from #Temp
> Drop Table #Temp
> end
> The problem I see is the use of a temporary table and how that will impact
> the server when it gets really loaded. Besides, this is just a procedure,
> I need this to function "as if" it were a table to query against.
> Any ideas?
>sql

make changes to records

I'll make my post clearer now.
I have an application, the user are able to change records in this
application.
Also, there's a button called "What If".
After the user pressed the "What If" button, the user may change the records
and save it. When the user open the same record again, all the changes is
there. (Pls. notice that the user is still in the "What If" environment).
Somehow the changes are save to a mirror table record.
The changes would only affect the real records until the user press another
button called "Accept changes", in which all the changes in the mirrored
table would be adapted into the real record.
Is there any way i can do it via MS Sql Database?
Pls. advice and thanks in advanceThere are ways to do it in sql, the best way to do it in
sql will depend on your application and how frequently it
is used and concurrency requirements.
I would think you are generating a new session or
connection ID each time a user logs into the application,
use that ID and create a temporary work table by
prefix/suffixing the ID to temp table name. When use first
queries the database, populate this table with the
resultset, and then query this table to display data to
user. When user makes changes using What If button you
update this temp table. Only if user clicks on Accept
Changes button, update main table with rows from this temp
table.
Someone here may help you in writing exact code if you
provide tables and queries.
There are different and better ways to do this depending
on what you really want.
hth.
>--Original Message--
>I'll make my post clearer now.
>I have an application, the user are able to change
records in this
>application.
>Also, there's a button called "What If".
>After the user pressed the "What If" button, the user may
change the records
>and save it. When the user open the same record again,
all the changes is
>there. (Pls. notice that the user is still in the "What
If" environment).
>Somehow the changes are save to a mirror table record.
>The changes would only affect the real records until the
user press another
>button called "Accept changes", in which all the changes
in the mirrored
>table would be adapted into the real record.
>Is there any way i can do it via MS Sql Database?
>Pls. advice and thanks in advance
>
>
>.
>|||Hi,
I hope I can provide the table but I'm actually still in way to develop this
system. However, when you said :
"use that ID and create a temporary work table by prefix/suffixing the ID to
temp table name. When use first queries the database, populate this table
with the resultset, and then query this table to display data to user"
Does this mean I should first copy whole the involved table's contents and
put it in the temp table. Then, every changes would be made into the temp
table until the user presses "Accept Changes", which will copy the whole
thing from that temp table into the original table back.
Would this be wise, slow?
Pls. advice and thanks in advance.
pcPirate
"Guru" <gss20@.hotmail.com> wrote in message
news:1501801c3fa3f$4f92f2e0$a001280a@.phx.gbl...
> There are ways to do it in sql, the best way to do it in
> sql will depend on your application and how frequently it
> is used and concurrency requirements.
> I would think you are generating a new session or
> connection ID each time a user logs into the application,
> use that ID and create a temporary work table by
> prefix/suffixing the ID to temp table name. When use first
> queries the database, populate this table with the
> resultset, and then query this table to display data to
> user. When user makes changes using What If button you
> update this temp table. Only if user clicks on Accept
> Changes button, update main table with rows from this temp
> table.
> Someone here may help you in writing exact code if you
> provide tables and queries.
> There are different and better ways to do this depending
> on what you really want.
> hth.|||You don't need to copy the whole table to a temp table,
just copy 10, 20 or 100 rows the user will be looking at
at the time. You should consider putting a contraint on
the form that user cannot look at another set of rows
until he Accepts or Rejects the changes. That way you will
only have to deal with a small set of rows at a time.
hth.
>--Original Message--
>Hi,
>I hope I can provide the table but I'm actually still in
way to develop this
>system. However, when you said :
>"use that ID and create a temporary work table by
prefix/suffixing the ID to
>temp table name. When use first queries the database,
populate this table
>with the resultset, and then query this table to display
data to user"
>Does this mean I should first copy whole the involved
table's contents and
>put it in the temp table. Then, every changes would be
made into the temp
>table until the user presses "Accept Changes", which will
copy the whole
>thing from that temp table into the original table back.
>Would this be wise, slow?
>Pls. advice and thanks in advance.
>pcPirate
>"Guru" <gss20@.hotmail.com> wrote in message
>news:1501801c3fa3f$4f92f2e0$a001280a@.phx.gbl...
>> There are ways to do it in sql, the best way to do it in
>> sql will depend on your application and how frequently
it
>> is used and concurrency requirements.
>> I would think you are generating a new session or
>> connection ID each time a user logs into the
application,
>> use that ID and create a temporary work table by
>> prefix/suffixing the ID to temp table name. When use
first
>> queries the database, populate this table with the
>> resultset, and then query this table to display data to
>> user. When user makes changes using What If button you
>> update this temp table. Only if user clicks on Accept
>> Changes button, update main table with rows from this
temp
>> table.
>> Someone here may help you in writing exact code if you
>> provide tables and queries.
>> There are different and better ways to do this depending
>> on what you really want.
>> hth.
>
>.
>

make changes to records

There are ways to do it in sql, the best way to do it in
sql will depend on your application and how frequently it
is used and concurrency requirements.
I would think you are generating a new session or
connection ID each time a user logs into the application,
use that ID and create a temporary work table by
prefix/suffixing the ID to temp table name. When use first
queries the database, populate this table with the
resultset, and then query this table to display data to
user. When user makes changes using What If button you
update this temp table. Only if user clicks on Accept
Changes button, update main table with rows from this temp
table.
Someone here may help you in writing exact code if you
provide tables and queries.
There are different and better ways to do this depending
on what you really want.
hth.

>--Original Message--
>I'll make my post clearer now.
>I have an application, the user are able to change
records in this
>application.
>Also, there's a button called "What If".
>After the user pressed the "What If" button, the user may
change the records
>and save it. When the user open the same record again,
all the changes is
>there. (Pls. notice that the user is still in the "What
If" environment).
>Somehow the changes are save to a mirror table record.
>The changes would only affect the real records until the
user press another
>button called "Accept changes", in which all the changes
in the mirrored
>table would be adapted into the real record.
>Is there any way i can do it via MS Sql Database?
>Pls. advice and thanks in advance
>
>
>.
>Hi,
I hope I can provide the table but I'm actually still in way to develop this
system. However, when you said :
"use that ID and create a temporary work table by prefix/suffixing the ID to
temp table name. When use first queries the database, populate this table
with the resultset, and then query this table to display data to user"
Does this mean I should first copy whole the involved table's contents and
put it in the temp table. Then, every changes would be made into the temp
table until the user presses "Accept Changes", which will copy the whole
thing from that temp table into the original table back.
Would this be wise, slow?
Pls. advice and thanks in advance.
pcPirate
"Guru" <gss20@.hotmail.com> wrote in message
news:1501801c3fa3f$4f92f2e0$a001280a@.phx
.gbl...
> There are ways to do it in sql, the best way to do it in
> sql will depend on your application and how frequently it
> is used and concurrency requirements.
> I would think you are generating a new session or
> connection ID each time a user logs into the application,
> use that ID and create a temporary work table by
> prefix/suffixing the ID to temp table name. When use first
> queries the database, populate this table with the
> resultset, and then query this table to display data to
> user. When user makes changes using What If button you
> update this temp table. Only if user clicks on Accept
> Changes button, update main table with rows from this temp
> table.
> Someone here may help you in writing exact code if you
> provide tables and queries.
> There are different and better ways to do this depending
> on what you really want.
> hth.|||You don't need to copy the whole table to a temp table,
just copy 10, 20 or 100 rows the user will be looking at
at the time. You should consider putting a contraint on
the form that user cannot look at another set of rows
until he Accepts or Rejects the changes. That way you will
only have to deal with a small set of rows at a time.
hth.

>--Original Message--
>Hi,
>I hope I can provide the table but I'm actually still in
way to develop this
>system. However, when you said :
>"use that ID and create a temporary work table by
prefix/suffixing the ID to
>temp table name. When use first queries the database,
populate this table
>with the resultset, and then query this table to display
data to user"
>Does this mean I should first copy whole the involved
table's contents and
>put it in the temp table. Then, every changes would be
made into the temp
>table until the user presses "Accept Changes", which will
copy the whole
>thing from that temp table into the original table back.
>Would this be wise, slow?
>Pls. advice and thanks in advance.
>pcPirate
>"Guru" <gss20@.hotmail.com> wrote in message
> news:1501801c3fa3f$4f92f2e0$a001280a@.phx
.gbl...
it
application,
first
temp
>
>.
>

Make change on Records

I'll make my post clearer now.
I have an application, the user are able to change records in this
application.
Also, there's a button called "What If".
After the user pressed the "What If" button, the user may change the records
and save it. When the user open the same record again, all the changes is
there. (Pls. notice that the user is still in the "What If" environment).
Somehow the changes are save to a mirror table record.
The changes would only affect the real records until the user press another
button called "Accept changes", in which all the changes in the mirrored
table would be adapted into the real record.
Is there any way i can do it via MS Sql Database?
Pls. advice and thanks in advanceDid you read my previous post?
"pcPirate" <pcPirate2003@.hotmail.com> wrote in message
news:O9SlS5e#DHA.4084@.tk2msftngp13.phx.gbl...
> I'll make my post clearer now.
> I have an application, the user are able to change records in this
> application.
> Also, there's a button called "What If".
> After the user pressed the "What If" button, the user may change the
records
> and save it. When the user open the same record again, all the changes is
> there. (Pls. notice that the user is still in the "What If" environment).
> Somehow the changes are save to a mirror table record.
> The changes would only affect the real records until the user press
another
> button called "Accept changes", in which all the changes in the mirrored
> table would be adapted into the real record.
> Is there any way i can do it via MS Sql Database?
> Pls. advice and thanks in advance
>

Make change on Records

Hi,
I have an application, in which there's a button called "What If".
When the user pressed this button, every changes they made on the records
displayed in form wouldn't affect the real records until the user press
another button called "Accept changes".
Is there any way i can do it via MS Sql Database?
Pls. advice and thanks in advance
pcPirateHi Pirate ( Do you really ?)
What will be happend if the user pressed a button "What if" and went to
take a coffe ( for two hours...)?
Do you really block others until he/she presses another button?
Consider using SELECT with (UPDLOCK) please refer to BOL.
UPDLOCK has the advantage of allowing you to read data (without blocking
other readers) and update it later with the assurance that the data has not
changed since you last read it
"pcPirate" <pcPirate2003@.hotmail.com> wrote in message
news:OeNRYoa#DHA.3436@.tk2msftngp13.phx.gbl...
> Hi,
> I have an application, in which there's a button called "What If".
> When the user pressed this button, every changes they made on the records
> displayed in form wouldn't affect the real records until the user press
> another button called "Accept changes".
> Is there any way i can do it via MS Sql Database?
> Pls. advice and thanks in advance
> pcPirate
>

Make change on Records

I'll make my post clearer now.
I have an application, the user are able to change records in this
application.
Also, there's a button called "What If".
After the user pressed the "What If" button, the user may change the records
and save it. When the user open the same record again, all the changes is
there. (Pls. notice that the user is still in the "What If" environment).
Somehow the changes are save to a mirror table record.
The changes would only affect the real records until the user press another
button called "Accept changes", in which all the changes in the mirrored
table would be adapted into the real record.
Is there any way i can do it via MS Sql Database?
Pls. advice and thanks in advanceDid you read my previous post?
"pcPirate" <pcPirate2003@.hotmail.com> wrote in message
news:O9SlS5e#DHA.4084@.tk2msftngp13.phx.gbl...
> I'll make my post clearer now.
> I have an application, the user are able to change records in this
> application.
> Also, there's a button called "What If".
> After the user pressed the "What If" button, the user may change the
records
> and save it. When the user open the same record again, all the changes is
> there. (Pls. notice that the user is still in the "What If" environment).
> Somehow the changes are save to a mirror table record.
> The changes would only affect the real records until the user press
another
> button called "Accept changes", in which all the changes in the mirrored
> table would be adapted into the real record.
> Is there any way i can do it via MS Sql Database?
> Pls. advice and thanks in advance
>

Make change on Records

Hi,
I have an application, in which there's a button called "What If".
When the user pressed this button, every changes they made on the records
displayed in form wouldn't affect the real records until the user press
another button called "Accept changes".
Is there any way i can do it via MS Sql Database?
Pls. advice and thanks in advance
pcPirateHi Pirate ( Do you really ?)
What will be happend if the user pressed a button "What if" and went to
take a coffe ( for two hours...)?
Do you really block others until he/she presses another button?
Consider using SELECT with (UPDLOCK) please refer to BOL.
UPDLOCK has the advantage of allowing you to read data (without blocking
other readers) and update it later with the assurance that the data has not
changed since you last read it
"pcPirate" <pcPirate2003@.hotmail.com> wrote in message
news:OeNRYoa#DHA.3436@.tk2msftngp13.phx.gbl...
> Hi,
> I have an application, in which there's a button called "What If".
> When the user pressed this button, every changes they made on the records
> displayed in form wouldn't affect the real records until the user press
> another button called "Accept changes".
> Is there any way i can do it via MS Sql Database?
> Pls. advice and thanks in advance
> pcPirate
>

make a stored procedure

Hi

In my table i have a datetime field
now i want to delete all records there are more than 1 hour old
can someone help me with this

Alvin

Hi,

Here is a possible solution:

delete from tableName
where datettimeField < dateadd(hour, -1, getdate())

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

make 5 records in per page

anyone can help me, how to make 5 records in every page on crystal report
thank'sin formatsection
write the formula

recordnumber mod 5 = 0

try this|||thank's for your attention tis707, your formula can working properly|||hi .. would u help too regarding ur Query ... caz at ur side formula works fine but i m unable to understand where to but this formula against which foramtSection property ... hope u understand my confussion ..
i m waiting for ur reply...
bye|||at newpageafter|||Goto the format section of details. Then next to NewPageAfter there is button named x-2. Click that write that code

recordnumber mod 5 = 0

Major Time Out Issue

I have created a database with three tables. The database has been up for a month now and contains about 20,000 records. In order to improve performance and resolve some issues I an attempting to change some of the table information. i.e. allow nulls in a few fields. When I use TSQL or the GUI to make these changes I get the following error: Timeout expired. The timeout period elapsed prior to completion of the operation or server not responding.

Source: .Net SQLClient Data Provider

I have SQL Server Express SP2 installed with .Net framework v3.0

Note: This issue appears when I attempt to delete a row from a table as well.

Any thoughts?

It is difficult to guess -there isn't enough information.

If users are in the database at the same time as you are attempting to make table wide changes, you could be experiencing 'blocking' behavior. Try making your changes when there are no users in the database. (Twenty thousand records is a very small table and most changes should happen relatively quickly.)

You might also verify the indexing.

Major Search Problem

Hi,
I got a problem on searching a keyword with '&'.
I run this query and I got all records with '&'.
select * from freetexttable(table1, *, 'AT&T');
I run this and I got records with AT&T.
select * from table1 where fieldname1 like '%AT&T%'
Since 'AT' and 'T' are considered noise words, how can I search for
'AT&T' as a whole word?
Thanks in advance.
Baldwin
remove t and at from your noise word list, rebuild your catalogs and it
should work.
RelevantNoise.com - dedicated to mining blogs for business intelligence.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Baldwin" <ibaldwinjr@.gmail.com> wrote in message
news:1194974569.682896.215800@.v2g2000hsf.googlegro ups.com...
> Hi,
> I got a problem on searching a keyword with '&'.
> I run this query and I got all records with '&'.
> select * from freetexttable(table1, *, 'AT&T');
> I run this and I got records with AT&T.
> select * from table1 where fieldname1 like '%AT&T%'
> Since 'AT' and 'T' are considered noise words, how can I search for
> 'AT&T' as a whole word?
> Thanks in advance.
> Baldwin
>

Major performance hit in select statement once database reaches 50,000 records

I'm running SQL Server 2005 (64bit) Evaluation Edition. I'm doing an evaluation on performance for an internal project here at work.

The machine has 2GB of RAM and is an AMD 64 running Windows XP 64 with SQL Server 2005 64 Eval

The database has about 200,000 records in the person table.

Select * from person is the statements "very simple"

The results up to 49,000 are acceptable then at 50,000 it goes into a crawl and starts returniong 100 records every 2 seconds.

Does anyone know what is going on here?

I assume you have a very good reason for sending tens of thousands of records back to a client... You may easily solve your problem by using something like a where clause.

The number of records in a table likely has little to do with the issue, more likely is that is has to do with the overall number of pages. For something like a SELECT * the very best plan you can get is a clustered index scan, providing you have a clustered index. Barring the presence of a clustered index you'll see a table scan.

Either one of these plans are going to generate a substantial amount of disk IO. On a machine with 2GB of ram the maximum size for data cache will be around 1.6GB, my guess is that your table exceeds that size and this simple query is creating a great deal of activity on your disk drives.

Another issue is going to be concurrency. For a SELECT * you might well be attempting to acquire a table lock up front.

Start by getting the query plan for your statement. You can do that with either SET STATISTICS PROFILE ON or SET STATISTICS XML ON.

You should also have a look at the amount of disk IO generated by the query, and the performance of your disk drives.

SET STATISTICS IO ON and SET STATISTICS TIME ON will help out there, and you'll need to start perfmon and have a look at the physical disk counters for avg disk sec/read, avg disk sec/write, avg disk sec/transfer for the drives where your data and log files are located. Anything over 10ms is cause for concern.

Be sure to try from different clients as well to rule out any client issues.

|||

Thank you for the input. I ran it again with your recommendations and yes my Disk Write is max'd out completley. The table has 174,000 records of sample data and the query took 1Hour and 29Minutes.

Here is what I got back from prefixing my SQL Select Statement:

SET STATISTICS PROFILE ON

SET STATISTICS IO ON

SET STATISTICS TIME ON

select * from consultants;

174511 1 select * from consultants; 1 1 0 NULL NULL NULL NULL 174511 NULL NULL NULL 20.51673 NULL NULL SELECT 0 NULL

174511 1 |--Clustered Index Scan(OBJECT:([NewRR].[dbo].[Consultants].[aaaaaConsultants_PK])) 1 2 1 Clustered Index Scan Clustered Index Scan OBJECT:([NewRR].[dbo].[Consultants].[aaaaaConsultants_PK]) [NewRR].[dbo].[Consultants].[ConsIntID], [NewRR].[dbo].[Consultants].[ConsultantID], [NewRR].[dbo].[Consultants].[Title], [NewRR].[dbo].[Consultants].[FirstName], [NewRR].[dbo].[Consultants].[MiddleName], [NewRR].[dbo].[Consultants].[LastName], [NewRR].[dbo].[Consultants].[Suffix], [NewRR].[dbo].[Consultants].[NickName], [NewRR].[dbo].[Consultants].[DisplayName], [NewRR].[dbo].[Consultants].[CompanyName], [NewRR].[dbo].[Consultants].[Available], [NewRR].[dbo].[Consultants].[AvailabilityDate], [NewRR].[dbo].[Consultants].[AvailabilityNotice], [NewRR].[dbo].[Consultants].[JobTitle], [NewRR].[dbo].[Consultants].[PrimarySkills], [NewRR].[dbo].[Consultants].[SecondarySkills], [NewRR].[dbo].[Consultants].[OtherSkills], [NewRR].[dbo].[Consultants].[TotalExp], [NewRR].[dbo].[Consultants].[USExp], [NewRR].[dbo].[Consultants].[CommSkills], [NewRR].[dbo].[Consultants].[Rate], [NewRR].[dbo].[Consultants].[Relocation], [NewRR].[dbo].[Consultants].[ResumeDir], [NewRR].[dbo].[Consultants].[ResumeFile], [NewRR].[dbo].[Consultants].[ModifiedResumeDir], [NewRR].[dbo].[Consultants].[ModifiedResumeFile], [NewRR].[dbo].[Consultants].[ResumeWebPath], [NewRR].[dbo].[Consultants].[ReferredBy], [NewRR].[dbo].[Consultants].[Summary], [NewRR].[dbo].[Consultants].[AdditionalInfo], [NewRR].[dbo].[Consultants].[XMLResume], [NewRR].[dbo].[Consultants].[SSN], [NewRR].[dbo].[Consultants].[VisaStatus], [NewRR].[dbo].[Consultants].[VisaExpiryDate], [NewRR].[dbo].[Consultants].[Address1], [NewRR].[dbo].[Consultants].[Address2], [NewRR].[dbo].[Consultants].[Address3], [NewRR].[dbo].[Consultants].[City], [NewRR].[dbo].[Consultants].[State], [NewRR].[dbo].[Consultants].[ZipCode], [NewRR].[dbo].[Consultants].[Country], [NewRR].[dbo].[Consultants].[HomePhone], [NewRR].[dbo].[Consultants].[WorkPhone], [NewRR].[dbo].[Consultants].[MobilePhone], [NewRR].[dbo].[Consultants].[Fax], [NewRR].[dbo].[Consultants].[EMail1], [NewRR].[dbo].[Consultants].[EMail2], [NewRR].[dbo].[Consultants].[Salary], [NewRR].[dbo].[Consultants].[SalaryReviewDate], [NewRR].[dbo].[Consultants].[BonusAmount], [NewRR].[dbo].[Consultants].[BonusAmountDate], [NewRR].[dbo].[Consultants].[DOE], [NewRR].[dbo].[Consultants].[DOT], [NewRR].[dbo].[Consultants].[DOB], [NewRR].[dbo].[Consultants].[DOM], [NewRR].[dbo].[Consultants].[SpouseName], [NewRR].[dbo].[Consultants].[EmergencyContactName], [NewRR].[dbo].[Consultants].[EmergencyPhone], [NewRR].[dbo].[Consultants].[Notes], [NewRR].[dbo].[Consultants].[Archived], [NewRR].[dbo].[Consultants].[SendInHotList], [NewRR].[dbo].[Consultants].[Employee], [NewRR].[dbo].[Consultants].[JobType], [NewRR].[dbo].[Consultants].[Categories], [NewRR].[dbo].[Consultants].[Groups], [NewRR].[dbo].[Consultants].[Owners], [NewRR].[dbo].[Consultants].[EmployeeNumber], [NewRR].[dbo].[Consultants].[OnHold], [NewRR].[dbo].[Consultants].[OnHoldTill], [NewRR].[dbo].[Consultants].[VacationDays], [NewRR].[dbo].[Consultants].[SickDays], [NewRR].[dbo].[Consultants].[TableHolidays], [NewRR].[dbo].[Consultants].[FloatHolidays], [NewRR].[dbo].[Consultants].[LinkToIntID], [NewRR].[dbo].[Consultants].[UserIDs], [NewRR].[dbo].[Consultants].[Private], [NewRR].[dbo].[Consultants].[CreateDate], [NewRR].[dbo].[Consultants].[EditDate], [NewRR].[dbo].[Consultants].[MergeDate], [NewRR].[dbo].[Consultants].[UserField1], [NewRR].[dbo].[Consultants].[UserField2], [NewRR].[dbo].[Consultants].[UserField3], [NewRR].[dbo].[Consultants].[UserField4], [NewRR].[dbo].[Consultants].[UserField5], [NewRR].[dbo].[Consultants].[UserField6], [NewRR].[dbo].[Consultants].[UserField7], [NewRR].[dbo].[Consultants].[UserField8], [NewRR].[dbo].[Consultants].[UserField9], [NewRR].[dbo].[Consultants].[UserField10], [NewRR].[dbo].[Consultants].[Field1], [NewRR].[dbo].[Consultants].[Field2], [NewRR].[dbo].[Consultants].[Field3], [NewRR].[dbo].[Consultants].[uuManager], [NewRR].[dbo].[Consultants].[uuResumeText], [NewRR].[dbo].[Consultants].[uuResponsibilites], [NewRR].[dbo].[Consultants].[uuStartDate], [NewRR].[dbo].[Consul.. 174511 20.32461 0.1921191 7222 20.51673 [NewRR].[dbo].[Consultants].[ConsIntID], [NewRR].[dbo].[Consultants].[ConsultantID], [NewRR].[dbo].[Consultants].[Title], [NewRR].[dbo].[Consultants].[FirstName], [NewRR].[dbo].[Consultants].[MiddleName], [NewRR].[dbo].[Consultants].[LastName], [NewRR].[dbo].[Consultants].[Suffix], [NewRR].[dbo].[Consultants].[NickName], [NewRR].[dbo].[Consultants].[DisplayName], [NewRR].[dbo].[Consultants].[CompanyName], [NewRR].[dbo].[Consultants].[Available], [NewRR].[dbo].[Consultants].[AvailabilityDate], [NewRR].[dbo].[Consultants].[AvailabilityNotice], [NewRR].[dbo].[Consultants].[JobTitle], [NewRR].[dbo].[Consultants].[PrimarySkills], [NewRR].[dbo].[Consultants].[SecondarySkills], [NewRR].[dbo].[Consultants].[OtherSkills], [NewRR].[dbo].[Consultants].[TotalExp], [NewRR].[dbo].[Consultants].[USExp], [NewRR].[dbo].[Consultants].[CommSkills], [NewRR].[dbo].[Consultants].[Rate], [NewRR].[dbo].[Consultants].[Relocation], [NewRR].[dbo].[Consultants].[ResumeDir], [NewRR].[dbo].[Consultants].[ResumeFile], [NewRR].[dbo].[Consultants].[ModifiedResumeDir], [NewRR].[dbo].[Consultants].[ModifiedResumeFile], [NewRR].[dbo].[Consultants].[ResumeWebPath], [NewRR].[dbo].[Consultants].[ReferredBy], [NewRR].[dbo].[Consultants].[Summary], [NewRR].[dbo].[Consultants].[AdditionalInfo], [NewRR].[dbo].[Consultants].[XMLResume], [NewRR].[dbo].[Consultants].[SSN], [NewRR].[dbo].[Consultants].[VisaStatus], [NewRR].[dbo].[Consultants].[VisaExpiryDate], [NewRR].[dbo].[Consultants].[Address1], [NewRR].[dbo].[Consultants].[Address2], [NewRR].[dbo].[Consultants].[Address3], [NewRR].[dbo].[Consultants].[City], [NewRR].[dbo].[Consultants].[State], [NewRR].[dbo].[Consultants].[ZipCode], [NewRR].[dbo].[Consultants].[Country], [NewRR].[dbo].[Consultants].[HomePhone], [NewRR].[dbo].[Consultants].[WorkPhone], [NewRR].[dbo].[Consultants].[MobilePhone], [NewRR].[dbo].[Consultants].[Fax], [NewRR].[dbo].[Consultants].[EMail1], [NewRR].[dbo].[Consultants].[EMail2], [NewRR].[dbo].[Consultants].[Salary], [NewRR].[dbo].[Consultants].[SalaryReviewDate], [NewRR].[dbo].[Consultants].[BonusAmount], [NewRR].[dbo].[Consultants].[BonusAmountDate], [NewRR].[dbo].[Consultants].[DOE], [NewRR].[dbo].[Consultants].[DOT], [NewRR].[dbo].[Consultants].[DOB], [NewRR].[dbo].[Consultants].[DOM], [NewRR].[dbo].[Consultants].[SpouseName], [NewRR].[dbo].[Consultants].[EmergencyContactName], [NewRR].[dbo].[Consultants].[EmergencyPhone], [NewRR].[dbo].[Consultants].[Notes], [NewRR].[dbo].[Consultants].[Archived], [NewRR].[dbo].[Consultants].[SendInHotList], [NewRR].[dbo].[Consultants].[Employee], [NewRR].[dbo].[Consultants].[JobType], [NewRR].[dbo].[Consultants].[Categories], [NewRR].[dbo].[Consultants].[Groups], [NewRR].[dbo].[Consultants].[Owners], [NewRR].[dbo].[Consultants].[EmployeeNumber], [NewRR].[dbo].[Consultants].[OnHold], [NewRR].[dbo].[Consultants].[OnHoldTill], [NewRR].[dbo].[Consultants].[VacationDays], [NewRR].[dbo].[Consultants].[SickDays], [NewRR].[dbo].[Consultants].[TableHolidays], [NewRR].[dbo].[Consultants].[FloatHolidays], [NewRR].[dbo].[Consultants].[LinkToIntID], [NewRR].[dbo].[Consultants].[UserIDs], [NewRR].[dbo].[Consultants].[Private], [NewRR].[dbo].[Consultants].[CreateDate], [NewRR].[dbo].[Consultants].[EditDate], [NewRR].[dbo].[Consultants].[MergeDate], [NewRR].[dbo].[Consultants].[UserField1], [NewRR].[dbo].[Consultants].[UserField2], [NewRR].[dbo].[Consultants].[UserField3], [NewRR].[dbo].[Consultants].[UserField4], [NewRR].[dbo].[Consultants].[UserField5], [NewRR].[dbo].[Consultants].[UserField6], [NewRR].[dbo].[Consultants].[UserField7], [NewRR].[dbo].[Consultants].[UserField8], [NewRR].[dbo].[Consultants].[UserField9], [NewRR].[dbo].[Consultants].[UserField10], [NewRR].[dbo].[Consultants].[Field1], [NewRR].[dbo].[Consultants].[Field2], [NewRR].[dbo].[Consultants].[Field3], [NewRR].[dbo].[Consultants].[uuManager], [NewRR].[dbo].[Consultants].[uuResumeText], [NewRR].[dbo].[Consultants].[uuResponsibilites], [NewRR].[dbo].[Consultants].[uuStartDate], [NewRR].[dbo].[Consul.. NULL PLAN_ROW 0 1

Any guidance would be appreciated.

|||

Well - that certainly is a wide table. Again I assume you have a good reason to send back tens of thousands of rows to a client. If this is for a performance test, I hope this is not indicative of how the application is written.

Since you already have a clustered index scan in the plan the only thing that might help you out here is to defrag the index, assuming you have not already done so.

I find it odd that your disk writes are impacted. There should be no write activity at all generated by SQL Server during execution of this statement, if you have writes then you need to find out what is using your drive and stop it.

I'm really curious about the client though, it seems that results should start flowing immediately. Can you replicate this same behavior from SQLCMD and managment studio?

|||Perhaps it is spooling to tempdb?|||How do u know the disk size is max out

Major performance hit in select statement once database reaches 50,000 records

I'm running SQL Server 2005 (64bit) Evaluation Edition. I'm doing an evaluation on performance for an internal project here at work.

The machine has 2GB of RAM and is an AMD 64 running Windows XP 64 with SQL Server 2005 64 Eval

The database has about 200,000 records in the person table.

Select * from person is the statements "very simple"

The results up to 49,000 are acceptable then at 50,000 it goes into a crawl and starts returniong 100 records every 2 seconds.

Does anyone know what is going on here?

I assume you have a very good reason for sending tens of thousands of records back to a client... You may easily solve your problem by using something like a where clause.

The number of records in a table likely has little to do with the issue, more likely is that is has to do with the overall number of pages. For something like a SELECT * the very best plan you can get is a clustered index scan, providing you have a clustered index. Barring the presence of a clustered index you'll see a table scan.

Either one of these plans are going to generate a substantial amount of disk IO. On a machine with 2GB of ram the maximum size for data cache will be around 1.6GB, my guess is that your table exceeds that size and this simple query is creating a great deal of activity on your disk drives.

Another issue is going to be concurrency. For a SELECT * you might well be attempting to acquire a table lock up front.

Start by getting the query plan for your statement. You can do that with either SET STATISTICS PROFILE ON or SET STATISTICS XML ON.

You should also have a look at the amount of disk IO generated by the query, and the performance of your disk drives.

SET STATISTICS IO ON and SET STATISTICS TIME ON will help out there, and you'll need to start perfmon and have a look at the physical disk counters for avg disk sec/read, avg disk sec/write, avg disk sec/transfer for the drives where your data and log files are located. Anything over 10ms is cause for concern.

Be sure to try from different clients as well to rule out any client issues.

|||

Thank you for the input. I ran it again with your recommendations and yes my Disk Write is max'd out completley. The table has 174,000 records of sample data and the query took 1Hour and 29Minutes.

Here is what I got back from prefixing my SQL Select Statement:

SET STATISTICS PROFILE ON

SET STATISTICS IO ON

SET STATISTICS TIME ON

select * from consultants;

174511 1 select * from consultants; 1 1 0 NULL NULL NULL NULL 174511 NULL NULL NULL 20.51673 NULL NULL SELECT 0 NULL

174511 1 |--Clustered Index Scan(OBJECT:([NewRR].[dbo].[Consultants].[aaaaaConsultants_PK])) 1 2 1 Clustered Index Scan Clustered Index Scan OBJECT:([NewRR].[dbo].[Consultants].[aaaaaConsultants_PK]) [NewRR].[dbo].[Consultants].[ConsIntID], [NewRR].[dbo].[Consultants].[ConsultantID], [NewRR].[dbo].[Consultants].[Title], [NewRR].[dbo].[Consultants].[FirstName], [NewRR].[dbo].[Consultants].[MiddleName], [NewRR].[dbo].[Consultants].[LastName], [NewRR].[dbo].[Consultants].[Suffix], [NewRR].[dbo].[Consultants].[NickName], [NewRR].[dbo].[Consultants].[DisplayName], [NewRR].[dbo].[Consultants].[CompanyName], [NewRR].[dbo].[Consultants].[Available], [NewRR].[dbo].[Consultants].[AvailabilityDate], [NewRR].[dbo].[Consultants].[AvailabilityNotice], [NewRR].[dbo].[Consultants].[JobTitle], [NewRR].[dbo].[Consultants].[PrimarySkills], [NewRR].[dbo].[Consultants].[SecondarySkills], [NewRR].[dbo].[Consultants].[OtherSkills], [NewRR].[dbo].[Consultants].[TotalExp], [NewRR].[dbo].[Consultants].[USExp], [NewRR].[dbo].[Consultants].[CommSkills], [NewRR].[dbo].[Consultants].[Rate], [NewRR].[dbo].[Consultants].[Relocation], [NewRR].[dbo].[Consultants].[ResumeDir], [NewRR].[dbo].[Consultants].[ResumeFile], [NewRR].[dbo].[Consultants].[ModifiedResumeDir], [NewRR].[dbo].[Consultants].[ModifiedResumeFile], [NewRR].[dbo].[Consultants].[ResumeWebPath], [NewRR].[dbo].[Consultants].[ReferredBy], [NewRR].[dbo].[Consultants].[Summary], [NewRR].[dbo].[Consultants].[AdditionalInfo], [NewRR].[dbo].[Consultants].[XMLResume], [NewRR].[dbo].[Consultants].[SSN], [NewRR].[dbo].[Consultants].[VisaStatus], [NewRR].[dbo].[Consultants].[VisaExpiryDate], [NewRR].[dbo].[Consultants].[Address1], [NewRR].[dbo].[Consultants].[Address2], [NewRR].[dbo].[Consultants].[Address3], [NewRR].[dbo].[Consultants].[City], [NewRR].[dbo].[Consultants].[State], [NewRR].[dbo].[Consultants].[ZipCode], [NewRR].[dbo].[Consultants].[Country], [NewRR].[dbo].[Consultants].[HomePhone], [NewRR].[dbo].[Consultants].[WorkPhone], [NewRR].[dbo].[Consultants].[MobilePhone], [NewRR].[dbo].[Consultants].[Fax], [NewRR].[dbo].[Consultants].[EMail1], [NewRR].[dbo].[Consultants].[EMail2], [NewRR].[dbo].[Consultants].[Salary], [NewRR].[dbo].[Consultants].[SalaryReviewDate], [NewRR].[dbo].[Consultants].[BonusAmount], [NewRR].[dbo].[Consultants].[BonusAmountDate], [NewRR].[dbo].[Consultants].[DOE], [NewRR].[dbo].[Consultants].[DOT], [NewRR].[dbo].[Consultants].[DOB], [NewRR].[dbo].[Consultants].[DOM], [NewRR].[dbo].[Consultants].[SpouseName], [NewRR].[dbo].[Consultants].[EmergencyContactName], [NewRR].[dbo].[Consultants].[EmergencyPhone], [NewRR].[dbo].[Consultants].[Notes], [NewRR].[dbo].[Consultants].[Archived], [NewRR].[dbo].[Consultants].[SendInHotList], [NewRR].[dbo].[Consultants].[Employee], [NewRR].[dbo].[Consultants].[JobType], [NewRR].[dbo].[Consultants].[Categories], [NewRR].[dbo].[Consultants].[Groups], [NewRR].[dbo].[Consultants].[Owners], [NewRR].[dbo].[Consultants].[EmployeeNumber], [NewRR].[dbo].[Consultants].[OnHold], [NewRR].[dbo].[Consultants].[OnHoldTill], [NewRR].[dbo].[Consultants].[VacationDays], [NewRR].[dbo].[Consultants].[SickDays], [NewRR].[dbo].[Consultants].[TableHolidays], [NewRR].[dbo].[Consultants].[FloatHolidays], [NewRR].[dbo].[Consultants].[LinkToIntID], [NewRR].[dbo].[Consultants].[UserIDs], [NewRR].[dbo].[Consultants].[Private], [NewRR].[dbo].[Consultants].[CreateDate], [NewRR].[dbo].[Consultants].[EditDate], [NewRR].[dbo].[Consultants].[MergeDate], [NewRR].[dbo].[Consultants].[UserField1], [NewRR].[dbo].[Consultants].[UserField2], [NewRR].[dbo].[Consultants].[UserField3], [NewRR].[dbo].[Consultants].[UserField4], [NewRR].[dbo].[Consultants].[UserField5], [NewRR].[dbo].[Consultants].[UserField6], [NewRR].[dbo].[Consultants].[UserField7], [NewRR].[dbo].[Consultants].[UserField8], [NewRR].[dbo].[Consultants].[UserField9], [NewRR].[dbo].[Consultants].[UserField10], [NewRR].[dbo].[Consultants].[Field1], [NewRR].[dbo].[Consultants].[Field2], [NewRR].[dbo].[Consultants].[Field3], [NewRR].[dbo].[Consultants].[uuManager], [NewRR].[dbo].[Consultants].[uuResumeText], [NewRR].[dbo].[Consultants].[uuResponsibilites], [NewRR].[dbo].[Consultants].[uuStartDate], [NewRR].[dbo].[Consul.. 174511 20.32461 0.1921191 7222 20.51673 [NewRR].[dbo].[Consultants].[ConsIntID], [NewRR].[dbo].[Consultants].[ConsultantID], [NewRR].[dbo].[Consultants].[Title], [NewRR].[dbo].[Consultants].[FirstName], [NewRR].[dbo].[Consultants].[MiddleName], [NewRR].[dbo].[Consultants].[LastName], [NewRR].[dbo].[Consultants].[Suffix], [NewRR].[dbo].[Consultants].[NickName], [NewRR].[dbo].[Consultants].[DisplayName], [NewRR].[dbo].[Consultants].[CompanyName], [NewRR].[dbo].[Consultants].[Available], [NewRR].[dbo].[Consultants].[AvailabilityDate], [NewRR].[dbo].[Consultants].[AvailabilityNotice], [NewRR].[dbo].[Consultants].[JobTitle], [NewRR].[dbo].[Consultants].[PrimarySkills], [NewRR].[dbo].[Consultants].[SecondarySkills], [NewRR].[dbo].[Consultants].[OtherSkills], [NewRR].[dbo].[Consultants].[TotalExp], [NewRR].[dbo].[Consultants].[USExp], [NewRR].[dbo].[Consultants].[CommSkills], [NewRR].[dbo].[Consultants].[Rate], [NewRR].[dbo].[Consultants].[Relocation], [NewRR].[dbo].[Consultants].[ResumeDir], [NewRR].[dbo].[Consultants].[ResumeFile], [NewRR].[dbo].[Consultants].[ModifiedResumeDir], [NewRR].[dbo].[Consultants].[ModifiedResumeFile], [NewRR].[dbo].[Consultants].[ResumeWebPath], [NewRR].[dbo].[Consultants].[ReferredBy], [NewRR].[dbo].[Consultants].[Summary], [NewRR].[dbo].[Consultants].[AdditionalInfo], [NewRR].[dbo].[Consultants].[XMLResume], [NewRR].[dbo].[Consultants].[SSN], [NewRR].[dbo].[Consultants].[VisaStatus], [NewRR].[dbo].[Consultants].[VisaExpiryDate], [NewRR].[dbo].[Consultants].[Address1], [NewRR].[dbo].[Consultants].[Address2], [NewRR].[dbo].[Consultants].[Address3], [NewRR].[dbo].[Consultants].[City], [NewRR].[dbo].[Consultants].[State], [NewRR].[dbo].[Consultants].[ZipCode], [NewRR].[dbo].[Consultants].[Country], [NewRR].[dbo].[Consultants].[HomePhone], [NewRR].[dbo].[Consultants].[WorkPhone], [NewRR].[dbo].[Consultants].[MobilePhone], [NewRR].[dbo].[Consultants].[Fax], [NewRR].[dbo].[Consultants].[EMail1], [NewRR].[dbo].[Consultants].[EMail2], [NewRR].[dbo].[Consultants].[Salary], [NewRR].[dbo].[Consultants].[SalaryReviewDate], [NewRR].[dbo].[Consultants].[BonusAmount], [NewRR].[dbo].[Consultants].[BonusAmountDate], [NewRR].[dbo].[Consultants].[DOE], [NewRR].[dbo].[Consultants].[DOT], [NewRR].[dbo].[Consultants].[DOB], [NewRR].[dbo].[Consultants].[DOM], [NewRR].[dbo].[Consultants].[SpouseName], [NewRR].[dbo].[Consultants].[EmergencyContactName], [NewRR].[dbo].[Consultants].[EmergencyPhone], [NewRR].[dbo].[Consultants].[Notes], [NewRR].[dbo].[Consultants].[Archived], [NewRR].[dbo].[Consultants].[SendInHotList], [NewRR].[dbo].[Consultants].[Employee], [NewRR].[dbo].[Consultants].[JobType], [NewRR].[dbo].[Consultants].[Categories], [NewRR].[dbo].[Consultants].[Groups], [NewRR].[dbo].[Consultants].[Owners], [NewRR].[dbo].[Consultants].[EmployeeNumber], [NewRR].[dbo].[Consultants].[OnHold], [NewRR].[dbo].[Consultants].[OnHoldTill], [NewRR].[dbo].[Consultants].[VacationDays], [NewRR].[dbo].[Consultants].[SickDays], [NewRR].[dbo].[Consultants].[TableHolidays], [NewRR].[dbo].[Consultants].[FloatHolidays], [NewRR].[dbo].[Consultants].[LinkToIntID], [NewRR].[dbo].[Consultants].[UserIDs], [NewRR].[dbo].[Consultants].[Private], [NewRR].[dbo].[Consultants].[CreateDate], [NewRR].[dbo].[Consultants].[EditDate], [NewRR].[dbo].[Consultants].[MergeDate], [NewRR].[dbo].[Consultants].[UserField1], [NewRR].[dbo].[Consultants].[UserField2], [NewRR].[dbo].[Consultants].[UserField3], [NewRR].[dbo].[Consultants].[UserField4], [NewRR].[dbo].[Consultants].[UserField5], [NewRR].[dbo].[Consultants].[UserField6], [NewRR].[dbo].[Consultants].[UserField7], [NewRR].[dbo].[Consultants].[UserField8], [NewRR].[dbo].[Consultants].[UserField9], [NewRR].[dbo].[Consultants].[UserField10], [NewRR].[dbo].[Consultants].[Field1], [NewRR].[dbo].[Consultants].[Field2], [NewRR].[dbo].[Consultants].[Field3], [NewRR].[dbo].[Consultants].[uuManager], [NewRR].[dbo].[Consultants].[uuResumeText], [NewRR].[dbo].[Consultants].[uuResponsibilites], [NewRR].[dbo].[Consultants].[uuStartDate], [NewRR].[dbo].[Consul.. NULL PLAN_ROW 0 1

Any guidance would be appreciated.

|||

Well - that certainly is a wide table. Again I assume you have a good reason to send back tens of thousands of rows to a client. If this is for a performance test, I hope this is not indicative of how the application is written.

Since you already have a clustered index scan in the plan the only thing that might help you out here is to defrag the index, assuming you have not already done so.

I find it odd that your disk writes are impacted. There should be no write activity at all generated by SQL Server during execution of this statement, if you have writes then you need to find out what is using your drive and stop it.

I'm really curious about the client though, it seems that results should start flowing immediately. Can you replicate this same behavior from SQLCMD and managment studio?

|||Perhaps it is spooling to tempdb?|||How do u know the disk size is max out