DEV Community: Srinivas R 🇮🇳

Temp table in SQL Server

Srinivas R 🇮🇳 — Fri, 22 Sep 2023 04:55:40 +0000

What is temp table in SQL Server?

In SQL Server, a temporary table is a table that is created and exists only for the duration of a database session or a specific batch of SQL statements. Temporary tables are typically used to store intermediate results, perform complex data manipulations, or break down a complex query into simpler steps.

There are two types of temporary tables in SQL Server:

Local Temporary Tables: These tables are prefixed with a single "#" symbol and are visible only within the current session. They are automatically dropped when the session that created them ends or when the batch of SQL statements completes.

Example:

CREATE TABLE #TempTable (
ID INT,
Name VARCHAR(50)
);

Global Temporary Tables: These tables are prefixed with a double "##" symbol and are visible across multiple sessions. They persist until all sessions that have referenced them are closed.

Example:

CREATE TABLE ##GlobalTempTable (
ID INT,
Name VARCHAR(50)
);

Temporary tables are useful for scenarios where you need to temporarily store and manipulate data within a specific scope, without affecting the structure of your permanent tables. Once you're done with a temporary table, it's a good practice to explicitly drop it to release the associated resources.

Please note that temporary tables can be a powerful tool, but they should be used judiciously to avoid performance issues and resource contention in a multi-user database environment.

What is ETL

Srinivas R 🇮🇳 — Mon, 18 Sep 2023 11:16:44 +0000

𝗪𝗵𝗮𝘁 𝗶𝘀 𝗘𝗧𝗟?

Extract, Transform, Load (ETL) is a data integration process that involves:

𝟭. 𝗘𝘅𝘁𝗿𝗮𝗰𝘁: This step involves extracting data from various heterogeneous sources. These sources include databases, flat files, APIs, or other data storage mechanisms.

𝟮. 𝗧𝗿𝗮𝗻𝘀𝗳𝗼𝗿𝗺: Once the data is extracted, it often needs to be transformed into a format suitable for analysis or reporting. This transformation can involve various operations such as:
  🔹 Cleaning the data (e.g., removing duplicates or correcting errors).
  🔹 Enriching the data (e.g., combining it with other sources).
  🔹 Aggregating or summarizing data.
  🔹 Converting data types or formats.
  🔹 Applying business rules or calculations.

𝟯. 𝗟𝗼𝗮𝗱: The final step is to load the transformed data into a target system, often a data warehouse, data mart, or another database. This system is then used for business intelligence, reporting, or further analysis.

Some everyday use cases for ETL are:

𝟭. 𝗗𝗮𝘁𝗮 𝗪𝗮𝗿𝗲𝗵𝗼𝘂𝘀𝗶𝗻𝗴: ETL processes are fundamental to data warehousing. They pull data from various operational systems, transform it, and then load it into a data warehouse for analysis.

𝟮. 𝗗𝗮𝘁𝗮 𝗠𝗶𝗴𝗿𝗮𝘁𝗶𝗼𝗻: When businesses change or upgrade their systems, they often need to move data from one system or format to another. ETL processes can help with this migration.

𝟯. 𝗗𝗮𝘁𝗮 𝗜𝗻𝘁𝗲𝗴𝗿𝗮𝘁𝗶𝗼𝗻: Companies often have data spread across multiple systems. ETL can integrate this data to provide a unified view.

𝟰. 𝗕𝘂𝘀𝗶𝗻𝗲𝘀𝘀 𝗜𝗻𝘁𝗲𝗹𝗹𝗶𝗴𝗲𝗻𝗰𝗲 𝗮𝗻𝗱 𝗥𝗲𝗽𝗼𝗿𝘁𝗶𝗻𝗴: For meaningful BI and reporting, data must often be cleaned, transformed, and integrated. ETL processes facilitate this.

𝟱. 𝗗𝗮𝘁𝗮 𝗟𝗮𝗸𝗲 𝗣𝗼𝗽𝘂𝗹𝗮𝘁𝗶𝗼𝗻: ETL processes can populate data lakes with structured and unstructured data from various sources.

Some standard 𝗘𝗧𝗟 𝘁𝗼𝗼𝗹𝘀 are Microsoft SSIS, Talend, Oracle Data Integrator, Apache NiFi, and AWS Glue.

There is also a bit different approach nowadays, called 𝗘𝗟𝗧 (𝗘𝘅𝘁𝗿𝗮𝗰𝘁, 𝗟𝗼𝗮𝗱, 𝗧𝗿𝗮𝗻𝘀𝗳𝗼𝗿𝗺). This is a data integration approach where raw data is extracted from various sources, loaded directly into a data warehouse or big data platform, and finally transformed within that target systems

What is CQRS Pattern?

Srinivas R 🇮🇳 — Sun, 17 Sep 2023 05:51:05 +0000

CQRS, which stands for Command Query Responsibility Segregation, is a software architectural pattern used in software development, particularly in the context of designing and building complex applications. The CQRS pattern separates the responsibility for handling commands (requests that change the state of an application) from the responsibility for handling queries (requests that retrieve data from the application).

Key characteristics of the CQRS pattern include:

Separation of Commands and Queries: In a CQRS-based architecture, commands (e.g., creating, updating, or deleting data) are handled separately from queries (e.g., reading data). This separation allows each part of the application to be optimized for its specific task.
Command Handlers: Command handlers are responsible for processing commands. They update the application's state and can perform tasks such as validation, authorization, and triggering domain logic.
Query Handlers: Query handlers are responsible for retrieving data in response to queries. They are optimized for reading data efficiently and do not modify the application's state.
Event Sourcing: Often used in conjunction with CQRS, event sourcing is a pattern where the state of an application is determined by a sequence of events. Events are recorded when commands are executed, and the application's state is reconstructed by replaying these events.
Scalability: CQRS can improve the scalability of an application since commands and queries can be scaled independently. This is especially useful in scenarios where read and write workloads have different performance requirements.
Complex Domains: CQRS is particularly beneficial in applications with complex domain models where different operations have distinct requirements.

CQRS can provide benefits in terms of performance optimization, scalability, and flexibility in designing systems that need to handle a variety of data manipulation and retrieval operations. However, it can also introduce complexity and is best suited for situations where the separation of concerns between commands and queries is advantageous.

𝗛𝗶𝘀𝘁𝗼𝗿𝘆 𝗢𝗳 𝗖# 𝗟𝗮𝗻𝗴𝘂𝗮𝗴𝗲

Srinivas R 🇮🇳 — Fri, 15 Sep 2023 06:09:13 +0000

During more than 20 years of development, we saw 11 versions of the C# language. Every version gave us some new and interesting features. Here is a brief overview of those features:

🔹 𝗖# 𝟭.𝟬 released with .NET 1.0 and VS2002 (January 2002).

🔹 𝗖# 𝟭.𝟮 released with .NET 1.1 and VS2003 (April 2003). The first version is called Dispose on IEnumerators which implemented IDisposable.

🔹 𝗖# 𝟮.𝟬 released with .NET 2.0 and VS2005 (November 2005). Major new features: generics, anonymous methods, nullable types, and iterator blocks.

🔹 𝗖# 𝟯.𝟬 released with .NET 3.5 and VS2008 (November 2007). Major new features: lambda expressions, extension methods, expression trees, anonymous types, implicit typing (var), and query expressions.

🔹 𝗖# 𝟰.𝟬 released with .NET 4 and VS2010 (April 2010). Major new features: late binding (dynamic), delegate and interface generic variance, more COM support, named arguments, tuple data type, and optional parameters.

🔹 𝗖# 𝟱.𝟬 released with .NET 4.5 and VS2012 (August 2012). Major features: async programming and caller info attributes.

🔹 𝗖# 𝟲.𝟬 released with .NET 4.6 and VS2015 (July 2015). Enabled by Roslyn. Features: initializers for automatically implemented properties, using directives to import static members, exception filters, and more.

🔹 𝗖# 𝟳.𝟬 released with .NET 4.7 and VS2017 (March 2017). Major new features: tuples, ref locals and ref return, pattern matching (including pattern-based switch statements), and more.

🔹 𝗖# 𝟳.𝟭 released with VS2017 v15.3 (August 2017). New features: async main, tuple member name inference, default expression, and pattern matching with generics.

🔹 𝗖# 𝟳.𝟮 released with VS2017 v15.5 (November 2017). New features: private protected access modifier, Span, aka interior pointer, aka stackonly struct, and everything else.

🔹 𝗖# 𝟳.𝟯 released with VS2017 v15.7 (May 2018). New features: enum, delegate, and unmanaged generic type constraints. ref reassignment.

🔹 𝗖# 𝟴.𝟬 released with .NET Core 3.0 and VS2019 v16.3 (September 2019). Major new features: nullable reference-types, asynchronous streams, indices and ranges, readonly members, and more.

🔹 𝗖# 𝟵.𝟬 released with .NET 5.0 and VS2019 v16.8 (November 2020). Major new features: init-only properties, records, with-expressions, data classes, positional records, top-level programs, and improved pattern matching.

🔹 𝗖# 𝟭𝟬.𝟬 released with .NET 6.0 (November 2021). Major new features: record structs, struct parameterless constructors, interpolated string handlers, and more.

🔹 𝗖# 𝟭𝟭.𝟬 released with .NET 7.0 (November 2022). Major new features: file-scoped types, generic math support, auto-default structs, pattern match Span on a constant string, and more.

What features do you use the most?