cursors with variables

need to have values set to the variables before declaring the cursor
i had a declaration section with out settings and this failed

Like not vulnerable to injection

create table #test (name varchar(100))   
insert into #test values ('fgdgfdfg'),('cxvxbcvb'),('tryuryry')
declare @like varchar(100) = 'f; select * from #test --'
select * from #test where name like @like + '%'

drop table #test

what do you think happens with appended spaces in sql

select len('dfdf       ')

select len('       dfdf')

in oracle too!


select * from
(

select 'sdff   ' a  from dual
)  main

where main.a ='sdff'

order by on binary cast as bigint

order by cast(sequence_no as bigint)

will not order by correctly

not in and not exists

here

Trying to pass a table-valued parameter with 11 column(s) where the corresponding user-defined table type requires 12 column(s).

select tt.name,count( c.name) from sys.table_types tt
inner join sys.columns c on c.object_id = tt.type_table_object_id
group by tt.name

View dependencies

in ssms is not always correct because it depends on sysdepends



this is more accurate

select t.name as TableWithForeignKey, fk.constraint_column_id as FK_PartNo , c.name as ForeignKeyColumn
from sys.foreign_key_columns as fk
inner join sys.tables as t on fk.parent_object_id = t.object_id
inner join sys.columns as c on fk.parent_object_id = c.object_id and fk.parent_column_id = c.column_id
where fk.referenced_object_id = (select object_id from sys.tables where name = 'BLAH')
order by TableWithForeignKey, FK_PartNo

if your'e lazy and have simple "and"s separated by "or"s

then neat "and"s and or"s work like parentheses

select * from

(

select 1 a , 'hi there' b , 102 c

union

select 2 a , 'hi there' b , 103 c

union

select 10 a , 'hi there' b , 103 c

union

select 9 a , 'hi there' b , 103 c

union

select 8 a , 'by there' b , 103 c

union

select 7 a , 'by there' b , 103 c

union

select 6 a , 'by there' b , 10 c

) as main

where b = 'by there'

and a=6

or a > 1

and a < 4

and b = 'hi there'

or a = 10

and b = 'hi there'

top variable needs parentheses

declare @int int = 10

select top (@int) * from

bozo

more things done to improve sql

if force order hint is used - put left joins after inner joins
create indicies on temp tables after inserts

recent things done to an important and complex sql query to improve performance

1. changed cross- apply to cte's
2. added indices that had all the output columns needed instead of using clustered index which forced more reads
3. removed max varchar columns from initial query to avoid key lookups and retrieved them at end of sql

another simple sql question

drop table x
create table x(x int, y int)
insert into x values (1,2)
update  x set x=y , y=x
select * from x

what happens

Would you believe...

select 1 where  'Draft' like  '%' + 'Dr' + '%'
select 1 where    '%' + 'Dr' + '%' like  'Draft'


i guess its obvious that what follows like is a subset

today I discovered that there is in sql

a switch to statement

object_id needs the schema

ouch and duh

GO's Kill variables

I learned the hard way

An aggregate may not appear in the set list of an UPDATE statement

i just used a CTE

sql server computed column with case

blah as case when ...

cross apply vs join on aggregate

thought this was great

sql joins