Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

indexes (non-unique,clustered) Expand / Collapse
Author
Message
Posted Wednesday, September 15, 2010 8:12 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 28, 2014 12:06 PM
Points: 163, Visits: 573
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
Posted Wednesday, September 15, 2010 8:33 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, August 7, 2014 1:54 PM
Points: 450, Visits: 1,337
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
Posted Wednesday, September 15, 2010 11:40 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:04 AM
Points: 6,593, Visits: 8,875
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
Posted Thursday, September 16, 2010 10:51 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 11:19 AM
Points: 1,194, Visits: 2,215
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
Posted Friday, July 6, 2012 2:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 6, 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
Posted Friday, July 6, 2012 2:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:20 AM
Points: 5,218, Visits: 5,067
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
Posted Friday, July 6, 2012 3:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 6, 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 »

Add to briefcase

Permissions Expand / Collapse