January 10, 2011 at 11:50 pm
Comments posted to this topic are about the item ANSI Padding
Tom
January 10, 2011 at 11:51 pm
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
January 11, 2011 at 12:04 am
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
--------------------------------------------------------------------------------
January 11, 2011 at 12:54 am
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.
January 11, 2011 at 1:02 am
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
January 11, 2011 at 1:40 am
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
January 11, 2011 at 1:46 am
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.
January 11, 2011 at 1:54 am
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
January 11, 2011 at 2:14 am
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!
January 11, 2011 at 2:18 am
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]
January 11, 2011 at 2:21 am
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!
January 11, 2011 at 2:31 am
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.
Best Regards,
Chris Büttner
January 11, 2011 at 3:15 am
Good question - I see only 8% have got the correct answer at the moment.
January 11, 2011 at 3:22 am
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 😉
January 11, 2011 at 3:50 am
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).
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply