Presenter: Matt Batalon
Topic: Introduction to SQL Stored Procedures
SQL Stored Procedures have been part of SQL Server since the early days. They are fundamental part of almost any SQL Server database. In this introductory presentation we will cover the benefits of using stored procedures in your database development projects, and also look as some drawbacks stored procedures might have. We will also cover the following topics to get you equipped to start writing, modifying and debugging stored procedures of your own.
1. Creating Stored Procedures
2. Modifying Stored Procedures
3. Creating Stored Procedures with parameters
4. Creating Stored Procedures with optional parameters
5. Viewing a Stored Procedure definition
6. Returning values from a stored procedure
7. Returning result sets from stored procedures
8. Deterring how nested your current stored procedure is in the call stack
9. Encrypting Stored procedures
10. Creating a stored procedure that executes on instance start up
11.Tips on coding stored procedures
12.Tips on Stored Procedure Performance
13. CLR Stored Procedures
Our chapter president, John Miner, will give a brief presentation on some SQL Server highlights coming up in 2015 from this years PASS SUMMIT. Afterwards, please join us for a fun game of SQL Jeopardy for a chance to win great prizes!
As always, there will be pizza to eat, soda to drink, and good conversation. We hope to see all of you at the meeting!
Due to the PASS Summit in Seattle this month, there will be no SNESSUG meeting this month. We will resume with our regularly scheduled “2nd Wednesday of the Month” meetings in December. Stay tuned for next month’s meeting topic.
SNESSUG Board of Directors
I am thrilled about presenting to the Southern New England SQL Server User Group (SNESSUG) on October 8, 2014.
I always love the home team advantage by knowing the audience very well. It makes for a relaxing environment to teach and attendees to learn.
Here is the gritty details of the presentation that I will be exploring during the 60 minute talk.
Basic Database Programming
You just found out that a client needs a SQL database to be designed from scratch. However, you existing DBA has given two week notice. This presentation is meant for the Accidental DBA that has little to no experience with creating Transaction SQL objects. After this presentation, will be ready to meet that clients need.
This presentation will review the following topics to get you ready to code.
1 – How is a view better than ad-hoc SQL?
2 – Abstracting underlying tables via a view.
3 – Avoiding DML updates to views.
4 – Using triggers to enforce business rules.
5 – Using triggers to prevent data modifications.
6 – What are the different types of functions?
7 – Simple use of each type.
8 – Using stored procedures as an application interface.
9 – Batch programming using stored procedures.
10 – How to schedule batch jobs.
John is the recent recipient of the Microsoft 2014 MVP award for SQL Server. He has over twenty years of IT experience including a Masters degree in Computer Science from the University of Rhode Island with concentrations in database technologies and programming languages. He also has Microsoft Certificates for Database Administration (MCDBA) and System Administration (MCSA).
John is currently a Senior Consultant at Atrion. He is the PASS chapter leader for Southern New England SQL Server Users Group.
Presenter: Mike Hillwig
Topic: What the VLF?
Downloads: Available at CrankyDBA.com
Experienced DBAs know that SQL Server stores data in data files and transaction log files. What is less commonly known is that the transaction log file is broken up into smaller segments known as Virtual Log Files, or VLFs. Having too many VLFs will cause performance to suffer.
And having too few will cause backup performance to suffer. How do you strike the right balance? In this more advanced session, veteran DBA Mike Hillwig will show you what VLFs are, how they’re created, how to identify them, and how to strike the right balance between too few and too many.
Native Pittsburgher and Boston resident Mike Hillwig is a senior SQL Server DBA for a provider of private cloud-based solutions for the financial industry He’s been working with SQL Server since version 7 and has a background in infrastructure and networking.
Presenter: Tim Harkin
Topic: SQL Server Statistics: The Secret Sauce
Learning how to write a query is a basic SQL skill. Eventually, a curious developer wants to know how SQL actually returns the data. Statistics are the “secret sauce” that SQL uses to create a query plan to get your data. In this session, the basics of how SQL creates and maintains statistics will be discussed, and the impact that the this has on query plans. Other topics covered will be what happens when statistics are wrong (and how that can happen), as well as when to take statistics maintenance into your own hands.
Tim Harkin has been working with SQL since SQL 2000 and has worked in a variety of database environments from multi-terabyte data warehouses to high volume OLTP applications and everything in between. Tim also helps run the Fairfield\Westchester SQL Server User Group. Currently he is the Senior DBA for Toluna, USA, a pioneer in the dynamic world of market research, data collection, reporting and visualization.
Presenter: Dave Ramsden
Topic: Building a Vision for IT
…is about creating a vision that will make IT more strategic as we move into the dramatically changing IT landscape of the first digital decade. The overall theme stresses focusing on business outcomes in order to gain strategic influence. It specifically calls on IT leadership to understand the major changes going on in the industry, leverage personal passion to accomplish change, and to understand and work within the context of their organization’s business goals and strategy. A big component involves demonstrating how our organization, Atrion Networking Corporation, created an intelligence function rather than just implementing a technological BI solution. I contend that BI is a valuable tool, but certainly not an outcome. Moreover, I argue that it takes an Intel Function using a full Intel Cycle to lead to meaningful outcomes. We examine the implementation of this model and demonstrate the results, further supporting the assertion.
David Ramsden is currently the Chief Strategy Officer (CSO) at Atrion Networking Corporation, Inc. He has worked in the technology industry for 20 years, and for 15 of them has held leadership roles in Professional Services, Operations Management, Competitive Intelligence and Strategy. He has a Bachelor’s Degree in History and a Master’s Degree in English. He is a member of the society of Strategic & Competitive Intelligence Professionals (SCIP) and has earned their Competitive Intelligence Professional (CIP-I) Certification. He is a member of American Mensa, and has published numerous articles on increasing the strategic influence of IT within an organization. David lives with his wife and 4 children in Massachusetts. Proud to be an Aspergian, Dave is active in raising Autism Awareness both within and outside of the technology community. He is a member of the Asperger’s Association of New England (AANE) and has spoken publically on living with Autism Spectrum Disorder (ASD).
Presenter: Andy Novick
Topic: SQL Server 2014 In-Memory tables, Working with Millions of rows
Memory optimized tables are one of the cool new features in SQL Server 2014. They’re being marketed as a high transaction throughput solution for OLTP. We’ll take a look at that and at how do they work when you try and work with millions of rows in situations more like data warehousing. This presentation will explore the issues and demonstrate where it can be made to work and where it can’t.
Andy Novick is a SQL Server Developer in the Boston area with 25 years of database and application development experience. His consulting practice focuses on building applications, including software products that use SQL Server’s capabilities to the maximum. He has developed both OLTP and data warehouse databases with multi-terabytes scale. He has particular expertise in automating data management for such large databases. Other recent projects have included ETL, Security and SQL Injection prevention. Andy is the originator of the popular “SQL Server Load-fest” event conduced in Waltham, MA.
Presenter: Christopher Schmidt
Topic: Advanced Tabular Development
In this session, we’ll cover some of the more advanced concepts in tabular databases. First we’ll take a look at things like partitions and perspectives, and how we can use them to assist in management and maintenance of an Analysis Services database. Then we’ll look at implementing dynamic security into our solution, and finally we’ll briefly explore the types of query modes available to tabular.When should In-Memory, DirectQuery, or one of the hybrids be used?
Chris is a Senior Business Intelligence Consultant with Pragmatic Works and has more than 5 years experience in developing business intelligence solutions. He lives in Raleigh, NC and is an active member of the SQL community. Chris is extremely passionate about business intelligence, and helping companies achieve insight through data. An avid sci-fi fan, when he’s not watching his beloved Manchester United play, you’ll frequently see Game of Thrones, Doctor Who and BattleStar Galactica reruns on his television. Chris also blogs at http://intelligentsql.wordpress.com.
Presenter: John Miner
Topic: A brief introduction to Power Tools.
Downloads: Available on CraftyDBA.com
In recent months, there has been a big push for self service business intelligence tools by Microsoft. The center of this universe is Microsoft Excel, a common application used by the typical data analyst.
The following four products started off as add-ins for Excel. Now, some of them are an integral part of MS Office 2013.
- Power Query
- Power Pivot
- Power View
- Power Map
It is not surprising that these four business intelligence products have a cloud offering in Office 365.
Today, we will explore how a typical BI request on the Adventure Works 2012 DW database can leverage these tools.
I will be covering the following topics in this presentation:
- Grabbing data with Power Query.
- Transforming data with Power Query.
- Storing data with Power Query.
- De-normalizing data for speed.
- DAX language is in the tabular data model.
- Power Pivot tables and charts.
- Adding slicers to Power Pivot.
- Adding the WOW effect with Power View
- Various Power View reports.
- Filtering vs drill down.
- Using the map to plot real world data.
- Story telling with tours.
John has over twenty years of IT experience including a Masters degree in Computer Science from the University of Rhode Island with concentrations
in database technologies and programming languages. He also has Microsoft Certificates for Database Administration (MCDBA) and System Administration (MCSA).
John is currently a Senior Platform Engineer at Atrion. He is the PASS chapter
leader for Southern New England SQL Server Users Group.