June 12, 2024

How to insert deleted identity row in SQL Server.

Naren June 12, 2024 0Comment

To insert a deleted identity row back into a SQL Server table while preserving the specific identity value, you need to temporarily enable IDENTITY_INSERT for the table.

Enable IDENTITY_INSERT on the table.
Insert the row with the specific identity value.
Disable IDENTITY_INSERT on the table.

Assume you have a table named td_employee with an identity column EmployeeID and other columns like FirstName, LastName, and Email.

Note:

Ensure Unique Identity Values: Make sure the identity value you’re inserting does not already exist in the table, as this would cause a primary key violation.
Performance Impact: Avoid keeping IDENTITY_INSERT on for extended periods, as it can affect performance and concurrency.
Permissions: You need appropriate permissions to enable/disable IDENTITY_INSERT and perform inserts.

Explanation:

SET IDENTITY_INSERT [TableName] ON: This command allows you to explicitly insert values into the identity column of the table.
INSERT INTO [TableName] ([ColumnList]) VALUES ([Values]): This is the standard SQL insert statement, but with the identity column explicitly included in the column list.
SET IDENTITY_INSERT [TableName] OFF: This command disables the ability to insert explicit values into the identity column, returning control of the identity column to SQL Server.

By following these steps, you can reinsert a deleted row with its original identity value, ensuring data integrity and continuity in your table.

SQL Tech

How to remove duplicate data in SQL server.

Naren June 12, 2024 0Comment

For remove duplicate data in SQL Server, you can use various approaches. Below, I’ll outline a method using Common Table Expressions and the ROW_NUMBER() function, which is a commonly used and effective method to assign unique number to each row.

Identify Duplicates:

Use the ROW_NUMBER() function to assign a unique identifier to each row within a partition of duplicates.

Delete the Duplicates:

Keep the first occurrence (based on some ordering, usually a primary key id column) and delete the rest.

Example:

Assume you have a table named test123 with columns id, FirstName, LastName, and Email, and Mobileno you want to remove duplicates based on Mobileno.

SQL Script

Here is a complete SQL script to achieve this:

Explanation:

Identify Duplicates with a CTE:
- The CTE uses ROW_NUMBER() to assign a unique number to each row within partitions defined by Mobileno.
- The PARTITION BY clause groups rows that are considered duplicates.
- The ORDER BY clause ensures the row with the smallest id gets RowNumber = 1.
Delete the Duplicates:

The DELETE statement removes rows where RowNumber is greater than 1, effectively keeping only the first occurrence of each set of duplicates if want to take latest occurrence so we can use order by id desc.

Additional Considerations:

Back Up Data: Before performing delete operations, always back up your data to prevent data loss.
Take a complete backup of table by using below script to prevent data losses .
select * into table_name from table

Following this method, you can efficiently remove duplicate rows from your SQL Server table while keeping your data consistent and clean.

SQL Tech

How to create a linked server using SQL Server Management Studio (SSMS) and T-SQL.

Naren June 12, 2024 0Comment

To Creating a linked server in SQL Server allows you to access external data sources such as other SQL Server instances, or other OLE DB data sources(e.g MYSQL,ORACLE,PLSQL etc). Here is a step-by-step guide on.

1.Using SQL Server Management Studio (SSMS):

Open SQL Server Management Studio (SSMS).
Connect to the SQL Server instance where you want to create the linked server.
In the Object Explorer, expand the Server Objects .
Right-click on Linked Servers and select New Linked Server.
In the New Linked Server dialog box:

Linked server: Enter the name of the linked server.
Server type: Select SQL Server or Other data source depending on the type of external data source.
Data Source: Enter the name or network address of the SQL Server instance you want to link to.

For Other Data Source:

Provider: Select the appropriate OLE DB provider.
Product name: Enter the name of the product.
Data source: Enter the network name of the external data source.
Provider string: Optional connection string specific to the provider.
Location: Optional location of the data.
Catalog: The initial database to connect to.

Security Page:

Configure the security settings for the linked server.
You can choose to impersonate a login or specify remote login and password.
Click OK to create the linked server.
Right click on newly created linked server and test connection if success then connection created sucessfully else failed.

2.Using T-SQL:

Here is an example T-SQL script to create a linked server:

Linked server configuration:
EXEC sp_addlinkedserver
@server = 'test',
@srvproduct = '',
@provider = 'provider name',
@datasrc = 'Remote server name or ip address';

Linked server security configuration:
EXEC sp_addlinkedsrvlogin 
    @rmtsrvname = 'test', 
    @useself = 'false', 
    @locallogin = NULL, 
    @rmtuser = 'sa', 
    @rmtpassword = '*****';
Verify linked server:
SELECT * FROM sys.servers WHERE name = 'test';

Notes:

Ensure the appropriate OLE DB(32 bit or 64 bit) provider is installed and configured on the SQL Server instance ..
For security reasons, consider creating a SQL Server login with minimal privileges for use with the linked server.
Test the linked server connection to ensure it works correctly(Right click and test connection).

By following these steps, you can create and configure a linked server in SQL Server, allowing you to query and interact with external data sources directly from your SQL Server session.

SQL Tech

How can I save a SQL Server Profiler trace and read it using a T-SQL command?

Naren June 12, 2024 0Comment

1.Save the Trace File:

Open SQL Server Profiler.Configure the trace by setting the events and filters as needed.Start the trace.Save the trace results to a file by selecting File > Save As > Trace File.Provide a file name xyz and save it.

2.Read the Trace File Using T-SQL:

Use the fn_trace_gettable function to read the trace file.

Here is an example T-SQL command to read the saved trace file: