Friday, February 10, 2012

Changing Data Types in SqlServer

I have upsized my access database to Sql Server - Fine. Everything exported in order, but..

I haven't had any success changing the data types and sizes
Ex. [nvarchar] [255] to [varchar] [1000].

I have tried in Sql Server to:

ALTER TABLE dbo.products
ALTER COLUMN description type [varchar] [1000]

Obviously this isn't a correct statement, however, not obvious to me..

Thanks much in advance for help.Q1 I haven't had any success changing the data types and sizes Ex. [nvarchar] [255] to [varchar] [1000]. I have tried in Sql Server to:

ALTER TABLE dbo.products
ALTER COLUMN description type [varchar] [1000]

Obviously this isn't a correct statement, however, not obvious to me..


A1 If the column name is "description type", sic, consider changing it to DescriptionType, or possibly description_type, to avoid all sorts of similar issues in the future; if this is not possible, more success is likely using:

a) [description type] (sic)

rather than the unbracketed form

b) description type

In any event the general syntax is:
Alter Table [Table_Name]
Alter Column [Column_Name]

For Example:
ALTER TABLE [dbo].[products]
ALTER COLUMN [description type] [varchar] (1000)

For a demonstration example, run the following:

Use TempDB
Go

if exists (select * from dbo.sysobjects where id = object_id(N'[Demo_Information]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Demo_Information]
GO

CREATE TABLE [dbo].[Demo_Information] (
[DemoPrimaryKey] [int] IDENTITY (1, 1) NOT NULL ,
[AlterTypeTargetData] [nvarchar] (255) NULL ,
CONSTRAINT [PK_Demo_Information] PRIMARY KEY CLUSTERED
(
[DemoPrimaryKey]
)
)
Go

INSERT INTO
[dbo].[Demo_Information]
([AlterTypeTargetData])
VALUES
('012342578901234257890123425789012342578901234257 89')

INSERT INTO
[dbo].[Demo_Information]
([AlterTypeTargetData])
VALUES
('01234257890123425789')

Select 'Note Type_Name and Precision:' As 'BEFORE Alter Table statement'
Exec sp_Columns Demo_Information
Go

Alter Table [Demo_Information]
Alter Column [AlterTypeTargetData] [Varchar] (1000) Null
Go

Select 'Note Type_Name and Precision:' As 'AFTER Alter Table statement'
Exec sp_Columns Demo_Information
Go

-- To Change the type back to nVarChar (255)
-- Alter Table [Demo_Information]
-- Alter Column [AlterTypeTargetData] [nVarchar] (255) Null
-- Go

No comments:

Post a Comment