Category Archives: SQL Server

SQL Server

How to get weekdays between two dates.

We are creating a SQL Server Table-value function to get weekdays between two dates. Logic is simple as DatePart(dw,Getdate()) returns integer value of the day. Sunday is equivalent to 1 and Monday is equivalent to 2 and so far. As we know weekdays are from Monday to Friday. So all the days between 2 and Continue Reading…

What is the difference between EXCEPT and NOT IN in SQL Server?

EXCEPT EXCEPT operator must have an equal number of expressions in their target lists. Example : Syntax is easy to write. It will return distinct rows from left part of EXCEPT if right part of the EXCEPT does not match any rows. Example Output : 4 NOT IN NOT IN operator can have different number of expressions in their Continue Reading…

How to remove specific characters from a string in SQL Server?

In this example we are using Patindex and Stuff functions. Patindex is used to find the specific character occurrence position and stuff is used to replace that character position with blank space. Result : Hitesh

Cannot truncate table ‘Table_Name*’ because it is being referenced by a FOREIGN KEY constraint.

If a table is referenced by other table. We can’t truncate this table directly. There are two ways to Truncate records from referenced table. First way Delete all records with delete command. Reset the table identity. Second Way Remove foreign key constraint. Truncate table. Apply foreign key constraint again.

How to recover database when database in Restoring state for a long time.

There are three different ways to recover the database. 1) Recover the database manually with following command. RESTORE DATABASE DataBaseName WITH RECOVERY 2) Recover the database with the log file. RESTORE LOG DataBaseName FROM LogFilePathWithName WITH RECOVERY 3) Recover the database with the bak file. RESTORE DATABASE DataBaseName FROM BakFilePathWithName WITH RECOVERY