What is the limit of rows in a table for SQL Server 2005, when SQL query starts getting slower? Is there any way to find out the limit?
I understand it will depend upon the data length of a row. This will also depend on how many more data in other tables and the hardware available.
There are no limits, the only restriction is hard-drive space.
The performance is based on your machine spec and how your tables are indexed.
I think you are asking the wrong question.
If you have the 'right' indexes to cover your query workload, the number of rows won't make much difference until you reach 10's or 100's millions of rows.
Make sure your server has enough RAM.
too many parameters that affect this number. hit and try method is the only available option
If your performance degrades significantly as you add rows and you have good indexing, one of the most likely culprits is a poorly written query. This is why we can't say for sure at what point it would happen, it depends greatly on exactly how you are querying your database and your database structure.
Some of the common things that can work fine in small data sets but which often work badly in larger sets: cursors correlated subqueries wildcards in the first character of a where condidtion functions non-sargeable where clauses forgetting to index the foreign key fields