TECHTalksPro
  • Home
  • Business
    • Internet
    • Market
    • Stock
  • Parent Category
    • Child Category 1
      • Sub Child Category 1
      • Sub Child Category 2
      • Sub Child Category 3
    • Child Category 2
    • Child Category 3
    • Child Category 4
  • Featured
  • Health
    • Childcare
    • Doctors
  • Home
  • SQL Server
    • SQL Server 2012
    • SQL Server 2014
    • SQL Server 2016
  • Downloads
    • PowerShell Scripts
    • Database Scripts
  • Big Data
    • Hadoop
      • Hive
      • Pig
      • HDFS
    • MPP
  • Certifications
    • Microsoft SQL Server -70-461
    • Hadoop-HDPCD
  • Problems/Solutions
  • Interview Questions

Sunday, February 26, 2017

SQL SERVER : ALTER, DELETE,DROP

 Chitchatiq     2/26/2017 02:08:00 PM     SQL Server     No comments   

SQL SERVER-ALTER, DELETE, DROP












We will use below table for demo purpose
CREATE TABLE Emp_Test
 (EmpID int,
 Empname varchar(10))

ALTER: ALTER command is used to do below things
  •    Add column/constrains
  •    Modify column
  •    Drop column/Constraint

Add Column syntax:
ALTER TABLE <<Table>>
ADD Column_name datatype [size]
Example:
ALTER TABLE Emp_test
ADD Dateofbirth date

We can also add constraints to the existing table. We will discuss about constrains in future sessions. For now we will learn how to add constraints 
Add Constraint Syntax:
ALTER TABLE <<tablename>>
ADD Constraint  Constraint_name ConstraintType (Columns…)
Example:
ALTER TABLE Emp_test
ADD constraint EMP_Unique UNIQUE(EMPID) – Here Unique means EMPID column data should not be repeated

Modify column:

We can also modify the existing column data type and its size if table is empty and it table has data and then we are trying to modify the existing column data type then if the new data type is compatible to the existing data in the table then it will allow other it will through error. E.g. existing column was created using char(10) then if we are trying to modify to Varchar(20)  then it won’t throw any error. Compatibility data types are always we can change. 
Syntax:
ALTER TABLE <<Table_name>> 
ALTER Column Col_name datatype
Example:
ALTER TABLE Emp_test
ALTER Column Empname char(12)


Drop column/Constraint: We can  drop existing column/constraint using ALTER command 
Syntax: 
ALTER TABLE <<table_name>>
DROP <<Column or Constraint>> <<Column_name>>
Example:
Drop Empname column
ALTER TABLE Emp_test
DROP Column Empname  

Drop unique constraint (EMP_Unique)
ALTER TABLE Emp_test
DROP CONSTRAINT EMP_Unique



DELETE:  DELETE Command is used to delete table rows. We can delete all the rows from a table or we can also delete specific rows by using WHERE condition. 

Syntax:
DELETE FROM <<table name>> 
<<WHERE some conditions>>
Example:
DELETE FROM Emp_test--Delete all the rows
DELETE FROM Emp_test WHERE EmpID=10--Delete rows which are having empid=10
DELETE FROM Emp_test WHERE EmpID<10 --Delete rows which are having empid<10
DELETE FROM Emp_test WHERE EmpID<>10 ----Delete rows which are having empid<>10 
In SQL server we can also write DELETE statement like below
DELETE emp_test



TRUNCATE: TRUNCATE Command is also used to delete the data from table
Syntax:
TRUNCATE TABLE <<Tablename>>

Example:
TRUNCATE TABLE emp_test

Here we should get one question like 

What is the difference between DELETE and TRUNCATE?

 Both are deleting rows. Yes both are deleting table data however there are some differences are there while deleting the data.

      1.     For DELETE We can use WHERE condition whereas TRUNCATE We can’t give 
      2.     DELETE Command will maintain log for each deleted row whereas TRUNCATE will not maintain any log
      3.     We can use some joins to delete the data where as in TRUNCATE we can’t use any joins
      4.     DELETE Will not reset IDENTITY [We will discuss in future sessions about IDENTITY] Whereas TRUNCATE Will      reset IDENTITY
      5.     We can Roll back the data when we use DELETE whereas TRUNCATE we can’t rollback
Overall, if we want to delete all the row then it’s always go with TRUNCATE Command


DROP: DROP Command is used to drop the table

Syntax:
DROP TABLE <<tablename>>

Example:
DROP TABLE emp_test

  • Share This:  
  •  Facebook
  •  Twitter
  •  Google+
  •  Stumble
  •  Digg
Email ThisBlogThis!Share to XShare to Facebook
Newer Post Older Post Home

0 comments:

Post a Comment

Popular Posts

  • What is BiG Data
                              1.     Big data is a term that describes the large volume of structured, semi-structured, un-structured da...
  • How to Fix ERROR: CANNOT PARALLELIZE AN UPDATE STATEMENT THAT UPDATES THE DISTRIBUTION COLUMNS Printable View
    Error: [DataDirect][ODBC Greenplum Wire Protocol driver][Greenplum]ERROR: Cannot parallelize an UPDATE statement that updates the distri...
  • SQL Server- SQL Server Data Types
    SQL SERVER Data types In SQL server, we have many data types and in this moment we no need to concentrate on all the data types. We wil...
  • Get Row Count Of All The Tables In SQL Server Database
    Generally most of the times, People who are working as Database Developers or DBA’s will come across to  Get Row Count of all the tables...
  • Error: Failed with exception Unable to move source hdfs: to destination dfs://hadoop1.dev.com/apps/ FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.MoveTask
    Resolution: 1.     Generally Move task will move the files from source location to destination location. If the user doesn’t have ...

Facebook

Categories

Best Practices (1) Big Data (5) BigData&Hadoop (6) DAG (1) Error 10294 (1) external tables (1) File Formats in Hive (1) Greenplum (3) Hadoop (5) Hadoop Commands (1) Hive (4) Internal tables (1) interview Questions (1) Managed tables (1) MySQL Installation (1) ORCFILE (1) org.apache.hadoop.hive.ql.exec.MoveTask (1) Powershell (1) Problems&Solutions (15) RCFILE (1) return code 1 (1) SEQUENCEFILE (1) Service 'userhome' (1) Service 'userhome' check failed: java.io.FileNotFoundException (1) SQL Server (27) sqoop (2) SSIS (1) TEXTFILE (1) Tez (1) transaction manager (1) Views (1) What is Hadoop (1)

Blog Archive

  • December (1)
  • November (1)
  • October (2)
  • September (6)
  • August (1)
  • July (3)
  • March (1)
  • February (8)
  • January (4)
  • December (9)
  • August (4)
  • July (1)

Popular Tags

  • Best Practices
  • Big Data
  • BigData&Hadoop
  • DAG
  • Error 10294
  • external tables
  • File Formats in Hive
  • Greenplum
  • Hadoop
  • Hadoop Commands
  • Hive
  • Internal tables
  • interview Questions
  • Managed tables
  • MySQL Installation
  • ORCFILE
  • org.apache.hadoop.hive.ql.exec.MoveTask
  • Powershell
  • Problems&Solutions
  • RCFILE
  • return code 1
  • SEQUENCEFILE
  • Service 'userhome'
  • Service 'userhome' check failed: java.io.FileNotFoundException
  • SQL Server
  • sqoop
  • SSIS
  • TEXTFILE
  • Tez
  • transaction manager
  • Views
  • What is Hadoop

Featured Post

TOP 100 SQL SERVER INTERVIEW QUESTIONS

SQL SERVER INTERVIEW QUESTIONS 1.       What is the Complex task that you handled in your project 2.       What are the diffe...

Pages

  • Home
  • SQL SERVER
  • Greenplum
  • Hadoop Tutorials
  • Contact US
  • Disclaimer
  • Privacy Policy

Popular Posts

  • FAILED: SemanticException [Error 10294]: Attempt to do update or delete using transaction manager that does not support these operations.
    Error: Resolution:         1.       Check if Your table storage format is ORC         2.      Check if yo...
  • Greenplum System-Useful Queries
    Useful Queries: Query to verify the list of segments in a Greenplum system select * from gp_segment_configuration; R...

Copyright © TECHTalksPro
Designed by Vasu