TransactSQL

1
Transact-SQL
2

• A basic SELECT statement consists of a series of
  clauses that define what information we are
  interested in, and how we want to view it.
• SELECT
• SupplierID, CompanyName, ContactName,
• FROM Suppliers
• WHERE Country ltgt 'USA'
• ORDER BY Country

3
Distinct

• Eliminating duplicate records from our rowset can
  be done within our query by specifying the
  DISTINCT keyword in our SELECT clause. It will
  cause the query engine to return only unique rows
  from our query.
• SELECT
• DISTINCT Country
• FROM Suppliers

4
Top

• The TOP operator is Microsoft extension in
  Transact SQL that allows us to limit the number
  of rows we want in our result set. After the
  query has been processed, the result set is
  limited to the number of rows specified.
• SELECT
• TOP 10 ProductID, Sum(UnitPrice Quantity) as
  Sales
• FROM Order Details
• GROUP BY ProductID
• ORDER BY Sales
•  

5
Subqueries

• A subquery is a complete select statement that
  returns a single value and is nested within
  another SQL statement in place of an expression.
  We can use the subquery anywhere that we can use
  an expression, whether it is part of the SELECT
  clause or WHERE clause. We can use the subquery
  to perform lookups on related tables, or to
  evaluate a condition.
• SELECT
• SupplierID, CompanyName FROM Suppliers S
• WHERE NOT EXISTS
•         (SELECT
• 'x' FROM Order Details D, Products P
•          WHERE D.ProductID P.ProductID
•          AND   S.SupplierID P.SupplierID)

6
String Concatenation

• One of the basic operations performed in the
  database is to generate human-readable output.
  Because the process of normalization will break
  apart things like addresses and names into
  multiple parts that need to be reassembled,
  string concatenation is an important function to
  understand. SQL Server's approach is to use the
  operator between character based values to
  concatenate
• SELECT
• 'SELECT
• ''' name ''', count() from ' name ''
• FROM sysobjects
• WHERE type 'U'
• ORDER BY name

7
Comments

• A quick note on comments. Just as all good code
  is self-documenting, we may find at times that it
  is helpful to others who read our code to find
  comments from the author explaining what is going
  on. Transact SQL recognizes two forms of
  comments Single-line and multi-line.
• SELECT
• GetDate() -- This is a comment from here on, I
  can type whatever I want
• Multi-line comments follow the C syntax of /
  comment text /, where the comment text can cover
  multiple lines of text.
• / This is a multi-line comment, none of the
  statements are ever processed
•  
• DECLARE _at_myDate DATETIME
• SET _at_myDate GetDate()
• PRINT 'The current date is ' convert(varchar(18)
  , _at_MyDate, 110)
•  
• /

8
Case

• The CASE expression allows you to evaluate a
  variable or a condition inline and return one of
  multiple values as the SQL statement is being
  run. The basic version takes a source variable or
  column and depending on the value returns one of
  a discrete set of values.
• Select
• case
•     when datepart(month, GetDate()) gt 9
•         then 'Winter is here'
•     when datepart(hour, GetDate()) gt 22
•         then 'Its getting late'
•     when convert(varchar(12), getdate(), 113)
  '28 NOV 2003'
•         then 'Everythings on SALE!'
•     when datepart(weekday, GetDate()) 6
•         then 'TGIF!'
•     Else 'Who knows???'

9
Coalesce, Nullif, IsNull

• Microsoft took the liberty of providing us with
  commonly used functions to make our lives easier.
  These are cousins of the CASE expression and
  could be coded in that way. The Coalesce function
  returns the first non-null expression in a list,
  otherwise it returns null.
•  
• Coalesce (expression1, expression2, ...)
•  
• -- this is essentially the same as
• case when expression1 is not null expression1
•      when expression2 is not null expression2
•      etc...

10

• select
• coalesce (OrderDate, RequiredDate, ShippedDate)
  as LastUpdate
• from orders
• NullIf and IsNull operate similarly, the former
  returning null if a condition is true, and the
  second an alternate value if the expression is
  null.
• NullIf(expression1, expression2)
•  
• -- NullIf is evaluated as
• case when expression1 expression2 then Null
•      else expression1
•  
• IsNull(expression1, expression2)
•  
• -- This is evaluated as
• case when expression1 is null then expression 2
•      else expression1

11

• INSERT INTO (, , ...) values (, , ...)
• UPDATE SET , WHERE ...
• DELETE FROM
• WHERE ...

12
Variables

• Variables are used to temporarily hold values
  that may be useful in the processing of
  statements. We declare them using the DECLARE
  keyword, specifying the name and the type, and we
  assign a value to them with the SET statement.  
• We reference them as we would column data or
  constants in the processing logic of our code
  using the _at_ character in front of the name to
  denote a variable.
• Global variables reference the status of the
  system and the database engine and other options
  of the server and are referenced with two _at__at_
  characters.
• DECLARE _at_MyVar varchar(15)
• SET _at_MyVar 'New Value'
• Go
•  
• PRINT 'The value of MyVar is ' _at_MyVar

13
Transaction

• A transaction is an atomic unit of work that is
  either completed in its entirety or not done at
  all.
• For recovery purposes, the system needs to keep
  track of when the transaction starts, terminates,
  and commits or aborts.

14
ACID properties

• Atomicity A transaction is an atomic unit of
  processing it is either performed in its
  entirety or not performed at all.
• Consistency preservation A correct execution of
  the transaction must take the database from one
  consistent state to another.
• Isolation A transaction should not make its
  updates visible to other transactions until it is
  committed.
• Durability or permanency Once a transaction
  changes the database and the changes are
  committed, these changes must never be lost
  because of subsequent failure.

15
Transaction and System Concepts

• Transaction operations
• 1. begin_transaction This marks the beginning of
  transaction execution.
• 2. read or write These specify read or write
  operations on the database items that are
  executed as part of a transaction.
• i.e. SELECT, UPDATE, INSERT, DELETE
• Two ways for a transaction to end
• 3. commit_transaction This signals a successful
  end of the transaction so that any changes
  (updates) executed by the transaction can be
  safely committed to the database and will not be
  undone.
• 4. rollback (or abort) This signals that the
  transaction has ended unsuccessfully, so that any
  changes or effects that the transaction may have
  applied to the database are undone.

16
Transaction and System Concepts

• Example
• This transaction makes two updates, but then
  rolls back and so it is as if it never even
  started
• Begin Transaction
• Update employees set salary1000000 where
  employeeid7
• Update employees set salarysalary100 where
  employeeid5
• Rollback
• In the absence of Begin Transaction, each
  statement will be treated as a transaction that
  is automatically committed

17
User Defined Functions

• Structured programming languages have long
  provided the ability to create procedures and
  function calls to breakdown complex routines into
  manageable units. The introduction of the User
  Defined Function (UDF) with SQL Server 2000
  provides us with a new way of organizing and
  optimizing our use of the database engine. A User
  Defined Function takes zero or more input
  parameters and returns either a scalar or a table
  based value.
•  
• UDF's are similar to stored procedures in that
  they both use the control statements and data
  manipulation code to define the logic of the
  module. But they differ in that UDF's don't allow
  modification of the global state of the calling
  process or the database environment. In other
  words User Defined Functions are stateless in
  that there can be no side affects.  You can't
  alter data in tables that are not locally defined
  within the UDF, and you can't use
  non-deterministic functions within your code.
  Deterministic functions always return the same
  value given the same input parameters, such as
  the DATEADD function. The GETDATE function is
  non-deterministic in that the value depends on
  when you call it.

18

• This flavor of UDF must return a single value
  each time it is called. We create a UDF using the
  DECLARE function
• CREATE FUNCTION udfSalePrice (_at_ProductID int,
  _at_TranDate datetime)
• RETURNS varchar(50)
• BEGIN
•         declare _at_UnitPrice money, _at_ProductName
  varchar(15)
•  
•         select
• _at_UnitPrice UnitPrice, _at_ProductName
  ProductName
•         from Northwind..Products
•         where ProductID _at_ProductID
•  
•         if _at_TranDate gt '28-NOV-2003 0800' and
•            _at_TranDate lt '28-NOV-2003 1800'
•              return _at_ProductName ' on Sale for
  '
•                   convert(varchar(10),_at_UnitPrice
  .85)
•  
•         return _at_ProductName ' for '
  convert(varchar(10),_at_UnitPrice)
• END

19

• Table Valued Functions return a table variable
  that can be comprised from one or more
  statements. If it is a single table and doesn't
  specify the column set in the declaration, then
  the statement doesn't require the begin/end
  wrapper, and it is considered to be Inline.
• create function udfRegionOrders (_at_Region
  varchar(15))
• returns TABLE
• as
•     return (select
• from Northwind..Orders where ShipRegion
  _at_Region)
•  
• go
• select
• from northwind..udfRegionOrders ('RJ')

20
Stored Procedures

• While we can run ad-hoc SQL Statements within
  Query Analyzer, and we have the ability to use
  the execute() function in ADO, frequently
  performed logic can be encapsulated into batches
  and saved as Stored Procedures on the server. The
  benefits from this approach not only include the
  reduction of work of testing multiple programs
  and applications to ensure that the logic is
  correct, but also from the execution plan
  generated on the server when the stored procedure
  is compiled and saved.
•  
• Stored Procedures are a collection of Transact
  SQL statements which perform a defined set of
  work. This can be as simple as updating a single
  row or returning a configuration setting, to
  being as complex as implementing a business rule
  that requires sending an e-mail alert containing
  crucial information specific to the problem. They
  can take both input and output parameters, and
  always return an integer value.

21
System Stored Procedures.

• Microsoft includes a broad suite of system stored
  procedures for administering the server engine.
  There are over 900 system stored procedures that
  SQL Books breaks into 17 categories. They include
  procedures for managing system security, to setup
  and manage distributed queries and linked servers
  (making tables and data on different machines
  visible to each other), data replication,
  retrieve information about schema, view current
  performance and usage information, manage SQL
  Agent (the scheduler that comes with SQL Server),
  and to interface with XML data.
• EXEC sp_help Employees

22
User defined Stored Procedures.

• We can define our own stored procedures to
  provide users of the database with a consistent
  implementation of work logic across applications.
  This simplifies the administration of business
  rules by acting as a single point of control on
  the database server itself. If the rule is
  changed, we only need to update the associated
  stored procedures instead of the various
  applications making use of the data.
• CREATE PROCEDURE spSimple as
• select
• 'HELLO WORLD!'
• go

23
Extended stored procedures

• Extended stored procedures allow us to extend
  Transact SQL develop logic outside of the SQL
  Server using tools such as C or Visual Basic.
  They follow the same calling standards as a
  normal stored procedure, complete with parameters
  and a return code. XP's are implemented using a
  call to dynamic link libraries (dll) that conform
  to the Extended Stored Procedure API. you need to
  be careful that they are thoroughly tested and
  checked for aberrant behavior before using them
  in a production situation because these calls are
  external to SQL Server.
• If we had written a standard event management
  system to log information to the NT Event Log and
  wanted to include it in our SQL environment  we
  call the stored procedure sp_addextendedproc
•   
• USE Master
• go
• EXEC sp_AddExtendedProc xp_LogEvent
  'c\winnt\system32\EventLogger.dll'