SQL Server Performance for Developers

For the .NET programmer, Visual Basic programmer or PHP programmer, if you are accessing a SQL Server database there are some things you should know to performance tune your queries. Learn how to improve query performance with Indexes, how to properly use parameterized queries, using the query analyzer, and avoiding common SQL Server performance pitfalls.

PerformanceTuning

This presentation is a lot of fun. This is one of the few presentations where there is audience participation. Four luck participants will be selected to help simulate the work that SQL Server does when accessing tables structured with different types of indexes.

Download the presentation here:

PerformanceTuning.zip

If you are interested in performance, please take a look at the Database Heath Monitor.

SQL Server FILESTREAM and FileTables

Monday: Using FILESTREAM and FILETABLES in SQL Server

Configuring and Using FILESTREAM and FILETABLES in SQL Server.  Developers love to use SQL Server to store files, but this causes headaches for the DBA, finally a reasonable solution for file storage in SQL Server FILETABLES and FILESTREAM. SQL Server 2008 and 2012 add the new features of FILESTREAM and FILETABLES. Learn how to configure and manipulate files in your SQL Server with FILESTREAM, then learn how to do everything that FILESTREAM sounds like it should do with FILETABLES. With FILETABLES inserting is as easy as drag and drop.

Here is the outline for the presentation:

  • FILESTREAM – SQL Server 2008 and newer
    • Introduction and Configuration
    • Creating a Table Using FILESTREAM
    • TSQL FILESTREAM Access
  • FileTables – SQL Server 2012 and newer
    • Configuring and Creating FileTables
    • Insert, Update and Delete with a FileTable
    • Drag and drop with the file system

 

Here are the slides from the presentation and the supporting sample files.

Filestream and FileTables.zip

 

Speaking at Vancouver DevTeach this week.

This week I will be attending and speaking at Vancouver DevTeach. This event taking place on December 2nd to 4th 2013 at the Vancouver Sheraton Wall Center Hotel. Monday and Tuesday I have morning presentations which leaves the rest of the day to attend a few other sessions.

DevTeach

Here are the sessions that I will be presenting.

Monday: Using FILESTREAM and FILETABLES in SQL Server

Configuring and Using FILESTREAM and FILETABLES in SQL Server.  Developers love to use SQL Server to store files, but this causes headaches for the DBA, finally a reasonable solution for file storage in SQL Server FILETABLES and FILESTREAM. SQL Server 2008 and 2012 add the new features of FILESTREAM and FILETABLES. Learn how to configure and manipulate files in your SQL Server with FILESTREAM, then learn how to do everything that FILESTREAM sounds like it should do with FILETABLES. With FILETABLES inserting is as easy as drag and drop.

Tuesday: SQL Server Performance for Developers

For the .NET programmer, Visual Basic programmer or PHP programmer, if you are accessing a SQL Server database there are some things you should know to performance tune your queries. Learn how to improve query performance with Indexes, how to properly use parameterized queries, using the query analyzer, and avoiding common SQL Server performance pitfalls.

Looking at the lineup of other speakers this looks like it will be a great few days of education. It is nice to attend a conference so close to home, no flights involved for me this time.

Advanced CTEs Slides and Sample Queries

My second presentation at SQL Saturday in Portland Oregon is Advanced Common Table Expressions.

Here is the outline for the session.

1. Hierarchical Recursive CTEs
2. Manipulating Data
3. Common Use Cases
4. CTE Performance Considerations

Download the presentation and samples here:  Advanced CTEs.zip

See Also:

Introduction to CTEs presentation

Introduction to CTEs Slides and Sample Queries

Today I am at SQL Saturday Portland Oregon, and at 9:00am I am presenting the Introduction To Common Table Expressions session:

Introduction

Here is the abstract:

Have you ever wanted to create a recursive query, but didn’t see how to do it. With the Common Table Expressions session you will learn everything needed to start using CTE’s for recursive queries, as temporary views, and to use the result set multiple times in the same query. Learn how simplify query syntax using CTE’s. One of the most overlooked features of SQL Server is the CTE which not only simplifies the query, but gives you the ability to do things that would otherwise be impossible (or at least very challenging) with SQL Server. The class is designed for people who haven’t used CTE’s before, or for those who want to learn the basics of CTEs including data paging. This session pairs well with the Advanced Common Table Expressions session.

This session will include the following topics:

  • Introduction to Memory Tables and CTEs
  • Simple CTE
  • CTE Instead of a Derived Table
  • Multiple CTE in a Query
  • Data Paging
  • CTEs in Stored Procedures, Functions and Views
  • Introduction To Recursive CTEs

At 10:45 I will be giving the Advanced Common Table Expressions Session.

Download the presentation here:  Introduction to CTEs.zip

Related Posts:

3 Technical Certifications This Year

Over a year ago I was at a SQL Saturday listening to Rick Morelan from Joes2Pros talking about his goal, which if I remember correctly was to get 3 certifications a year, and that he has obtained over 30 technical certifications. Earlier this year I decided that I would follow with a similar goal of 3 certifications per year. Yesterday I completed my third certification for 2013. The three technical certifications that I completed this year are:

1. Certified Scrum Professional (June 2013).

2. Certified Scrum Professional (October 2013).

3. Microsoft Exam 70-461 Querying Microsoft SQL Server 2012 ( November 2013).

Its been a good year for learning. Now to decide on the next certification to work towards in the beginning of 2014.