indexes (non-unique,clustered)

Question

Post reply

indexes (non-unique,clustered)

Steve Dell

SSCarpal Tunnel

Points: 4623
More actions
September 15, 2010 at 8:12 pm

#141931

Hi I have a Table (Below) where i need to create indexes (non-unique,clustered) on columns job_id,property_id can some one help me in doing so ,Thanks in advance 🙂
CREATE TABLE [dbo].[Valuation_Common](
[Valuation_common_id] [int] NOT NULL,
[job_id] [int] NOT NULL,
[property_id] [varchar](20) NULL,
[external_value_id] [int] NULL,
[internal_value_id] [int] NULL,
[external_value_date] [datetime] NULL,
[external_value_quick_sale] [money] NULL,
[external_value_normal] [money] NULL,
[external_value_repaired] [money] NULL,
[estimated_repair_amount] [money] NULL,
[mkt_time] [int] NULL,
[order_date] [datetime] NULL,
[order_uid] [varchar](20) NULL,
[due_date] [datetime] NULL,
[receive_date] [datetime] NULL,
[fee] [money] NULL,
[property_condition] [int] NULL,
[footage] [int] NULL,
[age] [int] NULL,
[valuation_type] [varchar](5) NULL,
[Stated_Val] [money] NULL,
[ext_val_delay_code] [varchar](5) NULL,
[external_prop_class] [varchar](2) NULL,
PRIMARY KEY CLUSTERED
(
[Valuation_common_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

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

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

Leo.Miller SSChampion Points: 13051 More actions · Answer 1

I recommend you do a little reading in Books On Line, or Google this as there are a lot of examples on how to do this.

Cheers

Leo

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

WayneS SSC Guru Points: 95460 More actions · Answer 2

I think that this link will tell you pretty much everything you need to know to create indexes.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 3

Please try this. This is just a basic definition

Use <Db_name>

Go

CREATE CLUSTERED INDEX CIX_Job_Id_Property_id

ON [dbo].[Valuation_Common]( job_id, property_id);

GO

Thank You,

Best Regards,

SQLBuddy

jaco-1011483 SSC Rookie Points: 46 More actions · Answer 4

I find this absolutely amazing that people will tell you on a forum, that

you should read the online books, I fully agree that peeps should put little

effort in in solving their issues but if they went throught the trouble of posting a question here:

a. try answer it, b. link it to an answer that helped you, but dont give comment as to read the books. PLEASE.

Ant-Green SSC Guru Points: 113507 More actions · Answer 5

You wont be able to create a clustered index on them two columns as your primary key is already clustered.

You would first need to drop your primary key and re-create it as a nonclustered primary key which will then allow you to create a clustered index on the other two fields.

@jaco-1011483

While I totally appreciate your comments, but how is us giving the OP the solution helping the OP learn the trade?

If the OP had come and said,

Hi I have read such and such a page in BOL but I cant seem to figure it out please could you help me with X Y Z

Then the OP might of got a different responce, there are plenty of good articles on this site and by using a search engine of your choice, all we ask is that you help us to help you, we will point you to BOL for the basics like this as its better to do it yourself than for us to give you a solution which you maynot understand, in which case how does that help the OP or us.

jaco-1011483 SSC Rookie Points: 46 More actions · Answer 6

I am not disputing that. I agree that people have to learn the trade and pointing them to that is great. I was mearly stating that most developers will post a question when really stuck, and wording I found is mostly the problem. The forums is absolutely fantastic, I was just reading that and I thought wow, if you look at the other two posts it was actually to a link and the other some sort of useful syntax layout. Anyways don't wanna get tied up in this, was more a personal comment than anything else.