Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Friday, 7 March 2014

Table concepts on Table

   Table concepts


Table concepts include Enable constraints/triggers Disable constraints/triggers and add new column and altering column to modify existing column.

Disabling All Constraints on a Table


ALTER TABLE CustomerAddresses NOCHECK CONSTRAINT ALL
To Re-Enable the constraints, issue use the same command, substituting NOCHECK with CHECK, as follows:

ALTER TABLE CustomerAddresses CHECK CONSTRAINT ALL

Disabling Individual Constraints

ALTER TABLE CustomerAddresses NOCHECK CONSTRAINT FK_CustomerAddresses_Customers
To Re-Enable the constraints change NOCHECK to CHECK:

ALTER TABLE CustomerAddresses CHECK CONSTRAINT FK_CustomerAddresses_Customers

Disabling Individual Triggers on table

Diable Trigger triggername on tablename
 
DISABLE TRIGGER Person.uAddress ON Person.Address; 

Enabling  Individual Triggers on table

 Enable Trigger triggername on table 
 
 ENABLE Trigger Person.uAddress ON Person.Address;  

Enabling all triggers that were defined with the same scope

ENABLE Trigger ALL ON ALL SERVER; 

Disabling all triggers that were defined with the same scope

DISABLE Trigger ALL ON ALL SERVER;

Adding New column in Table

ALTER TABLE [dbo].[defect] ADD [defect_id] [bigint] IDENTITY(1,1) NOT NULL

Altering already existing column in Table

ALTER TABLE [dbo].[defect] ALTER COLUMN [completion_hours] [nvarchar](50) NULL
Primary key defined at the table level: 
create table DepartmentManager(
Departmentmanagerid int

identity(1,1),empid int, Departmenttid int, primary key(Departmentmanagerid)
)

Adding Primary Key constraint using Alter table command

Alter Table employeemaster add constraint pk_EmpPrimaryKey primary key(empid)

Dropping a primary constraint from a table

Alter Table tablename Drop constraint name

alter table DepartmentManager drop constraint pk_EmpPrimaryKey
Adding Foreign Key and dropping Foreign key

Alter Table DepartmentManager Add Constraint Fk_Departmenttid Foreign Key(Departmenttid)references Department (Departmenttid)

Alter Table DepartmentManager Drop Constraint Fk_Departmenttid
Adding Unique Key and dropping Unique key

Alter table tablename add constraint constraintname Unique (Columnname)

alter table employeesalarymaster add constraint Uni_empid unique (empid)

Adding Check constraint
 
CREATE TABLE employeesalarymaster(empsalaryid int identity(1,1),Empid int, Empsalary numeric(10,2) not null,SalaryDt datetime,check (empsalary)5000))

 Note: - The Check constraints must be a Boolean expression that can be evaluated 

using the values in the rows being inserted or updated.


The power of SQL Server amaze me all the time, to see the power of SQL Server all you need is a little creativity and knowledge of the syntax. Some times derived tables can be more useful, likewise, you need to create a view for a single query and then you want to use it within another query and after that the view should be dropped because no use of the view after that action, then here derived tables can help you and benefits you by saving to create you a catalog entry in the form of view in the server.
For repeated queries, a SQL derived table used multiple times performs well with cached definition. It will not down your performance. A SQL derived table differs from a temporary table in that a SQL derived table exists only for the duration of the query, while a temporary table exists until the server is brought down and also it uses lot of disk space than a derived table in the temdb database. So it's better to make use of Derived tables wherever possible by eliminating the one time use views and by eliminating the temporary tables. What I think is it will improve the performance of your query.

Fixing the error: The multi-part identifier ... could not be bound in Join statements