Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

Your profile

Tools

Authors

Main Categories

Search

XML Feeds

RSS 2.0: Posts, Comments
Atom: Posts, Comments

What is RSS?

Google Ads

« Get weekly transactions showing Monday's date

Observing - Optimize for Ad Hoc Workload server option »

comments

When changing column data types use ALTER TABLE TableName ALTER Column syntax, don't drop and recreate column

by SQLDenis on Apr 10, 2011 in categories Data Modelling & Design, Database Programming, Microsoft SQL Server Admin, Microsoft SQL Server

Instapaper

Someone asked how to change the data type from datetime to datetime2

Here is the question

I have a SQL Server 2005 database with a datetime column. There is already data in the table but now the customer needs dates before 1753. So I decided to migrate the database to a SQL Server 2008 to use the datetime2 type.

However I can't just switch the type of the column from datetime to datetime2. Is there a way to do this conversion or do I have to reimport the data?
Someone else answered the following

Here is an answer that this person got from someone

You could add the new column.

UPDATE Table SET NewColumn = OldColumn

delete the old column

then rename the new column.

This of course is highly inefficient. Just imagine running that suggestion on a table with millions or billions of rows.

My approach would be to do this instead: ALTER TABLE TableName ALTER COLUMN ColumnName datetime2

Let's take a closer look at the T-SQL needed for this

First create the following table and insert one row

tsql

Line number Off | Hide | Select all

USE tempdb
GO
CREATE TABLE Test(SomeDate DATETIME)
INSERT Test values ('20110410')
SELECT * FROM Test
GO

Output
-----------------
2011-04-10 00:00:00.000

Datetime accepts dates in the range from January 1, 1753, through December 31, 9999. If you try to insert a value before January 1, 1753, it will fail
Run the code below which will try to insert January 1, 1600, to see the error

tsql

Line number Off | Hide | Select all

INSERT Test values ('16000101')

Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Now we will change the column from datetime to datetime2.
The syntax looks like this

ALTER TABLE <TableName> ALTER column <ColumnName> <New date type>

For our table the syntax will be the following: ALTER TABLE Test ALTER column SomeDate datetime2
Run the code below to make that happen

tsql

Line number Off | Hide | Select all

ALTER TABLE Test ALTER column SomeDate datetime2
GO

So now, if we try to insert January 1, 1600, it should succeeed

tsql

Line number Off | Hide | Select all

INSERT Test values ('16000101')

Now, you can look what is in the table

tsql

Line number Off | Hide | Select all

SELECT * FROM Test
GO

Output
---------------------------
2011-04-10 00:00:00.0000000
1600-01-01 00:00:00.0000000

Just be aware that if you are changing data types, make sure that what you change to can hold the current values. If you are changing from varchar to integer, make sure you only have values that can be converted to integers, the operation will fail if they can't be converted to integers.

Conclusion

Learn the product and learn it well. Don't overly depend on the wizards in SSMS, and if you use SSMS, hit the Script button to see what kind of T-SQL SSMS generates.

The wizards are nice but sometimes they get it wrong. Here is the code that the wizard generates to change the column

tsql

Line number Off | Hide | Select all

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_Test
(
SomeDate datetime2(7) NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_Test SET (LOCK_ESCALATION = TABLE)
GO
IF EXISTS(SELECT * FROM dbo.Test)
EXEC('INSERT INTO dbo.Tmp_Test (SomeDate)
SELECT CONVERT(datetime2(7), SomeDate) FROM dbo.Test WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.Test
GO
EXECUTE sp_rename N'dbo.Tmp_Test', N'Test', 'OBJECT'
GO
COMMIT

I definitely don't want that either, that creates a whole new table..yikes

Spend some time in Books On Line, maybe every day at lunch open a random topic and read it for half and hour and run the code examples. If you commute, download the topic to your local device or hit the online version and study it. Another good way to learn is of course hitting the newsgroups where you will see top notch advice from SQL Server experts

The more you know, the better it will be for you and your employer, if suddenly you can prove that yes, we can do this and here is a better way I found, you will be rewarded sooner or later.

*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum

About the Author

Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.

Social Sitings

Article Views: 6556 views

6 comments

Comment from: Justin Dearing [Visitor]

· http://www.justaprogrammer.net

I'm going off on a bit of a tangent here, but the handle of the person asking that question is "TheQuant." Now it would be fair to assume this person is not a programmer or a DBA by trade, but makes heavy use of SQL Server. I recently switched jobs and now I work with several such people, although not finance people in this case.

My point in all this is that while there is a good community of SQL server experts that are developers and DBAs, I don't see quants, analysts, etc in these communities. Perhaps I don't see them because they are listening and not speaking. However, lately I've been wondering if there are better ways to cater to these people? Perhaps there could be a Microsoft cert track that combines Excel, T-SQL and BI. Also, perhaps user groups could form a quant/analyst SIG.

So am I completely off the mark here or do other see similar things?

04/11/11 @ 11:40

Comment from: SQLDenis [Member]

I see similar things, where people use SQL and then sometimes run really bad bad queries that bring the server to its knees like populating a #temp table with 100 million rows

04/11/11 @ 12:08

Comment from: Justin Dearing [Visitor] · http://www.justaprogrammer.net

Denis, but how do we engange these people? I'm working on internal training in my company, but how do we connect them with the resources devs and DBAs utilize now? Should there be a PASS VC? (Also maybe I should join PASS but thats a different story.)

04/11/11 @ 14:01

Comment from: SQLDenis [Member]

One option is doing lunch and learn sessions where you can present every other day while they eat so that you don't 'waste' their valuable time
We for example have an internal wiki, you can setup one yourself where you can have best practices or how to documents
It is hard because some of them feel like it is a waste of their time since the only run a query once a month, they are not willing to learn

There is a big disconnect between operations and IT usually.....

04/11/11 @ 14:06

Comment from: Naomi Nosonovsky [Member]

What if you need to add a column? How does SQL Server handle it?

04/12/11 @ 07:58

Comment from: SQLDenis [Member]

Naomi, syntax is pretty similar

ALTER TABLE Test ADD SomeColumn varchar(100)
GO

Of course if you do that on a table like the following you will get fragmentation

See script below

USE tempdb
GO

CREATE TABLE Test(SomeDate DATETIME PRIMARY key)
GO

INSERT Test
SELECT DATEADD(d,1,number - 5000)
FROM master..spt_values
WHERE type = 'P'
UNION ALL
SELECT DATEADD(d,1,number)
FROM master..spt_values
WHERE type = 'P'

DBCC SHOWCONTIG ('Test')

DBCC SHOWCONTIG scanning 'Test' table...

Table: 'Test' (1146291689); index ID: 1, database ID: 2

TABLE level scan performed.

- Pages Scanned................................: 9

- Extents Scanned..............................: 7

- Extent Switches..............................: 7

- Avg. Pages per Extent........................: 1.3

- Scan Density [Best Count:Actual Count].......: 25.00% [2:8]

- Logical Scan Fragmentation ..................: 11.11%

- Extent Scan Fragmentation ...................: 0.00%

- Avg. Bytes Free per Page.....................: 359.1

- Avg. Page Density (full).....................: 95.56%

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

ALTER TABLE Test ADD SomeColumn varchar(100)
GO

DBCC SHOWCONTIG ('Test')

DBCC SHOWCONTIG scanning 'Test' table...

Table: 'Test' (1146291689); index ID: 1, database ID: 2

TABLE level scan performed.

- Pages Scanned................................: 9

- Extents Scanned..............................: 7

- Extent Switches..............................: 7

- Avg. Pages per Extent........................: 1.3

- Scan Density [Best Count:Actual Count].......: 25.00% [2:8]

- Logical Scan Fragmentation ..................: 11.11%

- Extent Scan Fragmentation ...................: 0.00%

- Avg. Bytes Free per Page.....................: 359.1

- Avg. Page Density (full).....................: 95.56%

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

UPDATE Test
SET SomeColumn = REPLICATE('a',99)

DBCC SHOWCONTIG ('Test')

DBCC SHOWCONTIG scanning 'Test' table...

Table: 'Test' (1146291689); index ID: 1, database ID: 2

TABLE level scan performed.

- Pages Scanned................................: 120

- Extents Scanned..............................: 21

- Extent Switches..............................: 40

- Avg. Pages per Extent........................: 5.7

- Scan Density [Best Count:Actual Count].......: 36.59% [15:41]

- Logical Scan Fragmentation ..................: 22.50%

- Extent Scan Fragmentation ...................: 28.57%

- Avg. Bytes Free per Page.....................: 4000.0

- Avg. Page Density (full).....................: 50.58%

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

04/12/11 @ 08:28

Comment feed for this post

LessThanDot

Your profile

Tools

Tag cloud

Authors

Main Categories

Search

XML Feeds

Google Ads

When changing column data types use ALTER TABLE TableName ALTER Column syntax, don't drop and recreate column

Conclusion

About the Author

6 comments

Leave a comment

LessThanDot

LTD Social Sitings

Your profile

Tools

Tag cloud

Authors

Main Categories

Search

XML Feeds

Google Ads

When changing column data types use ALTER TABLE TableName ALTER Column syntax, don't drop and recreate column

Conclusion

About the Author

6 comments

Leave a comment