How to use TOP in DELETE

Hi,

I have a large database in MSSQL 2005 (rows around 3,788,299 : size 4GB)

Two questions

1. SQL query ~ select top 100 * from [tablename], how do capture the next 100 record?

2. SQL query ~ delete top 100 from [tablename] -- MSSQL show me syntax error? How can I only delete the top 100 record?

thk.

Hi,

1. not sure about this one...
2. delete top(100) [tablename] where ........

James Steele

|||

One approach for each question:

Q1:

In SQL Server 2005, we can use ROW_NUMBER() function

SELECT ROW_NUMBER() OVER(ORDER BY id ) AS Row_Number, * FROM yourTable WHERE Row_Number<=100 -- first 100

-----

WHERE Row_Number>100 and Row_Number<=200 --100-200

Q2:

DELETE

tbl_test1WHERE IDIN(

SELECT

TOP 100 IDFROM tbl_test1ORDERBY id)|||

To select the next 100 records, try using ROW_NUMBER(). You can get details from Books Online, or the article below shows how to use it for custom paging.

http://aspnet.4guysfromrolla.com/articles/031506-1.aspx

|||

2. SQL query ~ delete top 100 from [tablename] -- MSSQL show me syntax error? How can I only delete the top 100 record?

New in SQL Server 2005, you can use TOP along with DELETE.

DELETE TOP (100) FROM yourTable

works!

But in SQL Server 2000, this one will not work.

In SQL Server 2000, use this instead:

DELETE yourTable FROM (SELECT TOP 10 * FROM yourTable) AS t1 WHERE yourTable.id=t1.id

How to Use Fixed memory Size in SQL Server 2005

Hi all,
I will label myself a total newbie and i desperately need direction.
Here it goes...I installed SQL Server 2005. I am trying to follow
direction and set the memory to use fixed memory size. I can not find
this option anywhere. I have the memory tab on which i have Server
memory options. here i have "Use AWE to allocate memory" , mimunim
server memory, maximum server memory and other memory options section.
Normally as per the what i understood, i should also have the option
to use fixed size on this tab...but i dont!!!
I thought this might have been because maybe, i dont have advanced
options, so i ran a script from the internet to set my show options to
'1'. This also did not change anything.
So can anyone please help me do what i need to so that i can have the
"Use fixed memory size" option!
Thanks in advance.
SheinazNormally you would set the min and max server memory allocations.
If you have enough memory that you will be using AWE (> 4GB), the min server
memory allocation is ignored.
If you need more information, refer to these articles:
Configuration -Memory, Adjust Memory Usage
http://support.microsoft.com/default.aspx?scid=kb;en-us;q321363
Configuration -Memory, AWE, Not usable < 4GB
http://download.microsoft.com/download/9/c/c/9cc42e30-538b-4451-8fdb-7134a004f94c/Adv64BitEnv.doc
Configuration -Memory, Can not use more than 2GB of memory
http://www.support.microsoft.com/?id=811891
Configuration -Memory, Large Memory Support Is Available in Windows 2000
(AWE)
http://www.support.microsoft.com/?id=283037
Configuration -Memory, SQL Server 7 & 2000 memory usage
http://www.support.microsoft.com/?id=321363
Configuration -Memory, SQL Server Memory
http://sqljunkies.com/Tutorial/0D4FF40A-695C-4327-A41B-F9F2FE2D58F6.scuk
Configuration -Memory, SQL Server to use more than 2 GB of physical memory
http://support.microsoft.com/kb/274750/
Configuration -Memory, Using AWE Memory
http://www.sql-server-performance.com/awe_memory.asp
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<sheinaz@.gmail.com> wrote in message
news:1161900531.090882.263030@.i42g2000cwa.googlegroups.com...
> Hi all,
> I will label myself a total newbie and i desperately need direction.
> Here it goes...I installed SQL Server 2005. I am trying to follow
> direction and set the memory to use fixed memory size. I can not find
> this option anywhere. I have the memory tab on which i have Server
> memory options. here i have "Use AWE to allocate memory" , mimunim
> server memory, maximum server memory and other memory options section.
> Normally as per the what i understood, i should also have the option
> to use fixed size on this tab...but i dont!!!
> I thought this might have been because maybe, i dont have advanced
> options, so i ran a script from the internet to set my show options to
> '1'. This also did not change anything.
> So can anyone please help me do what i need to so that i can have the
> "Use fixed memory size" option!
> Thanks in advance.
> Sheinaz
>|||Hi
sp_configure still has the options to set values for 'min server memory' and
'max server memory', see Books Online for more!
John
"sheinaz@.gmail.com" wrote:
> Hi all,
> I will label myself a total newbie and i desperately need direction.
> Here it goes...I installed SQL Server 2005. I am trying to follow
> direction and set the memory to use fixed memory size. I can not find
> this option anywhere. I have the memory tab on which i have Server
> memory options. here i have "Use AWE to allocate memory" , mimunim
> server memory, maximum server memory and other memory options section.
> Normally as per the what i understood, i should also have the option
> to use fixed size on this tab...but i dont!!!
> I thought this might have been because maybe, i dont have advanced
> options, so i ran a script from the internet to set my show options to
> '1'. This also did not change anything.
> So can anyone please help me do what i need to so that i can have the
> "Use fixed memory size" option!
> Thanks in advance.
> Sheinaz
>|||Hi everyone,
thanks for all your feedback. I set min=max and things already look
better. I figured out the reason i dont have the lock memory option is
because the version of SQL Server 2005 was installed on top of the
trial version, so it was not a clean install, and so possibly this is
the reason why all my options are not available to me. the 2005 trial
version is 9...something and the SQL Server 2005 -licensed version is
8. something. I guess that's the difference.
But already, with your suggestions i was able to reduce mem usage of
the cpu so we're on the right track... i will probably do a clean
install later on to get everythign right.
Thanks again!
sheinaz|||On 27 Oct 2006 06:51:51 -0700, sheinaz@.gmail.com wrote:
>I figured out the reason i dont have the lock memory option is
>because the version of SQL Server 2005 was installed on top of the
>trial version, so it was not a clean install, and so possibly this is
>the reason why all my options are not available to me.
I do not believe there IS an option for fixed memory other than
setting min and max to the same number. At least I could not find any
alternative in my copy of Management Studio (which was a clean
install) or the documentation (which has been updated).
Roy|||> I do not believe there IS an option for fixed memory other than
> setting min and max to the same number.
Correct. EM had such an option in the GUI and all it did was to set max and min to the same value.
:-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:qu74k2l4vgv2vj2568ed1c2qq3h47gj4e7@.4ax.com...
> On 27 Oct 2006 06:51:51 -0700, sheinaz@.gmail.com wrote:
>>I figured out the reason i dont have the lock memory option is
>>because the version of SQL Server 2005 was installed on top of the
>>trial version, so it was not a clean install, and so possibly this is
>>the reason why all my options are not available to me.
> I do not believe there IS an option for fixed memory other than
> setting min and max to the same number. At least I could not find any
> alternative in my copy of Management Studio (which was a clean
> install) or the documentation (which has been updated).
> Roy|||Something is not right here!
"the SQL Server 2005 -licensed version is 8. something. "
ALL versions of SQL Server 2005 are 9.something! As far as I am aware, there
are NO versions of SQL 2005 that are version 8.something.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<sheinaz@.gmail.com> wrote in message
news:1161957111.743790.5630@.i42g2000cwa.googlegroups.com...
> Hi everyone,
> thanks for all your feedback. I set min=max and things already look
> better. I figured out the reason i dont have the lock memory option is
> because the version of SQL Server 2005 was installed on top of the
> trial version, so it was not a clean install, and so possibly this is
> the reason why all my options are not available to me. the 2005 trial
> version is 9...something and the SQL Server 2005 -licensed version is
> 8. something. I guess that's the difference.
> But already, with your suggestions i was able to reduce mem usage of
> the cpu so we're on the right track... i will probably do a clean
> install later on to get everythign right.
> Thanks again!
> sheinaz
>|||this is what is bugging me so much!!
i do not understand it either. especially since i have two purchased
SQL Server 2005 CDs and they are supposed to be exactly the same!
either way, i believe i can limit the mem usage by making min=max.
thanks for all your help everyone!
I gave up tryign to figure out why the version numbers are different.
it beats me. i ever clean my entire machine and reformatted it and
started from scratch to make sure of everything. argh!
i now have entek software problems to deal with...any one have any idea
where i can ask those questions?
thanks
shen
Arnie Rowland wrote:
> Something is not right here!
> "the SQL Server 2005 -licensed version is 8. something. "
> ALL versions of SQL Server 2005 are 9.something! As far as I am aware, there
> are NO versions of SQL 2005 that are version 8.something.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the
> top yourself.
> - H. Norman Schwarzkopf
>
> <sheinaz@.gmail.com> wrote in message
> news:1161957111.743790.5630@.i42g2000cwa.googlegroups.com...
> > Hi everyone,
> > thanks for all your feedback. I set min=max and things already look
> > better. I figured out the reason i dont have the lock memory option is
> > because the version of SQL Server 2005 was installed on top of the
> > trial version, so it was not a clean install, and so possibly this is
> > the reason why all my options are not available to me. the 2005 trial
> > version is 9...something and the SQL Server 2005 -licensed version is
> > 8. something. I guess that's the difference.
> >
> > But already, with your suggestions i was able to reduce mem usage of
> > the cpu so we're on the right track... i will probably do a clean
> > install later on to get everythign right.
> >
> > Thanks again!
> >
> > sheinaz
> >

How to Use Fixed memory Size in SQL Server 2005

Hi all,
I will label myself a total newbie and i desperately need direction.
Here it goes...I installed SQL Server 2005. I am trying to follow
direction and set the memory to use fixed memory size. I can not find
this option anywhere. I have the memory tab on which i have Server
memory options. here i have "Use AWE to allocate memory" , mimunim
server memory, maximum server memory and other memory options section.
Normally as per the what i understood, i should also have the option
to use fixed size on this tab...but i dont!!!
I thought this might have been because maybe, i dont have advanced
options, so i ran a script from the internet to set my show options to
'1'. This also did not change anything.
So can anyone please help me do what i need to so that i can have the
"Use fixed memory size" option!
Thanks in advance.
SheinazSet the min and max server memory to whatever you set as the fixed
memory size. This is documented in the Books on Line.
http://msdn2.microsoft.com/en-us/library/ms191144.aspx
Roy Harvey
Beacon Falls, CT
On 26 Oct 2006 15:11:35 -0700, sheinaz@.gmail.com wrote:
>Hi all,
>I will label myself a total newbie and i desperately need direction.
>Here it goes...I installed SQL Server 2005. I am trying to follow
>direction and set the memory to use fixed memory size. I can not find
>this option anywhere. I have the memory tab on which i have Server
>memory options. here i have "Use AWE to allocate memory" , mimunim
>server memory, maximum server memory and other memory options section.
> Normally as per the what i understood, i should also have the option
>to use fixed size on this tab...but i dont!!!
>I thought this might have been because maybe, i dont have advanced
>options, so i ran a script from the internet to set my show options to
>'1'. This also did not change anything.
>So can anyone please help me do what i need to so that i can have the
>"Use fixed memory size" option!
>Thanks in advance.
>Sheinaz

How to Use Fixed memory Size in SQL Server 2005

Hi all,
I will label myself a total newbie and i desperately need direction.
Here it goes...I installed SQL Server 2005. I am trying to follow
direction and set the memory to use fixed memory size. I can not find
this option anywhere. I have the memory tab on which i have Server
memory options. here i have "Use AWE to allocate memory" , mimunim
server memory, maximum server memory and other memory options section.
Normally as per the what i understood, i should also have the option
to use fixed size on this tab...but i dont!!!
I thought this might have been because maybe, i dont have advanced
options, so i ran a script from the internet to set my show options to
'1'. This also did not change anything.
So can anyone please help me do what i need to so that i can have the
"Use fixed memory size" option!
Thanks in advance.
SheinazNormally you would set the min and max server memory allocations.
If you have enough memory that you will be using AWE (> 4GB), the min server
memory allocation is ignored.
If you need more information, refer to these articles:
Configuration -Memory, Adjust Memory Usage
http://support.microsoft.com/defaul...b;en-us;q321363
Configuration -Memory, AWE, Not usable < 4GB
http://download.microsoft.com/downl...Adv64BitEnv.doc
Configuration -Memory, Can not use more than 2GB of memory
http://www.support.microsoft.com/?id=811891
Configuration -Memory, Large Memory Support Is Available in Windows 2000
(AWE)
http://www.support.microsoft.com/?id=283037
Configuration -Memory, SQL Server 7 & 2000 memory usage
http://www.support.microsoft.com/?id=321363
Configuration -Memory, SQL Server Memory
http://sqljunkies.com/Tutorial/0D4F...F2FE2D58F6.scuk
Configuration -Memory, SQL Server to use more than 2 GB of physical memory
http://support.microsoft.com/kb/274750/
Configuration -Memory, Using AWE Memory
http://www.sql-server-performance.com/awe_memory.asp
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<sheinaz@.gmail.com> wrote in message
news:1161900531.090882.263030@.i42g2000cwa.googlegroups.com...
> Hi all,
> I will label myself a total newbie and i desperately need direction.
> Here it goes...I installed SQL Server 2005. I am trying to follow
> direction and set the memory to use fixed memory size. I can not find
> this option anywhere. I have the memory tab on which i have Server
> memory options. here i have "Use AWE to allocate memory" , mimunim
> server memory, maximum server memory and other memory options section.
> Normally as per the what i understood, i should also have the option
> to use fixed size on this tab...but i dont!!!
> I thought this might have been because maybe, i dont have advanced
> options, so i ran a script from the internet to set my show options to
> '1'. This also did not change anything.
> So can anyone please help me do what i need to so that i can have the
> "Use fixed memory size" option!
> Thanks in advance.
> Sheinaz
>|||Hi
sp_configure still has the options to set values for 'min server memory' and
'max server memory', see Books Online for more!
John
"sheinaz@.gmail.com" wrote:

> Hi all,
> I will label myself a total newbie and i desperately need direction.
> Here it goes...I installed SQL Server 2005. I am trying to follow
> direction and set the memory to use fixed memory size. I can not find
> this option anywhere. I have the memory tab on which i have Server
> memory options. here i have "Use AWE to allocate memory" , mimunim
> server memory, maximum server memory and other memory options section.
> Normally as per the what i understood, i should also have the option
> to use fixed size on this tab...but i dont!!!
> I thought this might have been because maybe, i dont have advanced
> options, so i ran a script from the internet to set my show options to
> '1'. This also did not change anything.
> So can anyone please help me do what i need to so that i can have the
> "Use fixed memory size" option!
> Thanks in advance.
> Sheinaz
>|||Hi everyone,
thanks for all your feedback. I set min=max and things already look
better. I figured out the reason i dont have the lock memory option is
because the version of SQL Server 2005 was installed on top of the
trial version, so it was not a clean install, and so possibly this is
the reason why all my options are not available to me. the 2005 trial
version is 9...something and the SQL Server 2005 -licensed version is
8. something. I guess that's the difference.
But already, with your suggestions i was able to reduce mem usage of
the cpu so we're on the right track... i will probably do a clean
install later on to get everythign right.
Thanks again!
sheinaz|||On 27 Oct 2006 06:51:51 -0700, sheinaz@.gmail.com wrote:

>I figured out the reason i dont have the lock memory option is
>because the version of SQL Server 2005 was installed on top of the
>trial version, so it was not a clean install, and so possibly this is
>the reason why all my options are not available to me.
I do not believe there IS an option for fixed memory other than
setting min and max to the same number. At least I could not find any
alternative in my copy of Management Studio (which was a clean
install) or the documentation (which has been updated).
Roy|||> I do not believe there IS an option for fixed memory other than
> setting min and max to the same number.
Correct. EM had such an option in the GUI and all it did was to set max and
min to the same value.
:-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:qu74k2l4vgv2vj2568ed1c2qq3h47gj4e7@.
4ax.com...
> On 27 Oct 2006 06:51:51 -0700, sheinaz@.gmail.com wrote:
>
> I do not believe there IS an option for fixed memory other than
> setting min and max to the same number. At least I could not find any
> alternative in my copy of Management Studio (which was a clean
> install) or the documentation (which has been updated).
> Roy|||Something is not right here!
"the SQL Server 2005 -licensed version is 8. something. "
ALL versions of SQL Server 2005 are 9.something! As far as I am aware, there
are NO versions of SQL 2005 that are version 8.something.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<sheinaz@.gmail.com> wrote in message
news:1161957111.743790.5630@.i42g2000cwa.googlegroups.com...
> Hi everyone,
> thanks for all your feedback. I set min=max and things already look
> better. I figured out the reason i dont have the lock memory option is
> because the version of SQL Server 2005 was installed on top of the
> trial version, so it was not a clean install, and so possibly this is
> the reason why all my options are not available to me. the 2005 trial
> version is 9...something and the SQL Server 2005 -licensed version is
> 8. something. I guess that's the difference.
> But already, with your suggestions i was able to reduce mem usage of
> the cpu so we're on the right track... i will probably do a clean
> install later on to get everythign right.
> Thanks again!
> sheinaz
>|||this is what is bugging me so much!!
i do not understand it either. especially since i have two purchased
SQL Server 2005 CDs and they are supposed to be exactly the same!
either way, i believe i can limit the mem usage by making min=max.
thanks for all your help everyone!
I gave up tryign to figure out why the version numbers are different.
it beats me. i ever clean my entire machine and reformatted it and
started from scratch to make sure of everything. argh!
i now have entek software problems to deal with...any one have any idea
where i can ask those questions?
thanks
shen
Arnie Rowland wrote:[vbcol=seagreen]
> Something is not right here!
> "the SQL Server 2005 -licensed version is 8. something. "
> ALL versions of SQL Server 2005 are 9.something! As far as I am aware, the
re
> are NO versions of SQL 2005 that are version 8.something.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to th
e
> top yourself.
> - H. Norman Schwarzkopf
>
> <sheinaz@.gmail.com> wrote in message
> news:1161957111.743790.5630@.i42g2000cwa.googlegroups.com...

How to Use Fixed memory Size in SQL Server 2005

Hi all,
I will label myself a total newbie and i desperately need direction.
Here it goes...I installed SQL Server 2005. I am trying to follow
direction and set the memory to use fixed memory size. I can not find
this option anywhere. I have the memory tab on which i have Server
memory options. here i have "Use AWE to allocate memory" , mimunim
server memory, maximum server memory and other memory options section.
Normally as per the what i understood, i should also have the option
to use fixed size on this tab...but i dont!!!
I thought this might have been because maybe, i dont have advanced
options, so i ran a script from the internet to set my show options to
'1'. This also did not change anything.
So can anyone please help me do what i need to so that i can have the
"Use fixed memory size" option!
Thanks in advance.
SheinazSet the min and max server memory to whatever you set as the fixed
memory size. This is documented in the Books on Line.
http://msdn2.microsoft.com/en-us/library/ms191144.aspx
Roy Harvey
Beacon Falls, CT
On 26 Oct 2006 15:11:35 -0700, sheinaz@.gmail.com wrote:

>Hi all,
>I will label myself a total newbie and i desperately need direction.
>Here it goes...I installed SQL Server 2005. I am trying to follow
>direction and set the memory to use fixed memory size. I can not find
>this option anywhere. I have the memory tab on which i have Server
>memory options. here i have "Use AWE to allocate memory" , mimunim
>server memory, maximum server memory and other memory options section.
> Normally as per the what i understood, i should also have the option
>to use fixed size on this tab...but i dont!!!
>I thought this might have been because maybe, i dont have advanced
>options, so i ran a script from the internet to set my show options to
>'1'. This also did not change anything.
>So can anyone please help me do what i need to so that i can have the
>"Use fixed memory size" option!
>Thanks in advance.
>Sheinaz

how to use .mdf/ldf file in network

Hi,

I have installed SQL server 2005 in my machine. Now when we restore the database, I have check the size of database file .mdf/.ldf is very large. So now I wants to place these two files in any other machine in network.

But when I try to do the same, I am getting "Error 5110: network device not supported for database files.".

Please anyone guide me about the process. So that I can place these 2 files in another machine in network or any sharepoint.

Thanks

Regards,

Nipun

The ability to run databases off network drives is not supported by Microsoft. However, it is possible by setting trace flag 1807. Read all about it in the following KB article:

http://support.microsoft.com/kb/304261

HTH!

|||

To keep a long answer short, this is not suggested as network shares are (evil) unreliable as long as they are not build for storing and doing reliable processing (like NAS).

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

As Jens indicated, it is generally NOT a good idea to put the data files on a network share.

(Using a SAN or NAS is quite different and approved.)