One large database or many small....

Hi Experts

We are debating what is best:

1. To combine all the company's data in one large database, and use schemas and file groups to create logical and physical distribution on drives and namespaces

or

2. Distribute the data into smaller databases with related data - eg. products and product description in one db, Customers in another and orders and orderlines in a third db.

Just what are the pros and cons?

regards

Jens Chr

In almost any case I would suggest one database. The ability to use foreign key constraints is enough right there. The only reason I would suggest many databases is if you might need to seperate them into different servers, but that is very seldom.

It is just so much easier to work with data in the same database versus multiple databases. You get one log, which is easier to manage (unless you have tons of disk drive channels to have multiple log channels, it will be faster to have the one log). If you seperate stuff into filegroups instead of databases, you can still backup and restore them independently if you need to, as well as position the tables in a filegroup on different drives as needed.

|||I echo Louis' sentiments - listen to him. He's a wise and experienced SQL person :)

Check out my SQL Server 2005 Video Tutorials: http://www.learnsqlserver.com/

One Date 5 Queries

How can I use one date range (begin / end) for 5 separate queries? I can't
combine the queries into one because I get a timeout. I'm writing queries
against 5 different tables and the results need to be combined.
Any help is appreciated...Thanks
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1DDL, sample data, desired output
http://www.aspfaq.com/5006
"Chamark via webservertalk.com" <u21870@.uwe> wrote in message
news:6205a8242857e@.uwe...
> How can I use one date range (begin / end) for 5 separate queries? I can't
> combine the queries into one because I get a timeout. I'm writing queries
> against 5 different tables and the results need to be combined.
> Any help is appreciated...Thanks
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||Due to security I can't send that info out. I am only trying to figure out
how to use a single point of reference for my date range to support multipl
e
views. I create viewA to add up the sums and I currently hard code the date
range in. I then write viewB to do calculations referencing viewA for the
sums.
I have combined viewA & viewb into viewC and it provides me what I need
(Derivedtbl). I can pass a date range from a Web form to substitue the hard
coding of the date range. I need to link 5 derived tables just like the
aforementioned example (viewA + viewB + ViewC) into one combined derived
table, but I don't know how or if its even possible to pass the date range
from the Web form to all the sub views?
I am probably making this too hard so your expertise is certainly valued.
Chamark wrote:
>How can I use one date range (begin / end) for 5 separate queries? I can't
>combine the queries into one because I get a timeout. I'm writing queries
>against 5 different tables and the results need to be combined.
>Any help is appreciated...Thanks
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||> Due to security I can't send that info out.
So you can't come up with a frivolous and generic structure of what you want
to do? Ok...|||You can't send DDL, and a small set of illustrative data? We don't need
sensitive data that needs to be protected-only the TYPE of data that you are
dealing with. Probalby not the entire table either, PK, FK, Date fields, and
data to be summed. Seems like not a big issue if it provides you the
opportunity to gain the assistance and expertise you desire.
It sounds like you just may need one View or query - not multiple views.
I do believe that a web form could easily pass date values (start and end)
into a Stored Procedure. And then the Stored Procedure could easily pass the
parameters into several queries if necessary.
There may be many ways to solve your delimma, but we can't even start to
help unless we know what we're working with.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Chamark via webservertalk.com" <u21870@.uwe> wrote in message
news:62060b87788fa@.uwe...
> Due to security I can't send that info out. I am only trying to figure out
> how to use a single point of reference for my date range to support
> multiple
> views. I create viewA to add up the sums and I currently hard code the
> date
> range in. I then write viewB to do calculations referencing viewA for the
> sums.
> I have combined viewA & viewb into viewC and it provides me what I need
> (Derivedtbl). I can pass a date range from a Web form to substitue the
> hard
> coding of the date range. I need to link 5 derived tables just like the
> aforementioned example (viewA + viewB + ViewC) into one combined derived
> table, but I don't know how or if its even possible to pass the date range
> from the Web form to all the sub views?
> I am probably making this too hard so your expertise is certainly valued.
>
> Chamark wrote:
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||Hopefully this will help. How would I send you partial table(s) data?
CREATE TABLE [CSI] (
[Part Surv Id] [float] NULL ,
[Seq C] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Prod Id C] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Site] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Segment] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Associate] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Team] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date] [smalldatetime] NULL ,
[#] [float] NULL ,
[Resp Val Id] [float] NULL ,
[Adjusted Weight] [float] NULL ,
[ExtSatWt] [float] NULL ,
[VerSatWt] [float] NULL ,
[H1] [float] NULL ,
[Last Touch] [float] NULL ,
[Surv Strt Tm] [smalldatetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [National Call Stats] (
[Date] [smalldatetime] NULL ,
[Site] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Segment] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Director] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Team] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Rep Ssn] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Associate] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NchQty] [float] NULL ,
[SchdOpenSecsQty] [float] NULL ,
[LogOnSecsQty] [float] NULL ,
[InAdherenceSecsQty] [float] NULL ,
[OutOfAdherenceSecsQty] [float] NULL ,
[HoldSecsQty] [float] NULL ,
[TotalHandleTime] [float] NULL ,
[TalkHoldAvailable] [float] NULL
) ON [PRIMARY]
GO
CREATE TABLE [Call Scores] (
[Date] [smalldatetime] NULL ,
[Site] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Associate] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Score] [float] NULL
) ON [PRIMARY]
CREATE TABLE [CSI-Disconnect] (
[Date] [smalldatetime] NULL ,
[Associate] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[H1] [float] NULL ,
[Last Touch] [float] NULL ,
[Team] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Site] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Call Strt Tm] [smalldatetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [National AR] (
[Date] [smalldatetime] NULL ,
[Site] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Director] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Team] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Segment] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Associate] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SSN Count] [float] NULL ,
[RTF] [money] NULL ,
[RTC] [money] NULL ,
[CashedOut] [money] NULL
) ON [PRIMARY]
GO
REATE TABLE [OPA] (
[Date] [smalldatetime] NULL ,
[Site] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Segment] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Director] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Team] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OPA Code] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Opa Seconds Qty] [float] NULL ,
[Associate] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
Arnie Rowland wrote:
>You can't send DDL, and a small set of illustrative data? We don't need
>sensitive data that needs to be protected-only the TYPE of data that you ar
e
>dealing with. Probalby not the entire table either, PK, FK, Date fields, an
d
>data to be summed. Seems like not a big issue if it provides you the
>opportunity to gain the assistance and expertise you desire.
>It sounds like you just may need one View or query - not multiple views.
>I do believe that a web form could easily pass date values (start and end)
>into a Stored Procedure. And then the Stored Procedure could easily pass th
e
>parameters into several queries if necessary.
>There may be many ways to solve your delimma, but we can't even start to
>help unless we know what we're working with.
>
>[quoted text clipped - 19 lines]
Message posted via http://www.webservertalk.com