The other day I wanted to quickly create a table from an existing table in another database. If you are using SSMS (SQL Server Management Studio) 2005/2008, you know the quickest way to do this is to right click on the table and “Script Table As” à “Create To” à “New Query Editor Window”. When this is done, you would expect a clean create table script, with defaults, primary and foreign keys, but instead I received the following:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Table1](
[col1] [int] NULL,
[col2] [varchar](20) NULL,
[col3] [money] NULL,
[col4] [timestamp] NOT NULL,
[col5] [uniqueidentifier] NULL,
[col6] [xml] NULL
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[Table1] ADD [col7] [varchar](30) NULL
ALTER TABLE [dbo].[Table1] ADD [col8] [char](25) NULL
ALTER TABLE [dbo].[Table1] ADD [col9] [nchar](40) NULL
ALTER TABLE [dbo].[Table1] ADD [col10] [nvarchar](10) NULL
SET ANSI_PADDING ON
ALTER TABLE [dbo].[Table1] ADD [col11] [varchar](30) NULL
ALTER TABLE [dbo].[Table1] ADD [col12] [char](25) NULL
ALTER TABLE [dbo].[Table1] ADD [col13] [nchar](40) NULL
ALTER TABLE [dbo].[Table1] ADD [col14] [nvarchar](10) NULL
GO
SET ANSI_PADDING OFF
GO
Why aren’t all the columns created in the initial “Create Table” statement? The answer lies in how the columns were initially added to the originating table.
When using SSMS to create objects, certain ANSI settings are set on the connection to the server. To view these ANSI settings, navigate to “Tools” à “Options” à “Query Execution” à “SQL Server” à “ANSI”
So, by default, “Set ANSI_PADDING” is set to ON for all connections to the server opened via SSMS.
One problem is that not all tools are nice enough to set these options for you automatically. If, for example, I used SSMS to create this table initially:
Create Table Table1
(col1 int
,col2 varchar(20)
,col3 money
,col4 timestamp
,col5 uniqueidentifier
,col6 xml
)
Then as we add more columns to Table1, another tool is used to add the columns col7, col8, col9 and col10. This tool though doesn’t set the ANSI_PADDING to ON by default.
Alter Table Table1
Add col7 varchar(30)
,col8 char(25)
,col9 nchar(40)
,col10 nvarchar(10)
After a while, more columns need to be added. This time the tool used to add the columns is SSMS and the defaults haven’t been changed. So ANSI_PADDING is turned on for the connection.
alter table Table1
add col11 varchar(30)
,col12 char(25)
,col13 nchar(40)
,col14 nvarchar(10)
Now when you generate the “Create Table” statement from SSMS, you will get the broken up statement with ANSI_PADDING ON for some columns and ANSI_PADDING OFF for others.
You can verify the ANSI_PADDING setting for each column by issuing the following statement:
SELECT Name,Is_ANSI_Padded
FROM sys.columns
where object_Name(object_ID) = 'Table1'
As you can see the ANSI_PADDED is set to off for columns col7 and col8, reflecting that they were added on a connection that didn’t set the ANSI_PADDED to ON.
But col9 and col10 were added on the same connection so why is there ANSI_PADDED set to ON? A quick glance at Books Online (BOL) states:
SET ANSI_PADDING is always ON for nchar and nvarchar. SET ANSI_PADDING OFF does not apply to the nchar or nvarchar data types.
So nchar and nvarchar will always be created with ANSI_PADDING ON.
Do we want to fix it and,if so, how do we do it?
First off, we need to understand what ANSI_PADDING is doing.
When ANSI_PADDING is set to ON:
· CHAR and BINARY (NULLable and non-NULLable) columns are filled with blanks or zeros to the length of the column.
· Trailing blanks and zeros in VARCHAR and VARBINARY columns are not trimmed, but the values are not padded to the end of the column.
When ANSI_PASSING is set to OFF:
· Non-NULLable CHAR and BINARY columns are filled with blanks or zeros to the length of the column.
· NULLable CHAR, BINARY, VARCHAR, and VARBINARY have zeros and blanks trimmed and are not padded to the end of the column.
Obviously, before you make any change you will want to test your applications for any issues with string manipulation or comparison on the columns affected columns.
How do we fix it?
Typically, a simple Alter Table <Table> Alter Column <Column> statement with the ANSI_PADDING ON set on the connection will do the trick.
If your SSMS doesn’t have ANSI_PADDING set to ON by default, you will need to turn it on for the connection:
Set ANSI_PADDING ON;
Then run the alter statement for each of the columns you want to change:
Alter Table Table1
Alter Column col7 varchar(30)
Alter Table Table1
Alter Column col8 char(25)
Now when you run query to look for ANSI_PADDING on each column
SELECT Name,Is_ANSI_Padded
FROM sys.columns
where object_Name(object_ID) = 'Table1'
After all that is done, when you go to create the table via script in SSMS, you will get the following:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Table1](
[col1] [int] NULL,
[col2] [varchar](20) NULL,
[col3] [money] NULL,
[col4] [timestamp] NOT NULL,
[col5] [uniqueidentifier] NULL,
[col6] [xml] NULL,
[col7] [varchar](30) NULL,
[col8] [char](25) NULL,
[col9] [nchar](40) NULL,
[col10] [nvarchar](10) NULL,
[col11] [varchar](30) NULL,
[col12] [char](25) NULL,
[col13] [nchar](40) NULL,
[col14] [nvarchar](10) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
No comments:
Post a Comment