Sql Server Query samples - Part 2

Views
     Views are the additional layer on the table.Which enables to protect our sensitive data based on the need.

Create Table Products
(
productid        INT,
productname  VARCHAR(40),
productdesc   VARCHAR(40),
price              INT,
)

Now from the above table i want to expose the important details instead of all columns so i am creating a view which will virutally create a visualization of that table.

Create View Product_View
As
Select productid, productname, price from Products


Auto Increment, Select, Insert into,Select Top,Select into,Distinct

         Auto Increment means instead of insert the value each time in your it will insert the value automatically to the corresponding column with one value increment than previous value.This can be specify in the table creation itself by a Keyword IDENTITY .This keyword have two values Seed and Increment

Create Table Products
(
productid        INT   IDENTITY(1000,1),
productname  VARCHAR(40),
productdesc   VARCHAR(40),
price              INT,
)

In the above table , Products productid is set as autoincrement starts the value inserting from 1000 and increment the for 1 for each insert .

Example : 

INSERT into Products(productname,productdesc,price) values('TV','Golden eye',25000);

INSERT into Products(productname,productdesc,price) values('TV','LED',15000);

INSERT into Products(productname,productdesc,price) values('Fridge','LCD',35000);

When you select the records from the Products table ,You can see the product id column value is inserted automatically with increment in each row.

Select * from Products

Output

      

     Now if we want to make a backup of data from existing table,but you don't have a create schema for table in this case we can create the Schema and backup data from existing table into new  by using Select into

SELECT  * INTO Product_BackUp 
FROM Products
WHERE 1=0

From the above code now we are taken a backup of table structure from Products to Product_BackUp it only have the structure of table not data.To get a backup including data following query will resolve that.

SELECT Productid,price INTO Product_BackUp
From Products

(OR)

SELECT * INTO Product_BackUp
From Products

Note :
Table Product_BackUp is created at the Runtime, We don't want to create the table .Now we select the records from the Product_BackUp,

Select * From Product_BackUp

Output :

If  you want to select the top records from the table then following query will be used.Below Query result in Top 2 records from the table.

SELECT TOP 2  * FROM Products

If you want to select Top 2 records other than 1 Record Then That  means records which is in the 2nd and 3rd based on condition, Result must have 1001 and 1002 productid value , it must leave 1000

SELECT   productid,
                 productname,
                 productdesc,price 
FROM     (      SELECT *,Row_number() over(order by productid) as 'Row'
                       FROM Products 
                 ) As  rtable

where Row > 1

Output

      If you want a records should be unique in display then we should use Distinct to avoid duplication in displaying the result.

Select Distinct * from Products

From the Above query we can see the basic things of sql server.

Sql Server Query samples - Part 1

In this article we can see the samples of Sql server basic concept.

Create

     Create is used to create table , database, view, trigger, stored procedure, index etc.Let we see some of them now.
/*  Create Database */
CREATE  DATABASE SAMPLE 
GO

/*  Create Table */
CREATE  TABLE Employee 
(
   ID   INT ,
   NAME VARCHAR(40),
   AGE  INT ,
)

/*  Create View */
CREATE  VIEW EMP_VIEW 
AS
SELECT ID,NAME,AGE FROM Employee 

Alter

Alter is used in database, table, stored procedure, trigger, function

/*  Alter Database for enable the change tracking */
USE MASTER 
GO 
ALTER DATABASE SAMPLE 
SET CHANGE_TRACKING = ON

/*  Alter Table by adding a column */
ALTER TABLE Employee ADD ADDR VARCHAR(40)

/*  Alter Table by alter the size of data type of a column */
ALTER TABLE Employee ALTER ADDR VARCHAR(40) VARCHAR(100)

Drop       
DROP DATABASE SAMPLE  
DROP TABLE Employee 
                                      

Truncate 
TRUNCATE TABLE Employee 

Constraints
/*  Create default constraint */

Default
ALTER TABLE Employee 
ADD CONSTRAINT DF_EMP
DEFAULT 'UNKNOWN' FOR ADDR 

/*  Create primary key constraint */

Primary Key
ALTER TABLE Employee 
ADD CONSTRAINT PK_EMP PRIMARY KEY(ID)

/*  Create foreign key constraint */

Foreign Key
ALTER TABLE EM_SAL
ADD CONSTRAINT FK_EMP (ID) REFERENCES Employee(ID)

/*  Create check constraint */
Check 
ALTER TABLE Employee 
ADD CONSTRAINT CK_EMP
CHECK (ID IS NOT NULL AND AGE > 18)

Index
     Create index keyword is used to create index on table.There can be only one Clustered index per table.Options in Creating index are Clustered or NonClustered or Unique.we can specify the columns to include at the leaf level of the index.

Single Non Clustered Index
CREATE UNIQUE  NONCLUSTERED INDEX IX_NC_SALEID   /* Specify index name */
ON DBO.[SALESTRACKING](saleid)       /* Specify table name and column name */

Composite Non Clustered Index
CREATE UNIQUE  NONCLUSTERED INDEX IX_NC_SALE_ID_NAME 
ON DBO.[SALESTRACKING](saleid,name) 


Composite Clustered Index
CREATE UNIQUE CLUSTERED INDEX IX_C_SALE_ID_NAME 
ON DBO.[SALESTRACKING](saleid,name) 

Non Clustered Index with Include Columns
CREATE NONCLUSTERED INDEX IX_NC_SALE_ID
ON DBO.[SALESTRACKING](saleid)
INCLUDE (age)

Index with Full Factor
CREATE NONCLUSTERED INDEX IX_NC_SALE_ID
ON DBO.[SALESTRACKING](saleid)
WITH (FILLFACTOR=80)

Index with Filter Option
CREATE NONCLUSTERED INDEX IX_NC_SALE_ID
ON DBO.[SALESTRACKING](saleid)
INCLUDE (age)
WHERE name IS NOT NULL

Compress the Contents of the Index
Compress the content of the index based on the Row or Page 
Row 
CREATE NONCLUSTERED INDEX IX_NC_SALE_ID
ON DBO.[SALESTRACKING](saleid)
WITH (DATA_COMPRESSION = ROW)


Page
CREATE NONCLUSTERED INDEX IX_NC_SALE_ID
ON DBO.[SALESTRACKING](saleid)
WITH (FILLFACTOR=80)
WITH (DATA_COMPRESSION = PAGE)

Microsoft Sql Server version and the Difference between the versions

Introduction to Sql Server :
    The Abbreviation of SQL is Structured Query Language.It is the standard query language for accessing the Database.Microsoft Sql Server is a Relational Database Server developed by Microsoft.




Microsoft had launched various versions of Sql Server.

• Sql Server 2000
• Sql Server 2005
• Sql Server 2008
• Sql Server 2008 R2
• Sql Server 2012

Microsoft Sql Server 2000 is a full-featured relational database management system.That offers variety of administrative tools to ease the burden of administration maintenance and database development.

Administrative tools :

1. Enterprise Manager.

2. Query Analyzer

3. Service Manager

4. Data Transformation service

5. Books Online

6. Sql Profiler

Microsoft Sql Server 2005

New Features available in the 2005 version are ,It has new indexing algorithms, syntax and better recovery system.

1. ETL Tool (SSIS or Business intelligence studio)

2. SQL CLR

3. Reporting Server

4. Analysis service (OLAP and Data Mining)

5. Service Broker and Notification Service

6. Supports XML Data type

Microsoft Sql Server 2008

New Features available in the 2008 version are ,Supports the ADO.Net Entity Framework and the Reporting tools, Replication and  Data Definition will be built around the Entity Data Model.

1. It have better comparison features 

2. Flat Earth and Round Earth are newly introduced data type to represent the Geometric and Geographic         data.

3. Full text search functionality is newly added

4. FILESTREAM new datatype is introduced 
5. Change Tracking

Microsoft Sql Server 2008 R2 

New feature available in version 2008 R2 is Central Management of master data entities and hierarachies.

1. Master data management 

2. Multi server management : A centralized console to manage the multiple sql server versions, including the       services like Reporting,Analysis and Integration.

3. PowerPivot for Excel and sharePoint.

4. StreamInSight, ReportBuilder 3.0, Addins for sharePoint

Microsoft Sql Server 2012

New feature available in version 2012 

1. Native support OLEDB instead of ODBC for native connectivity.

Microsoft Change Tracking Sync data from One server to two or more offline - Sql Server Part -1

What is change Tracking in Sql Server ?

Change Tracking is the light weight application, helping the developers to track the data changes takes place in database.Most commonly change track is used to sync the data between the two server applications.

That means synchronization the data from server to the two or more offline store applications.

In Microsoft they release a Change Tracking system in Sql Server 2008 , Which have following features.

1. Efficient and fast
2. Easy to track the data.
3. Minimum disk space
4. Regular clean up of additional table data.

To work with change tracking , we have to  Enable the change Tracking in Database and table .In which we have to track the data .

/* Functions used in change tracking */
CHANGETABLE()   
CHANGE_TRACKING_CURRENT_VERSION()   
CHANGE_TRACKING_MIN_VALID_VERSION()
CHANGE_TRACKING_IS_COLUMN_IN_MASK()   
WITH CHANGE_TRACKING_CONTEXT()

/* Change Tracking database */
Use Master;
Go
Create Database CT
Go

/* Create the Change Tracking Table */
USE CT
CREATE TABLE [SALESTRACKING]
(
SALEID INT PRIMARY KEY,
PRODUCTNAME VARCHAR(40),
MODEL VARCHAR(40)
)

/* INSERT 4 RECORDS */
INSERT INTO [SALESTRACKING] VALUES(1,'LG','AC45')
INSERT INTO [SALESTRACKING] VALUES(2,'SAMSUNG','S453')
INSERT INTO [SALESTRACKING] VALUES(3,'WHIRLPOOL','WL87')
INSERT INTO [SALESTRACKING] VALUES(4,'APPLE','IP342')
GO

SELECT * FROM SALESTRACKING

Check the Properties of database about Change Tracking by default it is disabled false

1. Right click the database
2. Select properties
3. Select the change tracking page
4. Now you can see the change tracking status



USE master
GO
ALTER DATABASE CT
SET CHANGE_TRACKING = ON

Now Change tracking is enabled your database .We have addtional two optional parameters (CHANGE_RETENTION,AUTO_CLEANUP). 
CHANGE_RETENTION         : MAINTAIN THE DATA OF HOW MANY DAYS
AUTO_CLEANUP                    : CLEAN THE OLD DATA AUTOMATICALLY IF IT IS ON

USE master
GO
ALTER DATABASE CT 
SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 100 DAYS,AUTO_CLEANUP = ON)
GO

ENABLE THE CHANGE TRACKING FOR TABLE TO TRACK THE DATA
1. Right click the table
2. Select the properties
3. Select the change tracking page
4. Now you can see the change tracking status.


USE CT
GO
ALTER TABLE [dbo].[SALESTRACKING]
ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF)

TRACK_COLUMNS_UPDATED: This parameter is used to indicate the columns which are changed by UPDATE operation and also indicates that row has changed. By default, it is OFF. 

Now delete two row and insert one row

DELETE FROM [SALESTRACKING] WHERE SALEID = 1
INSERT INTO [SALESTRACKING] VALUES(8,'LG','AC45')

Now we get the change tracked data by executing the following query

SELECT SYS_CHANGE_VERSION,SYS_CHANGE_OPERATION,SALEID
FROM CHANGETABLE(CHANGES [dbo].[SALESTRACKING],0)AS SI
ORDER BY SYS_CHANGE_VERSION

(OR EXECUTE THIS )

SELECT * FROM CHANGETABLE(CHANGES SALESTRACKING,0)AS SI
ORDER BY SYS_CHANGE_VERSION 

From this article we can learn how to enable the change tracking in Database and tables to track.Above image will show that what kind of change that data goes

DDL,DML,DCL,TCL

SQL SERVER  - DML, DDL, DCL and TCL Introduction 

/****************************************************************************************************************************/

DDL

DDL is the abbreviation of Data Definition Language, It is used to create , modify and drop the Database objects.
Examples : CREATE, ALTER, DROP statements

Let we consider the Database objects : Table,View

Table 
CREATE TABLE Emploee
(
     id           int    identity(1,1),
     name     varchar(40)  ,
     age        int
)

In the above example, we are creating a table and save the object to database. Table have columns id,name,age  which are in data type int,varchar(40),int . Column id set a Identity that means each value in that column in unique starts with 1 and increment by 1 so we don't need to insert the value for this column . value is automatically inserted by Sql Server. varchar datatype is representing the string type with 40 length

ALTER TABLE Employee
ALTER  address varchar(100)

In the above example, additional column address is added to the existing table Employee . Column address is defined as varchar(100)

DROP TABLE Employee

In the above example, Employee table is dropped from database

/****************************************************************************************************************************/
DML

DML is the abbreviation of Data Manipultation Language, It is used to insert, delete, update the Data which is stored in database

Examples : SELECT, UPDATE, INSERT statements

INSERT into Employee(name,age) values('Rajesh',23)

SELECT id,name From Employee

Update Employee
set        name = 'Rajesh Kumar',
            age    = '24'
Where id =1

/*****************************************************************************************************************************/

DCL

DCL is the abbreviation of Data Control Language , It is used to create roles, Permission

Examples : GRANT, REVOKE

Grant exec on stored_procedure to public

/******************************************************************************************************************************/

TCL

TCL is the abbreviation of Transactional Control Language,It is used to manage the transactions takes place in database.

Example COMMIT, ROLLBACK


/*****************************************************************************************************************************/

Read the Values from xml and Insert in to table

XML is a Technology , In which we can transfer a data from one system to another .

This Post will explain you about , fetch the values from xml element and insert in to the table in sql server.

Input Xml :

<Employees>
  <Employee>
    <Id>18</Id>
    <FirstName>fname1</FirstName>
    <LastName>lname1</LastName>
    <Company>rac</Company>
  </Employee>
  <Employee>
    <Id>15</Id>
    <FirstName>fname2</FirstName>
    <LastName>lname2</LastName>
    <Company>rac</Company>
  </Employee>
  <Employee>
    <Id>67</Id>
    <FirstName>fname3</FirstName>
    <LastName>lname3</LastName>
    <Company>dlf</Company>
  </Employee>
</Employees>

Step 1 : Create a Table

CREATE TABLE EMPLOYEE
(
   Id               int ,
   Firstname   varchar(20),
   Lastname   varchar(20),
   company    varchar(20)

)

Step 2 : Create a stored procedure

CREATE PROCEDURE Ins_Xml
(
@INPUT XML ,
@OUTPUT INT OUT
)
AS
BEGIN

SET NOCOUNT ON

BEGIN TRY
INSERT INTO  [Employee]
(
[Id],
[FirstName],
[LastName],
[company]
)

SELECT ISNULL(EMPS.EMP.value('Id[1]', 'int'),0) as 'ID',
EMPS.EMP.value('FirstName [1]', 'varchar(20)') as 'FirstName ',
EMPS.EMP.value('LastName[1]', 'varchar(20)') as 'LastName',
EMPS.EMP.value('Company[1]', 'varchar(50)') as 'Company'
FROM @INPUT.nodes('/Employees/Employee') as [EMPS](EMP)

SELECT @OUTPUT = 1

END TRY
BEGIN CATCH
/* Incase Of Error */
SELECT @OUTPUT = -1
END CATCH

SET NOCOUNT OFF


END

Step 3 : Exec the stored procedure

declare @inputxml xml
declare @result int

set @inputxml = N'<Employees>
  <Employee>
    <Id>18</Id>
    <FirstName>fname1</FirstName>
    <LastName>lname1</LastName>
    <Company>rac</Company>
  </Employee>
  <Employee>
    <Id>15</Id>
    <FirstName>fname2</FirstName>
    <LastName>lname2</LastName>
    <Company>rac</Company>
  </Employee>
  <Employee>
    <Id>67</Id>
    <FirstName>fname3</FirstName>
    <LastName>lname3</LastName>
    <Company>dlf</Company>
  </Employee>
</Employees>'

EXEC Ins_Xml @inputxml,@result out

select @result

On success insert 1 as select as output.