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 |
|
wkm1925
Posting Yak Master
207 Posts |
 Posted - 2012-01-17 : 11:31:01
|
| I've more than 100 million rows stored into 1 tableMy question as following,1. If I create an appropriate indexes on my above table, even 200 million rows stored in 1 table, there's no problem in term of performanceNeed explanation, how powerful an appropriate index |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2012-01-17 : 12:14:48
|
| go get a phonebook (does anyone know what these things are now?0and I want you to look up every reference to 'KAISER'The data is in no particular orderyou now MUST go page by page from page 1 thru page nnn to look for every occuranceOR you can have an Index (those names on the top of each page) so you can go directly to the pages that has all of the Kaisers listed on themWhich do YOU Think is faster?And when's the test or the interview?Brett8-)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/ |
 |
|
|
wkm1925
Posting Yak Master
207 Posts |
Posted - 2012-01-17 : 12:31:42
|
| Sir,Nothing to do with the test. I'm a novice to play with index. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
skybvi
Posting Yak Master
193 Posts |
Posted - 2012-01-18 : 10:03:38
|
| clustered or non clustered index are faster than normal retrival for dataRegards,SushantDBAVirgin Islands(U.K) |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-01-18 : 10:30:00
|
quote:
clustered or non clustered index are faster than normal retrival for data
Nope. Or at least very simplifiedSo a little primer.Take brett's analogy of the phonebook.The book has pages. Data lives on these pages. But it also has an order. the rows are in order of surname, firstname, address.That order (surname, firstname, address) is the CLUSTERED INDEX of the phonebook (the data is PHYSICALLY in that order).Therefore searching for surnames beginning 'SN', is very quick.However, searching for all firstnames of 'Charlie' is impossible without scanning the entire book.you could make an index on Firstname which has a lookup value (page number and row number), where you could quickly find people. This is a NONCLUSTERED INDEX of the phone book. To get the details of the people you woul have to LOOK UP the page from the NONCLUSTERED index to get the value.Hope that helps.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-01-18 : 13:19:33
|
| I thought a "normal" retrieval of data WAS using an index ... well, we don't have no stinking Heaps here ... |
|
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2012-01-18 : 13:26:26
|
| Hello wkm1925,Proper index usage and selection are very important for seek performance in RDBMS, but also the size and width of the table will determine the efficiency and speed of a search because data is stored on pages of a fixed size and the wider the rows, the more pages are required. So table design is also a factor in performance and should be considered in design based on need and data growth.FWIW. HTH. |
|
|
|
|
|
|
|
|
query on more than 100 million rows