Four statements to increase SQL Server scalability

This article explains how to use the search for left join, CROSS join, and identity values to improve the performance or scalability of a SQL Server based application.

The phenomenon you will encounter: SQL queries in your application do not respond the way you want. It either doesn't return data, or it takes a surprisingly long time. If it lowers the speed of an enterprise application, the user must wait a long time. Users want the application to respond quickly, and their reports can return profiling data in an instant.

In order to solve these problems, it is important to find the root of the problem. So, where do we start? The root cause is usually the database design and the query that accesses it. I'll talk about four technologies that can be used to improve the performance or improve the scalability of a SQL Server based application. I will carefully describe the use of the left join, the CROSS join, and the retrieval of the identity value. Remember, there is no magic solution at all. Adjusting your database and its queries takes time, analysis, and a lot of testing. These technologies have proven to be effective, but for your applications, some of these technologies may be more appropriate than others.

Return IDENTITY from INSERT

I decided to start with a lot of questions: how to retrieve the identity value after executing SQL INSERT. Often, the problem is not how to write a query that retrieves values, but where and when to retrieve them. In SQL Server, the following statement can be used to retrieve the IDENTITY value created by the most recent SQL statement running on the active database connection: SELECT @ @IDENTITY.

This SQL statement is not complicated, but one thing to keep in mind is that if this latest SQL statement is not an INSERT, or if you run this SQL for other connections that are not insert SQL, you will not get the expected value. You must run the following code to retrieve the IDENTITY that is immediately after INSERT SQL and on the same connection as follows:

INSERT INTO Products (ProductName) VALUES ('Chalk')
SELECT @@IDENTITY