1)Unusual Sort Orders
(Order by Contain Case)
SELECT certificate_id,
certificate_name,
certificate_code,
type_id,
date_position
FROM tbl_sref_train_certificate
ORDER BY
CASE date_position
WHEN NULL
THEN type_id
ELSE date_position
END
2) NULLIF,COALESCE
ISNULL : Returns a null value if the two specified expressions are equal.
SELECT NULLIF(2,2)
Result
----------------------------------------
NULL
SELECT NULLIF(2,3)
Result
----------------------------------------
2
COALESCE : coalesce returns the first non-null expression among its arguments.
Example 1
SELECT COALESCE(NULL, NULL, NULL,
GETDATE())
Result
----------------------------------------
2014-11-02 22:48:40.780
The above example may have single value or four values. If it has single value, then it fills
null values with remaining attributes.
---------------------------------------------------------------
column 1 | column1
----------------------------------------------------------------
Abc NULL
NULL Efg
--------------------------------------------------------------
Example 2
SELECT COALESCE(column 1, column1 )
Result
----------------------------------------
Abc
Efg
Example 3
Concatination
---------------------------------------------------------------
Country | Currency
----------------------------------------------------------------
India Rupee
Japan Yen
--------------------------------------------------------------
SELECT Country ,
Currency,
COALESCE(Country,Currency) As Country_Currency
---------------------------------------------------------------
Country | Currency | Country_Currency
----------------------------------------------------------------
India Rupee India Rupee
Japan Yen Japan Yen
--------------------------------------------------------------
3) UNION AND UNION Goof Exapmle
DECLARE @test1
TABLE
(
ID
INT,
NAME
VARCHAR(200)
)
DECLARE @test2
TABLE
(
ID
INT,
NAME
VARCHAR(200)
)
INSERT INTO @test1
VALUES (1,NULL),(2,
'India'),(3,
'Japan'),(4,NULL)
INSERT INTO @test1
VALUES (1,NULL),(2,
'USA'),(3,
'Braziln'),(4,NULL),(1,
'India')
SELECT ID,NAME FROM @test1
UNION
SELECT ID,NAME FROM @test2
SELECT ID,NAME FROM @test1
UNION ALL
SELECT ID,NAME FROM @test2
Result
----------------------------------------
ID NAME
-----------------------------------------
1 NULL
1 India
2 India
2 USA
3 Braziln
3 Japan
4 NULL
Result
----------------------------------------
ID NAME
-----------------------------------------
1 NULL
2 India
3 Japan
4 NULL
1 NULL
2 USA
3 Braziln
4 NULL
1 India
4)Random Rows Return
SELECT member_id,
last_name,
first_name
FROM tbl_member
TABLESAMPLE SYSTEM (1
PERCENT);
Result (1st Execution)
----------------------------------------
102 rows
Result (2st Execution)
----------------------------------------
106 rows
So on...
5)Overriding an IDENTITY Column
CREATE TABLE TEST
(
ID
INT IDENTITY ,
COUNTRY
VARCHAR(200)
)
INSERT INTO TEST (COUNTRY)
VALUES ('India'),('Brazil'),('China')
SELECT * FROM TEST
Result
----------------------------------------
ID NAME
-----------------------------------------
1 India
2 Brazil
3 China
SET IDENTITY_INSERT [Database].TEST
INSERT INTO TEST (COUNTRY)
VALUES (4,'Pakistan'),(5,'Irac')
----------------------------------------
ID NAME
-----------------------------------------
1 India
2 Brazil
3 China
4 Pakistan
5 Irac
6) Deleting Rows and Returning the Deleted Rows
CREATE TABLE TEST
(
ID
INT IDENTITY ,
COUNTRY
VARCHAR(200)
)
INSERT INTO TEST (COUNTRY)
VALUES (
'India' ),(
'Brazil' ),(
'China' ),(
'Nepal' )
DELETE
FROM TEST
OUTPUT deleted.COUNTRY
WHERE ID=4
7) Inserting Rows and Returning the Inserted Rows
CREATE TABLE TEST
(
ID
INT ,
COUNTRY
VARCHAR(200)
)
INSERT INTO TEST (ID,COUNTRY)
OUTPUT INSERTED.COUNTRY,INSERTED.ID
VALUES (1,
'India') , (2,
'Brazil') , (3,
'China') , (4,
'Nepal')
8)Updating Data and Returning the Affected Rows
CREATE TABLE TEST
(
ID
INT ,
COUNTRY
VARCHAR(200)
)
INSERT INTO TEST (ID,COUNTRY)
VALUES (1,
'India') , (2,
'Brazil') , (3,
'China') , (4,
'Nepal')
CREATE TABLE TEST1
(
ID
INT ,
COUNTRYX
VARCHAR(200) NULL
)
INSERT INTO TEST1
VALUES (1,NULL),(2,NULL),(3,NULL),(4,NULL)
SELECT *
FROM TEST1
UPDATE A
SET A.COUNTRYX=B.COUNTRY
OUTPUT INSERTED.COUNTRYX
FROM TEST1 A
INNER JOIN TEST B
ON A.ID=B.ID
SELECT *
FROM TEST1
9) SoundIndex ...Sound Like
DROP TABLE TEST
CREATE TABLE TEST
(
ID
INT ,
COUNTRY
VARCHAR(200)
)
INSERT INTO TEST (ID,COUNTRY)
VALUES (1,
'India' ) ,
(2,
'Brazil' ) ,
(3,
'China' ) ,
(4,
'Nepal' ) ,
(5,
'ian' ) ,
(6,
'en' ) ,
(7,
'an' ) ,
(8,
'am' ) ,
(9,
'em' ),
(10,
'am' )
SELECT top 5 COUNTRY
FROM TEST
WHERE SOUNDEX(COUNTRY)=
SOUNDEX(
'inI')
Result
----------------------------------------
COUNTRY
-----------------------------------------
ian
10) Merger Statement
CREATE TABLE TEST
(
ID
INT ,
COUNTRY
VARCHAR(200)
)
INSERT INTO TEST (ID,COUNTRY)
VALUES (1,
'India'),
(2
,'Brazil'),
(3,
'China'),
(4,
'Nepal'),
(5,
'ian'),
(6,
'en'),
(7,
'an'),
(8,
'am'),
(9,
'em'),
(10,
'am')
CREATE TABLE TEST1
(
ID
INT ,
COUNTRYX
VARCHAR(200) NULL
)
MERGE INTO TEST1
AS C
USING TEST
AS CT
ON C.ID = CT.ID
WHEN MATCHED
THEN
UPDATE SET
C.COUNTRYX = CT.COUNTRY
WHEN NOT MATCHED THEN
INSERT (ID,COUNTRYX)
VALUES (CT.ID, CT.COUNTRY);
SELECT *
FROM TEST1
---------------------------------------------------------------------------------
ID COUNTRYX
----------------------------------------------------------------------------------
1 India
2 Brazil
3 China
4 Nepal
5 ian
6 en
7 an
8 am
9 em
9 am
11) Sring Operation
LEFT & RIGHT
SELECT LEFT (
'India Govt has started clean india mission.' , 10)
SELECT RIGHT (
'India Govt has started clean india mission.' , 10) ;
Result
----------------------------------------
(No column name)
-----------------------------------------
India Govt
Result
----------------------------------------
(No column name)
-----------------------------------------
a mission.
UPPER & LOWER
SELECT UPPER (
'India Govt has started clean india mission. ' )
SELECT LOWER (
'India Govt has started clean india mission.' )
Result
----------------------------------------
(No column name)
-----------------------------------------
INDIA GOVT HAS STARTED CLEAN INDIA MISSION.
Result
----------------------------------------
(No column name)
-----------------------------------------
india govt has started clean india mission.
12) Date Function
ISDATE :valid date or not
SELECT MyDate ,
ISDATE(MyDate)
AS IsValiDate
FROM (
VALUES(
'IsValiDate' ),
(
'2012-02-14' ),
(
'2012-01-01T00:00:00' ),
(
'2014-12-31T23:59:59.9999999' )
) dt(MyDate)
Result
--------------------------------------------------------------------------------------------
MyDate | IsValiDate
-------------------------------------------------------------------------------------------
IsValiDate 0
2012-02-14 1
2012-01-01T00:00:00 1
2014-12-31T23:59:59.9999999 0
13) @@IDENTITY && SCOPE_IDENTITY
@@IDENTITY returns the last identity value generated by any table in the current session. If the insert statement fires a trigger that inserts into another table with an identity column, the value returned by
@@IDENTITY will be that of the table inserted into by the trigger.
SCOPE_IDENTITY returns the last identity value generated by any table in the current session and scope. In the previous scenario,
SCOPE_IDENTITY returns the identity value returned by the first insert statement, not the insert into the second table from the trigger.
CREATE TABLE TEST
(
IDX
INT IDENTITY ,
ID
INT ,
COUNTRY
VARCHAR(200)
)
INSERT INTO TEST (ID,COUNTRY)
VALUES (1 ,
'India' ),
(2 ,
'Brazil' ),
(3 ,
'China' ),
(4 ,
'Nepal' ),
(5 ,
'ian' ),
(6 ,
'en' ),
(7 ,
'an' ),
(8 ,
'am' ),
(9 ,
'em' ),
(10 ,
'am' )
SELECT @@IDENTITY,
SCOPE_IDENTITY(),
IDENT_CURRENT(
'dbo.TEST');
14) ESCAPE
SELECT access_name
FROM tbl_user
WHERE access_name LIKE
'%%%'
all ,value return , no match for wildcard %
Instead, you can try one of the following solutions:
SELECT access_name
FROM tbl_user
WHERE
access_name
LIKE '%[%]%'
SELECT access_name
FROM tbl_user
WHERE
access_name
LIKE '%\%%' ESCAPE
'\'
Step 2
