How to use SQL pagination using LIMIT and OFFSET

 How to extract just a portion of a larger result set from a SQL SELECT.

LIMIT n is an alternative syntax to the FETCH FIRST n ROWS ONLY.

The OFFSET clause specifies the number of rows of the result table to skip before any rows are retrieved,
and must be used with the LIMIT clause.

The OFFSET clause instructs the server where to start returning rows within the query result.
For example, if a query returns 100 rows, specifying OFFSET 10 instructs the server to skip
the first 10 rows of the query results:

Why do we need SQL functions when stored procedures can do all what SQL function does?

A function can be used inline in SQL statements while stored procedures cannot.

you cannot call a stored procedure in a SELECT statement. I guess this could be a reason for using functions.

Write a query to Update the Male <->Female in SQL server

 update tableA set Gender = (case gender ='Male' then 'Female' else 'Male' end)

Create Stored Procedure with output parameter and how to call stored procedure with output.

 Create proc with input parameter:

CREATE PROCEDURE Usp_GetEmployee

(

   @EmpId int

)

as

begin

select * from Emp where EmpId=@EmpId

end

 Create proc with Output parameter:

CREATE PROC Usp_OutPutEmp

(

  @EmpId int ,

  @empName varchar output

)

as 

begin

SELECT @empName= EmpName  FROM Emp WHERE EmpId=@EmpId

end

---------------------Call Proc with output Parameter------------

DECLARE @EmpName varchar(30)

exec Usp_OutPutEmp 1,@EmpName output

print @EmpName

How to remove dublicate record in a table

 DELETE P1 FROM ProductId AS P1

INNER JOIN PRODUCTID AS P2 ON

P1.ProductId>P2.ProductId AND P1.PName=P2.PName

Can a TABLE have primary key without Clustered Index? - Yes | Can a TABLE have Clustered Index without primary key? - Yes

 A primary key is a unique index that is clustered by default. By default means that when you create a primary key, if the table is not clustered yet, the primary key will be created as a clustered unique index. Unless you explicitly specify the nonclustered option.

A clustered index stores all columns at the leaf level. That means a clustered index contains all data in the table. A table without a clustered index is called a heap.

SQL Server Function

 STUFF :

The STUFF() function deletes a part of a string and then inserts another part into the string, starting at a specified position.

Note: Also look at the REPLACE() function.

Syntax:

STUFF(string, start, length, new_string)

Example: 

SELECT STUFF('SQL Tutorial!', 13, 1, ' is fun!');

CharIndex()

The CHARINDEX() function searches for a substring in a string, and returns the position.

If the substring is not found, this function returns 0.

Note: This function performs a case-insensitive search.

Syntax:

CHARINDEX(substring, string, start).

Example:

SELECT CHARINDEX('OM', 'Customer') AS MatchPosition