SQL Server Tips & Tricks: October 2008

Here are some useful tips for sql server. These tips are taken
from the following link

http://souptonuts.sourceforge.net/mssql_tutorial_tips.htm


TIP 1:

Need to know how much fragmentation?

    DBCC SHOWCONTIG (tbl_transaction)

There is also a more advanced option

    DBCC SHOWCONTIG (tbl_transaction) WITH TABLERESULTS, ALL_LEVELS

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
   BEGIN
      drop table OutputTest
   END
 
 
   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')
 
   DECLARE @DeleteLog AS TABLE (LogID INT, LogEntry VARCHAR(MAX))
 
       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]
             (
      [SPID],
      [Status],
      [Login],
      [HostName],
      [BlkBy],
      [DBName],
      [Command],
      [CPUTime],
      [DiskIO],
      [LastBatch],
      [ProgramName],
      [SPID1],
      [REQUESTID])
    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';
    END

To view the records:

   select * from [top]




TIP 5:

 sp_help
 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
      GO
      -- To update the currently configured value for advanced options.
      RECONFIGURE
      GO
      -- To enable the feature.
      EXEC sp_configure 'xp_cmdshell', 1
      GO
      -- To update the currently configured value for this feature.
      RECONFIGURE
      GO

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



TIP  7:

      select
          'Name'          = o.name,
          'Owner'         = user_name(ObjectProperty( object_id, 'ownerid')),
          'Object_type'   = substring(v.name,5,31)

      --into t_sp_help
      from sys.all_objects o, master.dbo.spt_values v
      where o.type = substring(v.name,1,2) collate database_default and v.type = 'O9T'
      and substring(v.name,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 *
      FROM
      (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 =
         mig.index_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?


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




TIP 11:

  Put last day of month into a user defined function.


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

  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
      AS
      BEGIN
        DECLARE  @OutDate datetime
        set @OutDate = (Select dateadd(mm,-12,convert(datetime,convert(char(10),
                   getdate(),110)) - day(getdate())+1)
      )
        RETURN(@OutDate)
      END

      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: http://www.sqlmag.com/Article/ArticleID/49827/49827.html)
   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))         as         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        BEGIN           DROP TABLE junk        END               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: http://msdn2.microsoft.com/en-us/library/ms188001.aspx)    TIP 16:     More sp_executesql --            DECLARE @sql AS NVARCHAR(MAX), @i AS INT;        SET @sql = N'';        SET @i = 1;        WHILE @i <= 100        BEGIN          SET @sql = @sql + N'PRINT ' + CAST(@i AS NVARCHAR(10))           + NCHAR(13) + NCHAR(10);          SET @i = @i + 1;        END        EXEC sp_executesql @sql;    TIP 17:     Simple example of a union statement:         select  *  into t1_t2_combined from        (select * from t1             union          select * from t2) as a    TIP 18:     Need to find SQL version?         SELECT @@VERSION     or                SELECT  SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'),              SERVERPROPERTY ('edition')        Reference:         http://support.microsoft.com/kb/321185    TIP 19:     Example calculating Median on the table Median_table                                GO                SET ANSI_NULLS ON                GO                SET QUOTED_IDENTIFIER ON                GO                               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
              AS
              BEGIN
            
              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
            
            
                RETURN(@Median)
              END




TIP 20:

  Example listing all the indexes on table mytable.

         select a.name table_name,
                b.name index_name,
                d.name column_name,
                c.index_column_id
           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 a.name      = 'mytable'



TIP 21:

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

      http://www.wisdomforce.com/dweb/resources/docs/MSSQL2005_ORACLE10g_compare.pdf



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
  command:

         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.  

      SET ANSI_NULLS ON
      GO
      SET QUOTED_IDENTIFIER ON
      GO
      -- =============================================
      -- Author:              
      -- Create date: 
      -- Description: 
      -- =============================================
      alter PROCEDURE free_space

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

              DECLARE cursor_value CURSOR
              FOR
                              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
              END
              CLOSE cursor_value
              DEALLOCATE cursor_value

     END
     GO




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


TIP 27:

  Order can make a difference when doing updates

   
     IF object_id('tempdb..#t1') IS NOT NULL
     BEGIN
        DROP TABLE #t1
     END
     IF object_id('tempdb..#t2') IS NOT NULL
     BEGIN
        DROP TABLE #t2
     END
     IF object_id('tempdb..#t3') IS NOT NULL
     BEGIN
        DROP TABLE #t3
     END
   
   
   
     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
  created?


     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


   Reference:
       http://msdn2.microsoft.com/en-us/library/ms176105.aspx
       http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/06/29/850.aspx



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

     */
SQL Server Tips & Tricks

Monday, October 6, 2008

Sunday, October 5, 2008
