Transcript SQL_and_Microsoft_SQL_Serverx

Thank You Sponsors!
Visit the Sponsor tables to
enter their end of day raffles.
Turn in your completed Event
Evaluation form at the end of the
day in the Registration area to be
entered in additional drawings.
Want more free training? Check out the
Houston Area SQL Server User Group
which meets on the 2nd Tuesday of each
month. Details at
http://houston.sqlpass.org
6/13/2015
SQL
& Microsoft SQL Server for Builders of Software
(SQL Server - Tips, Tricks, and Other Good Stuff)
Dane Schilling
Year Old Headspringer
Sprung: 4/1/2014
You’re in good hands...
● 6 years of report writing
o
Cognos, Crystal, and SSRS
● 3 years of MS SQL Server Administration
● 3 years of .NET Development
● Passionate about SQL
● Learning from new mistakes everyday
Agenda
● Things to check
● DR/HA
● T-SQL
● Goodies
Oddities...
● Google with keyword tsql
● Who to trust: Paul Randal, Kimberly Tripp,
Brent Ozar..and others
● Great resource: www.sqlservercentral.com
● It depends
Things to check
Really...please check these things
Recovery Model
● Development and test databases should be
in simple mode
● Databases should not be left in bulk-logged
● Don’t arbitrarily change recovery models
o
This will affect backups and behavior
● If not in simple mode you MUST take log
backups
Two Gotchas...
● SQL Server 2012 bug (fixed in CU7) with
model database
● RoundHouse bug (fixed in version 0.8.6)
default to full mode
Database Maintenance
Check that the following are occurring on some
schedule (on all environments):
● Backups
● CHECKDB*
● Statistics Updates
● Index Maintenance
● History Cleanup
Security
● sa account should be disabled
● Best to use Windows authentication only
● Grant minimal rights*
*Hard to do
Files
At a minimum add at least one file; this at least
separates the system data from your data.
The more separation you have the more
restoration options you have - it isn’t magic
though.
Indexes
● Clustered = physical sort (only one allowed,
avoid heaps)
● Others are non-clustered
● Double edged sword
● Foreign keys are not automatically indexed
Questions?
Disaster Recovery
& High Availability
These things are important to our
stakeholders.
Disaster Recovery
● Backups are worthless if they can’t be
restored...ask someone to restore one
occasionally
● You cannot restore to a point in time in
simple mode and sometimes not in bulklogged
High Availability
● Ask stakeholders if they have any replicated
DBs, mirrors or AlwaysOn systems in
place...bad things can happen if you ignore
their existence
o
o
o
High latency
Delayed log truncation (file growth)
Replication hell
AlwaysOn
● 2012+ high availability option
● Super awesome
o
Multiple read replicas
o
Connection strings can be set to take advantage of
read only replicas if available (ApplicationIntent)
o
Great for reporting
Questions?
T-SQL
Microsoft’s blend of SQL
RBAR - Row By Agonizing Row
When working with a database always leverage
its ability to work with sets.
If you find yourself trying to loop through rows
then you are going about it the wrong way.
Joins
● List Tables Together
o Join tables together in
WHERE clause
● JOIN
o Only returns matches
● INNER JOIN
o Same as just JOIN
● FULL (FULL OUTER) JOIN
o Returns all matches and all
remaining non-matched rows
● LEFT (LEFT OUTER) JOIN
o Returns all matches and all
remaining rows from left source
● RIGHT (RIGHT OUTER) JOIN
o Returns all matches and all
remaining rows from right source
● CROSS JOIN
o Returns all combinations (no on
clause)
Gotcha
Temp tables, table variables and CTEs
#Temp
● tempdb
● indexes
● connection
scope
@Variable
● memory
● primary key
● batch
scope
CTE
● different
● request
;
Working with Sets
● UNION ( ALL )
● INTERSECT
● EXCEPT
● MERGE
Aggregates and Window Functions
● Aggregates
o
o
o
o
o
o
o
ROW_NUMBER()
RANK()
NTILE()
DENSE_RANK()
COUNT()
AVG()
etc….
● Window Functions
o
o
o
o
o
OVER
PARTITION BY
ORDER BY
Can’t do
COUNT(DISTINCT)
Don’t require
GROUP BY
(duplicates)
Subqueries
● Can be used in
o
o
o
o
o
o
o
SELECT
CASE
WINDOW
FUNCTION
FROM
JOIN
WHERE
ORDER BY
● Can’t be used in
o
o
Aggregates
GROUP BY
Stored Procedures and Views
● Stored Procedures
o
o
o
o
Offer performance
improvements
Accept parameters
Can’t be included
with other queries
Code consistency
and reusability
● Views
o
o
o
Don’t offer
performance
improvements
(unless indexed)
Can be included in
other queries
Code consistency
and reusability
Functions
● Table and Scalar
o
o
o
Use with caution
Must be performant
Number of calls can be dramatic
● Table - Multi vs Single (Inline)
○ Can be used with APPLY
■ CROSS
■ OUTER
● Scalar
○ SELECT
○ Clauses (ON and WHERE)
○ ORDER BY
○ GROUP BY
Questions?
Goodies!!!
Now for some cool stuff???
CTE Trick
Power of simple math!!!
http://www.sqlservercentral.com/articles/T-SQL/67899/
4K Splitter
...also from sqlservercentral.com
http://www.sqlservercentral.com/articles/Tally+Table/72993/
FOR XML PATH
...I don’t remember where I saw this for the
first time. I just know I love to abuse it.
Going a little crazy...
You can use the XML CLR to build HTML!!!!!!!!!
Questions?