SQL Server Performance

Primary key as clustered.?

Discussion in 'General Developer Questions' started by nitingulati7, May 18, 2005.

  1. nitingulati7 New Member

    Hi,

    Just two small questions.. regarding the following

    CREATE TABLE [dbo].[CF] (
    [CFileId] [varchar] (256) NOT NULL ,
    [id] [int] NOT NULL ,
    [Data] [ntext] NULL ,
    CONSTRAINT [PK_CF] PRIMARY KEY CLUSTERED
    (
    [CFileId]
    ) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


    1. I would like to understand if we have any advantage of having a constraint on a primany key as CLUSTERED.

    2. [PRIMARY] TEXTIMAGE_ON [PRIMARY] what is the advantage that we get if we specify the file group.


    Thanks
    Nitin
    nitingulati7, May 18, 2005
    #2

  2. Madhivanan Moderator

    From Books On line


    quote:
    A clustered index is particularly efficient on columns that are often searched for ranges of values. After the row with the first value is found using the clustered index, rows with subsequent indexed values are guaranteed to be physically adjacent. For example, if an application frequently executes a query to retrieve records between a range of dates, a clustered index can quickly locate the row containing the beginning date, and then retrieve all adjacent rows in the table until the last date is reached. This can help increase the performance of this type of query. Also, if there is a column(s) that is used frequently to sort the data retrieved from a table, it can be advantageous to cluster (physically sort) the table on that column(s) to save the cost of a sort each time the column(s) is queried.



    Madhivanan

    Failing to plan is Planning to fail
    Madhivanan, May 18, 2005
    #2

  3. dineshasanka Moderator

    quote:Originally posted by nitingulati7

    Hi,
    2. [PRIMARY] TEXTIMAGE_ON [PRIMARY] what is the advantage that we get if we specify the file group.



    Anwer to the second question

    TEXTIMAGE_ON indicats that the text, ntext, and image columns are stored on the specified filegroup. If TEXTIMAGE_ON is not specified, the text, ntext, and image columns are stored in the same filegroup as the table.
    So there will be definite advantage of specifing TEXTIMAGE_ON
    dineshasanka, May 19, 2005
    #2

  4. nitingulati7 New Member

    Ok so for the answer to the first. Will i have any advantage if the datatype of the clustered column is String (varchar)

    for Second: where is TEXTIMAGE_ON file group? is it stored in Database?

    nitingulati7, May 19, 2005
    #2

  5. satya Moderator

    satya, May 19, 2005
    #2

  6. dineshasanka Moderator

    Go to the database properties and select filegroup
    u can add new files group say (SS) and
    when creating the table you can give
    CREATE TABLE [dbo].[CF] (
    [CFileId] [varchar] (256) NOT NULL ,
    [id] [int] NOT NULL ,
    [Data] [ntext] NULL ,
    CONSTRAINT [PK_CF] PRIMARY KEY CLUSTERED
    (
    [CFileId]
    ) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [SS]

    More Info
    For a heavily accessed table with text/image columns, place this
    table in one filegroup and place text/image columns in a different
    filegroup on different physical disks.
    You can use a CREATE TABLE statement with TEXTIMAGE_ON keyword to
    place text/image columns in a different filegroup.
    See SQL BOL for details.


    quote:Originally posted by nitingulati7

    for Second: where is TEXTIMAGE_ON file group? is it stored in Database?


    dineshasanka, May 19, 2005
    #2

  7. dineshasanka Moderator

    dineshasanka, May 19, 2005
    #2

Share This Page