SQL Server 2012 Magic

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_nameFROM tbl_userWHERE 
    access_name LIKE  '%[%]%' 
 

SELECT access_name FROM tbl_user  WHERE
    access_name LIKE '%\%%' ESCAPE '\'