SQL: ANY

This following Query Explain, how to use ANY in SQL Query.

USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE city = ANY
(SELECT city
FROM publishers)

SQL: EXISTS

This following Query Explains, How to use Exists in SQL Query.

Have a look at the sample given below

USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE exists
(SELECT *
FROM publishers
WHERE authors.city = publishers.city)

When a subquery is introduced with the keyword EXISTS, it functions as an existence test. The WHERE clause of the outer query tests for the existence of rows returned by the subquery. The subquery does not actually produce any data; it returns a value of TRUE or FALSE.

SQL: First Day Of Month

Use this Query to get First day of a Month,

SELECT DATENAME(dw, DATEADD(dd, - DATEPART(dd, GETDATE()) + 1, GETDATE())) AS FirstDay

In this Query, We used dw which is the abbreviations of weekday.

To Know, other abbreviations
-----------------------------------
Datepart - Abbreviations
-----------------------------------
year - yy, yyyy
quarter - qq, q
month - mm, m
dayofyear - dy, y
day - dd, d
week - wk, ww
weekday - dw
hour - hh
minute - mi, n
second - ss, s
millisecond - ms
-----------------------------------

SQL: Number roundup

To round up a value of a number, follow this Query,

For example even if the user enters 7.1 it should be rounded up to 8.

SELECT CEILING (7.1)

SQL:Convert Uppercase

This query will convert all the letters in a word to upper case

SELECT UPPER('test')

SQL:Find top salary among two tables

To find highest salary between 2 tables, the following query will be helpful

SELECT TOP 1 sal
FROM (SELECT MAX(sal) AS sal
FROM sal1
UNION
SELECT MAX(sal) AS sal
FROM sal2) a
ORDER BY sal DESC

SQL: Find nth highest salary

If you want to use nth highest salary, use the following query.

this query will find 6th highest salary

SELECT TOP 1 salary
FROM (SELECT DISTINCT TOP 6 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary

SQL: Duplicate Rows

This Query will delete duplicate records, not all the records, for examples if 3 duplicate rows exists in a table, this query delete 2 records and keep 1 rows

SET ROWCOUNT 1

DELETE yourtable
FROM yourtable a
WHERE
(SELECT COUNT(*) FROM yourtable b WHERE b.name1 = a.name1 AND b.age1 = a.age1) > 1
WHILE @@rowcount > 0
DELETE yourtable
FROM yourtable a
WHERE
(SELECT COUNT(*) FROM yourtable b WHERE b.name1 = a.name1 AND b.age1 = a.age1) > 1
SET ROWCOUNT 0

SQL: Get Table Row’s count without Count(*)

you can use alternative way instead of SELECT COUNT(*) statement. Because SELECT COUNT(*) statement make a full table scan to return the total table's row count, it can take very many time for the large table. There is another way to determine the total row count in a table. You can use sysindexes system table, in this case. There is ROWS column in the sysindexes table. This column contains the total row count for each table in your database. So, you can use the following select statement instead of SELECT COUNT(*):

SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid < 2

So, you can improve the speed of such queries in several times.