|
By Nishant Kumar
SQL Server Optimization Tips are as follows:
|
Restrict Query Result for performance optimization
Restricting query result means return of required rows instead of all
rows of the table. This helps in reducing network traffic.
Restrict columns in the SQL query
Never try to return all the columns of the table when you require only a few of
them. This helps in reducing network traffic.
|
Use stored procedure instead of heavy duty queries
Stored procedure exists as compiled objects on disk. Moreover, SQL
server also keeps optimized execution plan for stored procedure. This really
saves time and speeds up execution.
Avoid SQL cursor for optimal performance
You should avoid using SQL cursor since it has adverse effect on
SQL server’s performance. It fetches the records row by row which results in
repeated network round trips.
Use constraints instead of trigger
Trigger runs every time data gets updated even when it is not required. This
adds overhead on the system.
Use table variable instead of temporary tables.
When you create a temporary table (#TABLE) it physically creates the
table in tempdb. It is an added overhead to create table. A table variable
doesn’t need to be created in the database, it can stay in memory and go out of
scope immediately after the batch ends, just like regular variables.
Avoid using Having clause
Try to use where as far as possible instead of Having clause.
Include SET NOCOUNT ON in the stored procedure.
This prevents stored procedure to send messages indicating number of
rows affected thus saves network traffic.
Database Optimization question that can be asked during interview
-
What is nested join, hash join and merge join in SQL Query plan?
-
What is an Execution plan?
-
What are indexes? What are types of indexes?
-
Define B-Trees.
-
How do you see the SQL plan in textual format?
-
Is it a good database design to create indexes on the table in which lot of
inserts occurs?
-
What are “Table Scan’s” and “Index Scan’s”?
-
What is “FillFactor” concept in indexes? What is the best value for
“FillFactor”?
-
Define “Index statistics”.
-
How can we see statistics of an index?
-
Define Fragmentation. How can we measure Fragmentation?
-
What are the criteria to be considered while selecting an index?
-
Define “Index Tuning Wizard”.
SQL
Server Interview questions part 2 includes following questions with
answers
Difference between DELETE and TRUNCATE commands in SQL
Server. | What are constraints in SQL Server? | What is an index? | What is
RAID? | Ways to troubleshoot performance problems in SQL Server. | Steps to
secure an SQL Server. | What is a deadlock and what is a live lock? | What is
blocking and how would you troubleshoot it? | Ways of moving data between
servers and databases in SQL Server? | Explian different types of BACKUPs
avaialabe in SQL Server? | What is database replicaion?
SQL
Server Interview questions part 3 includes following questions with
answers
What are cursors in SQL Server? | What is a join and explain different types of
joins. | What is an extended stored procedure in SQL Server? | What are
triggers in SQL Server? | What is a self join in SQL Server? | What is the
difference between UNION ALL Statement and UNION? | Write some disadvantage of
Cursor? | What is Log Shipping in SQL Server?
The column or columns of the table whose value uniquely identifies each row in
the table is called primary key. You can define column as primary key using
primary key constraint while you create table.....
|