SYNONYM small Concept but provide Best Security..

CREATE TABLE SYNONYM_Temp

(
id INT IDENTITY(1, 1),
varName VARCHAR(50)
)

CREATE SYNONYM Bank FOR SYNONYM_Temp

--Step :1

INSERT INTO Bank(varName)VALUES ('ICIC')

INSERT INTO Bank(varName)VALUES ('HSBC')

INSERT INTO Bank(varName)VALUES ('HDFC')

INSERT INTO Bank(varName)VALUES ('CITY')

SELECT *  FROM Bank-->SYNONYM
SELECT *  FROM SYNONYM_Temp-->Origional Table

--Step :2

DELETE FROM Bank WHERE ID= 4

SELECT * FROM Bank

---Step :3

UPDATE Bank SET varName= 'CITY' WHERE ID= 3

SELECT * FROM Bank

---Step :4, you can not change the Structure of Table

ALTER TABLE bank ADD Col3 VARCHAR(50)
 
DROP SYNONYM Bank
 
Note: using SYNONYM Client never know what is origional table Name.

Change Table Name Using sp_Rename Procedure

CREATE TABLE Temp_Customer

(
id INT IDENTITY(1, 1),
varName VARCHAR(15)
)

INSERT INTO Temp_Customer (varName) VALUES ('Nirav')

INSERT INTO Temp_Customer (varName) VALUES ('Rahul')

SELECT * FROM Temp_Customer

EXEC sp_Rename oldTableName,NewTableName


EXEC sp_Rename 'Temp_Customer','Temp_CustomerData'

SELECT * FROM Temp_CustomerData

Database in Restoring State to Simple State

1) Restore DATABASE dbname with Recovery

How to recover a Database in suspect mode

1) Step 1

     EXEC sp_resetstatus 'DBname';

2) Step 2

     ALTER DATABASE DBname SET EMERGENCY

     DBCC checkdb('DBname')

3) Step 3

    ALTER DATABASE DBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    DBCC CheckDB ('DBname', REPAIR_ALLOW_DATA_LOSS)

4) Step 4

    ALTER DATABASE DBname SET MULTI_USER

Compare String

1) Comapre Both String are same or not
2) Case sensitive is  allow.
3) Both String are different  then use this.

    SELECT CheckSum('nirav')   
    SELECT CheckSum('Nirav')

 
1) Comapre Both String are same or not.
2) Case sensitive is not  allow.

  SELECT binary_CheckSum('nirav')  
  SELECT binary_CheckSum('Nirav')

Execution Cycle in sql

 Execution Cycle it means Which error is  encounter  first?
  by Default Sql check following cycle:

                        1)  Null value

                        2)  Data-type Length

                        3)  Data-type

                        4)  Primary Key

                        5)  Unique Key

Please run step-by-step  below example:

1) Step-1 ,Create temp table and Insert value

        CREATE TABLE #temp
         (
             intGlcode INT PRIMARY KEY,
            varEmpName VARCHAR(15) NOT NULL,
            varLastName VARCHAR(15),
            intAge TINYINT,
            Emp_Code VARCHAR(5) UNIQUE
         )

        in above table intGlcode->Primary key fields, varEmpName ->inserting not null value     eilds,Emp_Code->insert Unique value fields

INSERT  INTO #temp
        (
          intGlcode,
          varEmpName,
          varLastName,
          intAge,
          Emp_Code
        )
VALUES  (
          1,
          'Nirav',
          'Gandhi',
          18,
          '0001'
        ) 
2) Step-2 , insert data
INSERT  INTO #temp
        (
          intGlcode,
          varEmpName,
          varLastName,
          intAge,
          Emp_Code
        )
VALUES  (
          1,
          NULL,
          'Gandhi',
          'abcd',
          '0001'
        )
  In above example check,  insert a null value in varEmpname and also insert 'abcd' value in intAge,both are wrong data, but when you are running above example in your screen first  'null' value error  generate.

3) Step 3,  DataType Length value       
INSERT  INTO #temp
        (
          intGlcode,
          varEmpName,
          varLastName,
          intAge,
          Emp_Code
        )
VALUES  (
          1,
          'Niravasasasasasasasa',
          'Gandhi',
          'abcd',
          '0001'
        )
In above example check,  insert a large value in varEmpname and also insert 'abcd' value in intAge,both are wrong data, but when you are running above example in your screen first  'String or binary data would be truncated'   error  generate, because varEmpname datatype lenght on VARCHAR(15).
    
4) Step 4 --DataType     

INSERT  INTO #temp
        (
          intGlcode,
          varEmpName,
          varLastName,
          intAge,
          Emp_Code
        )
VALUES  (
          1,
          'Nirav',
          'Gandhi',
          99999999,
          '0001'
        )
In above example check,  insert a 1  in intGlcode and also insert '9999999' value in intAge,both are wrong data, but when you are running above example in your screen first  'Arithmetic overflow error for data type tinyint, value = 99999999.'   error  generate, because intAge datatype- 'tinyint' here primary key error not Generated.

5) Step 5 --Primary key

INSERT  INTO #temp
        (
          intGlcode,
          varEmpName,
          varLastName,
          intAge,
          Emp_Code
        )
VALUES  (
          1,
          'Nirav',
          'Gandhi',
          18,
          '0001'
        )
In above example check,  insert a 1  in intGlcode and also insert '0001' value in Emp_Code,both are wrong data, but when you are running above example in your screen first  'Violation of PRIMARY KEY constraint'   error  generate, because intGlcode is Primary key, here Unique key  error not Generated.

6) Step 6   --Unique
INSERT  INTO #temp
        (
          intGlcode,
          varEmpName,
          varLastName,
          intAge,
          Emp_Code
               
        )
VALUES  (
          2,
          'Nirav',
          'Gandhi',
          18,
          '0001'
               
        )
      --In above example Unique key error generated.
DROP TABLE #temp