How to improve the performance of SQL queries ?

There are several ways to improve the performance of SQL queries:

1. Indexing: 
• Indexing is the process of creating a data structure that allows for faster retrieval of data. By indexing frequently used columns, queries can run much faster. You should index columns that are frequently used in WHERE, JOIN, and ORDER BY clauses.
2. Use appropriate data types: 
• It's important to use appropriate data types for columns in your tables. Using the wrong data type can lead to slower queries. For example, if you're storing dates as strings instead of using the DATE data type, it can slow down queries that are filtering by dates.
3. Limit the amount of data returned: 
• When you're writing queries, try to limit the amount of data that is returned. You can do this by using the SELECT statement to only return the columns that you need, and by using the WHERE clause to filter out rows that you don't need.
4. Avoid using SELECT *: 
• Using SELECT * can slow down queries because it returns all columns in the table, even if you don't need them. Instead, you should specify the columns that you need.
5. Use JOINs wisely: 
• JOINs can be very powerful, but they can also be very slow if not used correctly. Try to use INNER JOINs instead of OUTER JOINs whenever possible, and make sure that you're joining on columns that are indexed.
6. Avoid subqueries: 
• Subqueries can be slow, so try to avoid them whenever possible. You can often rewrite a subquery as a JOIN or a correlated subquery to improve performance.
7. Optimize database design: 
• Good database design can improve query performance. Make sure that your tables are normalized and that you're using appropriate data types for columns.
8. Use stored procedures: 
• Stored procedures can be precompiled and stored in memory, which can improve performance. If you have frequently used queries, consider creating stored procedures for them.
9. Use database performance tools: 
• Most databases have performance tools that can help you identify slow queries and optimize them. Use these tools to identify queries that need to be optimized.
10. Keep your database software up to date: 
• Finally, make sure that you're using the latest version of your database software. Newer versions often have performance improvements that can help speed up queries.

By following these tips, you can help ensure that your SQL queries are as fast and efficient as possible. 

Query to generate Single XML from multiple tables in SQL Server

Here is the sample code to generate a single xml from multiple table query in Sql Server. In this example I am querying data from two tables.

Sample Query :

SELECT
    (SELECT * FROM dbo.TableA FOR XML PATH('TableA'), TYPE),
    (SELECT * FROM dbo.TableB FOR XML PATH('TableB'), TYPE)
FOR XML PATH(''), ROOT('root')

XML Output :

<root>
   <TableA>
	 <Column1>Value1</Column1>
    	 <Column2>Value2</Column2>
   </TableA>
   <TableB>
	 <Column1>Value1</Column1>
    	 <Column2>Value2</Column2>
   </TableB>
</root>

You can load this Xml into DataSet using below C# code.

DataSet ds = new DataSet();
ds.ReadXml("queryResult.xml");

Sample SQL script to delete messages in Service Broker Queue manually

SQL Script:

declare @ch uniqueidentifier
while(1=1)
begin
    select top 1 @ch = conversation_handle from [dbo].[YourQueueName]
    if (@@ROWCOUNT = 0)
    break
    end conversation @ch with cleanup
end

Add or ignore where conditions in SQL Server query based on a flag

Assume you want to select data from SQL Server based on a condition. But the condition should be considered only if another condition satisfies else it should return all data. How would you do that. Here is a sample program to achieve the solution.

declare @flagAll = 1;


Select *

from Students S

where @flagAll = 1

or S.Department = 'CSE'

In this example if the @flagAll is set to 1, then the first condition will be satisfied so the second condition will not be executed as it is OR condition, so all data will be returned. Else if @flagAll = 0 then the first condition will be failed, as it is OR condition the second condition S.Department = 'CSE' will be considered and all CSE department Student data will be returned.

If you have alternate solution, Kindly post as comment.

Create custom SQL exception with custom message in C# (CSharp)

Below is a sample code which is used to create a custom SQL exception with custom message. The parameter accepts the message to be thrown. It is mostly used in Unit testing scenario where you need to validate for a custom message.

Sample Code

private SqlException GetSqlException(string message)
{
    SqlErrorCollection collection = Construct();
    SqlError error = Construct(-2, (byte)2, (byte)3, "Server", message, "Prcedure", 100, (uint)1);

    typeof(SqlErrorCollection)
        .GetMethod("Add", BindingFlags.NonPublic | BindingFlags.Instance)
        .Invoke(collection, new object[] { error });

    var e = typeof(SqlException)
        .GetMethod("CreateException", BindingFlags.NonPublic | BindingFlags.Static, null, CallingConventions.ExplicitThis, new[] { typeof(SqlErrorCollection), typeof(string) }, new ParameterModifier[] { })
        .Invoke(null, new object[] { collection, "11.0.0" }) as SqlException;

    return e;
}

private T Construct(params object[] p)
{
    return (T)typeof(T).GetConstructors(BindingFlags.NonPublic | BindingFlags.Instance)[0].Invoke(p);
}