SQL Examples

SQL Examples with Microsoft SQL Server

Here's my notes on using Sql Server for a decade.

  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. Add a column with check for existence first

        IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
           WHERE TABLE_NAME = 'MyTableName' AND COLUMN_NAME = 'newcolumn')
        BEGIN
           ALTER TABLE MyTableName ADD newcolumn varchar(32) NOT NULL DEFAULT ''
        END
        
      6. How to delete, or drop, a column:

        Always a good idea to test if the column is there if you are running from a script

        IF EXISTS (SELECT * FROM dbo.syscolumns 
            WHERE id = object_id(N'[dbo].[Projects]')
            AND name = 'ProjectManager')
               ALTER TABLE Projects DROP COLUMN [ProjectManager]
        
      7. 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. How to format a number with two decimal places

        -- this will return a field 13 characters wide, 10 before the decimal point and two after
        select  CONVERT(DECIMAL(12,2),myField) from myTable
        -- if your number is larger than that you get this error:
        -- Msg 8115, Level 16, State 8, Line 1
        -- Arithmetic overflow error converting int to data type numeric.
        -- or you can use the STR function
        select str(myField, 12, 2) from myTable
        
      5. 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
        
    6. SQL Server Utilities [Note: use osql only if your version of Sql Server is earlier than 2005. Use "sqlcmd" for new versions. You are better off using Invoke-Sqlcmd inside PowerShell.]
      1. osql - the command line batch interface to SQL Server before 2005
        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. In "sqlcmd" you can use the "-E" option and use your current identity.

      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 get a list of all databases on a server

        SELECT name FROM master..sysdatabases
        -- or 
        EXEC sp_databases
        
      8. To grant a user permission to a database:
             USE mydatabase
             EXECUTE sp_adduser newloginid
        
      9. To grant a user permission to a database table:
        GRANT [EXECUTE|ALL] ON TableName TO myloginid
        
      10. Note: If a stored procedure is not the first item in a series of SQL statements, it must be prefaced with 'EXECUTE'.
      11. To unload a dll: dbcc xp_netq (FREE)
      12. To have SQLServer run a simple diagnostic on a db named mydb:
        dbcc checkdb(mydb) 
        

        also try dbcc checkalloc(db4)

      13. To get the name of the current database
        SELECT db_name()
        
      14. to show the names of all databases
        USE master
        SELECT name FROM sysdatabases
        
      15. 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 used in the LIKE clause:
        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)+'%'
        
        To find fields containing an '_', which is tricky since '_' is usually the "any character" flag.
        SELECT labelInfo FROM mytable WHERE labelInfo like '%[_]%'
        
        
      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. 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. How to drop a stored procedure
        IF EXISTS ( SELECT name FROM sysobjects WHERE type = 'P' AND name = 'addadult' )
           DROP PROCEDURE addadult
        

        or you can do this:

        IF OBJECTPROPERTY(object_id('dbo.YourStoredProcName'), N'IsProcedure') = 1
           DROP PROCEDURE [dbo].[YourStoredProcName]
        GO
        
        
      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. How to write a FUNCTION

      Functions are more flexible to use than stored procedures. Here's a simple function that returns an integer.

      IF OBJECT_ID(N'NumberProcessedInTimeSpan', N'FN') IS NOT NULL 
         DROP function [dbo].[NumberProcessedInTimeSpan]
      GO
      CREATE function NumberProcessedInTimeSpan (@startDate DateTime, @endDate DateTime ) 
      returns int 
      AS 
      BEGIN
      RETURN (select COUNT(*)
         from JobMetrics WITH(NOLOCK)
            where
            ((StartTime < @endDate) and (StartTime > DATEADD(HOUR,-3,@startDate)))
             )
      END
      GO
      -- test case
      DECLARE @q int
      exec @q = NumberProcessedInTimeSpan '2014-11-11 22:01:00',  '2014-11-11 22:11:00'
      print @q
      
    10. 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. Delete rows from a table in small batches like slicing a salami.

          Using the WHILE statement this stored procedure deletes records older than a specific number of days. Deleting all the rows can cause the database to freeze or the query can timeout.

          -- trims table to only keep specific number of previous days in table which must have a datetime field named 'dtime'
          -- example:  EXEC sd_trimTable 'practice', 50
          --           this would remove all rows older than 50 days in slices of 1000
          IF OBJECTPROPERTY(object_id('dbo.sd_trimTable'), N'IsProcedure') = 1
             DROP PROCEDURE dbo.sd_trimTable
          GO
          CREATE PROCEDURE sd_trimTable @tableName char(128), @trimDays int
          AS
             DECLARE @trimDate datetime
             DECLARE @cmd char(240)
             DECLARE @counter int
             SET @trimDate = DATEADD(day,-@trimDays,GETDATE())
             SET @counter = 1
             SET @cmd = 'DELETE TOP (1000) FROM '+ (@tableName)+'  WHERE dtime <  ''' + CONVERT(VARCHAR(10), @trimDate, 111) + ''''
             SELECT @cmd
             WHILE 1 = 1
                BEGIN
                  EXEC (@cmd)
                  IF @@ROWCOUNT = 0
                    BREAK
                  WAITFOR DELAY '00:00:00.2' --give other processes time to access table
              END
          GO
          
        4. 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
          
        5. 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. How to prevent division by zero errors

          Use the NULLIF function. If @total is zero, NULL is returned for the division operation.

            SELECT @mydate as 'Date Time',@total AS 'total', @completes AS 'Number of completes', 
                  @completes*100/NULLIF(@total,0)
          

          You can also set Sql Server to just report divide by zero problems as 0

          SET ARITHABORT OFF 
          SET ANSI_WARNINGS OFF
          
        3. To see which programs are connecting to your sql server instance.

          SELECT program_name, count(*)
          FROM
          Master..sysprocesses
          WHERE ecid=0
          GROUP BY program_name
          ORDER BY count(*) desc
          
        4. To count how many processes are blocked

          SELECT count(*) from master..sysprocesses 
          WHERE blocked <> 0 
          
        5. To find processes blocked for more than 6 minutes (360000 millisecs)

          SELECT * from master..sysprocesses WHERE blocked <> 0 and waittime > 360000
          
        6. To get information on which jobs are blocked and by whom

          SELECT 
              p1.SPID AS blockedSPID, p2.SPID AS blockingSPID
          ,p1.kpid, p1.blocked, db_name(p1.dbid) AS 'db_name', p1.status,
          p1.waittime, p1.lastwaittype,p1.cpu, p1.physical_io, p1.memusage,
          p1.login_time, p1.last_batch
          FROM master..sysprocesses p1
              JOIN
              master..sysprocesses p2 ON p1.blocked = p2.spid
          
        7. How to trim down the size of log files in MSSQL 2008

          This is dangerous for production machines, but for development boxes where you really don't care about recovery this should reduce the log size.

          ALTER DATABASE [MyDatabaseName]  SET RECOVERY SIMPLE
          go
          BACKUP log [MyDatabaseName] WITH TRUNCATE_ONLY
          go
          DBCC SHRINKDATABASE ([MyDatabaseName], 10, TRUNCATEONLY)
          go
          
          
        8. How to see all collations in a database
          SELECT TABLE_NAME, COLUMN_NAME,DATA_TYPE,COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS
          WHERE COLLATION_NAME IS NOT null
          
        9. How to write the SQL to change all collation sequences

          from Paul Deen, http://members.chello.nl/p.deen7/knowledgebase/sql_change_collation.htm

          SELECT 'Alter Table [' + table_catalog + '].[' + table_schema + '].[' + table_name + '] alter column [' +  column_name + '] ' + case data_type when 'char' then 'char' when 'varchar' then 'varchar' when 'text' then 'text' end + case when data_type <>'text' then '(' + convert(varchar,character_maximum_length) + ')' else '' end + ' collate SQL_Latin1_General_CP1_CI_AS ' + case when is_nullable='YES' then 'NULL ' else 'NOT NULL' end
          FROM INFORMATION_SCHEMA.COLUMNS
          WHERE collation_name <> 'SQL_Latin1_General_CP1_CI_AS'
          AND data_type in ('char', 'varchar', 'text')
          AND table_name in (select table_name from INFORMATION_SCHEMA.tables where table_type = 'BASE TABLE')
          
          
        10. How To Obtain The Size Of All Tables In A SQL Server Database

          From http://therightstuff.de/CommentView,guid,df930155-f60f-4f56-ab33-f1352ff091a1.aspx

          SET NOCOUNT ON 
          DBCC UPDATEUSAGE(0) 
          -- DB size.
          EXEC sp_spaceused
          -- Table row counts and sizes.
          CREATE TABLE #t 
          ( 
              [name] NVARCHAR(128),
              [rows] CHAR(11),
              reserved VARCHAR(18), 
              data VARCHAR(18), 
              index_size VARCHAR(18),
              unused VARCHAR(18)
          ) 
          INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?''' 
          SELECT *
          FROM   #t
          -- # of rows.
          SELECT SUM(CAST([rows] AS int)) AS [rows]
          FROM   #t
          DROP TABLE #t 
          
        11. Print the current time immediately in seconds

          we use RAISERROR since it's supposably not buffered

          DECLARE @str char(240)
          SELECT @str = convert(varchar(19), getdate(), 121)
          RAISERROR('Trimming 100,000 rows at %s', 0, 1, @str) WITH NOWAIT
          
        12. sp_tables
          sp_tables -- to show a list of tables in a database:
          -- or
          SELECT name FROM sysobjects WHERE type = 'u' -- to see the user tables only
          
      3. Change the size of varchar column

        You may need to add the 'NOT NULL' option if your column is an indexed column

        ALTER TABLE MyTableName ALTER COLUMN MyColumnName varchar(32)
        
      4. Show all constraints of type DEFAULT_CONSTRAINT

        SELECT * FROM sys.objects
        WHERE type_desc LIKE '%CONSTRAINT'
        AND type_desc = 'DEFAULT_CONSTRAINT'
        
      5. Read more than 256 characters from an ntext field

        OK, there's the right way to do this with pointer and READTEXT, but you can cheat and use the SUBSTRING command to read from an ntext field

        SELECT TOP 1000
               SUBSTRING([TextData],1,255)
              ,SUBSTRING([TextData],256,400)
              ,[Duration]/1000 as durationMs
          FROM [zzzzz].[dbo].[SDWhistory_1103]
          where cpu is not NULL and textdata is not null and cpu > 0
          order by duration desc
        
      6. Modifying column properties with constraints

        Sometimes you can't modify a column if it has a constraint. First you must drop the constraint, then modify the column, then re-add the constraint.

        IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[SampleSources]') AND name = N'PK_SampleSources')
           ALTER TABLE [dbo].[SampleSources] DROP CONSTRAINT [PK_SampleSources]
        alter table SampleSources Alter Column name varchar(65)  NOT NULL
        /****** Object:  Index [PK_SampleSources]    Script Date: 08/06/2010 09:11:22 ******/
        ALTER TABLE [dbo].[SampleSources] ADD  CONSTRAINT [PK_SampleSources] PRIMARY KEY CLUSTERED 
        (
        	[name] ASC
        )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
        
      7. 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
        
      8. 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
        
        
      9. Loop through all the tables in a database and print the number of rows

        set nocount on
        declare @tablename varchar(90)
        declare @mycount varchar(90)
        SELECT @mycount = ' '
        SELECT @tablename = ' '
        while @tablename IS NOT NULL
         BEGIN
         SELECT @tablename = MIN(name) FROM sysobjects WHERE type = 'u' and name > @tablename
         IF @tablename IS NOT null
             BEGIN
                   SELECT @mycount = 'select '''+@tablename+''', count(*) from ' + @tablename
                   execute (@mycount)
              END
         END
        
        
      10. 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
        
      11. RAISERROR

        This writes a message to the console and 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
        
      12. 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
        
      13. To make the log file less verbose

        ALTER DATABASE MyDatabase SET RECOVERY SIMPLE
        
      14. PRINT
              PRINT 'this is a print'
              PRINT 'SELECT sorta does the same thing, but not quite, sometimes you need a PRINT'
        

        The PRINT statement is buffered. To get your output immediately (unfortunately there's no FLUSH command) you can use RAISERROR instead.

                set @msg = convert(char(25),GETDATE()) + ': my cool message'
                RAISERROR ( @msg,0,1) WITH NOWAIT
        
      15. 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
        
      16. 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 
      
      

      How to sum counts per day into a total. Sometimes you have a field you want to find the total for a day, like how many invoices did we send each day of this last year. Here's and example of getting a count for every day of the year

      SELECT datepart(year,dtime) AS 'year',datepart(month,dtime) AS 'month',datepart(day,dtime) AS 'day', count(*) AS 'count' FROM sdRespondents
      GROUP BY datepart(year,dtime),datepart(month,dtime),datepart(day,dtime)
      ORDER BY datepart(year,dtime),datepart(month,dtime),datepart(day,dtime)
      
    3. How to get 5 or 10 minute intervals

      declare @minutes int 
      set @minutes = 5
      select GETUTCDATE(), 
         DATEPART( hour, dtime) as hour, 
         (DATEPART(minute,dtime) / @minutes) * @minutes as '5 min interval', 
      machineName,COUNT(machineName) as employees from employees with(nolock)
      where dtime > dateadd(day,-1,getutcdate())
      group by  machineName,DATEPART( hour, dtime),(DATEPART(minute,dtime) / @minutes)
      order by DATEPART( hour, dtime), (DATEPART(minute,dtime) / @minutes),machineName
      
    4. How to fill leading zeros

      This gives just one date column, so it can be graphed easier

      SELECT CONVERT(char(4),datepart(year,dtime))+'-'+ 
             RIGHT('00'+CONVERT(varchar,datepart(month,dtime)),2)+'-'+
             RIGHT('00'+CONVERT(varchar,datepart(day,dtime)),2) AS 'date', count(*) AS 'count' 
        FROM myTable WITH(NOLOCK)
        WHERE dtime > DATEADD(day,-365,GETUTCDATE())
      GROUP BY datepart(year,dtime),datepart(month,dtime),datepart(day,dtime)
      ORDER BY datepart(year,dtime),datepart(month,dtime),datepart(day,dtime)
      

      Produces:

      date             count
      ---------- -----------
      2011-12-29          76
      2011-12-30         277
      2011-12-31         121
      ...
      
    5. How to import a CSV file into a SQL table?

      BULK INSERT MyTableName FROM 'c:\MyCsvFile.txt'
      WITH
      (
      FIELDTERMINATOR = ',',
      ROWTERMINATOR = '\n'
      )
      GO
      
      
  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. JOIN on NULL

    SQL Server considers a NULL to be ambiguous, so one NULL is not the same as another NULL. You can force a JOIN if you know the NULLs should match by using something like "(c.color = p.color OR (c.color IS NULL AND p.color IS NULL))".

    CREATE TABLE colors (
      team      varchar(16) NOT NULL,
      color     varchar(32) NULL,
    )
    CREATE TABLE points (
      color     varchar(32) NULL,
      points    int
    )
    
    INSERT INTO colors VALUES ('lobsters','red')
    INSERT INTO colors VALUES ('swans','white')
    INSERT INTO colors VALUES ('jellyfish',NULL)
    
    INSERT INTO points VALUES ('red',100)
    INSERT INTO points VALUES ('white',90)
    INSERT INTO points VALUES (NULL,80)
    SELECT * FROM colors c JOIN points p ON c.color = p.color
    -- returns:
    --lobsters         red                              red                              100
    --swans            white                            white                            90
    SELECT * FROM colors c JOIN points p ON (c.color = p.color OR (c.color IS NULL AND p.color IS NULL))
    -- returns:
    --lobsters         red                              red                              100
    --swans            white                            white                            90
    --jellyfish        NULL                             NULL                             80
    
  10. 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)
      
  11. 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
      
  12. 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
    
  13. 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
    
  14. 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
    
    
    
  15. Comments
    Comments are surrounded with '/*' and '*/', but cannot, for some
    bizzare reason contain a 'GO' command.
    
    Everything after a '--' is a comment
    
  16. 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
    
  17. 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
    
  18. How to specify a cascading deletion

    When the source of the foreign key is deleted, this row will also be deleted automagically if you append "ON DELETE CASCADE". This is called a Cascade Delete

    ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [FK_MyTable_name__MainTable_name] 
       FOREIGN KEY ([name]) REFERENCES [MainTable] (name) ON DELETE CASCADE
    
  19. 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)
    
  20. 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 Version of SQLServer do I have and what Service Packs does my SQL Server have installed?
    try
    SELECT @@version
                         
    

    CREATE VIEW with JOIN

    create view summary
      as
      select RuleId, Stakeholder, ProductType, Language, LanguageCode, MeasureName, Assessment, AcceptanceThreshold 
         from Rules r 
         join Measures m on r.MeasureID = m.MeasureId
         join Types t on t.TypeID = r.TypeID
         join Languages l on l.TableSuffix = t.Language
    
  21. 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
      -- another way
        IF OBJECTPROPERTY(object_id('dbo.stuff'), N'IsView') = 1
           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
      */
      
  22. How to drop a foreign key constraint

    ALTER TABLE [UserGroupResolutions] DROP CONSTRAINT FK_UserGroupResolutions_groupName_Groups_name
    
  23. 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" or "WITH(NOLOCK)", which does not lock a table during a SELECT and allows "dirty" reads. This is dangerous, but in certain situations can be advantageous.

    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
  24. 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
    
  25. 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
    
  26. 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
    
    
  27. 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
    
  28. 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
    -->
    
  29. 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 useful 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)
    
  30. Convert a database from local time to utc time.

    First we find all constraints defining the default value for the column to be "GETDATE()". Then we drop those constraints, and add the default constraint "GETUTCDATE()" instead. Secondly we go through all the columns and adjust their time from local to UTC.

    /* Converts all fields with a default value containing "GETDATE()" to "GETUTCDATE()" and updates existing times to utc */
    set nocount on
    declare @tableName varchar(255)
    declare @columnName varchar(255)
    declare @constraintName varchar(255)
    declare @newConstraintName varchar(255)
    declare @myDropCmd varchar(512)
    declare @myCreateCmd varchar(512)
    declare @timeZoneCommand varchar(512)
    declare @timeZoneOffset int
    
    select @timeZoneOffset = Datediff(hh,GETDATE(),GETUTCDATE())
    
    -- original base query from 'MeanOldDBA' at 
    -- http://www.developmentnow.com/g/113_2004_9_0_0_433030/Search-for-defaults-in-table-DDL.htm
    DECLARE mycursor CURSOR FOR 
       SELECT
       so1.name AS table_name,
       sc1.name AS column_name,
       so2.name AS default_name
       FROM
       sysobjects so1
       INNER JOIN syscolumns sc1 ON so1.id = sc1.id
       INNER JOIN sysobjects so2 ON sc1.cdefault = so2.id
       INNER JOIN syscomments sc2 ON so2.id = sc2.id
       WHERE
       sc2.text LIKE '%getdate()%'
    OPEN mycursor
       FETCH NEXT FROM mycursor INTO @tableName, @columnName, @constraintName
       WHILE (@@fetch_status = 0)      -- while OK
         BEGIN
            print '-- Table: ' + @tableName + ', Constraint: ' + @constraintName+ ', Column: ' + @columnName
            SELECT @myDropCmd = '  ALTER TABLE ['+@tableName+'] DROP CONSTRAINT ['+@constraintName+']'
            PRINT @myDropCmd
            EXECUTE (@myDropCmd)
            SELECT @newConstraintName = 'DF_'+@tableName+'_dtime_utc'
            SELECT @myCreateCmd = '  ALTER TABLE ['+@tableName+'] ADD CONSTRAINT ['+@newConstraintName+'] DEFAULT (GETUTCDATE()) FOR ['+@columnName+']'
            PRINT @myCreateCmd 
            EXECUTE (@myCreateCmd)
            select @timeZoneCommand =  '  UPDATE ' + @tableName + ' SET ' + @columnName + ' = DATEADD(HOUR, '+convert(varchar(3),@timeZoneOffset)+', '+@columnName+')'
    		PRINT @timeZoneCommand
    		EXECUTE (@timeZoneCommand)
    
            FETCH NEXT FROM mycursor INTO @tableName, @columnName, @constraintName
         END
    CLOSE mycursor
    DEALLOCATE mycursor
    GO
    
  31. 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. How to convert a varchar column to nvarchar?

      You can convert a column in place.

      ALTER TABLE MyTableName ALTER COLUMN [MyColumnName] NVARCHAR(3000)
      
    3. 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;
      
    4. 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
      
    5. 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
      
    6. 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
    7. the EXECUTE command can be used to build commands from parts
    8. Example 1
      create proc displaytable
      @tbl varchar(30)
      as
      EXECUTE ('SELECT * FROM ' + @tbl)
      
    9. Example 2
      SELECT @cmd = "create database "+@projabbrev+" ON "+@datadevice+"  = 6 LOG ON "+@logdevice+" = 6"
      SELECT @cmd
      EXECUTE (@cmd)
      
    10. 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

    11. 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) 
      
      
    12. 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
      
      
    13. Get all the column names in a table
      SELECT name FROM syscolumns WHERE id in (SELECT id FROM sysobjects
      where name = 'mycolumn')
      
    14. Easy way is to access meta data is the 'information_schema' view:
      SELECT * FROM information_schema.columns
      
    15. 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
      
    16. Restoring database in Sql Server 2008 from thumb drive on E:

      It's more concise

      RESTORE DATABASE [MY_DB_NAME] FROM  DISK = N'E:MY_DB_NAME.bak' 
        WITH  FILE = 1,  NOUNLOAD,  STATS = 10
      
    17. How to retrieve data as xml

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

      SELECT * FROM sdgroups FOR XML AUTO
      
    18. 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.

    19. 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
      
    20. 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
  32. 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
      
      
  33. 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. 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 
      
    4. 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 
      
    5. Example of declaring a date variable and using DATEADD

      DECLARE @mydate datetime
      SET @mydate = '2010-11-11 00:00:00.00'
      SELECT count(time) AS 'number', avg(time) AS 'time(ms)' FROM myTable 
          WITH (nolock)
          WHERE 
          dtime > @mydate AND dtime < DATEADD(day,1,@mydate)
      
    6. Example of printing the date with milliseconds (ISO8601)

      set @msg = convert(char(25),GETDATE(),126) -- msg = 2011-11-07T13:08:41.933
      
    7. 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)
      
      
    8. Get all the column names in a table
      SELECT name FROM syscolumns WHERE id in (SELECT id FROM sysobjects
      where name = 'mycolumn')
      
    9. Easy way is to access meta data is the 'information_schema' view:
      SELECT * FROM information_schema.columns
      
    10. 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
      
    11. 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;
      
    12. How to retrieve data as xml

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

      SELECT * FROM sdgroups FOR XML AUTO
      
    13. How to select fields that are only letters and numbers?

      Since SQL doesn't have a isAlphaOrNumber() function you can use this user function from our friends at simple-talk.com

      IF OBJECT_ID(N'IsAlnum') IS NOT NULL
         DROP FUNCTION IsAlnum
      GO
      CREATE FUNCTION dbo.[IsAlnum] (@string VARCHAR(MAX))  
      /*
      Select dbo.isalnum('how many times must I tell you')
      Select dbo.isalnum('345rtp')
      Select dbo.isalnum('co10?')
      */
      RETURNS INT
      AS BEGIN
            RETURN CASE WHEN PATINDEX('%[^a-zA-Z0-9]%', @string) > 0 THEN 0
                        ELSE 1
                   END
         END
      GO
        
      
      create table test  (Description varchar(255))
      insert into test (Description) VALUES ('imok')
      insert into test (Description) VALUES ('imok2')
      insert into test (Description) VALUES ('i''mNot ok')
      
      select * from test where dbo.IsAlnum(Description) = 1 /* selects only rows containing only letters and numbers */
      
    14. How to validate a sql statement before executing it?

      One option is to user FMTONLY, but it's rumored to cause false negatives.

      SET FMTONLY ON
      -- lets test the next statement
      select * from test2
      SET FMTONLY OFF
      
      Msg 208, Level 16, State 1, Line 5
      Invalid object name 'test2'.
      

      You can also use "SET PARSEONLY ON" and "SET PARSEONLY OFF" , but this just checks syntax, not if a tablename is misspelled.

    15. 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
      
    16. 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
    17. Resetting a user's password
      sp_password 'myusername', 'my+stro'
      
  34. THE END

    A SQL query walks into a bar, approaches two tables, and says, "Mind if I join you?"