i have to tune the below query select * from table in table has lakes of records,it is not having any index either clustered or non clustered.I need to retrieve a records faster without adding any index in this table while executing the above query.
The query, by itself, cannot be tuned. Even if you could add indexes, an index would only help if you had a `WHERE` clause, or only returned a sub-set of columns that were covered by an index. If you can add a `WHERE` clause so the query doesn't return lakhs of rows, you might be in a better place. You will also be doing better if you don't return every column (remove * if you can). How many columns does the table have, and how many do you need the query to return? If you cannot add a `WHERE` clause or reduce the number of columns, the best way to get the query to return faster is to put the database on a faster disk sub-system.
There is no way to tune the query SELECT * FROM Table. There is especially no way to tune any query if you can't put indexes on the table. That is one of the primary mechanisms for tuning queries (in addition to writing the correct code). Under the limits you have set, you only have a very few options. First, you can try isolating this table onto it's own disk so that it doesn't face contention with other resources. You can try installing faster CPUs, more CPUs, faster memory, more memory, faster disks, more disks, to try to make the hardware run faster. But any performance enhancements are going to be focused into those areas, reduced contention or improved hardware. What is the full purpose that you're trying to achieve? Or is this an interview question? Either way, the limits you're imposing are quite abnormal.
**The interview answer** The bottom line if this came up in an interview is that I would think the interviewer was asking me to come back with "I would work with the requester to refine the request". I have seen developers, especially those not overly familiar with SQL, just bring the whole table over to the application and then filter there. If its not going fast enough for them, it is probably because the table has grown and now it is time to refine their sql by first refining the requirement and finding out what data the application actually needs. If they are filtering inside the code, we can filter in sql instead and that will probably be faster. If they aren't actually using every single column, we can exclude some and save time that way. **Actually speeding it up, and why you can't optimize** In a sense, that query is too simple to optimize. It is essentially instructing the server to dump everything in the table. With more complicated queries, you have options. Indexes are normally the first place to look, but those have been banned and even if you could use them they would not help here. Indexes are great at making specific subsets of the tables go faster, but here you want the entire table. With a more complicated query, you can ensure that you are avoiding things that will slow it down (are you using a triangle join or some other comparison that will force it to go row by agonizing row? Are you using a "distinct" where it isn't needed? Are you hiding an unneeded "distinct" by using something like union?) But here you don't have any of those. Table hints won't really help. They can help guide the interpreter to the right index, but that doesn't apply here. Nolock was mentioned in one of the comments, and that might help, but mostly if there was a blocking issue and it risks dirty reads. So, if you really need this query to go faster, I would look to the hardware as Grant said. But I don't think most people would call throwing more hardware at the situation optimizing. If you want go the hardware route, I would start with the disks. The speed difference between a normal harddrive and a good [SSD is definitely noticeable]. If you really want to go crazy, you can create a ramdisk and store this table there, though of course you would then need to recreate everytime you reboot. I would also look at the speed of the network connection if the server is a separate machine (which is the most common scenario outside of test environments). The other hardware upgrades Grant mentioned can all help but I would focus on the speed of the drive and the network connection first. The other thing that could help is removing other requirements from the server. Is the server supporting lots of other databases that we can move somewhere else to free up resources for this query and its users? Is the server handling other roles like also acting as a file server or a shared drive and can we offload those roles elsewhere? But like the hardware, I don't consider these optimizations in the normal sense of the word. **A tricky interviewer** Ok, this only applies to an interview question that is meant to be tricky, and it doesn't really work with this "dump the entire table" scenario. But assume you are asked to optimize a query that is almost as simple without putting an index on the table select * from table where col1 = 2 If you want that to go faster the obvious way is to put an index on col1, but that was banned. So, you make view for that query and then put the index on the view. SQL Server (at least [Enterprise Edition]) is smart enough to use the index on the view to benefit queries on the base table. Of course, this only applies if you think you have an interviewer asking you a trick question. In real life doing that would be ridiculous. You would just put the index on the base table, and if you couldn't index the base table there would be some real reason for it that would also stop you from indexing a view (for instance you have a severe space shortage and can't afford the disk space the index would take, or you need to keep write speeds fast and an index slows down write operations....) :
SQL server is intelligent enough to handle this query. you can tune a query that has some selection criteria. if you are selecting everything from table even though table has index why would sql server use index ? table scan is fastest way when you do not have any WHERE condition.