Understanding Microsoft SQL CROSS APPLY and OUTER APPLY Operators

Microsoft SQL has two variants i.e. CROSS APPLY and OUTER APPLY. Both operators are in SQL used to perform table-valued function /correlated subqueries against rows in another table.

CROSS APPLY

  • It is similar to an INNER JOIN i.e. It returns rows from the left table that have a matching row in the right table.
  • No rows in the left table are excluded, even if there’s no match in the right side. However, for those rows, the columns from the right side will be NULL.
  • You can use CROSS APPLY when you need data where there’s a match on both sides, similar to an INNER JOIN.

OUTER APPLY

  • It is similar to a LEFT JOIN i.e. It returns all rows from the left table regardless of whether there’s a matching row in the right side.
  • It retrieves all records from both sides, even if there’s no match.
  • Use OUTER APPLY when you want all rows from the left table, even if there’s no corresponding data in the right table (like a LEFT JOIN).
  • If a simple JOIN can achieve the same result, it’s generally preferred for better performance.

Points to consider:

  • Both APPLY operators can be used with table-valued functions and correlated subqueries.
  • APPLY operators often offer more flexibility than JOINs, but can be less performant.

SQL – Information of TRY, CATCH and Error Handling

In SQL Deadlocks are very crucial things which we need to handle with ease, So for this features, You can do whatever if any error occur.
@@ERROR is not used for every statement executed then.
TRY and CATCH helps to write logic and error handling code differenly.
Whatever code need to execute you can write in TRY block and if any error occur from code block then you can handle in CATCH block.
In case the code within the TRY block fails,
the control automatically jumps to the CATCH block, letting the transaction roll back and resume execution.
Also in CATCH block it provide information related to Error ID, message, state of error and transaction state.

Below are the Functions used in CATCH block :

ERROR_NUMBER: returns the error number, and is the same value of @@ERROR.
ERROR_SEVERITY: returns the severity level of the error that invoked the CATCH block.
ERROR_STATE: returns the state number of the error.
ERROR_LINE: returns the line number where the error occurred.
ERROR_PROCEDURE: returns the name of the stored procedure or trigger for which the error occurred.
ERROR_MESSAGE: returns the full message text of the error. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.
You can use these functions anywhere inside a CATCH block, and they will return information regarding the error that has occurred. These functions will return the value null outside of the CATCH block.

Syntax:
BEGIN TRY
{
SQL Code Block
}
END TRY
BEGIN CATCH
{
SQL Block
}
END CATCH

The TRY or CATCH block can contain a single T-SQL statement or a series of statements.
The CATCH block must follow immediately after the TRY block.
The TRY/CATCH block cannot span more than a single batch. In addition, TRY/CATCH block cannot span an IF/ELSE statement.

Example of TRY…CATCH:

BEGIN TRY
DECLARE @X INT
—- String to binary convertion error
SET @X = ‘1X’
PRINT ‘TRY block’
END TRY
BEGIN CATCH
PRINT ‘Error Occured : ‘+ ERROR_MESSAGE()
END CATCH
PRINT ‘After Completion TRY CATCH blocks’

Above code will return following result:

Error Occured
After Completion TRY CATCH blocks

If all the statements within the TRY block are executed successfully, then processing does not enter the CATCH block,
but instead skips over the CATCH block and executes the first statement following the END CATCH statement.

SQL 2016 Features News

1] Stretch Database

Stretch Database allows you to keep your most recent production data local to your location but migrate the historical data to the cloud(Microsoft Azure). This new feature gives you the benefit of presenting the data as a single data store while behind the scenes allowing you the flexibility to segregate active and historical data helping to reduce cost of storage, backups, and help speed up access to “Live” data.

2] Basic Availability Groups

Basic Availability Groups replaced Database Mirroring technology. This feature is available in Standard Edition giving you the ability to have a single primary and single replica database.

3] Distributed Availability Groups

Another new feature in SQL 2016 is the ability to have availability groups defined on two different Windows Clustered. This new feature allows available groups to exist in both the production environment as well as your disaster recovery (DR) environment. If connectivity to your DR site is interrupted, it will not affect your internal availability groups.

4] Query Store

You can now compare differences in your query plans over time without the need to create your own custom solution. SQL Server now stores query plans with executed queries and runtime statistics to make performance troubleshooting faster and easier.

5] Live Query Statistics

Gone are the days of viewing only the estimated execution plan or waiting for that long-running query to finish to see the actual execution plan. SQL Server now gives you the ability to view the execution plan as the query is active on the system.

6] In-Memory OLTP

First introduced in SQL 2014, In-Memory OLTP received several significant updates in SQL 2016. The ALTER TABLE command received a performance update to minimize the number of log writes to help reduce overall IO usage. Stats are now updated automatically. Additionally, several DDL and DML commands were added to your arsenal including Index on null values, foreign keys and DML triggers.

7] Changes to TempDB

New with 2016, when installing SQL Server, the installation wizard will detect the number of CPUs on the system and automatically populated the number of TempDB datafiles recommended to support the number of CPUs. The new wizard gives you more flexibility on configuring drive locations of the TempDB files. On high performance systems, Trace flags -T1117 and -T1118 no longer need to be set.

SQL – Index Rebuild / Index Reorganize

Index Rebuild will drop the existing Index and it will recreates the index.

ALTER INDEX index_name ON databasename.tablename REBUILD

Index Reorganize physically reorganizes the leaf nodes of the index.

ALTER INDEX index_name ON databasename.tablename REORGANIZE

Index should be rebuild when index fragmentation is most of the time great than 40%. Index should be reorganized when index fragmentation is between 10% to 40%.
Index rebuilding process uses more CPU and it acquired locking in database resources. In latest SQL Server version and Enterprise version there has option available i.e. ONLINE mode, through which you can be turned on when Index is rebuilt.
ONLINE option will keep index available during the rebuilding.

SQL – Search Text in Stored Procedure

There is below few questions which will ask you while Interview or Any time …
How can I find if particular table is being used in the stored procedure?
How to search in stored procedures?
How can I do dependency check for objects in stored procedure without using sp_depends?

The same feature can be implemented using following script.

–Searching the text or tableName in Stored Procedure
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE ‘%tableName or any text%’

SQL – Temp Table v/s Temp Variable

Temp Tables are originated for the storage and it’s manipulation of temporal data. Also its stored physically created in tempDB.
Temp Variables are originated for returning date-sets from table-valued functions.


Temp tables might result in stored procedures being recompiled, perhaps often. Table variables will not.


Temp Table create using SELECT INTO, which can be quicker to write (good for ad-hoc querying) and may allow you to deal with changing datatypes over time, since you don’t need to define your temp table structure upfront.
Global Temp Tables (##tmp) are another type of temp table available to all sessions and users.


Temp table result can be used by multiple users.
Table variable can be used by the current user only. 

Temp table can do all the DDL operations. It allows creating the indexes, dropping, altering, etc..,
Table variable won’t allow doing the DDL operations. But the table variable allows us to create the clustered index only.

Identify Locking SPs

In a previous section we learn about how to get Blocking SPs, In Todays section we seen how to identify locking .

Locking is integral part of successful transactions of RDBMS. It is important in SQL Server that transactions pass the ACID test:

  • Atomic – the transaction performs in an all-or-nothing fashion
  • Consistent
  • Isolated – transactions are properly isolated until they’re finished
  • Durable – the RDBMS will maintain a record of uncompleted transactions in the event of recovery during a failure. 

SQL – Unique Index and Unique Constraint

Unique Index and Unique Constraint both are the same in terms of working in Tables. They achieve same goal. Performance wise also both are same.

—Unique Constraint
ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE NONCLUSTERED(column_names) ON [PRIMARY]

—Unique Index
CREATE UNIQUE NONCLUSTERED INDEX index_name ON table_name
(column_name) ON [PRIMARY]

In both syntax are different the effect is the same. Unique Constraint creates Unique Index to maintain the constraint to prevent duplicate keys. Unique Index or Primary Key Index are physical structure that maintain uniqueness over some combination of columns across all rows of a table. It is a convenient way to enforce a Unique Constraint for SQL Server.

How to Delete Duplicate Records from Table

Below query is useful to delete the duplicate Ids data from table. The table must have identity column, which will be used to identify the duplicate records.

CREATE TABLE StudentDetail(ID INT IDENTITY(1,1),StudentID INT,Name VARCHAR(10),Address VARCHAR(100))

INSERT INTO StudentDetail
SELECT 10001,’Test1′,’Mumbai’
INSERT INTO StudentDetail
SELECT 10002,’Test2′,’Mumbai’
INSERT INTO StudentDetail
SELECT 10003,’Test3′,’Mumbai’
INSERT INTO StudentDetail
SELECT 10004,’Test4′,’Mumbai’
INSERT INTO StudentDetail
SELECT 10005,’Test5′,’Mumbai’
INSERT INTO StudentDetail
SELECT 10001,’Test11′,’Mumbai’
INSERT INTO StudentDetail
SELECT 10005,’Test51′,’Mumbai’

;WITH CTE
AS
(
SELECT ID,ROW_NUMBER()OVER(PARTITION BY StudentID ORDER BY ID ASC)DuplID
FROM StudentDetail
)
DELETE FROM CTE WHERE DuplID > 1

SELECT * FROM StudentDetail;

Design a site like this with WordPress.com
Get started