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»»

T-SQL Expand / Collapse
Author
Message
Posted Tuesday, January 4, 2011 12:36 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 4:16 AM
Points: 330, Visits: 197
SQL 2000
Post #1042200
Posted Tuesday, January 4, 2011 12:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:29 AM
Points: 12,945, Visits: 10,716
sathishmcc (1/4/2011)
SQL 2000


It probably has something to do with the ancient version that you are using
However, I've set the compatability level to 80 on my test database and the query still worked with me. Maybe because I'm using a newer client tool?




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 #1042218
Posted Tuesday, January 4, 2011 1:24 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 4:16 AM
Points: 330, Visits: 197
Thanks for your reply sir
Post #1042226
Posted Tuesday, January 4, 2011 7:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 8:11 PM
Points: 5,356, Visits: 8,910
Hugo Kornelis (1/3/2011)
john.moreno (1/3/2011)
Hugo Kornelis (1/3/2011)

After reading your reply, I started to doubt, so I copied the script, pasted it in SSMS, changed it to work for SQL Server 2005


Which is a bit of a problem -- 5% of the respondents said zero, zero. I image that most if not all of them were thinking 2000/2005, where that would be the correct answer. I noticed the syntax, but picked the right answer because I remembered someone pointing out this as a new feature in 2008, if not for that I would have thought it was a trick question and picked zero, zero.

Though I agree that it would have been better to include the version in the question text, or to use a more backward compatible syntax, I don't thnik this should affect the result of the question much. It's 2011 already; the first CTP for SQL Server 2011 has been out for quite some time already - I think it's safe to assume version 2008 when nothing is mentioned explicitly.


And this gets into what I didn't like about this question - it was needlessly tied to a specific version of SQL. One simple change (replace the insert values (),(),() with separate insert statements) would have made this question applicable to versions back to SQL 2000. A second set of minor changes (replace the table variable with a temporary table; replace the nvarchar with varchar) would have made this question applicable to versions back to SQL 6.5 - if not even earlier. (And yes - I did just try this code on versions 6.5 and 2000!) It wouldn't have compromised the point that the question was making at all.

I think that it's upon the QotD authors to attempt to write their questions to apply to as many versions of sql as possible (obviously, when writing about features specific to one version, this wouldn't apply). This question should have been applicable as written to at least SQL 2000.


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1042373
Posted Tuesday, January 4, 2011 7:42 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:58 PM
Points: 2,561, Visits: 3,842
WayneS (1/4/2011)
Hugo Kornelis (1/3/2011)
john.moreno (1/3/2011)
Hugo Kornelis (1/3/2011)

After reading your reply, I started to doubt, so I copied the script, pasted it in SSMS, changed it to work for SQL Server 2005


Which is a bit of a problem -- 5% of the respondents said zero, zero. I image that most if not all of them were thinking 2000/2005, where that would be the correct answer. I noticed the syntax, but picked the right answer because I remembered someone pointing out this as a new feature in 2008, if not for that I would have thought it was a trick question and picked zero, zero.

Though I agree that it would have been better to include the version in the question text, or to use a more backward compatible syntax, I don't thnik this should affect the result of the question much. It's 2011 already; the first CTP for SQL Server 2011 has been out for quite some time already - I think it's safe to assume version 2008 when nothing is mentioned explicitly.


And this gets into what I didn't like about this question - it was needlessly tied to a specific version of SQL. One simple change (replace the insert values (),(),() with separate insert statements) would have made this question applicable to versions back to SQL 2000. A second set of minor changes (replace the table variable with a temporary table; replace the nvarchar with varchar) would have made this question applicable to versions back to SQL 6.5 - if not even earlier. (And yes - I did just try this code on versions 6.5 and 2000!) It wouldn't have compromised the point that the question was making at all.

I think that it's upon the QotD authors to attempt to write their questions to apply to as many versions of sql as possible (obviously, when writing about features specific to one version, this wouldn't apply). This question should have been applicable as written to at least SQL 2000.


Didn't Steve make a comment last year that we should assume SQL Server 2008 unless stated otherwise by the author? I though I remember reading that.

If you want questions to be backward compatible to 2000, why not earlier than that?

It would be nice, though, for authors to state on what version they ran their code.
Post #1042416
Posted Tuesday, January 4, 2011 7:49 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 29, 2014 10:20 AM
Points: 1,740, Visits: 6,366
WayneS (1/4/2011)
[quote]I think that it's upon the QotD authors to attempt to write their questions to apply to as many versions of sql as possible (obviously, when writing about features specific to one version, this wouldn't apply). This question should have been applicable as written to at least SQL 2000.


I can't see any point in going back before SQL2005, as that's the oldest supported version. How many of us even have 2000 installed any more? I don't so couldn't test against it even if I wanted to

And no, I haven't submited a QotD yet either - but did write half of one before someone else got there first with a much better effort!
Post #1042424
Posted Tuesday, January 4, 2011 9:22 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, September 7, 2014 10:22 PM
Points: 1,126, Visits: 1,387
Toreador (1/4/2011)
WayneS (1/4/2011)
[quote]I think that it's upon the QotD authors to attempt to write their questions to apply to as many versions of sql as possible (obviously, when writing about features specific to one version, this wouldn't apply). This question should have been applicable as written to at least SQL 2000.


I can't see any point in going back before SQL2005, as that's the oldest supported version. How many of us even have 2000 installed any more? I don't so couldn't test against it even if I wanted to

And no, I haven't submited a QotD yet either - but did write half of one before someone else got there first with a much better effort!

As a author, I also assume SQL 2008 because I read in one post from Steve that we should assume SQL 2008 if it is not mentioned. I used new syntax to insert the records for those who don't know the new syntax and they can have idea about that.
Still its better to mention the SQL Server version when you post the QofD/article, next time I will take care


Thanks
Post #1042801
Posted Wednesday, January 5, 2011 6:16 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, November 6, 2013 12:34 PM
Points: 480, Visits: 214
To complicated. Yo can solve it using CHARINDEX:

DECLARE @inputValue NVARCHAR(20)
DECLARE @student TABLE
(Id INT PRIMARY KEY IDENTITY(1,1),
StudentName NVARCHAR(50),
StudentResult INT)

INSERT INTO @student
VALUES( 'Hardy', 100), ('Rocky', 98), ('Panky', 99)

--Select 1
SET @inputValue = 'Hardy'
SELECT * FROM @student WHERE StudentName IN (@inputValue)

--Select 2
SET @inputValue = 'Hardy,Rocky'
SELECT * FROM @student WHERE CHARINDEX(StudentName, @inputValue) > 0

Saluti
Post #1042989
Posted Wednesday, January 5, 2011 9:57 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:00 PM
Points: 2,160, Visits: 2,191
Alberto IT (1/5/2011)
To complicated. Yo can solve it using CHARINDEX:


As long as you don't mind incorrect results:

DECLARE @inputValue NVARCHAR(20)
DECLARE @student TABLE
(Id INT PRIMARY KEY IDENTITY(1,1),
StudentName NVARCHAR(50),
StudentResult INT)

INSERT INTO @student
VALUES( 'Hardy', 100), ('Rocky', 98), ('Panky', 99), ('Ardy', 99)

--Select 1
SET @inputValue = 'Hardy'
SELECT * FROM @student WHERE StudentName IN (@inputValue)

--Select 2
SET @inputValue = 'Hardy,Rocky'
SELECT * FROM @student WHERE CHARINDEX(StudentName, @inputValue) > 0

Ardy shouldn't be returned but it is.
Post #1043177
Posted Wednesday, January 5, 2011 10:41 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, November 6, 2013 12:34 PM
Points: 480, Visits: 214
UMG Developer (1/5/2011)
Alberto IT (1/5/2011)
To complicated. Yo can solve it using CHARINDEX:


As long as you don't mind incorrect results:

DECLARE @inputValue NVARCHAR(20)
DECLARE @student TABLE
(Id INT PRIMARY KEY IDENTITY(1,1),
StudentName NVARCHAR(50),
StudentResult INT)

INSERT INTO @student
VALUES( 'Hardy', 100), ('Rocky', 98), ('Panky', 99), ('Ardy', 99)

--Select 1
SET @inputValue = 'Hardy'
SELECT * FROM @student WHERE StudentName IN (@inputValue)

--Select 2
SET @inputValue = 'Hardy,Rocky'
SELECT * FROM @student WHERE CHARINDEX(StudentName, @inputValue) > 0

Ardy shouldn't be returned but it is.


Good point! CHARINDEX should work only with short and controled set of data.
Post #1043212
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse