DDL Statements:

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.

DDL Statements:

Alter statements:

-> 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 constraintconstrainttype alter table add col4 varchar(20) constraint primarykey

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: sp_rename 'Employees1.e_name','e1','column' -> The script for renaming any object(table,sp etc) sp_RENAME'[oldtablenamr]','[newtablename]' -> Adding contraint to the column: CREATE table dbo.vendors (vendorID int primary key,vendorName nvarchar(50), creditrating tinyint) ALTER TABLE dbo.vendors ADD CONSTRAINT CK_vendor_CreditRAting CHECK(creditrating>=1 AND Creditrating<=5) -> How to drop a constraint using T-SQL: ALTER TABLE DROP CONSTRAINT createtable tb3 ( c1 intidentity(1,1), c2 int, c3 int ) insert into tb3 values(2,5)select*from tb3 --SELECT IDENT_CURRENT('tb3') --idea createtrigger trgInsTb1 on tb1 insteadofInsert as declare@maxrow int; select pid,ROW_NUMBER()OVER(ORDERBY pid asc) as rn into #temp1 from patients; select*from #temp1; select*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)

@@IDENTITY:

-> 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.

SCOPE_IDENTITY:

-> 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.

IDENT_CURRENT('tablename'):

-> 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.