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

One of the most common errors that you might face in join statements is this error:

Msg 4104, Level 16, State 1, Line ...

The multi-part identifier ...... could not be bound.

The main reason for this error is that the source table cannot be found, for example if you have statement such as Table1.OrderDate, and then if you get error above, this means that Table1 cannot be found in the query. Sometimes you can see that source table exists in the query, but T-SQL cannot understand it, especially when you write join statements.

Consider join statement below:

select * from sales.SalesOrderHeader SH

left outer join sales.SalesOrderDetail SD

left outer join Production.Product P

on SD.ProductID=p.ProductID and SH.SalesOrderID=SD.SalesOrderID

With statement above you will get the error : The multi-part identifier "SH.SalesOrderID" Could not be bound.

the reason is that SalesOrderHeader (SH) table is hidden from the third part of the join. and if you want to apply joining condition it will throw the error.

Resolution is:

put join condition of each join statement exactly after it. query below is showing fixed version of that statement:

select * from sales.SalesOrderHeader SH

left outer join sales.SalesOrderDetail SD

on SH.SalesOrderID=SD.SalesOrderID

left outer join Production.Product P

on SD.ProductID=p.ProductID

This statement used SH.SalesOrderID = SD.SalesOrderID right after the first join where the SH table is totally visible

Replace Duplicate Spaces with Single Space in T-SQL

T-SQL code (working in SQL Server) to replace duplicate spaces (or triple or more) with a single space is :

declare @text varchar(max)

set @text=' St    Charles'

while charindex('  ',@text)>0

set @text=replace(@text,'  ',' ')

select @text

and the result is :

St Charles