T-SQL count work days between two dates

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = ‘2022/08/01’
SET @EndDate = ‘2022/08/25’

SELECT
(DATEDIFF(dd, @StartDate, @EndDate) + 1)
-(DATEDIFF(wk, @StartDate, @EndDate) * 2)
-(CASE WHEN DATENAME(dw, @StartDate) = ‘Sunday’ THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, @EndDate) = ‘Saturday’ THEN 1 ELSE 0 END)

SQL script delete duplicate rows in the table

In the data world, duplicated data is a very common issue. It may duplicated for few fields or even worse you have more then two identical rows in the table and there is no way to distinguish those rows. So how to clean those duplicated data?

DELETE Duplicate Records Using TOP

Since SQL Server 2005 we can use the TOP command when we issue the delete, such as the following.

CREATE TABLE dbo.duplicateTest (
	[id] [INT] NULL,
	[name] [VARCHAR](100) 
)

INSERT INTO dbo.duplicateTest VALUES (1, 'a')
INSERT INTO dbo.duplicateTest VALUES (1, 'a')
INSERT INTO dbo.duplicateTest VALUES (1, 'a')
INSERT INTO dbo.duplicateTest VALUES (2, 'b')
INSERT INTO dbo.duplicateTest VALUES (3, 'c')

DECLARE @id int
WHILE EXISTS (SELECT COUNT(id) FROM dbo.duplicateTest  GROUP BY id HAVING COUNT(id) > 1 ) 
BEGIN
	SELECT TOP 1  @id = id
	FROM dbo.duplicateTest
	GROUP BY id 
	HAVING COUNT(id) > 1 

	DELETE TOP(1) FROM dbo.duplicateTest WHERE id =@id 
END

So as you can see using the top in while loop we can remove multiple duplicated records even you are not sure which id is duplicated.

DELETE Duplicate Records Using CTE

another way we can use to remove duplicated records is by using CTE.

TRUNCATE TABLE dbo.duplicateTest
INSERT INTO dbo.duplicateTest VALUES(1, 'Bob') 
INSERT INTO dbo.duplicateTest VALUES(2, 'Dave') 
INSERT INTO dbo.duplicateTest VALUES(3, 'Karen') 
INSERT INTO dbo.duplicateTest VALUES(1, 'Bob')
INSERT INTO dbo.duplicateTest VALUES(1, 'Bob')	


WITH cte1
AS (
   SELECT ROW_NUMBER() OVER ( PARTITION BY ID ORDER BY ID) rank1,*
   FROM dbo.duplicateTest 
)
DELETE FROM cte1 WHERE rank1 > 1

What is the difference between Clustered and Non-Clustered Indexes in SQL Server?

Index is used to speed-up query performance in SQL Server. It is similar to book or dictionary indexes. In the book, if you are looking for some particular chapter, you can looking the index and get the page number of the chapter then go directly to that page. Without index, it will take long time for you to find the desired chapter.

The similar logic for the database index. Well-designed indexes can reduce disk I/O operations and consume fewer system resources therefore improving query performance. In SQL Server table can have two types of indexes:

  • Clustered Indexes
    Clustered index is as same as dictionary where the data is arranged by alphabetical order. The order clustered indexes stored the table or view data rows in the table is the order of key values order. Each table can have 1 clustered indexes only, because it can have one kind of order in the same. In SQL Server, the primary key constraint automatically creates a clustered index on that particular column.


    Example of clustered index

CREATE DATABASE schooldb
CREATE TABLE student
(
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
gender VARCHAR(50) NOT NULL,
DOB datetime NOT NULL,
total_score INT NOT NULL,
city VARCHAR(50) NOT NULL
)

The primary key of column ‘id’ will become clustered index automatically. You use system procedure “sp_helpindex” to see the indexes of the table.

USE schooldb
EXECUTE sp_helpindex student

Another way to view table indexes is by going to “Object Explorer-> Databases-> Database_Name-> Tables-> Table_Name -> Indexes”.

Non-clustered Indexes
Non-Clustered Index is similar to the index of textbook. The index of textbook listed the chapter name and related page number, you can directly go to the page by using the page number of the chapter if you are looking for some particular one.

The data and index are stored in different place. Therefore you can have multiple non-clustered index for each table, all the index will point to the same storage place.

To create a new clustered Index, execute the following script:

use schooldb
CREATE CLUSTERED INDEX IX_tblStudent_Gender_Score
ON student(gender ASC, total_score DESC)

Differences

differences between clustered and non-clustered indexes.

  1. Can only have one clustered index per table. However, We can create multiple non-clustered indexes on the same table.
  2. Clustered indexes only sort tables. Therefore, they do not consume extra storage. Non-clustered indexes are stored in a separate place from the actual table claiming more storage space.
  3. Clustered indexes are faster than non-clustered indexes since they don’t involve any extra lookup step.
  4. In clustered index, the clustered key defines order of data within table. In non-clustered index, the index key defines the order of data in the index only.

SQL : Finding rows that have changed in Table with CHECKSUM, BINARY_CHECKSUM, HASHBYTES

CHECKSUM function returns the checksum value computed over a table row, or over an expression list.

BINARY_CHECKSUM returns the binary checksum value computed over a row of a table or over a list of expressions.

HASHBYTES returns the MD2, MD4, MD5, SHA, SHA1, or SHA2 hash of its input in SQL Server.For SQL Server 2014 (12.x) and earlier, allowed input values are limited to 8000 bytes. Beginning with SQL Server 2016 (13.x), all algorithms other than SHA2_256, and SHA2_512 are deprecated.

BINARY_CHECKSUM and CHECKSUM return different values for the string data types, where locale can cause strings with different representation to compare as equal. 

However, CHECKSUM and BINARY_CHECKSUM  cannot is not guaranteed the values in the expression list is changed. It’s better to use HASHBYTES instead. 

Examples

If we have an incoming row of data (let’s say @column1, @column2, @column3, @column4) and I want to know if the incoming values match the ones already in the table and update the table only if they are different, what’s the best way to do that/

You might think that’s easy and you’d just add a WHERE clause to your UPDATE like:

Where Column1 = @column1
and Column2 = @column2
and Column3 = @column3
and Column4 = @column4

If you have large number of columns then you can imagine what this looks like. In addition, you may need to handle the the NULL value if column are nullable like this:

( ISNULL(Column1,'') = ISNULL(@column1,'')

The easier way to do this is by adding one more column that represents a checksum or hash value for all the columns then just compare that one field. The remain question is about which checksum or hashing function to use. Based on the information I mentioned in the above, the checksum and binary_checsum cannot guarantee the result is correct. Therefore hashbytes is the better options to do this job.

HASHBYTES('SHA2_256',
CONCAT(@column1, '|',
@column2, '|',
@column3, '|',
@column4,'|' ))

Tips: add a separator ‘|’ character between columns to allow for empty strings

SQL Server : IDENT_CURRENT,@@IDENTITY, and SCOPE_IDENTITY

SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions because they return values that are inserted into identity columns. However, the scope and session on which last is defined in each of these functions differ:

  • IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. It returns the last identity value generated for a specific table in any session and any scope.
  • @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.
  • SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

Failed statements and transactions can change the current identity for a table and create gaps in the identity column values. The identity value is never rolled back even though the transaction that tried to insert the value into the table is not committed. For example, if an INSERT statement fails because of an IGNORE_DUP_KEY violation, the current identity value for the table is still incremented.

Examples

The following example shows the different identity values that are returned by IDENT_CURRENT@@IDENTITY, and SCOPE_IDENTITY

USE test;
GO
IF OBJECT_ID(N’t6′, N’U’) IS NOT NULL
DROP TABLE t6;
GO
IF OBJECT_ID(N’t7′, N’U’) IS NOT NULL
DROP TABLE t7;
GO
CREATE TABLE t6(id INT IDENTITY);
CREATE TABLE t7(id INT IDENTITY(100,1));
GO
CREATE TRIGGER t6ins ON t6 FOR INSERT
AS
BEGIN
INSERT t7 DEFAULT VALUES
END;
GO
–End of trigger definition

SELECT id FROM t6;
–IDs empty.

SELECT id FROM t7;
–ID is empty.

–Do the following in Session 1
INSERT t6 DEFAULT VALUES;
SELECT @@IDENTITY;
/*Returns the value 100. This was inserted by the trigger.*/

SELECT SCOPE_IDENTITY();
/* Returns the value 1. This was inserted by the
INSERT statement two statements before this query.*/

SELECT IDENT_CURRENT(‘t7’);
/* Returns value inserted into t7, that is in the trigger.*/

SELECT IDENT_CURRENT(‘t6’);
/* Returns value inserted into t6. This was the INSERT statement four statements before this query.*/

— Do the following in Session 2.
SELECT @@IDENTITY;
/* Returns NULL because there has been no INSERT action up to this point in this session.*/

SELECT SCOPE_IDENTITY();
/* Returns NULL because there has been no INSERT action up to this point in this scope in this session.*/

SELECT IDENT_CURRENT(‘t7’);
/* Returns the last value inserted into t7.*/

ISNULL, NULLIF, and COALESCE

The ISNULL function and the COALESCE expression have a similar purpose but ISNULL can have two parameters only, COALESCE can have multiple input.

Both are checking the parameters in order and returns the current value of the first expression that initially doesn’t evaluate to NULL.

SELECT ISNULL(hourly_wage * 40 * 52, salary) AS income
FROM dbo.wages

SELECT COALESCE(hourly_wage * 40 * 52, salary, commission) AS income
FROM dbo.wages

The NULLIF function returns a NULL value if the two parameters are equal. for instance, we could use NULLIF if we wanted to return NULLs whenever the field equal giving value:

SELECT NULLIF(4,4) AS Same, NULLIF(5,7) AS Different;

SQL Placeholder and Composite Format String

The msg_str argument in RAISERROR (Transact-SQL) described the structure of message string and the use of parameters in the string. It’s very useful if we want to build dynamic text or string with multiple parameters. In this article, we are listed variety of format sample.

Conversion specifications format

% [[flag] [width] [. precision] [{h | l}]] type

flag

Is a code that determines the spacing and justification of the substituted value.

CodePrefix or justificationDescription
– (minus)Left-justifiedLeft-justify the argument value within the given field width.
+ (plus)Sign prefixPreface the argument value with a plus (+) or minus (-) if the value is of a signed type.
0 (zero)Zero paddingPreface the output with zeros until the minimum width is reached. When 0 and the minus sign (-) appear, 0 is ignored.
# (number)0x prefix for hexadecimal type of x or XWhen used with the o, x, or X format, the number sign (#) flag prefaces any nonzero value with 0, 0x, or 0X, respectively. When d, i, or u are prefaced by the number sign (#) flag, the flag is ignored.
‘ ‘ (blank)Space paddingPreface the output value with blank spaces if the value is signed and positive. This is ignored when included with the plus sign (+) flag.

width

Is an integer that defines the minimum width for the field into which the argument value is placed. If the length of the argument value is equal to or longer than width, the value is printed with no padding. If the value is shorter than width, the value is padded to the length specified in width.

An asterisk (*) means that the width is specified by the associated argument in the argument list, which must be an integer value.

precision

Is the maximum number of characters taken from the argument value for string values. For example, if a string has five characters and precision is 3, only the first three characters of the string value are used.

For integer values, precision is the minimum number of digits printed.

An asterisk (*) means that the precision is specified by the associated argument in the argument list, which must be an integer value.

{h | l} type

Is used with character types d, i, o, s, x, X, or u, and creates shortint (h) or longint (l) values.

Type specificationRepresents
d or iSigned integer
oUnsigned octal
sString
uUnsigned integer
x or XUnsigned hexadecimal

Examples

string format

SELECT FORMATMESSAGE(‘Hello %s!’, ‘TEST’);
SELECT FORMATMESSAGE(‘Hello %20s!’, ‘TEST’);
SELECT FORMATMESSAGE(‘Hello %-20s!’, ‘TEST’);
SELECT FORMATMESSAGE(‘Hello % 20s!’, ‘TEST’);
SELECT FORMATMESSAGE(‘Hello %5.3s!’, ‘TEST12345678’);

number format

SELECT FORMATMESSAGE(‘Signed int %i, %d %i, %d, %+i, %+d, %+i, %+d’, 5, -5, 50, -50, -11, -11, 11, 11);
SELECT FORMATMESSAGE(‘Signed int with up to 3 leading zeros %03i’, 5);
SELECT FORMATMESSAGE(‘Signed int with up to 20 leading zeros %020i’, 5);
SELECT FORMATMESSAGE(‘Signed int with leading zero 0 %020i’, -55);
SELECT FORMATMESSAGE(‘Bigint %I64d’, 3000000000);
SELECT FORMATMESSAGE(‘Unsigned int %u, %u’, 50, -50);
SELECT FORMATMESSAGE(‘Unsigned octal %o, %o’, 50, -50);
SELECT FORMATMESSAGE(‘Unsigned hexadecimal %x, %X, %X, %X, %x’, 11, 11, -11, 50, -50);
SELECT FORMATMESSAGE(‘Unsigned octal with prefix: %#o, %#o’, 50, -50);
SELECT FORMATMESSAGE(‘Unsigned hexadecimal with prefix: %#x, %#X, %#X, %X, %x’, 11, 11, -11, 50, -50);

How to set up mail notification for SQL Server job failures

When we set up SQL Server Job, its is a very common requirement ‘need to get email notification once Job is failed’. It’s not difficult, just need few clicks in SQL Server Management Studio to implement this requirement.

Overview Steps

  1. Configure Database Mail.
  2. Set up SQL Server Agent Alert System .
  3. Create an Operator.
  4. Set up SQL Server job notifications

Configuring Database Mail

Open SQL Server Management Studio (SSMS), expand Management and right-click on Database Mail and then click Configure Database Mail.

SQL Server Management Studio
Database Mail Configuration Wizard

Select “Set up Database Mail by performing the following tasks” and Click Next.

Click Yes and then click Next if mail hasn’t been configured yet.

Give the New Profile a name and then click Add.

create new profile

Fill in the correct SMTP information as below

click Next

New Profile

Check the option to make the new profile Public. If the profile isn’t set to Public

Manage Profile Security

Review the System Parameters , please keep the default settings if your are not sure how to adjust them. Click Next and Finish the Mail set up.

Configure system parameters

You should see that each Action was completed and has a Status of Success. Click Close.

Configuration Complete Test

Set up SQL Server Agent Alert System

Right-click SQL Server Agent and select Properties.

SQL Server Agent Properties

Click on Alert System

· Under Mail session, select the option to Enable mail profile. Ensure the correct Mail profile is selected.

· Under Token replacement, enable Replace tokens for all job responses to alerts.

Click OK. Restart the SQL Server Agent service.

Create an Operator

Under the SQL Server Agent, right-click Operators and select New Operator…

SQL Server Agent Operator

Type in the recipient email address in the E-mail name and click OK.

Set up SQL Server job notifications

Expand SQL Server Agent and click on Jobs. Find the job you want to change, right click it and select Properties.

Under Select a page, select Notifications. Enable the first option, E-mail. Then select the Operator we just created and change the job to email When the job fails. Click OK.

How To Get Day Of Week In SQL Server?

SQL Server has a couple of inbuilt functions to get the day of week from the given date. To get the name of the day of week, you can use DATENAME function and to get the number of the day of week, you can use DATEPART function.

Example

SELECT DATENAME(WEEKDAY, GETDATE())
/* Result */
Monday

SELECT DATEPART(WEEKDAY, GETDATE())
/* Result */
2

By default, SQL server always uses Sunday as the first day of the week. You can use SET DATEFIRST 1 to set Monday as the first day of the week.

Why left join not returning nulls?

Issue

I am trying to get all products and category name if category is still active with below left join script. But I cannot get production information if category is not active. What’s wrong with this left join script?

select p.name, c.name
from products p
left join categories c on p.categoryid = c.categoryid
where c.isactive = 1 or c.isactive is null

Reason

To find out what’s the issue of this script have, we need to understand order of a SQL query. In the script above we used SELECT, FROM, LEFT JOIN and WHERE

  1. FROM and JOIN: The FROM clause, and subsequent JOINs are first executed to determine the total working set of data that is being queried
  2. WHERE: Once we have the total working set of data, the WHERE constraints are applied to the individual rows, and rows that do not satisfy the constraint are discarded.
  3. SELECT: Any expressions in the SELECT part of the query are finally computed.

The WHERE clause is execution after the ON clause. which means the predicates in the ON clause are applied to the table before the join, WHERE clause is semantically applied to the result of the join.

So below script will get all the data from product table and will keep categories data as null if cannot find category.

select p.name, c.name
from products p
left join categories c on p.categoryid = c.categoryid

WHERE clause is applied to the JOIN result which will remove products result if category missing.

where c.isactive = 1 or c.isactive is null

Solution

if we want to get all product information even if categories is missing or is not active, we need to changed the WHERE clause to ON clause as below:

select p.name, c.name
from products p
left join categories c on p.categoryid = c.categoryid
and (c.isactive = 1 or c.isactive is null )