I have seen developers using WITH(nolock) in the query, is there any disadvantage of it? Also, what is the default mode of execution of query? My database do not have any index.
Is there any other way to increase database select statement performance?
The common misconception with nolock is that that it places no locks on the database whilst executing. Technically it does issues a schema-stability (sch-s) lock, so the 'no' part of the lock relates to the data side of the query.
Most of the time that I see this, it is a premature optimization by a developer because they have heard it makes the query faster.
Unless you have instrumented proof and validity in accepting a dirty read (and potentially reading the same row twice) then it should not be used - it definately should not be the default approach to queries, but an exception to the rule when it can be shown that it is required.