- Create a table as shown below
CREATE TABLE [dbo].[Employee]( [EmployeeID] [int] IDENTITY(1,1) NOT NULL, [EmployeeFirstName] [varchar](50) NOT NULL, [EmployeeLastName] [varchar](50) NOT NULL, [EmployeeDOB] [date] NOT NULL, [CreatedDateTime] [datetime] NOT NULL, [ModifiedDateTime] [datetime] NOT NULL, CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ( [EmployeeID] ASC ) GO
- Populate the table with some records
- Create a View as below
CREATE VIEW [dbo].[EmployeeView] AS SELECT EmployeeID, EmployeeFirstName, EmployeeLastName, EmployeeDOB, CreatedDateTime, ModifiedDateTime FROM dbo.Employee GO
- Do a select from the view.
SELECT * FROM [dbo].[EmployeeView]
- Everything looks fine now.
- Include a EmployeeMiddleName column in the Employee table and do the select from the view again.
- Can you see the difference? EmployeeMiddleName column doesn’t appear but the EmployeeMiddleName data appears under the EmployeeLastName column and EmployeeDOB has the EmployeeLastName data and so son.
a) Don’t use * instead denote the column names in the view.
b) Use Schema binding when creating a view but it will keep the schema of table and view intact and cannot change the underlying table schema until the view definition is modified or view is dropped otherwise the DB engine will throw an error. Schema Binding will improve stability and performance but also increases developers work.