Copyright (c)  2007  Mike Chirico (

Permission is granted to copy, distribute and/or modify this document
under the terms of the GNU Free Documentation License, Version 1.2 or
any later version published by the Free Software Foundation.

Last Updated: Fri Dec 21 12:37:14 EST 2007

TIP 1:

 Need to know how much fragmentation?

      DBCC SHOWCONTIG (tbl_transaction)

 There is also a more advanced option


 There is also a defrag command

      DBCC INDEXDEFRAG(Northwind,Customers,1)

TIP 2:

 This is a cool feature:

     IF object_id('OutputTest') IS NOT NULL
        drop table OutputTest
     CREATE TABLE OutputTest
             ID int NOT NULL,
             Description varchar(max) NOT NULL,
         INSERT INTO OutputTest (ID, Description) VALUES (1, 'row 1')
         INSERT INTO OutputTest (ID, Description) VALUES (2, 'row 2')
         INSERT INTO OutputTest (ID, Description) VALUES (3, 'row 3')
         DELETE OutputTest
         OUTPUT DELETED.ID, DELETED.Description INTO @DeleteLog
         WHERE ID = 1
         SELECT *into DeleteLog FROM @DeleteLog
     select * from DeleteLog

TIP 3:

     Standard table create statement, with getdate() for automatically
     recording the time when a record was added.

       CREATE TABLE [junk23](
        [JunkID] [smallint] IDENTITY(1,1) NOT NULL,
        [Name] varchar(20) NOT NULL,
        [ModifiedDate] [datetime] NOT NULL   DEFAULT (getdate()))

TIP 4:

     create PROCEDURE Insert_Top as
     CREATE TABLE [dbo].[top](
        [SPID] [float] NULL,
        [Status] [nvarchar](255) NULL,
        [Login] [nvarchar](255) NULL,
        [HostName] [nvarchar](255) NULL,
        [BlkBy] [nvarchar](255) NULL,
        [DBName] [nvarchar](255) NULL,
        [Command] [nvarchar](255) NULL,
        [CPUTime] [float] NULL,
        [DiskIO] [float] NULL,
        [LastBatch] [nvarchar](255) NULL,
        [ProgramName] [nvarchar](255) NULL,
        [SPID1] [float] NULL,
        [REQUESTID] [float] NULL,
        [ModifiedDate] [datetime] NULL CONSTRAINT [DF_top_ModifiedDate]  DEFAULT (getdate())
     ) ON [PRIMARY]
     INSERT INTO [DPart_P008_Dev].[dbo].[top]
      EXEC sp_who2

  Now, create the loop procedure:

      create PROCEDURE Loop_Insert_Top as
      WHILE (1=1) BEGIN
          EXEC insert_top
          WAITFOR DELAY '00:01:00';

  To view the records:

     select * from [top]

TIP 5:

   sp_helptext Insert_Page7_into_Combined_sproc

   "All that really happens when you create a procedure is that its
    syntax is checked and its source code is inserted into the
    syscomments system table"

TIP 6:

    Getting a directory listing of the c:\ drive.

        -- To allow advanced options to be changed.
        EXEC sp_configure 'show advanced options', 1
        -- To update the currently configured value for advanced options.
        -- To enable the feature.
        EXEC sp_configure 'xp_cmdshell', 1
        -- To update the currently configured value for this feature.

        EXEC master..xp_cmdshell "dir c:\*"

TIP  7:

            'Name'          =,  
            'Owner'         = user_name(ObjectProperty( object_id, 'ownerid')),  
            'Object_type'   = substring(,5,31) 

        --into t_sp_help
        from sys.all_objects o, master.dbo.spt_values v  
        where o.type = substring(,1,2) collate database_default and v.type = 'O9T'  
        and substring(,5,31) ='stored procedure'
        and user_name(ObjectProperty( object_id, 'ownerid'))='dbo'
        order by [Owner] asc, Object_type desc, Name asc  

TIP 8:

   You may need indexes. Try running the following query to see if the
   value is above 5000.

        SELECT *
        (SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS index_advantage,
        migs.* FROM sys.dm_db_missing_index_group_stats migs) AS migs_adv
          INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs_adv.group_handle =
          INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle
        ORDER BY migs_adv.index_advantage

TIP 9:

    Need to see what users are doing?

         select * from sys.dm_exec_sessions

TIP 10:

    Need the last day of the month?

             dateadd(mm,1,convert(datetime,convert(char(10),getdate(),110)) - 

TIP 11:

    Put last day of month into a user defined function.

        create FUNCTION dbo.GetLastDayOfMonth()
        RETURNS datetime
          DECLARE  @OutDate datetime
            set @OutDate = (Select dateadd(mm,1,convert(datetime,convert(char(10),getdate(),110)) 
               - day(getdate())+1)-1)

    Now call it:

        select dbo.GetLastDayOfMonth()

TIP 12:

    Need a function that gets the date a year ago at the beginning of
    the month?

        create FUNCTION dbo.Get1YearAgo()
        RETURNS datetime
          DECLARE  @OutDate datetime
          set @OutDate = (Select dateadd(mm,-12,convert(datetime,convert(char(10),
                     getdate(),110)) - day(getdate())+1)

        select dbo.Get1YearAgo()

TIP 13:

    Need to get a list of all stored procedures?

        SELECT name
        FROM dbo.sysobjects
        WHERE type = 'P' AND category = 0

TIP 14:

     Need to get the median?  Also see TIP 15 below and TIP 19.

         SELECT Median=AVG(c1) FROM (
           SELECT MIN(c1) AS c1 FROM (
           SELECT TOP 50 PERCENT col AS c1 FROM table
          ORDER BY c1 DESC) t
            UNION ALL
          SELECT MAX(c1) FROM (
          SELECT TOP 50 PERCENT col AS c1 FROM table
          ORDER BY c1) t
          ) M

    (Also reference:
     But note, you can probably use the sp_executesql statement as well.

     Here's a way to get the 75%.

        WITH T AS
          SELECT [Sls Rep],[Prod No],[UOM],[Unit Sales $],
              ROW_NUMBER() OVER(PARTITION BY [Sls Rep],[Prod No],[UOM] 
                 ORDER BY [Unit Sales $]) AS RowNum,
                  COUNT(*) OVER(PARTITION BY [Sls Rep],[Prod No],[UOM]) AS Cnt
                    FROM Pg7_all_trans
                    SELECT [Sls Rep],[Prod No],[UOM],Avg([Unit Sales $]) [Median Unit Sales $], 
                    Max(RowNum) [RowNum], Max(Cnt) [Cnt] into Pg7_75
                    FROM T
                    WHERE RowNum >= 0.75*(Cnt+1) and RowNum <= 0.75*(Cnt+2) 
                    GROUP BY [Sls Rep],[Prod No],[UOM]

TIP 15:

    sp_executesql -- Executing the contents of a string as a SQL statement.

         Declare @S nvarchar(500);
         set @S = 'select * from' + ' junk'
         execute sp_executesql @S

    But wait! You can do this in a sproc:

         create proc select_table(@a as nvarchar(50))
         Declare @t as nvarchar(500);
         set @t = 'select * from ' + @a;
         execute sp_executesql @t

    Now just run this as follows:

         select_table 'junk';

    Here's another example with parameters:

        EXECUTE sp_executesql 
          N'SELECT * FROM AdventureWorks.HumanResources.Employee 
          WHERE ManagerID = @level',
          N'@level tinyint',
          @level = 109;

    Here's an example with quotes:

        Declare @S nvarchar(max);
        set @S = '
        IF object_id(''junk'') IS NOT NULL
           DROP TABLE junk
        create table
        junk (a int,
        b varchar(20));
        insert into junk (a,b) values (1,''one'');
        insert into junk (a,b) values (2,''two'');
        select * from junk;
        -- print @S
        execute sp_executesql @S

     (Also reference:

TIP 16:

    More sp_executesql -- 
        SET @sql = N'';
        SET @i = 1;
        WHILE @i <= 100
          SET @sql = @sql + N'PRINT ' + CAST(@i AS NVARCHAR(10))
           + NCHAR(13) + NCHAR(10);
          SET @i = @i + 1;
        EXEC sp_executesql @sql;

TIP 17:

    Simple example of a union statement:

        select  *  into t1_t2_combined from 
        (select * from t1
          select * from t2) as a

TIP 18:

    Need to find SQL version?



        SELECT  SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), 
              SERVERPROPERTY ('edition')


TIP 19:

    Example calculating Median on the table Median_table

                SET ANSI_NULLS ON
                alter FUNCTION [dbo].[calc_median_product_uom] (@product as nvarchar(30),
                @uom as nvarchar(4)
                -- Created:  Mike Chirico <>
                -- Updated: Thu Sep 27 16:30:20 EDT 2007
                -- Example Usage:
                -- select product,uom,DPart_P008.dbo.calc_median_product_uom(product,uom) Median from Median_table
                RETURNS money
                DECLARE  @Median as money
                SELECT @Median=AVG(c1) FROM (
                  SELECT MIN(c1) AS c1 
                   FROM (SELECT TOP 50 PERCENT AvqPrice AS c1 FROM Median_table 
                     where product=@product and uom=@uom ORDER BY c1 DESC
                ) as t
                UNION ALL
                select max(c1) as c1 from (
                  SELECT TOP 50 PERCENT AvqPrice AS c1 FROM 
                    Median_table where product=@product and uom=@uom ORDER BY c1
                ) as t
                ) M

TIP 20:

    Example listing all the indexes on table mytable.

           select table_name,
             from sys.tables a,
                  sys.indexes b,
                  sys.index_columns c,
                  sys.columns d
           where a.object_id = b.object_id
            and b.object_id = c.object_id
            and b.index_id  = c.index_id
            and c.object_id = d.object_id
            and c.column_id = d.column_id
            and      = 'mytable'

TIP 21:

    Need a comparison between Oracle 10g and SQL 2005? Take a look at
    the following:

TIP 22:

    Need to free up space?  The following query shows how much space
    can be freed.

           SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
           FROM sys.database_files;

    To free up space from the database 'mydatabase' use the following

           DBCC SHRINKDATABASE('mydatabase',0);

    To free up sapce from the logfile 'mylogfile' use the following command:

           DBCC SHRINKFILE('mylogfile',0);

TIP 23:

    Getting the Sum, Max across rows in a table.

       Assume a simple table defined as follows:

       create table junk
       (a int,
        b int,
        c int);

       insert into junk (a,b,c) values (1,4,3);
       insert into junk (a,b,c) values (2,5,9);
       insert into junk (a,b,c) values (3,8,2);

        select *, (select sum(my_columns)
        from (select a as my_columns
           union all
              select b 
           union all
             select c) as t) as sum_column
       from junk

Tip 24:

    Delete all tables with the name delete in them.    

        -- =============================================
        -- Author:              <Mike Chirico>
        -- Create date: <Thu Nov  1 08:34:41 EDT 2007>
        -- Description: <This proc will go through and delete
        --              all tables with the name delete in them.>
        -- =============================================
        alter PROCEDURE free_space 

                -- SET NOCOUNT ON added to prevent extra result sets from
                -- interfering with SELECT statements.
                SET NOCOUNT ON;

                DECLARE cursor_value CURSOR
                                select name from sys.tables where name like '%delete%'
                FOR READ ONLY

                DECLARE @cursor_row_value varchar(120)
                DECLARE @S nvarchar(500);
                OPEN cursor_value
                FETCH cursor_value INTO @cursor_row_value
                WHILE (@@FETCH_STATUS=0) BEGIN
                           set @S = 'drop table ['+@cursor_row_value+']'
                           execute sp_executesql @S
                           FETCH cursor_value INTO @cursor_row_value
                CLOSE cursor_value
                DEALLOCATE cursor_value


TIP 25:

    What is the difference between UNION and UNION ALL?  UNION only returns the distinct
    number of rows and is slower than UNION ALL.

TIP 26:

    What is the difference between NVARCHAR, VARCHAR and CHAR?
    NVARCHAR takes two bytes, VARCHAR takes 1 byte, and CHAR is fixed

TIP 27:

    Order can make a difference when doing updates

       IF object_id('tempdb..#t1') IS NOT NULL
          DROP TABLE #t1
       IF object_id('tempdb..#t2') IS NOT NULL
          DROP TABLE #t2
       IF object_id('tempdb..#t3') IS NOT NULL
          DROP TABLE #t3
       create table #t1 (a int, b int);
       insert into #t1 (a,b) values (1,2);
       insert into #t1 (a,b) values (1,5);
       create table #t2 (a int ,b int, c int, d int, [order] int);
       insert into #t2 (a,b,c,d,[order]) values (1,2,10,10,0);
       insert into #t2 (a,b,c,d,[order]) values (1,2,50,60,1);

       -- Change order here:
       --select * into #t3 from #t2 order by [order] desc
       select * into #t3 from #t2 order by [order] asc
       update #t1 
       set #t1.a=#t3.c, #t1.b=#t3.d
       from #t1 ,#t3
       where #t1.a=#t3.a and #t1.b=#t3.b 
       select * from #t1

TIP 28:

    Need a listing of all stored procedures on the system that you

       SELECT * -- Stored Procs
       FROM sys.objects
       WHERE objectproperty( object_id, N'IsMSShipped' ) = 0
       AND objectproperty( object_id, N'IsProcedure' ) = 1

    Below are some other common methods. But take a look at the
    following below and note the condition where N'IsMSShipped' is
    false, or equal to zero.

       SELECT * -- Functions
       FROM sys.objects
       WHERE objectproperty( object_id, N'IsMSShipped' ) = 0
       AND ( objectproperty( object_id, N'IsTableFunction' ) = 1
       OR objectproperty( object_id, N'IsScalarFunction' ) = 1 )

       SELECT * -- User tables
       FROM sys.objects
       WHERE objectproperty( object_id, N'IsMSShipped' ) = 0
       AND objectproperty( object_id, N'IsTable' ) = 1

       SELECT * -- Views
       FROM sys.objects
       WHERE objectproperty( object_id, N'IsMSShipped' ) = 0
       AND objectproperty( object_id, N'IsView' ) = 1


TIP 29:

    Combining two tables into 1 pivot table

       create table p1 (a int, b int)
       create table p2 (a int, b int)
       delete from p1;
       delete from p2;
       insert into p1 (a,b) values (1,34);
       insert into p1 (a,b) values (2,12);
       insert into p1 (a,b) values (3,22);
       insert into p2 (a,b) values (1,500);
       insert into p2 (a,b) values (2,512);
       insert into p2 (a,b) values (4,600);
       select g.a,g.b,g.c 
       	select p1.a, p1.b,p2.b as c from p1, p2
       	where p1.a=p2.a
       union all
             select p1.a,p1.b,null as c from p1 left outer join
              p2 on p1.a=p2.a
              where p2.a is null
       union all
             select p2.a,null as b,p2.b as c from p2 left outer join 
              p1 on p2.a=p1.a
              where p1.a is null
       ) as g
       /* Results:
       a  b	c

       1  34	500
       2  12	512
       3  22	NULL
       4  NULL	600



Linux Quota Tutorial This tutorial walks you through implementing disk quotas for both users and groups on Linux, using a virtual filesystem, which is a filesystem created from a disk file. Since quotas work on a per-filesystem basis, this is a way to implement quotas on a sub-section, or even multiple subsections of your drive, without reformatting. This tutorial also covers quotactl, or quota's C interface, by way of an example program that can store disk usage in a SQLite database for monitoring data usage over time.

Gmail on Home Linux Box using Postfix and Fetchmail If you have a Google Gmail account, you can relay mail from your home linux system. It's a good exercise in configuring Postfix with TLS and SASL. Plus, you will learn how to bring down the mail safely, using fetchmail with the "sslcertck" option.

Breaking Firewalls with OpenSSH and PuTTY If the system administrator deliberately filters out all traffic except port 22 (ssh), to a single server, it is very likely that you can still gain access other computers behind the firewall. This article shows how remote Linux and Windows users can gain access to firewalled samba, mail, and http servers. In essence, it shows how openSSH and Putty can be used as a VPN solution for your home or workplace.

Create your own custom Live Linux CD These steps will show you how to create a functioning Linux system, with the latest 2.6 kernel compiled from source, and how to integrate the BusyBox utilities including the installation of DHCP. Plus, how to compile in the OpenSSH package on this CD based system. On system boot-up a filesystem will be created and the contents from the CD will be uncompressed and completely loaded into RAM -- the CD could be removed at this point for boot-up on a second computer. The remaining functioning system will have full ssh capabilities. You can take over any PC assuming, of course, you have configured the kernel with the appropriate drivers and the PC can boot from a CD.

SQLite Tutorial This article explores the power and simplicity of sqlite3, first by starting with common commands and triggers, then the attach statement with the union operation is introduced in a way that allows multiple tables, in separate databases, to be combined as one virtual table, without the overhead of copying or moving data. Next, the simple sign function and the amazingly powerful trick of using this function in SQL select statements to solve complex queries with a single pass through the data is demonstrated, after making a brief mathematical case for how the sign function defines the absolute value and IF conditions.

Lemon Parser Tutorial Lemon is a compact, thread safe, well-tested parser generator written by D. Richard Hipp. Using a parser generator, along with a scanner like flex, can be advantageous because there is less code to write. You just write the grammar for the parser. This article is an introduction to the Lemon Parser, complete with examples.

Chirico img Mike Chirico, a father of triplets (all girls) lives outside of Philadelphia, PA, USA. He has worked with Linux since 1996, has a Masters in Computer Science and Mathematics from Villanova University, and has worked in computer-related jobs from Wall Street to the University of Pennsylvania. His hero is Paul Erdos, a brilliant number theorist who was known for his open collaboration with others.

Mike's notes page is souptonuts. For open source consulting needs, please send an email to All consulting work must include a donation to Logo