T-SQL: LEFT (RIGHT) JOIN and WHERE clause
Keep it simple :-)
SQL Server
T-SQL: Hash Different Data Types
Just a quick note: pay attention when you hash different data types. The result is different. Also the lower/upper case: Keep it simple :-)
T-SQL: Avoid MERGE. Write separate statements instead
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, […]
T-SQL: Avoid Cursor. Use WHILE instead
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 […]
Python: Build JSON Array and keep the last object based on key 2
An ETL that i build recently instigated me to share the following excerpt of python code. The players in this ETL are: Apache Kafka (Source) Azure Data Factory (ETL app) Azure Databricks (Extract and Transform with Python) Azure Data Lake Storage (File storage) Cosmos DB (Destination) In this example i […]
SSRS: Split Groups with List
Let say that we need the following transformation in SSRS report: One of the tools to do this is List. I will explain only the tricky part and will give the .rdl in the end. Drag-and-drop List in the report body: Right click the top left corner of the list […]
T-SQL: Parse “regular” and XML Column(s) to Table
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 […]
Data Model: Entity-Atribute-Value (EAV)
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 […]
SQL Server CLR: Assembly to Read/Write from/to REST API
In this example we’ll create SQL Server Common Language Runtime (CLR) to read/write from/to REST API. Technology used: Visual Studio 2019 (C# Windows .NET Class Library) SQL Server 2019 (on premises) Authentication: Basic (base64 encoded Username:Password) Requests: HTTP SQL GET SELECT POST INSERT Visual Studio 2019 Create new C#, Windows, […]
Book: Learn Microsoft® SQL Server® Intuitively. Transact-SQL: The Solid Basics
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 :-)