Multiple INSERT's INTO temp table w/ Primary Key

Hi all -
I need to create a temp table containing all Items with activity for the
last five years. These records will be coming from a few different tables.
So I have the following . . .
CREATE TABLE #tt_item (item nvarchar(30) PRIMARY KEY)
INSERT INTO #tt_item
SELECT dbo.item.item
FROM Orders
. . . . which I'll have to repeat for each table in question.
My question is, with the PRIMARY KEY on the #tt_item table, how do I check
for the existence of an Item in #tt_item as I'm doing the INSERT?This depends on what you want to do when there is a conflict (Duplicate key
value)...
Do you want to ignore the dupe, (not insertt it)? If so, then
CREATE TABLE #tt_item (item nvarchar(30) PRIMARY KEY)
-- --
INSERT INTO #tt_item(item)
SELECT O.item
FROM Orders O
Where Not Exists (Select * From #tt_item
Where item = O.item)
If otoh, you want to insert them anyway, then you cannot use item as the
primary key of the temp table... You will need to make a composite key
consisting of, say, (Sourcetable, item)
"Michael.Fisher" wrote:

> Hi all -
> I need to create a temp table containing all Items with activity for the
> last five years. These records will be coming from a few different tables
.
> So I have the following . . .
> CREATE TABLE #tt_item (item nvarchar(30) PRIMARY KEY)
> INSERT INTO #tt_item
> SELECT dbo.item.item
> FROM Orders
> . . . . which I'll have to repeat for each table in question.
> My question is, with the PRIMARY KEY on the #tt_item table, how do I check
> for the existence of an Item in #tt_item as I'm doing the INSERT?|||This depends on what you want to do when there is a conflict (Duplicate key
value)...
Do you want to ignore the dupe, (not insertt it)? If so, then
CREATE TABLE #tt_item (item nvarchar(30) PRIMARY KEY)
-- --
INSERT INTO #tt_item(item)
SELECT O.item
FROM Orders O
Where Not Exists (Select * From #tt_item
Where item = O.item)
If otoh, you want to insert them anyway, then you cannot use item as the
primary key of the temp table... You will need to make a composite key
consisting of, say, (Sourcetable, item)
"Michael.Fisher" wrote:

> Hi all -
> I need to create a temp table containing all Items with activity for the
> last five years. These records will be coming from a few different tables
.
> So I have the following . . .
> CREATE TABLE #tt_item (item nvarchar(30) PRIMARY KEY)
> INSERT INTO #tt_item
> SELECT dbo.item.item
> FROM Orders
> . . . . which I'll have to repeat for each table in question.
> My question is, with the PRIMARY KEY on the #tt_item table, how do I check
> for the existence of an Item in #tt_item as I'm doing the INSERT?|||INSERT INTO #tt_item (item)
SELECT O.item
FROM Orders AS O
LEFT JOIN #tt_item AS I
ON O.item = I.item
WHERE I.item IS NULL
OR:
INSERT INTO #tt_item (item)
SELECT item
FROM Orders
UNION
SELECT item
FROM Foo
UNION
SELECT item
FROM Bar
UNION
..
David Portas
SQL Server MVP
--|||Many thanks David, as well as CBretana, for providing these timely, helpful
examples. Looks like any of these will work.
"David Portas" wrote:

> INSERT INTO #tt_item (item)
> SELECT O.item
> FROM Orders AS O
> LEFT JOIN #tt_item AS I
> ON O.item = I.item
> WHERE I.item IS NULL
> OR:
> INSERT INTO #tt_item (item)
> SELECT item
> FROM Orders
> UNION
> SELECT item
> FROM Foo
> UNION
> SELECT item
> FROM Bar
> UNION
> ...
> --
> David Portas
> SQL Server MVP
> --
>

Multiple Foreign Keys

I am having trouble creating multiple foreign keys on a table so that I can set up cascading update and cascading delete from two different primary tables. I am using the diagram to do this but when I try to save the diagram I get the following error.

ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_IndResults_RaceData'. The conflict occurred in database 'VIRA', table 'RaceData', column 'RaceID'.

What would cause this to happen? Is it possible that I have records in the foreign table that do not transfer back to the primary table?

Thanks!Is it possible that I have records in the foreign table that do not transfer back to the primary table?Not just possible, nearly guaranteed.

The best answer is to create a query that will show you the offending rows, then go and fix them. You can often pick a value that makes a reasonable default (sometimes NULL works well, meaning there isn't any relationship). Sometimes you've got to figure out what FK value you need, which can be a lot of work.

-PatP|||Thanks! That was exactly what it was and it is fixed!

Multiple Filegroups.

Hi Everyone,
I added a secondary filegroup to a production database and moved some tables
to it from the Primary Filegroup.In the process I changed the database
recovery model to Simple and back to Full,backed up the Transaction Log and
shrunk it,shrunk the primary filegroup datafile. The database recovery model
is Full at the moment. I have noticed 2 oddities:
1. The Transaction Log appears to grow much slower than before(only 2 - 5 MB
a day,the database is being used as usual)
2. sp_spaceused and Enterprise Manager Console report strange values for
database available space:
db_name size unallocated_space
DB_LIVE 55812.06 MB -4257.63 MB
reserved data index_size unused
61507528 KB 61203424 KB 291632 KB 12472 KB
Do I need to run DBCC UPDATEUSAGE or sp_spaceused @.updateusage = 'TRUE' or
is there somethig else I have to do?
Best Regards.I have another question. I took a Full Backup of the database before adding
the secondary filegroup(single filegroup database) , the backup file size is
about 55,4 GB.
Today I backed up the Primary Filegroup and the newly added Filegroup1. The
backup file size for the Primary filegroup is about 45 GB and backup file
size for the Filegroup1 filegroup is about 6 GB and that adds up to 51 GB.
As far as I know no data has been deleted. Is there any explanation
regarding the cause of the total size reduction ?
Regards.
Sezgin Rafet
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:OnAw0z1aGHA.4520@.TK2MSFTNGP03.phx.gbl...
> Hi Everyone,
> I added a secondary filegroup to a production database and moved some
> tables to it from the Primary Filegroup.In the process I changed the
> database recovery model to Simple and back to Full,backed up the
> Transaction Log and shrunk it,shrunk the primary filegroup datafile. The
> database recovery model is Full at the moment. I have noticed 2 oddities:
> 1. The Transaction Log appears to grow much slower than before(only 2 - 5
> MB a day,the database is being used as usual)
> 2. sp_spaceused and Enterprise Manager Console report strange values for
> database available space:
> db_name size unallocated_space
> DB_LIVE 55812.06 MB -4257.63 MB
> reserved data index_size
> unused
> 61507528 KB 61203424 KB 291632 KB 12472 KB
>
> Do I need to run DBCC UPDATEUSAGE or sp_spaceused @.updateusage = 'TRUE'
> or is there somethig else I have to do?
> Best Regards.
>|||Hi
You don't say how you moved the tables, I assumed that you re-created the
clustered index in the new filegroup, in which case it would have been
re-built. If you had not defragged the index for a while then this may be
where the space has been found. You may want to look at what fill factors yo
u
are using or whether the clustered index is suitable.
John
"Sezgin Rafet" wrote:

> I have another question. I took a Full Backup of the database before addin
g
> the secondary filegroup(single filegroup database) , the backup file size
is
> about 55,4 GB.
> Today I backed up the Primary Filegroup and the newly added Filegroup1. Th
e
> backup file size for the Primary filegroup is about 45 GB and backup file
> size for the Filegroup1 filegroup is about 6 GB and that adds up to 51 GB.
> As far as I know no data has been deleted. Is there any explanation
> regarding the cause of the total size reduction ?
> Regards.
> Sezgin Rafet
>
> "Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
> news:OnAw0z1aGHA.4520@.TK2MSFTNGP03.phx.gbl...
>
>|||Hello John,
Thanks for the reply.
I moved the tables using Enterprise Manager -> Design Table -> Properties ->
Selected Filegroup1 as the Table Filegroup -> Close -> Save
All the tables I moved this way have the same clustered index(the tables
have the same structuce,each table holds 1 month's worth of data) , so I
presume the index was rebuit for each table(my knowledge about indexing is
limited).We don't do anything regarding index defragmentation , thanks to
you it is only now that I realize it is an important issue to us.
In the beginning of each month a new data table is created(programmaticaly
by a custom Windows Service).
Data is fed into the table at very high rate from a SCADA system. Older data
tables are not modified.
Last month's data table has about 70 000 000 rows and is about 6.3 GB in
size.
As each new data table is created I will move the oldest data table to the
secondary filegroup.
I intend to keep 2 data tables in the Primary Filegroup(it is the Default
Filegroup as well).
Clustered Index Fill Factor is 0 % - is this a good choice ?.
What would be the best method to defragment the clustered index ? (This may
be no longer necessary , because when moving the tables it will be rebilt.
Any data table will be having data changes for only a period of 1 month)
What would be the advantage of using a non-clustered index in our case ,
would the query performance loss be acceptable ?
Best Regards.
Sezgin
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:86C16903-A902-4706-951C-D5E25E7C68C2@.microsoft.com...[vbcol=seagreen]
> Hi
> You don't say how you moved the tables, I assumed that you re-created the
> clustered index in the new filegroup, in which case it would have been
> re-built. If you had not defragged the index for a while then this may be
> where the space has been found. You may want to look at what fill factors
> you
> are using or whether the clustered index is suitable.
> John
> "Sezgin Rafet" wrote:
>|||Hi
I don't know what commands EM uses to move tables, but at a guess it creates
a new table, sucks the data into from the old table and then renames the new
table after dropping it. This would mean that any clustered index would need
to be in the same filegroup. Without knowing what your indexes are it is har
d
to comment on whether the fill factor is good or whether you would benefit
from a different from a different clustered index or even if your system
would work better without one (if speed of inserts is more important than
speed of retrieval).
You may want to consider changing your processing to load into a "standard"
set of tables, then do a monthly archive process into a new table (which is
basically what I think EM does for you). This could be automated and you the
n
have the choice of having different indexes on the load table to the archive
d
data; you would also be able to build the indexes on the archived data at th
e
end of the load process so that they would be fully optimized and if no data
changes are made they could have 100% fill factor. If this data is rarely
accessed you may even consider archiving it to a separate database.
For defragging look at the topic DBCC SHOWCONTIG in Books Online and there
is an example script you can use as a starting point for any manual
maintenance task. You can use DBCC DBREINDEX instead of DBCC INDEXDEFRAG if
you want a defragment the whole index. Alternatively look at the database
maintenance wizard and set up a job which will create a maintenance plan for
your database(s).
HTH
John
"Sezgin Rafet" wrote:

> Hello John,
> Thanks for the reply.
> I moved the tables using Enterprise Manager -> Design Table -> Properties
->
> Selected Filegroup1 as the Table Filegroup -> Close -> Save
> All the tables I moved this way have the same clustered index(the tables
> have the same structuce,each table holds 1 month's worth of data) , so I
> presume the index was rebuit for each table(my knowledge about indexing is
> limited).We don't do anything regarding index defragmentation , thanks to
> you it is only now that I realize it is an important issue to us.
> In the beginning of each month a new data table is created(programmaticaly
> by a custom Windows Service).
> Data is fed into the table at very high rate from a SCADA system. Older da
ta
> tables are not modified.
> Last month's data table has about 70 000 000 rows and is about 6.3 GB in
> size.
> As each new data table is created I will move the oldest data table to the
> secondary filegroup.
> I intend to keep 2 data tables in the Primary Filegroup(it is the Default
> Filegroup as well).
> Clustered Index Fill Factor is 0 % - is this a good choice ?.
> What would be the best method to defragment the clustered index ? (This ma
y
> be no longer necessary , because when moving the tables it will be rebilt.
> Any data table will be having data changes for only a period of 1 month)
> What would be the advantage of using a non-clustered index in our case ,
> would the query performance loss be acceptable ?
> Best Regards.
>
> Sezgin
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:86C16903-A902-4706-951C-D5E25E7C68C2@.microsoft.com...
>
>

Multiple Filegroups.

Hi Everyone,
I added a secondary filegroup to a production database and moved some tables
to it from the Primary Filegroup.In the process I changed the database
recovery model to Simple and back to Full,backed up the Transaction Log and
shrunk it,shrunk the primary filegroup datafile. The database recovery model
is Full at the moment. I have noticed 2 oddities:
1. The Transaction Log appears to grow much slower than before(only 2 - 5 MB
a day,the database is being used as usual)
2. sp_spaceused and Enterprise Manager Console report strange values for
database available space:
db_name size unallocated_space
DB_LIVE 55812.06 MB -4257.63 MB
reserved data index_size unused
61507528 KB 61203424 KB 291632 KB 12472 KB
Do I need to run DBCC UPDATEUSAGE or sp_spaceused @.updateusage = 'TRUE' or
is there somethig else I have to do?
Best Regards.I have another question. I took a Full Backup of the database before adding
the secondary filegroup(single filegroup database) , the backup file size is
about 55,4 GB.
Today I backed up the Primary Filegroup and the newly added Filegroup1. The
backup file size for the Primary filegroup is about 45 GB and backup file
size for the Filegroup1 filegroup is about 6 GB and that adds up to 51 GB.
As far as I know no data has been deleted. Is there any explanation
regarding the cause of the total size reduction ?
Regards.
Sezgin Rafet
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:OnAw0z1aGHA.4520@.TK2MSFTNGP03.phx.gbl...
> Hi Everyone,
> I added a secondary filegroup to a production database and moved some
> tables to it from the Primary Filegroup.In the process I changed the
> database recovery model to Simple and back to Full,backed up the
> Transaction Log and shrunk it,shrunk the primary filegroup datafile. The
> database recovery model is Full at the moment. I have noticed 2 oddities:
> 1. The Transaction Log appears to grow much slower than before(only 2 - 5
> MB a day,the database is being used as usual)
> 2. sp_spaceused and Enterprise Manager Console report strange values for
> database available space:
> db_name size unallocated_space
> DB_LIVE 55812.06 MB -4257.63 MB
> reserved data index_size
> unused
> 61507528 KB 61203424 KB 291632 KB 12472 KB
>
> Do I need to run DBCC UPDATEUSAGE or sp_spaceused @.updateusage = 'TRUE'
> or is there somethig else I have to do?
> Best Regards.
>|||Hi
You don't say how you moved the tables, I assumed that you re-created the
clustered index in the new filegroup, in which case it would have been
re-built. If you had not defragged the index for a while then this may be
where the space has been found. You may want to look at what fill factors you
are using or whether the clustered index is suitable.
John
"Sezgin Rafet" wrote:
> I have another question. I took a Full Backup of the database before adding
> the secondary filegroup(single filegroup database) , the backup file size is
> about 55,4 GB.
> Today I backed up the Primary Filegroup and the newly added Filegroup1. The
> backup file size for the Primary filegroup is about 45 GB and backup file
> size for the Filegroup1 filegroup is about 6 GB and that adds up to 51 GB.
> As far as I know no data has been deleted. Is there any explanation
> regarding the cause of the total size reduction ?
> Regards.
> Sezgin Rafet
>
> "Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
> news:OnAw0z1aGHA.4520@.TK2MSFTNGP03.phx.gbl...
> > Hi Everyone,
> >
> > I added a secondary filegroup to a production database and moved some
> > tables to it from the Primary Filegroup.In the process I changed the
> > database recovery model to Simple and back to Full,backed up the
> > Transaction Log and shrunk it,shrunk the primary filegroup datafile. The
> > database recovery model is Full at the moment. I have noticed 2 oddities:
> >
> > 1. The Transaction Log appears to grow much slower than before(only 2 - 5
> > MB a day,the database is being used as usual)
> >
> > 2. sp_spaceused and Enterprise Manager Console report strange values for
> > database available space:
> >
> > db_name size unallocated_space
> > DB_LIVE 55812.06 MB -4257.63 MB
> >
> > reserved data index_size
> > unused
> > 61507528 KB 61203424 KB 291632 KB 12472 KB
> >
> >
> > Do I need to run DBCC UPDATEUSAGE or sp_spaceused @.updateusage = 'TRUE'
> > or is there somethig else I have to do?
> >
> > Best Regards.
> >
> >
>
>|||Hello John,
Thanks for the reply.
I moved the tables using Enterprise Manager -> Design Table -> Properties ->
Selected Filegroup1 as the Table Filegroup -> Close -> Save
All the tables I moved this way have the same clustered index(the tables
have the same structuce,each table holds 1 month's worth of data) , so I
presume the index was rebuit for each table(my knowledge about indexing is
limited).We don't do anything regarding index defragmentation , thanks to
you it is only now that I realize it is an important issue to us.
In the beginning of each month a new data table is created(programmaticaly
by a custom Windows Service).
Data is fed into the table at very high rate from a SCADA system. Older data
tables are not modified.
Last month's data table has about 70 000 000 rows and is about 6.3 GB in
size.
As each new data table is created I will move the oldest data table to the
secondary filegroup.
I intend to keep 2 data tables in the Primary Filegroup(it is the Default
Filegroup as well).
Clustered Index Fill Factor is 0 % - is this a good choice ?.
What would be the best method to defragment the clustered index ? (This may
be no longer necessary , because when moving the tables it will be rebilt.
Any data table will be having data changes for only a period of 1 month)
What would be the advantage of using a non-clustered index in our case ,
would the query performance loss be acceptable ?
Best Regards.
Sezgin
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:86C16903-A902-4706-951C-D5E25E7C68C2@.microsoft.com...
> Hi
> You don't say how you moved the tables, I assumed that you re-created the
> clustered index in the new filegroup, in which case it would have been
> re-built. If you had not defragged the index for a while then this may be
> where the space has been found. You may want to look at what fill factors
> you
> are using or whether the clustered index is suitable.
> John
> "Sezgin Rafet" wrote:
>> I have another question. I took a Full Backup of the database before
>> adding
>> the secondary filegroup(single filegroup database) , the backup file size
>> is
>> about 55,4 GB.
>> Today I backed up the Primary Filegroup and the newly added Filegroup1.
>> The
>> backup file size for the Primary filegroup is about 45 GB and backup file
>> size for the Filegroup1 filegroup is about 6 GB and that adds up to 51
>> GB.
>> As far as I know no data has been deleted. Is there any explanation
>> regarding the cause of the total size reduction ?
>> Regards.
>> Sezgin Rafet
>>
>> "Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
>> news:OnAw0z1aGHA.4520@.TK2MSFTNGP03.phx.gbl...
>> > Hi Everyone,
>> >
>> > I added a secondary filegroup to a production database and moved some
>> > tables to it from the Primary Filegroup.In the process I changed the
>> > database recovery model to Simple and back to Full,backed up the
>> > Transaction Log and shrunk it,shrunk the primary filegroup datafile.
>> > The
>> > database recovery model is Full at the moment. I have noticed 2
>> > oddities:
>> >
>> > 1. The Transaction Log appears to grow much slower than before(only 2 -
>> > 5
>> > MB a day,the database is being used as usual)
>> >
>> > 2. sp_spaceused and Enterprise Manager Console report strange values
>> > for
>> > database available space:
>> >
>> > db_name size unallocated_space
>> > DB_LIVE 55812.06 MB -4257.63 MB
>> >
>> > reserved data index_size
>> > unused
>> > 61507528 KB 61203424 KB 291632 KB 12472 KB
>> >
>> >
>> > Do I need to run DBCC UPDATEUSAGE or sp_spaceused @.updateusage = 'TRUE'
>> > or is there somethig else I have to do?
>> >
>> > Best Regards.
>> >
>> >
>>|||Hi
I don't know what commands EM uses to move tables, but at a guess it creates
a new table, sucks the data into from the old table and then renames the new
table after dropping it. This would mean that any clustered index would need
to be in the same filegroup. Without knowing what your indexes are it is hard
to comment on whether the fill factor is good or whether you would benefit
from a different from a different clustered index or even if your system
would work better without one (if speed of inserts is more important than
speed of retrieval).
You may want to consider changing your processing to load into a "standard"
set of tables, then do a monthly archive process into a new table (which is
basically what I think EM does for you). This could be automated and you then
have the choice of having different indexes on the load table to the archived
data; you would also be able to build the indexes on the archived data at the
end of the load process so that they would be fully optimized and if no data
changes are made they could have 100% fill factor. If this data is rarely
accessed you may even consider archiving it to a separate database.
For defragging look at the topic DBCC SHOWCONTIG in Books Online and there
is an example script you can use as a starting point for any manual
maintenance task. You can use DBCC DBREINDEX instead of DBCC INDEXDEFRAG if
you want a defragment the whole index. Alternatively look at the database
maintenance wizard and set up a job which will create a maintenance plan for
your database(s).
HTH
John
"Sezgin Rafet" wrote:
> Hello John,
> Thanks for the reply.
> I moved the tables using Enterprise Manager -> Design Table -> Properties ->
> Selected Filegroup1 as the Table Filegroup -> Close -> Save
> All the tables I moved this way have the same clustered index(the tables
> have the same structuce,each table holds 1 month's worth of data) , so I
> presume the index was rebuit for each table(my knowledge about indexing is
> limited).We don't do anything regarding index defragmentation , thanks to
> you it is only now that I realize it is an important issue to us.
> In the beginning of each month a new data table is created(programmaticaly
> by a custom Windows Service).
> Data is fed into the table at very high rate from a SCADA system. Older data
> tables are not modified.
> Last month's data table has about 70 000 000 rows and is about 6.3 GB in
> size.
> As each new data table is created I will move the oldest data table to the
> secondary filegroup.
> I intend to keep 2 data tables in the Primary Filegroup(it is the Default
> Filegroup as well).
> Clustered Index Fill Factor is 0 % - is this a good choice ?.
> What would be the best method to defragment the clustered index ? (This may
> be no longer necessary , because when moving the tables it will be rebilt.
> Any data table will be having data changes for only a period of 1 month)
> What would be the advantage of using a non-clustered index in our case ,
> would the query performance loss be acceptable ?
> Best Regards.
>
> Sezgin
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:86C16903-A902-4706-951C-D5E25E7C68C2@.microsoft.com...
> > Hi
> >
> > You don't say how you moved the tables, I assumed that you re-created the
> > clustered index in the new filegroup, in which case it would have been
> > re-built. If you had not defragged the index for a while then this may be
> > where the space has been found. You may want to look at what fill factors
> > you
> > are using or whether the clustered index is suitable.
> >
> > John
> >
> > "Sezgin Rafet" wrote:
> >
> >> I have another question. I took a Full Backup of the database before
> >> adding
> >> the secondary filegroup(single filegroup database) , the backup file size
> >> is
> >> about 55,4 GB.
> >> Today I backed up the Primary Filegroup and the newly added Filegroup1.
> >> The
> >> backup file size for the Primary filegroup is about 45 GB and backup file
> >> size for the Filegroup1 filegroup is about 6 GB and that adds up to 51
> >> GB.
> >> As far as I know no data has been deleted. Is there any explanation
> >> regarding the cause of the total size reduction ?
> >>
> >> Regards.
> >>
> >> Sezgin Rafet
> >>
> >>
> >>
> >> "Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
> >> news:OnAw0z1aGHA.4520@.TK2MSFTNGP03.phx.gbl...
> >> > Hi Everyone,
> >> >
> >> > I added a secondary filegroup to a production database and moved some
> >> > tables to it from the Primary Filegroup.In the process I changed the
> >> > database recovery model to Simple and back to Full,backed up the
> >> > Transaction Log and shrunk it,shrunk the primary filegroup datafile.
> >> > The
> >> > database recovery model is Full at the moment. I have noticed 2
> >> > oddities:
> >> >
> >> > 1. The Transaction Log appears to grow much slower than before(only 2 -
> >> > 5
> >> > MB a day,the database is being used as usual)
> >> >
> >> > 2. sp_spaceused and Enterprise Manager Console report strange values
> >> > for
> >> > database available space:
> >> >
> >> > db_name size unallocated_space
> >> > DB_LIVE 55812.06 MB -4257.63 MB
> >> >
> >> > reserved data index_size
> >> > unused
> >> > 61507528 KB 61203424 KB 291632 KB 12472 KB
> >> >
> >> >
> >> > Do I need to run DBCC UPDATEUSAGE or sp_spaceused @.updateusage = 'TRUE'
> >> > or is there somethig else I have to do?
> >> >
> >> > Best Regards.
> >> >
> >> >
> >>
> >>
> >>
>
>

Multiple Databases/One SQL Server instance

We would like to put several production databases (approx. 8) on one
sql server instance.
Each database has 3 filegroups(primary, index, log), what is the best
way to set up these files on the server? We will have 3 or 4 drives.
Is it best to spread the log files across the different drives, or keep
the log files all on one drive etc?
Hi
What do you mean by 3 or 4 drives? If these are physically separate drives
then if you want resilience then you would probably be best of mirroring
them, therefore leaving you with two pairs of which log files can go on one
pair and data files can go on the other pair. If you want more resilience
then you would need a minimum of 3 discs for Raid 5 and 4 discs for Raid 10.
You may want to read more on raid such as at
http://msdn2.microsoft.com/en-us/library/ms190764.aspx and
http://www.acnc.com/04_01_10.html
As the type of I/O for log files is different to that of the data files you
are best keeping them separate. If you subsequently have I/O problems then
you will need to analyse what is causing this, and possibly move one or more
filegroups and their data files onto their own set of discs.
John
"Jayme" wrote:

> We would like to put several production databases (approx. 8) on one
> sql server instance.
> Each database has 3 filegroups(primary, index, log), what is the best
> way to set up these files on the server? We will have 3 or 4 drives.
> Is it best to spread the log files across the different drives, or keep
> the log files all on one drive etc?
>

Multiple Databases/One SQL Server instance

We would like to put several production databases (approx. 8) on one
sql server instance.
Each database has 3 filegroups(primary, index, log), what is the best
way to set up these files on the server? We will have 3 or 4 drives.
Is it best to spread the log files across the different drives, or keep
the log files all on one drive etc?Hi
What do you mean by 3 or 4 drives? If these are physically separate drives
then if you want resilience then you would probably be best of mirroring
them, therefore leaving you with two pairs of which log files can go on one
pair and data files can go on the other pair. If you want more resilience
then you would need a minimum of 3 discs for Raid 5 and 4 discs for Raid 10.
You may want to read more on raid such as at
http://msdn2.microsoft.com/en-us/library/ms190764.aspx and
http://www.acnc.com/04_01_10.html
As the type of I/O for log files is different to that of the data files you
are best keeping them separate. If you subsequently have I/O problems then
you will need to analyse what is causing this, and possibly move one or more
filegroups and their data files onto their own set of discs.
John
"Jayme" wrote:
> We would like to put several production databases (approx. 8) on one
> sql server instance.
> Each database has 3 filegroups(primary, index, log), what is the best
> way to set up these files on the server? We will have 3 or 4 drives.
> Is it best to spread the log files across the different drives, or keep
> the log files all on one drive etc?
>

Multiple Databases/One SQL Server instance

We would like to put several production databases (approx. 8) on one
sql server instance.
Each database has 3 filegroups(primary, index, log), what is the best
way to set up these files on the server? We will have 3 or 4 drives.
Is it best to spread the log files across the different drives, or keep
the log files all on one drive etc?Hi
What do you mean by 3 or 4 drives? If these are physically separate drives
then if you want resilience then you would probably be best of mirroring
them, therefore leaving you with two pairs of which log files can go on one
pair and data files can go on the other pair. If you want more resilience
then you would need a minimum of 3 discs for Raid 5 and 4 discs for Raid 10.
You may want to read more on raid such as at
http://msdn2.microsoft.com/en-us/library/ms190764.aspx and
http://www.acnc.com/04_01_10.html
As the type of I/O for log files is different to that of the data files you
are best keeping them separate. If you subsequently have I/O problems then
you will need to analyse what is causing this, and possibly move one or more
filegroups and their data files onto their own set of discs.
John
"Jayme" wrote:

> We would like to put several production databases (approx. 8) on one
> sql server instance.
> Each database has 3 filegroups(primary, index, log), what is the best
> way to set up these files on the server? We will have 3 or 4 drives.
> Is it best to spread the log files across the different drives, or keep
> the log files all on one drive etc?
>

Multiple column primary key

Hi,
I'd like to know what is a good reason to use a primary key on more
than one field. In a many-to-many relationship, where I have these
tables :
ITEMS
item_id (pk)
...
...
PROPS
prop_id (pk)
...
...
ITEMS_PROPS
item_id
prop_id
For the table ITEMS_PROPS, is it a good idea to use a primary key on
both fields? Or is it better not to have any primary key at all?
Thanks a lotIt the table is truly many-to-many, then you must include both fields in the
primary key unless you create a surrogate key (which I do not recommend for
this situation). I would strongly discourage not having a primary key on
the table. In fact, for this situation, I can't imagine why you wouldn't
want one.
Regards,
John Opincar
"ibiza" <lambertb@.gmail.com> wrote in message
news:1154876795.909121.193220@.m73g2000cwd.googlegroups.com...
> Hi,
> I'd like to know what is a good reason to use a primary key on more
> than one field. In a many-to-many relationship, where I have these
> tables :
> ITEMS
> item_id (pk)
> ...
> ...
> PROPS
> prop_id (pk)
> ...
> ...
> ITEMS_PROPS
> item_id
> prop_id
> For the table ITEMS_PROPS, is it a good idea to use a primary key on
> both fields? Or is it better not to have any primary key at all?
> Thanks a lot
>

Multiple column primary key

Hi,
I'd like to know what is a good reason to use a primary key on more
than one field. In a many-to-many relationship, where I have these
tables :
ITEMS
item_id (pk)
...
...
PROPS
prop_id (pk)
...
...
ITEMS_PROPS
item_id
prop_id
For the table ITEMS_PROPS, is it a good idea to use a primary key on
both fields? Or is it better not to have any primary key at all?
Thanks a lotIt the table is truly many-to-many, then you must include both fields in the
primary key unless you create a surrogate key (which I do not recommend for
this situation). I would strongly discourage not having a primary key on
the table. In fact, for this situation, I can't imagine why you wouldn't
want one.
Regards,
John Opincar
"ibiza" <lambertb@.gmail.com> wrote in message
news:1154876795.909121.193220@.m73g2000cwd.googlegroups.com...
> Hi,
> I'd like to know what is a good reason to use a primary key on more
> than one field. In a many-to-many relationship, where I have these
> tables :
> ITEMS
> item_id (pk)
> ...
> ...
> PROPS
> prop_id (pk)
> ...
> ...
> ITEMS_PROPS
> item_id
> prop_id
> For the table ITEMS_PROPS, is it a good idea to use a primary key on
> both fields? Or is it better not to have any primary key at all?
> Thanks a lot
>