Here are some basic tips for the use of SQL SERVER:
SET NOCOUNT = OFF
Don’t count the number of rows affected for big queries with a lot of joins. This will significantly improve performance
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.
SELECT CAST(25.65 AS int); SELECT CAST(CURRENT_TIMESTAMP AS DATE);
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