Showing posts with label structure. Show all posts
Showing posts with label structure. Show all posts

Monday, March 26, 2012

Normalization Question.

I have few doubts whether the following DB structure is good or rubbish.

Suppose you are an organization has many cinemas,Theatres,Venues etc:
Cinemas
Theatres
Venues
etc

and you want to store-retrieve about each one of this item

would you normalize like this

Organization Table
==================
orgID PK
OrgName


CinemaInOrganization
==================
OrgID PK
CinID PK


TheatreInOrganization
==================
OrgID PK
CinID PK

VenueInOrganization
====================
OrgID PK
VenID PK

Do you envisage any problem with querying tables with this structure?

Thanks a lot in advance

Not really, it largely depends on how your systems handle the Theatres, Cinemas and Venues.

Personally they are all types of venues, what commonality of data and function is shared across them all?

|||

Thanks a lot for your quick reply.
That was just a fictious example that strongly reflect a scenario I have to implement.

The commonality among them is that all these Items (Venues,Theatres etc) they all belong to an Organization.

What I wanted to establish is that if you have the following Tables:

Venue VeniD -VenName etc
Theatre ThID -THName etc
Cinema CinID -CinName

and then this organization has a one to many toabove tables should you create Link table for each one of them as I mentioned in the original post?

Thanks again


|||

My confusion.

I would only create link tables where you can have many to many relationships. It really complicates the issue to have those intermediate tables when you don't need them (in my view)

Wednesday, March 21, 2012

NonLogged Bulk copy syntax

SQL 7.0
I need to use nonlogged bcp to copy from a table with 90
millin records to an empty table structure that's
different than the source structure.
Does someone know the syntax?
old table example:
name, char, 30
address,char,30
new table example:
name,char,30
id,decimal,10 this is NULL on the source file structure
address, char,30
thanks,
DonUse select into statement
That is not logged.
You can select * or select fieldlist.
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:54aa01c4c911$c07226b0$a401280a@.phx.gbl...
> SQL 7.0
> I need to use nonlogged bcp to copy from a table with 90
> millin records to an empty table structure that's
> different than the source structure.
> Does someone know the syntax?
> old table example:
> name, char, 30
> address,char,30
> new table example:
> name,char,30
> id,decimal,10 this is NULL on the source file structure
> address, char,30
> thanks,
> Don
>|||Just to be clear you can never have a non-logged operation in SQL Server.
You can get what is called a Minimally Logged operation by doing a Select
Into, BCP or Bulk Insert when the proper conditions are set. This still
logs entries to the transaction log but are minimal compared to the fully
logged operations. In the case of the Select Into you need to be in the
Bulk Logged or Simple recovery mode for this to occur.
Andrew J. Kelly SQL MVP
<news.optonline.net> wrote in message
news:%235btXEVyEHA.3784@.tk2msftngp13.phx.gbl...
> Use select into statement
> That is not logged.
> You can select * or select fieldlist.
>
> "Don" <anonymous@.discussions.microsoft.com> wrote in message
> news:54aa01c4c911$c07226b0$a401280a@.phx.gbl...
>|||To do this I would create a view on the new table
create view vw_newtable
as
select * from newtable
than bcp the data out
master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -N'
then bcp in
master..xp_cmdshell 'bcp mydb..newtbl out c:\newtbl.txt -N'
Note there must be no indexes on the dest table.
For 90 million recs I would consider doing the above but maybe 10 million
recs at a time. Use a query to extract the data with queryout on an indexed
column (or you can use views in the same way)
"Don" wrote:

> SQL 7.0
> I need to use nonlogged bcp to copy from a table with 90
> millin records to an empty table structure that's
> different than the source structure.
> Does someone know the syntax?
> old table example:
> name, char, 30
> address,char,30
> new table example:
> name,char,30
> id,decimal,10 this is NULL on the source file structure
> address, char,30
> thanks,
> Don
>|||I'm using SQL 7.0 and the modes you mention are for SQL
2000.
Don

>--Original Message--
>Just to be clear you can never have a non-logged
operation in SQL Server.
>You can get what is called a Minimally Logged operation
by doing a Select
>Into, BCP or Bulk Insert when the proper conditions are
set. This still
>logs entries to the transaction log but are minimal
compared to the fully
>logged operations. In the case of the Select Into you
need to be in the
>Bulk Logged or Simple recovery mode for this to occur.
>
>--
>Andrew J. Kelly SQL MVP
>
><news.optonline.net> wrote in message
>news:%235btXEVyEHA.3784@.tk2msftngp13.phx.gbl...
message[vbcol=seagreen]
90[vbcol=seagreen]
structure[vbcol=seagreen]
>
>.
>|||I guess I don't follow. for one thing you say bcp in, but
then the command is out.
then i don't see where anything done in the view is used
again in the bcp commands.
thanks,
don

>--Original Message--
>To do this I would create a view on the new table
>create view vw_newtable
>as
>select * from newtable
>than bcp the data out
>master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -
N'
>then bcp in
>master..xp_cmdshell 'bcp mydb..newtbl out c:\newtbl.txt -
N'
>Note there must be no indexes on the dest table.
>For 90 million recs I would consider doing the above but
maybe 10 million
>recs at a time. Use a query to extract the data with
queryout on an indexed
>column (or you can use views in the same way)
>
>
>
>"Don" wrote:
>
90[vbcol=seagreen]
>.
>|||Then make sure you set the Select Into / Bulk Insert mode and it will be the
same.
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:07db01c4c9bb$f22588f0$a501280a@.phx.gbl...[vbcol=seagreen]
> I'm using SQL 7.0 and the modes you mention are for SQL
> 2000.
> Don
>
> operation in SQL Server.
> by doing a Select
> set. This still
> compared to the fully
> need to be in the
> message
> 90
> structure|||doh (deliberate mistake - left to the reader)
create view vw_newtable
as
select * from newtable
then bcp the data out
master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -N'
then bcp in
master..xp_cmdshell 'bcp mydb..vw_newtbl in c:\newtbl.txt -N'
"Don" wrote:

> I guess I don't follow. for one thing you say bcp in, but
> then the command is out.
> then i don't see where anything done in the view is used
> again in the bcp commands.
> thanks,
> don
>
> N'
> N'
> maybe 10 million
> queryout on an indexed
> 90
>

NonLogged Bulk copy syntax

SQL 7.0
I need to use nonlogged bcp to copy from a table with 90
millin records to an empty table structure that's
different than the source structure.
Does someone know the syntax?
old table example:
name, char, 30
address,char,30
new table example:
name,char,30
id,decimal,10 this is NULL on the source file structure
address, char,30
thanks,
DonUse select into statement
That is not logged.
You can select * or select fieldlist.
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:54aa01c4c911$c07226b0$a401280a@.phx.gbl...
> SQL 7.0
> I need to use nonlogged bcp to copy from a table with 90
> millin records to an empty table structure that's
> different than the source structure.
> Does someone know the syntax?
> old table example:
> name, char, 30
> address,char,30
> new table example:
> name,char,30
> id,decimal,10 this is NULL on the source file structure
> address, char,30
> thanks,
> Don
>|||Just to be clear you can never have a non-logged operation in SQL Server.
You can get what is called a Minimally Logged operation by doing a Select
Into, BCP or Bulk Insert when the proper conditions are set. This still
logs entries to the transaction log but are minimal compared to the fully
logged operations. In the case of the Select Into you need to be in the
Bulk Logged or Simple recovery mode for this to occur.
Andrew J. Kelly SQL MVP
<news.optonline.net> wrote in message
news:%235btXEVyEHA.3784@.tk2msftngp13.phx.gbl...
> Use select into statement
> That is not logged.
> You can select * or select fieldlist.
>
> "Don" <anonymous@.discussions.microsoft.com> wrote in message
> news:54aa01c4c911$c07226b0$a401280a@.phx.gbl...
>> SQL 7.0
>> I need to use nonlogged bcp to copy from a table with 90
>> millin records to an empty table structure that's
>> different than the source structure.
>> Does someone know the syntax?
>> old table example:
>> name, char, 30
>> address,char,30
>> new table example:
>> name,char,30
>> id,decimal,10 this is NULL on the source file structure
>> address, char,30
>> thanks,
>> Don
>|||To do this I would create a view on the new table
create view vw_newtable
as
select * from newtable
than bcp the data out
master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -N'
then bcp in
master..xp_cmdshell 'bcp mydb..newtbl out c:\newtbl.txt -N'
Note there must be no indexes on the dest table.
For 90 million recs I would consider doing the above but maybe 10 million
recs at a time. Use a query to extract the data with queryout on an indexed
column (or you can use views in the same way)
"Don" wrote:
> SQL 7.0
> I need to use nonlogged bcp to copy from a table with 90
> millin records to an empty table structure that's
> different than the source structure.
> Does someone know the syntax?
> old table example:
> name, char, 30
> address,char,30
> new table example:
> name,char,30
> id,decimal,10 this is NULL on the source file structure
> address, char,30
> thanks,
> Don
>|||I'm using SQL 7.0 and the modes you mention are for SQL
2000.
Don
>--Original Message--
>Just to be clear you can never have a non-logged
operation in SQL Server.
>You can get what is called a Minimally Logged operation
by doing a Select
>Into, BCP or Bulk Insert when the proper conditions are
set. This still
>logs entries to the transaction log but are minimal
compared to the fully
>logged operations. In the case of the Select Into you
need to be in the
>Bulk Logged or Simple recovery mode for this to occur.
>
>--
>Andrew J. Kelly SQL MVP
>
><news.optonline.net> wrote in message
>news:%235btXEVyEHA.3784@.tk2msftngp13.phx.gbl...
>> Use select into statement
>> That is not logged.
>> You can select * or select fieldlist.
>>
>> "Don" <anonymous@.discussions.microsoft.com> wrote in
message
>> news:54aa01c4c911$c07226b0$a401280a@.phx.gbl...
>> SQL 7.0
>> I need to use nonlogged bcp to copy from a table with
90
>> millin records to an empty table structure that's
>> different than the source structure.
>> Does someone know the syntax?
>> old table example:
>> name, char, 30
>> address,char,30
>> new table example:
>> name,char,30
>> id,decimal,10 this is NULL on the source file
structure
>> address, char,30
>> thanks,
>> Don
>>
>
>.
>|||I guess I don't follow. for one thing you say bcp in, but
then the command is out.
then i don't see where anything done in the view is used
again in the bcp commands.
thanks,
don
>--Original Message--
>To do this I would create a view on the new table
>create view vw_newtable
>as
>select * from newtable
>than bcp the data out
>master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -
N'
>then bcp in
>master..xp_cmdshell 'bcp mydb..newtbl out c:\newtbl.txt -
N'
>Note there must be no indexes on the dest table.
>For 90 million recs I would consider doing the above but
maybe 10 million
>recs at a time. Use a query to extract the data with
queryout on an indexed
>column (or you can use views in the same way)
>
>
>
>"Don" wrote:
>> SQL 7.0
>> I need to use nonlogged bcp to copy from a table with
90
>> millin records to an empty table structure that's
>> different than the source structure.
>> Does someone know the syntax?
>> old table example:
>> name, char, 30
>> address,char,30
>> new table example:
>> name,char,30
>> id,decimal,10 this is NULL on the source file structure
>> address, char,30
>> thanks,
>> Don
>>
>.
>|||Then make sure you set the Select Into / Bulk Insert mode and it will be the
same.
--
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:07db01c4c9bb$f22588f0$a501280a@.phx.gbl...
> I'm using SQL 7.0 and the modes you mention are for SQL
> 2000.
> Don
>>--Original Message--
>>Just to be clear you can never have a non-logged
> operation in SQL Server.
>>You can get what is called a Minimally Logged operation
> by doing a Select
>>Into, BCP or Bulk Insert when the proper conditions are
> set. This still
>>logs entries to the transaction log but are minimal
> compared to the fully
>>logged operations. In the case of the Select Into you
> need to be in the
>>Bulk Logged or Simple recovery mode for this to occur.
>>
>>--
>>Andrew J. Kelly SQL MVP
>>
>><news.optonline.net> wrote in message
>>news:%235btXEVyEHA.3784@.tk2msftngp13.phx.gbl...
>> Use select into statement
>> That is not logged.
>> You can select * or select fieldlist.
>>
>> "Don" <anonymous@.discussions.microsoft.com> wrote in
> message
>> news:54aa01c4c911$c07226b0$a401280a@.phx.gbl...
>> SQL 7.0
>> I need to use nonlogged bcp to copy from a table with
> 90
>> millin records to an empty table structure that's
>> different than the source structure.
>> Does someone know the syntax?
>> old table example:
>> name, char, 30
>> address,char,30
>> new table example:
>> name,char,30
>> id,decimal,10 this is NULL on the source file
> structure
>> address, char,30
>> thanks,
>> Don
>>
>>
>>.|||doh (deliberate mistake - left to the reader)
create view vw_newtable
as
select * from newtable
then bcp the data out
master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -N'
then bcp in
master..xp_cmdshell 'bcp mydb..vw_newtbl in c:\newtbl.txt -N'
"Don" wrote:
> I guess I don't follow. for one thing you say bcp in, but
> then the command is out.
> then i don't see where anything done in the view is used
> again in the bcp commands.
> thanks,
> don
> >--Original Message--
> >To do this I would create a view on the new table
> >create view vw_newtable
> >as
> >select * from newtable
> >
> >than bcp the data out
> >
> >master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -
> N'
> >then bcp in
> >master..xp_cmdshell 'bcp mydb..newtbl out c:\newtbl.txt -
> N'
> >
> >Note there must be no indexes on the dest table.
> >For 90 million recs I would consider doing the above but
> maybe 10 million
> >recs at a time. Use a query to extract the data with
> queryout on an indexed
> >column (or you can use views in the same way)
> >
> >
> >
> >
> >
> >
> >"Don" wrote:
> >
> >> SQL 7.0
> >>
> >> I need to use nonlogged bcp to copy from a table with
> 90
> >> millin records to an empty table structure that's
> >> different than the source structure.
> >> Does someone know the syntax?
> >>
> >> old table example:
> >> name, char, 30
> >> address,char,30
> >>
> >> new table example:
> >> name,char,30
> >> id,decimal,10 this is NULL on the source file structure
> >> address, char,30
> >>
> >> thanks,
> >> Don
> >>
> >>
> >.
> >
>sql

NonLogged Bulk copy syntax

SQL 7.0
I need to use nonlogged bcp to copy from a table with 90
millin records to an empty table structure that's
different than the source structure.
Does someone know the syntax?
old table example:
name, char, 30
address,char,30
new table example:
name,char,30
id,decimal,10 this is NULL on the source file structure
address, char,30
thanks,
Don
Use select into statement
That is not logged.
You can select * or select fieldlist.
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:54aa01c4c911$c07226b0$a401280a@.phx.gbl...
> SQL 7.0
> I need to use nonlogged bcp to copy from a table with 90
> millin records to an empty table structure that's
> different than the source structure.
> Does someone know the syntax?
> old table example:
> name, char, 30
> address,char,30
> new table example:
> name,char,30
> id,decimal,10 this is NULL on the source file structure
> address, char,30
> thanks,
> Don
>
|||Just to be clear you can never have a non-logged operation in SQL Server.
You can get what is called a Minimally Logged operation by doing a Select
Into, BCP or Bulk Insert when the proper conditions are set. This still
logs entries to the transaction log but are minimal compared to the fully
logged operations. In the case of the Select Into you need to be in the
Bulk Logged or Simple recovery mode for this to occur.
Andrew J. Kelly SQL MVP
<news.optonline.net> wrote in message
news:%235btXEVyEHA.3784@.tk2msftngp13.phx.gbl...
> Use select into statement
> That is not logged.
> You can select * or select fieldlist.
>
> "Don" <anonymous@.discussions.microsoft.com> wrote in message
> news:54aa01c4c911$c07226b0$a401280a@.phx.gbl...
>
|||To do this I would create a view on the new table
create view vw_newtable
as
select * from newtable
than bcp the data out
master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -N'
then bcp in
master..xp_cmdshell 'bcp mydb..newtbl out c:\newtbl.txt -N'
Note there must be no indexes on the dest table.
For 90 million recs I would consider doing the above but maybe 10 million
recs at a time. Use a query to extract the data with queryout on an indexed
column (or you can use views in the same way)
"Don" wrote:

> SQL 7.0
> I need to use nonlogged bcp to copy from a table with 90
> millin records to an empty table structure that's
> different than the source structure.
> Does someone know the syntax?
> old table example:
> name, char, 30
> address,char,30
> new table example:
> name,char,30
> id,decimal,10 this is NULL on the source file structure
> address, char,30
> thanks,
> Don
>
|||I'm using SQL 7.0 and the modes you mention are for SQL
2000.
Don

>--Original Message--
>Just to be clear you can never have a non-logged
operation in SQL Server.
>You can get what is called a Minimally Logged operation
by doing a Select
>Into, BCP or Bulk Insert when the proper conditions are
set. This still
>logs entries to the transaction log but are minimal
compared to the fully
>logged operations. In the case of the Select Into you
need to be in the[vbcol=seagreen]
>Bulk Logged or Simple recovery mode for this to occur.
>
>--
>Andrew J. Kelly SQL MVP
>
><news.optonline.net> wrote in message
>news:%235btXEVyEHA.3784@.tk2msftngp13.phx.gbl...
message[vbcol=seagreen]
90[vbcol=seagreen]
structure
>
>.
>
|||I guess I don't follow. for one thing you say bcp in, but
then the command is out.
then i don't see where anything done in the view is used
again in the bcp commands.
thanks,
don

>--Original Message--
>To do this I would create a view on the new table
>create view vw_newtable
>as
>select * from newtable
>than bcp the data out
>master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -
N'
>then bcp in
>master..xp_cmdshell 'bcp mydb..newtbl out c:\newtbl.txt -
N'
>Note there must be no indexes on the dest table.
>For 90 million recs I would consider doing the above but
maybe 10 million
>recs at a time. Use a query to extract the data with
queryout on an indexed[vbcol=seagreen]
>column (or you can use views in the same way)
>
>
>
>"Don" wrote:
90
>.
>
|||Then make sure you set the Select Into / Bulk Insert mode and it will be the
same.
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:07db01c4c9bb$f22588f0$a501280a@.phx.gbl...[vbcol=seagreen]
> I'm using SQL 7.0 and the modes you mention are for SQL
> 2000.
> Don
> operation in SQL Server.
> by doing a Select
> set. This still
> compared to the fully
> need to be in the
> message
> 90
> structure
|||doh (deliberate mistake - left to the reader)
create view vw_newtable
as
select * from newtable
then bcp the data out
master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -N'
then bcp in
master..xp_cmdshell 'bcp mydb..vw_newtbl in c:\newtbl.txt -N'
"Don" wrote:

> I guess I don't follow. for one thing you say bcp in, but
> then the command is out.
> then i don't see where anything done in the view is used
> again in the bcp commands.
> thanks,
> don
> N'
> N'
> maybe 10 million
> queryout on an indexed
> 90
>

Tuesday, March 20, 2012

Non-clustered indexes on 1 separate fileGroup?

Hi Guys,

I am reviewing the storage structure of my growing db.

I am going to create multiple FileGroups and db files.
Most of the filegroups will be used to allow table partitioning and put the heavily used partitions on different storage than the read-only archives. TempDB will also have its own filegroup/storage.

For Indexes, I have a doubt. I am sure I can have all my non-clustered indexes on a separate fileGroup and storage but what about primary keys and/or clustered indexes?

Should I move them to the same filegroup than the non-clustered indexes or should I leave them with the data? What would be best?

I am running a 100GB reporting datamart on SQL2005 enterprise, There are almost no transactions, just a daily and weekly refresh. I will soon have more storage, SAN or NetApp, not sure yet.

Thanks,

Philippe

Hi

Moving your indexes to a seperate filegroup can improve the parallism of some querries that need to work with indexes and then do a bookmark lookup afterwards. If you have a VLDB like you said you should consider doing it, since the indexes cant be kept in memory all the time. So when you access the indexes it will not block the retrieval of your data from the other partition.