Rx_
Nothing In Moderation
- Local time
- Today, 04:44
- Joined
- Oct 22, 2009
- Messages
- 2,803
Converting some SQL over to TSQL? These were 3 useful things to know.
Shamelessly copied from SQL Server Central
SQL Server Central is a great place to ask TSQL Questions. But, be prepared to become a complete beginner when playing in their sandbox.
How many results are returned from this batch using this wildcard?
What will this code return?
What is the difference between the two?
Explanation: The correct answer is 2. The carot (^) as a wildcard performs a "not" match. In this case, those items that are like "ste" but not "step" are returned.
Ref: ^ Wildcard - http://msdn.microsoft.com/en-US/library/ms188342(v=sql.90).aspx
Correct answer: @X is now A<
Explanation: If an ELSE path is not provided in a CASE statement, the value it returns is NULL. In this case, the is not a matching value as @x is null (not initialized). As a result, a NULL is used in the ISNULL expression. However, since the size of the variable is char(1), it truncates the ISNULL value to 'A' only. The result is "@X is now A<".
Ref: CASE - http://msdn.microsoft.com/en-US/library/ms181765(v=sql.90).aspx
Explanation: There is no difference. Because NEWID() is a non-deterministic function it returns one value for each row. It doesn't matter if the returned value is enclosed within a sub-SELECT or not.
Ref: NEWID() - http://msdn.microsoft.com/en-us/library/ms190348(v=sql.105).aspx/html
Shamelessly copied from SQL Server Central
SQL Server Central is a great place to ask TSQL Questions. But, be prepared to become a complete beginner when playing in their sandbox.
How many results are returned from this batch using this wildcard?
Code:
DECLARE @i TABLE( mychar VARCHAR(50));
INSERT @i VALUES ('Steve'), ('Stephan'), ('Stephanie')
, ('Sterling'), ('Steppenwolf')
SELECT mychar
FROM @i
WHERE mychar LIKE 'Ste[^p]%'What will this code return?
Code:
DECLARE @x CHAR(1)
SET @x = CASE
WHEN @x = 'x' THEN 1
END
PRINT '@X is now ' + CAST(ISNULL(@x, 'A NULL') AS VARCHAR(50)) + '<'What is the difference between the two?
Code:
-- 1
SELECT ROW_NUMBER() OVER (ORDER BY NEWID())
FROM sys.all_columns
-- 2
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NEWID()))
FROM sys.all_columnsExplanation: The correct answer is 2. The carot (^) as a wildcard performs a "not" match. In this case, those items that are like "ste" but not "step" are returned.
Ref: ^ Wildcard - http://msdn.microsoft.com/en-US/library/ms188342(v=sql.90).aspx
Correct answer: @X is now A<
Explanation: If an ELSE path is not provided in a CASE statement, the value it returns is NULL. In this case, the is not a matching value as @x is null (not initialized). As a result, a NULL is used in the ISNULL expression. However, since the size of the variable is char(1), it truncates the ISNULL value to 'A' only. The result is "@X is now A<".
Ref: CASE - http://msdn.microsoft.com/en-US/library/ms181765(v=sql.90).aspx
Explanation: There is no difference. Because NEWID() is a non-deterministic function it returns one value for each row. It doesn't matter if the returned value is enclosed within a sub-SELECT or not.
Ref: NEWID() - http://msdn.microsoft.com/en-us/library/ms190348(v=sql.105).aspx/html
