SQL Server Performance

Problem facing to improve performance

Discussion in 'T-SQL Performance Tuning for Developers' started by s_niladri, Nov 13, 2007.

  1. s_niladri New Member

    I have created the following table
    CREATE TABLE [dbo].[testclient] (
    [client_id] [bigint] NOT NULL ,
    [one_Code] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Client_Name] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
    My requirement is to display results sorted by client_Name. As there are 1,67,000 records in table applying order by clause degrades the performance slower. So I have created clustered index on client name to avoid order by clause in select statement as clustered index physically arranged data in table based on clustered index column.
    Please see the details below.

    I have created Non-Clustered Primary Key on Client_Id and Clustered Index on Client_Name.
    When I enter data in the above table SQL Server does not physically order data on client name.
    According to me SQL Server physcially sorts data on clustered index column at the time of inserting/updating/deleting data in table. but it does not store data sorted by client_name which is Clustered index. Am i missing something?

    Note: I have created clusterd index on client_name so that i do not need to apply order by clause in select statement but I am surprise to see that client table is not physically sorted by client_name field.
    Can anyone help me out?
  2. Adriaan New Member

    The SQL standard is clear, and there is no shortcut available: you must use an ORDER BY clause to force the order in which the data is returned.
    If you do not specify the ORDER BY clause, then the data will be returned in haphazard order. This may happen to be the actual order that you wanted, but it would be purely by chance, and unlikely to be repeatable over time.
    A clustered index on a character column in a table with more than a million records, with presumably lots of inserts and/or updates and/or deletes, is far from ideal. If the table has an identity column, make that the clustered index, and add a nonclustered index on the client_Name column.

Share This Page