Unleashing the Power of Data with Azure Fabric: A Unified Data Platform

In today's data-driven world, organizations are grappling with the challenge of managing and deriving insights from vast amounts of data scattered across various sources. This is where Azure Fabric emerges as a game-changer. It's a unified data platform that empowers businesses to seamlessly integrate, explore, and analyze data to drive informed decision-making.

What is Azure Fabric?

Azure Fabric is a comprehensive platform that brings together data integration, data warehousing, data exploration, and machine learning capabilities into a single, cohesive environment. It offers a unified experience for data professionals, allowing them to work efficiently and collaboratively.

Key Features and Benefits

• Unified Data Integration: Azure Fabric simplifies data ingestion from diverse sources, including on-premises, cloud, and real-time data streams. This ensures data consistency and accessibility across the organization.
• High-Performance Data Warehousing: Its powerful data warehousing capabilities enable lightning-fast query performance, even on massive datasets. This empowers analysts to uncover valuable insights quickly.
• Interactive Data Exploration: With intuitive tools and visualizations, Azure Fabric empowers users to explore data visually, discover patterns, and identify trends effortlessly.
• Advanced Analytics and Machine Learning: The platform integrates seamlessly with Azure's AI and machine learning services, allowing you to build predictive models and uncover hidden insights.
• Collaboration and Governance: Azure Fabric fosters collaboration among data teams, enabling them to share insights and work together effectively. It also provides robust governance features to protect sensitive data.

Real-World Use Cases

• Retail: Optimize inventory management, personalize customer experiences, and predict sales trends.
• Financial Services: Detect fraud, assess risk, and improve customer retention through advanced analytics.
• Healthcare: Analyze patient data to improve treatment outcomes, optimize resource allocation, and accelerate drug discovery.
• Manufacturing: Optimize production processes, predict equipment failures, and enhance supply chain management.

Getting Started with Azure Fabric

To embark on your data transformation journey with Azure Fabric, consider the following steps:

1. Assess Your Data Landscape: Understand your data sources, volumes, and requirements to determine the optimal Fabric configuration.
2. Build a Strong Data Foundation: Establish a robust data ingestion pipeline to ensure data quality and consistency.
3. Empower Your Data Teams: Provide training and support to enable your teams to leverage Fabric's capabilities effectively.
4. Start Small, Scale Up: Begin with a pilot project to validate the platform's value and gradually expand its usage.

Mastering Data Integration with Azure Fabric

Data integration is the cornerstone of any successful data platform. Azure Fabric excels in this area by offering a comprehensive suite of tools and services to seamlessly bring data from various sources into a unified environment.

Key Features and Benefits:

• Broad Connectivity: Azure Fabric supports a wide range of data sources including relational databases, NoSQL stores, cloud applications, and real-time data streams.
• Data Transformation: Powerful data transformation capabilities allow you to clean, enrich, and prepare data for analysis.
• Data Quality: Built-in data quality checks ensure data accuracy and consistency.
• Scalability: Easily handle increasing data volumes and complexity.
• Performance Optimization: Accelerate data ingestion and processing through optimized pipelines.

Integration Patterns:

• Batch Integration: For large, static datasets that require periodic updates.
• Delta Integration: For incremental changes to existing data.
• Change Data Capture (CDC): For real-time updates from transactional systems.
• Stream Processing: For high-velocity data streams that require immediate processing.

Best Practices for Data Integration:

• Data Profiling: Understand your data before integration to identify quality issues and potential challenges.
• Data Mapping: Clearly define how data will be transformed and loaded into the target system.
• Data Validation: Implement robust data validation checks to ensure data integrity.
• Error Handling: Develop strategies for handling data errors and failures.
• Monitoring and Optimization: Continuously monitor data pipelines for performance and identify optimization opportunities.

Real-World Examples:

• Retailer: Integrating sales data from multiple stores, online channels, and loyalty programs to create a unified customer view.
• Financial Institution: Consolidating data from various systems (CRM, trading platforms, risk management) to improve decision-making.
• Healthcare Provider: Integrating patient data from electronic health records, medical devices, and claims to support population health management.

Additional Considerations:

• Data Security and Privacy: Implement appropriate security measures to protect sensitive data.
• Cost Optimization: Optimize data integration processes to reduce costs.
• Metadata Management: Effectively manage metadata to improve data discoverability and understanding.

By effectively leveraging Azure Fabric's data integration capabilities, organizations can create a solid foundation for data-driven insights and decision-making.

Understanding OAuth 2.0 Grant Types and Their Usage

 

Understanding OAuth 2.0 Grant Types and Their Usage

In today's digital landscape, securing user data and ensuring seamless access to resources are paramount. OAuth 2.0, an authorization framework, has become a cornerstone in achieving these goals. By delegating user authentication to the service that hosts the user account and authorizing third-party applications to access the user account, OAuth 2.0 offers a robust mechanism for managing access to resources. Let's dive into the various grant types defined by OAuth 2.0 and understand their specific usage scenarios.

1. Authorization Code Grant

Usage Scenario: This is the most common grant type, designed for web and mobile applications. It involves a two-step process where the client application first obtains an authorization code and then exchanges it for an access token.

Flow:

1. The user is redirected to the authorization server to authenticate.
2. After authentication, the authorization server redirects back to the client with an authorization code.
3. The client exchanges the authorization code for an access token by making a request to the authorization server.

Example Use Case:

• A web application that needs to access a user's resources stored on another server, such as accessing Google Drive from a web app.

2. Implicit Grant

Usage Scenario: This grant type is optimized for public clients, such as single-page applications (SPA) or mobile apps, where the client secret cannot be stored securely.

Flow:

1. The user is redirected to the authorization server to authenticate.
2. After authentication, the authorization server redirects back to the client with an access token directly (no intermediate authorization code).

Example Use Case:

• A single-page web application that needs quick access to an access token without server-side code.

3. Resource Owner Password Credentials Grant

Usage Scenario: This grant type is used when the user trusts the client application completely, such as first-party applications. It involves the client obtaining the user's credentials directly and exchanging them for an access token.

Flow:

1. The user provides their username and password directly to the client application.
2. The client application sends these credentials to the authorization server.
3. The authorization server returns an access token.

Example Use Case:

• A company's internal application where users are required to log in with their company credentials.

4. Client Credentials Grant

Usage Scenario: This grant type is used for server-to-server interactions where the client is acting on its own behalf, not on behalf of a user.

Flow:

1. The client application authenticates itself to the authorization server using its client ID and client secret.
2. The authorization server returns an access token.

Example Use Case:

• A backend service that needs to authenticate itself to access another service's API, such as a microservice accessing a configuration service.

5. Refresh Token Grant

Usage Scenario: This grant type allows clients to obtain a new access token by using a refresh token, which is typically issued with the initial access token. This is useful for long-lived access without requiring the user to re-authenticate.

Flow:

1. The client application uses the refresh token to request a new access token from the authorization server.
2. The authorization server returns a new access token (and optionally a new refresh token).

Example Use Case:

• A web application that needs to maintain user sessions over long periods without forcing the user to log in again.

Summary of Grant Types and Their Use Cases

Grant Type	Use Case Description
Authorization Code Grant	Web/mobile apps needing to securely obtain an access token
Implicit Grant	Single-page apps needing quick access tokens
Resource Owner Password Grant	Trusted applications where users provide credentials directly
Client Credentials Grant	Server-to-server interactions
Refresh Token Grant	Obtaining new access tokens without re-authentication

Example Implementation: Authorization Code Grant in .NET Core

To provide a concrete example, let's look at how you might implement the Authorization Code Grant in a .NET Core application using the Microsoft Identity platform.

Step 1: Configure Authentication in Startup.cs

public void ConfigureServices(IServiceCollection services)
{
    {
        options.DefaultScheme = CookieAuthenticationDefaults.AuthenticationScheme;
        options.DefaultChallengeScheme = OpenIdConnectDefaults.AuthenticationScheme;
        options.ClientId = Configuration["AzureAd:ClientId"];
        options.Authority = $"{Configuration["AzureAd:Instance"]}{Configuration["AzureAd:TenantId"]}";
        options.ClientSecret = Configuration["AzureAd:ClientSecret"];
        options.ResponseType = "code";
        options.SaveTokens = true;
        options.UseTokenLifetime = true;
        options.CallbackPath = "/signin-oidc";
    });
public void Configure(IApplicationBuilder app, IHostingEnvironment env)
    services.AddAuthentication(options =>
    .AddOpenIdConnect(options =>
}
{
    if (env.IsDevelopment())
        app.UseDeveloperExceptionPage();
    else
    })

    {
    {
        app.UseExceptionHandler("/Home/Error");
        app.UseHsts();
    app.UseHttpsRedirection();
    app.UseRouting();
    app.UseAuthentication();

    {
        endpoints.MapControllerRoute(
            name: "default",
            pattern: "{controller=Home}/{action=Index}/{id?}");
    });
}
    .AddCookie()
    }
    {
    }
    app.UseStaticFiles();
    app.UseAuthorization();
    app.UseEndpoints(endpoints =>

Step 2: Configure Azure AD in appsettings.json

This example demonstrates how to set up authentication using the Authorization Code Grant 
in a .NET Core application. Adjust the configurations according to your specific needs 
and identity provider.

{
  "AzureAd": {
    "Instance": "https://login.microsoftonline.com/",
    "TenantId": "your-tenant-id",
    "ClientId": "your-client-id",
    "ClientSecret": "your-client-secret",
    "CallbackPath": "/signin-oidc"
  }
}

Conclusion

OAuth 2.0 provides a versatile and secure framework for managing authorization in various scenarios. By understanding the different grant types and their appropriate use cases, developers can effectively implement OAuth 2.0 to enhance the security and user experience of their applications.

Whether you're building web applications, mobile apps, or server-to-server integrations, OAuth 2.0 offers the flexibility and security needed to manage user authentication and authorization efficiently.

Mastering the AI-102 Exam: A Comprehensive Guide Based on My Experience

 The AI-102 exam, officially titled "Designing and Implementing an Azure AI Solution," is a critical certification for professionals looking to demonstrate their expertise in creating AI solutions using Microsoft Azure. Having recently prepared for and taken the AI-102 exam, I’m excited to share my insights and strategies that helped me pass it successfully. This guide will cover the key areas you should focus on and the best practices for preparing and acing the exam.

Understanding the AI-102 Exam

The AI-102 exam is designed for individuals who want to validate their skills in designing and implementing AI solutions on Azure. The exam tests your ability to:

• Analyze solution requirements
• Design AI solutions
• Integrate AI models into solutions
• Deploy and maintain AI solutions

It covers a range of topics, including:

• Analyzing solution requirements
• Designing AI solutions
• Integrating AI solutions
• Deploying and maintaining AI solutions

Key Areas of Focus

Based on my experience, here are the critical areas to concentrate on:

1. Understanding AI Concepts and Azure AI Services

• AI Fundamentals: Have a solid grasp of AI concepts, including machine learning, natural language processing, and computer vision.
• Azure AI Services: Get familiar with Azure services such as Azure Cognitive Services, Azure Machine Learning, and Azure Bot Services. Understand their features, capabilities, and best use cases.

2. Analyzing Solution Requirements

• Requirements Gathering: Practice analyzing business requirements and translating them into technical specifications for AI solutions.
• Case Studies: Work on real-world case studies to understand how to design solutions that meet specific needs and constraints.

3. Designing AI Solutions

• Solution Design: Learn how to design AI solutions that leverage various Azure services effectively. Focus on designing solutions for different scenarios, such as chatbots, image recognition, and sentiment analysis.
• Architecture: Understand the architectural considerations for deploying AI solutions, including scalability, security, and performance.

4. Integrating AI Solutions

• Integration Patterns: Explore how to integrate AI models into applications and services. Familiarize yourself with integration patterns and techniques, including REST APIs and SDKs.
• Data Handling: Know how to manage and preprocess data for AI models. This includes data ingestion, cleaning, and transformation.

5. Deploying and Maintaining AI Solutions

• Deployment: Learn about deployment options for AI solutions, including Azure Kubernetes Service (AKS), Azure App Services, and Azure Functions.
• Monitoring and Maintenance: Understand how to monitor AI solutions, handle errors, and perform maintenance tasks to ensure optimal performance.

Study Resources and Preparation Strategies

1. Microsoft Learn

• Learning Paths: Microsoft Learn provides structured learning paths specifically for the AI-102 exam. These include modules on AI concepts, Azure AI services, and solution design.

2. Official Documentation

• Azure Documentation: Dive into the Azure documentation for Cognitive Services, Machine Learning, and Bot Services. This will give you detailed information on service capabilities and best practices.

3. Practice Tests

• Exam Practice: Take practice exams to familiarize yourself with the question format and identify areas where you need further study. Use official practice tests and sample questions available from Microsoft and other trusted sources.

4. Hands-On Experience

• Azure Portal: Gain hands-on experience by working directly in the Azure portal. Set up and configure various AI services, build sample projects, and experiment with different features.

5. Study Groups and Forums

• Community Engagement: Join study groups and online forums to discuss exam topics, share resources, and get advice from others who have taken the exam.

Tips for Exam Day

• Review Key Concepts: Before the exam, review your notes and focus on key concepts and services.
• Read Questions Carefully: During the exam, read each question carefully and ensure you understand what is being asked before selecting an answer.
• Manage Your Time: Keep track of time and pace yourself to ensure you can answer all questions within the allotted time.

Conclusion

Passing the AI-102 exam requires a solid understanding of AI concepts, practical experience with Azure AI services, and effective study strategies. By focusing on the key areas, utilizing the right resources, and practicing diligently, you can position yourself for success. The AI-102 certification will not only validate your skills but also enhance your ability to design and implement AI solutions on Microsoft Azure. Good luck with your exam preparation!

Azure SQL Managed Instance: Your Path to Seamless Cloud Migration

 

Azure SQL Managed Instance: The Ultimate Guide for Modern Data Management

Azure SQL Managed Instance (MI) is a managed database service that bridges the gap between SQL Server and the full range of capabilities offered by Platform as a Service (PaaS). It combines the best features of SQL Server with the operational and financial benefits of the cloud, making it a top choice for organizations seeking a scalable, secure, and cost-effective database solution. In this blog, we’ll explore what Azure SQL Managed Instance is, its key features, benefits, use cases, and how to get started.

What is Azure SQL Managed Instance?

Azure SQL Managed Instance is a fully managed database service that offers near 100% compatibility with the latest SQL Server (Enterprise Edition) database engine. It provides the full power of SQL Server with the operational and financial advantages of an intelligent, fully-managed service. This service is designed for applications that require high availability, advanced data security, and strong performance, without the need to manage underlying infrastructure.

Key Features of Azure SQL Managed Instance

1. Compatibility

Azure SQL Managed Instance provides nearly 100% compatibility with SQL Server, ensuring that your applications can run seamlessly with minimal changes. This compatibility extends to features like SQL Server Agent, Service Broker, Database Mail, and more.

2. High Availability and Disaster Recovery

Managed Instance includes built-in high availability (HA) with automatic failover capabilities and a 99.99% uptime SLA. Additionally, it supports automated backups and geo-redundant disaster recovery options.

3. Security

Azure SQL Managed Instance offers advanced security features such as Transparent Data Encryption (TDE), Always Encrypted, Row-Level Security, and Dynamic Data Masking. It also integrates with Azure Active Directory for seamless identity management and role-based access control.

4. Scalability

With Managed Instance, you can easily scale your resources up or down based on your workload demands. It supports both vertical and horizontal scaling, ensuring your database can handle varying levels of traffic.

5. Cost Efficiency

Managed Instance offers a cost-effective solution with flexible pricing options, including pay-as-you-go and reserved capacity. You also save on operational costs as Microsoft handles the maintenance, patching, and updates.

6. Managed Service

As a fully managed service, Managed Instance takes care of routine database management tasks, such as backups, monitoring, and patching, allowing you to focus on your core business.

Benefits of Azure SQL Managed Instance

1. Ease of Migration

Migrating to Azure SQL Managed Instance is straightforward, thanks to the high compatibility with on-premises SQL Server. Tools like Azure Database Migration Service simplify the process, ensuring minimal downtime and data loss.

2. Reduced Operational Overhead

With Microsoft handling the operational aspects of database management, your IT team can focus on more strategic initiatives. This reduction in overhead translates to cost savings and increased efficiency.

3. Enhanced Performance

Managed Instance provides consistent and predictable performance through built-in features like automatic tuning and adaptive query processing. This ensures your applications run smoothly, even during peak times.

4. Comprehensive Monitoring and Analytics

Azure provides robust monitoring and analytics tools, such as Azure Monitor and Azure Log Analytics, to help you gain insights into your database performance and usage patterns.

5. Compliance and Certifications

Azure SQL Managed Instance is compliant with a wide range of industry standards and certifications, including GDPR, HIPAA, and ISO/IEC 27001. This ensures that your data is handled in accordance with the highest security and privacy standards.

Use Cases for Azure SQL Managed Instance

1. Application Modernization

Organizations looking to modernize their legacy applications can migrate to Managed Instance to take advantage of cloud benefits without re-architecting their applications.

2. Disaster Recovery

With its built-in high availability and disaster recovery capabilities, Managed Instance is ideal for organizations seeking robust DR solutions.

3. Hybrid Cloud Solutions

Managed Instance supports hybrid cloud architectures, allowing you to integrate your on-premises and cloud environments seamlessly.

4. Development and Testing

The flexibility and scalability of Managed Instance make it a great choice for development and testing environments, where resource requirements can vary significantly.

Getting Started with Azure SQL Managed Instance

1. Create an Instance

Begin by creating an Azure SQL Managed Instance through the Azure portal. You’ll need to specify the instance name, region, pricing tier, and other configurations.

2. Configure Networking

Managed Instance requires a virtual network (VNet) for connectivity. Configure the VNet and subnets as per your requirements.

3. Migrate Your Data

Use Azure Database Migration Service or other tools like Data Migration Assistant to migrate your existing databases to Managed Instance.

4. Optimize Performance

After migration, use built-in tools and features like Query Store, automatic tuning, and Azure Advisor to optimize the performance of your databases.

5. Monitor and Manage

Leverage Azure Monitor and other Azure management tools to monitor the health and performance of your Managed Instance.

Conclusion

Azure SQL Managed Instance is a powerful, fully managed database service that offers the best of SQL Server with the benefits of a managed cloud service. Its high compatibility, robust security, and advanced features make it an ideal choice for modernizing your data infrastructure, reducing operational overhead, and ensuring high availability and performance. Whether you are migrating legacy applications or building new cloud-native solutions, Azure SQL Managed Instance provides a scalable, secure, and cost-effective platform for your data needs.

Mastering Azure SQL: A Comprehensive Guide

 

Introduction

In the modern digital landscape, data is the lifeblood of businesses. Managing and leveraging this data effectively is crucial for success. Azure SQL, a suite of managed SQL services by Microsoft, offers a robust, scalable, and intelligent solution for managing relational databases in the cloud. This blog aims to provide a comprehensive guide to understanding and utilizing Azure SQL, covering its key features, benefits, and practical applications.

What is Azure SQL?

Azure SQL is a family of managed, secure, and intelligent SQL database services built on the SQL Server engine. It includes several deployment options:

1. Azure SQL Database: A fully managed relational database service designed for the cloud. It offers a high-availability architecture, automatic backups, and advanced security features.
2. Azure SQL Managed Instance: Combines the best features of Azure SQL Database with full SQL Server compatibility. It’s ideal for migrating existing SQL Server applications to the cloud.
3. SQL Server on Azure Virtual Machines: Provides full control over the SQL Server instance, running on a virtual machine in Azure. It’s perfect for applications that require complete SQL Server compatibility and custom configurations.

Key Features of Azure SQL

1. Scalability

Azure SQL offers flexible and scalable performance levels to meet varying workload demands. You can scale up or down on-the-fly, ensuring that your database can handle spikes in traffic without compromising performance.

2. High Availability and Disaster Recovery

Azure SQL provides built-in high availability and disaster recovery features. It includes automated backups, point-in-time restore, and geo-replication, ensuring your data is protected and available even in the event of a regional outage.

3. Advanced Security

Security is a top priority in Azure SQL. It offers advanced data protection features such as data encryption at rest and in transit, advanced threat protection, and vulnerability assessments. Managed identity integration and role-based access control (RBAC) further enhance security.

4. Intelligent Performance

Azure SQL leverages machine learning and AI to optimize performance. Features like automatic tuning, query performance insights, and intelligent query processing ensure your database performs optimally without manual intervention.

5. Compatibility

Azure SQL Managed Instance and SQL Server on Azure Virtual Machines provide high compatibility with on-premises SQL Server environments. This makes it easier to migrate existing applications to the cloud with minimal changes.

Benefits of Using Azure SQL

1. Reduced Management Overhead

Azure SQL is a managed service, meaning Microsoft handles most of the administrative tasks such as patching, backups, and monitoring. This allows your team to focus on more strategic activities rather than routine database maintenance.

2. Cost Efficiency

Azure SQL offers a pay-as-you-go pricing model, allowing you to only pay for the resources you use. With features like serverless compute and auto-scaling, you can optimize costs by automatically adjusting resources based on workload demands.

3. Enhanced Security and Compliance

Azure SQL’s comprehensive security features help you meet compliance requirements and protect your data against threats. Microsoft’s extensive compliance certifications further ensure your database adheres to industry standards and regulations.

4. High Performance and Availability

The built-in high availability and performance optimization features of Azure SQL ensure that your applications run smoothly and reliably, providing a seamless experience for your users.

Practical Applications of Azure SQL

1. Modernizing Legacy Applications

Migrating legacy on-premises SQL Server databases to Azure SQL Managed Instance allows organizations to modernize their applications without extensive rewrites. This approach leverages cloud benefits while maintaining compatibility with existing SQL Server features.

2. Developing New Applications

For new application development, Azure SQL Database offers a fully managed and scalable platform. Developers can quickly provision databases, scale resources, and leverage built-in intelligence features to optimize performance.

3. Data Analytics and BI

Azure SQL can serve as a backend for data analytics and business intelligence (BI) solutions. Its integration with Azure Synapse Analytics and Power BI enables organizations to build powerful data-driven insights and visualizations.

4. Disaster Recovery

Azure SQL’s geo-replication and point-in-time restore capabilities make it an excellent choice for implementing robust disaster recovery solutions. Organizations can ensure business continuity by replicating databases across different regions.

Getting Started with Azure SQL

Step 1: Create an Azure SQL Database

To get started, log in to the Azure Portal and create a new Azure SQL Database. You can choose from various pricing tiers based on your performance and storage requirements.

Step 2: Configure Database Settings

During the creation process, configure the necessary settings such as compute size, storage capacity, and backup retention policies. You can also enable features like geo-replication and threat detection.

Step 3: Connect to Your Database

Once your database is provisioned, you can connect to it using tools like SQL Server Management Studio (SSMS), Azure Data Studio, or directly from your application using the provided connection string.

Step 4: Manage and Monitor Your Database

Use the Azure Portal or Azure CLI to manage and monitor your database. Azure SQL provides built-in dashboards and performance insights to help you track and optimize database performance.

Best Practices for Using Azure SQL

1. Optimize Query Performance

Regularly review and optimize your queries to ensure they run efficiently. Use tools like Query Performance Insight and automatic tuning to identify and address performance bottlenecks.

2. Implement Security Best Practices

Ensure your database is secure by implementing best practices such as using strong passwords, enabling encryption, and regularly reviewing security alerts and assessments.

3. Plan for High Availability

Leverage Azure SQL’s high availability features to minimize downtime. Implement geo-replication and backup strategies to ensure your data is protected and can be quickly restored in case of an outage.

4. Monitor and Manage Costs

Regularly monitor your database usage and costs. Use Azure Cost Management tools to set budgets and alerts, and optimize your resources by adjusting performance levels based on workload demands.

Conclusion

Azure SQL provides a powerful, flexible, and secure platform for managing relational databases in the cloud. Whether you are modernizing legacy applications, developing new solutions, or implementing data analytics, Azure SQL offers the tools and features needed to succeed. By leveraging its scalability, high availability, advanced security, and intelligent performance, organizations can ensure their data is managed efficiently and effectively. Start your Azure SQL journey today and unlock the full potential of your data.

Ideal SQL Query For Handling Error & Transcation in MS SQL

BEGIN TRY

BEGIN TRAN

--put queries here

COMMIT;

END TRY
BEGIN CATCH
IF @@TRANCOUNT>0
BEGIN
SELECT @@ERROR,ERROR_MESSAGE ( )
ROLLBACK;
END
END CATCH


any one can use above code to manage transaction as well as error handling in sql server.

Capitalize the First Character of Each Word using queries

I have found t-sql challenges question where they were asked to provide a solution of oracle INITCAP function in sql.

I was started to find the solution of INITCAP function & found lots of user defined function.

But here I have shared a solution where queries doesn't use any user defined function as well as any variable.

--First of all create one temp table    
DECLARE @t TABLE(ID INT IDENTITY, Data VARCHAR(1000))

--insert the sample data
INSERT INTO @t 
SELECT 'yogesh bhadauriya' 


--use the Common table expression
;with CTE AS
(

    --anchor query where we are finding the first character for replacing with uppercase
    SELECT Cast(STUFF(LOWER(Data), 1, 1, UPPER(LEFT(Data, 1))) As varchar(max)) As Data,
    PATINDEX('%[^a-zA-Z][a-z]%', Data COLLATE Latin1_General_Bin) As Position,ID,Data As OldData
    From @t
    
    Union All
    
   --recursive query
    SELECT Cast(STUFF(CTE.Data, Position, 2, UPPER(SUBSTRING(CTE.Data, CTE.Position, 2))) As Varchar(max)) as Data,
    Cast(PATINDEX('%[^a-zA-Z][a-z]%', CTE.Data COLLATE Latin1_General_Bin) As Int) As Position,CTE.ID,T.Data As OldData
    From @t As T
    Inner Join CTE On CTE.ID=CTE.ID
    where CTE.Data is not null
)
Select ID,OldData As Data,Data As Results
From CTE
where Data is not null
and Position=0


---output
--Id Data    Results
--1 yogesh bhadauriya Yogesh Bhadauriya
    

User defined function to convert the number into charcter

How to convert the numeric value into its specific character? If this is your question, than create the following User Defined Function into your database.

--create the following function 
CREATE FUNCTION dbo.udf_Num_ToWords 
(
 @Number Numeric (38, 0) -- Input number with as many as 18 digits

) RETURNS VARCHAR(8000) 
/*
* Converts a integer number as large as 34 digits into the 
* equivalent words.  The first letter is capitalized.
*
* Attribution: Based on NumberToWords by Srinivas Sampath
*        as revised by Nick Barclay
*
* Example:
select dbo.udf_Num_ToWords (1234567890) + CHAR(10)
      +  dbo.udf_Num_ToWords (0) + CHAR(10)
      +  dbo.udf_Num_ToWords (123) + CHAR(10)
select dbo.udf_Num_ToWords(76543210987654321098765432109876543210)
 
DECLARE @i numeric (38,0)
SET @i = 0
WHILE @I <= 1000 BEGIN 
    PRINT convert (char(5), @i)  
            + convert(varchar(255), dbo.udf_Num_ToWords(@i)) 
    SET @I  = @i + 1 
END
*
* Published as the T-SQL UDF of the Week Vol 2 #9 2/17/03
****************************************************************/
AS BEGIN

DECLARE @inputNumber VARCHAR(38)
DECLARE @NumbersTable TABLE (number CHAR(2), word VARCHAR(10))
DECLARE @outputString VARCHAR(8000)
DECLARE @length INT
DECLARE @counter INT
DECLARE @loops INT
DECLARE @position INT
DECLARE @chunk CHAR(3) -- for chunks of 3 numbers
DECLARE @tensones CHAR(2)
DECLARE @hundreds CHAR(1)
DECLARE @tens CHAR(1)
DECLARE @ones CHAR(1)

IF @Number = 0 Return 'Zero'

-- initialize the variables
SELECT @inputNumber = CONVERT(varchar(38), @Number)
     , @outputString = ''
     , @counter = 1
SELECT @length   = LEN(@inputNumber)
     , @position = LEN(@inputNumber) - 2
     , @loops    = LEN(@inputNumber)/3

-- make sure there is an extra loop added for the remaining numbers
IF LEN(@inputNumber) % 3 <> 0 SET @loops = @loops + 1

-- insert data for the numbers and words
INSERT INTO @NumbersTable   SELECT '00', ''
    UNION ALL SELECT '01', 'one'      UNION ALL SELECT '02', 'two'
    UNION ALL SELECT '03', 'three'    UNION ALL SELECT '04', 'four'
    UNION ALL SELECT '05', 'five'     UNION ALL SELECT '06', 'six'
    UNION ALL SELECT '07', 'seven'    UNION ALL SELECT '08', 'eight'
    UNION ALL SELECT '09', 'nine'     UNION ALL SELECT '10', 'ten'
    UNION ALL SELECT '11', 'eleven'   UNION ALL SELECT '12', 'twelve'
    UNION ALL SELECT '13', 'thirteen' UNION ALL SELECT '14', 'fourteen'
    UNION ALL SELECT '15', 'fifteen'  UNION ALL SELECT '16', 'sixteen'
    UNION ALL SELECT '17', 'seventeen' UNION ALL SELECT '18', 'eighteen'
    UNION ALL SELECT '19', 'nineteen' UNION ALL SELECT '20', 'twenty'
    UNION ALL SELECT '30', 'thirty'   UNION ALL SELECT '40', 'forty'
    UNION ALL SELECT '50', 'fifty'    UNION ALL SELECT '60', 'sixty'
    UNION ALL SELECT '70', 'seventy'  UNION ALL SELECT '80', 'eighty'
    UNION ALL SELECT '90', 'ninety'   

WHILE @counter <= @loops BEGIN

 -- get chunks of 3 numbers at a time, padded with leading zeros
 SET @chunk = RIGHT('000' + SUBSTRING(@inputNumber, @position, 3), 3)

 IF @chunk <> '000' BEGIN
  SELECT @tensones = SUBSTRING(@chunk, 2, 2)
       , @hundreds = SUBSTRING(@chunk, 1, 1)
       , @tens = SUBSTRING(@chunk, 2, 1)
       , @ones = SUBSTRING(@chunk, 3, 1)

  -- If twenty or less, use the word directly from @NumbersTable
  IF CONVERT(INT, @tensones) <= 20 OR @Ones='0' BEGIN
   SET @outputString = (SELECT word 
                                      FROM @NumbersTable 
                                      WHERE @tensones = number)
                   + CASE @counter WHEN 1 THEN '' -- No name
                       WHEN 2 THEN ' thousand ' WHEN 3 THEN ' thousand '
                       WHEN 4 THEN ' billion '  WHEN 5 THEN ' trillion '
                       WHEN 6 THEN ' quadrillion ' WHEN 7 THEN ' quintillion '
                       WHEN 8 THEN ' sextillion '  WHEN 9 THEN ' septillion '
                       WHEN 10 THEN ' octillion '  WHEN 11 THEN ' nonillion '
                       WHEN 12 THEN ' decillion '  WHEN 13 THEN ' undecillion '
                       ELSE '' END
                               + @outputString
      END
   ELSE BEGIN -- break down the ones and the tens separately

             SET @outputString = ' ' 
                            + (SELECT word 
                                    FROM @NumbersTable 
                                    WHERE @tens + '0' = number)
              + '-'
                             + (SELECT word 
                                    FROM @NumbersTable 
                                    WHERE '0'+ @ones = number)
                   + CASE @counter WHEN 1 THEN '' -- No name
                       WHEN 2 THEN ' thousand ' WHEN 3 THEN ' million '
                       WHEN 4 THEN ' billion '  WHEN 5 THEN ' trillion '
                       WHEN 6 THEN ' quadrillion ' WHEN 7 THEN ' quintillion '
                       WHEN 8 THEN ' sextillion '  WHEN 9 THEN ' septillion '
                       WHEN 10 THEN ' octillion '  WHEN 11 THEN ' nonillion '
                       WHEN 12 THEN ' decillion '   WHEN 13 THEN ' undecillion '
                       ELSE '' END
                            + @outputString
  END

  -- now get the hundreds
  IF @hundreds <> '0' BEGIN
   SET @outputString  = (SELECT word 
                                      FROM @NumbersTable 
                                      WHERE '0' + @hundreds = number)
                 + ' hundred ' 
                                + @outputString
  END
 END

 SELECT @counter = @counter + 1
      , @position = @position - 3

END

-- Remove any double spaces
SET @outputString = LTRIM(RTRIM(REPLACE(@outputString, '  ', ' ')))
SET @outputstring = UPPER(LEFT(@outputstring, 1)) + SUBSTRING(@outputstring, 2, 8000)


RETURN @outputString -- return the result
END

To check the output of the above User Defined Function, execute the below query into your database.

--now run the following query to get the result

select dbo.udf_Num_ToWords(5800000)

--output
--Five thousand eight hundred thousand

--
Happy Coding to All

Recursive Query To Find All Child Node in SQL server 2005+

How to find the all child of particular parent using the Recursive Query

--here we are creating the table variable so that we can insert some dummy record
Declare @Table Table
(
 TId int,
 ParentId int,
 Name varchar(10)
)
--inserting some records
--using the union all to insert more than one records
insert into @Table
Select 1,NULL,'ICT'
Union All
Select 2,1,'ICT-M1'
Union All
Select 4,1,'ICT-M2'
Union All
Select 7,2,'ICT-M1U1'
Union All
Select 8,2,'ICT-M1U2'
Union All
Select 9,4,'ICT-M2U1'
Union All
Select 10,4,'ICT-M2U2'
Union All
Select 11,7,'ICT-M1U1P1'
Union All
Select 12,7,'ICT-M1U1P2'
Union All
Select 13,8,'ICT-M1U2P1'
Union All
Select 14,8,'ICT-M1U2P2'
Union All
Select 15,9,'ICT-M2U1P1'
Union All
Select 16,9,'ICT-M2U1P2'
Union All
Select 17,10,'ICT-M2U2P1'
Union All
Select 18,10,'ICT-M2U2P2'

--variable to hold data
Declare @ChildNode varchar(1000)
Set @ChildNode='';

 
--use the standard recursive query
;with [CTE] as 
(
 --anchor query where we are finding the all parents
    select TId,ParentId,Name,CAST(ISNULL(CAST(ParentId as varchar(10)),'0') As Varchar(100)) As ChildNode
    from @Table c where c.[ParentId] is null

    union all

 --recursive query where we are finding the all child according to anchor query parent
    select c.TId,c.ParentId,c.Name,
    CAST( p.ChildNode +','+cast(c.TId as varchar(10) ) As Varchar(100)) As ChildNode
    from [CTE] p, @Table c where c.[ParentId] = p.[TId]    
)
--select the child node as per the id
--Assigin the all Ids into  one variable
select @ChildNode=@ChildNode+','+Cast(TId as varchar(10))
from [CTE]
Cross Apply
dbo.Split(ChildNode,',')
where items=9
order by TId

select SUBSTRING(@ChildNode,2,LEN(@ChildNode))

--outpu
--2,7,8,11,12,13,14


----
--create the tabular function to split the multi valued into table
Create FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))     
returns @temptable TABLE (items varchar(8000))     
as     
begin     
 declare @idx int     
 declare @slice varchar(8000)     
    
 select @idx = 1     
  if len(@String)<1 or @String is null  return     
    
 while @idx!= 0     
 begin     
  set @idx = charindex(@Delimiter,@String)     
  if @idx!=0     
   set @slice = left(@String,@idx - 1)     
  else     
   set @slice = @String     
  
  if(len(@slice)>0)
   insert into @temptable(Items) values(@slice)     

  set @String = right(@String,len(@String) - @idx)     
  if len(@String) = 0 break     
 end 
return     
end

--

Happy Coding

How to insert multiple record using xml in sql server 2005+

How to insert the multiple record using the xml into sql server, please check the following example.

DECLARE @idoc int
DECLARE @doc varchar(max)
SET @doc ='
<ROOT>
 <Trans TransId="1" Add="false" Edit="true" Delete="true" View="true" Block="false">   
 </Trans>
 <Trans TransId="2" Add="1" Edit="1" Delete="1" View="1" Block="0">   
 </Trans>
</ROOT>'

--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.


SELECT *
Into #TempTable 
FROM OPENXML(@idoc, '/ROOT/Trans',1)
WITH 
( 
 TransId  varchar(10),
    [Add] bit,
    Edit bit,
    [Delete] bit,
    [View] bit,
    Block bit
)

Select * From #TempTable

drop table #TempTable

--

happy coding