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 6(including these two) are weekdays. Here is the code for the same.

Create function dbo.getWeekDays(@startdate date, @enddate date)
returns  @temptable TABLE (weekDays varchar(100),Name Varchar(100)) 
As
Begin
     Declare @weekday date
	 While  @startdate <= @enddate
	 Begin
	    IF DATEPART(dw,@startdate) between 2 and 6
		Begin
		   Insert into @temptable(weekDays,Name) Values(@startdate,DATENAME (DW, @startdate))
		End
		SET @startdate = DATEADD(DAY,1,@startdate)
	 End
	 Return
End


Query to get weekdays

SELECT weekDays,Name from dbo.getWeekDays ('01/01/2016', '01/31/2016')

How to truncate all tables in SQL Server.

To truncate all tables in SQL Server we need to create dynamic script.

DECLARE @TableName AS VARCHAR(1000) 
DECLARE @Script AS VARCHAR(1000) 
IF OBJECT_ID('tempdb.dbo.#AllTables') IS NOT NULL
    DROP TABLE #AllTables
 
SELECT name, ROW_NUMBER() over (order by name) as RNo
INTO #AllTables
FROM sys.tables

Declare @TotalTableCount int
Set @TotalTableCount =(Select Count(1) from #AllTables)
Declare @Counter int=1
WHILE (@Counter < @TotalTableCount)
    BEGIN
        SELECT @TableName = name
        FROM   #AllTables Where RNo = @Counter
        SELECT @Script = 'truncate table ' + @TableName;        
        EXECUTE (@Script)
        Set @Counter += 1
    END

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 :

    Select EmpID from #Student
    Except
    Select EmpID from #CourseStudent
    

  • 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

Create table #Student(
EmpID int,
EmpName varchar(100)
)

Create table #CourseStudent
(
  Course Varchar(100),
  EmpID int
)

Insert into #Student(EmpID,EmpName)
Select 1,'Hitesh' Union
Select 2,'Priyanka' Union
Select 3,'Rahul' Union
Select 4,'Abhimanyu' 

Insert into #CourseStudent(Course,EmpID)
Select 'Dot Net',1 Union
Select 'English',2 Union
Select 'Java',3 

//Except query
Select EmpID from #Student
Except
Select EmpID from #CourseStudent


Output : 4
NOT IN

  • NOT IN operator can have different number of expressions in their target lists.
    Example :

    Select EmpID,EmpName from #Student
    Where EmpID not in(
    Select EmpID from #CourseStudent)
    

  • Syntax is some difficult as compare to Except to write.

Output : 4
Note : EXCEPT and NOT IN both have same performance cost.

How to get max value in a row in SQL Server?

create table #Rpt
(
  iID  int identity(1,1),
  salary int,
  age int
)

insert into #Rpt values(100,20)
insert into #Rpt values(100,120)
insert into #Rpt values(120,20)

select * from #Rpt

Select (Select Max(P) from (Values (salary),(age)) as value(P)) As maxValue from #Rpt

Output :
maxValue
100
120
120

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.

DECLARE @RemoveChar VARCHAR(50),
@String_Exp VARCHAR(500) = 'Hi123t4e5s6h'

SET @RemoveChar = '%[0-9]%' -- Remove All Integer

-- SET @RemoveChar = '%[a-z]%'  Remove All Alphabets

WHILE PATINDEX(@RemoveChar, @String_Exp) > 0
SET @String_Exp = STUFF(@String_Exp, PATINDEX(@RemoveChar, @String_Exp), 1,'')

Select @String_Exp


Result : Hitesh

How to get comma separated values from table rows in SQL Server.

Create table #Employee
(
  EmpId int,
  EmpName Varchar(100),
  Department Varchar(100)
)

Insert into #Employee(EmpId,EmpName,Department)
Select 1,'Hitesh','Csharp' Union
Select 2,'Rahul','Java' Union
Select 3,'Ravi','Java' Union
Select 4,'Priyanka','English' Union
Select 5,'Dev','Csharp'

Select Department,
STUFF((SELECT ', ' + CAST(EmpName AS VARCHAR(1000))
         FROM #Employee
         WHERE Department = t.Department
         FOR XML PATH(''), TYPE)
        .value('.','VARCHAR(MAX)'),1,2,' ') EmpName
FROM #Employee t
Group by Department

Drop table #Employee

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 get Referenced Table and Column Name in SQL Server.

Query to get Referenced Table, Referring Table, Foreign Key, Referenced Column, Referring Column

SELECT OBJECT_NAME (FK.referenced_object_id) 'Referenced Table', 
OBJECT_NAME(FK.parent_object_id) 'Referring Table', 
FK.name 'Foreign Key', 
COL_NAME(FK.referenced_object_id, FKC.referenced_column_id) 'Referenced Column',
COL_NAME(FK.parent_object_id,FKC.parent_column_id) 'Referring Column'
FROM sys.foreign_keys AS FK
    INNER JOIN sys.foreign_key_columns AS FKC 
        ON FKC.constraint_object_id = FK.OBJECT_ID
WHERE OBJECT_NAME (FK.referenced_object_id) = 'Table_Name'


Note : Table_Name is the table name

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