Single Or Multiple DB For Different Systems?

Jun 6, 2008

Hi everyone,

Im having a hard time deciding what approach should I take. The scenario is this: I have developed various systems (inventory, HR, accounting, etc.). All this systems are (and should be) tightly integrated with one another. At present, for all these systems, i've used a single DB prefixing the tables with the systems name (eg. Inventory.Items).

My question is: did I did the right (and practical) thing? Or should I create a DB for each system to organize them? The problem with multiple DBs is some system uses the other system's table(s). Example, if i created a separate DB for accounting, and a separated DB for inventory, and another for HR, how am I going to relate inventory and HR's accounts to the accounting DB's table? I want a single instance for each table; I don't want to create another account table for inventory or HR so I can enforce integrity. And if different DBs, is there a performance impact on this?

Or is there another way? My concern is performance and manageability. Please help. Thanks!

View 2 Replies


ADVERTISEMENT

How To Merge Multiple Rows One Column Data Into A Single Row With Multiple Columns

Mar 3, 2008



Please can anyone help me for the following?

I want to merge multiple rows (eg. 3rows) into a single row with multip columns.

for eg:
data

Date Shift Reading
01-MAR-08 1 879.880
01-MAR-08 2 854.858
01-MAR-08 3 833.836
02-MAR-08 1 809.810
02-MAR-08 2 785.784
02-MAR-08 3 761.760

i want output for the above as:

Date Shift1 Shift2 Shift3
01-MAR-08 879.880 854.858 833.836
02-MAR-08 809.810 785.784 761.760
Please help me.

View 8 Replies View Related

Transact SQL :: Query To Convert Single Row Multiple Columns To Multiple Rows

Apr 21, 2015

I have a table with single row like below

 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
Column0 | Column1 | Column2 | Column3 | Column4|
 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Value0    | Value1    | Value2    | Value3    |  Value4  |

Am looking for a query to convert above table data to multiple rows having column name and its value in each row as shown below

_ _ _ _ _ _ _ _
Column0 | Value0
 _ _ _ _ _ _ _ _
Column1 | Value1
 _ _ _ _ _ _ _ _
Column2 | Value2
 _ _ _ _ _ _ _ _
Column3 | Value3
 _ _ _ _ _ _ _ _
Column4 | Value4
 _ _ _ _ _ _ _ _

View 6 Replies View Related

Multiple Columns With Different Values OR Single Column With Multiple Criteria?

Aug 22, 2007

Hi,

I have multiple columns in a Single Table and i want to search values in different columns. My table structure is

col1 (identity PK)
col2 (varchar(max))
col3 (varchar(max))

I have created a single FULLTEXT on col2 & col3.
suppose i want to search col2='engine' and col3='toyota' i write query as

SELECT

TBL.col2,TBL.col3
FROM

TBL
INNER JOIN

CONTAINSTABLE(TBL,col2,'engine') TBL1
ON

TBL.col1=TBL1.[key]
INNER JOIN

CONTAINSTABLE(TBL,col3,'toyota') TBL2
ON

TBL.col1=TBL2.[key]

Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record.

I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information.
for e.g.;
i was thinking to concatinate both fields like
col4= ABengineBA + ABBToyotaBBA
and in search i use
SELECT

TBL.col4
FROM

TBL
INNER JOIN

CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1
ON

TBL.col1=TBL1.[key]
Result = 1 row

But it don't work in following scenario
col4= ABengineBA + ABBCorola ToyotaBBA

SELECT

TBL.col4
FROM

TBL
INNER JOIN

CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1
ON

TBL.col1=TBL1.[key]

Result=0 Row
Any idea how i can write second query to get result?

View 1 Replies View Related

Transact SQL :: Converting From Multiple Rows With Single Values To Single Rows With Multiple Values

May 10, 2015

Here is some data that will explain what I want to do:

Input Data:
Part ColorCode
A100 123
A100 456
A100 789
B100 456
C100 123
C100 456

Output Data:
Part ColorCode
A100 123;456;789
B100 456
C100 123;456

View 4 Replies View Related

Insert Single Row / Multiple Rows Into Multiple Tables

Sep 3, 2014

How to insert single row/multiple rows into multiple tables by using single insert statement.

View 1 Replies View Related

SQLCE V3.5: Single SDF With Multiple Tables Or Multiple SDFs With Fewer Tables

Mar 21, 2008

Hi! I have a general SQL CE v3.5 design question related to table/file layout. I have an system that has multiple tables that fall into categories of data access. The 3 categories of data access are:


1 is for configuration-related data. There is one application that will read/write to the data, and a second application that will read the data on startup.

1 is for high-performance temporal storage of data. The data objects are all the same type, but they are our own custom object and not just simple types.

1 is for logging where the data will be permanent - unless the configured size/recycling settings cause a resize or cleanup. There will be one application writing alot [potentially] of data depending on log settings, and another application searching/reading sections of data.
When working with data and designing the layout, I like to approach things from a data-centric mindset, because this seems to result in a better performing system. That said, I am thinking about using 3 individual SDF files for the above data access scenarios - as opposed to a single SDF with multiple tables. I'm thinking this would provide better performance in SQL CE because the query engine will not have alot of different types of queries going against the same database file. For instance, the temporal storage is basically reading/writing/deleting various amounts of data. And, this is different from the logging, where the log can grow pretty large - definitely bigger than the default 128 MB. So, it seems logical to manage them separately.

I would greatly appreciate any suggestions from the SQL CE experts with regard to my approach. If there are any tips/tricks with respect to different data access scenarios - taking into account performance, type of data access, etc. - I would love to take a look at that.

Thanks in advance for any help/suggestions,
Bob

View 1 Replies View Related

Single Or Multiple Sp ???

Nov 1, 2005

greetings,i was wondering is it better to have multiple small stored procedures or one large store procedure ???? example : 100 parameters that needs to be inserted  or delete ..into/from a table.............is it better to break it up into multiple small store proc or have 1 large store proc....thanks...............

View 3 Replies View Related

Bcp Single CPU Vs Multiple CPU

Oct 20, 2001

I'm doing a BCP of a large table 37 million rows. On a single CPU server, SQL 7, sp 3, with 512 meg of RAM, this job runs in about 3 hours. On a 8 way server with 4 Gig of RAM, SQL 7 Enterprise, this job runs 12 hours and is only a third done. The single CPU machine is running one RAID 5 set while the 8 way server is running 4 RAID 5 sets with the database spread out over two of them.

Is there something obvious that a single CPU box would run this much faster?

View 5 Replies View Related

Multiple Values For Single Row

Mar 19, 2007

hi iam totally new to databases , as a project i have to design a database of users...they have to register first like any site..so i used stored procs and made entries to database using insert command...its working for now..now every user will search and add other users in the database..so every user will have a contact list...i have no idea how to implement this...so far i created a table 'UserAccount' with column names as UserName as varchar(50)Password as varchar(50)EmailID as varchar(100)DateOfJoining as datetimeUserID as int ---> this is unique for user..i enabled automatic increment..and this is primary key..so now every user must have a list of other userid's.. as contact list..Any help any ideas will be great since i have no clue how to put multiple values for each row..i didnt even know how to search for this problems solution..iam sorry if this posted somewhere else..THANK YOU !if it helps..iam using sql server express edition..and iam accessing using asp.net/C#

View 1 Replies View Related

How To Group Multiple Row As Single Row

May 30, 2008

eg say.

i have a table emp

data
----
id name phone no
1 smith 423-422-5226
1 smith 414-255-5252
2 george 511-522-2525
2 george 524-522-2428
........
........


i need output as

1 smith 423-422-5226, 414-255-5252
2 george 511-522-2525, 524-522-2428
.....

can u any one help me

View 1 Replies View Related

Multiple Value In One Single Parameter

Mar 12, 2014

I am looking for a simple way to do multiple values in one single parameter in my simple Stored Procedures. Let's say for example I have a column called RoomNumber and the value data type is INT. Here is my Stored Procedures:

CREATE PROC ROOMVACANCY
@RoomNumber int,
SELECT vacancy, roomnumber
FROM hoteldb
WHERE Vacancy IN (@RoomNumber)
END

The value for roomnumber has 100 records. I want to be able to select for more than selection when I execute this stored procedures. How do I do that in the simple way?

View 10 Replies View Related

Multiple Copy Of A Single Row

Aug 15, 2007

Hi All,

Dont mistake me for asking such a small question, im little bit confused.

Result of my query gives 1 rows.
i want some hundred copy of that single row, how can i do that.

Right now i put a while loop and i get the things done, but i know its a wrong way. plz guide me

With Regards
Amjath

PS:if anybody having good tutorial for sql pls pass it to me

View 2 Replies View Related

Multiple SET/WHERE Within Single UPDATE?

Mar 21, 2008



Can an Update statement support multiple SET/WHERE clauses? The following example won't work, but this is what I'd like to be able to do:





Code Snippet
UPDATE [CPSAgacar].tmp_CTA2

SET [Admit Date] = NULL
WHERE [CPSAgacar].tmp_CTA2.CalcAdmitDate IS NOT NULL

SET CalcDischDate = Discharge
WHERE [CPSAgacar].tmp_CTA2.CalcAdmitDate IS NOT NULL AND CPSAgacar].tmp_CTA2.CalcDischDate IS NULL






Thanks.

View 1 Replies View Related

Single Or Multiple Services

Aug 31, 2007

I have 2 systems that will send data to each other. Each system will originate a particular set of messages, there will be no overlap. This scenario has transaction data going to a reporting system and management operations going back to the transactional system. It is semi-related data.

I have two patterns in mind, a combined stream of all messages or 2 streams of segregated messages.

A. A single service on each instance would originate a set of messages, process the responses, and receive the other instance's messages. The responses and original message from the other system would mix on the same Q. The activation procs would have to handle all message types. The same infrastructure (message types, contracts, Qs, activation stored procs) would be created on both systems. Although, distinct service names and ports would be used on each instance.

B. Two services and two Qs. 1 service would originate a set of messages, process the responses. The other service would process messages from the other system. The responses and original message from the other system would be on the separate Qs. There would be 2 infrastructures created. There could be separate activation stored procs.

There is just one message type and message validation is only WELL_FORMED_XML.

Which is pattern is better for management and performance? Should I create 1 service or 2 on each instance? Either way should work about as well as the other? 2 services are twice as complex to set up. Separation is not necessary, but I like the idea. 1 service will send many more messages (>10x) than the other. Any thoughts?

View 1 Replies View Related

Single Row Into Multiple Rows

May 8, 2008

Hi All,

We've a table as in the following format:













PK_Column1
PK_Column2
Issue_Date1
Issue_Amount1
Issue_Category1
Issue_Reject1
Issue_Date2
Issue_Amount2
Issue_Category2
Issue_Reject2


We need to divide it into two new tables as follows:








UniqueID
PK_Column1
PK_Column2
And











UniqueID
PK_Column1
PK_Column2
Sequence_ID
Issue_Date
Issue_Amount
Issue_Category
Issue_Reject

Unique1


1
Issue_Date1
Issue_Amount1
Issue_Category1
Issue_Reject1

Unique2


2
Issue_Date2
Issue_Amount2
Issue_Category2


Unique3


1
xx


xx

Unique4


2
xx

xx


Unique5


3
xx







4

xx



There will be one UniqueID for each row.
We'll get the uniqueID and PK1 and PK2 in a file.
Imp: We need to generate the Sequence_Id depending on number of Issue_dates or Issue_amounts or Issue_Categories or Issue_Rejects as in the above table.

Can we do this without using cursors?
This is going to be one time process.

Any ideas are appreciated.

Thanks,
Siva.







View 1 Replies View Related

Getting Multiple Rows In A Single Row

Jan 21, 2008

Hi,

I've a temp variable where I'm moving some columns like below:







id
value
type1
type2

0
ab
type1val1
type2val1

0
cd
type1val1
type2val1

0
ef
type1val1
type2val1

1
ab
type1val2
type2val2

1
cd
type1val2
type2val2

1
ef
type1val2
type2val2
What I want to do is group these by their id and get the following o/p
ab,cd,ef type1val1 type2val1
ab,cd,ef type1val2 type2val2

The grouped values need to be separated by commas.

What I'm doing currently:
I'm using a temp variable to put all these values but am unable to coalesce and get the desired o/p.

Can anybody help me out?

Thanks,
Subha

View 4 Replies View Related

Convert Single MDF To Multiple MDF + Ndf + Ndf

Feb 12, 2008



I have a 100GB db that is all in a single MDF file.

I would like to spread this across a few (or several) files, with the data distributed evenly (or close to it).

In other words, I would like to have, say 10 files in [PRIMARY] each with 10GB.

Any thoughts on how to make this happen?

View 3 Replies View Related

How To Run Multiple Sql Statements From A Single File?

Nov 15, 2005

Hi,I am wondering if anyone has any examples of how to run multiple sql statements from a file using .net?  I want to automatically install any stored procedures or scripts from a single file on the server when my web application runs for the first time.  Thanks for your help in advance!

View 1 Replies View Related

Multiple Columns Into Single Row -- Very Urgent

Jun 15, 2006

Hi. I want to return multiple rows into a single row in different columns. For example my query returns something like thisThe query looks like thisSelect ID, TYPE, VALUE From myTable Where filtercondition = 1ID       TYPE       VALUE1         type1      121         type2      152         type1      16   2         type2      19Each ID will have the same number of types and each type for each ID might have a different value. So if there are only two types then each ID will have two types. Now I want to write the query in such a way that it returnsID      TYPE1      TYPE2      VALUE1      VALUE21        type1       type2      12             152        type1       type2      16             19Type1, Type2, Value1, and Value2 are all dynamic. Can someone help me please. Thank you.

View 1 Replies View Related

Comparing For Multiple Value With Single Column

May 10, 2001

Hi,
i need to select from a table transact where one of the coulmn values has to be equal to (1and 2 and 3).

e.g: column in (1,2,3) would give me what "OR" would do,

View 1 Replies View Related

Dump Multiple DBs To Single Device

Mar 27, 2000

I'm having trouble doing backups of several databases (on a single server) to one device (a disk file).
I created a script with each DUMP statement and when I run it from the query window, it works
just fine. But when I create a stored procedure out of the same script, I get errors because the
second DUMP statement is trying to access the device that is already being written to by the
first DUMP statement.

If I split them apart into different stored procedures, then they seem to overwrite each other and
I end up with only the last database backed up.

I'm trying to put this into a task and that is why I need to put it into stored procedures.

Is there a synchronous/asynchronous setting or parameter that I should be using? For now, I'm
just dumping each to separate devices, but this is a little sumbersome, since I have four
databases to backup for each day of the week. Which gives me a total of 28 separate
devices.

I'm sure there is a better way of doing this. Does anyone have any suggestions. Thank you in
advance.

View 1 Replies View Related

Store Multiple Values In A Single Value

Nov 6, 2005

was hoping someone couild provide some insight into a problem I'm trying to solve.

I have a table called SEARCHCRITERIA. It consists of a USERID column and a CRITERIA column. Users will be able to search for other users based on a set of criteria. There are 5 total criteria a user can choose. They can choose as few as none or all five. I'd like to store the criteria chosen as a single number in the SEARCHCRITERIA table. Then use a function to parse out the criteria. For example:

CRITERIAID CRITERIA CRITERIAVALUE
1 AGE 2
2 SEX 4
3 GRADE 8
4 LOCALE 16
5 REGION 32

A user performs a search based on AGE, SEX, and LOCALE. I would then store the value 22 (the sum of 2, 4, and 16) in the SEARCH table. I would then need a function to pull out the three individual values.

Has anyone done anything like this before?

If so, any help would be appreciated!

Thanks in advance!

View 1 Replies View Related

Spliting Single Row Into Multiple Rows

Sep 20, 2006

I have a table that contains many columns in a single row and I'd like to split the table so that it has fewer column values and more rows.
My table structure is:
create table #scoresheet
(Decisions varchar(10), DNumericalValue int, DVI varchar(10), DComments nvarchar(255),
Competence varchar(10), CNumericalValue int, CVI varchar(10), CComments nvarchar(255),
Equipment varchar(10), ENumericalValue int, EVI varchar(10), EComments nvarchar(255));
I would like to have three rows with four columns.
What I've done so far is create a stored procedure that uses a table variable:
create procedure sp_splitsinglerow as

declare @Scoresheet_rows_table_var table (
ReviewArea varchar(25),
NumericalValue int,
VI varchar(10),
Comments nvarchar(255));
insert into @Scoresheet_rows_table_var
(ReviewArea, NumericalValue, VI, Comments)
select Decisions, DNumericalValue, DVI, DComments
from #scoresheet

The trouble with this approach is that I have to explicitly name the columns that I insert into the table variable. What I'd really like to be able to is have a loop construct and select the first 4 columns the first time, the second 4 the next time and the last 4 the third time.

Any ideas on how to achieve that?

BTW, I have resolved this issue by suggesting to the Developers that they change the structure of the original table, but I'd still like to know if there is another solution. :)

View 2 Replies View Related

View Multiple Records In A Single Row?

Jan 3, 2012

Id account num acc_type
42 1376200071278 gl
42 1308111111111 ic
42 1291111111111 os
34 1245200000000 gl
34 1132485111111 ic

this is table structure.there are multiple records like this in a table . I need output as

id gl accountnum ic accountnum osaccountnum
42 1376200071278 1308111111111 1291111111111
34 1245200000000 1132485111111 -

View 7 Replies View Related

Split Single Row To Multiple Rows

Feb 27, 2012

I have a table which looks like

Low High
-------------------------
cx01 cx04
sn05 sn08

I need output like

Result
-------------
cx01
cx02
cx03
cx04
sn05
sn06
sn07
sn08

How to get this output using sql query?

View 8 Replies View Related

Multiple Distributors From A Single Publisher?

Mar 7, 2013

I am setting up a 40 subscriber node transactional replication environment (pull), and was looking to have 2 or more distributors service one publisher, each having a group of subscribers, is there any benefit to this ?, as opposed to having one distributor service all subscribers, is it possible (distributor at instance level ?)

From what I've seen, I could not find anything that shows multiple distributors to one publisher.

Publisher and Distributor are on separate physical hosts.

View 1 Replies View Related

Restoring Multiple Databases From Single BAK?

Oct 24, 2013

I have a single .bak file containing full backups of 20+ databases.

Whats the best way of creating and restoring these database onto an instance on a separate server.

note that this is to migrate the whole instance of sql server onto a newly build server.

View 1 Replies View Related

T-SQL (SS2K8) :: Multiple Rows Into A Single Row

Nov 7, 2014

I am working with some old code that we are trying to clean up and perform some performance enhancements. The performance is now, so Very much better. From over 3 minutes to under 2 seconds.

But I am still trying to get the multiple rows into a single row. I would like to place this into a CTE to get the multiples into a single row. I just cannot get my head around how is the best, most efficient way to write the query.

This is a small example of what the rows look like in the resultset, and what I want to single to be.

DECLARE @BillingCorrect TABLE
(
ContractNumber char(10)
, pc1 int
, pb int
, om int
, vp int

[Code] ....

I am not sure how to write the query to have all the data in a single row.

View 2 Replies View Related

SQL Single Query For Multiple Table

Apr 15, 2008

Hi friends,
I have three table named as Eventsmgmt,blogmgmt,forummgmt..
Each table contain the common column named as CreatedDateTime..

I want to get the most recent CreationDateTime from these three table in single query..

Plzz help me its urgent

Thanks

View 20 Replies View Related

Multiple Inserts In Single Trigger

Feb 10, 2014

I have a requirement that if in a table update happened based on 1st condition then it should insert in one way and if update happened on second condition the insert statement will differ. That is it should insert the deleted records i.e., previous records existed in a table.The syntax is like

CREATE TRIGGER [dbo].[tr_a] on [dbo].[A]
AFTER UPDATE
AS
BEGIN TRY
IF NOT EXISTS

[code]....

I m getting some syntactical errors.

View 6 Replies View Related

Multiple Value In Single Parameter - ZoneID

Mar 6, 2014

I am writting the T-SQL using some parameters. One of the parameter is to display the ZoneID which has about 2100 records. So I declare the parameter (called it @ZoneID varchar(max)).

Now, how do I make all the value available for multiple selection? For example, if I execute the query, I can put the ZoneID (for example:) YH09, TR45, TTS0.

View 2 Replies View Related

Passing Multiple Value In Single Parameter

Mar 9, 2014

How do I pass the multiple value in the single parameter? Here is the select simple select statement with two fields (ID and Map_Area):

SELECT *
FROM TestParamOne

Here is the output:

ID Map_Are
12345KK45
657463IIY7
34345FGD
44342DFRE
4646DSAW
424245DSAW
12121DSAW
5753FRDE
575737FRDE
1121FRDE
1121F5FR
646462F5FR
8568F5FR

Here is my simple stored proc. Right know I only can execute with one value at the time but I am trying to run the stored proc with more than one value.

CREATE PROCEDURE TestParam1
@Map varchar(4)
AS
BEGIN
SET NOCOUNT ON;

SELECT ID, Map_Area
FROM TestParamOne
WHERE Map_Area=@Map
END
GO

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved