| Author |
Topic |
|
falconlee
Starting Member
2 Posts |
 Posted - 2010-11-24 : 19:30:58
|
| Hi, I have some question regarding creating index in sqlserver 2005. I am using sqlserver management studio express. And i discover several ways to create index. 1. Modify the table, right click one of the column, and choose indexes / keys. From here i can add more index. After created, it would appear in the Indexes folder.2. Right click the Indexes folder, choose New Index. A windows is prompted. So, it require to fill in a name and add index key. It can be add more than one index key. What's the different if i add one index key per index AND one index with multiple keys?Also, What's the Included Columns? What's the purpose include other column in the index? how it works? I am confusing when i want to create an index. |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
falconlee
Starting Member
2 Posts |
Posted - 2010-11-25 : 01:40:28
|
| Hi Brett, i cannot get what you meant ! |
 |
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2010-11-26 : 05:41:13
|
| If you look in Books Online under indexes it explains in some detail how an index works-----------I used to think I wasn't a morning person but it never got better in the afternoon |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-11-26 : 05:48:20
|
| Use a query windowalter table to create a constraint - e.g. PK - which will be supported by a unique indexcreate index for everything else.An index is supported by a hierarchy of indexed column data (balanced, no binary, tree) to efficiently get to the leaf level.Included columns are not part of that hierarchy but are added to the leaf level. Means that the index can be made covering (no need to access the dat page) without the overhead of maintaining the index levels - makes the index smaller.Kalen Delaney wrote sql server 6.5 unleashed which had a very clear and readable explanation of data structures in sql server - if you can get a copy it's still worth reading. Later unleashed were a waste of time imho.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2010-11-26 : 06:19:34
|
quote:
Originally posted by nigelrivett
Use a query windowalter table to create a constraint - e.g. PK - which will be supported by a unique indexcreate index for everything else.An index is supported by a hierarchy of indexed column data (balanced, no binary, tree) to efficiently get to the leaf level.Included columns are not part of that hierarchy but are added to the leaf level. Means that the index can be made covering (no need to access the dat page) without the overhead of maintaining the index levels - makes the index smaller.Kalen Delaney wrote sql server 6.5 unleashed which had a very clear and readable explanation of data structures in sql server - if you can get a copy it's still worth reading. Later unleashed were a waste of time imho.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy.
Contact Kalen directly or go herehttp://www.amazon.com/Microsoft-SQL-Server-6-5-Unleashed/dp/0672309564cut my teeth on 6.5...after about 15 years on DB2 OS390Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxWant to help yourself?http://msdn.microsoft.com/en-us/library/ms130214.aspxhttp://weblogs.sqlteam.com/brettk/http://brettkaiser.blogspot.com/ |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-11-26 : 06:24:58
|
| Good grief - it's still avaliable.I threw out my copy a couple of weeks ago during a junk dump - with all the old VB, unix, dos, win32, nt4, psion books.The ones that aren't supporting hi-fi equipment anyway.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-11-26 : 11:43:05
|
| everything on technology I hadn't used for the last 10 years.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
Sqlserver 2005 indexing