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 

Performance Comparision Between For, While and Foreach Loop

Today I was posted one post regarding list to datatable conversion. But one of the my senior told me that try to avoid foreach loop.

So that I was googled/binged regarding the same.

Here I am showing you some figure for the same here.

Using the System.Diagnostics.Stopwatch class I ran some tests. 100,000 iterations in a for loop that did nothing inside took me 0.0003745 seconds. This was the code for the loop:

for (int i = 0; i < 100000; i++) ;

The while loop resulted 0.0003641 seconds, which is pretty much the same as the for loop. Here is the code I used:

int i=0;
while (i < 100000)
 i++;

The foreach loop has a slightly different purpose. It is meant for itterating through some collection that implements IEnumerable. It's performance is much slower, my test resulted in 0.0009076 seconds with this code:

int[] test = new int[100000];
foreach (int i in test) ;

foreach creates an instance of an enumerator (returned from GetEnumerator) and that enumerator also keeps state throughout the course of the foreach loop. It then repeatedly calls for the Next() object on the enumerator and runs your code for each object it returns.


So, it seems that 'while' is the fastest looping technique among the three available techniques in C#,  for a given processing within the loop. Right?

It varies. "while" and "for" have pretty much the same results. While had a slight advantage, but not one that great.


Im not an expert, but I have the feeling that a while and for loop, once compiled to MSLI, probably are both the exact same thing.

And I wouldn't be surprised if foreach was faster when iterating through objects, since the optimizer can "expect" whats going to happen... So I'd go with foreach being faster if you can use it, and the 2 others being the same thing, if foreach isn't applicable.

My logic here is that if you're looping through a collection, and you use a for loop for example, you're going to have to use the index of the collection, which depending on implementation, could be a minor performance hit to "seek" the object, as opposed to going through a well written iterator.

Just an example.So really: I wouldn't care too much about performance of these loops. This isn't C/C++, and when you compile, its not native code (at first). So it is safe to assume that the solution that seems the most efficient "logically" will be so in practice.

For each is slower for a number of reasons. One is it is using the IEnumerable interface, which requires some casting (assuming you aren't using a generic collection). My test above seemed to go along with that as well. A simple for/while/do loop is pretty much as simple as it gets.

As for the MSIL code, let's take a look. This is the MSIL for the for loop:

IL_0000:  nop
  IL_0001:  ldc.i4.0
  IL_0002:  stloc.0
  IL_0003:  br.s       IL_0009
  IL_0005:  ldloc.0
  IL_0006:  ldc.i4.1
  IL_0007:  add
  IL_0008:  stloc.0
  IL_0009:  ldloc.0
  IL_000a:  ldc.i4     0x186a0
  IL_000f:  clt
  IL_0011:  stloc.1
  IL_0012:  ldloc.1
  IL_0013:  brtrue.s   IL_0005

And here it is for the while loop:

IL_0000:  nop
  IL_0001:  ldc.i4.0
  IL_0002:  stloc.0
  IL_0003:  br.s       IL_0009
  IL_0005:  ldloc.0
  IL_0006:  ldc.i4.1
  IL_0007:  add
  IL_0008:  stloc.0
  IL_0009:  ldloc.0
  IL_000a:  ldc.i4     0x186a0
  IL_000f:  clt
  IL_0011:  stloc.1
  IL_0012:  ldloc.1
  IL_0013:  brtrue.s   IL_0005

So yes, they are exactly identical.

Although very handy, C#'s foreach statement is actually quite dangerous. In fact, I may swear off its use entirely. Why? Two reasons: (1) performance, and (2) predictability.

Performance

Iterating through a collection using foreach is slower than with for. I can't remember where I first learned that, perhaps in Patterns & Practices: Improving .Net Application Performance. Maybe it was from personal experience. How much slower? Well, I suppose that depends on your particular circumstances. Here are a few interesting references:

• FOREACH Vs. FOR (C#): shows the extra IL code created for a foreach loop
• To foreach or not to foreach that is the question: claims it isn't so clear-cut, at least for regular arrays. Not absolutely conclusive.

Predictability

I was looking at the C# Reference entry for foreach today and noticed this for the first time (italics added by me):

The foreach statement is used to iterate through the collection to get the desired information, but should not be used to change the contents of the collection to avoid unpredictable side effects.

What's that all about? Let's take this as an example:

foreach(MyClass myObj in List)

Looking deeper into the C# Language Specification... the iteration variable is supposed to be read-only, though apparently that doesn't stop you from updating a property of an object. Thus for instance it would be illegal to assign a new value to myObj, but not to assign a new value to myObj.MyProperty.
And that's all I can find. Why are there unpredictable side effects? I don't know. But seems best to heed Microsoft's warning.

Conclusion

Some argue that you shouldn't code for performance from the beginning, and therefore go ahead and use foreach whenever you want so long as you don't update the values. In my experience that's hogwash — most of the code I work on goes into environments where performance is extremely important. Besides, writing a for statement requires very little extra coding compared to a foreach statement. Furthermore, if you have a lot going on inside your iteration block, it can be easy to forget and accidentally update the iteration variable inside a foreach loop. Thus do I conclude: just avoid foreach altogether.

Honestly the academically correct answer is "It's Irrelevant".  You cant optimize performance by somehow picking "the best loop".  If you're doing performance optimizations you should start with a Big O analysis of your algorithms and Profiling.  You'd be amazed at how much faster keeping around a dictionary for lookups or using a sorted list + binary search is than looping over a list every time you need to find an object.

Doing so will give you unnoticeable performance increases at the cost of maintainability and programmer time and the effort you're putting in to such small performance boosts is better spent on a proper design and implementation.   Let's take a real world example of finding duplicates in a list to illustrate my point:
first let's assume that comparing two items is O(1).  We can implement this any number of ways, two of which are:

A) use nested loops with i on the outer loop and j on the inner loop, when list[i] == list[j] push the pair onto a list of duplicates.
B) copy the list to tmpList.  quicksort tmpList.  iterate over the list with i, when list[i] == list[i+1] push the pair onto the list of duplicates

No amount of optimization can change the fact that A runs in O(n^2) while B runs in O(2*n+n*log(n)).

Convert List to DataTable

One day I confused to convert the List collection to datatable object. I was wondering how to iterate the columns wise and rows wise in list to make some calculation.

I got the solution which was posted below:

public DataTable ListToDataTable(IEnumerable list)
{
    var dt = new DataTable();

    foreach (var info in typeof(T).GetProperties())
    {
        dt.Columns.Add(new DataColumn(info.Name, info.PropertyType));
    }
    foreach (var t in list)
    {
        var row = dt.NewRow();
        foreach (var info in typeof(T).GetProperties())
        {
            row[info.Name] = info.GetValue(t, null);
        }
        dt.Rows.Add(row);
    }
    return dt;
}

--
Happy coding.

Age calculation with SQL Server

There seem to be many different methods being suggested to calculate an age in SQLServer.  Some are quite complex but most are simply wrong.  This is by far the simplest and accurate method that I know.

Declare @Date1 datetime
Declare @Date2 datetime


Select @Date1 = '15Feb1971'Select @Date2 = '08Dec2009'select CASE
WHEN dateadd(year, datediff (year, @Date1, @Date2), @Date1) > @Date2
THEN datediff (year, @Date1, @Date2) - 1
ELSE datediff (year, @Date1, @Date2)END as Age

--

happy coding