Change SQL Server Table Structures with SQL ALTER TABLE

Nocheck constraint all

Please see MSDN documentation for more details. Columns can either allow or not allow null values. The code below shows three different ways to explore a given table. The image below shows the details of the SalesLt. Product table. We can use the TOP command to limit the number of rows returned.

SQL WHERE Clause

It tells us useful information such as reserved space, data space, and index space in kilobytes. To recap, there are many system stored procedures that can help the database administrator do their daily job. In this example, we determined that the photo stored in the database and the image's file name are no longer needed. The execution of the first T-SQL statement drops the column. The command either returns a success or failure. The image below shows a successful execution. If we try to query the missing columns after column removal, the query analyzer tells us the column names are invalid. The image below shows the output from executing the second T-SQL statement. This is because images tend to take up a lot of file space. Note: Any database objects dependent upon the name must be dropped, recreated, and re-validated. If the business line asks the IT team to change the column name to WeightGrams , we must perform the following steps:. The last T-SQL statement shows the modification to the table schema.

The column name has been successfully changed. Just watch out for any objects that depend on the column name. Unless you specify a default value, the column should be defined as nullable. The T-SQL code below creates a new column named WeightPounds and uses an update statement to populate the column with values. Please note that the column was defined as eight digits with a precision of two decimal places. This definition is reflected in the output below. In our example, the product owner on the business team has requested more precision for the weight columns. The script below makes changes to two columns in the products table. There are times when storing the same data twice is a waste of space. In fact, there might be a situation in which we have plenty of processing power but not necessarily enough disk space. Since the weight in pounds can be calculated from weight in grams, we can create a calculated virtual column. The value of the column is created at execution when the data is queried.

The above T-SQL drops the existing column and creates a new calculated column. Please see the MSDN documentation for more details. When a database administrator adds a new column to a table, it is appended to the end of the table. How can we order the columns so the end user has them listed in the order they want?

Demo Database

One solution is to use a user-defined view. The above T-SQL statement creates a view that orders the columns virtually. Some end users might want to reorder the columns physically. In the next section, I will show you a technique that tools like Visual Studio Database Projects use to guarantee end state conditions such as column ordering. The code behind this process is relatively large since we must recreate the table with all the constraints, including check, foreign key, and primary key. The image below shows the three steps required to reorder the columns in a table physically. SEED of an identity column is the starting point for identity values in a table. In some scenarios it may be required to interrupt the automated sequence by allotting a new SEED value. Instead of the normal sequential flow, as a result of the above script, identity values for new records would start from now. While re-seeding identity values make sure that there is no chance of identity value duplication.

For example if you have records in the table with identity values 1 to And you re-seeding identity value to as in above script. The reason is that identity value is present in the table and your statement again tried to insert the same value. Before changing the data type of a column make sure that data would not be compromised while converting to new data type. This task may be performed by using the following T-SQL command. T-SQL would not provide you any extension related to the order of the columns. Through T-SQL you can not create new columns at a position other than at end of previous columns.

Disable foreign key sql server

Similarly you can not change the position of any previously created columns. You would have to use SSMS designer for this purpose or create a new table with the new column and migrate the data from the old table to the table. In either approach table recreation would need to take place. Again this facility is not accessible through T-SQL directly. You have to make use of SSMS designer. Go to table design and set Is Identity value to No. Upon saving the changes the table would be dropped and recreated. Now table is without an identity column. You may find other cases where table drop and recreation needs to take place in SSMS designer. If you are using SSMS then configure it to not make changes that require table drop and re-creation. So that such changes through designer may only be made after proper analysis. Thank you so much for putting this out there. Your scenario relates to section " Changing data type of any column ". Using designer would recreate the table in this case also.

Following code may be used for any modification in datatype of a column. Such a simple question, you did not answer above but I am very green and would appreciate your input. What about simply modifying the size of a column. Currently my colmn is varchar and I want to change to varchar max. View all my tips. Related Articles. Best practices for working with read only databases in SQL Server. Back To Top Hi Simon, The change to the table structure will create locks, so any inserts that occur after this process starts would be blocked until the process completes. It is possible that a duplicate value could be inserted creating an issue when you try to create the primary key, but not sure how your primary key values are generated and if this would even be an issue.