SQL : Condition for all or fixed values

Select Emp_Code, Emp_Name, Emp_Address, Emp_Telephone, Emp_Postcode
From Employees A
Where (A.Emp_Code = @Emp_Code or isnull(@Emp_Code,'')='')

SQL : Each Null has Different Value

IF NULL = NULL
BEGIN
    SELECT 'Are Equal'
END
ELSE
BEGIN
    SELECT 'Are Not Equal'
END

Query Result is : Are Not Equal

This means that in SQL each Null has different value.

Search Specific Word in Database

-- EXEC SearchWordInDatabase 'Test'

CREATE PROC SearchWordInDatabase
(
    @SearchWord nvarchar(100)
)
AS
BEGIN

    CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

    SET NOCOUNT ON

    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchWord2 nvarchar(110)
    SET  @TableName = ''
    SET @SearchWord2 = QUOTENAME('%' + @SearchWord + '%','''')

    WHILE @TableName IS NOT NULL
    BEGIN
        SET @ColumnName = ''
        SET @TableName =
        (
            SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
            FROM     INFORMATION_SCHEMA.TABLES
            WHERE         TABLE_TYPE = 'BASE TABLE'
                AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                AND    OBJECTPROPERTY(
                        OBJECT_ID(
                            QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                             ), 'IsMSShipped'
                               ) = 0
        )

        WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
        BEGIN
            SET @ColumnName =
            (
                SELECT MIN(QUOTENAME(COLUMN_NAME))
                FROM     INFORMATION_SCHEMA.COLUMNS
                WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                    AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                    AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                    AND    QUOTENAME(COLUMN_NAME) > @ColumnName
            )
   
            IF @ColumnName IS NOT NULL
            BEGIN
                INSERT INTO #Results
                EXEC
                (
                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
                    FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchWord2
                )
            END
        END   
    END

    SELECT ColumnName, ColumnValue FROM #Results
END

SQL : Numeric Order By

SELECT EmployeeID, FirstName, LastName, Address1, Address2, Telephone FROM dbo.Employees
ORDER BY 2

--This query will show your data by FirstName as it takes the Column no. 2 to sort.

Get your's Birthday details Year, Quarters, Months, Weeks, Days, Hours, Minutes & Seconds in SQL

DECLARE @StartDate SMALLDATETIME

SET @StartDate = '01/08/1985 1:35:00' -- Put Your Date of Birth & Time

SELECT DATEDIFF(yyyy, @StartDate, GETUTCDATE()) Years,
DATEDIFF(qq, @StartDate, GETUTCDATE()) AS Quarters,
DATEDIFF(mm, @StartDate, GETUTCDATE()) AS Months,
DATEDIFF(ww, @StartDate, GETUTCDATE()) AS Weeks,
DATEDIFF(dd, @StartDate, GETUTCDATE()) AS [Days],
DATEDIFF(hh, @StartDate, GETUTCDATE()) AS [Hours],
DATEDIFF(mi, @StartDate, GETUTCDATE()) AS [Minutes],
DATEDIFF(ss, @StartDate, GETUTCDATE()) AS [Seconds]

Capitalize the first character

Function :

CREATE FUNCTION InitCapital (
@string varchar(255)
)
RETURNS varchar(255) AS
BEGIN

RETURN upper(left(@string, 1)) + right(@string, len(@string) - 1)

END

Query :

SELECT
InitCapital(Left(user_name, CHARINDEX('.', user_name, 1) - 1)) as First_Name,
InitCapital(Right(user_name, (Len(id_user) - CHARINDEX('.', user_name, 1)))), as Last_Name,
username,
FROM tblUsers

How to Delete Duplicate Rows from Table

--Create Temp Table
IF OBJECT_ID('ABC') IS NOT NULL
DROP TABLE 'ABC'
GO

CREATE TABLE [dbo].[ABC]
(
[ID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_ABC_ID] DEFAULT (newid()),
[FullName] [nchar](10) NOT NULL,
CONSTRAINT [PK_ABC] PRIMARY KEY CLUSTERED ( [ID] ASC )ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO ABC VALUES ( NEWID(), 'New1')
INSERT INTO ABC VALUES ( NEWID(), 'New2')
INSERT INTO ABC VALUES ( NEWID(), 'New2')
INSERT INTO ABC VALUES ( NEWID(), 'New2')
INSERT INTO ABC VALUES ( NEWID(), 'New3')
INSERT INTO ABC VALUES ( NEWID(), 'New3')

-- This code will select the Duplicate row only and keep single copy from row
SELECT [ID], [FullName], [RowIndex]
FROM
(
SELECT
[ID], [FullName], RANK() OVER (PARTITION BY [FullName]
ORDER BY [ID] ASC) AS [RowIndex]
FROM [dbo].[ABC]
)[A]
WHERE [A].[RowIndex] > 1
GO

-- If replace SELECT SQL command with DELETE SQL command
DELETE FROM [dbo].[ABC] WHERE [ID] IN
(
SELECT [ID] FROM
(
SELECT
[ID], [FullName],
RANK() OVER (PARTITION BY [FullName] ORDER BY [ID] ASC) AS [RowIndex]
FROM [dbo].[ABC]
)[A] WHERE [A].[RowIndex] > 1
)
GO

SQL : Data Types Ranges

Exact numerics

Type	From	To
bigint	-9,223,372,036,854,775,808	9,223,372,036,854,775,807
int	-2,147,483,648	2,147,483,647
smallint	-32,768	32,767
tinyint	0	255
bit	0	1
decimal	-10^38 +1	10^38 –1
numeric	-10^38 +1	10^38 –1
money	-922,337,203,685,477.5808	+922,337,203,685,477.5807
smallmoney	-214,748.3648	+214,748.3647

numeric and decimal are Fixed precision and scale data types and are functionally equivalent.

Approximate numerics

Type	From	To
float	-1.79E + 308	1.79E + 308
real	-3.40E + 38	3.40E + 38

datetime and smalldatetime

Type	From	To
datetime (3.33 milliseconds accuracy)	Jan 1, 1753	Dec 31, 9999
smalldatetime (1 minute accuracy)	Jan 1, 1900	Jun 6, 2079

Character Strings

Type	Description
char	Fixed-length non-Unicode character data with a maximum length of 8,000 characters.
varchar	Variable-length non-Unicode data with a maximum of 8,000 characters.
varchar(max)	Variable-length non-Unicode data with a maximum length of 231 characters (SQL Server 2005 only).
text	Variable-length non-Unicode data with a maximum length of 2,147,483,647 characters.

Unicode Character Strings

Type	Description
nchar	Fixed-length Unicode data with a maximum length of 4,000 characters.
nvarchar	Variable-length Unicode data with a maximum length of 4,000 characters.
nvarchar(max)	Variable-length Unicode data with a maximum length of 230 characters (SQL Server 2005 only).
ntext	Variable-length Unicode data with a maximum length of 1,073,741,823 characters.

Binary Strings

Type	Description
binary	Fixed-length binary data with a maximum length of 8,000 bytes.
varbinary	Variable-length binary data with a maximum length of 8,000 bytes.
varbinary(max)	Variable-length binary data with a maximum length of 231 bytes (SQL Server 2005 only).
image	Variable-length binary data with a maximum length of 2,147,483,647 bytes.

Get Charindex in Reverse

SELECT RIGHT(@String, CHARINDEX(' ', REVERSE(@String)) - 1)

Get your Database Tables Details

SELECT * FROM INFORMATION_SCHEMA.TABLES
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

Getting only Month and Year from Date

Select[YEAR] = YEAR(getdate()), [YEAR] = DATEPART(YY,getdate()), [MONTH] = MONTH(getdate()),
[MONTH] = DATEPART(MM,getdate()), [MONTH NAME] = DATENAME(MM, getdate())

Get First and Last date for any Month in any Year

DECLARE @Month int
DECLARE @Year int

set @Month = 2
set @Year = 2012

select DATEADD(month,@Month-1,DATEADD(year,@Year-1900,0)) /*First*/

select DATEADD(day,-1,DATEADD(month,@Month,DATEADD(year,@Year-1900,0))) /*Last*/

Date difference in Days,hours minutes and seconds.

SELECT Create_Date,Modify_Date,DATEDIFF(D,Create_Date,Modify_Date) AS Days,DATEDIFF(HH,Create_Date,Modify_Date)AS Hours,DATEDIFF(MI,Create_Date,Modify_Date)AS Minutes,DATEDIFF(SS,Create_Date,Modify_Date)AS SecondsFROM SYS.OBJECTs WHERE TYPE='U'AND CREATE_DATE != MODIFY_DATE