RAID-Best Practices ?

I am moving from an Oracle Db server to a SQL server running on a new w2k3
member server. Prior to purchase I would like to select the appropriate RAID
level for the server which gives me the best performance and recovery options.
I heard something about RAID 10?
Appreciate it!
RPM
Here are the improvements in order of importance. Go as far down the list
as you have budget for.
1) Tlogs and Data on separate physical devices. If necessary, OS and TLogs
can share the same physical disks without too much of an impact IF it is a
dedicated and properly tuned SQL server.
2) Tlogs on RAID 1 or 1+0. These are sequentially written files and are
critical to transactional performance. Slow writes to the TLOGS are death
to a SQL server. Data can survive on RAID5.
3) Data and TLogs on different controllers. Much better for recovery in
case of hardware failure.
4) Data on RAID 1+0. Much faster than RAID5. About a five times faster for
transactional updates, depending on the number of drives in the physical
array. RAID 5 slows down on writes with more spindles. RAID 1+0 speeds up.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ron P" <RonP@.discussions.microsoft.com> wrote in message
news:3ED1EBCB-0A73-4BAF-9AC4-5AD52D59DC62@.microsoft.com...
> I am moving from an Oracle Db server to a SQL server running on a new w2k3
> member server. Prior to purchase I would like to select the appropriate
RAID
> level for the server which gives me the best performance and recovery
options.
> I heard something about RAID 10?
> Appreciate it!
> RPM
|||In addition to Geoff's excellent advice, separate filegroups for data and
nonclustered indexes can help performance if they're on separate
drives/controllers. All this depends on how many drives/controllers you
have. I would place this tip between 3 and 4 on Geoff's list below.
Thanks,
Michael C#, MCDBA
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:eiQ3kZyCFHA.3812@.TK2MSFTNGP15.phx.gbl...
> Here are the improvements in order of importance. Go as far down the list
> as you have budget for.
> 1) Tlogs and Data on separate physical devices. If necessary, OS and
> TLogs
> can share the same physical disks without too much of an impact IF it is a
> dedicated and properly tuned SQL server.
> 2) Tlogs on RAID 1 or 1+0. These are sequentially written files and are
> critical to transactional performance. Slow writes to the TLOGS are death
> to a SQL server. Data can survive on RAID5.
> 3) Data and TLogs on different controllers. Much better for recovery in
> case of hardware failure.
> 4) Data on RAID 1+0. Much faster than RAID5. About a five times faster
> for
> transactional updates, depending on the number of drives in the physical
> array. RAID 5 slows down on writes with more spindles. RAID 1+0 speeds
> up.
>
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Ron P" <RonP@.discussions.microsoft.com> wrote in message
> news:3ED1EBCB-0A73-4BAF-9AC4-5AD52D59DC62@.microsoft.com...
> RAID
> options.
>
|||First, you should evaluate your needs in term of capacity, performance and
reliability before making the choice for the RAID. Simply saying that you
want the *best* worths nothing in an evaluation.
S. L.
"Ron P" <RonP@.discussions.microsoft.com> wrote in message
news:3ED1EBCB-0A73-4BAF-9AC4-5AD52D59DC62@.microsoft.com...
>I am moving from an Oracle Db server to a SQL server running on a new w2k3
> member server. Prior to purchase I would like to select the appropriate
> RAID
> level for the server which gives me the best performance and recovery
> options.
> I heard something about RAID 10?
> Appreciate it!
> RPM
|||Thank you all for the input.
To clarify, If I have 20-25 users hitting this dedicated SQL server (DELL
2850 2.0 GB RAM, Dual Processors) which has the OS on a Raid 1 setup, would I
be better off with RAID 1+0 or Raid 1 for the data partition. Should I put
the TLogs on the OS or on the data partition?
TX again
Ron P
"Ron P" wrote:

> I am moving from an Oracle Db server to a SQL server running on a new w2k3
> member server. Prior to purchase I would like to select the appropriate RAID
> level for the server which gives me the best performance and recovery options.
> I heard something about RAID 10?
> Appreciate it!
> RPM
|||"Ron P" <RonP@.discussions.microsoft.com> wrote in message
news:F8A7A612-C982-4912-B04A-BB0B63E5160E@.microsoft.com...
> Thank you all for the input.
> To clarify, If I have 20-25 users hitting this dedicated SQL server (DELL
> 2850 2.0 GB RAM, Dual Processors) which has the OS on a Raid 1 setup,
would I
> be better off with RAID 1+0 or Raid 1 for the data partition. Should I put
> the TLogs on the OS or on the data partition?
I'd probably put the OS and data on the same partition and the Tlogs on a
separete physical partition.
But my first question would be, "do you really need to?". Is Disk I/O your
biggest bottleneck here?
[vbcol=seagreen]
> TX again
> Ron P
> "Ron P" wrote:
w2k3[vbcol=seagreen]
RAID[vbcol=seagreen]
options.[vbcol=seagreen]
|||Ron,
I think you need to study up a bit more.
RAID 1+0 is definately the highest performance RAID leve, but it is also the
most expensive.
a huge percentage of production systems use RAID 5. RAID 5 is not the best
for performance, but it provides fault tolerance and is relatively
inexpensive.
It sounds to me like placing OS and Logs on a RAID 1 Volume and the Data and
Indexes on RAID 5 might be the ticket.
Another important issue with RAID is, the MORE physical disks you have the
better. In other words, if you need 140GB of storage, you are better off
purchasing 4 36GB Drives as opposed to using 2 72GB Drives.
RAID Controller is also important. You need a Controller with Battery Backed
Cache for performance and for Fault Tolerance.
There are TONS of articles on RAID on the net. Just "Google" it up.
if you have more questions, you can email me directly
Greg Jackson
Portland, Oregon
|||My standard build recommendation on a 2850 is Mirrored drives (RAID 1) on
one channel for OS and TLOGs. Definitely 15KRPM, most likely 73GB. The
other four drive slots can be used for RAID 1+ 0 data on the second channel.
Depending on your actual data space requirements, you can use either 73GB
15KRPM drives or 146GB 10KRPM drives. Remember to order the split backplane
module to take advantage of the extra controller channel.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ron P" <RonP@.discussions.microsoft.com> wrote in message
news:F8A7A612-C982-4912-B04A-BB0B63E5160E@.microsoft.com...
> Thank you all for the input.
> To clarify, If I have 20-25 users hitting this dedicated SQL server (DELL
> 2850 2.0 GB RAM, Dual Processors) which has the OS on a Raid 1 setup,
would I[vbcol=seagreen]
> be better off with RAID 1+0 or Raid 1 for the data partition. Should I put
> the TLogs on the OS or on the data partition?
> TX again
> Ron P
> "Ron P" wrote:
w2k3[vbcol=seagreen]
RAID[vbcol=seagreen]
options.[vbcol=seagreen]
|||The choice between RAID 1 and RAID 5 is highly dependent on the ratio of
reads vs. writes. If you perform a large proportion of writes, RAID 5 will
cause a performance hit. As you mentioned, RAID 5 is not good for
Transaction Logs, regardless of how you decide to store data and indexes.
Also, the OP mentions separate partitions - how many physical drives do you
have Ron? As Greg pointed out, Physical Drives and separate controllers are
important performance factors. Separate partitions on the same hard drive
probably won't help, and might hinder, performance.
Thanks,
Michael C#, MCDBA
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:OVe0OSHDFHA.3596@.TK2MSFTNGP12.phx.gbl...
> Ron,
> I think you need to study up a bit more.
> RAID 1+0 is definately the highest performance RAID leve, but it is also
> the most expensive.
> a huge percentage of production systems use RAID 5. RAID 5 is not the best
> for performance, but it provides fault tolerance and is relatively
> inexpensive.
> It sounds to me like placing OS and Logs on a RAID 1 Volume and the Data
> and Indexes on RAID 5 might be the ticket.
> Another important issue with RAID is, the MORE physical disks you have the
> better. In other words, if you need 140GB of storage, you are better off
> purchasing 4 36GB Drives as opposed to using 2 72GB Drives.
> RAID Controller is also important. You need a Controller with Battery
> Backed Cache for performance and for Fault Tolerance.
> There are TONS of articles on RAID on the net. Just "Google" it up.
> if you have more questions, you can email me directly
>
> Greg Jackson
> Portland, Oregon
>
|||If Windows and TLogs are on RAID1 and DB's are on RAID5, where is the best
place for SQL backup files?

RAID-Best Practices ?

I am moving from an Oracle Db server to a SQL server running on a new w2k3
member server. Prior to purchase I would like to select the appropriate RAID
level for the server which gives me the best performance and recovery option
s.
I heard something about RAID 10?
Appreciate it!
RPMHere are the improvements in order of importance. Go as far down the list
as you have budget for.
1) Tlogs and Data on separate physical devices. If necessary, OS and TLogs
can share the same physical disks without too much of an impact IF it is a
dedicated and properly tuned SQL server.
2) Tlogs on RAID 1 or 1+0. These are sequentially written files and are
critical to transactional performance. Slow writes to the TLOGS are death
to a SQL server. Data can survive on RAID5.
3) Data and TLogs on different controllers. Much better for recovery in
case of hardware failure.
4) Data on RAID 1+0. Much faster than RAID5. About a five times faster for
transactional updates, depending on the number of drives in the physical
array. RAID 5 slows down on writes with more spindles. RAID 1+0 speeds up.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ron P" <RonP@.discussions.microsoft.com> wrote in message
news:3ED1EBCB-0A73-4BAF-9AC4-5AD52D59DC62@.microsoft.com...
> I am moving from an Oracle Db server to a SQL server running on a new w2k3
> member server. Prior to purchase I would like to select the appropriate
RAID
> level for the server which gives me the best performance and recovery
options.
> I heard something about RAID 10?
> Appreciate it!
> RPM|||In addition to Geoff's excellent advice, separate filegroups for data and
nonclustered indexes can help performance if they're on separate
drives/controllers. All this depends on how many drives/controllers you
have. I would place this tip between 3 and 4 on Geoff's list below.
Thanks,
Michael C#, MCDBA
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:eiQ3kZyCFHA.3812@.TK2MSFTNGP15.phx.gbl...
> Here are the improvements in order of importance. Go as far down the list
> as you have budget for.
> 1) Tlogs and Data on separate physical devices. If necessary, OS and
> TLogs
> can share the same physical disks without too much of an impact IF it is a
> dedicated and properly tuned SQL server.
> 2) Tlogs on RAID 1 or 1+0. These are sequentially written files and are
> critical to transactional performance. Slow writes to the TLOGS are death
> to a SQL server. Data can survive on RAID5.
> 3) Data and TLogs on different controllers. Much better for recovery in
> case of hardware failure.
> 4) Data on RAID 1+0. Much faster than RAID5. About a five times faster
> for
> transactional updates, depending on the number of drives in the physical
> array. RAID 5 slows down on writes with more spindles. RAID 1+0 speeds
> up.
>
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Ron P" <RonP@.discussions.microsoft.com> wrote in message
> news:3ED1EBCB-0A73-4BAF-9AC4-5AD52D59DC62@.microsoft.com...
> RAID
> options.
>|||First, you should evaluate your needs in term of capacity, performance and
reliability before making the choice for the RAID. Simply saying that you
want the *best* worths nothing in an evaluation.
S. L.
"Ron P" <RonP@.discussions.microsoft.com> wrote in message
news:3ED1EBCB-0A73-4BAF-9AC4-5AD52D59DC62@.microsoft.com...
>I am moving from an Oracle Db server to a SQL server running on a new w2k3
> member server. Prior to purchase I would like to select the appropriate
> RAID
> level for the server which gives me the best performance and recovery
> options.
> I heard something about RAID 10?
> Appreciate it!
> RPM|||Thank you all for the input.
To clarify, If I have 20-25 users hitting this dedicated SQL server (DELL
2850 2.0 GB RAM, Dual Processors) which has the OS on a Raid 1 setup, would
I
be better off with RAID 1+0 or Raid 1 for the data partition. Should I put
the TLogs on the OS or on the data partition?
TX again
Ron P
"Ron P" wrote:

> I am moving from an Oracle Db server to a SQL server running on a new w2k3
> member server. Prior to purchase I would like to select the appropriate RA
ID
> level for the server which gives me the best performance and recovery opti
ons.
> I heard something about RAID 10?
> Appreciate it!
> RPM|||"Ron P" <RonP@.discussions.microsoft.com> wrote in message
news:F8A7A612-C982-4912-B04A-BB0B63E5160E@.microsoft.com...
> Thank you all for the input.
> To clarify, If I have 20-25 users hitting this dedicated SQL server (DELL
> 2850 2.0 GB RAM, Dual Processors) which has the OS on a Raid 1 setup,
would I
> be better off with RAID 1+0 or Raid 1 for the data partition. Should I put
> the TLogs on the OS or on the data partition?
I'd probably put the OS and data on the same partition and the Tlogs on a
separete physical partition.
But my first question would be, "do you really need to?". Is Disk I/O your
biggest bottleneck here?
[vbcol=seagreen]
> TX again
> Ron P
> "Ron P" wrote:
>
w2k3[vbcol=seagreen]
RAID[vbcol=seagreen]
options.[vbcol=seagreen]|||Ron,
I think you need to study up a bit more.
RAID 1+0 is definately the highest performance RAID leve, but it is also the
most expensive.
a huge percentage of production systems use RAID 5. RAID 5 is not the best
for performance, but it provides fault tolerance and is relatively
inexpensive.
It sounds to me like placing OS and Logs on a RAID 1 Volume and the Data and
Indexes on RAID 5 might be the ticket.
Another important issue with RAID is, the MORE physical disks you have the
better. In other words, if you need 140GB of storage, you are better off
purchasing 4 36GB Drives as opposed to using 2 72GB Drives.
RAID Controller is also important. You need a Controller with Battery Backed
Cache for performance and for Fault Tolerance.
There are TONS of articles on RAID on the net. Just "Google" it up.
if you have more questions, you can email me directly
Greg Jackson
Portland, Oregon|||My standard build recommendation on a 2850 is Mirrored drives (RAID 1) on
one channel for OS and TLOGs. Definitely 15KRPM, most likely 73GB. The
other four drive slots can be used for RAID 1+ 0 data on the second channel.
Depending on your actual data space requirements, you can use either 73GB
15KRPM drives or 146GB 10KRPM drives. Remember to order the split backplane
module to take advantage of the extra controller channel.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ron P" <RonP@.discussions.microsoft.com> wrote in message
news:F8A7A612-C982-4912-B04A-BB0B63E5160E@.microsoft.com...
> Thank you all for the input.
> To clarify, If I have 20-25 users hitting this dedicated SQL server (DELL
> 2850 2.0 GB RAM, Dual Processors) which has the OS on a Raid 1 setup,
would I[vbcol=seagreen]
> be better off with RAID 1+0 or Raid 1 for the data partition. Should I put
> the TLogs on the OS or on the data partition?
> TX again
> Ron P
> "Ron P" wrote:
>
w2k3[vbcol=seagreen]
RAID[vbcol=seagreen]
options.[vbcol=seagreen]|||The choice between RAID 1 and RAID 5 is highly dependent on the ratio of
reads vs. writes. If you perform a large proportion of writes, RAID 5 will
cause a performance hit. As you mentioned, RAID 5 is not good for
Transaction Logs, regardless of how you decide to store data and indexes.
Also, the OP mentions separate partitions - how many physical drives do you
have Ron? As Greg pointed out, Physical Drives and separate controllers are
important performance factors. Separate partitions on the same hard drive
probably won't help, and might hinder, performance.
Thanks,
Michael C#, MCDBA
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:OVe0OSHDFHA.3596@.TK2MSFTNGP12.phx.gbl...
> Ron,
> I think you need to study up a bit more.
> RAID 1+0 is definately the highest performance RAID leve, but it is also
> the most expensive.
> a huge percentage of production systems use RAID 5. RAID 5 is not the best
> for performance, but it provides fault tolerance and is relatively
> inexpensive.
> It sounds to me like placing OS and Logs on a RAID 1 Volume and the Data
> and Indexes on RAID 5 might be the ticket.
> Another important issue with RAID is, the MORE physical disks you have the
> better. In other words, if you need 140GB of storage, you are better off
> purchasing 4 36GB Drives as opposed to using 2 72GB Drives.
> RAID Controller is also important. You need a Controller with Battery
> Backed Cache for performance and for Fault Tolerance.
> There are TONS of articles on RAID on the net. Just "Google" it up.
> if you have more questions, you can email me directly
>
> Greg Jackson
> Portland, Oregon
>

RAID-Best Practices ?

I am moving from an Oracle Db server to a SQL server running on a new w2k3
member server. Prior to purchase I would like to select the appropriate RAID
level for the server which gives me the best performance and recovery options.
I heard something about RAID 10?
Appreciate it!
RPMHere are the improvements in order of importance. Go as far down the list
as you have budget for.
1) Tlogs and Data on separate physical devices. If necessary, OS and TLogs
can share the same physical disks without too much of an impact IF it is a
dedicated and properly tuned SQL server.
2) Tlogs on RAID 1 or 1+0. These are sequentially written files and are
critical to transactional performance. Slow writes to the TLOGS are death
to a SQL server. Data can survive on RAID5.
3) Data and TLogs on different controllers. Much better for recovery in
case of hardware failure.
4) Data on RAID 1+0. Much faster than RAID5. About a five times faster for
transactional updates, depending on the number of drives in the physical
array. RAID 5 slows down on writes with more spindles. RAID 1+0 speeds up.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ron P" <RonP@.discussions.microsoft.com> wrote in message
news:3ED1EBCB-0A73-4BAF-9AC4-5AD52D59DC62@.microsoft.com...
> I am moving from an Oracle Db server to a SQL server running on a new w2k3
> member server. Prior to purchase I would like to select the appropriate
RAID
> level for the server which gives me the best performance and recovery
options.
> I heard something about RAID 10?
> Appreciate it!
> RPM|||In addition to Geoff's excellent advice, separate filegroups for data and
nonclustered indexes can help performance if they're on separate
drives/controllers. All this depends on how many drives/controllers you
have. I would place this tip between 3 and 4 on Geoff's list below.
Thanks,
Michael C#, MCDBA
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:eiQ3kZyCFHA.3812@.TK2MSFTNGP15.phx.gbl...
> Here are the improvements in order of importance. Go as far down the list
> as you have budget for.
> 1) Tlogs and Data on separate physical devices. If necessary, OS and
> TLogs
> can share the same physical disks without too much of an impact IF it is a
> dedicated and properly tuned SQL server.
> 2) Tlogs on RAID 1 or 1+0. These are sequentially written files and are
> critical to transactional performance. Slow writes to the TLOGS are death
> to a SQL server. Data can survive on RAID5.
> 3) Data and TLogs on different controllers. Much better for recovery in
> case of hardware failure.
> 4) Data on RAID 1+0. Much faster than RAID5. About a five times faster
> for
> transactional updates, depending on the number of drives in the physical
> array. RAID 5 slows down on writes with more spindles. RAID 1+0 speeds
> up.
>
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Ron P" <RonP@.discussions.microsoft.com> wrote in message
> news:3ED1EBCB-0A73-4BAF-9AC4-5AD52D59DC62@.microsoft.com...
>> I am moving from an Oracle Db server to a SQL server running on a new
>> w2k3
>> member server. Prior to purchase I would like to select the appropriate
> RAID
>> level for the server which gives me the best performance and recovery
> options.
>> I heard something about RAID 10?
>> Appreciate it!
>> RPM
>|||First, you should evaluate your needs in term of capacity, performance and
reliability before making the choice for the RAID. Simply saying that you
want the *best* worths nothing in an evaluation.
S. L.
"Ron P" <RonP@.discussions.microsoft.com> wrote in message
news:3ED1EBCB-0A73-4BAF-9AC4-5AD52D59DC62@.microsoft.com...
>I am moving from an Oracle Db server to a SQL server running on a new w2k3
> member server. Prior to purchase I would like to select the appropriate
> RAID
> level for the server which gives me the best performance and recovery
> options.
> I heard something about RAID 10?
> Appreciate it!
> RPM|||Thank you all for the input.
To clarify, If I have 20-25 users hitting this dedicated SQL server (DELL
2850 2.0 GB RAM, Dual Processors) which has the OS on a Raid 1 setup, would I
be better off with RAID 1+0 or Raid 1 for the data partition. Should I put
the TLogs on the OS or on the data partition?
TX again
Ron P
"Ron P" wrote:
> I am moving from an Oracle Db server to a SQL server running on a new w2k3
> member server. Prior to purchase I would like to select the appropriate RAID
> level for the server which gives me the best performance and recovery options.
> I heard something about RAID 10?
> Appreciate it!
> RPM|||"Ron P" <RonP@.discussions.microsoft.com> wrote in message
news:F8A7A612-C982-4912-B04A-BB0B63E5160E@.microsoft.com...
> Thank you all for the input.
> To clarify, If I have 20-25 users hitting this dedicated SQL server (DELL
> 2850 2.0 GB RAM, Dual Processors) which has the OS on a Raid 1 setup,
would I
> be better off with RAID 1+0 or Raid 1 for the data partition. Should I put
> the TLogs on the OS or on the data partition?
I'd probably put the OS and data on the same partition and the Tlogs on a
separete physical partition.
But my first question would be, "do you really need to?". Is Disk I/O your
biggest bottleneck here?
> TX again
> Ron P
> "Ron P" wrote:
> > I am moving from an Oracle Db server to a SQL server running on a new
w2k3
> > member server. Prior to purchase I would like to select the appropriate
RAID
> > level for the server which gives me the best performance and recovery
options.
> >
> > I heard something about RAID 10?
> > Appreciate it!
> >
> > RPM|||Ron,
I think you need to study up a bit more.
RAID 1+0 is definately the highest performance RAID leve, but it is also the
most expensive.
a huge percentage of production systems use RAID 5. RAID 5 is not the best
for performance, but it provides fault tolerance and is relatively
inexpensive.
It sounds to me like placing OS and Logs on a RAID 1 Volume and the Data and
Indexes on RAID 5 might be the ticket.
Another important issue with RAID is, the MORE physical disks you have the
better. In other words, if you need 140GB of storage, you are better off
purchasing 4 36GB Drives as opposed to using 2 72GB Drives.
RAID Controller is also important. You need a Controller with Battery Backed
Cache for performance and for Fault Tolerance.
There are TONS of articles on RAID on the net. Just "Google" it up.
if you have more questions, you can email me directly
Greg Jackson
Portland, Oregon|||My standard build recommendation on a 2850 is Mirrored drives (RAID 1) on
one channel for OS and TLOGs. Definitely 15KRPM, most likely 73GB. The
other four drive slots can be used for RAID 1+ 0 data on the second channel.
Depending on your actual data space requirements, you can use either 73GB
15KRPM drives or 146GB 10KRPM drives. Remember to order the split backplane
module to take advantage of the extra controller channel.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ron P" <RonP@.discussions.microsoft.com> wrote in message
news:F8A7A612-C982-4912-B04A-BB0B63E5160E@.microsoft.com...
> Thank you all for the input.
> To clarify, If I have 20-25 users hitting this dedicated SQL server (DELL
> 2850 2.0 GB RAM, Dual Processors) which has the OS on a Raid 1 setup,
would I
> be better off with RAID 1+0 or Raid 1 for the data partition. Should I put
> the TLogs on the OS or on the data partition?
> TX again
> Ron P
> "Ron P" wrote:
> > I am moving from an Oracle Db server to a SQL server running on a new
w2k3
> > member server. Prior to purchase I would like to select the appropriate
RAID
> > level for the server which gives me the best performance and recovery
options.
> >
> > I heard something about RAID 10?
> > Appreciate it!
> >
> > RPM|||The choice between RAID 1 and RAID 5 is highly dependent on the ratio of
reads vs. writes. If you perform a large proportion of writes, RAID 5 will
cause a performance hit. As you mentioned, RAID 5 is not good for
Transaction Logs, regardless of how you decide to store data and indexes.
Also, the OP mentions separate partitions - how many physical drives do you
have Ron? As Greg pointed out, Physical Drives and separate controllers are
important performance factors. Separate partitions on the same hard drive
probably won't help, and might hinder, performance.
Thanks,
Michael C#, MCDBA
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:OVe0OSHDFHA.3596@.TK2MSFTNGP12.phx.gbl...
> Ron,
> I think you need to study up a bit more.
> RAID 1+0 is definately the highest performance RAID leve, but it is also
> the most expensive.
> a huge percentage of production systems use RAID 5. RAID 5 is not the best
> for performance, but it provides fault tolerance and is relatively
> inexpensive.
> It sounds to me like placing OS and Logs on a RAID 1 Volume and the Data
> and Indexes on RAID 5 might be the ticket.
> Another important issue with RAID is, the MORE physical disks you have the
> better. In other words, if you need 140GB of storage, you are better off
> purchasing 4 36GB Drives as opposed to using 2 72GB Drives.
> RAID Controller is also important. You need a Controller with Battery
> Backed Cache for performance and for Fault Tolerance.
> There are TONS of articles on RAID on the net. Just "Google" it up.
> if you have more questions, you can email me directly
>
> Greg Jackson
> Portland, Oregon
>|||If Windows and TLogs are on RAID1 and DB's are on RAID5, where is the best
place for SQL backup files?|||Another server.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:F8350DA6-12A5-4930-B131-9CD2C217BBC4@.microsoft.com...
> If Windows and TLogs are on RAID1 and DB's are on RAID5, where is the best
> place for SQL backup files?|||I agree that would be my first choice. However in the senario where there is
no option but to have the SQL backups on the same server, where is the best
place for an successful recovery.|||USB drive so you can attach it to a recovery server.
Local backups aren't.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:F87D91FF-1B21-4646-83DB-4DE7BD2C3725@.microsoft.com...
>I agree that would be my first choice. However in the senario where there
>is
> no option but to have the SQL backups on the same server, where is the
> best
> place for an successful recovery.
>|||As Geoff suggests, you need to get the data off the server. However, if
your .mdf data files are on the RAID 5, and you need to first backup to the
local disks, backup to the other physical disks on your server. It sounds
like your only option is the RAID 1 partition. If two of your RAID 5 disks
die, you still have your latest backup on the RAID 1 partition.
Mark
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:F8350DA6-12A5-4930-B131-9CD2C217BBC4@.microsoft.com...
> If Windows and TLogs are on RAID1 and DB's are on RAID5, where is the best
> place for SQL backup files?

RagRe: How to select this ?

SELECT CAST(MONTH(DateCol) AS VARCHAR(2)) + '/' RIGHT(CAST(YEAR(DateCol) AS VARCHAR(2)),2)
SUM(CASE WHEN Work = 'Design' THEN 1 ELSE 0 END) as Design,
SUM(CASE WHEN Work = 'Programming' THEN 1 ELSE 0 END) as Programming
From SomeTable
GROUP BY
CAST(MONTH(DateCol) AS VARCHAR(2)) + '/' RIGHT(CAST(YEAR(DateCol) AS VARCHAR(2)),2)

The above query works fine . but when i try to change to below format :

SUM(CASE WHEN Work = 'Design' THEN 1 ELSE 0 END) as Design

in this query i how to insert this

SUM(CASE WHEN Work = 'Design' THEN (select distinct(act_point) from act_table) where act_id='1000' ELSE 0 END) as Design

when i use this i get the following error.

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

How to solve this problem ?

Raghu:

Is what you are trying to do to add in a value of "the sum of all distinct 'act_point' values" for each 'DESIGN' record?

act_id act_point
-
1001 15
1000 10
1000 10
1002 5
1000 12
1000 7

For this mock-up act_table data you would try to add 10+12+7=19 for each 'DESIGN' record?

Dave

Radio button - not reqire to select!

I am currently using a web form to insert data into SQL DB.
The radio button for Gender is not a require field.
currently if I use this command is in VB
' cmd.Parameters.Add(New SQLParameter("@.Gender", rblGender.SelectedItem.text)) >
it work fine if the user select one of the radio button, if the user don't select any button then it will give an error.
so I thought I can try the if else statement to see if it work, unfortunately it didn't either and error out on rblGender.SelectedItem.text
ex.
If rblGender.SelectedItem.text <> "" then
cmd.Parameters.Add(New SQLParameter("@.Gender", rblGender.SelectedItem.text))
Else
cmd.Parameters.Add(New SQLParameter("@.Gender", ""))
End IF
Any help would be appreciated.
hydro

You could do something like this:
If rblGender.SelectedIndex <> -1 then
cmd.Parameters.Add(New SQLParameter("@.Gender", rblGender.SelectedItem.text))
Else
cmd.Parameters.Add(New SQLParameter("@.Gender", DbNull.Value))
End IF
|||

That work!! Thanks!!

hydro

sql

Qyery returns different results on SQL server 7.0 and 2000.

SELECT CASE
WHEN ISNUMERIC(t.clnum)=1 THEN
(SELECT CASE
WHEN PropertyAddressOptionCode =
'F' THEN 1
WHEN PropertyAddressOptionCode =
'SZ' THEN 2
ELSE -10
END
FROM TableA (NOLOCK) WHERE Loannumber = cast(t.clnum as int))
ELSE -1
END AS 'PropertyAddressOptionType'
From TableB t where t.lnum='xyz'
The scenarios is that TableA does not having a matching value returned by
TableB.
In such scenario the ideally output should be null.
This is waht we get in 2000 but on sql 7.0 we get -10 as output.
Any ideas as why it that happens like that on 7.0.
I have a solution to handle this, but i'm interested in why that is happenin
g.
The above query returns -10 valueTry adding SET ANSI_NULLS ON to the start of your batch and re-run on both
servers.
It could be that the default setting of ANSI_NULLS differs between v7 and
2000
HTH. Ryan
"Manoj9" <Manoj9@.discussions.microsoft.com> wrote in message
news:24E5D921-3E10-4F8B-91F0-0B35EBDF53C3@.microsoft.com...
> SELECT CASE
> WHEN ISNUMERIC(t.clnum)=1 THEN
> (SELECT CASE
> WHEN PropertyAddressOptionCode =
> 'F' THEN 1
> WHEN PropertyAddressOptionCode =
> 'SZ' THEN 2
> ELSE -10
> END
> FROM TableA (NOLOCK) WHERE Loannumber = cast(t.clnum as int))
> ELSE -1
> END AS 'PropertyAddressOptionType'
> From TableB t where t.lnum='xyz'
> --
> The scenarios is that TableA does not having a matching value returned by
> TableB.
> In such scenario the ideally output should be null.
> This is waht we get in 2000 but on sql 7.0 we get -10 as output.
> Any ideas as why it that happens like that on 7.0.
> I have a solution to handle this, but i'm interested in why that is
> happening.
> The above query returns -10 value
>

QUOTENAME Problem

I have following four cases

Code Snippet

1) select QUOTENAME (QUOTENAME ('ABCD', ''''),'''' )
Output: '''ABCD'''

Code Snippet

2) select QUOTENAME (QUOTENAME ('ABCD', ''''),']' )
Output: ['ABCD']

Code Snippet

3) select QUOTENAME (QUOTENAME ('ABCD', ''''),'"' )
Output: "'ABCD'"

Code Snippet

4) select QUOTENAME (QUOTENAME ('ABCD', '"'),'"' )
Output: """ABCD"""

Now my questions is second & three outputs are fine, but what happen to the first & forth one. I want single quote twice around string(in first case) and double quote twice around string(in forth case) but it gives me three single/double quotes, WHY ?

Gurpreet S. Gill

There was a recent post here:

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

In which I noted this behavior in Louis Davidson's example. This behavior also appears in books online so it is intended and you will need to account for it in your coding.

|||

Sorry, but I can not see what your concern is. Let me try to explain 1 and 4.

1 - select QUOTENAME (QUOTENAME ('ABCD', ''''),'''' )

The first call to the function will produce 'ABCD', correct?. Ther second call has to wrap the string 'ABCD' between aposthopes (including the existing aposthropes), so in order to wrap an aposthrope between apostropes you have to double the inner apostrophe. The result of the second call will be '''ABCD''', where the inner apostrophes were double. Let us put some spaces to differentiate them.

' ''ABCD'' '

2 - select QUOTENAME (QUOTENAME ('ABCD', '"'),'"' )

The same as in number 1, but this time both wrap will be between the double qoute ("). The inner double quote should be double and wrap them between double quote.

" ""ABCD"" "

We need to scape the character inside the string and we do it doubling the character being used, in those cases apostrophe and double quote. Let us see what happend if we decide to do the same with ']'.

select quotename(quotename('ABCD', ']'), ']')

Result:

[[ABCD]]]

The behavior with ']' is different compare with apostrophe or double quote, just the most right is doubled.

Hope my english does not get you dizzy.

AMB

Quoted Identifier nonsense

I Have a query that does the following:
SELECT PAT.LastName + ", " + PAT.FirstName AS FirstName FROM X
I have Quoted Identifiers turned OFF and yet this query fails because it
says that ',' is not a valid column name.
Um, unless everything has changed, the expression in " " should be treated
as a constant with quoted identifiers off, right?
Any other ideas?Are you sure you have the setting OFF? Try the following in QA:
SET QUOTED_IDENTIFIER OFF
GO
SELECT au_fname + ", " + au_lname
FROM Pubs..authors;
--
- Anith
( Please reply to newsgroups only )|||Ok,
I did that and then it worked UNTIL I closed my Query Analyzer window and
then we are back to the same behavior. I am SURE the setting is off in the
DB properties otherwise...
I am getting concerned
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OUIiK4GtDHA.1224@.TK2MSFTNGP09.phx.gbl...
> Are you sure you have the setting OFF? Try the following in QA:
> SET QUOTED_IDENTIFIER OFF
> GO
> SELECT au_fname + ", " + au_lname
> FROM Pubs..authors;
> --
> - Anith
> ( Please reply to newsgroups only )
>|||It is simply a QA behaviour which you are seeing. QA sets SET
QUOTED_IDENTIFIER to ON by default and so, you have to explcitly set to OFF
to make your query work.
To behave the same way as in SQL 7.0, keep the setting to OFF. This has
little to do with the overall behaviour of the database.
--
- Anith
( Please reply to newsgroups only )|||Setting it as a database property doesn't work at all. Every time you start
a new QA session, ODBC sends its own SET commands to SQL Server which
override anything you have set at the db level. I wrote a column for SQL
Server Magazine pointing out that in most cases setting database options for
the ANSI behaviors is absolutely useless, because it always gets overridden
at the session level.
Even in YOU don't set it, the API sets it behind the scenes for you. You can
watch this happen in Profiler. :-)
For the Query Analyzer Tool, you can go to Tools|Options|Connection
Properties, and clear the box for setting Quoted Identifer. Then all new
connections in QA will have this OFF. But if you use other tools, they will
not be affected.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"A" <agarrettbNOSPAM@.hotmail.com> wrote in message
news:u8qBu9GtDHA.1680@.TK2MSFTNGP12.phx.gbl...
> Ok,
> I did that and then it worked UNTIL I closed my Query Analyzer window and
> then we are back to the same behavior. I am SURE the setting is off in
the
> DB properties otherwise...
> I am getting concerned
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:OUIiK4GtDHA.1224@.TK2MSFTNGP09.phx.gbl...
> > Are you sure you have the setting OFF? Try the following in QA:
> >
> > SET QUOTED_IDENTIFIER OFF
> > GO
> > SELECT au_fname + ", " + au_lname
> > FROM Pubs..authors;
> >
> > --
> > - Anith
> > ( Please reply to newsgroups only )
> >
> >
>|||In addition to the other posts:
Is there any particular reason why you want to use double quotes for string delimiters? This is
non-standard and SQL Server is moving away from this (as you have noticed).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"A" <agarrettbNOSPAM@.hotmail.com> wrote in message news:u8qBu9GtDHA.1680@.TK2MSFTNGP12.phx.gbl...
> Ok,
> I did that and then it worked UNTIL I closed my Query Analyzer window and
> then we are back to the same behavior. I am SURE the setting is off in the
> DB properties otherwise...
> I am getting concerned
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:OUIiK4GtDHA.1224@.TK2MSFTNGP09.phx.gbl...
> > Are you sure you have the setting OFF? Try the following in QA:
> >
> > SET QUOTED_IDENTIFIER OFF
> > GO
> > SELECT au_fname + ", " + au_lname
> > FROM Pubs..authors;
> >
> > --
> > - Anith
> > ( Please reply to newsgroups only )
> >
> >
>

Quick TSQL?

Hey all... I have a few tables that I am joining and need to know how to set a value from the return to a different column:

example:

SELECT

Prospect.ProspectName AS P1, AccountShipTo.ShipToName AS [Account Name], ProposalHeader.PropHCity AS City, ProposalHeader.PropHState AS State,

ProposalHeader.PropHNumb AS [Proposal ID], ProposalHeader.PropHRevNumb AS Rev, ProposalHeader.PropHCreateDate AS [Creation Date],

ProposalHeader.update_timestamp AS [Last Edit Date]

FROM ProposalHeader LEFT OUTER JOIN

AccountShipTo ON ProposalHeader.PropHShipTo = LTRIM(AccountShipTo.ShipToCust) LEFT OUTER JOIN

Prospect ON ProposalHeader.PropHBillTo = LTRIM(Prospect.ProspectNumb)

WHERE (ProposalHeader.Alias = N'billb')

GROUP BY ProposalHeader.PropHRevNumb, AccountShipTo.ShipToName, ProposalHeader.PropHCity, ProposalHeader.PropHState,

ProposalHeader.PropHNumb, ProposalHeader.PropHCreateDate, ProposalHeader.update_timestamp, Prospect.ProspectName

ORDER BY [Proposal ID]

I need P1 value (Test - Timberline Corp)to be in the Account Name column (NULL)... any ideas?

P1 Account Name City State Proposal ID Rev Creation Date Last Edit Date

NULL Samples, Inc. High Point NC Samples1 1 2007-07-25 2007-07-30

Test - Timberline Corp NULL Rapid City SD test1 1 2007-07-31 2007-07-31

(2 row(s) affected)

Any help would be appreciated... thanks!

Have you tried coalesce(AccountShipTo.ShipToName, Prospect.ProspectName) or isnull(AccountShipTo.ShipToName, Prospect.ProspectName)
|||

Very coo! Thanks...

Did this....

SELECT COALESCE (AccountShipTo.ShipToName, Prospect.ProspectName) AS [Account Name]

Quick SQL Select Statement ?

I am using SQL Server Express and ASP.

I have a table that contains news articles, headlines, start and end
dates. I am trying to create a recordset that shows all of the
articles that are greater than or equal to the start date and less
than or equal to the end date. For some reason I cannot get it to
function properly using a statement similar to StartDate >= Now() AND
EndDate <= Now(). SQL doesnt like the Now() piece of the statement.
Anyone have an idea how I can get this to work? I have also tried
CURDATE() but to no avail.

Please help.t8ntboy wrote:

Quote:

Originally Posted by

I am using SQL Server Express and ASP.
>
I have a table that contains news articles, headlines, start and end
dates. I am trying to create a recordset that shows all of the
articles that are greater than or equal to the start date and less
than or equal to the end date. For some reason I cannot get it to
function properly using a statement similar to StartDate >= Now() AND
EndDate <= Now(). SQL doesnt like the Now() piece of the statement.
Anyone have an idea how I can get this to work? I have also tried
CURDATE() but to no avail.

StartDate <= GetDate() and EndDate >= GetDate()|||On Mar 20, 4:33 pm, Ed Murphy <emurph...@.socal.rr.comwrote:

Quote:

Originally Posted by

t8ntboy wrote:

Quote:

Originally Posted by

I am using SQL Server Express and ASP.

>

Quote:

Originally Posted by

I have a table that contains news articles, headlines, start and end
dates. I am trying to create a recordset that shows all of the
articles that are greater than or equal to the start date and less
than or equal to the end date. For some reason I cannot get it to
function properly using a statement similar to StartDate >= Now() AND
EndDate <= Now(). SQL doesnt like the Now() piece of the statement.
Anyone have an idea how I can get this to work? I have also tried
CURDATE() but to no avail.

>
StartDate <= GetDate() and EndDate >= GetDate()

Ed:

Many thanks!!!|||t8ntboy,

You might want to use CURRENT_TIMESTAMP which is ANSI compliant and
equivalent to GETDATE(). There is no functional difference, its just easier
for someone coming from another DB platform to understand.

-- Bill

"t8ntboy" <t8ntboy@.gmail.comwrote in message
news:1174421409.303027.198580@.l75g2000hse.googlegr oups.com...

Quote:

Originally Posted by

>I am using SQL Server Express and ASP.
>
I have a table that contains news articles, headlines, start and end
dates. I am trying to create a recordset that shows all of the
articles that are greater than or equal to the start date and less
than or equal to the end date. For some reason I cannot get it to
function properly using a statement similar to StartDate >= Now() AND
EndDate <= Now(). SQL doesnt like the Now() piece of the statement.
Anyone have an idea how I can get this to work? I have also tried
CURDATE() but to no avail.
>
>
Please help.
>

Quick SQL question...

I'm trying to change every value of a certain column in a table by adding an
extra character to it:
UPDATE event_details
SET code_event = (SELECT code_event + '0' FROM event_details)
But I know I need some sort of join to do this but I'm not sure what. Can
any one advise please?
Cheers,
elziko> CREATE TABLE #Temp
I need to do this without using CREATE TABLE
Any ideas?
Cheers,
elziko|||elziko
I was creating table for repro your question because you did not post DDL.
Just use UPDATE Tablename.............
"elziko" <elziko@.NOTSPAMMINGyahoo.co.uk> wrote in message
news:#BHQ$tt#DHA.2184@.TK2MSFTNGP12.phx.gbl...
> I need to do this without using CREATE TABLE
> Any ideas?
> --
> Cheers,
> elziko
>|||Declare @.NewCharacter VarChar (20)
Declare @.OldCharcter VarChar (20)
Declare @.AddCharacter VarChar (20)
Set @.AddCharacter = '0'
Select @.OldCharcter = code_event FROM event_details
Select @.NewCharacter = @.OldCharcter + @.AddCharacter
Print @.NewCharacter
Update event_details set code_event = @.NewCharacter
"elziko" <elziko@.NOTSPAMMINGyahoo.co.uk> wrote in message
news:eQLPHRt%23DHA.2808@.TK2MSFTNGP10.phx.gbl...
> I'm trying to change every value of a certain column in a table by adding
an
> extra character to it:
> UPDATE event_details
> SET code_event = (SELECT code_event + '0' FROM event_details)
> But I know I need some sort of join to do this but I'm not sure what. Can
> any one advise please?
> --
> Cheers,
> elziko
>|||Thanks, but how would I only update the rows who are already only seven
characters long? Where would I put the where clause?
Cheers,
elziko|||Declare @.NewCharacter VarChar (20)
Declare @.OldCharcter VarChar (20)
Declare @.AddCharacter VarChar (20)
Set @.AddCharacter = '0'
Select @.OldCharcter = code_event FROM event_details where LEN(code_event)
= 7
Select @.NewCharacter = @.OldCharcter + @.AddCharacter
Print @.NewCharacter
Update event_details set code_event = @.NewCharacter where LEN(code_event) =
7
I am not sure exactly what you are trying to achieve. The above code will
add a '0' to every code_event that is 7 characters long. If your 7
character/digit code_events are different then I would suggest using a
cursor.
Cheers,
Andre
"elziko" <elziko@.NOTSPAMMINGyahoo.co.uk> wrote in message
news:403b730c$0$21923$afc38c87@.news.easynet.co.uk...
> Thanks, but how would I only update the rows who are already only seven
> characters long? Where would I put the where clause?
> --
> Cheers,
> elziko
>|||> I am not sure exactly what you are trying to achieve. The above code will
> add a '0' to every code_event that is 7 characters long. If your 7
> character/digit code_events are different then I would suggest using a
> cursor.
Yeah thats exactly what I want to do and thats where I initially put the
WHERE clauses before replying to you! But I get the following error:
MED0017 0
Server: Msg 8152, Level 16, State 9, Line 13
String or binary data would be truncated.
The statement has been terminated.
When I do the print of the NewCharacter it seems to have a space in it but
the column I'm updating is only 8 chars long so it looks like this is the
problem? Where is that space coming from? Or is something else wrong here?
Thanks a lot for your help.
Cheers,
elziko

quick SELECT statement question

Hello all!
I have a date/time column on my table. How can I write a select statement
to pick only items from todays date? I looked, but can't find an answer.
SELECT * User FROM TABLE WHERE datecolumn = "todays date"
Thanks!
RudySELECT
*
FROM
Table
WHERE
CONVERT(CHAR(8), datecolumn, 112) = CONVERT(CHAR(8),
GETDATE(), 112)
Due to the time that is stored in a datatime datatype, you need to strip the
time out as above.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rudy" <Rudy@.discussions.microsoft.com> wrote in message
news:5E70E4E8-EB91-4DBF-8489-5F9E3A5A204C@.microsoft.com...
> Hello all!
> I have a date/time column on my table. How can I write a select statement
> to pick only items from todays date? I looked, but can't find an answer.
> SELECT * User FROM TABLE WHERE datecolumn = "todays date"
> Thanks!
> Rudy|||Hi Mike!
WOW!! I wouild have never figured that out. Thanks!!
Rudy
"Mike Epprecht (SQL MVP)" wrote:

> SELECT
> *
> FROM
> Table
> WHERE
> CONVERT(CHAR(8), datecolumn, 112) = CONVERT(CHAR(8),
> GETDATE(), 112)
> Due to the time that is stored in a datatime datatype, you need to strip t
he
> time out as above.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Rudy" <Rudy@.discussions.microsoft.com> wrote in message
> news:5E70E4E8-EB91-4DBF-8489-5F9E3A5A204C@.microsoft.com...
>
>|||Hi
Or
SELECT
*
FROM
Table
WHERE
datecolumn >= (CONVERT(CHAR(8), GETDATE(), 112) + '
00:00:00.000'
AND datecolumn <= (CONVERT(CHAR(8), GETDATE(), 112) + '
23:59:59.997'
The 1st one can not use an index if that is the only predicate in the where
clause as each row needs to be evaluated.
The 2nd one could use an index, but make sure that you use ' 23:59:59.997'
and not ' 23:59:59.999' as .999 can not be represented in datetime, so it
rounds itself to 00:00:00.000, the next day
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rudy" <Rudy@.discussions.microsoft.com> wrote in message
news:2202B61C-5A3A-4FEB-B0E4-6FC0480FB1EA@.microsoft.com...
> Hi Mike!
> WOW!! I wouild have never figured that out. Thanks!!
> Rudy
> "Mike Epprecht (SQL MVP)" wrote:
>|||In addition to Mike's comments, you might want to red more about the subject
at:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rudy" <Rudy@.discussions.microsoft.com> wrote in message
news:5E70E4E8-EB91-4DBF-8489-5F9E3A5A204C@.microsoft.com...
> Hello all!
> I have a date/time column on my table. How can I write a select statement
> to pick only items from todays date? I looked, but can't find an answer.
> SELECT * User FROM TABLE WHERE datecolumn = "todays date"
> Thanks!
> Rudy

Quick select statement question

hi all

another easy one

how would i write it to where i select a value = 0 where all are equal to 0 rather than one line. when selecting based off a key from another table how would i select the values that all bring back 0 for that key rather than just a line or 2? i hope that makes sense, lol.What have you come up with so far?|||select case when exists(select * from MyTable where MyValue <> 0) then 1 else 0 end|||SELECT dbo.tPA00175.chrJobNumber, dbo.tPA00175.intJobKey, dbo.tPA00125.numQuantityToInv
FROM dbo.tPA00125 INNER JOIN
dbo.tPA00175 ON dbo.tPA00125.intJobKey = dbo.tPA00175.intJobKey
WHERE (dbo.tPA00125.numQuantityToInv = 0)

but this is only selecting the single values...id like to select the ones that have all 0's for the particular jobkey|||How about:

select dbo.tPA00175.chrJobNumber,
dbo.tPA00175.intJobKey,
numQuantityToInv = 0
from dbo.tPA00175 inner join (select intJobKey
from dbo.tPA00125
group by intJobKey
having max(case when numQuantityToInv = 0 then 0 else 1 end) = 0
) as t2 on dbo.tPA00175.intJobKey = t2.intJobKey|||thanks, that works nicely

Quick DISTINCT question

Hello all!
I know the following will work,
"SELECT DISTINCT Name, MIN(Sign) AS Sign
FROM Profile
GROUP BY Name"
Will return 2 columns, Name and Sign.
But what if I want more than just the two columns, and I need four to be
listed, but using the same code above. Just not sure how to add additional
columns without getting errors. Is this even possible?
TIA!!!
RudyIf you can show us some sample data and the required output we can come up
with some queries. Without that, you either add those additional columns to
the GROUP BY clause, or have then in the SELECT, within an aggregate
function.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Rudy" <Rudy@.discussions.microsoft.com> wrote in message
news:5EC4A04B-AD95-40C6-BBEE-D8A1E9B3BF52@.microsoft.com...
Hello all!
I know the following will work,
"SELECT DISTINCT Name, MIN(Sign) AS Sign
FROM Profile
GROUP BY Name"
Will return 2 columns, Name and Sign.
But what if I want more than just the two columns, and I need four to be
listed, but using the same code above. Just not sure how to add additional
columns without getting errors. Is this even possible?
TIA!!!
Rudy|||Yes, you need to decide, for each of those other columns, which of the
possible multiple values that exists should be output by the query...
Since you are Grouping By Name, that means you will get one row in your
output per disntinct value of Name. There may be many rows in the original
Table for each value of Name, each with different values for these other
columns... So for each, you must tell query whether to output the Min(), the
Max(), the Sum(), AVG(), or whatever...
Select Name, MIN(Sign) AS Sign,
Min(Col1), Max(Col2), etc...
From Profile
Group By Name
If you want ALL the values of these other columns listed, as:
Name Col1 Col2
John 1 AA
John 2 AB
John 3 AC
etc.
then you can't group just by name, you need to add the other columns to the
group By clause
"Rudy" wrote:

> Hello all!
> I know the following will work,
> "SELECT DISTINCT Name, MIN(Sign) AS Sign
> FROM Profile
> GROUP BY Name"
> Will return 2 columns, Name and Sign.
> But what if I want more than just the two columns, and I need four to be
> listed, but using the same code above. Just not sure how to add additional
> columns without getting errors. Is this even possible?
> TIA!!!
> Rudy|||also, look at the with rollup and with group options for group, then you can
do stuff like:
Select Name, col1, min(Sign)
From Profile
Group By Name, col1 with rollup
and you will get all sorts of different levels...
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:406DA4BA-6217-431D-B838-CA56B96C3453@.microsoft.com...
> Yes, you need to decide, for each of those other columns, which of the
> possible multiple values that exists should be output by the query...
> Since you are Grouping By Name, that means you will get one row in your
> output per disntinct value of Name. There may be many rows in the original
> Table for each value of Name, each with different values for these other
> columns... So for each, you must tell query whether to output the Min(),
> the
> Max(), the Sum(), AVG(), or whatever...
> Select Name, MIN(Sign) AS Sign,
> Min(Col1), Max(Col2), etc...
> From Profile
> Group By Name
> If you want ALL the values of these other columns listed, as:
> Name Col1 Col2
> John 1 AA
> John 2 AB
> John 3 AC
> etc.
> then you can't group just by name, you need to add the other columns to
> the
> group By clause
>
> "Rudy" wrote:
>|||Thanks you everyone for your suggestions! CBretana, your answer did the
trick. Thank!!!
Rudy
"CBretana" wrote:
> Yes, you need to decide, for each of those other columns, which of the
> possible multiple values that exists should be output by the query...
> Since you are Grouping By Name, that means you will get one row in your
> output per disntinct value of Name. There may be many rows in the original
> Table for each value of Name, each with different values for these other
> columns... So for each, you must tell query whether to output the Min(), t
he
> Max(), the Sum(), AVG(), or whatever...
> Select Name, MIN(Sign) AS Sign,
> Min(Col1), Max(Col2), etc...
> From Profile
> Group By Name
> If you want ALL the values of these other columns listed, as:
> Name Col1 Col2
> John 1 AA
> John 2 AB
> John 3 AC
> etc.
> then you can't group just by name, you need to add the other columns to th
e
> group By clause
>
> "Rudy" wrote:
>