Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
anti-rich
Starting Member
14 Posts |
 Posted - 2007-05-14 : 21:53:46
|
| hi everyone,could somebody please explain the benefits of indexes and data partitioning on sql server 2005, the only articles i can find deal with sql2k or the betas of 2k5.i have a table of roughly 80k rows, would indexes/partitioning help performance greatly if i indexed them (unique or non-unique) or partitioned them?i have never dealt with this advanced stuff of sql2k5 before, only the basic select update insert etc...thanksadam |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-14 : 22:55:18
|
| 80k rows are not that big, I'll not partition it. But will create index based on queries, benefits of index are same on all rdbms no matter which version you use. |
 |
|
|
anti-rich
Starting Member
14 Posts |
Posted - 2007-05-15 : 01:13:03
|
| do you have to have a unique identifier (ie primary key)to create an index? cheers for the feedbackadam |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-05-15 : 02:05:51
|
you do not *need* to have a primary key to create an index. however a table without a primary key is usually a no-no. how about you post the DDL for your table and explain what sort of data it is supposed to represent. which indexes you create besides the primary key is determined by what sort of queries you typically execute against the table.btw, the term uniqueidentifier is a datatype in sql server - it's what everyone else calls a GUID. not sure if that's what you mean by "unique identifier" or not.
www.elsasoft.org |
|
|
|
anti-rich
Starting Member
14 Posts |
Posted - 2007-05-15 : 02:29:43
|
| sorry, 'unique identifier', i should have just said primary key...ok, the 'DLL' is that design document layout? not sure, as im a bit behind the 8ball with abbreviations *sheepish grin*you said that a table without a primary key is a no-no, but according to my company's business rules, a primary key is not an option as sometimes there will be duplicates in my table (completely duplicated rows, not just parts of the row duplicated). should i just create another integer column and use an incrementing number for the PK?cheersadam |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-05-15 : 02:46:31
|
DDL is "data definition language". it's the CREATE TABLE ... statement.normally dupes in a table are to be avoided. if you search in this and any other sql forum you'll find a huge number of "how do I remove dupes" type posts. but in your case it sounds like you *want* dupes. not knowing what sort of data this table is supposed to represent, it's hard to say whether this desire of yours is well placed or not.  post your DDL, and also a description in words of what this table is for, and the t-sql for some typical queries you'll be running against this table.
www.elsasoft.org |
|
|
|
|
|
|
|
|
advantages of indexes and partitioning