Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
data conversion in indexed views

Answered data conversion in indexed views

  • Sunday, January 06, 2013 8:47 PM
     
     
    Sign In to Vote
    0

    I have to set up a sql replication from server A(source) to server B(target) the datatype in server A is date and in server B is smalldatetime for 2 of the columns .one of the datatype is varchar in the source and in the target its char for 1 column.

    the table structure in the source A is

    TABLE [xx].[xxxx](
        [COLUMN1] [int] NOT NULL,
        [COLUMN2] [char](2) NOT NULL,
        [COLUMN3] [varchar](3) NOT NULL,
        [COLUMN4] [smallint] NULL,
        [COLUMN5] [char](3) NULL,
        [COLUMN6] [decimal](9, 2) NULL,
        [COLUMN7] [date] NULL,
        [COLUMN8] [char](2) NULL,
        [COLUMN9] [date] NULL,
        [COLUMN10] [char](7) NULL,

    table structure in the target B is

    TABLE [xx].[xxxx](
        [COLUMN1] [int] NOT NULL,
        [COLUMN2] [char](2) NOT NULL,
        [COLUMN3] [char](3) NOT NULL,
        [COLUMN4] [smallint] NOT NULL,
        [COLUMN5] [char](3)NOT NULL,
        [COLUMN6] [decimal](9, 2)NOT NULL,
        [COLUMN7] [SMALLdateTIME] NOT NULL,
        [COLUMN8] [char](2) NOT NULL,
        [COLUMN9] [SMALLdateTIME] NOT NULL,
        [COLUMN10] [char](7)NOT NULL,

    I created an indexed view and made the conversion to set up replication from indexed to view to table B (target)

     WHEN xxxx < CONVERT(DATE,'19000101',103) THEN CONVERT(SMALLDATETIME,'19000101',103)
                    WHEN xxxx BETWEEN CONVERT(DATE,'19000101',103) AND CONVERT(DATE,'20790607',103) THEN CONVERT(SMALLDATETIME,xxxx,103)
                    ELSE CONVERT(SMALLDATETIME,'20790606',103)
                END AS xxxx

    (CONVERT(CHAR(3),COLUMN3)) as COLUMN3

    When i executed the indexed view the column3 in the indexed view changed to null from notnull.

    VIEW STRUCTURE [xx].[xxxx_VW](
        [COLUMN1] [int] NOT NULL,
        [COLUMN2] [char](2) NOT NULL,
        [COLUMN3] [char](3)  NULL,
        [COLUMN4] [smallint]  NULL,
        [COLUMN5] [char](3) NULL,
        [COLUMN6] [decimal](9, 2) NULL,
        [COLUMN7] [SMALLdateTIME]  NULL,
        [COLUMN8] [char](2)  NULL,
        [COLUMN9] [SMALLdateTIME]  NULL,
        [COLUMN10] [char](7) NULL,

    What needs to be done to make sure the column3 wont change to null from notnull. Is it possible to set up sql replication from the indexed view with nulls to the target table B with all not nulls.


     

All Replies

  • Sunday, January 06, 2013 9:31 PM
     
     Answered Has Code
    Sign In to Vote
    0

    You  can get the column in the indexed view to be considered NOT NULL by using the ISNULL() function.  (Note that you must use ISNULL, not COALESCE - this is one of the cases where those two functions act differently).  So an example (first creating an indexed view without the ISNULL, and the column is nullable, then creating the same view with the ISNULL function, and the column is not nullable.

    use tempdb
go
Create Table dbo.Foo(fooi int primary key, Column3 varchar(3) Not Null);
go
Create View dbo.FooView With SchemaBinding As Select fooi, Convert(char(3), Column3) As Column3 From dbo.Foo;
go
Create Unique Clustered Index FooViewIdx On dbo.FooView(fooi);
go
Select 'Will show as null';
go
sp_help FooView;
go
Drop View dbo.FooView;
go

Create View dbo.FooView With SchemaBinding As Select fooi, IsNull(Convert(char(3), Column3), '') As Column3 From dbo.Foo;
go
Create Unique Clustered Index FooViewIdx On dbo.FooView(fooi);
go
Select 'Will show not null';
go
sp_help FooView;
go
Drop View dbo.FooView;
go
Drop Table dbo.Foo;

    Tom

     
  • Monday, January 07, 2013 9:26 PM
     
     
    Sign In to Vote
    0
    Thank you Tom