Here are some basic tips for the use of SQL SERVER:

 

1. NOCOUNT

SET NOCOUNT = OFF

Don’t count the number of rows affected for big queries with a lot of joins. This will significantly improve performance

 

2. DATEADD

SELECT count(personId) FROM persons WHERE creationDate > DATEADD(DAY, -14, CURRENT_TIMESTAMP);
SELECT count(personId) FROM persons WHERE creationDate > DATEADD(DAY, -14, GETDATE());

The DATEADD function makes date comparisons easy by allowing you to easily specify a date against another one (eg. the current date).

(both CURRENT_TIMESTAMP and GETDATE() return the current as a datetime)

 

3. Use Variables

DECLARE @LastName nvarchar(30), @FirstName nvarchar(20), @CountryCode nchar(2);
SET @LastName = 'lasty';
SET @FirstName = 'firsty';
SET @CountryCode = 'US';

SELECT top(100) Address WHERE CC = @CountryCode;

A variable makes it easy if you use the same parameter in many queries. You only have to change it in one place.

 

4. CASTing

SELECT CAST(25.65 AS int);
SELECT CAST(CURRENT_TIMESTAMP AS DATE);

will return:

25

2020-01-01 (assuming that is today)

 

5.  Begin Transaction and with(nolock)

SELECT * FROM table with(nolock)

Create non blocking queries by stating with(nolock)

 

BEGIN TRANSACTION;
UPDATE MyTable
  SET status = 'NEW'
  WHERE creationDate < DATEADD(DAY, -1, GETDATE());
--commit
--rollback

If you need to be extra sure not to affect any columns that you don’t want affected then use a transaction to make the update.

You will see the number of rows affected and can then commit or rollback