ANSI Padding

  • Comments posted to this topic are about the item ANSI Padding

    Tom

  • Good, but difficult question. I had to read quite some BOL pages to get this one right.

    And only 1 point?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Very good question containing lot of information.

    But I always suggest to break such single question in multiple questions having just (Yes/No) options rather then having so many options in a single question.

    So people won't disappoint if they choose the wrong answer.

    Regards,

    --------------------------------------------------------------------------------
    Mayank Parmar
    Software Engineer

    Clarion Technologies
    SEI CMMI Level 3 Company

    8th Floor, 803, GNFC info Tower,
    SG Highway, Ahmedabad - 380 054,
    Gujarat, India.
    www.clariontechnologies.co.in

    Email: mayank.parmar@clariontechnologies.co.in
    MSN : mayank.parmar@clariontechnologies.co.in
    Mobile: +91 9727748789
    --------------------------------------------------------------------------------

  • Koen (da-zero) (1/10/2011)


    Good, but difficult question. I had to read quite some BOL pages to get this one right.

    And only 1 point?

    I get it wrong, because I don't want waste my time to learn BOL for a deprecated option.

  • Carlo Romagnano (1/11/2011)


    Koen (da-zero) (1/10/2011)


    Good, but difficult question. I had to read quite some BOL pages to get this one right.

    And only 1 point?

    I get it wrong, because I don't want waste my time to learn BOL for a deprecated option.

    And what if your company or your client uses an older version of SQL Server (there are still some SQL 2000 or older out there. SQL Server 2005 is still common)? Doesn't it make sense to know some of the implications of such a setting?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Hi,

    I almost got the question right, but failed because of the usage of index on computed columns.

    I have tested it with the following script and the index seems to be unused when ANSI_PADDING is off:

    SET ANSI_PADDING ON

    GO

    DROP TABLE dbo.Comp;

    CREATE TABLE dbo.Comp (a int NOT NULL PRIMARY KEY NONCLUSTERED, B AS (a+1*2)+a);

    WITH A AS (SELECT 1 AS Num UNION ALL SELECT 1 UNION ALL SELECT 1

    ) ,B AS (SELECT A1.Num FROM A A1,A A2,A A3)

    INSERT INTO dbo.Comp

    SELECT ROW_NUMBER() OVER (ORDER BY B1.Num) Num FROM B B1, B B2, B B3;

    GO

    CREATE UNIQUE INDEX IComp ON dbo.Comp(B);

    GO

    SET NOCOUNT ON

    SET ANSI_PADDING ON

    GO

    SET SHOWPLAN_TEXT ON

    GO

    SELECT B FROM dbo.Comp WHERE B = 1;

    GO

    SET SHOWPLAN_TEXT OFF

    GO

    SET ANSI_PADDING OFF

    GO

    SET SHOWPLAN_TEXT ON

    GO

    SELECT B FROM dbo.Comp WHERE B = 1;

    GO

    SET SHOWPLAN_TEXT OFF

    GO

    StmtText

    --------------------------------------

    SELECT B FROM dbo.Comp WHERE B = 1;

    StmtText

    --------------------------------------------------------------------------------------------------------------------------------------------

    |--Compute Scalar(DEFINE:([master].[dbo].[Comp].=[master].[dbo].[Comp].))

    |--Index Seek(OBJECT:([master].[dbo].[Comp].[IComp]), SEEK:([master].[dbo].[Comp].=CONVERT_IMPLICIT(int,[@1],0)) ORDERED FORWARD)

    StmtText

    --------------------------------------

    SELECT B FROM dbo.Comp WHERE B = 1;

    StmtText

    ------------------------------------------------------------------------------------------------------------------------------------------------------------

    |--Compute Scalar(DEFINE:([master].[dbo].[Comp].=[master].[dbo].[Comp].))

    |--Compute Scalar(DEFINE:([master].[dbo].[Comp].=([master].[dbo].[Comp].[a]+(2))+[master].[dbo].[Comp].[a]))

    |--Table Scan(OBJECT:([master].[dbo].[Comp]), WHERE:((([master].[dbo].[Comp].[a]+(2))+[master].[dbo].[Comp].[a])=CONVERT_IMPLICIT(int,[@1],0)))

    Besides this, the answer

    Altering an index on a computed column will not work

    may not be fully correct either, since the following examples seem to work with ANSI_PADDING being either ON or OFF:

    ALTER INDEX IComp ON dbo.Comp SET (STATISTICS_NORECOMPUTE = ON,

    ALLOW_PAGE_LOCKS = ON

    );

    or ALTER INDEX IComp ON dbo.Comp REORGANIZE;

    Best Regards,

    Chris Büttner

  • Koen (da-zero)

    And what if your company or your client uses an older version of SQL Server (there are still some SQL 2000 or older out there. SQL Server 2005 is still common)? Doesn't it make sense to know some of the implications of such a setting?

    But the question is in SQL2008.

  • Carlo Romagnano (1/11/2011)


    Koen (da-zero)

    And what if your company or your client uses an older version of SQL Server (there are still some SQL 2000 or older out there. SQL Server 2005 is still common)? Doesn't it make sense to know some of the implications of such a setting?

    But the question is in SQL2008.

    Hmmm. You are certainly right 😀 (didn't notice it the first time)

    Allright, let's look at it from another perspective. Although this setting will always be on in future versions, you can still manipulate this setting in current versions (2008 and 2008 R2). It is still possible for example that you inherit some code from someone else that uses this setting. So I consider it still usefull to know about this feature and the implications it has.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Not a terribly useful question IMHO. OK, so you might inherit some legacy code, but hopefully most of us don't use any of the 'non-ansii' options any more for new code (if we ever did).

    As for the option about performance relative to SQL2000 - that's an 11-year-old unsupported version, we're not far off the day when SQL2005 becomes unsupported so what's the benefit of learning about its predecessor!

  • God damn it!! I would have got this one right had I read the MSDN linked page properly, it was my reference from google.

    I assumed VARCHAR & NVARCHAR would be treated the same, instead of reading further down and seeing that NVARCHAR is unaffected.

    Lost point, but through my own stupidity... that is frustrating!

    :rolleyes:

    _____________________________________________________________________
    [font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]

  • I got it right apart from the last option--the reference I was looking at didn't mention anything about TOP performance being affected by this. Not greatly saddened because I don't think it likely I'll ever run into this anyway, but more information is always handy to have!

  • Toreador (1/11/2011)


    As for the option about performance relative to SQL2000 - that's an 11-year-old unsupported version[...]

    SQL Server 2000 is not unsupported. Only the mainstream support has ended.

    http://blogs.msdn.com/b/sqlreleaseservices/archive/2008/02/15/end-of-mainstream-support-for-sql-server-2005-sp1-and-sql-server-2000-sp4.aspx

    Best Regards,

    Chris Büttner

  • Good question - I see only 8% have got the correct answer at the moment.

  • Christian Buettner-167247 (1/11/2011)


    SQL Server 2000 is not unsupported. Only the mainstream support has ended.

    So it's supported, but only if we pay for it.

    In my book, that means it's unsupported 😉

  • Usually, when I come to the forum while thinking that the question is not 100% acccurate, I encounter lots of similar remakrs already. Today, nobody has yet raised the point I want to make - so I'm beginning to doubt myself. Please tell me if I misread Books Online...

    On the page referenced from the question's explanation, it says:

    For more information about required SET option settings with indexed views and indexes on computed columns, see "Considerations When You Use the SET Statements" in SET (Transact-SQL).

    Following that page and scrolling down to the last paragraph, I read:

    If any one of these options is not set to the required values, (...) SQL Server will process SELECT statements on these tables or indexed views as if the indexes on computed columns or on the views do not exist.

    My interpretation of this text is that indexes on computed columns will not be used when ANSI_PADDING and various other SET options are not set to the required value. But the explanation in the question says the exact reverse: "(...) but existing indexes can be used" (emphasis is mine).

    Did I misinterpret the documentation? Or is Tom incorrect? (Christian's test code suggests the latter).


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Viewing 15 posts - 1 through 15 (of 32 total)

You must be logged in to reply to this topic. Login to reply