TSQL

1
T-SQL

• Eat it, Sleep it, Drink it, Dream it, Live it,
  Love it

2
Resources

• SQL Books Online THE Resource for T-SQL for MS
  SQL Server

3
Object Names Field Names

• Valid Object Names
• server.database.owner.object
• server.database..object
• server..owner.object
• server...object
• database.owner.object
• database..object
• owner.object
• Object
• Valid Field Names
• validObjectName.fieldName
• fieldName
• When names collide with reserved key words you
  must use around the name
• Examples
• myDatabase.dbo.myTable
• myServer.myDatabase..myTable
• myTable.aField
• aField

4
SQL Books Online T-SQL Reference Tips
Ref SQL Books Online - Transact-SQL
Reference/Transact-SQL Syntax Conventions
5
Sample T-SQL Reference Syntax

• INSERT INTO      table_name WITH ( lt
  table_hint_limited gt ...n )         
  view_name          rowset_function_limited
                ( column_list )         
  VALUES             ( DEFAULT NULL
  expression ,...n )             
  derived_table              execute_statement
                     DEFAULT VALUES lt
  table_hint_limited gt      FASTFIRSTROW
           HOLDLOCK          PAGLOCK         
  READCOMMITTED          REPEATABLEREAD
           ROWLOCK          SERIALIZABLE
           TABLOCK          TABLOCKX         
  UPDLOCK     

6
SELECT Overview

• SELECT select_list INTO new_table FROM
  table_source WHERE search_condition GROUP
  BY group_by_expression HAVING
  search_condition ORDER BY order_expression
  ASC DESC

7
SELECT Clause (SELECT)

• SELECT ALL DISTINCT      TOP n PERCENT
  WITH TIES     lt select_list gt
• lt select_list gt
•                   table_name view_name
  table_alias .               column_name
  expression IDENTITYCOL ROWGUIDCOL
               AS column_alias         
  column_alias expression          ,...n

8
FROM Clause (SELECT)

• FROM lt table_source gt ,...n lt
  table_source gt     table_name AS
  table_alias WITH ( lt table_hint gt ,...n )
       view_name AS table_alias     
  rowset_function AS table_alias     
  OPENXML      derived_table AS table_alias
  ( column_alias ,...n )      lt
  joined_table gt
• lt joined_table gt      lt table_source gt lt
  join_type gt lt table_source gt ON lt
  search_condition gt      lt table_source gt CROSS
  JOIN lt table_source gt      lt joined_table gt
• lt join_type gt      INNER LEFT RIGHT
  FULL OUTER      lt join_hint gt
      JOIN

9
WHERE Clause (SELECT)

• WHERE lt search_condition gt lt old_outer_join gt
  
• lt old_outer_join gt     column_name
  column_name

10
Search Condition (WHERE Clause)

• lt search_condition gt          NOT lt
  predicate gt ( lt search_condition gt )
           AND OR NOT lt predicate gt
  ( lt search_condition gt )          ,...n
• lt predicate gt         expression lt gt
  ! gt gt ! gt lt lt ! lt
  expression          string_expression NOT
  LIKE string_expression              ESCAPE
  'escape_character'          expression NOT
  BETWEEN expression AND expression         
  expression IS NOT NULL          CONTAINS
              ( column , 'lt
  contains_search_condition gt' )         
  FREETEXT ( column , 'freetext_string' )
           expression NOT IN ( subquery
  expression ,...n )          expression
  lt gt ! gt gt ! gt lt lt ! lt
               ALL SOME ANY ( subquery )
           EXISTS ( subquery )     

11
GROUP BY Clause (SELECT)

• GROUP BY ALL group_by_expression ,...n
           WITH CUBE ROLLUP
• Usually used in conjunction with aggregate
  functions in the select list (i.e. SUM, COUNT,
  AVG, etc.)

12
HAVING Clause (SELECT)

• HAVING lt search_condition gt

13
UNION Clause (SELECT)

• lt query specification gt ( lt query expression
  gt )         UNION ALL         lt query
  specification ( lt query expression gt )
               UNION ALL lt query
  specification ( lt query expression gt )
                   ...n

14
ORDER BY Clause (SELECT)

• ORDER BY order_by_expression ASC DESC
       ,...n

15
INSERT

• INSERT INTO      table_name WITH ( lt
  table_hint_limited gt ...n )         
  view_name          rowset_function_limited
                ( column_list )         
  VALUES             ( DEFAULT NULL
  expression ,...n )             
  derived_table              execute_statement
                     DEFAULT VALUES lt
  table_hint_limited gt      FASTFIRSTROW
           HOLDLOCK          PAGLOCK         
  READCOMMITTED          REPEATABLEREAD
           ROWLOCK          SERIALIZABLE
           TABLOCK          TABLOCKX         
  UPDLOCK     

16
UPDATE

• UPDATE                   table_name WITH ( lt
  table_hint_limited gt ...n )          
  view_name           rowset_function_limited
                   SET          column_name
  expression DEFAULT NULL         
  _at_variable expression          _at_variable
  column expression ,...n      FROM
  lt table_source gt ,...n         
  WHERE             lt search_condition gt
                    WHERE CURRENT OF         
  GLOBAL cursor_name cursor_variable_name
                    OPTION ( lt query_hint gt
  ,...n )

17
DELETE

• DELETE      FROM          table_name WITH (
  lt table_hint_limited gt ...n )         
  view_name          rowset_function_limited
                    FROM lt table_source gt
  ,...n
•      WHERE          lt search_condition gt
           CURRENT OF                 
  GLOBAL cursor_name                     
  cursor_variable_name                 
                                  OPTION (
  lt query_hint gt ,...n )