Microsoft SQL Server Tips
Use NOLOCK statement in all cases or it should not be used for all cases.
If stored procedure uses parameter-based code block (if/else) and if you use WITH (NOLOCK) in some cases only then it will lead to bad execution.
It should be used in all cases, or it should not be used at all.
SQL Code:
SELECT C.CUSTOMERID, C.CUSTOMERNAME, O.ORDERDATE
FROM [dbo].[CUSTOMER] C WITH (NOLOCK)
INNER JOIN [dbo].[ORDER] O WITH (NOLOCK)
ON O.CUSTOMERID = C.CUSTOMERID
ORDER BY O.ORDERDATE DESC
Use EXISTS () instead of COUNT ()
This SQL optimization technique concerns the use of EXISTS (). If you want to check if a record exists, use EXISTS () instead of COUNT (). While COUNT () scans the entire table. This will give you better performance.
SQL Code:
SELECT COUNT(1) FROM [dbo].[CUSTOMER]
SQL Code:
IF (EXISTS (SELECT 1 FROM [dbo].[CUSTOMER]))
BEGIN
PRINT 'Row Exists'
END
ELSE
BEGIN
PRINT 'No Rows Exists'
END
Encrypt and Decrypt Column Value in SQL Server Table
Encrypt SQL Functions Code:
String Value:
--String Value Encrypt logics
-- SELECT [dbo].[ufn_Encrypt_String]('SAMPLE')
CREATE FUNCTION [dbo].[ufn_Encrypt_String] (@input nvarchar(2000))
RETURNS varbinary(max)
AS
BEGIN
DECLARE @data varbinary(max)
DECLARE @ApplicationName varchar(7) = 'Test123'
IF (LEN(@input) > 0)
BEGIN
SELECT @data = ENCRYPTBYPASSPHRASE(@ApplicationName, @input)
END
ELSE
BEGIN
SET @data = NULL
END
RETURN @data
END
SQL Code:
SELECT First_Name = [dbo].[ufn_Encrypt_String]](C.First_Name) FROM [dbo].[CUSTOMER] C WITH (NOLOCK) -- ENCRYPT First_Name
Decrypt SQL Functions Code:
For String Value:
--String Value Decrypt logics
-- SELECT [dbo].[ufn_Decrypt_String]([dbo].[ufn_Encrypt_String]('SAMPLE'))
CREATE FUNCTION [dbo].[ufn_Decrypt_String] (@input varbinary(max))
RETURNS nvarchar(2000)
AS
BEGIN
DECLARE @data nvarchar(2000)
DECLARE @ApplicationName varchar(7) = 'Test123'
IF (LEN(@input) > 0)
BEGIN
SELECT @data = CONVERT(nvarchar(2000), DECRYPTBYPASSPHRASE(@ApplicationName, @input))
END
ELSE
BEGIN
SET @data = NULL
END
RETURN @data
END
SQL Code:
SELECT First_Name = [dbo].[ufn_Decrypt_String]](C.First_Name) FROM [dbo].[CUSTOMER] C WITH (NOLOCK) -- Decrypt First_Name
NOTE: Make sure your key should be same as in my case ‘Test123’ while Encrypt & Decrypt
Mask Column Value in SQL Server Table
For String Value:
--String Value Mask logics
CREATE FUNCTION [dbo].[ufn_Mask_String] (@input nvarchar(2000))
RETURNS nvarchar(2000)
AS
BEGIN
DECLARE @data nvarchar(2000)
IF (LEN(@input) > 10)
BEGIN
SELECT
@data = STUFF(@input, 1, LEN(@input) - 0, REPLICATE('*', LEN(@input) - 1))
END
ELSE
BEGIN
SET @data = '**********'
END
Recommended by LinkedIn
RETURN @data
END
SQL Code:
SELECT First_Name = [dbo].[ufn_Mask_String](C.First_Name) FROM [dbo].[CUSTOMER] C WITH (NOLOCK) -- MASK First_Name
For DateTime Value:
SQL Code:
SELECT Date_of_Birth = DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 365), C.Date_of_Birth) FROM [dbo].[CUSTOMER] C WITH (NOLOCK) -- RANDOM DATETIME VALUES
How to Update Millions of Records in a SQL table
A simple and efficient way of performing updates on millions of records. The key is to batch out your data to allow you to process a smaller number of records at a time.
Script:
USE DatabaseName
GO
DECLARE @StartTime datetime = GETDATE()
DECLARE @EndTime datetime
DECLARE @Seconds int = 0
DECLARE @batchStartId int
DECLARE @batchEndId int
DECLARE @batchSize int
DECLARE @results int
-- ASSIGN VALUE
SET @batchStartId = 1
SET @batchEndId = 1
SET @batchSize = 10000 -- Batch Size
SET @results = 1
BEGIN TRY
-- To find the end of the record value
SELECT TOP 1 @batchEndId = Id -- Primary Key with Data Type integer Value
FROM Table1
ORDER BY Id DESC
-- when 0 rows returned, exit the loop
WHILE (@results > 0)
BEGIN
BEGIN TRAN;
UPDATE Table1
SET column1 = Table2.column1
FROM Table2
INNER JOIN Table1 ON Table2.Id = Table1.Id
WHERE (LEN(Table1.column1) < 1)
AND (Table2.Id > @batchStartId
AND Table2.Id <= @batchStartId + @batchSize)
SET @results = @@ROWCOUNT
-- next batch
SET @batchStartId = @batchStartId + @batchSize
IF (@results = 0
AND (@batchStartId < @batchEndId))
BEGIN
SET @batchStartId = @batchStartId + 1
SET @results = 1
END
--PRINT @batchStartId
COMMIT TRAN;
END
-- Timing
SET @EndTime = GETDATE()
SET @Seconds = DATEDIFF(SECOND, @StartTime, @EndTime)
PRINT 'Total Time: ' +
CONCAT(FLOOR(@Seconds / 3600 / 24), ' days ',
FLOOR((@Seconds % (3600 * 24)) / 3600), ' hours ',
FLOOR((@Seconds % 3600) / 60), ' minutes ',
@Seconds % 60, ' seconds')
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
AND XACT_STATE() <> 0
DECLARE @ErrorMessage nvarchar(max),
@ErrorSeverity int,
@ErrorState int,
@ErrorNumber int,
@ErrorProcedure nvarchar(max),
@ErrorLine int;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorNumber = ERROR_NUMBER(),
@ErrorProcedure = ERROR_PROCEDURE(),
@ErrorLine = ERROR_LINE();
RAISERROR (@ErrorMessage, -- Message text
@ErrorSeverity, -- Severity
@ErrorState, -- State
@ErrorNumber, --Number
@ErrorProcedure, --Procedure
@ErrorLine --Line
);
END CATCH
NOTE:
Muthukumaramani Santhanamariappan 👏🏻👏🏻
Brilliant