indexes (non-unique,clustered)

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » indexes (non-unique,clustered)

indexes (non-unique,clustered)

Rate Topic

Display Mode

Topic Options

Author

Message

RamSteve

Posted Wednesday, September 15, 2010 8:12 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 9:53 AM
Points: 144, Visits: 479

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]

Post #986853

Leo.Miller

Posted Wednesday, September 15, 2010 8:33 PM

SSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:44 PM
Points: 442, Visits: 1,300

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

Post #986858

WayneS

Posted Wednesday, September 15, 2010 11:40 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 6,367, Visits: 8,228

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

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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Post #986918

sqlbuddy123

Posted Thursday, September 16, 2010 10:51 AM

Right there with Babe

Group: General Forum Members
Last Login: Wednesday, March 13, 2013 11:49 AM
Points: 715, Visits: 1,523

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

Post #987459

jaco-1011483

Posted Friday, July 06, 2012 2:14 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, July 06, 2012 2:58 AM
Points: 2, Visits: 2

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.

Post #1325892

anthony.green

Posted Friday, July 06, 2012 2:57 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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.

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1325907

jaco-1011483

Posted Friday, July 06, 2012 3:06 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, July 06, 2012 2:58 AM
Points: 2, Visits: 2

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.

Post #1325912

« Prev Topic | Next Topic »

Permissions