SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ANSI Padding


ANSI Padding

Author
Message
Tom Thomson
Tom Thomson
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36169 Visits: 12792
Comments posted to this topic are about the item ANSI Padding

Tom

Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99843 Visits: 13323
Good, but difficult question. I had to read quite some BOL pages to get this one right.
And only 1 point?


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Mayank Parmar
Mayank Parmar
SSC Eights!
SSC Eights! (807 reputation)SSC Eights! (807 reputation)SSC Eights! (807 reputation)SSC Eights! (807 reputation)SSC Eights! (807 reputation)SSC Eights! (807 reputation)SSC Eights! (807 reputation)SSC Eights! (807 reputation)

Group: General Forum Members
Points: 807 Visits: 100
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
--------------------------------------------------------------------------------
Carlo Romagnano
Carlo Romagnano
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9363 Visits: 3441
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.
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99843 Visits: 13323
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?


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Christian Buettner-167247
Christian Buettner-167247
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6771 Visits: 3889
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(DEFINESad[master].[dbo].[Comp].[B]=[master].[dbo].[Comp].[B]))
|--Index Seek(OBJECTSad[master].[dbo].[Comp].[IComp]), SEEKSad[master].[dbo].[Comp].[B]=CONVERT_IMPLICIT(int,[@1],0)) ORDERED FORWARD)

StmtText
--------------------------------------
SELECT B FROM dbo.Comp WHERE B = 1;

StmtText
------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Compute Scalar(DEFINESad[master].[dbo].[Comp].[B]=[master].[dbo].[Comp].[B]))
|--Compute Scalar(DEFINESad[master].[dbo].[Comp].[B]=([master].[dbo].[Comp].[a]+(2))+[master].[dbo].[Comp].[a]))
|--Table Scan(OBJECTSad[master].[dbo].[Comp]), WHERESad(([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
Carlo Romagnano
Carlo Romagnano
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9363 Visits: 3441
[b]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.
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99843 Visits: 13323
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 :-D (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.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Toreador
Toreador
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4700 Visits: 8170
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!
DugyC
DugyC
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1936 Visits: 779
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

_____________________________________________________________________
"The difficult tasks we do immediately, the impossible takes a little longer"
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search