more Murder They Wrote notes

Murder They Wrote was a day long training I attended in advance of SQLSaturday which tied into SQLSaturday. One way to guard against SQL injection is to use parameterized stored procedures. Sqlninja is a tool one may download to help with authoring SQL injection attacks. mySQL and Oracle are more prone to SQL injection than MSSQL which is probably the safest database option there is. If you need to dynamically build up a query string you will not be able to protect yourself with parameterized stored procedures. Instead use the sp_executesql sproc to run built up query string as it will allow for parameters to be placed inside of what is built up. Example: Password=@password Bind parameters in SSRS reports to stored procedures which take in parameterized inputs. The CURSOR stuff is really slow. It does rbar (row by agonizing row) scanning, and as bad as they are WHILE loops tend to be worse. This suggestion messed with my mind a little bit and the speakers seemed to say some conflicting things with regards to whether a CURSOR could exist without a WHILE loop or not. It was suggested that GO 5 in contrast to a mere GO will run the lines before it five times over and that as much is an example of a CURSOR without a WHILE loop. DBCC CHECKDB was given as an example of a CURSOR which didn't suck and a set-based approach (specify what to do but not how to do it) to CURSOR stuff was encouraged. There is a plan cache stored for each query, and a plan hash (execution plan) has to be unique for each, but all cursors use the same plan! ORM (Object Relational Mapping) tools like Entity Framework, NHibernate, Ruby on Rails, LLBL Gen Pro, and SQL Alchemy may write some really nasty SQL beneath the hood. Deadlocks can happen often in ORMs. A recipe for slowness characteristic of ORMs is to have these three things all in the mix together in the SQL created:

• nested views
• recursive views
• functions (especially those which are not inline table functions and are thus particularly slow... varieties of functions being:
        1. scalar
        2. table:
              1. inline
              2. multi-statement

Scalar functions return a simple type while table functions return a table. Multi-statement table functions must have the table to be returned declared at the outset and there will be a begin/end block around the around that which is returned. Inline table functions are all other functions not of this shape which hand back a table.) A nested view is a view which does a SELECT on top of another view or views instead of reaching directly into tables and Entity Framework has bad habit of making these in its SQL. This sort of mess will create a sucky plan in the optimizer and you should never nest views if you can avoid doing so. If view one queries view two which queries view three which actually queries tables as seen in Jason Brimhall's whiteboarding here then view one and view two are nested views. If true madness ensues and view three also reaches backwards against the progression to view one as much (view three that is) would be an example of a recursive view. You shouldn't do a SELECT * in lieu of calling out to just the columns you need in a table for tables that are huge and have numerous columns, and at the same time it is not recommended to have just the ninety of one hundred columns that you "need" specified either as this will make a bytes heavy outgoing request when you send the SQL itself across the network. Ask for just what you need and get back just what you need. The part of a where clause which goes seeking against the table id should go as far to the left as possible in a WHERE clause. Hit that first. sp_refreshview will refresh a view to make sure that columns weren't dropped in editing a referenced (or indirectly referenced) table. If you call out to columns numerically instead of by name in a view the view may get jacked up when columns are dropped from the table.