In this SQL Server 2012 tutorial I will show you how to add and remove column using Microsoft SQL Server Management. For the purpose of this tutorial I will use AdventureWorks2012.
Other Versions of SQL Server:
- SQL Server 2012
- This blog post has been written using SQL Server 2012
- SQL Server 2008 / R2
- The examples we used should work in SQL Server 2008 and R2
- SQL Server 2005
- We suspect that this will work in SQL Server 2005 as well
In the Object Explorer I will select HumanResources.vEmployeeDepartmentHistory the third position.
Next we click : Script View as, ALTER To and New Query Editor Window.When the New Query Editor Window it shows I will delete the first field BusinessEntityID. To delete BusinessEntityID I have to select this field and click delete also I will remove the coma and Execute the Query.
Pictures below shows clearly each steps.
When the Query was completed successfully we have to go to Object Explorer where we click Views folder and refresh. Next we have to select HumanResources.vEmployeeDepartmentHistory it shows Columns where we can see no BusinessEntityID. This mean that procces goes successfully.
For the next example we have to close the Query which we used at the previous process. The Object Explorer is staying in the same position but this time we will use Sales.vSalesPerson and the same like in previous example we have to click: Script View as, ALTER To and New Query Editor Window.
In my first step in this example I will add a first name field from table INNER JOIN Person.Person p (we have to scroll down).
In the Title field I placed a coma and above we have to select First Name, to do this we have to write p. and then it shows options from the table - we choose First Name. When we do this steps we have to Execute a Query and at the right side should be Execute successfully.
If we want to check if a new field First Name is added we have to go to Object Explorer and click plus in Sales.vSalesPerson. We can see a four option below our Sales field to check our field we have to click Column and the First Name should be added at first position.
To show you the next example I will use the same Query.
In this task I would like to change First Name to Name. Next to First Name field I will place As and I will type the new field Name. Now we can Execute a Query.
To check it we have to refresh Object Explorer at the left side and go to Columns. Column shows us that First Name was change to Name so our process was completed successfully.
At the last example I will explain you how to change the data type.
I will use the same Query like in previous example. For this purpose I will change ?,a.[City] data type which we can see at the right side. At the left side we can see City (nvarchar(30), not null) - and I will change nvarchar(30) to nvarchar(50).
To change the nvarchar I will use function Cast at the right side. We have to click at the left side of function City and select Cast - rest is in the same place. After City field i place As and type new data type nvarchar with lenght 50 and we close the parenthesis.
After parenthesis we have to type a field Name. It should look like this ,CAST (a.[City] As nvarchar(50)) As City . We execute a Query and it should be successfully.
To finish this example we have to refresh Object Explorer and we can see that City nvarchar changed to 50 data type.
I hope this SQL Tutorial was helpful.
Katie and Emil