DDL statements are used to define the database structure or schema.
Examples of DDL statements
-> CREATE-to create objects in the database
-> ALTER-alters the structure of the database
-> DROP-delete objects from the database
-> TRUNCATE-remove all records from a table, including all spaces allocated for the records are removed.
-> RENAME-rename an object.
-> Altering column data type:
alter table alter column datatype.
-> Adding new column:
alter tableadd col2 datatype
-> Adding new column with constraint:
alter tableadd col3 datatype
alter table add col4 varchar(20)
Adding multiple columns to the table:
alter table tablename add col1 datatype,
col2 datatypeconstraint constraintname constrainttype
-> How to Drop column:
alter table Employees1 dropcolumn e_id
-> How to Drop all columns in the table:
alter table Employees2 dropcolumn eid,ename
Note: A table should have at least one column.So we cannot drop all the columns.
Ex:alter table countries drop column id,averageheight
-> How to rename a column:
-> The script for renaming any object(table,sp etc)
-> Adding contraint to the column:
CREATE table dbo.vendors
(vendorID int primary key,vendorName nvarchar(50),
ALTER TABLE dbo.vendors ADD CONSTRAINT CK_vendor_CreditRAting
CHECK(creditrating>=1 AND Creditrating<=5)
-> How to drop a constraint using T-SQL:
insert into tb3 values(2,5)select*from tb3
select pid,ROW_NUMBER()OVER(ORDERBY pid asc) as rn into #temp1 from patients;
--drop table #temp1
insertinto patients values(9,'Madhu',97,1,14,'2012-01-03')
-> Finding the last inserted identity column value:
Inoorder to find hte last identity column value we have 3 approaches.
-> @@identity(system function)
-> SCOPE_IDENTITY() (system function)
-> IDENT_CURRENRT(.tablename')(sysytem function)
-> It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.
-> @@IDENTITY will return the last identity value entered into a table in your current session.
-> While @@IDENTITY is limited to the current session, it is not limited to the current scope.
-> If you have a trigger on a table that causes an identity to be created in another table,you will get the identity that was created last, even if it was the trigger that created it.
-> It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.
-> SCOPE_IDENTITY(),like @@IDENTITY,will return the last identity value created in the current session, but it will also limit it to your current scope as well.
-> In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined.
-> It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement
that produced the value.
-> IDENT_CURRENT is not limited by scope and session, it is limited to a specified table.
-> IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.