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 Top N Records:
SELECT * FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
columns
FROM
tablename
) AS foo
WHERE rownumber <= n
another
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 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
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
