sql server 2012 for developers l.
Skip this Video
Loading SlideShow in 5 Seconds..
SQL Server 2012 for Developers PowerPoint Presentation
Download Presentation
SQL Server 2012 for Developers

Loading in 2 Seconds...

play fullscreen
1 / 59

SQL Server 2012 for Developers - PowerPoint PPT Presentation

  • Uploaded on

SQL Server 2012 for Developers. UTS Short Course. Mehmet Ozdemir – SA @ SSW. w: blog.ozdemir.id.au | e: mehmet @ssw.com.au | t: @ mozdemir_au. SQL Server, BI, Infrastructure Specializes in Application architecture and design SQL Performance Tuning and Optimization HyperV , SCVMM

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

SQL Server 2012 for Developers

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
mehmet ozdemir sa @ ssw
Mehmet Ozdemir – SA @ SSW

w: blog.ozdemir.id.au | e: mehmet@ssw.com.au | t: @mozdemir_au

  • SQL Server, BI, Infrastructure
  • Specializes in
    • Application architecture and design
    • SQL Performance Tuning and Optimization
    • HyperV, SCVMM
    • Technology aficionado
      • Virtualization
      • Reporting/BI
      • Cubes
course website
Course Website
  • http://sharepoint.ssw.com.au/Training/UTSSQL/Documents/Latest/02_TSQL_Enhancements.pptx
  • http://sharepoint.ssw.com.au/Training/UTSSQL/Documents/Latest/02_TSQL_Enhancements_Lab.doc
    • Course Materials
last week
Last Week
  • SQL Management Studio
  • SQL Configuration Manager
  • Consoles
    • SQLCMD
    • PowerShell
  • SQL Profiler
  • SQL Database Tuning Advisor
  • T-SQL Part 1
last week additional
Last Week - Additional
  • How to setup maintenance plans over night
  • Database encryption
    • Data
    • Source code (Stored procs)
  • Best practices
    • Typical maintenance plans
    • Policies
Modify maintenance plan
  • 2nd page in wizard (new plan)
database encryption
Database encryption

Encrypting data - Transparent Data Encryption (TDE)



Encrypting Connections to SQL Server


Encrypting source code


best practices security
Best practices - Security

Security Best Practices


Security Best Practices Checklist



For me!

Demo Central Management Servers.


Create a schema called Salary

Create a table called Employees in Schema

Create a user called Manager

Give only manager permission to update/insert/delete in schema

Create a user called Peter

Give Peter only read to schema (=salary)

Create a user Alice

Deny everything for Alice in Salary

tsql part 2 agenda
TSQL Part 2 Agenda
  • ColumnStore Indexes
  • New SQL2012 Analytical Functions
  • Sequence Generator
columnstore indexes
ColumnStore Indexes
  • New in SQL Server 2012 (Enterprise Edition feature)
  • Huge speed improvements for data warehouse workloads
  • Data for index is stored by column, whereas in a traditional index it is stored by row
  • Only one ColumnStore index per table
  • Once created READ-ONLY
columnstore restrictions no free lunch
ColumnStore Restrictions (no free lunch)
  • Only in Enterprise Edition
  • Read only index
  • Index cannot be altered, must be dropped and recreated
  • Can’t participate in replication
  • Only works with traditional SQL Datatypes, ie not CLR types, MAX datatypesetc
  • Doesn’t include sparse columns
  • Cannot act as Primary Key (PK) or Foreign Key (FK)
order by offset fetch
  • Typical problem web page search returns 500 results
  • You want page this to 10 items per page
  • How would you do write a query to do this?
order by offset fetch18
  • Before offset and fetch we would use a Common Table Expression (CTE)
order by offset fetch19
  • New way, much cleaner and nicer
new analytical functions
New Analytical Functions
  • CUME_DIST computes the relative position of a specified value in a group of values
  • FIRST_VALUE, LAST_VALUE Returns the first/last value in an ordered set of values
  • LAG, LEADAccesses data from a previous/next row in the same result set without the use of a self-join
  • PERCENT_RANK Relative rank of row within ordered rows
    • PERCENTILE_CONT, PERCENTILE_DISC– can be used to determine median, but with additional flexibility
try catch throw
  • Nice little enhancement to TRY CATCH which has been in SQL Server since 2005
  • Can now THROW the error instead of using RAISERORR
try convert
  • TRY_CONVERT takes the value passed to it and tries to convert it to the specified data_type.
  • If convert fails *GRACEFULLY returns a NULL (I love this!)
sequence generator
Sequence Generator
  • Table-independent identity values
  • Generate predictable number of values
  • No need to use unique identifier if we only need uniqueness in the database
concat eomonth iif choose
  • CONCAT, like using + operator but different handling of NULLs
  • EOMONTH, easy way of getting the End Of Month
agenda clr integration
Agenda - CLR Integration
  • What is .NET?
  • What is CLR Integration?
  • Requirements on SQL box
  • Samples
  • Internals
  • CLR Integration: Pros
  • CLR Integration: Cons
  • Real world - When to use CLR Integration
what is net
What is .NET?
  • An application development platform from Microsoft
    • Tools, Languages, Runtime (Virtual machine), IDE, …
  • Rapidly develop secure and robust software
  • Web and Windows
  • Full support for object-oriented programming
net overview
.NET Overview
  • IL = Intermediate Language
  • CLR = Runtime
    • Common
    • Language
    • Runtime
  • = Virtual machine
net framework
.NET Framework
  • Evolution
  • The whole .NET FX
    • http://shrinkster.com/1515(PDF Poster)
what is clr integration
What is CLR Integration?
  • Lets you write your database queries using .NET
  • Create and debug using Visual Studio IDE
  • New since SQL 2005 (Standard and Express)
  • Support for large UDT (User defined types) in SQL 2008 (up to 2GB)
  • Support for multiple inputs on UDA (User defined aggregators)
    • e.g. string concatenator that takes in a column and separator char
  • Any .NET language (C#, VB, C++)
clr integration
CLR Integration
  • You can do the same thing as SQL Server using .NET code
    • Stored Procedures
    • Triggers
    • User-defined functions
    • User-defined types
    • Aggregate functions
server enabling clr integration
Server - Enabling CLR Integration
  • Enabled on an instance (not per database)
  • SQL Script
    • Execute sp_configure ‘clr enabled’, ‘1’
    • reconfigure

public partial class StoredProcedures



public static void HelloWorld()


// Put your code here

using (SqlConnectioncn = new SqlConnection("Context Connection=true"))



SqlCommandcmd = new SqlCommand("SELECT * FROM DEMO", cn);




what you do
What you do
  • Enable CLR
  • Create a new database project in Visual Studio
  • Create a new stored procedure in Visual Studio
  • Connect to current context using “Context Connection=true”
  • Add a simple SELECT statement
  • Deploy and run it
  • Assembly collated as set of files
    • Stored within SQL Server system tables
    • Assembly, references, program database (pdb), source files
  • Deployed to SQL Server
    • Manually
      • Catalogued with CREATE ASSEMBLY
      • Dropped with DROP ASSEMBLY
    • Automatically
      • Deployed from VS 2008
security levels
Security Levels
  • Safe (default)
    • Access only to CLR code. No access is allowed to external resources, thread management, unsafe code or interop.
  • External_Access
    • Access is allowed to external systems, such as the EventLog, File System, and network. Still no access to unsafe code or interop code.
  • Unsafe
    • Access is not limited whatsoever. User-defined types

public partial class UserDefinedFunctions



public static boolIsValidPostCode(string postcode)


return System.Text.RegularExpressions.Regex.IsMatch(

postcode, ^(((2|8|9)\d{2})|((02|08|09)\d{2})|([1-9]\d{3}))$");



what you do45
What you do
  • Create IsValidPostCode in C# (.NET)
  • Deploy it to SQL Server
  • Run it
stored procedure vs function
Stored procedure vs. Function
  • What is the difference?
  • Function
    • Base functionality
    • Independent of Database itself
  • Stored procedure
    • Many operations at once
    • Normally database specific

public partial class Triggers


[Microsoft.SqlServer.Server.SqlTrigger(Name = "EmailTrigger", Target = "Customers", Event = "FOR UPDATE")]

public static void SalaryFraudTrigger()


SqlTriggerContext context = SqlContext.TriggerContext;

using (SqlConnectioncnn = new SqlConnection("context connection=true"))



SqlCommand command = cnn.CreateCommand();

command.CommandText = "SELECT * FROM inserted";

SqlDataReader reader = command.ExecuteReader();

while (reader.Read())


for (intcolumnNumber = 0; columnNumber<context.ColumnCount; columnNumber++)


SqlContext.Pipe.Send(string.Format("Col: {0} = {1}", columnNumber, reader[columnNumber].ToString()));







what you do49
What you do
  • Create EmailTrigger in C# (.NET)
  • Deploy it to SQL Server
  • Test it
clr integration pros continued
CLR Integration: Pros (Continued)
  • Take advantage of the powerful .NET Framework
  • .NET is a full-featured programming language
    • Supports things like “for each” loops, arrays, collections
    • Object Oriented programming model to organise your queries
    • Obtaining data from external resources
      • The File System
      • The Event Log
      • A Web Service
      • The Registry
clr integration pros
CLR Integration: Pros
  • For complex calculations
    • Parsing strings (like the regular expression code)
    • User-defined types
      • Date, time, currency, and extended numeric types
      • Geospatial applications
      • Encoded or encrypted data (see books online)
    • User-defined aggregates
  • Powerful Intellisense and debugging
  • Generally faster
    • E.g. CLR aggregate 100x faster than cursor
clr integration cons continued
CLR Integration: Cons (Continued)
  • Lots of programming for simple operations
  • Some overhead in communicating with assemblies
  • Remember – T-SQL is designed and optimised for data, use it!
  • Not useful if your guys do not know any .NET
  • Potentially costly to rewrite logic
    • Companies (including us) have invested a lot in T-SQL
clr integration cons
CLR Integration: Cons
  • There are some restrictions to observe when calling between T-SQL and SQL-CLR.
    • You must only use T-SQL supported data types (No streams)
    • You can't use inheritance or polymorphism
    • .NET cannot easily represent either VARCHAR or TIMESTAMP
      • .NET strings are Unicode, the equivalent of NVARCHAR
    • The CLR decimal type is not the same as SQL_DECIMAL
when to use clr integration
When to use CLR Integration
  • Do I need to manipulate data before it is displayed?
    • .NET code and SQLCLR
  • Do I need to do set-based operations such as pivoting?
    • T-SQL
  • Do I need to do extensive computation or custom algorithms?
    • .NET code and SQLCLR
  • Are my developers SQL gurus but .NET newbies?
    • T-SQL
  • Do I have loads of stored procs that are becoming hard to manage?
    • .NET code and SQLCLR
clr integration55
CLR Integration

The Bottom Line

Use T-SQL for all data operations

Use CLR assemblies for any complex calculations and transformations

quick tips
Quick tips
  • SQL Management Studio Shortcuts
    • Ctrl + L – Display query execution plan
    • F5 – Run/Show result grid
  • Rules for SQL Server


  • SQL Server Cheat sheet



session 2 lab
Session 2 Lab
  • T-SQL Enhancements Part 2
  • CLR Integration
3 things
  • EricPhan@ssw.com.au
  • http://ericphan.info
  • twitter.com/ericphan
Thank You!

Gateway Court Suite 10 81 - 91 Military Road Neutral Bay, Sydney NSW 2089 AUSTRALIA

ABN: 21 069 371 900

Phone: + 61 2 9953 3000 Fax: + 61 2 9953 3105