Thursday, August 1, 2019

MS SQL Server

  • SQL Query Performance Improvement
    • Use EXISTS () instead of COUNT ()
    • Use NO LOCK
    • Specify column names instead of using * in SELECT statement
    • Create Proper Index
    • Use Join query instead of sub-query
    • sp_executesql instead of Execute for dynamic queries as it cache plan reuse
    • Avoid sp_ prefix
    • Avoid Cursors and While loop
  • Collate
    • Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types and character width.
  • NULLIF - returns null if two expressions are equal else first expression NULLIF(25, 25)
  • COALESCE - returns first not nullable value COALESCE (exp1,exp2,exp3)
  • Partition By - ROW_NUMBER() OVER(Partition by EmpName ORDER BY EmpName)
  • GROUPING SETS - generates multiple grouping sets.
    • SELECT
          column1,
          column2,
          aggregate_function (column3)
      FROM
          table_name
      GROUP BY
          GROUPING SETS (
              (column1, column2),
              (column1),
              (column2),
              ()
      );
       
  • CUBE - generate grouping sets with all combinations of the dimension columns.
    • SELECT
          d1,
          d2,
          d3,
          aggregate_function (c4)
      FROM
          table_name
      GROUP BY
          CUBE (d1, d2, d3); 
  • ROLLUP - generate grouping sets with an assumption of the hierarchy between input columns
    • SELECT
          d1,
          d2,
          d3,
          aggregate_function(c4)
      FROM
          table_name
      GROUP BY
          ROLLUP (d1, d2, d3);
       
  • OFFSET FETCH 
    • select * from Products order by ProductID OFFSET 2 ROW FETCH NEXT 2 ROW ONLY 
    • OFFSET means skip rows
    • FETCH specifies the number of rows to return after OFFSET clause has been processed
  • CTE - use common table expresssions to make complex queries more readable.
    • WITH expression_name[(column_name [,...])]
      AS
          (CTE_definition)
      SQL_statement;
       
  • Recursive CTE - query hierarchical data using recursive CTE.
    • WITH expression_name (column_list)
      AS
      (
          -- Anchor member
          initial_query  
          UNION ALL
          -- Recursive member that references expression_name.
          recursive_query  
      )
      -- references expression name
      SELECT *
      FROM   expression_name
       
  • UNION - Returns rows from T1 as well as T2 results but remove duplicate rows
  • UNION ALL - Returns rows from T1 as well as T2 result sets
  • INTERSECT - Returns common rows from two result sets
  • EXCEPT - Returns rows from T1 that does not exist in T2
  • PIVOT - convert rows to columns
    •  
  • MERGE
    • The MERGE statement in SQL is a very popular clause that can handle inserts, updates, and deletes all in a single transaction without having to write separate logic for each of these
    • MERGE target_table USING source_table
      ON merge_condition
      WHEN MATCHED
          THEN update_statement
      WHEN NOT MATCHED
          THEN insert_statement
      WHEN NOT MATCHED BY SOURCE
          THEN DELETE;
       
  • Indexed Views
    • With SchemaBinding
  • Triggers
    • Special type of stored procedure that automatically runs when an event occurs in database 
    • Data Manipulation Language events like INSERT, UPDATE and DELETE Table Data 
    • Data Definition Language events like CREATE, ALTER and DROP TABLE
  • Stored Procedure
    • The procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.
    • We can use Transactions in Procedure whereas we can't use Transactions in Function.

  • Functions
    • The function must return a value but in Stored Procedure it is optional
    • Functions can have only input parameters for it whereas Procedures can have input or output parameters.
    • Functions can be called from Procedure whereas Procedures cannot be called from a Function.
    • Function can be embedded in a SELECT statement.
    • Functions that return tables can be treated as another rowset. This can be used in JOINs with other tables.
    • An exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.
  • Cursor
  • Transaction Concurrency Issues
    • Lost Updates
      • When two users make update to same row, first user update will be lost
    • Dirty Read
      • When first user read the data and at same time second user update same data then first user has dirty data or dirty read
    • Non-Repeatable Read
      • When user read data every time he gets different rows 
    • Phantom Read
      • When user 1 read data, and user 2 deleted that data
  • Transaction Isolation Levels
    • READ UNCOMMITED
      • Syntax - SET TRANSACTION ISOLATION LEVEL READ UNCOMMITED
      • Read Uncommitted is the lowest isolation level. In this level, one transaction may read not yet committed changes made by other transaction, thereby allowing dirty reads. In this level, transactions are not isolated from each other.
    • READ COMMITED
      • Syntax - SET TRANSACTION ISOLATION LEVEL READ COMMITED
      • Read committed is an isolation level that guarantees that any data read was committed at the moment is read. It simply restricts the reader from seeing any intermediate, uncommitted, 'dirty' read. IT makes no promise whatsoever that if the transaction re-issues the read, will find the Same data, data is free to change after it was read.
    • REPEATABLE READ
      •  Syntax - SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
      • Repeatable read is a higher isolation level, that in addition to the guarantees of the read committed level, it also guarantees that any data read cannot change, if the transaction reads the same data again, it will find the previously read data in place, unchanged, and available to read.
    • SERIALIZABLE
      •  Syntax - SET TRANSACTION ISOLATION LEVELSERIALIZABLE
      • makes an even stronger guarantee: in addition to everything repeatable read guarantees, it also guarantees that no new data can be seen by a subsequent read.
    • Say you have a table T with a column C with one row in it, say it has the value '1'. And consider you have a simple task like following:
    • BEGIN TRANSACTION;
      SELECT * FROM T;
      WAITFOR DELAY '00:01:00'
      SELECT * FROM T;
      COMMIT;
      That is a simple task that issue two reads from table T, with a delay of 1 minute between them.
    • under READ COMITTED, the second SELECT may return any data. A concurrent transaction may update the record, delete it, insert new records. The second select will always see the new data.
    • under REPEATABLE READ the second SELECT is guaranteed to see the rows that has seen at first select unchanged. New rows may be added by a concurrent transaction in that one minute, but the existing rows cannot be deleted nor changed.
    • under SERIALIZABLE reads the second select is guaranteed to see exactly the same rows as the first. No row can change, nor deleted, nor new rows could be inserted by a concurrent transaction.
    • And finally, there is also the SNAPSHOT isolation level. 
      SNAPSHOT isolation level makes the same guarantees as serializable, 
      but not by requiring that no concurrent transaction can modify the 
      data. Instead, it forces every reader to see it's own version of the 
      world (it's own 'snapshot'). This makes it very easy to program against 
      as well as very scalable as it does not block concurrent updates. 
      However, that benefit comes with a price: extra server resource 
      consumption. 
       
  • Nested Transaction
  • Save Points in Transaction
  • Constraints
    • SQL constraints are used to specify rules for the data in a table.
    • CHECK
    • UNIQUE
    • Primary Key
    • Foreign Key
    • Default
    • Not null
  • Linked Server
    • Linked Servers are a method by which a SQL Server can talk to another SQL Server
    • sp_addlinkedserver
    • sp_linkedservers
  • Temporary Table vs Table Variable
    • Table variable (@table) is created in the memory. Whereas, a Temporary table (#temp) is created in the tempdb database. However, if there is a memory pressure the pages belonging to a table variable may be pushed to tempdb.
    • Table variables cannot be involved in transactions, logging or locking. This makes @table faster then #temp. So table variable is faster then temporary table
    • Temporary tables are allowed CREATE INDEXes whereas, Table variables aren’t allowed CREATE INDEX instead they can have index by using Primary Key or Unique Constraint.
    • Table variable can be passed as a parameter to functions and stored procedures while the same cannot be done with Temporary tables.
    • Use Table variable, if you have less than 1000 rows otherwise go for Temporary tables.
    • Temporary Tables are physically created in the tempdb database. These tables act as the normal table and also can have constraints, index like normal tables.

No comments:

Post a Comment