Post reply

sql question

  • June 25, 2004 at 9:12 am

    #162170

    CREATE TABLE [dbo].[emp_details] (

     [emp_number] [int] NOT NULL ,

     [emp_id] [int] NOT NULL ,

     [comp_id] [int] NOT NULL

    &nbsp ON [PRIMARY]

    GO

     

    In the above create table command what does primary stands for.

    Thanks.

  • June 25, 2004 at 9:18 am

    #511981

    This is the filegroups that this table belongs to.

  • June 28, 2004 at 1:55 pm

    #512268

    I was wondering about the same thing. What's a filegroup? And why is there more than one?

    Thanks

  • June 28, 2004 at 2:10 pm

    #512276

    There a lot of reasons to have multiple filegroups, but I will make it short and sweet.  If you have a lot of data in a single table, you can put it on a seperate hard drive, or file group.  Thats the main concept,  a file group can be on a different drive, e.g. physical spindle.  Its the same concept as a log file.  You place the log on a seperate drive so you can write to drive and maintain the log on the other.  You can do the same it large tables, indexes, and log files.

    Thats as short a response as I could think of.

  • July 2, 2004 at 5:06 am

    #513113

    Filegroup is quite close to the concept of tablespace in Oracle. I prefer to use filegroups to separate tables that don't change often from those that do. And I place them on different drives to limit fragmentation to each filegroup. More importantly, please correct me if I'm wrong, it is useful to store indexes in a filegroup of its own and place it on its own drive(s). I believe it improves performance because SQl can then scan the index and retrieve data from two drives. The performance gain in Oracle is huge. I hope someone could confirm or reject my theory. Primary filegroup should be used for SQL default databases (master, msdb etc).

  • July 2, 2004 at 7:04 am

    #513142

    Yes, the index things is correct. You can see a noticable performance increase as long as the drive is on either a seprate controller or if the same controller but a different channel. Also sometimes helps to put any text columns in a table on a seperate filegroup.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply