Monthly Archives: August 2008

The truth about Project Lifecycles

This has been around for a while, and it is still true 🙂

http://www.sellsbrothers.com/fun/#Project_Life_Cycle

Advertisements

MySQL EXPLAIN Cheat Sheet

Came across this handy cheat sheet for MySQL EXPLAIN:

http://www.beberlei.de/mysql_explain.html

EXPLAIN is a clause in MySQL that explains how a SELECT statement will be executed, and allows one to determine whether the indexing scheme is effective or not.

SQLCMD Examples

Here are some samples on how to use SQLCMD in SSMS.

   1: -- ==========================================================================
   2: -- Object       : SQLCMD-SSMS.sql
   3: -- Object Type  : Script
   4: -- Description  : Various examples
   5: -- Developer    : Donabel Santos
   6: -- Origin       : 2008/08/01
   7: -- Last Modified: 2008/08/05
   8: -- Notes        : 
   9: --               This needs to be run in SSMS in SQLCMD mode
  10: -- ==========================================================================
  11:
  12: -- SHORTCUTS
  13: -- F1 Help
  14: -- Ctrl+ Shift+M replace template parameters
  15:
  16: -- --------------------------------------------------------------------------
  17: -- How to use an output file to store query output
  18: -- --------------------------------------------------------------------------
  19: :out C:Tempsampleoutput.txt
  20:
  21: SET NOCOUNT ON
  22:
  23: SELECT
  24:     @@VERSION AS 'Server Version',
  25:     GETDATE() AS 'Current Date'
  26:
  27: SET NOCOUNT OFF
  28:
  29:
  30: -- --------------------------------------------------------------------------
  31: -- How to set a variable
  32: -- Note variables must be run in the same batch
  33: -- --------------------------------------------------------------------------
  34:
  35: -- sample 1
  36: :setvar dbname AdventureWorks
  37: :setvar col1 DepartmentID
  38: :setvar col2 Name
  39: :setvar tablename HumanResources.Department
  40:
  41: SELECT '$(dbname)' AS dbname
  42:
  43: -- use the database
  44: USE $(dbname)
  45: SELECT
  46:     DB_NAME() AS 'DB_NAME()',
  47:     DB_ID()   AS 'DB_ID()'
  48:
  49: -- select from the table
  50: SELECT
  51:     $(col1),
  52:     $(col2)
  53: FROM
  54:    $(tablename)
  55:
  56:
  57: -- sample 2
  58: -- here we are setting several environment variables
  59: :setvar workpath C:Temp
  60: :setvar outfile out.txt
  61: :setvar errfile err.txt
  62: :out      $(workpath)$(outfile)
  63: :Error    $(workpath)$(errfile)
  64:
  65: -- output of this will go to file specified in :out
  66: SELECT @@VERSIONS
  67:
  68:
  69: -- --------------------------------------------------------------------------
  70: -- How to execute a script file
  71: -- --------------------------------------------------------------------------
  72:
  73: -- sample 1
  74: -- connect to a different database
  75: -- make sure you are in SQLCMD mode
  76: USE test
  77: :r Y:DropTable.sql
  78: GO
  79: :r Y:CreateTable.sql
  80: GO
  81:
  82: SELECT
  83:     DB_NAME()    AS 'Database',
  84:     [name]        AS 'Table',
  85:     create_date    AS 'Created',
  86:     DATEDIFF(second,create_date, GETDATE()) AS 'Seconds Ago'
  87: FROM
  88:     sys.objects
  89: WHERE
  90:     type = 'U'
  91: ORDER BY
  92:     create_date DESC
  93:
  94: -- sample2
  95: :setvar dbname Test
  96: :setvar workpath Y:
  97: :setvar file1 DropTable.sql
  98: :setvar file2 CreateTable.sql
  99: :setvar errfile err.txt
 100:
 101: USE $(dbname)
 102: :r $(workpath)$(file1)
 103: :r $(workpath)$(file2)
 104:
 105: SELECT
 106:     DB_NAME()    AS 'Database',
 107:     [name]       AS 'Table',
 108:     create_date   AS 'Created',
 109:     DATEDIFF(second,create_date, GETDATE()) AS 'Seconds Ago'
 110: FROM
 111:     sys.objects
 112: WHERE
 113:     type = 'U'
 114: ORDER BY
 115:     create_date DESC