Can anyone pls help me with any SQL syntax / logic of extracting only the
non-unique records from an SQL table ?
Thanks
ShekharIf i understand correctly you're after a query that returns the duplicates.
If so give this a try :-
SELECT Col001, COUNT(*) FROM tablename
GROUP BY Col001
HAVING COUNT(*) > 1
This will return any Col001 that are duplicate
HTH. Ryan
"Shekhar Gupta" <ShekharGupta@.discussions.microsoft.com> wrote in message
news:38DC7E2D-051B-4573-9C9A-5B6164F99E78@.microsoft.com...
> Can anyone pls help me with any SQL syntax / logic of extracting only the
> non-unique records from an SQL table ?
> Thanks
> Shekhar|||gr8, Thanks Ryan, this worked
shekhar
"Ryan" wrote:
> If i understand correctly you're after a query that returns the duplicates
.
> If so give this a try :-
> SELECT Col001, COUNT(*) FROM tablename
> GROUP BY Col001
> HAVING COUNT(*) > 1
> This will return any Col001 that are duplicate
> --
> HTH. Ryan
>
> "Shekhar Gupta" <ShekharGupta@.discussions.microsoft.com> wrote in message
> news:38DC7E2D-051B-4573-9C9A-5B6164F99E78@.microsoft.com...
>
>
Showing posts with label records. Show all posts
Showing posts with label records. Show all posts
Thursday, March 29, 2012
Sunday, February 26, 2012
Express Edition - limitations?
Hello -
Is the Express Edition able to handle 300 simultaneous users that are only
adding new records? Can it handle a couple million records?
Any help will be greatly appreciated!
--
SandyExpress Edition is limited to 1 CPU, 1 GB of RAM, and 4 GB database size.
What's the row size of the table with a couple of million rows? When
populated, will the database exceed 4 GB? That would be my number one
concern.
Other than that, I think it's really more a hardware question--do you have a
high-speed CPU? High-speed RAM? High-speed hard drives (RAID-1+0)?
Multiple physical drives to provide separate spindles for the O/S, databases
,
and transaction log file?
You can always start with Express and upgrade when needed to Workgroup or
Standard edtion.
Hope that helps,
Joe
"Sandy" wrote:
> Hello -
> Is the Express Edition able to handle 300 simultaneous users that are only
> adding new records? Can it handle a couple million records?
> Any help will be greatly appreciated!
> --
> Sandy|||P.S. And it depends on the application too. In a typical day, how many
records will the average user insert into the database? And more
importantly, over what time period? For example, if all of the activity hit
s
at the end of a shift, you might have a problem. If it's spread over the
entire shift, it may work fine.
Based on 300 users creating 2 million rows in a year, thats 2,000,000 rows /
300 users = 6667 rows per user per year. 6667 / 235 work days = 28 rows per
day. (To account for vacation and holidays, I assumed 47 work w
s * 5 day
s
per w = 235 work days.) So, 300 users * 28 rows = 8,400 rows per day.|||Hi Joe -
Thanks for your reply. The row size is 567 bytes. I calculated the total
GB to be 1.6, assuming 3,000,000 rows. I should be relatively safe in that
regard, if the database size limitation is 4 GB and my calculations are
correct.
The activity should be spread throughout the entire shift, so that should be
okay.
What would you suggest for a minimum in the way of CPU, RAM and high-speed
hard drives? Suggested OS?
--
Sandy
"Joe from WI" wrote:
> P.S. And it depends on the application too. In a typical day, how many
> records will the average user insert into the database? And more
> importantly, over what time period? For example, if all of the activity h
its
> at the end of a shift, you might have a problem. If it's spread over the
> entire shift, it may work fine.
> Based on 300 users creating 2 million rows in a year, thats 2,000,000 rows
/
> 300 users = 6667 rows per user per year. 6667 / 235 work days = 28 rows p
er
> day. (To account for vacation and holidays, I assumed 47 work ws * 5 d
ays
> per w = 235 work days.) So, 300 users * 28 rows = 8,400 rows per day.|||SQL Server Express supports Windows 2000 SP4, Windows XP SP1, and Windows
2003. I'd recommend Windows Server 2003 - Standard Edition. If that doesn'
t
fit your budget, Windows XP Pro could be used but it's not as robust.
You can only have 1 physical CPU so I'd recommend the fastest you can
afford. Keep in mind that Microsoft licenses by the physical CPU so you can
purchase 1 dual core CPU and still be compliant with licensing. A dual-core
runs a little slower but I believe it will give overall better performance
when other services are competing for the CPU.
Top-end AMD's include Athlon 64 X2 4800+, Opteron 280 (dual core), and
Athlon 64 4000+.
Top-end Intel's include Pentium D 840 (dual core), Pentium 4 670, and
Pentium 4 Extreme Edition, and XEON EM64T.
CPU choice is heavily dependent on what motherboard you have/will have. My
personal favorite is the AMD Athlon 64 X2 4800+.
If the computer is only used for SQL Server and there are no extensive
query's, etc., then I'd recommend 2 GB of RAM. If you will be querying thos
e
3 million rows, aggregates (SUM, AVG, etc.), processing data, etc., I'd
recommend 4 GB of RAM. If you can only afford 1 or 2 GB, buy 1 GB sticks
(assuming the motherboard will support them) and hopefully, you'll have open
slots to add more memory later.
The speed of the memory is determined by what the motherboard will support.
If it's AMD, you'll be looking at DDR400 memory. If it's Intel, DDR400,
perhaps DDR2, and perhaps higher clock speeds. Intel often sounds faster bu
t
Intel CPU's use an external memory controller. AMD's have the memory
controller integrated into the CPU for very fast memory access.
Hard drives are dependent on budget. Fastest is Ultra320 SCSI 15,000 RPM
drives. In the SATA world, Western Digital Raptor 74GB SATA 10,000 RPM are
the fastest, primarily due to the access time of 4.5ms. (Other SATA drives
like ATA/IDE run at 7200 RPM and access data around 8.5 to 9ms.) If you
cannot afford SCSI, I'd highly recommend the WD Raptors.
An PCI/PCI-e RAID controller that has the RAID processor built into it is
the fastest solution. Controllers integrated into the motherboard utilize
the CPU for processing and are a bottleneck. One solution is to use the
motherboard HDD controller for the O/S drives in a RAID-1 (mirroring) array
as the O/S drive doesn't get heavy pounding. Then, use a separate controlle
r
for the database drives.
If you don't want to loose data, you'll need to protect, at a minumum, the
database log file (a.k.a. the transaction log) with RAID-1 (mirroring) or
RAID-1+0. Do NOT use RAID-5. In my opinion, too much overhead to calculate
parity. The log file is written to serially so if you can dedicate a
separate pair of drives, there will be minimum hard drive head movement and
will give you the best throughput.
If you can't afford for the box to be down, you'll need RAID-1 or RAID-1+0
for the O/S and for the database. Do NOT use RAID-5. In my opinion, too
much overhead to calculate and re-calculate (when data changes) parity
information.
Sorry for the long post. Hope that helps,
Joe
"Sandy" wrote:
> Hi Joe -
> Thanks for your reply. The row size is 567 bytes. I calculated the total
> GB to be 1.6, assuming 3,000,000 rows. I should be relatively safe in tha
t
> regard, if the database size limitation is 4 GB and my calculations are
> correct.
> The activity should be spread throughout the entire shift, so that should
be
> okay.
> What would you suggest for a minimum in the way of CPU, RAM and high-speed
> hard drives? Suggested OS?
> --
> Sandy
>
> "Joe from WI" wrote:
>|||Note that Express is 32 bit only so a 64 bit processor isn't of much use to
an Express installation.
I think you biggest issue with that many simultaneous users will be the 1GB
limit on memory. Each user requires some memory so a lot of users will
stretch the 1GB limit. If the load from each user is fairly light, you
might want to close the connection after each insert. Otherwise, I doubt
that you will get adequate performance from Express.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Joe from WI" <JoefromWI@.discussions.microsoft.com> wrote in message
news:0FC7ADE0-75A2-4D38-B826-A1BE395F31B5@.microsoft.com...
> SQL Server Express supports Windows 2000 SP4, Windows XP SP1, and Windows
> 2003. I'd recommend Windows Server 2003 - Standard Edition. If that
> doesn't
> fit your budget, Windows XP Pro could be used but it's not as robust.
> You can only have 1 physical CPU so I'd recommend the fastest you can
> afford. Keep in mind that Microsoft licenses by the physical CPU so you
> can
> purchase 1 dual core CPU and still be compliant with licensing. A
> dual-core
> runs a little slower but I believe it will give overall better performance
> when other services are competing for the CPU.
> Top-end AMD's include Athlon 64 X2 4800+, Opteron 280 (dual core), and
> Athlon 64 4000+.
> Top-end Intel's include Pentium D 840 (dual core), Pentium 4 670, and
> Pentium 4 Extreme Edition, and XEON EM64T.
> CPU choice is heavily dependent on what motherboard you have/will have.
> My
> personal favorite is the AMD Athlon 64 X2 4800+.
> If the computer is only used for SQL Server and there are no extensive
> query's, etc., then I'd recommend 2 GB of RAM. If you will be querying
> those
> 3 million rows, aggregates (SUM, AVG, etc.), processing data, etc., I'd
> recommend 4 GB of RAM. If you can only afford 1 or 2 GB, buy 1 GB sticks
> (assuming the motherboard will support them) and hopefully, you'll have
> open
> slots to add more memory later.
> The speed of the memory is determined by what the motherboard will
> support.
> If it's AMD, you'll be looking at DDR400 memory. If it's Intel, DDR400,
> perhaps DDR2, and perhaps higher clock speeds. Intel often sounds faster
> but
> Intel CPU's use an external memory controller. AMD's have the memory
> controller integrated into the CPU for very fast memory access.
> Hard drives are dependent on budget. Fastest is Ultra320 SCSI 15,000 RPM
> drives. In the SATA world, Western Digital Raptor 74GB SATA 10,000 RPM
> are
> the fastest, primarily due to the access time of 4.5ms. (Other SATA
> drives
> like ATA/IDE run at 7200 RPM and access data around 8.5 to 9ms.) If you
> cannot afford SCSI, I'd highly recommend the WD Raptors.
> An PCI/PCI-e RAID controller that has the RAID processor built into it is
> the fastest solution. Controllers integrated into the motherboard utilize
> the CPU for processing and are a bottleneck. One solution is to use the
> motherboard HDD controller for the O/S drives in a RAID-1 (mirroring)
> array
> as the O/S drive doesn't get heavy pounding. Then, use a separate
> controller
> for the database drives.
> If you don't want to loose data, you'll need to protect, at a minumum, the
> database log file (a.k.a. the transaction log) with RAID-1 (mirroring) or
> RAID-1+0. Do NOT use RAID-5. In my opinion, too much overhead to
> calculate
> parity. The log file is written to serially so if you can dedicate a
> separate pair of drives, there will be minimum hard drive head movement
> and
> will give you the best throughput.
> If you can't afford for the box to be down, you'll need RAID-1 or RAID-1+0
> for the O/S and for the database. Do NOT use RAID-5. In my opinion, too
> much overhead to calculate and re-calculate (when data changes) parity
> information.
> Sorry for the long post. Hope that helps,
> Joe
> "Sandy" wrote:
>|||Thank you very much, Joe!
--
Sandy
"Joe from WI" wrote:
> SQL Server Express supports Windows 2000 SP4, Windows XP SP1, and Windows
> 2003. I'd recommend Windows Server 2003 - Standard Edition. If that does
n't
> fit your budget, Windows XP Pro could be used but it's not as robust.
> You can only have 1 physical CPU so I'd recommend the fastest you can
> afford. Keep in mind that Microsoft licenses by the physical CPU so you c
an
> purchase 1 dual core CPU and still be compliant with licensing. A dual-co
re
> runs a little slower but I believe it will give overall better performance
> when other services are competing for the CPU.
> Top-end AMD's include Athlon 64 X2 4800+, Opteron 280 (dual core), and
> Athlon 64 4000+.
> Top-end Intel's include Pentium D 840 (dual core), Pentium 4 670, and
> Pentium 4 Extreme Edition, and XEON EM64T.
> CPU choice is heavily dependent on what motherboard you have/will have. M
y
> personal favorite is the AMD Athlon 64 X2 4800+.
> If the computer is only used for SQL Server and there are no extensive
> query's, etc., then I'd recommend 2 GB of RAM. If you will be querying th
ose
> 3 million rows, aggregates (SUM, AVG, etc.), processing data, etc., I'd
> recommend 4 GB of RAM. If you can only afford 1 or 2 GB, buy 1 GB sticks
> (assuming the motherboard will support them) and hopefully, you'll have op
en
> slots to add more memory later.
> The speed of the memory is determined by what the motherboard will support
.
> If it's AMD, you'll be looking at DDR400 memory. If it's Intel, DDR400,
> perhaps DDR2, and perhaps higher clock speeds. Intel often sounds faster
but
> Intel CPU's use an external memory controller. AMD's have the memory
> controller integrated into the CPU for very fast memory access.
> Hard drives are dependent on budget. Fastest is Ultra320 SCSI 15,000 RPM
> drives. In the SATA world, Western Digital Raptor 74GB SATA 10,000 RPM ar
e
> the fastest, primarily due to the access time of 4.5ms. (Other SATA drive
s
> like ATA/IDE run at 7200 RPM and access data around 8.5 to 9ms.) If you
> cannot afford SCSI, I'd highly recommend the WD Raptors.
> An PCI/PCI-e RAID controller that has the RAID processor built into it is
> the fastest solution. Controllers integrated into the motherboard utilize
> the CPU for processing and are a bottleneck. One solution is to use the
> motherboard HDD controller for the O/S drives in a RAID-1 (mirroring) arra
y
> as the O/S drive doesn't get heavy pounding. Then, use a separate control
ler
> for the database drives.
> If you don't want to loose data, you'll need to protect, at a minumum, the
> database log file (a.k.a. the transaction log) with RAID-1 (mirroring) or
> RAID-1+0. Do NOT use RAID-5. In my opinion, too much overhead to calcula
te
> parity. The log file is written to serially so if you can dedicate a
> separate pair of drives, there will be minimum hard drive head movement an
d
> will give you the best throughput.
> If you can't afford for the box to be down, you'll need RAID-1 or RAID-1+0
> for the O/S and for the database. Do NOT use RAID-5. In my opinion, too
> much overhead to calculate and re-calculate (when data changes) parity
> information.
> Sorry for the long post. Hope that helps,
> Joe
> "Sandy" wrote:
>|||Thanks Roger!
--
Sandy
"Roger Wolter[MSFT]" wrote:
> Note that Express is 32 bit only so a 64 bit processor isn't of much use t
o
> an Express installation.
> I think you biggest issue with that many simultaneous users will be the 1G
B
> limit on memory. Each user requires some memory so a lot of users will
> stretch the 1GB limit. If the load from each user is fairly light, you
> might want to close the connection after each insert. Otherwise, I doubt
> that you will get adequate performance from Express.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Joe from WI" <JoefromWI@.discussions.microsoft.com> wrote in message
> news:0FC7ADE0-75A2-4D38-B826-A1BE395F31B5@.microsoft.com...
>
>|||oops...I got carried away in my memory statement.
I recommended 2 GB of RAM because 1 GB can be utilized by SQL Server 2005
Express and the other 1 GB can be used by Windows, free space, etc. With
Express, there is no need to have more memory than 2 GB. (Unless of course,
you have other memory hungry applications on the same computer.)
The 4 GB of RAM recommendation would require Workgroup or Standard in order
to make use of it.
Sorry for the error, confusion, misstatement.
Joe
"Sandy" wrote:
> Thank you very much, Joe!
> --
> Sandy
>
> "Joe from WI" wrote:
>|||Also, Express is limited by 1 scheduler. A dual-core won't help you on the
SQL Server side of things.
"Joe from WI" wrote:
> oops...I got carried away in my memory statement.
> I recommended 2 GB of RAM because 1 GB can be utilized by SQL Server 2005
> Express and the other 1 GB can be used by Windows, free space, etc. With
> Express, there is no need to have more memory than 2 GB. (Unless of cours
e,
> you have other memory hungry applications on the same computer.)
> The 4 GB of RAM recommendation would require Workgroup or Standard in orde
r
> to make use of it.
> Sorry for the error, confusion, misstatement.
> Joe
> "Sandy" wrote:
>
Is the Express Edition able to handle 300 simultaneous users that are only
adding new records? Can it handle a couple million records?
Any help will be greatly appreciated!
--
SandyExpress Edition is limited to 1 CPU, 1 GB of RAM, and 4 GB database size.
What's the row size of the table with a couple of million rows? When
populated, will the database exceed 4 GB? That would be my number one
concern.
Other than that, I think it's really more a hardware question--do you have a
high-speed CPU? High-speed RAM? High-speed hard drives (RAID-1+0)?
Multiple physical drives to provide separate spindles for the O/S, databases
,
and transaction log file?
You can always start with Express and upgrade when needed to Workgroup or
Standard edtion.
Hope that helps,
Joe
"Sandy" wrote:
> Hello -
> Is the Express Edition able to handle 300 simultaneous users that are only
> adding new records? Can it handle a couple million records?
> Any help will be greatly appreciated!
> --
> Sandy|||P.S. And it depends on the application too. In a typical day, how many
records will the average user insert into the database? And more
importantly, over what time period? For example, if all of the activity hit
s
at the end of a shift, you might have a problem. If it's spread over the
entire shift, it may work fine.
Based on 300 users creating 2 million rows in a year, thats 2,000,000 rows /
300 users = 6667 rows per user per year. 6667 / 235 work days = 28 rows per
day. (To account for vacation and holidays, I assumed 47 work w
s * 5 days
per w
= 235 work days.) So, 300 users * 28 rows = 8,400 rows per day.|||Hi Joe -Thanks for your reply. The row size is 567 bytes. I calculated the total
GB to be 1.6, assuming 3,000,000 rows. I should be relatively safe in that
regard, if the database size limitation is 4 GB and my calculations are
correct.
The activity should be spread throughout the entire shift, so that should be
okay.
What would you suggest for a minimum in the way of CPU, RAM and high-speed
hard drives? Suggested OS?
--
Sandy
"Joe from WI" wrote:
> P.S. And it depends on the application too. In a typical day, how many
> records will the average user insert into the database? And more
> importantly, over what time period? For example, if all of the activity h
its
> at the end of a shift, you might have a problem. If it's spread over the
> entire shift, it may work fine.
> Based on 300 users creating 2 million rows in a year, thats 2,000,000 rows
/
> 300 users = 6667 rows per user per year. 6667 / 235 work days = 28 rows p
er
> day. (To account for vacation and holidays, I assumed 47 work w
s * 5 days
> per w
= 235 work days.) So, 300 users * 28 rows = 8,400 rows per day.|||SQL Server Express supports Windows 2000 SP4, Windows XP SP1, and Windows2003. I'd recommend Windows Server 2003 - Standard Edition. If that doesn'
t
fit your budget, Windows XP Pro could be used but it's not as robust.
You can only have 1 physical CPU so I'd recommend the fastest you can
afford. Keep in mind that Microsoft licenses by the physical CPU so you can
purchase 1 dual core CPU and still be compliant with licensing. A dual-core
runs a little slower but I believe it will give overall better performance
when other services are competing for the CPU.
Top-end AMD's include Athlon 64 X2 4800+, Opteron 280 (dual core), and
Athlon 64 4000+.
Top-end Intel's include Pentium D 840 (dual core), Pentium 4 670, and
Pentium 4 Extreme Edition, and XEON EM64T.
CPU choice is heavily dependent on what motherboard you have/will have. My
personal favorite is the AMD Athlon 64 X2 4800+.
If the computer is only used for SQL Server and there are no extensive
query's, etc., then I'd recommend 2 GB of RAM. If you will be querying thos
e
3 million rows, aggregates (SUM, AVG, etc.), processing data, etc., I'd
recommend 4 GB of RAM. If you can only afford 1 or 2 GB, buy 1 GB sticks
(assuming the motherboard will support them) and hopefully, you'll have open
slots to add more memory later.
The speed of the memory is determined by what the motherboard will support.
If it's AMD, you'll be looking at DDR400 memory. If it's Intel, DDR400,
perhaps DDR2, and perhaps higher clock speeds. Intel often sounds faster bu
t
Intel CPU's use an external memory controller. AMD's have the memory
controller integrated into the CPU for very fast memory access.
Hard drives are dependent on budget. Fastest is Ultra320 SCSI 15,000 RPM
drives. In the SATA world, Western Digital Raptor 74GB SATA 10,000 RPM are
the fastest, primarily due to the access time of 4.5ms. (Other SATA drives
like ATA/IDE run at 7200 RPM and access data around 8.5 to 9ms.) If you
cannot afford SCSI, I'd highly recommend the WD Raptors.
An PCI/PCI-e RAID controller that has the RAID processor built into it is
the fastest solution. Controllers integrated into the motherboard utilize
the CPU for processing and are a bottleneck. One solution is to use the
motherboard HDD controller for the O/S drives in a RAID-1 (mirroring) array
as the O/S drive doesn't get heavy pounding. Then, use a separate controlle
r
for the database drives.
If you don't want to loose data, you'll need to protect, at a minumum, the
database log file (a.k.a. the transaction log) with RAID-1 (mirroring) or
RAID-1+0. Do NOT use RAID-5. In my opinion, too much overhead to calculate
parity. The log file is written to serially so if you can dedicate a
separate pair of drives, there will be minimum hard drive head movement and
will give you the best throughput.
If you can't afford for the box to be down, you'll need RAID-1 or RAID-1+0
for the O/S and for the database. Do NOT use RAID-5. In my opinion, too
much overhead to calculate and re-calculate (when data changes) parity
information.
Sorry for the long post. Hope that helps,
Joe
"Sandy" wrote:
> Hi Joe -
> Thanks for your reply. The row size is 567 bytes. I calculated the total
> GB to be 1.6, assuming 3,000,000 rows. I should be relatively safe in tha
t
> regard, if the database size limitation is 4 GB and my calculations are
> correct.
> The activity should be spread throughout the entire shift, so that should
be
> okay.
> What would you suggest for a minimum in the way of CPU, RAM and high-speed
> hard drives? Suggested OS?
> --
> Sandy
>
> "Joe from WI" wrote:
>|||Note that Express is 32 bit only so a 64 bit processor isn't of much use to
an Express installation.
I think you biggest issue with that many simultaneous users will be the 1GB
limit on memory. Each user requires some memory so a lot of users will
stretch the 1GB limit. If the load from each user is fairly light, you
might want to close the connection after each insert. Otherwise, I doubt
that you will get adequate performance from Express.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Joe from WI" <JoefromWI@.discussions.microsoft.com> wrote in message
news:0FC7ADE0-75A2-4D38-B826-A1BE395F31B5@.microsoft.com...
> SQL Server Express supports Windows 2000 SP4, Windows XP SP1, and Windows
> 2003. I'd recommend Windows Server 2003 - Standard Edition. If that
> doesn't
> fit your budget, Windows XP Pro could be used but it's not as robust.
> You can only have 1 physical CPU so I'd recommend the fastest you can
> afford. Keep in mind that Microsoft licenses by the physical CPU so you
> can
> purchase 1 dual core CPU and still be compliant with licensing. A
> dual-core
> runs a little slower but I believe it will give overall better performance
> when other services are competing for the CPU.
> Top-end AMD's include Athlon 64 X2 4800+, Opteron 280 (dual core), and
> Athlon 64 4000+.
> Top-end Intel's include Pentium D 840 (dual core), Pentium 4 670, and
> Pentium 4 Extreme Edition, and XEON EM64T.
> CPU choice is heavily dependent on what motherboard you have/will have.
> My
> personal favorite is the AMD Athlon 64 X2 4800+.
> If the computer is only used for SQL Server and there are no extensive
> query's, etc., then I'd recommend 2 GB of RAM. If you will be querying
> those
> 3 million rows, aggregates (SUM, AVG, etc.), processing data, etc., I'd
> recommend 4 GB of RAM. If you can only afford 1 or 2 GB, buy 1 GB sticks
> (assuming the motherboard will support them) and hopefully, you'll have
> open
> slots to add more memory later.
> The speed of the memory is determined by what the motherboard will
> support.
> If it's AMD, you'll be looking at DDR400 memory. If it's Intel, DDR400,
> perhaps DDR2, and perhaps higher clock speeds. Intel often sounds faster
> but
> Intel CPU's use an external memory controller. AMD's have the memory
> controller integrated into the CPU for very fast memory access.
> Hard drives are dependent on budget. Fastest is Ultra320 SCSI 15,000 RPM
> drives. In the SATA world, Western Digital Raptor 74GB SATA 10,000 RPM
> are
> the fastest, primarily due to the access time of 4.5ms. (Other SATA
> drives
> like ATA/IDE run at 7200 RPM and access data around 8.5 to 9ms.) If you
> cannot afford SCSI, I'd highly recommend the WD Raptors.
> An PCI/PCI-e RAID controller that has the RAID processor built into it is
> the fastest solution. Controllers integrated into the motherboard utilize
> the CPU for processing and are a bottleneck. One solution is to use the
> motherboard HDD controller for the O/S drives in a RAID-1 (mirroring)
> array
> as the O/S drive doesn't get heavy pounding. Then, use a separate
> controller
> for the database drives.
> If you don't want to loose data, you'll need to protect, at a minumum, the
> database log file (a.k.a. the transaction log) with RAID-1 (mirroring) or
> RAID-1+0. Do NOT use RAID-5. In my opinion, too much overhead to
> calculate
> parity. The log file is written to serially so if you can dedicate a
> separate pair of drives, there will be minimum hard drive head movement
> and
> will give you the best throughput.
> If you can't afford for the box to be down, you'll need RAID-1 or RAID-1+0
> for the O/S and for the database. Do NOT use RAID-5. In my opinion, too
> much overhead to calculate and re-calculate (when data changes) parity
> information.
> Sorry for the long post. Hope that helps,
> Joe
> "Sandy" wrote:
>|||Thank you very much, Joe!
--
Sandy
"Joe from WI" wrote:
> SQL Server Express supports Windows 2000 SP4, Windows XP SP1, and Windows
> 2003. I'd recommend Windows Server 2003 - Standard Edition. If that does
n't
> fit your budget, Windows XP Pro could be used but it's not as robust.
> You can only have 1 physical CPU so I'd recommend the fastest you can
> afford. Keep in mind that Microsoft licenses by the physical CPU so you c
an
> purchase 1 dual core CPU and still be compliant with licensing. A dual-co
re
> runs a little slower but I believe it will give overall better performance
> when other services are competing for the CPU.
> Top-end AMD's include Athlon 64 X2 4800+, Opteron 280 (dual core), and
> Athlon 64 4000+.
> Top-end Intel's include Pentium D 840 (dual core), Pentium 4 670, and
> Pentium 4 Extreme Edition, and XEON EM64T.
> CPU choice is heavily dependent on what motherboard you have/will have. M
y
> personal favorite is the AMD Athlon 64 X2 4800+.
> If the computer is only used for SQL Server and there are no extensive
> query's, etc., then I'd recommend 2 GB of RAM. If you will be querying th
ose
> 3 million rows, aggregates (SUM, AVG, etc.), processing data, etc., I'd
> recommend 4 GB of RAM. If you can only afford 1 or 2 GB, buy 1 GB sticks
> (assuming the motherboard will support them) and hopefully, you'll have op
en
> slots to add more memory later.
> The speed of the memory is determined by what the motherboard will support
.
> If it's AMD, you'll be looking at DDR400 memory. If it's Intel, DDR400,
> perhaps DDR2, and perhaps higher clock speeds. Intel often sounds faster
but
> Intel CPU's use an external memory controller. AMD's have the memory
> controller integrated into the CPU for very fast memory access.
> Hard drives are dependent on budget. Fastest is Ultra320 SCSI 15,000 RPM
> drives. In the SATA world, Western Digital Raptor 74GB SATA 10,000 RPM ar
e
> the fastest, primarily due to the access time of 4.5ms. (Other SATA drive
s
> like ATA/IDE run at 7200 RPM and access data around 8.5 to 9ms.) If you
> cannot afford SCSI, I'd highly recommend the WD Raptors.
> An PCI/PCI-e RAID controller that has the RAID processor built into it is
> the fastest solution. Controllers integrated into the motherboard utilize
> the CPU for processing and are a bottleneck. One solution is to use the
> motherboard HDD controller for the O/S drives in a RAID-1 (mirroring) arra
y
> as the O/S drive doesn't get heavy pounding. Then, use a separate control
ler
> for the database drives.
> If you don't want to loose data, you'll need to protect, at a minumum, the
> database log file (a.k.a. the transaction log) with RAID-1 (mirroring) or
> RAID-1+0. Do NOT use RAID-5. In my opinion, too much overhead to calcula
te
> parity. The log file is written to serially so if you can dedicate a
> separate pair of drives, there will be minimum hard drive head movement an
d
> will give you the best throughput.
> If you can't afford for the box to be down, you'll need RAID-1 or RAID-1+0
> for the O/S and for the database. Do NOT use RAID-5. In my opinion, too
> much overhead to calculate and re-calculate (when data changes) parity
> information.
> Sorry for the long post. Hope that helps,
> Joe
> "Sandy" wrote:
>|||Thanks Roger!
--
Sandy
"Roger Wolter[MSFT]" wrote:
> Note that Express is 32 bit only so a 64 bit processor isn't of much use t
o
> an Express installation.
> I think you biggest issue with that many simultaneous users will be the 1G
B
> limit on memory. Each user requires some memory so a lot of users will
> stretch the 1GB limit. If the load from each user is fairly light, you
> might want to close the connection after each insert. Otherwise, I doubt
> that you will get adequate performance from Express.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Joe from WI" <JoefromWI@.discussions.microsoft.com> wrote in message
> news:0FC7ADE0-75A2-4D38-B826-A1BE395F31B5@.microsoft.com...
>
>|||oops...I got carried away in my memory statement.
I recommended 2 GB of RAM because 1 GB can be utilized by SQL Server 2005
Express and the other 1 GB can be used by Windows, free space, etc. With
Express, there is no need to have more memory than 2 GB. (Unless of course,
you have other memory hungry applications on the same computer.)
The 4 GB of RAM recommendation would require Workgroup or Standard in order
to make use of it.
Sorry for the error, confusion, misstatement.
Joe
"Sandy" wrote:
> Thank you very much, Joe!
> --
> Sandy
>
> "Joe from WI" wrote:
>|||Also, Express is limited by 1 scheduler. A dual-core won't help you on the
SQL Server side of things.
"Joe from WI" wrote:
> oops...I got carried away in my memory statement.
> I recommended 2 GB of RAM because 1 GB can be utilized by SQL Server 2005
> Express and the other 1 GB can be used by Windows, free space, etc. With
> Express, there is no need to have more memory than 2 GB. (Unless of cours
e,
> you have other memory hungry applications on the same computer.)
> The 4 GB of RAM recommendation would require Workgroup or Standard in orde
r
> to make use of it.
> Sorry for the error, confusion, misstatement.
> Joe
> "Sandy" wrote:
>
Sunday, February 19, 2012
Exporting to Excel error
I am having a problem exporting a report to Excel. The report has about 1000
records. I can export it to a pdf fine, but not Excel. I get an "Index was
outside the bounds of the array" error message.
thank you for any help possible.Are you still having this issue or it's solved? Can you tell me what you did
to fix the problem? Because I am having the same problem too. Thanks.
"PatNUFC" wrote:
> I am having a problem exporting a report to Excel. The report has about 1000
> records. I can export it to a pdf fine, but not Excel. I get an "Index was
> outside the bounds of the array" error message.
> thank you for any help possible.
records. I can export it to a pdf fine, but not Excel. I get an "Index was
outside the bounds of the array" error message.
thank you for any help possible.Are you still having this issue or it's solved? Can you tell me what you did
to fix the problem? Because I am having the same problem too. Thanks.
"PatNUFC" wrote:
> I am having a problem exporting a report to Excel. The report has about 1000
> records. I can export it to a pdf fine, but not Excel. I get an "Index was
> outside the bounds of the array" error message.
> thank you for any help possible.
Wednesday, February 15, 2012
Exporting tables along with their primary and forgen keys and records
Hi all,
Im trying to export 120 tables from SQL server 2000 to SQL server 2005 with their Primary and corresponding records.
Is there way to do this?
Thanks for any help.
Abrahimwhy not just restore from a backup? you can restore a 2000 backup to a 2005 server.|||if you just want the script enterprise manager has a very easy method of exporting...
go to your database, the tables of which u want to export, then go to tables in the table list section select the tables you want to export then right click -> All Taks -> Generate SQL Script
there in the formatting and options tab you can select all the required options like primary, foreign, triggers.indexes...just about everything
try this out and let me know is this what u want...
or as said by jezemine... directly restore the backup|||Hi all,
In Enterprise manager Import\Export Under Column Mappings Tab I see:
Create destination table.
Delete rows in destination table.
Append rows to destination table and enable identity insert.
I tried all of the above options, and none does what I wanted.
About restoring the database.
There are couples of issues that we prefer not to use the restore function.
Regards,
Abrahim|||If you want to generate scripts, here's a little console app I wrote to script out all objects and data in any 2000 or 2005 database. It's useful if you want to automate the scripting process, or if you hate GUIs.
http://www.codeplex.com/scriptdb
hope it's useful to you.|||So,
Is that mean Enterprise manager Import\Export is not capable of doing what I was trying to do?
Thanks in advance,
Abrahim|||7338
7339
You never checked the right options.... kindly check the attachments i have added along with this thread and you will have a better idea....
also if you perform the following option you will still reach here
Right Click on Database name -> All Tasks -> Generate SQL Script
7340
Alternatively a longer programmatic procedure
http://www.databasejournal.com/features/mssql/article.php/2205291
for importing data present in these tables you can use the import/export wizard
Im trying to export 120 tables from SQL server 2000 to SQL server 2005 with their Primary and corresponding records.
Is there way to do this?
Thanks for any help.
Abrahimwhy not just restore from a backup? you can restore a 2000 backup to a 2005 server.|||if you just want the script enterprise manager has a very easy method of exporting...
go to your database, the tables of which u want to export, then go to tables in the table list section select the tables you want to export then right click -> All Taks -> Generate SQL Script
there in the formatting and options tab you can select all the required options like primary, foreign, triggers.indexes...just about everything
try this out and let me know is this what u want...
or as said by jezemine... directly restore the backup|||Hi all,
In Enterprise manager Import\Export Under Column Mappings Tab I see:
Create destination table.
Delete rows in destination table.
Append rows to destination table and enable identity insert.
I tried all of the above options, and none does what I wanted.
About restoring the database.
There are couples of issues that we prefer not to use the restore function.
Regards,
Abrahim|||If you want to generate scripts, here's a little console app I wrote to script out all objects and data in any 2000 or 2005 database. It's useful if you want to automate the scripting process, or if you hate GUIs.
http://www.codeplex.com/scriptdb
hope it's useful to you.|||So,
Is that mean Enterprise manager Import\Export is not capable of doing what I was trying to do?
Thanks in advance,
Abrahim|||7338
7339
You never checked the right options.... kindly check the attachments i have added along with this thread and you will have a better idea....
also if you perform the following option you will still reach here
Right Click on Database name -> All Tasks -> Generate SQL Script
7340
Alternatively a longer programmatic procedure
http://www.databasejournal.com/features/mssql/article.php/2205291
for importing data present in these tables you can use the import/export wizard
Subscribe to:
Posts (Atom)