Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

ANSI Padding Expand / Collapse
Author
Message
Posted Monday, January 10, 2011 11:50 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 5:59 PM
Points: 8,748, Visits: 9,296
Comments posted to this topic are about the item ANSI Padding

Tom
Post #1045703
Posted Monday, January 10, 2011 11:51 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:51 PM
Points: 13,639, Visits: 10,534
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1045704
Posted Tuesday, January 11, 2011 12:04 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, September 21, 2011 4:03 AM
Points: 713, 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
--------------------------------------------------------------------------------
Post #1045709
Posted Tuesday, January 11, 2011 12:54 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:23 AM
Points: 2,509, Visits: 2,386
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.
Post #1045723
Posted Tuesday, January 11, 2011 1:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:51 PM
Points: 13,639, Visits: 10,534
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1045729
Posted Tuesday, January 11, 2011 1:40 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 22, 2014 12:47 AM
Points: 2,840, Visits: 3,872
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].[B]=[master].[dbo].[Comp].[B]))
|--Index Seek(OBJECT[master].[dbo].[Comp].[IComp]), SEEK[master].[dbo].[Comp].[B]=CONVERT_IMPLICIT(int,[@1],0)) ORDERED FORWARD)

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

StmtText
------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Compute Scalar(DEFINE[master].[dbo].[Comp].[B]=[master].[dbo].[Comp].[B]))
|--Compute Scalar(DEFINE[master].[dbo].[Comp].[B]=([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
Post #1045734
Posted Tuesday, January 11, 2011 1:46 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:23 AM
Points: 2,509, Visits: 2,386
[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.
Post #1045735
Posted Tuesday, January 11, 2011 1:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:51 PM
Points: 13,639, Visits: 10,534
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.




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

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1045737
Posted Tuesday, January 11, 2011 2:14 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 29, 2014 10:20 AM
Points: 1,740, Visits: 6,366
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!
Post #1045742
Posted Tuesday, January 11, 2011 2:18 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 27, 2013 4:28 AM
Points: 1,248, 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!



_____________________________________________________________________
"The difficult tasks we do immediately, the impossible takes a little longer"
Post #1045744
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse