Help with SQL planning

The law firm I work for is about to go through some major software/hardware
changes that will include several new applications needing to use SQL
server. I definately want to make sure I make the right decision as to what
hardware and licensing choices are right for us (in other words I dont want
to be talked into buying anything we dont need).
The applications that need the use of a SQL server are:
Imaging database (database for scanned legal documents)
Firm database (stores the firms software application data)
Dialer database (works with a predictive dialer)
There are roughly 80 users on the network...with almost all accessing the
Firm database...Imaging only used by a few but contains alot of data...The
dialer would only account for about 20 users but as you can imagine would be
put to constant use.
Would it be logical to put all this load on one server? What solution would
you recommend from a cost perspective? A performance perspective?
Any tips would be greatly appreciated.
DaveHi Dave
As I don't know any of these applications, the best estimates will be those
provided by a vendor or partner. You can always get more than one quote and
award your business to the best tender.
If you are already using these applications then you should be able to get
some idea of sizing for the databases and predict possible growth rates.
You may have to do some load testing as part of the test cycle to make sure
the supplied system meets your capacity specifications.
Putting them on the same server would mean that they would share resources,
it would also mean that it would be very difficult to conclude that any one
application supplier was causing a particular issue. Having separate systems
may mean additional up-front costs for hardware and software licences.
What ever system(s) you opt for, you may want to make sure that you have
options to expand without having to totally change the hardware again.
John
"Dave" wrote:
> The law firm I work for is about to go through some major software/hardware
> changes that will include several new applications needing to use SQL
> server. I definately want to make sure I make the right decision as to what
> hardware and licensing choices are right for us (in other words I dont want
> to be talked into buying anything we dont need).
> The applications that need the use of a SQL server are:
> Imaging database (database for scanned legal documents)
> Firm database (stores the firms software application data)
> Dialer database (works with a predictive dialer)
> There are roughly 80 users on the network...with almost all accessing the
> Firm database...Imaging only used by a few but contains alot of data...The
> dialer would only account for about 20 users but as you can imagine would be
> put to constant use.
> Would it be logical to put all this load on one server? What solution would
> you recommend from a cost perspective? A performance perspective?
> Any tips would be greatly appreciated.
> Dave
>
>|||Dave wrote:
> The law firm I work for is about to go through some major software/hardware
> changes that will include several new applications needing to use SQL
> server. I definately want to make sure I make the right decision as to what
> hardware and licensing choices are right for us (in other words I dont want
> to be talked into buying anything we dont need).
> The applications that need the use of a SQL server are:
> Imaging database (database for scanned legal documents)
> Firm database (stores the firms software application data)
> Dialer database (works with a predictive dialer)
> There are roughly 80 users on the network...with almost all accessing the
> Firm database...Imaging only used by a few but contains alot of data...The
> dialer would only account for about 20 users but as you can imagine would be
> put to constant use.
> Would it be logical to put all this load on one server? What solution would
> you recommend from a cost perspective? A performance perspective?
> Any tips would be greatly appreciated.
> Dave
>
Hi Dave,
Based on your questions, I would suggest you hire a Consultant to plan
things "right from the start". Best of luck to you on this project.

Help with Join

H
I have two tables
Create table company
( firm char(32),
p_name char(40),
proj_code char(8),
proj_start_dt datetime
)
go
insert into company values ("ABC","John Doe", "DJOE","Nov 12 2001")
insert into company values ("ABC","Jane Doe", "JANEDOE","Oct 26 2000")
insert into company values ("ABC","Bruce Smith", "BRUCES","Mar 01 2002")
insert into company values ("ABC","David Smith", "SDAVID","Nov 12 2003")
insert into company values ("ABC","Lisa Cox", "LCOX","Apr 15 2004")
go
Create table employee
( firm char(32),
p_name char(40),
proj_end_dt datetime
)
go
insert into employee values ("ABC","John Doe", "Nov 20 2003")
insert into employee values ("ABC","Jane Doe", "Dec 26 2002")
insert into employee values ("ABC","Bruce Smith","Apr 01 2003")
go
I need to extract data for p_name with proj_start_dt > proj_end_dt
my query is not working
select c.firm,
c.p_name,
c.proj_code,
c.proj_start_dt
from company c
left join employee e
on c.p_name=e.p_name
and c.proj_start_dt > e.proj_end_dt
go
returns all values
firm p_name
proj_code proj_start_dt
-- --- --
-- --
ABC John Doe
DJOE Nov 12 2001 12:00AM
ABC Jane Doe
JANEDOE Oct 26 2000 12:00AM
ABC Bruce Smith
BRUCES Mar 1 2002 12:00AM
ABC David Smith
SDAVID Nov 12 2003 12:00AM
ABC Lisa Cox
LCOX Apr 15 2004 12:00AM
select c.firm,
c.p_name,
c.proj_code,
c.proj_start_dt
from company c,
employee e
where c.p_name=e.p_name
and c.proj_start_dt > e.proj_end_dt
go
returns no values
firm p_name
proj_code proj_start_dt
-- --- --
-- --
Could someone please help.
AjmisterThanks for posting the DDL and sample data. Could you show and explain
exactly what result you want. As far as I can see there are no rows where
Proj_start_dt is greater than Proj_end_dt for any given P_name.
David Portas
SQL Server MVP
--|||try this...
select c.firm,
c.p_name,
c.proj_code,
c.proj_start_dt
from company c
left join employee e
on c.p_name=e.p_name
where c.proj_start_dt > e.proj_end_dt
"ajmister" wrote:

> H
> I have two tables
> Create table company
> ( firm char(32),
> p_name char(40),
> proj_code char(8),
> proj_start_dt datetime
> )
> go
> insert into company values ("ABC","John Doe", "DJOE","Nov 12 2001")
> insert into company values ("ABC","Jane Doe", "JANEDOE","Oct 26 2000")
> insert into company values ("ABC","Bruce Smith", "BRUCES","Mar 01 2002")
> insert into company values ("ABC","David Smith", "SDAVID","Nov 12 2003")
> insert into company values ("ABC","Lisa Cox", "LCOX","Apr 15 2004")
> go
>
> Create table employee
> ( firm char(32),
> p_name char(40),
> proj_end_dt datetime
> )
> go
> insert into employee values ("ABC","John Doe", "Nov 20 2003")
> insert into employee values ("ABC","Jane Doe", "Dec 26 2002")
> insert into employee values ("ABC","Bruce Smith","Apr 01 2003")
> go
> I need to extract data for p_name with proj_start_dt > proj_end_dt
> my query is not working
>
> select c.firm,
> c.p_name,
> c.proj_code,
> c.proj_start_dt
> from company c
> left join employee e
> on c.p_name=e.p_name
> and c.proj_start_dt > e.proj_end_dt
> go
> returns all values
> firm p_name
> proj_code proj_start_dt
> -- ---
--
> -- --
> ABC John Doe
> DJOE Nov 12 2001 12:00AM
> ABC Jane Doe
> JANEDOE Oct 26 2000 12:00AM
> ABC Bruce Smith
> BRUCES Mar 1 2002 12:00AM
> ABC David Smith
> SDAVID Nov 12 2003 12:00AM
> ABC Lisa Cox
> LCOX Apr 15 2004 12:00AM
>
> select c.firm,
> c.p_name,
> c.proj_code,
> c.proj_start_dt
> from company c,
> employee e
> where c.p_name=e.p_name
> and c.proj_start_dt > e.proj_end_dt
> go
> returns no values
> firm p_name
> proj_code proj_start_dt
> -- ---
--
> -- --
> Could someone please help.
>
> Ajmister
>
>|||Did you try it? :-)
David Portas
SQL Server MVP
--|||Sorry abt my previous post, that was wrong.
here is the answer,
the company table has the proj_start_dt = Apr 15 2004 which is greater than
the proj_end date.But the value of p_name in the company table is Lisa Cox
which is not there in the employee table.So when you do a join you will not
get the result.
"ajmister" wrote:

> H
> I have two tables
> Create table company
> ( firm char(32),
> p_name char(40),
> proj_code char(8),
> proj_start_dt datetime
> )
> go
> insert into company values ("ABC","John Doe", "DJOE","Nov 12 2001")
> insert into company values ("ABC","Jane Doe", "JANEDOE","Oct 26 2000")
> insert into company values ("ABC","Bruce Smith", "BRUCES","Mar 01 2002")
> insert into company values ("ABC","David Smith", "SDAVID","Nov 12 2003")
> insert into company values ("ABC","Lisa Cox", "LCOX","Apr 15 2004")
> go
>
> Create table employee
> ( firm char(32),
> p_name char(40),
> proj_end_dt datetime
> )
> go
> insert into employee values ("ABC","John Doe", "Nov 20 2003")
> insert into employee values ("ABC","Jane Doe", "Dec 26 2002")
> insert into employee values ("ABC","Bruce Smith","Apr 01 2003")
> go
> I need to extract data for p_name with proj_start_dt > proj_end_dt
> my query is not working
>
> select c.firm,
> c.p_name,
> c.proj_code,
> c.proj_start_dt
> from company c
> left join employee e
> on c.p_name=e.p_name
> and c.proj_start_dt > e.proj_end_dt
> go
> returns all values
> firm p_name
> proj_code proj_start_dt
> -- ---
--
> -- --
> ABC John Doe
> DJOE Nov 12 2001 12:00AM
> ABC Jane Doe
> JANEDOE Oct 26 2000 12:00AM
> ABC Bruce Smith
> BRUCES Mar 1 2002 12:00AM
> ABC David Smith
> SDAVID Nov 12 2003 12:00AM
> ABC Lisa Cox
> LCOX Apr 15 2004 12:00AM
>
> select c.firm,
> c.p_name,
> c.proj_code,
> c.proj_start_dt
> from company c,
> employee e
> where c.p_name=e.p_name
> and c.proj_start_dt > e.proj_end_dt
> go
> returns no values
> firm p_name
> proj_code proj_start_dt
> -- ---
--
> -- --
> Could someone please help.
>
> Ajmister
>
>|||Your first join is returning all rows because you are doing an outer join
which is telling SQL you want to return all row from the set on the left
(company) even if they don't match any rows in the set on the right
(employee). If you run the following command you can see you are not
returning any values from the employee table:
select c.*, e.*
from company c
left join employee e
on c.p_name=e.p_name
and c.proj_start_dt > e.proj_end_dt
go
Your second query seems to be doing what you want, and you are not getting
any rows returned because the are no company.proj_start_dt's that are > the
employee.proj_end_dt when the company.p_name and employee.p_name are equal.
Hope this helps explain why you are getting the results you posted.
----
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"ajmister" <ajmister@.optonline.net> wrote in message
news:etG3OUXCFHA.4052@.TK2MSFTNGP15.phx.gbl...
> H
> I have two tables
> Create table company
> ( firm char(32),
> p_name char(40),
> proj_code char(8),
> proj_start_dt datetime
> )
> go
> insert into company values ("ABC","John Doe", "DJOE","Nov 12 2001")
> insert into company values ("ABC","Jane Doe", "JANEDOE","Oct 26 2000")
> insert into company values ("ABC","Bruce Smith", "BRUCES","Mar 01 2002")
> insert into company values ("ABC","David Smith", "SDAVID","Nov 12 2003")
> insert into company values ("ABC","Lisa Cox", "LCOX","Apr 15 2004")
> go
>
> Create table employee
> ( firm char(32),
> p_name char(40),
> proj_end_dt datetime
> )
> go
> insert into employee values ("ABC","John Doe", "Nov 20 2003")
> insert into employee values ("ABC","Jane Doe", "Dec 26 2002")
> insert into employee values ("ABC","Bruce Smith","Apr 01 2003")
> go
> I need to extract data for p_name with proj_start_dt > proj_end_dt
> my query is not working
>
> select c.firm,
> c.p_name,
> c.proj_code,
> c.proj_start_dt
> from company c
> left join employee e
> on c.p_name=e.p_name
> and c.proj_start_dt > e.proj_end_dt
> go
> returns all values
> firm p_name
> proj_code proj_start_dt
> -- ---
--
> -- --
> ABC John Doe
> DJOE Nov 12 2001 12:00AM
> ABC Jane Doe
> JANEDOE Oct 26 2000 12:00AM
> ABC Bruce Smith
> BRUCES Mar 1 2002 12:00AM
> ABC David Smith
> SDAVID Nov 12 2003 12:00AM
> ABC Lisa Cox
> LCOX Apr 15 2004 12:00AM
>
> select c.firm,
> c.p_name,
> c.proj_code,
> c.proj_start_dt
> from company c,
> employee e
> where c.p_name=e.p_name
> and c.proj_start_dt > e.proj_end_dt
> go
> returns no values
> firm p_name
> proj_code proj_start_dt
> -- ---
--
> -- --
> Could someone please help.
>
> Ajmister
>|||Thank you for a quick response, sorry I want to extract all Proj_start_dt
that do not have a proj_end_dt
Thanx
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:taednVcaFv9azpzfRVn-sg@.giganews.com...
> Thanks for posting the DDL and sample data. Could you show and explain
> exactly what result you want. As far as I can see there are no rows where
> Proj_start_dt is greater than Proj_end_dt for any given P_name.
> --
> David Portas
> SQL Server MVP
> --
>|||Yes, but it did not return any values
firm p_name
proj_code proj_start_dt
-- --- --
-- --
I would the query to return proj_start_dt whihc do not have proj_end_dt
(sorry about the previous error)
example
firm p_name
proj_code proj_start_dt
-- --- --
-- --
ABC David Smith
SDAVID Nov 12 2003 12:00AM
ABC Lisa Cox
LCOX Apr 15 2004 12:00AM
Thank you
Ajmister
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:yt6dnXskg5uRy5zfRVn-vg@.giganews.com...
> Did you try it? :-)
> --
> David Portas
> SQL Server MVP
> --
>|||Try this:
SELECT C.firm, C.p_name, C.proj_code, C.proj_start_dt
FROM company AS C
LEFT JOIN employee AS E
ON C.p_name = E.p_name
AND C.proj_start_dt < E.proj_end_dt
WHERE C.proj_start_dt IS NOT NULL
AND E.proj_end_dt IS NULL
David Portas
SQL Server MVP
--|||Thank you sir. That gave me the correct output.
Ajmister
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:OIydnb5UiOmFGZzfRVn-iA@.giganews.com...
> Try this:
> SELECT C.firm, C.p_name, C.proj_code, C.proj_start_dt
> FROM company AS C
> LEFT JOIN employee AS E
> ON C.p_name = E.p_name
> AND C.proj_start_dt < E.proj_end_dt
> WHERE C.proj_start_dt IS NOT NULL
> AND E.proj_end_dt IS NULL
> --
> David Portas
> SQL Server MVP
> --
>