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.
