WAT - @@Rowcount, SELECT, DISTINCT, CTEs, and unexpected results.

 I was looking at a piece of code that was slow. The slow part was due to a "where (a.fielda = @param or a.fieldb = @param)". So I took it from 

declare @field varchar(10) = (select distinct (field) from myview a where (a.fielda = @param or a.fieldb = @param) and a.fieldc = @param2)

if @@rowcount > 0

BEGIN

blahblahblah

END

to

declare @field varchar(10);

with cte_distincter as 

(

select fielda from myview a where (a.fielda = @param) and a.fieldc = @param2

UNION ALL

select fielda from myview a where (a.fieldb = @param) and a.fieldc = @param2

)select distinct @field = fielda from cte_distincter

if @@rowcount >0

BEGIN

blahblahblah

END

Pretty good, right? 150-250x faster. Problem solved.

Except.. now the @@rowcount code doesn't fire right. Because the first code, event if it returns an empty set, has a @@rowcount of 1. The CTE code will have the @@rowcount = 0 if it's NULL. So, had to drop the CTE, and just went to using a derived table. Which works. But jeeze, talk about unexpected behavior. CTEs aren't always the solution!

declare @field varchar(10) = (select DISTINCT(fielda) from 

(

select fielda from myview a where (a.fielda = @param) and a.fieldc = @param2

UNION ALL

select fielda from myview a where (a.fieldb = @param) and a.fieldc = @param2

) a)

[WAT] fun with declare @blah = varchar!

Another in my "WAT" file. (Go find it on youtube, you'll laugh and groan) 

What happens if you don't assign a length to varchar? I swear I'd learned that it became varchar(30).
Running on SQL Server 2014 SP3. Bold are the selects that are getting returned.

DECLARE @blah VARCHAR 

SET @blah = '123456' 

SELECT @blah 

DECLARE @date DATETIME=getdate() 

SELECT CONVERT(VARCHAR, @date, 112) 

SELECT @blah = CONVERT(VARCHAR, @date, 112) 

SELECT @blah 

1 

2030302

2

[WAT] Fun with table variables

Run this code.  Did you expect it to act a certain way?  Why?  Because table variables.  Thanks to (crap, who was it? *sigh*) who pointed this out in his tempDB talk a year or so ago.


Update 2016/08/15 - I heard a good use for this at #SQLSatSA!   Use it as part of an ETL.  When it fails and rolls back, you know which batch you were on.  : D

DECLARE @blah TABLE (fielda VARCHAR(20))
INSERT INTO @blah
        (fielda)
SELECT 'a'
UNION ALL
SELECT 'b'
SELECT * FROM @blah

BEGIN TRANSACTION
UPDATE @blah
SET fielda = 'c'
SELECT * FROM @blah
ROLLBACK TRANSACTION

SELECT * FROM @blah

[Wat] Be careful what you end a job step in \

Had a job step that kept failing, though it ran when I ran it from SSMS. Start with simple, work towards complex.  And when I compared my code to the job step, they kept mismatching!  After the third time...

Create a job step with this:

select 'a' --test with a slash \ in the middle
select 'b' --test with a slash at the end \
select 'c' --test with a slash then a space \
select 'd' --last test no slash
--which of these will combine
--burma shave

aka

USE [msdb]
GO
EXEC msdb.dbo.sp_update_jobstep @job_id=N'5c84930d-3c6c-4408-9792', @step_id=4 ,
        @command=N'select ''a'' --test with a slash \ in the middle
select ''b'' --test with a slash at the end \
select ''c'' --test with a slash then a space \
select ''d'' --last test no slash
--which of these will combine
--burma shave'
GO

No problem, right?  A, B, C.  Set it to save to a output file, save, and run.  Now open that output file.



Wait, WAT?  Where's the C?

Open up the job step again... lo and behold, it combined lines... and commented a statement out.






Moral of the story - never end a line in a job step with a slash.

[WAT] Fun length issue with REPLACE in SSMS "Results to"

Just had this burn me, and don't remember having seen this issue before.

REPLACE changes the length of your columns, in Results to Text/Results to File.

Run this piece of code in SSMS. Query->Results To->Grid.

SELECT TOP 100 REPLACE(REPLACE(name,'&',';'),'/','#'), name FROM sysdatabases

Copy and paste on successive lines.  The same.
Now do the same thing, in Results to Text...
And now the same, Results to File...

Note that they're no longer the same length.  Seems the case at least since 2005.
The length of the header, which dictates the length (if doing fixed-width stuff) is twice as long.  That occurs even when the replaced characters are the same length.

Now let's see what our 2012++ friend, which gives us the metadata for a query, says.

SELECT * FROM sys.dm_exec_describe_first_result_set('
SELECT TOP 100 replace(replace(name,''&'','';''),''/'',''$'') as rname, name
FROM sysdatabases', NULL, 0)

Which gets you this fun nugget...



Hey look!  It went from nvarchar(128) (aka sysname) to nvarchar(4000).  Interesting!

Maybe this is normal/expected behavior.  But it made life difficult today, and experience is gained from doing it wrong, so here's your experience for the day.

[Wat] Leading Dots work in SQL queries? Well, that's some interesting syntax there, Lou.

Wound up looking at a failing piece of code.  The first thing was how it was called. 

EXEC .dbo.the_sp_name

...but it turns out, that part works.  As does this query: SELECT * FROM .sys.tables .  Had never seen that before.

[Wat] DECLAREing inside a loop? Yes, yes you can.

Came across this code today.  Sticking it in my personal 'Wat?' file (https://www.destroyallsoftware.com/talks/wat/)

Lump this in with the "hey, did you know you can DECLARE and set to a value without being inside a SP"?

This code works on 2008 and higher.


DECLARE @i INT = 1

WHILE @i < 5
BEGIN
DECLARE @a INT = 4
PRINT @i
SET @i = @i+1
END