File using XML Data Type

CREATE TABLE #temp
(
id INT IDENTITY(1, 1) ,
varName VARCHAR(20)
)

INSERT INTO #temp(varName )VALUES ('Nirav')
INSERT INTO #temp(varName )VALUES ('Mayank')
INSERT INTO #temp(varName )VALUES ('Vihar')
INSERT INTO #temp(varName )VALUES ('Rahul')
INSERT INTO #temp(varName )VALUES ('Nilay')

SELECT * FROM #temp

DECLARE @data AS XML

SELECT @data = ( SELECT * FROM #temp
FOR XML PATH('Child') ,ROOT('Roots') )

SELECT @data

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

Create/Alter/Drop table

Create Table:
                          CREATE TABLE Employee_Mst

                          (

                           Id int , 

                          varFirstName varchar(30), 

                          varLastName varchar(30)

                          );

• if you want to Add  Primary key in Employee_Mst Table use Alter Function:
• -Ex: Alter table Employee_Mst ADD  PRIMARY KEY (Id);

• if you want to Add Column in Employee_Mst also use Alter Function:
• -Ex: Alter table Employee_Mst ADD varAddress varchar(255)

• if you want to Drop Column in Employee_Mst also use Alter Function:
• -Ex: Alter table Employee_Mst Drop varAddress

• if you want to Drop table  Employee_Mst Drop Function use:
• -Ex: Drop table Employee_Mst

Reset Identity Column use CHECKIDENT Command

Reset Identity Column : DBCC CHECKIDENT (Table_Name, RESEED, 0)

If you are using an identity column on your SQL Server tables, you can set the next insert value to whatever value you want.
Ex:- DBCC CHECKIDENT (Table_Name, RESEED, 100)
Now your identity number start with 101 Number.

Select/Delete Multiple Record Using IN Command

Select Query:
Select * from Employee_Mst Where Id IN (1,2,3)
Select * from Employee_Mst Where varEmpname IN ('Nirav','Ashish','Rahul')

Delete Query:
Delete from Employee_Mst Where Id IN (1,2,3)
Delete from Employee_Mst Where varEmpname IN ('Nirav','Ashish','Rahul')

Returning a Row Number in a Query

SELECT varEmpname,decAmount,varState
    ROW_NUMBER() OVER (ORDER BY varEmpname) AS 'RowNumber'
    FROM Employee_mst

RowNumber varEmpname decAmount  varState

 1        Nirav      15,000     Gujarat

 2        Ashish     25,000     Mumbai

Pivot Command in sql

SELECT [CA], [AZ], [TX]
FROM
(
SELECT sp.StateProvinceCode
FROM Person.Address a
INNER JOIN Person.StateProvince sp
ON a.StateProvinceID = sp.StateProvinceID
) p
PIVOT
(
COUNT (StateProvinceCode)
FOR StateProvinceCode
IN ([CA], [AZ], [TX])
) AS pvt;