question

Mehdi593 avatar image
Mehdi593 asked

clustered index

In my business there is One table with this structure :

    CREATE TABLE Tbl(
	[Month] [INT] NOT NULL,
	[Region] [CHAR](1) NOT NULL,
	[ID] [BIGINT] NULL,
	[LastName] [NVARCHAR](100) NOT NULL,
	[FirstName] [NVARCHAR](50) NULL,
	[Address] [NVARCHAR](100) NOT NULL,
	[Path] [CHAR](8) NOT NULL,
	[LastUpdate] [NUMERIC](6, 0) NOT NULL,
	[PostCode] [CHAR](10) NULL,
	[NationalCode] [CHAR](10) NULL,
	[Tel] [VARCHAR](12) NULL,
	[cl1] [SMALLINT] NULL,
	[cl2] [NUMERIC](12, 0) NULL,
	[Date] [NUMERIC](6, 0) NULL,
	[Time] [CHAR](6) NULL,
    [City] SMALLINT NOT NULL)

all Queries will written such as following:

select LastName,FirstName,Address,... from tbl where City = Value1 and region = Value4 and Month = vlaue3 and Path between value8 and value9

the combination of id and Month will are Unique for create primary key.

how can i choose clustered index on table?

how can i create nonclustered index on table for above query?

index
4 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image JohnM commented ·
Is ID an incremental value such as an identity column?
0 Likes 0 ·
Mehdi593 avatar image Mehdi593 commented ·
Thanks! No! each person have an ID. that is not incremental. information of Persons every month will have been received.
0 Likes 0 ·
Oleg avatar image Oleg commented ·
If ID is not incremental then it will cause high fragmentation, and therefore, it does not make sense to cluster the table by ID and month, but if month is ever-increasing then it might be OK to cluster by month and ID (in this order), it all depends on whether it is possible to sort the newly coming IDs to be sorted prior to inserting.
0 Likes 0 ·
Mehdi593 avatar image Mehdi593 commented ·
select LastName,FirstName,Address,... from tbl where City = Value1 and region = Value4 and Month = vlaue3 and Path between value8 and value9 how can i create nonclustered index on table for above query?
0 Likes 0 ·

1 Answer

·
WRBI avatar image
WRBI answered
Hi Mehdi, There is a good series on the main SQL Server Central website called [Stairway to SQL Server Indexes][1]. I'd highly recommend working your way through it to give yourself a good grounding in indexes. You'll then be able to apply in indexes however/whenever you want to. [1]: http://www.sqlservercentral.com/stairway/72399/
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mehdi593 avatar image Mehdi593 commented ·
Hi ! Thanks !
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.