Some Important Queries in SQL Server

Below are some difficult queries in SQL Server

SQL Main Queries using SQL Operators

Between Operation

Get records using between dates

SELECT *

FROM TableName

where   CAST(Date as Date) between '2013-01-22' and '2014-05-22' order by Date desc

Get Records using between Id

SELECT *

FROM TableName

where   ID between 24 and 55 order by id desc

IN Operator

Get records which are having that list of Ids

SELECT *

FROM TableName

where ID in(1,11,22,33,44)  order by id desc

Functions:

Count()

To get Count of Records in a table

SELECT COUNT(*) FROM table_name;

First Record from a table

SELECT TOP 1 CustomerName FROM Customers

ORDER BY CustomerID ASC;

Last record from A table

SELECT TOP 1 CustomerName FROM Customers

ORDER BY CustomerID DESC;

Duplicates are having Or not

select COUNT(no)as count,no from TableName group by No having COUNT(No)>1

Select Top N Records:

SELECT * FROM (

  SELECT

    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,

    columns

  FROM tablename

) AS foo

WHERE rownumber <= n

another

WITH RequireRows AS
(
    SELECT SalesOrderID, OrderDate,
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
    FROM Sales.SalesOrderHeader
)

SELECT *
FROM RequireRows
WHERE RowNumber <= n;

Select nth Record

SELECT * FROM (

  SELECT

    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,

    columns

  FROM tablename

) AS foo

WHERE rownumber = n


another

WITH RequireRows AS
(
    SELECT SalesOrderID, OrderDate,
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
    FROM Sales.SalesOrderHeader 
)  

SELECT * 
FROM RequireRows

WHERE RowNumber = 5;

Insert records into one table to another existing table:

INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;

Insert records from one table to another new table:

SELECT column_name(s)
INTO newtable [IN externaldb]
FROM table1;

Function to search in function from C#


CREATE FUNCTION [dbo].[iter$simple_intlist_to_tbl] (@list nvarchar(MAX))  
   RETURNS @tbl TABLE (number varchar(50) NOT NULL) AS  
BEGIN  
   DECLARE @pos        int,  
           @nextpos    int,  
           @valuelen   int  
  
   SELECT @pos = 0, @nextpos = 1  
  
   WHILE @nextpos > 0  
   BEGIN  
      SELECT @nextpos = charindex(',', @list, @pos + 1)  
      SELECT @valuelen = CASE WHEN @nextpos > 0  
                              THEN @nextpos  
                              ELSE len(@list) + 1  
                         END - @pos - 1  
      INSERT @tbl (number)  
         VALUES (convert(varchar(50), substring(@list, @pos + 1, @valuelen)))  
      SELECT @pos = @nextpos  
   END  
  RETURN  
END