all_about_SQL

SQL Interview -8
1) Indexes are always important,if yes why?
2) which index type is not used generally for databases and why?
3) what if fragmentation is good for databases? Comment if any condition is true.
4) which fragmentation is more dangerous for queries, external or internal?
5) Is there any other process except rebuild and reorganize for removal of fragmentation?
# dba # DBA interview

SQL interview -6
1) what is the difference between rank() and dense_rank()
2) limitations on using rank function and group by together.
3) Can ranking functions be used with all SQL statements.
4) The importance of a derived table with ranking functions .
5) Can we filter results on ranking functions without using a derived table.

some optimization techniques for SQL query:
1) try to avoid (*) for column selection.
2) always use three part naming conventions.
3) on calculated column, if possible create columnstored indexes.
4) while using computed column, use persisted property.
5) Avoid dynamic SQL .
6) Try to use temporary table instead of table variable if data is big( also can check tempdb spills)
7) indexes should not be fragmented regular maintenance is needed.
8) For stored procedure tables, try to update statistics regularly.