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)