Microsoft SQL Server

TCRUD - Create, Read, Update, and Delete

1. Create 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

      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

   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. Delete 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

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
   

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()
    

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
      
      

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.
   

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

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