Q12. How to identify slow running queries in SQL.
Q13. How to increase the performance of your database queries.
Q14. Things to keep in mind while creating a database architecture?
Q15. What are CTEs. What are the benefits of CTEs?
-----------------------------------------------------------------------------------------------------------------------------
Q11. What is ACID in SQL?
Answer:
ACID - Atomic, Consistent, Isolation, and Durability
Atomic – Transaction acting on several pieces of information complete only if all pieces successfully save. Here, “all or nothing” applies to the transaction. The transaction is Atomic. Meaning, the operation to deduct funds from checking cannot complete unless the operation to deposit into savings succeeds. Any interruption cancels the entire transaction.
Consistent – guarantees that a transaction never leaves your database in a half-finished state. In atomic is for all whole transactions. Consistency is for running single transaction
Isolation – No other transactions take place and affect the transaction in process. This prevents “mid-air collisions. ”Isolation ensures that other banking transactions don’t affect the outcome of your transfer. Other transaction to alter your checking balance must wait until your transaction completes.
Durable – System failures or restarts do not affect committed transactions. Being Durable, once the transaction is saved or committed, it can’t be “lost.” Data won’t go missing after a power outage or system crash.
Q12. How to identify slow running queries in SQL.
Answer:
1. Generate an Execution plan. It is part of SSMS - Sequel server management studio.
2. Monitor Resource usages. If running windows machine for SQL server, enable system monitor tool to get the performance data.
3. SQL Azure query performance insight.
-----------------------------------------------------------------------------------------------------------------------------
Q13. How to increase the performance of your database queries.
Answer:
- Use the actual column names in the SQL query instead of selecting all columns (using SELECT *) FROM a table, so that only necessary columns are selected.
- Indexing is usually helpful in large tables. In case of small tables it could be burden.
- Do not use cursors instead use CTE. common table expression.
- Avoid nchar and nvarchar if possible since both the data types takes just double memory as char and varchar.
- Choose appropriate Data Type. E.g. To store strings use varchar in place of text data type. Use text data type, whenever you need to store large data (more than 8000 characters)
- use joins instead of sub-queries
- Use WITH (NOLOCK) while querying the data from any table.
- Use SET NOCOUNT ON and use TRY- CATCH to avoid deadlock condition.
- Use Table variable in place of Temp table. Use of Temp tables required interaction with TempDb database which is a time taking task.
- Use UNION ALL in place of UNION if possible. UNION removes the duplicate records thus has less performance than UNIONALL
-----------------------------------------------------------------------------------------------------------------------------
Q14. Things to keep in mind while creating a database architecture?
Answer:
- Keep a prefect balance between Normalization and denormalization.
- Establish naming conventions before hand.
- Make sure you use constraints (CHECK, NOT NULL, FOREIGN KEY, PRIMARY KEY, and DEFAULT) to ensure that only correct data is stored in the database in the first place. You can always buy faster hardware but you cannot buy more correct data.
- Do not use a GUID as your primary key as it can kill performance. If you need a guid for replication also consider having an int or big int primary key.
- Use singular for table names (i.e. use StudentCourse instead of StudentCourses). Table represents a collection of entities, there is no need for plural names. This will give issues with collections name in .net like entity framework
- Use bit fields for Boolean values. Using integer or varchar is unnecessarily storage consuming. Also start those column names with “Is”.
- Partition big and unused/rarely used tables/table parts to different physical storages for better query performance.
- Lack of database documentation is evil. Document your database design with ER schemas and instructions. Also write comment lines for your triggers, stored procedures and other scripts.
--------------------------------------------------------------------------------------------------------------------------------
Q15. What are CTEs. What are the benefits of CTEs?
Answer:
CTE: Common table expression.
They are of two type 1) recursive and 2) non recursive.
This SQL CTE is used to generate a temporary named set (like a temporary table) that exists for the duration of a query unlike temporary tables in which data remain accessible through the life of procedure, CTE dies with query completion.
CTE recursive can be used to solve hierarchy types of problem like boss of A is B and boss of B is C and so on.
--------------------------------------------------------------------------------------------------------------------------------