In my projects i need to design a transactional database and since the beginning i know that it will be the source of an ETL, feeding a Data Warehouse. I add Primary Key (single column or composite) and standardized metadata columns in all the tables. The date-tracking columns: Option 1 […]
T-SQL
37 posts
Just a quick note: pay attention when you hash different data types. The result is different. Also the lower/upper case: Keep it simple :-)
The MERGE statement was introduced in SQL Server 2008 and the developers embraced it immediately. Later a feedback came and the statement was criticized as not very efficient. Aaron Bertrand collected some links about this here. MERGE statement anatomy The MERGE statement joins ‘source’ and ‘target’ tables and runs INSERT, […]
Set-based Database Development As database developer, we need to switch our mindsets to “Set-based thinking”. In simple words we need to manipulate the data as a “set”. An example of set-based database development is: JOIN tables Aggregate the result Calculate the portion (%) of a row to the total of […]
XML is dead, long live JSON! Even before i knew JSON, i thought “XML contains more metadata than data”. Today in my new development, i prefer JSON and avoid using XML, but in some cases we need to fight with XML too. In this quick example i show how to […]
Entity-Atribute-Value (EAV) data model gives us the flexibility to store the data in a way that we have dynamic number of: tables columns in the simplest EAV we have 3 tables: Entity Attribute Value To simplify the understanding of the model we declare: Table Entity holds the list of the […]
The PDF version of the book Learn Microsoft® SQL Server® Intuitively. Transact-SQL: The Solid Basics can be found here. The source code can be downloaded here. Keep it simple :-)
I create a web application and I need to show the navigation to the current page as breadcrumb. The structure of the menu is stored in the database and I will use a recursive CTE to show the current page as breadcrumb.