SQL Examples with Microsoft's SQL Server

1. Working with Databases
   1. Creating Databases:

       
      CREATE DATABASE myNewDatabase
      

   2. Deleting Databases:

       
      DROP DATABASE database_name [, database_name...]
      

   3. Renaming a database

       
      USE master
         GO 
         EXEC sp_dboption myOldData, 'Single User', True
         EXEC sp_renamedb 'myOldData', 'myNewData'
         EXEC sp_dboption myNewData, 'Single User', False
         GO 
      

   4. Working With Tables

      Traditionally known as CRUD - Create, Read, Update, and Delete

      1. Creating a Table
         1. syntax:

             
              CREATE TABLE tablename (column_name column_type [(column_width[,column_precision])] ...)
            

            example:

            CREATE TABLE practice
            (
             fname     VARCHAR(20)   NOT NULL,
             lname     VARCHAR(30)   NOT NULL,
             hire_date SMALLDATETIME NULL,
             ssn       CHAR(11)      NOT NULL
            )
            GO
            -- 'NOT NULL' is the default.
            

            EXECUTE sp_help practice -- shows structure of table
            

         2. ALTER TABLE practice

            ALTER TABLE XYZ ADD mi CHAR(1) NULL
            GO
            
            

      2. "SELECT INTO"

         Tables can also be created with the "SELECT INTO" construct SELECT select_list INTO new_table_name FROM table_list WHERE search_condition Note: if new_table_name starts with #, it will be put in the temp tables ##, it will be in temp tables and be global (accessable to all other sessions) Example using Temp tables:

         SELECT * INTO #tempcustomers2 FROM customers
         SELECT * FROM #tempcustomers2
         
         

         Temp tables go away when the server is restarted or at the end of the connection.

      3. Deleting a table: DROP TABLE table_name
      4. Add a column: ALTER TABLE SDSurveys ADD KQLogic VARCHAR (1000) NULL
      5. Remove a column: ALTER TABLE SDSurveys drop column KQLogic
      6. Show list of columns in a table sp_columns table_name
   5. Working With Rows
      1. Inserting Rows into a Table
         1. Syntax:

            INSERT INTO table_name [(column_id1,column_id2...)]
               VALUES (value1,value2,...)
            examples:
            INSERT INTO autos (1996,'ford') -- INTO is an optional keyword in SQLServer
            INSERT tablename VALUES ('a','b',DEFAULT,i) -- DEFAULT is a key word
            INSERT INTO title VALUES(1001,'The Odyssey','Homer',NULL)
            

         2. add multiple rows to a table

            INSERT INTO table (c17,c4,c8,c3) SELECT a,b,c,d FROM ...
            
            

         3. add multiple rows to a table with stored procedure

            INSERT INTO table
            (c17,c4,c8,c3)
            EXECUTE sp_mystored_procedure
            

         4. INSERT rows from this database into a table in another database

            INSERT INTO OtherDatabaseName..users 
            SELECT * FROM users WHERE loginid ='mfincher'  
            

      2. UPDATEing Rows in a Table
         1. UPDATE changes the values in an existing row

            UPDATE tbl SET col = value|col|expr
            UPDATE table_name SET column_id = expr  WHERE condition
            
            

         2. Examples:

                update users set password = 'newpass' WHERE user = 'quest'
                update users set password = 'newpass' WHERE (UserID > 1) AND (UserID < 113)
                update users set password = 'newpass', change='Y' WHERE user = 'quest'
                UPDATE discounts SET discount = discount + .1 WHERE lowqty >= 100
                UPDATE employees SET LNAME = UPPER(LNAME)
            
            

         3. updating a value based on other tables

            UPDATE titles SET ytd_sales = 
            (SELECT SUM(qty)
             FROM sales
             WHERE sales.title_id = titles.title_id
             AND ord_date BETWEEN '01/01/94' AND '12/31/94')
            
            

         4. example of adding a record based on data in another table

            INSERT adult SELECT
              ju.member_no,  ad.street,  ad.city,  ad.state,  ad.zip,
              ad.phone_no,
              DATEADD(YY, 1, GETDATE())
            FROM
              juvenile ju -- aliasing juvenile to ju
            JOIN
              adult ad    -- aliasing adult to ju
            ON
              ju.adult_member_no = ad.member_no
            WHERE
              (DATEADD(YY, 18, ju.birth_date) < GETDATE())
            
            

      3. Deleting rows from a Table DELETE FROM table_name [WHERE search_condition] example:

         DELETE FROM mytable WHERE userid < 50
         DELETE tablename -- deletes all rows in a table (see "DROP TABLE" to delete a table)
         TRUNCATE TABLE tablename -- deletes all rows, but doesn't log transaction
         DELETE titles WHERE title_id in (SELECT title_id FROM ...)
         

      4. Example of CAST, Datepart, and STR

         In the example below, "dtime" is a column containing the datetime. "datepart(yy,dtime)" produces the year, "str(datepart(dy,dtime),3)" produces a right justified day-of-the-year.

         SELECT cast(datepart(yy,dtime) as varchar(4)) + ' ' + str(datepart(dy,dtime),3)  as 'Year  Day', 
                COUNT(time) AS 'Key Question Pages',
                avg(time) AS 'avg time (msec)' 
         FROM sdrespondentpagetimes
         WHERE name='Key'
         group by cast(datepart(yy,dtime) as varchar(4)) + ' ' + str(datepart(dy,dtime),3)
          order by cast(datepart(yy,dtime) as varchar(4)) + ' ' + str(datepart(dy,dtime),3) asc
         

         The results:

         Year  Day Key Question Pages avg time (msec) 
         --------- ------------------ --------------- 
         2004  51  13                 768
         2004  54  10                 747
         2004  56  6                  1090
         2004  57  34                 702
         ...
         2004 351  1                  6000
         2004 362  14                 548
         2005   4  3                  629
         2005   5  20                 834
         2005   7  10                 469
         2005  10  1                  3593
         2005  11  1228               269
         

      Top rated books on SQL Server development:

   6. SQL Server Utilities
      1. osql - the command line batch interface to SQL Server
         1. To EXECUTE a single statement use the -Q option

                osql -Umoi -P"mypassword"
                   -Q"INSERT DATA_TABLE (column1, column2) values col1_value, col2_value"
         

         1. To have osql run a script file, (e.g., testSQL.SQL), use the -i option

            osql -Umyuserid -P"mypassword" -itestSQL.SQL
            

         2. Then to get things back out use the -o option:

            osql -Umoi -P
            -Q"SELECT column1, column2 FROM DATA_TABLE" -oOutput_file
            

         3. To specify a host: -H delphi
         4. To specify a server: -S delphi
         5. To specify a server running SQLExpress: -S "delphi\SqlExpress"
         6. to specify a database: -d db3
         7. to surpress the headers: -h-1
         8. to specify a separator: -s!
         9. to set the line width to 5000: -w5000

         A better way to handle the password is to set an environmental variable, "OSQLPASSWORD", so the "-U" option may be omitted.

      2. To get a list of all databases on a server

         SELECT DISTINCT name FROM SYSDATABASES
         

      3. To see which service pack (if any is installed)

         SELECT SERVERPROPERTY('ProductLevel')
         Returns:
         'RTM' = shipping version.
         'SPn' = service pack version
         'Bn', = beta version.
         

      4. To impersonate a user:

              SETUSER 'mynewname'
              GO
              (SQL statements)
         
         

         
         use SETUSER to get back to the original user
      5. To add a login for a new user for a database:

              EXECUTE sp_adduser 'newloginid'
              EXECUTE sp_addlogin 'newloginid','password'
         
         

      6. To find the size of all databases

         EXEC sp_helpdb  
         -- or for one database
         EXEC sp_helpdb 'dbname'
         

      7. To grant a user permission to a database:

              USE mydatabase
              EXECUTE sp_adduser newloginid
         

      8. To grant a user permission to a database table:

         GRANT [EXECUTE|ALL] ON TableName TO myloginid
         

      9. Note: If a stored procedure is not the first item in a series of SQL statements, it must be prefaced with 'EXECUTE'.
      10. To unload a dll: dbcc xp_netq (FREE)
      11. To have SQLServer run a simple diagnostic on a db named mydb:

          dbcc checkdb(mydb) 
          

          also try dbcc checkalloc(db4)

      12. To get the name of the current database

          SELECT db_name()
          

      13. to show the names of all databases

          USE master
          SELECT name FROM sysdatabases
          

      14. to get the date

          SELECT getDate()
          

   7. SQL Server Results
      1. Three ways to change column headings

         SELECT First = au_fname,
                au_lname 'Last Name',
                au_id AS 'Identification Number'
         FROM authors
         Produces:       
         First      Last Name                  Identification Number 
         ----------------------------------------------------------- 
         Johnson    White                      172-32-1176           
         Marjorie   Green                      213-46-8915           
         
         

      2. Retrieving Results
         1. General to get unique values:

            SELECT DISTINCT column_name FROM mytable -- note: this also does an implicit sort
            

         2. to get frequency of column values:

            SELECT column_name, COUNT(column_name) as mycount FROM table_name
                  GROUP BY column_name ORDER BY mycount [ASC | DESC]
            
            

         3. to join two tables the SQLServer way:

            SELECT tablea.A1, tableb.B1 FROM tablea, tableb
                WHERE tablea.mykey = tableb.mykey
            
            

         4. to get info from two tables

            UPDATE table1 SET mycolumn = '2' WHERE userid IN
                     ( SELECT userid FROM table2 WHERE table2.dissat <> 'somevalue')
            
            

      3. Aliasing tables

         SELECT a.title_id, a.au_id, b.au_id
         FROM titleauthor a
         INNER JOIN titleauthor b
         ON a.title_id = b.title_id
         WHERE a.au_id < b.au_id
         

      4. how to preface info in a column with a string, 'x-',
         

         UPDATE mytable SET mycolumn = 'x-'+mycolumn
         

      5. wildcards:

         Wildcard	Meaning
         %	Any string of zero or more characters
         _	Any single character
         [ ]	Any single character within the specified range ([a-f]) or set
         ([abcdef])
         [^]	Any single character not within the specified range ([^a-f]) or set ([^abcdef])

         LIKE 'Mc%' searches for all names that begin with the letters "Mc" (McBadden).
         SELECT column_name FROM table WHERE mystring LIKE '%[XYZ]' 
         
         To find all fields which contain a carriage return:
         SELECT userid, Zip FROM mytable WHERE Zip like '%'+char(10)+'%'
         
         

      6. Advanced Topics in Retrieving Results
         1. Limit the number of rows returned

            (note this can be very dangerous since it affects all subsequent commands)

            SET rowcount 4 
            SELECT TableName,ColumnName,Language Title,LU_Table,Description,Logic,Type FROM DataGroup 
            SET rowcount 0 -- turns off rowcounting
            

         2. UNION This concatenates result set together

            USE sqldemo
            SELECT * FROM authors   --23 rows
            UNION
            SELECT * FROM newauthors  --8 rows
            ORDER BY authors.au_lname
            

            UNION will surpress duplicates, use UNION ALL to show all rows

         3. GROUP BY and HAVING

            /*
            **  This query will search through the member_no column looking for
            **  member numbers that have duplicate values and also show the number
            **  of duplicates found for each member number.
            */
              SELECT member_no, Number_of_Dups = count(*)
              FROM member
              GROUP BY member_no
              HAVING count(*) > 1
            
            

   8. Stored Procedures
      1. Stored Procedures
         1. General syntax of stored procedure

            CREATE PROCEDURE pname
               @var vartype[=defaultvalue][,@var2 vartype]
            AS
               ...
            GO
            

         2. Declaring Variables

            DECLARE @varname type[,@varname type]* -- to define a variable
            DECLARE @x int
            SELECT @x = 5
            

         3. Simple Example

            CREATE PROCEDURE emplist
            AS
              SELECT empname, title, salary
              FROM emp
              ORDER BY title
            GO
            
            

         4. Simple Example with one argument

            CREATE PROCEDURE AuthorState
            @statevar CHAR(2) = '__' -- if no arg passed, '__' is the default
            AS SELECT state, au_fname, au_lname FROM authors WHERE state LIKE
            @statevar ORDER BY au_lname GO To test it: EXECUTE AuthorState
            'UT'
            

         5. Fun things to know and tell about Stored Procedures
            1. Stored Procedures can have up to 255 parameters
            2. EXECUTE sp_helptext mystoredproc -- shows the source code
            3. EXECUTE sp_depends mystoredproc -- see what tables are associated with the sp
            4. SELECT name FROM sysobjects WHERE type = 'P'-- to see all the stored procedures
            5. sp_makestartup -- makes an existing sp a startup procedure
            6. sp_ummakestartup -- removes an existing sp a startup procedure
            7. in transact sql to get the results of a select into a variable:
               

               SELECT @LanguageStringID = MAX(LanguageStringID) FROM
               LanguageString

         6. drop an existing sp

            IF EXISTS ( SELECT name FROM sysobjects WHERE type = 'P' AND name = 'addadult' )
               DROP PROCEDURE addadult
            
            

         7. example of complete sp:

            CREATE PROCEDURE addadult
                @lastname         shortstring,
                @firstname        shortstring,
                @middleinitial    letter = NULL,
                @street           shortstring = NULL,
                @city             shortstring = NULL,
                @state            statecode = NULL,
                @zip              zipcode = NULL,
                @phone_no         phonenumber = NULL
            AS 
                DECLARE  @member_no  member_no
            
                IF  @middleinitial = NULL OR
                    @street        = NULL OR
                    @city          = NULL OR
                    @state         = NULL OR
                    @zip           = NULL OR
                    @phone_no      = NULL 
                BEGIN
                  PRINT "   "
                  PRINT "   Please reenter this Adult Member."
                  PRINT "   You must provide Last name, First name, Middle initial, "
                  PRINT "    Street, City, State, Zip, and Phone number."
                  PRINT "   "
                  RETURN
                END
            
                BEGIN TRANSACTION
            
                SELECT @member_no = (SELECT max(member_no) FROM member HOLDLOCK) + 1
            
                    INSERT member 
                        ( member_no,  lastname,  firstname,  middleinitial, photograph)
                    VALUES    
                        (@member_no, @lastname, @firstname, @middleinitial, NULL )
                    IF @@error <> 0
                     BEGIN
                       ROLLBACK TRAN
                       RETURN
                     END
                    INSERT adult
                        ( member_no,  street,  city,  state,  zip,  phone_no, expr_date)
                    VALUES
                        (@member_no, @street, @city, @state, @zip, @phone_no, dateadd(year,1,getdate()) )
                    IF @@error != 0
                     BEGIN
                       ROLLBACK TRAN
                       RETURN
                     END
            
                     PRINT "      "
                     PRINT "        *** Adult Member added ***   "
                     PRINT "      "
            
            
                COMMIT TRANSACTION
            GO
            

         8. Another Example

            CREATE PROCEDURE overdue_books 
            AS
            SELECT member.lastname,copy_no,due_date FROM loan 
            JOIN member ON loan.member_no = member.member_no
                WHERE DATEDIFF(DAY,GETDATE(),due_date)>1
            GO
            
            

         9. OUTPUT parameter

            Example using the OUTPUT parameter

            ** This script file creates a procedure, LOANCOUNT, 
            ** that returns a status of 0 if a member has any 
            ** loans. If a member has no loans, it returns a
            ** status of -1. 
            USE library 
            GO CREATE PROC loancount @member_no member_no, @loancount int OUTPUT AS IF EXISTS (SELECT
            * FROM loan WHERE member_no = @member_no) 
            
            BEGIN SELECT
            @loancount=COUNT(*) FROM loan WHERE member_no = @member_no RETURN
            (0) END ELSE RETURN (-1) 
            GO 
            --NOTE: if any syntax errors in a batch, nothing in batch runs 
            -- all the statements are rolled together for syntax checking 
            -- if any statements have problems the "GO" is not executed.
            

   9. Control of Flow
      1. Control of Flow
         1. IF

            IF(condition)
              BEGIN
              ...
              END
            ELSE
              BEGIN
              ...
              END
             
            

            Example:
            

              IF (SELECT COUNT(member_no) 
                  FROM loan
                  WHERE member_no = @param) <= 4
              RETURN 1
              ELSE
              RETURN 2
            GO
            
            

         2. WHILE
            1. Syntax
               WHILE condition BEGIN ... END BREAK/CONTINUE
            2. Example of simple WHILE statement

               set nocount on
               declare @i int SELECT @i = 0
               while (@i < 10)
                  BEGIN
                  SELECT 'i = ' + convert(char(5),@i)
                  SELECT @i = @i + 1
                  END 
               set nocount off
               

               Code to insert 3000 rows into the database

               DECLARE @i INT
               SET @i=10000
               WHILE(@i <= 30000)
                  BEGIN
                  INSERT INTO mytable (date, thread, level, logger, server, userid, message) 
                  VALUES
                  (GETDATE(),'0000','INFO','XYZ1','LogFile',@i,'my message')
                  SET @i=@i+1
               END
               

         3. Example to show space used by each table

            DECLARE @next VARCHAR(30)
            SELECT @next = ' '  --Makes it "not null"
            WHILE @next IS NOT NULL
            BEGIN
              SELECT @next = MIN(name)
              FROM sysobjects
              WHERE type = 'U'  --User Tables
              AND name > @next
              ORDER BY name
              EXECUTE sp_spaceused @next
            END
            

         4. RETURN used for 1 non-null integer

            CREATE PROCEDURE checkmem
              @param INT
            AS
              IF (SELECT COUNT(member_no) 
                  FROM loan
                  WHERE member_no = @param) <= 4
              RETURN 1
              ELSE
              RETURN 2
            GO
            To use this construct:
            DECLARE @ret INT
            EXECUTE @ret = checkmem 100
            

      2. Misc commands
         1. Example to create many databases

            set nocount on
            declare @i int SELECT @i = 2
            declare @si char(1)
            while (@i < 5)
               BEGIN
               SELECT @si = convert(char(1),@i)
               SELECT 'i = ' + convert(char(5),@i)
               exec('CREATE DATABASE db'+@si+' ON sndat = 1 LOG ON snlog = 1')
               SELECT @i = @i + 1
               END 
            set nocount off
            
            

         2. sp_tables

            sp_tables -- to show a list of tables in a database:
            -- or
            SELECT name FROM sysobjects WHERE type = 'u'
            

      3. Working with parameterized SQL

         You have to declare and set the variable

         declare @id int 
         select @id = 4140
         SELECT userid FROM mytable WHERE userid > @id ORDER BY userid
         

      4. loop thru all the tables in a database and UPDATE their Statistics

         set nocount on
         declare @next varchar(30)
         SELECT @next = ' '
         while @next IS NOT NULL
          BEGIN
          SELECT @next = MIN(name) FROM sysobjects WHERE type = 'u' and name > @next
          SELECT 'next is ' + @next
           IF @next IS NOT null
              BEGIN
                    EXECUTE ('UPDATE STATISTICS ' + @next)
                    END
          END
         
         set nocount on
         
         

      5. loop thru all the databases and print the name. (Useful for performing operations on each database).

         set nocount on
         use master
         declare @next varchar(30)
         SELECT @next = ' '
         while @next IS NOT NULL
          BEGIN
          SELECT @next = MIN(name) FROM sysdatabases WHERE dbid > 5 AND name > @next
          --SELECT 'next is ' + @next
           IF @next IS NOT null
              BEGIN
                    SELECT 'Looking at database',@next
                    END
          END
         
         set nocount on
         

      6. RAISERROR This writes a message to the event log

         RAISERROR('** this is my message',16,1) WITH LOG
         RAISERROR('** this is my message',servity,state) WITH LOG
         
         --error serverity 1-16 ok, 17-18 serious, 19-25 fatal
         

      7. Adding Space to a Database: Add 1 MEG:

         ALTER DATABASE library ON library_dev1 = 1 -- MEG to increase by
         

         To increase size of log file:

         ALTER DATABASE library ON logdevname = size in 2k
         

      8. PRINT

               PRINT 'this is a print, its a pretty worthless statement'
               PRINT 'SELECT does the same thing only better'
         

      9. CASE statement

         -- SIMPLE CASE EXPRESSION
         SELECT Category = 
            CASE type
                    WHEN 'popular_comp' THEN 'Popular Computing'
                    WHEN 'mod_cook'     THEN 'Modern Cooking'
                    WHEN 'business'     THEN 'Business'
                    WHEN 'psychology'   THEN 'Psychology'
              WHEN 'trad_cook'    THEN 'Traditional Cooking'
                    ELSE 'Not Yet Categorized'
                  END,
                CONVERT(varchar(30), title) AS 'Shortened Title',
                Price
         FROM titles
         WHERE price IS NOT NULL
         ORDER BY type
         COMPUTE AVG(price) BY type
         

      10. SEARCHED CASE EXPRESSION

          SELECT m.member_no, 
                 m.lastname,
                 'Member Type' = 
                    CASE 
                      WHEN m.member_no IN
                        (SELECT member_no 
                   FROM juvenile j
                         WHERE j.member_no = m.member_no)
                THEN 'Juvenile'
                      ELSE 'Adult'
                    END,
                 expr_date
          FROM member m, adult a, juvenile j
          WHERE j.adult_member_no = a.member_no
          AND (m.member_no = a.member_no OR m.member_no = j.member_no)
          AND expr_date < GETDATE()
          ORDER BY m.member_no
          

2. BuiltIn Functions
   1. BuiltIn Functions
      1. Aggregate Functions: SUM, AVG, MIN, MAX, and COUNT example:

         SELECT SUM(ytd_sales) FROM titles
         
         to get the number of something:
            SELECT COUNT(*) FROM Datagroup WHERE Language = 'ENU'
         COUNT([ALL|DISTINCT]expression) -- note: count can have options 
         
         
            - GROUP BY
            - COMPUTE or COMPUTE BY
            - WITH CUBE or WITH ROLLUP 
         

      2. Example of COMPUTE

         SELECT respondents.surveyName,
                min(minValue) as 'desired min age', 
                min(a1) as 'actual min age', 
                abs(min(minValue) - min(a1)) as 'diff min age',
                max(maxValue) as 'desired max age', 
                max(a1) as 'actual max age', 
                abs(max(maxValue) - max(a1)) as 'diff max age',
               (min(minValue)+max(maxValue))/2  as 'desired avg age' ,
                avg(a1) as 'actual avg age',
                abs((min(minValue)+max(maxValue))/2 - avg(a1)) as 'diff avg age',
                abs(min(minValue) - min(a1))+abs(max(maxValue) - max(a1))+ abs((min(minValue) + 
                max(maxValue))/2 - avg(a1)) as 'Total Skew'
         from respondents join surveyDirectorQuotas on surveyDirectorQuotas.surveyName = 
              respondents.surveyName 
         --where respondents.surveyName = 'EClientE2'
          and quotaQuestion = '_age'
         group by respondents.surveyName 
         COMPUTE count(respondents.surveyName),
         sum(abs(min(minValue) - min(a1))+abs(max(maxValue) - max(a1))+ 
            abs((min(minValue)+max(maxValue))/2 - avg(a1)))
         

      3. NULLIF (expr1,epr2) returns null if the two expressions are equal

         SELECT AVG(NULLIF(royaltyper, 100))
                AS 'Average Shared Royalty %'
         FROM titleauthor
         

      4. COLESCE(expr1,expr2,...exprN) -- returns first non-null value
      5. ISNULL

         Wonderful function that returns a specified value (in this example -1) if the query returns null.

         SELECT ISNULL( (SELECT userid FROM billing WHERE pid = @pid) ,-1)
         

      6. Misc

         /* What database is now open? */
         SELECT "Open Database is:",DB_NAME()
         

         /* What is it's unique database identifier? */
         SELECT "Database ID is:",DB_ID()
         

         /* What is the current host machine name? */
         SELECT "Host Name is:",HOST_NAME()
         

         /* What is the login name of the current user? */
         SELECT "Login Name is:", SUSER_NAME()
         

         /* What is the database user name of the current user? */
         SELECT "DB User Name is:",USER_NAME()
         

      7. ARITHMETIC OPERATORS

         SELECT price, 
                (price * 1.1) AS 'New Price', 
                title
         FROM titles
         
         --Round to 2 decimals
         SELECT price, 
                CONVERT(MONEY, (price * 1.1)) AS 'New Price', 
                title
         FROM titles
         

      8. MATHEMATICAL FUNCTIONS

         ABS (numeric_expr)  Absolute value of the numeric
         CEILING (numeric_expr)  Smallest integer greater than or equal to
                                   the numeric expression. 
         COS (float_expr)  Trigonometric cosine of the specified angle (in radians)
         RAND  ([seed])  Random approximate numeric (float) value between 0 and 1,
         ROUND (numeric_expr, length)  Numeric expression rounded off to the
                                 length (or precision)
         

         Example:

         SELECT price,
                FLOOR(price) AS 'Floor Price',
                CEILING(price) AS 'Ceiling Price'
         FROM titles
         

3. TRANSACTIONS

   BEGIN TRANSACTION -- forces all or none of the following commands
   INSERT ...
   INSERT ...
   IF()
     ROLLBACK TRANSACTION
   COMMIT TRANSACTION
   
   

4. Using @@error

   IF @@error <> 0 BEGIN ROLLBACK TRAN
   RETURN END 
   

5. LOCKS

   SELECT * FROM authors (nolock)  -- will read even locked pages
   
   SET TRANSACTion isolation level READ UNCOMMITTED -- lets reader look
   SELECT * FROM authors
   
   sp_tableoption tablename,'INSERT row lock', true -- to turn on InsertRowLock
   

6. String Functions
   1. Misc

      CONVERT (type, var)     Converts types (eg, convert(real,'12.5'))
      RTRIM (char_expr) Removes trailing blanks.
      LOWER (char_expr) Converts uppercase character data to lowercase.
      LTRIM (char_expr) Removes leading blanks.
      SUBSTRING(expression, start, length)  Returns part of a
                     character or binary string.
      STUFF (char_expr1, start, length, char_expr2) Deletes length
                     characters FROM char_expr1 at start and then inserts
                     char_expr2 into char_expr1 at start. 
      UPPER (char_expr) Converts lowercase character data to uppercase.
      
      

      Examples:
      

      SELECT 'The price for ' + 
      CONVERT(char(38), title) -- doing a substring but appends spaces to make it 38
      + ' is $' + CONVERT(varchar(10), price) FROM titles
      
      SELECT fname + ' ' + lname + ' was hired on ' +
             CONVERT(char(8), hire_date, 1)
      FROM employee
      
      SELECT LOWER(lastname + SUBSTRING(firstname,1,2)) FROM member WHERE lastname = 'ANDERSON'
      
      --Round to 2 decimals
      SELECT price, 
             CONVERT(MONEY, (price * 1.1)) AS 'New Price', 
             title
      FROM titles
      
      -- shows last name, authors first initial
      SELECT au_lname + ', ' + SUBSTRING(au_fname,1,1) + '.' AS 'Name',
             au_id
      FROM authors
      

   2. Dates

      /* What is the current date and time? */
      SELECT 'Current date and time is',GETDATE()
      
      /* What is a week from today? */
      SELECT 'A week from today is',DATEADD(WEEK,1,GETDATE())
      
      /* How many days between now and end of century? */
      SELECT 'Days until Year 2000 is',DATEDIFF(DAY,GETDATE(),'01/01/2000')
      
      /* What is the name of the current month? */
      SELECT 'Name of this month is',DATENAME(MONTH,GETDATE())
      
      /* What is the name of the day for today? */
      SELECT 'Day of the week is',DATENAME(WEEKDAY,GETDATE())
      
      /* What is the day of the month? */
      SELECT 'Current day of month is',DATEPART(DAY, GETDATE())
      
      /* get date differences in seconds */
      SELECT userID, DATEDIFF(second,dtime , getdate()) FROM respondents 
      
      

7. ORDER BY

   ORDER BY sorts results sets

   1. example:

      SELECT column_name, COUNT(column_name) as mycount
         FROM table_name ORDER BY mycount [ASC | DESC] 
      

   2. ORDER BY can have mulitple arguments

        SELECT colA,colB,colC FROM table1 ORDER BY colB, colC
      

      and you can add in the direction of the sort

        SELECT colA,colB,colC FROM table1 ORDER BY colB ASC, colC DESC
      

   3. ORDER BY will also take a number specifying which columns to sort by. In this case the second and third, so it would be colB and colC.

        SELECT colA,colB,colC FROM table1 ORDER BY 2, 3
      

   4. example:

      SELECT member_no,loan=count(member_no)
      FROM loanhist
      GROUP BY member_no
      HAVING count(member_no) > 50
      
      member_no loan        
      --------- ----------- 
      2         442         
      3         130         
      4         104         
      5         260         
      6         78          
      

   5. Another Example

      SELECT member_no,
             date=loanhist.out_date,
             fine=loanhist.fine_assessed,
             paid=loanhist.fine_paid
      FROM loanhist
      WHERE (member_no BETWEEN 100 AND 150) AND (fine_assessed - ISNULL(fine_paid,0) > 0)
      ORDER BY member_no
      COMPUTE sum(fine_paid), sum(fine_assessed) BY member_no
      

8. JOINs - We are all family here

   Joins allow you to retrieve columns from two or more tables usually within the same database, but also among different databases.

   1. Two types of JOINs ANSI - Join Operators: INNER JOIN, CROSS JOIN, LEFT/RIGHT/FULL OUTER JOIN SQL Server - Join Operators: =,<>, *= and =*
   2. Examples of SQL Server

      SELECT pub_name, title
      FROM titles
      INNER JOIN publishers  --Note: INNER is optional
      ON titles.pub_id = publishers.pub_id
      
      SELECT pub_name, title
      FROM titles, publishers
      WHERE titles.pub_id = publishers.pub_id
      

      Join Types and estimated usage from Joe Temborius:

      % use  Join Types
      90     Inner
      0      Cross
      6      Outer
      1      Self
      3      Union
      

   3. Example of ANSI INNER JOIN

      SELECT stor_name, qty, title
      FROM titles
      INNER JOIN sales
      ON titles.title_id = sales.title_id
      INNER JOIN stores
      ON stores.stor_id = sales.stor_id
      

   4. Example of multiple ANSI INNER JOIN

      SELECT co.isbn,
             co.copy_no,
             co.on_loan,
             ti.title,
             ti.author,
             it.language,
             it.cover,
             it.loanable
      FROM title ti
             INNER JOIN copy co
             ON ti.title_no = co.title_no
             INNER JOIN item it
             ON it.title_no = co.title_no
      WHERE
             co.isbn = 1500 OR co.ISBN = 1000
      ORDER BY co.isbn
      

   5. Example of LEFT OUTER JOIN

      SELECT member.member_no,
             lastname,firstname,middleinitial,
             isbn,
             log_date
      FROM member LEFT OUTER JOIN reservation
       ON member.member_no = reservation.member_no
      WHERE member.member_no = 340 OR member.member_no = 341
      ORDER BY member.member_no
      

   6. Example of SQLServer Join

      SELECT member_no , count(*) FROM reservation 
      WHERE isbn IN 
      (SELECT item.isbn 
      FROM title 
      JOIN item 
      ON title.title_no = item.title_no
      WHERE title LIKE "%Programmer%")
      group by member_no
      

   7. A small example of joins given:

      CREATE TABLE table1
      (
       mykey     int NOT NULL ,
       letter1    char(1) NOT NULL,
      )
      INSERT INTO table1 (mykey,letter1) VALUES (1,'a')
      INSERT INTO table1 (mykey,letter1) VALUES (2,'b')
      INSERT INTO table1 (mykey,letter1) VALUES (3,'c')
      
      CREATE TABLE table2
      (
       mykey     int NOT NULL ,
       letter2    char(1) NOT NULL,
      )
      INSERT INTO table2 (mykey,letter2) VALUES (1,'d')
      INSERT INTO table2 (mykey,letter2) VALUES (2,'e')
      INSERT INTO table2 (mykey,letter2) VALUES (4,'g')
      

      which creates two tables:

      TABLE: table1
      mykey [int(10)]	letter1 [char(1)]
      1	a
      2	b
      3	c

      TABLE: table2
      mykey [int(10)]	letter2 [char(1)]
      1	d
      2	e
      4	g

   8. More Examples of JOINs
      1. INNER JOIN - connects only the common rows.

         SELECT table1.mykey,table1.letter1,table2.letter2
         FROM table1
         INNER JOIN table2
         ON table1.mykey = table2.mykey
         which produces:
         mykey       letter1 letter2 
         ----------- ------- ------- 
         1           a       d       
         2           b       e       
         

      2. CROSS JOIN - creates the cartesian product (all possible combinations)

         SELECT table1.mykey,table1.letter1,table2.letter2
         FROM table1
         CROSS JOIN table2
         
         
         which produces:
         mykey       letter1 letter2 
         ----------- ------- ------- 
         1           a       d       
         1           a       e       
         1           a       g       
         2           b       d       
         2           b       e       
         2           b       g       
         3           c       d       
         3           c       e       
         3           c       g       
         

      3. OUTER JOIN - three types
         1. LEFT OUTER JOIN - selects all valid rows from the first table

            SELECT table1.mykey,table1.letter1,table2.letter2
            FROM table1
            LEFT OUTER JOIN table2
            ON table1.mykey = table2.mykey
            

         2. RIGHT OUTER JOIN - selects all valid rows from the second table

            SELECT table1.mykey,table1.letter1,table2.letter2
            FROM table1
            RIGHT OUTER JOIN table2
            ON table1.mykey = table2.mykey
            
            mykey       letter1 letter2 
            ----------- ------- ------- 
            1           a       d       
            2           b       e       
            (null)      (null)  g       
            

         3. FULL OUTER JOIN - selects all matching rows from both

            SELECT table1.mykey,table1.letter1,table2.letter2
            FROM table1
            FULL OUTER JOIN table2
            ON table1.mykey = table2.mykey
            
            mykey       letter1 letter2 
            ----------- ------- ------- 
            1           a       d       
            2           b       e       
            3           c       (null)  
            (null)      (null)  g       
            
            

      4. Multiple OUTER JOINs

         CREATE TABLE table3
         (
          mykey     int NOT NULL ,
          letter3    char(1) NOT NULL,
         )
         INSERT INTO table3 (mykey,letter3) VALUES (1,'d')
         INSERT INTO table3 (mykey,letter3) VALUES (2,'e')
         INSERT INTO table3 (mykey,letter3) VALUES (5,'h')
         
         Then we execute,
         
         SELECT table1.mykey,table1.letter1,table2.letter2,table3.letter3
         FROM table1
         FULL OUTER JOIN table2 ON table1.mykey = table2.mykey
         FULL OUTER JOIN table3 ON table1.mykey = table3.mykey
         
         Which yields:
         
         mykey       letter1 letter2 letter3 
         ----------- ------- ------- ------- 
         1           a       d       d       
         2           b       e       e       
         3           c       (null)  (null)  
         (null)      (null)  g       (null)  
         (null)      (null)  (null)  h       
         
         

9. Subqueries
   1. Three ways to connect queries with subqueries
      1. =,>,< ...
      2. IN
      3. EXISTS
   2. Example 1

      --This is a 'classic' subquery.
      --Show all titles that have a price 
      --larger than average of all titles.
      USE pubs
      SELECT price, title
      FROM titles
      WHERE price >
      (SELECT AVG(price) FROM titles)
      

   3. Example 2

      SELECT title_id AS Title,
             qty AS Quantity,
             (SELECT SUM(qty) FROM sales) AS 'Total Sales',
             (CONVERT(money,qty)/(SELECT SUM(qty) FROM sales))*100 
             AS '% of Total'
      FROM sales
      

   4. Example 3

      SELECT title_id, title
      FROM titles
      WHERE title_id IN
      (SELECT title_id FROM sales)
      

10. Triggers

    Triggers are bits of code that are executed when an operation occurs on a table. Triggers can be set for INSERT,UPDATE, or DELETE

    1. Example

         CREATE TRIGGER deltrig
         ON emp
         FOR DELETE
       AS
         DECLARE @currentuser VARCHAR(30)
         SELECT @currentuser = 'Sorry, ' + USER_NAME()
         PRINT @currentuser
         PRINT 'Employees can''t be deleted!'
         ROLLBACK TRANSACTION
       GO
       

    2. Example - get info about a trigger

       SELECT name, crdate 
       FROM sysobjects
       WHERE type = 'TR'
       
       EXECUTE sp_helptext deltrig -- show code, if not encrypted
       
       EXECUTE sp_depends deltrig -- what tables does it affect
       

    3. drop trigger IF EXISTS ( SELECT name FROM sysobjects WHERE
       type = 'TR' AND name = 'member_insert' ) DROP TRIGGER
       member_insert 
       

    4. an INSERT trigger creates a special table, called 'inserted' with the inserted row DELETE triggers create 'deleted' with the deleted rows These tables can be queried.
    5. to create your own messages -- sp_addmessage (>50000)
    6. sp_configure 'nested triggers',0 -- prevents cascading triggers
    7. Example:

       CREATE TRIGGER member_insert
           ON member
           FOR INSERT
       AS
       /* 
       ** Don't allow duplicate primary keys.
       ** This is normally done via a unique 
       ** index rather than a trigger.  It is
       ** included here just as an example.     
       */
       
       IF (SELECT count(*)
           FROM member, inserted
           WHERE member.member_no = inserted.member_no) > 1
       BEGIN
           RAISERROR ('Transaction will not be processed. Two members cannot have the same member_no.',10,1)
           ROLLBACK TRANSACTION
       END
       ELSE
       

    8. Example:

       /*
       ** Auto generate a primary key value, if needed
       ** (A zero got in there because it is the default; 
       ** ie, the user did not enter a member number.)    
       ** Works only if one row at a time is being added. 
       */
       
       IF (SELECT count(*)
           FROM member
           WHERE member.member_no = 0) > 0
       BEGIN
           UPDATE member
               SET member_no = (SELECT max(member_no) FROM member) + 1
           WHERE member_no = 0
       END
       GO
       

11. Data Integrity/IDENTITY

    An identity forces SQL server to have a unique value in a field. The starting value and the increment value can be set as follows

    CREATE TABLE stores2
    (
      stor_id  int identity(1000,10) NOT NULL , -- starts at 1000, increments by 10
      stor_name varchar (40) NULL ,
    )
    

    To see the values of these,
    

    SELECT IDENT_SEED('class'), IDENT_INCR('class') -- shows initial identity and increment
    

12. to set directly an IDENTITY field (for testing etc to override defaults)

       SET IDENTITY_INSERT [TableName] ON
    

    Example:

       SET IDENTITY_INSERT Pubs..class ON -- allows an identity to be inserted
    

13. IDENTITYCOL is a keyword which substitues for the name of the identity column

    SELECT MAX(IDENTITYCOL) FROM stores2 
    -- identitycol is a keyword refering to a tables' identity column
    

    -- Defining constraints
    
    Primary Key
    Unique
    Foreign Key
    Default
    Check
    
    in syscomments a field called 'text' contains your constraits
    sp_helpconstraint or sp_help <tablename> -- more friendly
    
    
    

14. Comments

    Comments are surrounded with '/*' and '*/', but cannot, for some
    bizzare reason contain a 'GO' command.
    
    Everything after a '--' is a comment
    

15. CONSTRAINTs

    Constraints can be set on the value of valid fields.

    CREATE TABLE Tbl_lvl ( 
    ItemCode CHAR(6) NOT NULL, 
    UnitCost SMALLMONEY NOT NULL, 
    UnitPrice SMALLMONEY NOT NULL, 
    CONSTRAINT pricediff
    CHECK (UnitPrice > UnitCost) ) GO
    

16. Constraints can be named

    CREATE TABLE Payroll
    (
     Empno CHAR(3) NOT NULL,
    
     Checkno CHAR(10) NOT NULL
     CONSTRAINT U_Checkno
        UNIQUE NONCLUSTERED (checkno),
    
     Paydate SMALLDATETIME NOT NULL
     CONSTRAINT DF_Paydate
        DEFAULT GETDATE(),
     
     Amount SMALLMONEY NOT NULL 
     CONSTRAINT CK_Amount
        CHECK (Amount BETWEEN 10000 AND 100000),
    
     CONSTRAINT PK_Payroll
        PRIMARY KEY CLUSTERED
        (empno,checkno,paydate)
        WITH FILLFACTOR=25,
     
     CONSTRAINT FK_Payroll
        FOREIGN KEY (empno) REFERENCES emp (empno)
    )
    -- must drop constraint before index
    IF EXISTS (SELECT name FROM sysobjects WHERE name = 'member_ident')
      ALTER TABLE member
        DROP CONSTRAINT member_ident
    
    IF EXISTS (SELECT name FROM sysindexes WHERE name = 'member_ident')
        DROP INDEX  member.member_ident
    
    ALTER TABLE member
      ADD CONSTRAINT member_ident PRIMARY KEY CLUSTERED (member_no) WITH FILLFACTOR = 90
    

17. Constraints can set a default value:

    IF EXISTS (SELECT name FROM sysobjects WHERE name = 'state_default')
      ALTER TABLE adult
        DROP CONSTRAINT state_default
    GO
    ALTER TABLE adult
      ADD CONSTRAINT state_default DEFAULT 'WA' FOR state
    GO
    

    to add a primary key restaint:

    alter table mytable ADD primary key (mykeyid, myothercolumn)
    

18. Views

    CREATE VIEW authorlist
    (AuthorName, Location)
    AS
    SELECT SUBSTRING(au_fname + ' ' + au_lname,1,25),
           SUBSTRING(city + ', ' + state + '  ' + zip,1,25)
    FROM authors
    GO
    
    SELECT * FROM authorlist
    
    --
    -- see your views:
    SELECT name, crdate 
    FROM sysobjects
    WHERE type = 'V'
    
    -- to see your source code for the view
    EXECUTE sp_helptext AuthorList
    
    EXECUTE sp_depends AuthorList -- list tables needed for this view
    
    
    CREATE VIEW Managers
    WITH ENCRYPTION
    AS
    SELECT empno, empname, title 
    FROM emp 
    WHERE title='MANAGER'
    WITH CHECK OPTION -- updates/inserts into this view must satisfy WHERE clause
    GO
    
    SELECT * FROM managers
    
    sp_helptext managers
    

    --This should fail the CHECK
    INSERT INTO managers 
    VALUES ('999', 'GATES, BILL', 'MCT') -- fails because MCT != MANAGER
    
    What Service Packs does my SQL Server have installed?
    try
    SELECT @@version
                         
    

19. OBJECT_ID()
    1. How to conditionally delete a view

       -- old style
       IF EXISTS ( SELECT name FROM sysobjects
                   WHERE type = 'V' AND name = 'stuff' )
            DROP VIEW stuff
       -- new style
       IF OBJECT_ID('stuff', 'V') IS NOT NULL 
           DROP VIEW stuff  
       go
       

    2. How to conditionally delete a table

       IF OBJECT_ID('traffic_data', 'U') IS NOT NULL 
           DROP TABLE traffic_data
       GO
       

    3. Common second parameters to OBJECT_ID()

       /*
       C = CHECK constraint
       D = DEFAULT (constraint or stand-alone)
       F = FOREIGN KEY constraint
       PK = PRIMARY KEY constraint
       P = SQL stored procedure
       FN = SQL scalar function
       U = Table (user-defined)
       UQ = UNIQUE constraint
       V = View
       X = Extended stored procedure
       */
       

20. Isolation levels via Table Hints

    If the results of your queries do not always have to be perfect, you can increase query speed and reduce contention by using "READ UNCOMMITTED", which does not lock a table during a SELECT and allows "dirty" reads.

    SELECT COUNT(*) FROM dbo.Authors WITH(NOLOCK) 

    This will not lock the table and make the query faster.

    Use the following command to make an entire session use no locking

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    GO

21. INDEXs

    CREATE INDEX [CLUSTERED|NONCLUSTERED] name ON table (col1,col2,...)
    
    EXECUTE sp_helpindex emp shows info on index
    
    CNTRL -- skips to next heading
    limit: composite index length < 900 chars
    

    SET SHOWPLAN ON -- debug statement to show if indexes are being used
    GO
    SELECT * 
    FROM authors 
    WHERE city = 'Kent'   --table scan
    
    SELECT * 
    FROM authors 
    WHERE au_id = '123-45-6789' --clustered index
    SET SHOWPLAN OFF
    GO
    --
    

    --------------
    -- OPTIMIZER HINTS
    --------------
    SET SHOWPLAN ON
    GO
    SELECT au_lname, au_fname, phone
    FROM authors  (INDEX = 1) -- INDEX=0 is none, INDEX=indexname
    WHERE au_lname = 'Smith'
    SET SHOWPLAN OFF
    GO
    

    --------------
    -- UPDATE STATISTICS -- updates statistics that the optimizer uses 
    to determine which index to use on a query
    --------------
    
    --Plug in a value for TABLENAME
    

    SELECT 'Index Name' = sysindexes.name, 
           'Statistics Date' = STATS_DATE(sysindexes.id, sysindexes.indid)
    FROM sysobjects, sysindexes
    WHERE sysobjects.name = 'authors' 
    AND sysobjects.id = sysindexes.id
    
    UPDATE STATISTICS authors
    
    -- show fragmentation
    DECLARE @tbl INT
    SELECT @tbl = OBJECT_ID('sales')
    DBCC SHOWCONTIG (@tbl)
    

    ------------- check databases
    DBCC CHECKDB   -- should be run once a week on all database, 
                   -- should be done before a backup
    
    --
    daily - dump database, update statistics on all indexes
    hourly - dump transaction log
    weekly - drop and recreate indexes
    -- show performance data
    SET SHOWPLAN ON -- shows indexes being used
    SET STATISTICS IO ON 
    SET STATISTICS TIME ON --
    

    -- drop an index if it exists
    IF EXISTS (SELECT name FROM sysindexes WHERE name = 'reserve_item_link')
        DROP INDEX  reservation.reserve_item_link
    GO
    
    -- example of showing the elapsed time
    DECLARE @message char(255)  DECLARE @began datetime  SELECT @began = GETDATE()
    
    CREATE UNIQUE  INDEX reserve_ident ON reservation (member_no, isbn)
           WITH FILLFACTOR = 75
    
    CREATE INDEX reserve_item_link ON reservation (isbn)
    
    SELECT @message = 'Time (in minutes:seconds) to create reservation related indexes.  '
         + CONVERT( char(2), DATEDIFF(ss,@began,GETDATE())/60 ) + ':'
         + CONVERT( char(2), DATEDIFF(ss,@began,GETDATE())%60 )
    PRINT @message
    GO
    

22. System Variables

    SELECT 'Connections:    ',@@connections
    SELECT 'CPU Busy        ',@@cpu_busy
    SELECT 'Cursor Rows     ',@@cursor_rows
    SELECT 'DBTS            ',@@dbts
    SELECT 'Error           ',@@error
    SELECT 'Fetch Status    ',@@fetch_status
    SELECT 'Identity        ',@@identity
    SELECT 'Idle            ',@@idle    
    SELECT 'IO Busy         ',@@io_busy    
    SELECT 'Language ID     ',@@langid 
    SELECT 'Language        ',@@language
    SELECT 'Max Connections ',@@max_connections
    SELECT 'Max Precision   ',@@max_precision
    SELECT 'MS Version      ',@@microsoftversion
    SELECT 'Nest Level      ',@@nestlevel
    SELECT 'Options         ',@@options
    SELECT 'Pack Received   ',@@pack_received
    SELECT 'Pack Sent       ',@@pack_sent
    SELECT 'Packet Errors   ',@@packet_errors
    SELECT 'Procedure ID    ',@@procid
    SELECT 'Row Count       ',@@rowcount -- how many rows were effected on last operation
    SELECT 'Server Name     ',@@servername
    SELECT 'Service Name    ',@@servicename
    SELECT 'SPID            ',@@spid
    SELECT 'Text Size       ',@@textsize
    SELECT 'Time Ticks      ',@@timeticks
    SELECT 'Total Errors    ',@@total_errors
    SELECT 'Total Read      ',@@total_read
    SELECT 'Total Write     ',@@total_write
    SELECT 'Tran Count      ',@@trancount
    SELECT 'Version         ',@@version
    

23. BCP bulk copy

    rem
    rem   BCPDEMO.BAT
    rem
    rem   This batch file exports all AUTHORS FROM the
    rem   PUBS database to an ASCII file called RESULTS.TXT
    rem 
    rem   /c indicates 'character' format vs. /n for 'native' format
    rem   /t indicates the field terminator (default is TAB)
    rem   /S indicates the Server to connect to
    rem   /U indicates the Login ID
    rem   /P is the password (if any)
    rem
    rem   Type in BCP with no options to see parameter summary
    rem 
    rem   After you run this, go into NOTEPAD and 
    rem   check out the results in 'results.txt'
    rem
    rem   *** CHANGE SERVER NAME BELOW BEFORE RUNNING ***
    
    bcp pubs.dbo.authors out results.txt /c /t"|" /Sstudentx /Usa /P
    
    

24. EXTENDED STORED PROCEDURES

    --Display all 'registered' ESP's
    EXECUTE sp_helpextendedproc
    
    -- to get the last inserted identity
    INSERT Respondents (test) VALUES ('N') SELECT @@IDENTITY 
    
    
    ---- misc xp_*
    USE master
    GO
    
    --Display all devices
    EXECUTE xp_cmdshell 'dir c:\mssql\data\*.dat /os'
    PRINT ''
    
    --Send a message to the NT Event Log
    EXECUTE xp_logevent 60000, 'Stop sign - worst kind!', error
    PRINT ''
    
    --Show current NT accounts that can access SQL Server
    EXECUTE xp_logininfo
    PRINT ''
    
    --Display current SQL Server version and build info
    EXECUTE xp_msver
    PRINT ''
    
    --Display all local NT groups
    EXECUTE xp_enumgroups
    

25. UNDOCUMENTED ESP's

    --XP_REGREAD allows you to read Registry Key values
    
    SET NOCOUNT ON
    GO
    
    DECLARE @org VARCHAR(50)
    DECLARE @own VARCHAR(50)
    
    EXECUTE xp_regread 'HKEY_LOCAL_MACHINE',
                    'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\CurrentVersion',
                    'RegisteredOrganization',@param = @org output
    
    EXECUTE xp_regread 'HKEY_LOCAL_MACHINE',
                    'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\CurrentVersion',
                    'RegisteredOwner',@param = @own output
    
    PRINT 'Whoever set up SQL Server for this class typed these in:'
    PRINT ' '
    SELECT 'Registered Organization is '+UPPER(@org)
    SELECT 'Registered Owner is        '+UPPER(@own)
    
    SET NOCOUNT OFF
    GO
    -->
    

26. CURSORS

    Cursors allow an entire result set to be captured and manipulted as a single piece of data.

    ******************************************************-
    DECLARE @currentvalue char(32)
    DECLARE mycursor CURSOR FOR
      SELECT distinct myvalue FROM mytable
    OPEN mycursor
    FETCH NEXT FROM mycursor INTO @currentvalue 
    WHILE (@@fetch_status = 0)      -- while OK
      BEGIN
            print @currentvalue
            exec('SELECT mycolumn FROM ' + @currentvalue ) -- some usefull work
      FETCH NEXT FROM mycursor INTO @currentvalue
      END
    CLOSE mycursor
    DEALLOCATE mycursor
    GO
    
    
    ******************************************************-
    
    --Count the number of members that have
    --a 'J' as a middle initial value
    
    USE Library
    
    SET NOCOUNT ON
    
    DECLARE @mivalue char(1)
    DECLARE @counter int
    DECLARE @strcounter char(50)
    
    SELECT @counter=0
    
    DECLARE micount CURSOR FOR
    SELECT middleinitial FROM member
    
    OPEN micount
    
    FETCH NEXT FROM micount 
    INTO @mivalue   -- move to first row
    
    WHILE (@@fetch_status = 0)      -- while OK
      BEGIN
      
      IF @mivalue = 'J' SELECT @counter = @counter + 1
      
      FETCH NEXT FROM micount
      INTO @mivalue
    
      END
    
    CLOSE micount
    
    DEALLOCATE micount
    
    SELECT @strcounter = 'Number of J initials:' +CONVERT(char(10),@counter)
    PRINT @strcounter
    GO
    
    -- RAISERROR
    sp_addmessage 55555, 16, 'Mitchs test message',us_english ,true 
    
    RAISERROR(55555,16,1)
    

27. Misc Tips
    1. When SQLServer UPDATEs a row, it really deletes i replaces it with a new one

       SELECT title,copy_no,due_date,DATEDIFF(DAY,due_date,GETDATE()) FROM overdue 
       WHERE DATEDIFF(DAY,due_date,GETDATE()) > 14
       

    2. Finding gaps in a sequence

       This finds any non-consecutive numbers in the userID column.

       SELECT A.userID + 1
       FROM SDRespondents AS A
       WHERE NOT EXISTS (
           SELECT B.userID FROM SDRespondents AS B 
           WHERE A.userID + 1 = B.userID)
       GROUP BY A.userID;
       

    3. Set a database to single use only.

       This is useful in simulating database failure to stop general access to a database.

       EXECUTE sp_dboption library, 'dbo use only', TRUE -- dbo user only
       EXECUTE sp_dboption library, 'dbo use only', FALSE
       

       or

        alter database myDatabaseName set SINGLE_USER WITH ROLLBACK IMMEDIATE
        alter database myDatabaseName set MULTI_USER
       

    4. User Defined Datatypes (UDDT)

         EXECUTE sp_addtype zipcode, 'char(10)'
         EXECUTE sp_addtype phonenumber, 'char(13)', NULL
         EXECUTE sp_droptype <typename>  -- to get rid of it
       

       to get a list of UDDT

         sp_help
       

       example:

       CREATE TABLE member (
       member_no member_no NOT NULL, -- member_no is a User Defined Data Type
       lastname shortstring NOT NULL,
       firstname shortstring NOT NULL,
       middleinitial letter NULL,
       photograph image NULL
       )
       

       USE MAster
       EXECUTE sp_dboption mydb, 'trunc. log on chkpt.',true
       to set SELECT into/bulkcopy
       EXECUTE sp_dboption mydb, 'SELECT into/bulkcopy',true
       

       chars are treated as varchar's if NULL is a possible value.

       Binary Large OBject - BLOB

       SET NOCOUNT ON    --suppress 'rows affected' msg
       SET NOCOUNT OFF
       

    5. Logic don't use "= NULL" use "IS NULL" because its ANSI NULLs fail all comparisons "NULL = NULL" is false Order of Presedence: (),NOT,AND,OR
    6. the EXECUTE command can be used to build commands from parts
    7. Example 1

       create proc displaytable
       @tbl varchar(30)
       as
       EXECUTE ('SELECT * FROM ' + @tbl)
       

    8. Example 2

       SELECT @cmd = "create database "+@projabbrev+" ON "+@datadevice+"  = 6 LOG ON "+@logdevice+" = 6"
       SELECT @cmd
       EXECUTE (@cmd)
       

    9. Terms:

       A batch is a set of one or more SQL statements submitted together and executed as a single group. A script is a series of one or more batches submitted one after the other. A script is a file, with a default extension of .SQL Comparison operators (=,<>,>,<,>=,<=), BETWEEN, IN, LIKE, IS NULL, and IS NOT NULL

    10. The System supplied databases.

        Database	Function
        Master:	Controls all user databases and SQL Server as a whole
        Model:	Serves as a template when creating new user databases
        Msdb:	Provides support for the SQL Executive service
        Tempdb:	Used for temporary working storage for all connections
        pubs:	A sample database

        2. 
           Allocation Unit: 1/2MB increments of database storage space
           Extent: Eight 2K pages of table or index storage space
           Page: The basic unit of I/O in SQL Server (2K in size) 
        
        

    11. Cursors

        --deleteViews
        -- This deletes all views which start with an X
        -- trivial example of using cursors in sql server 6.5
        IF EXISTS ( SELECT name FROM sysobjects
                    WHERE type = 'P' AND name = 'deleteViews' )
            DROP PROCEDURE deleteViews
        go
        ******************----------------
        GO
        
        CREATE PROCEDURE deleteViews
        @databasename varchar(30)
        AS
        DECLARE @viewname SYSNAME,
                @sqls CHAR(255),
                @fullviewname CHAR(255)
        
        SELECT @sqls = 'DECLARE view_cursor CURSOR FOR SELECT name FROM '
        + @databasename + '..sysobjects WHERE type = ''v'' and name LIKE ''X%'' '
        
        --  
        
        exec(@sqls)
        
        OPEN view_cursor
        FETCH NEXT FROM view_cursor INTO @viewname
        WHILE (@@FETCH_STATUS=0)
        BEGIN
            SELECT @fullviewname = @databasename + '..' + @viewname
            SELECT 'current viewname is ' + @fullviewname
            --EXEC ('DROP VIEW ' + @fullviewname)
        
            FETCH NEXT FROM view_cursor INTO @viewname  
        END
        CLOSE view_cursor
        DEALLOCATE view_cursor
        GO
        
        

    12. Get all the column names in a table
        

        SELECT name FROM syscolumns WHERE id in (SELECT id FROM sysobjects
        where name = 'mycolumn')
        

    13. Easy way is to access meta data is the 'information_schema' view:
        

        SELECT * FROM information_schema.columns
        

    14. How to restore a database FROM a .BAK file

        RESTORE FILELISTONLY 
            FROM DISK = 
            'C:\Program Files\Microsoft SQL Server\MSSQL$AUS158\BACKUP\SDRespondents.bak'
        RESTORE DATABASE SDRespondents
           FROM DISK = 
            'C:\Program Files\Microsoft SQL Server\MSSQL$AUS158\BACKUP\SDRespondents.bak'
           WITH MOVE 'SDRespondents_Data' TO 
             'C:\Program Files\Microsoft SQL Server\MSSQL$AUS158\Data\SDRespondents.mdf',
           MOVE 'SDRespondents_log' TO 
             'C:\Program Files\Microsoft SQL Server\MSSQL$AUS158\Data\SDRespondents.ldf'
        GO
        

    15. How to retrieve data as xml

        Use "FOR XML AUTO" or "FOR XML EXPLICIT"

        SELECT * FROM sdgroups FOR XML AUTO
        

    16. How to increase the timeout setting

        When you get an exception like this:

        Exception: System.Net.WebException Message: The operation has timed out
        

        You need to increase the timeout setting by opening Enterprise Manager, right clicking on your server and select "Properties", then "Connections", then set "Remote query timeout" to be something larger.

    17. Connection Strings

        Bad example where password is embedded (but occassionally useful for testing on dev boxes)

        Application Name=myApplication; Data Source=myDatabaseServer; user id=sa;password=mypassword; database=myDatabaseName
        

        Real solution where password is not in connection string

        Data Source=myDatabaseServer; Trusted_Connection=yes; database=myDatabaseName
        

    18. Working with Devices

        Devices are containers for databases and their logs.

        1. Creating Devices

           DISK INIT creates devices:
           To create data devices you must be in master.
           
           DISK INIT name='LIBRARY_DEV1',
            physname = 'c:\MSSQL\DATA\LIBRARY.DAT',
            vdevno = 100, -- must be unique, use sp_helpdevice()
                          -- to find currently used device_number(s)
            size = 10240  -- in 2K blocks
            DISK INIT
            name='LIBRLOG_DEV2',
            physname = 'c:\MSSQL\DATA\LIBRLOG.DAT',
            vdevno = 101,
            size = 4096
           

        2. Resizing Devices -- you can only increase the size of a device, never shrink DISK RESIZE name='MASTER', SIZE=15360 -- size is in 2k pages
        3. Deleting Devices Note: When using EntepriseManager to drop a device, it does not drop the .DAT file, instead use: sp_dropdevice logical_name [, DELFILE] -- deletes .DAT file too
28. Globally Unique Identifier - GUID

    GUIDs are 16 byte binary integers created to be unique identifiers across database instances.

    1. Create a GUID

       SELECT newid()
       

       Produces:

       B3A15B59-AD75-4D8B-8888-0D839C84C301
       

    2. An example

       We create the first row by using newid() to have sqlserver create the GUID for us, the second row we create by feeding it a GUID directly.

       CREATE TABLE guidpractice
       (
        guid      UNIQUEIDENTIFIER,
        fname     VARCHAR(20)   NOT NULL,
        lname     VARCHAR(30)   NOT NULL,
       )
       insert guidpractice (guid,fname,lname) values (newid(),'Adam','Smith')
       insert guidpractice (guid,fname,lname) values ('857CFD44-8BB4-4D05-AEF1-22B62142A7FF','Adam','Smith')
       select * from guidpractice
       

       Produces:

       DA4414FD-7178-4DE2-8C77-86817B04ECF8	Adam	Smith
       857CFD44-8BB4-4D05-AEF1-22B62142A7FF	Adam	Smith
       

    3. You can have sqlserver create Guids by default

       CREATE TABLE guidpractice2
       (
        guid      UNIQUEIDENTIFIER DEFAULT newid(),
        fname     VARCHAR(20)   NOT NULL,
        lname     VARCHAR(30)   NOT NULL
       )
       GO
       INSERT guidpractice2 (fname,lname) VALUES ('Adam','Smith')
       INSERT guidpractice2 (fname,lname) VALUES ('Adam','Smith')
       SELECT * FROM guidpractice2
       

       Produces:

       guid                                 fname                lname
       ------------------------------------ -------------------- ------
       D6A672EB-39A5-42E9-92C6-0C7DD0F9324D Adam                 Smith
       609876C7-92A4-4D78-8393-19D72904F194 Adam                 Smith
       
       

29. Misc SQL Stuff
    1. when SQLServer UPDATEs a row, it really deletes it and replaces it with a new one
    2. DATEDIFF

       SELECT title,copy_no,due_date,DATEDIFF(DAY,due_date,GETDATE()) FROM overdue 
       WHERE DATEDIFF(DAY,due_date,GETDATE()) > 14
       

    3. Set a database to single use only.

       EXECUTE sp_dboption library, 'dbo use only', TRUE -- dbo user only
       EXECUTE sp_dboption library, 'dbo use only', FALSE
       

    4. Using local variables

       You can define local variables with the "@" sign. These are used in parameterized sql commands.

       DECLARE @lang varchar(12)
       set @lang = 'en-UK'
       SELECT name from SDTextDefinitions 
       WHERE text LIKE '%buy%' and lang=@lang 
       

    5. Using LIKE with a scalar variable and wildcards

       You need to preface it with the wildcards in separate strings like this

       DECLARE @lang varchar(12)
       DECLARE @searcher varchar(12)
       set @lang = 'en-UK'
       set @searcher = 'buy'
       SELECT name from SDTextDefinitions 
       WHERE text LIKE '%'+@searcher+'%' and lang=@lang 
       

    6. User Defined Datatypes (UDDT)

         EXECUTE sp_addtype zipcode, 'char(10)'
         EXECUTE sp_addtype phonenumber, 'char(13)', NULL
         EXECUTE sp_droptype <typename>  -- to get rid of it
       

       to get a list of UDDT

         sp_help
       

    7. Using a User Defined Type

       CREATE TABLE member (
       member_no member_no NOT NULL, -- member_no is a User Defined Data Type
       lastname shortstring NOT NULL,
       firstname shortstring NOT NULL,
       middleinitial letter NULL,
       photograph image NULL
       )
       

    8. sp_dboption examples

       USE Master
       EXECUTE sp_dboption mydb, 'trunc. log on chkpt.',true
       to set SELECT into/bulkcopy
       EXECUTE sp_dboption mydb, 'SELECT into/bulkcopy',true
       

    9. Chars are treated as varchar's if NULL is a possible value.
    10. NOCOUNT

        SET NOCOUNT ON    --suppress 'rows affected' msg
        SET NOCOUNT OFF
        

    11. Don't use "= NULL". Use "IS NULL" because ANSI NULLs fail all comparisons "NULL = NULL" is false Order of Presedence: (),NOT,AND,OR
    12. the EXECUTE command can be used to build commands from parts
        1. Example 1

           create proc displaytable
           @tbl varchar(30)
           as
           EXECUTE ('SELECT * FROM ' + @tbl)
           

        2. Example 2

           SELECT @cmd = "create database "+@projabbrev+" ON "+@datadevice+"  = 6 LOG ON "+@logdevice+" = 6"
           SELECT @cmd
           EXECUTE (@cmd)
           

    13. Terms:

        A batch is a set of one or more SQL statements submitted together and executed as a single group. A script is a series of one or more batches submitted one after the other. A script is a file, with a default extension of .SQL Comparison operators (=,<>,>,<,>=,<=), BETWEEN, IN, LIKE, IS NULL, and IS NOT NULL

    14. The System supplied databases.

        Database	Function
        Master:	Controls all user databases and SQL Server as a whole
        Model:	Serves as a template when creating new user databases
        Msdb:	Provides support for the SQL Executive service
        Tempdb:	Used for temporary working storage for all connections
        pubs:	A sample database

    15. Wait, pause, sleep command

          WAITFOR DELAY '00:00:59' -- sleeps for 59 seconds
        

        SELECT @cmd = "create database "+@projabbrev+" ON "+ 
        @datadevice+"  = 6 LOG ON "+@logdevice+" = 6"
        SELECT @cmd
        EXECUTE (@cmd)
        
        

    16. Get all the column names in a table
        

        SELECT name FROM syscolumns WHERE id in (SELECT id FROM sysobjects
        where name = 'mycolumn')
        

    17. Easy way is to access meta data is the 'information_schema' view:
        

        SELECT * FROM information_schema.columns
        

    18. How to restore a database FROM a .BAK file

        RESTORE FILELISTONLY 
            FROM DISK = 
            'C:\Program Files\Microsoft SQL Server\MSSQL$AUS158\BACKUP\SDRespondents.bak'
        

        The above command will show two rows. The database name and the log file name are in the first column, 'LogicalName'. These should be inserted below for 'SDRespondents_Data' and 'SDRespondents_log' respectively.

        RESTORE DATABASE SDRespondents
           FROM DISK = 
            'C:\Program Files\Microsoft SQL Server\MSSQL$AUS158\BACKUP\SDRespondents.bak'
           WITH MOVE 'SDRespondents_Data' TO 
             'C:\Program Files\Microsoft SQL Server\MSSQL$AUS158\Data\SDRespondents.mdf',
           MOVE 'SDRespondents_log' TO 
             'C:\Program Files\Microsoft SQL Server\MSSQL$AUS158\Data\SDRespondents.ldf'
        GO
        

    19. How to see all active requests

        SELECT start_time, [text], status, total_elapsed_time,
        		 DB_Name(database_id), blocking_session_id, wait_type,
        		 wait_time, cpu_time, command,
        		 logical_reads, text_size, row_count, session_id
        FROM sys.dm_exec_requests AS R
        CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS S
        WHERE session_id <> @@SPID;
        

    20. How to retrieve data as xml

        Use "FOR XML AUTO" or "FOR XML EXPLICIT"

        SELECT * FROM sdgroups FOR XML AUTO
        

    21. Connection Strings

        Bad example where password is embedded (but occassionally useful for testing on dev boxes)

        Application Name=myApplication; Data Source=myDatabaseServer; user id=sa;password=mypassword; database=myDatabaseName
        

        Real solution where password is not in connection string

        Data Source=myDatabaseServer; Trusted_Connection=yes; database=myDatabaseName
        

    22. Working with Devices

        Devices are containers for databases and their logs.

        1. Creating Devices

           DISK INIT creates devices:
           To create data devices you must be in master.
           
           DISK INIT name='LIBRARY_DEV1',
            physname = 'c:\MSSQL\DATA\LIBRARY.DAT',
            vdevno = 100, -- must be unique, use sp_helpdevice()
                          -- to find currently used device_number(s)
            size = 10240  -- in 2K blocks
            DISK INIT
            name='LIBRLOG_DEV2',
            physname = 'c:\MSSQL\DATA\LIBRLOG.DAT',
            vdevno = 101,
            size = 4096
           

        2. Resizing Devices -- you can only increase the size of a device, never shrink DISK RESIZE name='MASTER', SIZE=15360 -- size is in 2k pages
        3. Deleting Devices Note: When using EntepriseManager to drop a device, it does not drop the .DAT file, instead use: sp_dropdevice logical_name [, DELFILE] -- deletes .DAT file too
    23. Resetting a user's password

        sp_password 'myusername', 'my+stro'
        

30. THE END