# EDW

* Before creating a temp table, delete the table if already exists (IF OBJECT\_ID('TEMP.sg\_ALL\_TXN') IS NOT NULL DROP TABLE TEMP.sg\_ALL\_TXN)
* While creating table, use distribution (CREATE TABLE TEMP.sg\_ALL\_TXN WITH (DISTRIBUTION = HASH(CUSTOMER\_ID),HEAP) AS DL)
* Use table with nolock (WITH(NOLOCK))
* Use outz filter for optimization (INNER JOIN FACT\_LOAN FL WITH(NOLOCK) ON DL.APPL\_ID = FL.APPL\_ID AND DL.OUTZ = 20991231120000 AND FL.OUTZ = 20991231120000)
* Partition by: [https://www.sqlshack.com/sql-partition-by-clause-overview/](https://www.sqlshack.com/sql-partition-by-clause-overview/)
* Different methods to remove duplicate rows: [https://www.sqlshack.com/different-ways-to-sql-delete-duplicate-rows-from-a-sql-table/](https://www.sqlshack.com/different-ways-to-sql-delete-duplicate-rows-from-a-sql-table/)
* Date related: [https://www.sqlshack.com/sql-convert-date-functions-and-formats/](https://www.sqlshack.com/sql-convert-date-functions-and-formats/)
* Update and take care of performance: [https://www.sqlshack.com/how-to-update-from-a-select-statement-in-sql-server/](https://www.sqlshack.com/how-to-update-from-a-select-statement-in-sql-server/)
* While loop in SQL: [https://www.sqlshack.com/sql-while-loop-with-simple-examples/](https://www.sqlshack.com/sql-while-loop-with-simple-examples/)
* Join in SQL: [https://www.sqlshack.com/learn-sql-join-multiple-tables/](https://www.sqlshack.com/learn-sql-join-multiple-tables/)
* SQL Variables: [https://www.sqlshack.com/sql-variables-basics-and-usage/](https://www.sqlshack.com/sql-variables-basics-and-usage/)
* with(NOLOCK) best practices: [https://www.sqlshack.com/understanding-impact-clr-strict-security-configuration-setting-sql-server-2017/](https://www.sqlshack.com/understanding-impact-clr-strict-security-configuration-setting-sql-server-2017/)
*

### References

* [https://www.altexsoft.com/blog/enterprise-data-warehouse-concepts/](https://www.altexsoft.com/blog/enterprise-data-warehouse-concepts/)
* [https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/overview-architecture](https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/overview-architecture)
*
