SQL Server equivalent of Oracle’s ‘Create table x as select * from’

e.g.

SELECT * INTO Employees
FROM all_emp

… this creates Employees as a direct copy of ‘all_emp’

2011
01/12
CATEGORY
sql server generic
TAGS

Write comment

Oracle SQL Tuning

http://www.orafaq.com/tuningguide/

2010
08/13
CATEGORY
Oracle
Tuning
TAGS


Write comment

Oracle Indexes

Great article on Oracle Indexes

by ‘rleishman’ from: http://www.orafaq.com/node/1403

I’m not really an “early-adopter” of technology. Don’t get me wrong; I love it, I just don’t want to feed the addiction. When I do get a new piece of technology though, it’s like a fever; I can’t concentrate on anything until I’ve read the manual from cover to cover and found out everything it can do, every built-in gizmo, and every trashy piece of after-market merchandise that can be plugged into it.And I don’t think I’m alone here. Working in I.T., there’s no shortage of people who can peel off a half-hour litany on their new Blackberry/IPod/Notepad/Digital Watch within a day of purchase.

So why are databases different? I worked with Oracle databases for 5 years before I understood indexes – and it’s right there in the manual (Concepts manual, for those interested). I don’t mean a deep, spiritual, one-ness with indexes; I mean just a basic understanding of the mechanics of the things. I distinctly remember my first tuning methodology: “It’s running slow. I think I’ll index some of the columns and see if it improves.” I should have copyrighted it because I’ve seen it used so many times in the last 10 years, I could’ve made a fortune in commissions.

If you understand how indexes work, 99 times out of a 100 you don’t need the suck-it-and-see methodology because you know beforehand whether an index will help?

This is covered in the Oracle Concepts manual, of course, but here’s the Cliff Notes version.

First you need to understand a block. A block – or page for Microsoft boffins – is the smallest unit of disk that Oracle will read or write. All data in Oracle – tables, indexes, clusters – is stored in blocks. The block size is configurable for any given database but is usually one of 4Kb, 8Kb, 16Kb, or 32Kb. Rows in a table are usually much smaller than this, so many rows will generally fit into a single block. So you never read “just one row”; you will always read the entire block and ignore the rows you don’t need. Minimising this wastage is one of the fundamentals of Oracle Performance Tuning.

Oracle uses two different index architectures: b-Tree indexes and bitmap indexes. Cluster indexes, bitmap join indexes, function-based indexes, reverse key indexes and text indexes are all just variations on the two main types. b-Tree is the “normal” index, so we will come back to Bitmap indexes another time.

A b-Tree index is a data structure in the form of a tree – no surprises there – but it is a tree of database blocks, not rows. Imagine the leaf blocks of the index as the pages of a phone book.

  • Each page in the book (leaf block in the index) contains many entries, which consist of a name (indexed column value) and an address (ROWID) that tells you the physical location of the telephone (row in the table).
  • The names on each page are sorted, and the pages – when sorted correctly – contain a complete sorted list of every name and address

A sorted list in a phone book is fine for humans, beacuse we have mastered “the flick” – the ability to fan through the book looking for the page that will contain our target without reading the entire page. When we flick through the phone book, we are just reading the first name on each page, which is usually in a larger font in the page header. Oracle cannot read a single name (row) and ignore the reset of the page (block); it needs to read the entire block.

If we had no thumbs, we may find it convenient to create a separate ordered list containing the first name on each page of the phone book along with the page number. This is how the branch-blocks of an index work; a reduced list that contains the first row of each block plus the address of that block. In a large phone book, this reduced list containing one entry per page will still cover many pages, so the process is repeated, creating the next level up in the index, and so on until we are left with a single page: the root of the tree.

To find the name Gallileo in this b-Tree phone book, we:

  • Read page 1. This tells us that page 6 starts with Fermat and that page 7 starts with Hawking.
  • Read page 6. This tells us that page 350 starts with Fyshe and that page 351 starts with Garibaldi.
  • Read page 350, which is a leaf block; we find Gallileo’s address and phone number.

That’s it; 3 blocks to find a specific row in a million row table. In reality, index blocks often fit 100 or more rows, so b-Trees are typically quite shallow. I have never seen an index with more than 5 levels. Curious? Try this:

SELECT index_name, blevel+1 FROM user_indexes ORDER BY 2;

user_indexes.blevel is the number of branch levels. Always add 1 to include the leaf level; this tells you the number of blocks a unique index scan must read to reach the leaf-block. If you’re really, really, insatiably curious; try this in SQL*Plus:

ACCEPT index_name PROMPT "Index Name: "

ALTER SESSION SET TRACEFILE_IDENTIFIER = '&index_name';

COLUMN object_id NEW_VALUE object_id

SELECT object_id
FROM   user_objects
WHERE  object_type = 'INDEX'
AND    object_name = upper('&index_name');

ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME TREEDUMP LEVEL &object_id';
ALTER SESSION SET TRACEFILE_IDENTIFIER = "";

SHOW PARAMETER user_dump_dest

Give the name of an index on a smallish table (because this will create a BIG file). Now, on the Oracle server, go to the directory shown by the final SHOW PARAMETER user_dump_dest command and find your trace file – the file name will contain your index name. Here is a sample:

*** 2007-01-31 11:51:26.822
----- begin tree dump
branch: 0x68066c8 109078216 (0: nrow: 325, level: 1)
   leaf: 0x68066c9 109078217 (-1: nrow: 694 rrow: 694)
   leaf: 0x68066ca 109078218 (0: nrow: 693 rrow: 693)
   leaf: 0x68066cb 109078219 (1: nrow: 693 rrow: 693)
   leaf: 0x68066cc 109078220 (2: nrow: 693 rrow: 693)
   leaf: 0x68066cd 109078221 (3: nrow: 693 rrow: 693)
   ...
   ...
   leaf: 0x68069cf 109078991 (320: nrow: 763 rrow: 763)
   leaf: 0x68069d0 109078992 (321: nrow: 761 rrow: 761)
   leaf: 0x68069d1 109078993 (322: nrow: 798 rrow: 798)
   leaf: 0x68069d2 109078994 (323: nrow: 807 rrow: 807)
----- end tree dump

This index has only a root branch with 323 leaf nodes. Each leaf node contains a variable number of index entries up to 807! A deeper index would be more interesting, but it would take a while to dump.

Contrary to popular belief, b is not for binary; it’s balanced.

As you insert new rows into the table, new rows are inserted into index leaf blocks. When a leaf block is full, another insert will cause the block to be split into two blocks, which means an entry for the new block must be added to the parent branch-block. If the branch-block is also full, it too is split. The process propagates back up the tree until the parent of split has space for one more entry, or the root is reached. A new root is created if the root node splits. Staggeringly, this process ensures that every branch will be the same length. Try it on paper for yourself!

Indexes have three main uses:

  • To quickly find specific rows by avoiding a Full Table ScanWe’ve already seen above how a Unique Scan works. Using the phone book metaphor, it’s not hard to understand how a Range Scan works in much the same way to find all people named “Gallileo”, or all of the names alphabetically between “Smith” and “Smythe”. Range Scans can occur when we use >, <, LIKE, or BETWEEN in a WHERE clause. A range scan will find the first row in the range using the same technique as the Unique Scan, but will then keep reading the index up to the end of the range. It is OK if the range covers many blocks.
  • To avoid a table access altogetherIf all we wanted to do when looking up Gallileo in the phone book was to find his address or phone number, the job would be done. However if we wanted to know his date of birth, we’d have to phone and ask. This takes time. If it was something that we needed all the time, like an email address, we could save time by adding it to the phone book.

    Oracle does the same thing. If the information is in the index, then it doesn’t bother to read the table. It is a reasonably common technique to add columns to an index, not because they will be used as part of the index scan, but because they save a table access. In fact, Oracle may even perform a Fast Full Scan of an index that it cannot use in a Range or Unique scan just to avoid a table access.

  • To avoid a sortThis one is not so well known, largely because it is so poorly documented (and in many cases, unpredicatably implemented by the Optimizer as well). Oracle performs a sort for many reasons: ORDER BY, GROUP BY, DISTINCT, Set operations (eg. UNION), Sort-Merge Joins, uncorrelated IN-subqueries, Analytic Functions). If a sort operation requires rows in the same order as the index, then Oracle may read the table rows via the index. A sort operation is not necessary since the rows are returned in sorted order.

    Despite all of the instances listed above where a sort is performed, I have only seen three cases where a sort is actually avoided.

    1. GROUP BY 
        1  select src_sys, sum(actl_expns_amt), count(*)
  2  from ef_actl_expns
  3  where src_sys = 'CDW'
  4  and actl_expns_amt > 0
  5* group by src_sys

-------------------------------------------------------------
| Id  | Operation                           | Name          |
-------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |
|   1 |  SORT GROUP BY NOSORT               |               |
|*  2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| EF_ACTL_EXPNS |
|*  3 |    INDEX RANGE SCAN                 | EF_AEXP_PK    |
-------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("ACTL_EXPNS_AMT">0)
   3 - access("SRC_SYS"='CDW')

      Note the NOSORT qualifier in Step 1.

    2. ORDER BY 
        1  select *
  2  from ef_actl_expns
  3  where src_sys = 'CDW'
  4  and actl_expns_amt > 0
  5* order by src_sys

------------------------------------------------------------
| Id  | Operation                          | Name          |
------------------------------------------------------------
|   0 | SELECT STATEMENT                   |               |
|*  1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| EF_ACTL_EXPNS |
|*  2 |   INDEX RANGE SCAN                 | EF_AEXP_PK    |
------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ACTL_EXPNS_AMT">0)
   2 - access("SRC_SYS"='CDW')

      Note that there is no SORT operation, despite the ORDER BY clause. Compare this to the following:

        1  select *
  2  from ef_actl_expns
  3  where src_sys = 'CDW'
  4  and actl_expns_amt > 0
  5* order by actl_expns_amt

-------------------------------------------------------------
| Id  | Operation                           | Name          |
-------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |
|   1 |  SORT ORDER BY                      |               |
|*  2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| EF_ACTL_EXPNS |
|*  3 |    INDEX RANGE SCAN                 | EF_AEXP_PK    |
-------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("ACTL_EXPNS_AMT">0)
   3 - access("SRC_SYS"='CDW')
    3. DISTINCT 
        1  select distinct src_sys
  2  from ef_actl_expns
  3  where src_sys = 'CDW'
  4* and actl_expns_amt > 0

-------------------------------------------------------------
| Id  | Operation                           | Name          |
-------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |
|   1 |  SORT UNIQUE NOSORT                 |               |
|*  2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| EF_ACTL_EXPNS |
|*  3 |    INDEX RANGE SCAN                 | EF_AEXP_PK    |
-------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("ACTL_EXPNS_AMT">0)
   3 - access("SRC_SYS"='CDW')

      Again, note the NOSORT qualifier.

    This is an extraordinary tuning technique in OLTP systems like SQL*Forms that return one page of detail at a time to the screen. A SQL with a DISTINCT, GROUP BY, or ORDER BY that uses an index to sort can return just the first page of matching rows without having to fetch the entire result set for a sort. This can be the difference between sub-second response time and several minutes or hours.

    Up to now, we’ve seen how indexes can be good. It’s not always the case; sometimes indexes are no help at all, or worse: they make a query slower.

    A b-Tree index will be no help at all in a reduced scan unless the WHERE clause compares indexed columns using >, <, LIKE, IN, or BETWEEN operators. A b-Tree index cannot be used to scan for any NOT style operators: eg. !=, NOT IN, NOT LIKE. There are lots of conditions, caveats, and complexities regarding joins, sub-queries, OR predicates, functions (inc. arithmetic and concatenation), and casting that are outside the scope of this article. Consult a good SQL tuning manual.

    Much more interesting – and important – are the cases where an index makes a SQL slower. These are particularly common in batch systems that process large quantities of data.

    To explain the problem, we need a new metaphor. Imagine a large deciduous tree in your front yard. It’s Autumn, and it’s your job to pick up all of the leaves on the lawn. Clearly, the fastest way to do this (without a rake, or a leaf-vac…) would be get down on hands and knees with a bag and work your way back and forth over the lawn, stuffing leaves in the bag as you go. This is a Full Table Scan, selecting rows in no particular order, except that they are nearest to hand. This metaphor works on a couple of levels: you would grab leaves in handfuls, not one by one. A Full Table Scan does the same thing: when a bock is read from disk, Oracle caches the next few blocks with the expectation that it will be asked for them very soon. Type this in SQL*Plus:

    SHOW PARAMETER db_file_multiblock_read_count

    Just to shake things up a bit (and to feed an undiagnosed obsessive compulsive disorder), you decide to pick up the leaves in order of size. In support of this endeavour, you take a digital photograph of the lawn, write an image analysis program to identify and measure every leaf, then load the results into a Virtual Reality headset that will highlight the smallest leaf left on the lawn. Ingenious, yes; but this is clearly going to take a lot longer than a full table scan because you cover much more distance walking from leaf to leaf.

    So obviously Full Table Scan is the faster way to pick up every leaf. But just as obvious is that the index (virtual reality headset) is the faster way to pick up just the smallest leaf, or even the 100 smallest leaves. As the number rises, we approach a break-even point; a number beyond which it is faster to just full table scan. This number varies depending on the table, the index, the database settings, the hardware, and the load on the server; generally it is somewhere between 1% and 10% of the table.

    The main reasons for this are:

    • As implied above, reading a table in indexed order means more movement for the disk head.
    • Oracle cannot read single rows. To read a row via an index, the entire block must be read with all but one row discarded. So an index scan of 100 rows would read 100 blocks, but a FTS might read 100 rows in a single block.
    • The db_file_multiblock_read_count setting described earlier means FTS requires fewer visits to the physical disk.
    • Even if none of these things was true, accessing the entire index and the entire table is still more IO than just accessing the table.

    • So what’s the lesson here? Know your data! If your query needs 50% of the rows in the table to resolve your query, an index scan just won’t help. Not only should you not bother creating or investigating the existence of an index, you should check to make sure Oracle is not already using an index. There are a number of ways to influence index usage; once again, consult a tuning manual. The exception to this rule – there’s always one – is when all of the columns referenced in the SQL are contained in the index. If Oracle does not have to access the table then there is no break-even point; it is generally quicker to scan the index even for 100% of the rows.

    Indexes are not a dark-art; they work in an entirely predictable and even intuitive way. Understanding how they work moves Performance Tuning from the realm of guesswork to that of science; so embrace the technology and read the manual.

2010
08/13
CATEGORY
Indexes
Oracle
TAGS

2 comments

Oracle vs. SQL Server Date Conversion

An excellent post on this subject here (full text displayed under the link):

http://sql-troubles.blogspot.com/2010/02/oracle-vs-sql-server-date-conversion.html

During data conversions, data migrations or also during simple processing of data is requested to format dates to a given format, extract a given time unit or convert a string to a date data type. Even if Oracle and SQL Server provides several functions for this purpose, there are small techniques that could help make things easier.

In SQL Server the DatePart and DateName functions can be used to extract the various type of time units, the first function returning always an integer, while the second returns a character string, allowing thus to get the name of the current month or of the current day of the week, otherwise the output being quite similar.
– SQL Server DatePart
SELECT GETDATE() CurrentDate
, DatePart(ss, GETDATE()) SecondPart
, DatePart(mi, GETDATE()) MinutePart
, DatePart(hh, GETDATE()) MinutePart
, DatePart(d, GETDATE()) DayPart
, DatePart(wk, GETDATE()) WeekPart
, DatePart(mm, GETDATE()) MonthPart
, DatePart(q, GETDATE()) QuaterPart
, DatePart(yyyy, GETDATE()) YearPart
– SQL Server DateName
SELECT GETDATE() CurrentDate
, DateName(ss, GETDATE()) SecondPart
, DateName(mi, GETDATE()) MinutePart
, DateName(hh, GETDATE()) MinutePart
, DateName(d, GETDATE()) DayPart
, DateName(wk, GETDATE()) WeekPart
, DateName(mm, GETDATE()) MonthPart
, DateName(q, GETDATE()) QuaterPart
, DateName(yyyy, GETDATE()) YearPart , DateName(mm, GETDATE()) MonthName
, DateName(dd, GETDATE()) DayName

SQL Server provides three quite useful functions for getting the Day, Month or Year of a given date:
– SQL Server alternative functions
SELECT DAY(GetDate()) DayPart
, MONTH(GetDate()) MonthPart , YEAR(GetDate()) YearPar

Oracle provides a more flexible alternative of DateName function, respectively the TO_CHAR function, that allow not only the extraction of the different time units, but also the conversion of a date to a specified format.
– Oracle Date parts
SELECT to_char(SYSDATE, ‘dd-mon-yyyy hh24:mi:ss’) CurrentDate
, to_char(SYSDATE, ‘SS’) SecondPart
, to_char(SYSDATE, ‘MI’) MinutePart
, to_char(SYSDATE, ‘HH’) HourPart
, to_char(SYSDATE, ‘DD’) DayPart
, to_char(SYSDATE, ‘IW’) WeekPart
, to_char(SYSDATE, ‘MM’) MonthPart
, to_char(SYSDATE, ‘QQ’) QuarterPart
, to_char(SYSDATE, ‘YYYY’) YearPart
, to_char(SYSDATE, ‘MONTH’) MonthName
, to_char(SYSDATE, ‘DAY’) DayName
FROM DUAL
– Oracle Date formatting
SELECT to_char(SYSDATE, ‘yyyy-mm-dd hh24:mi:ss’) CurrentDate
, to_char(SYSDATE, ‘Mon dd yyyy hh24:mi’) USDateFormat
, to_char(SYSDATE, ‘mm/dd/yyyy’) ANSIDateFormat
, to_char(SYSDATE, ‘yyyy.mm.dd’) BritishDateFormat
, to_char(SYSDATE, ‘dd/mm/yyyy’) GermanDateFormat
, to_char(SYSDATE, ‘dd-mm-yyyy’) ItalianDateFormat
, to_char(SYSDATE, ‘yyyy/mm/dd’) JapanDateFormat
, to_char(SYSDATE, ‘yyyymmdd’) ISODateFormat
, to_char(SYSDATE, ‘dd Mon yyyy hh24:mi:ss’) EuropeDateFormat
, to_char(SYSDATE, ‘yyyy-mm-dd hh24:mi:ss’) ODBCDateFormat
, Replace(to_char(SYSDATE, ‘yyyy-mm-dd hh24:mi:ss’), ‘ ‘, ‘T’) ISO8601DateFormat
FROM DUAL
Date Oracle fomatting
Even if there are more plausible combinations, the above examples could be used as a starting point, they being chosen to match the similar functionality provided by SQL Server using the CONVERT function and styles.
– SQL Server date formatting
SELECT GETDATE() CurrentDate
, CONVERT(varchar(20), GETDATE(), 100) USDateFormat
, CONVERT(varchar(20), GETDATE(), 101) ANSIDateFormat
, CONVERT(varchar(20), GETDATE(), 102) BritishDateFormat
, CONVERT(varchar(20), GETDATE(), 103) GermanDateFormat
, CONVERT(varchar(20), GETDATE(), 105) ItalianDateFormat
, CONVERT(varchar(20), GETDATE(), 111) JapanDateFormat
, CONVERT(varchar(20), GETDATE(), 112) ISODateFormat
, CONVERT(varchar(20), GETDATE(), 113) EuropeDateFormat
, CONVERT(varchar(20), GETDATE(), 120) ODBCDateFormat
, CONVERT(varchar(20), GETDATE(), 126) ISO8601DateFormat
Date SQL Server formatting
The use of CONVERT function with styles is not the best approach though it saves the day. When the same formatting is used in multiple objects it makes sense to encapsulate the used date conversions in a function, making thus easier their use and their maintenance in case of changes of formatting.
CREATE FUNCTION dbo.GetDateAsString( @date datetime)
RETURNS varchar(10)
AS
BEGIN
 RETURN CONVERT(varchar(10), @date, 103)
END

The inverse problem is the conversion of a string to a date, Oracle providing the TO_DATE, CAST, TO_TIMESTAMP and  TO_TIMESTAMP_TZ functions for this purpose, the first two functions being the most used.
–Oracle String to Date Conversion
SELECT TO_DATE(’25-03-2009′, ‘DD-MM-YYYY’)
, TO_DATE(’25-03-2009 18:30:23′, ‘DD-MM-YYYY HH24:MI:SS’)
, Cast(’25-MAR-2009′ as Date)
FROM DUAL Excepting the CONVERT function mentioned above, SQL Server provides a CAST function too, both allowing the conversion of strings to date.
SELECT CAST(’2009-03-25′ as date)
, CONVERT(date, ’2009-03-25′)

When saving dates into text attributes in SQL Server it should be targeted to use the ISO format which is independent of the format set by DATEFORMAT, otherwise, in case the format of the date stored is known, the string could be translated to the ISO format like in the below function:
–SQL Server: DD/MM/YYYY  String to Date function
CREATE
FUNCTION dbo.GetStringDate(
@date varchar(10))
RETURNS datetime
AS
BEGIN
 RETURN Cast(Right(@date, 4) + ‘/’ + Substring(@date, 4,2) + ‘/’ + Left(@date, 2) as datetime)
END
SELECT
dbo.GetStringDate(’25/09/2009′)

Other approach I found quite useful in several cases is based on the VBScript DateSerial function that allows the creation of a date from its constituents:
– SQL Server: DateSerial
CREATE FUNCTION dbo.DateSerial(
@year int
, @month smallint ,
@day smallint)
RETURNS
date
AS
BEGIN
RETURN (Cast(@year as varchar(4)) + ‘-’ + Cast(@month as varchar(2)) + ‘-’ + Cast(@day as varchar(2)))
END
SELECT
dbo.DateSerial(2009,10,24)
– SQL Server: DateTimeSerial
CREATE FUNCTION dbo.DateTimeSerial(
@year int
, @month smallint
, @day smallint
, @hour smallint
, @minute smallint
, @second smallint)
RETURNS
datetime AS
BEGIN
RETURN (Cast(@year as varchar(4)) + ‘-’ + Cast(@month as varchar(2)) + ‘-’ + Cast(@day as varchar(2))
+ ‘ ‘ + Cast(@hour as varchar(2)) + ‘:’ + Cast(@minute as varchar(2)) + ‘:’ + Cast(@second as varchar(2)))
END
 Given TO_DATE function’s flexibility none of the three above functions – GetStringDate, DateSerial and DateTimeSerial, are really needed in Oracle.

2010
05/12
CATEGORY
Uncategorized
TAGS



Write comment

How SQL query results are generated

From the book – “SQL: The Complete Reference”…

To generate the query results for a single-table SELECT statement, follow these steps:

// 1. FROM Clause:

Selecting the table or view containing data to be retrieved

// 2.  WHERE clause:

Selecting specific rows from the table.

If there is a WHERE clause, apply its search condition to each row of the table, retaining those rows for which the search condition is TRUE, and discarding those rows for which it is FALSE or NULL.

// 3. SELECT clause:

Generating the specific columns of query results and eliminating

duplicate rows, if requested.

For each remaining row, calculate the value of each item in the select list to produce a single row of query results. For each column reference, use the value of the column in the current row.

// 4. If SELECT DISTINCT is specified, eliminate any duplicate rows of query results that

were produced.

// 5.  ORDER BY clause:

If there is an ORDER BY clause, sort the query results as specified.

… The rows generated by this procedure comprise the query results.

2010
02/15
CATEGORY
SQL Generic / Cross-Platform
TAGS

Write comment

Data Visualisation: Some books of note

Here are some very good books on data visualisation and how to present and communicate quantitative information effectively  –>

Now You See It: Simple Visualization Techniques for Quantitative Analysis


http://www.amazon.com/gp/product/0970601980

Information-Dashboard-Design-Effective-Communication

The Visual Display of Quantitative Information, 2nd edition

http://www.amazon.com/Visual-Display-Quantitative-Information-2nd/dp/0961392142/ref=pd_bxgy_b_img_c

2010
02/11
CATEGORY
Data Visualisation
TAGS


Write comment

Screenshots with Greenshot

This is a great little screenshot capturer application. Open source so it is free, and works really well with pasting into e-memory tools like Evernote.com.

Previously I’ve used an application called ‘MWSnap’ which is great but this one is a bit better and easier to use.

http://greenshot.sourceforge.net/

Here is the screenshot from the site link above:



“Greenshot is a revolutionary screenshot tool optimized for productivity.

  • Save a screenshot or a part of the screen to a file within a second.
  • Apply text and shapes to the screenshot.
  • Offers capture of window, region or full screenshot.
  • Supports several image formats.”
2010
02/09
CATEGORY
Useful Tools
TAGS
Write comment

Dashboard links

http://www.asiamarketresearch.com/glossary/top-line.htm

http://www.montrosecorporatetravel.com/management-reporting.html

http://dashboardinsight.com/dashboards/gallery/gallery-page2.aspx

http://www.enterprise-dashboard.com/

http://www.dashboardzone.com/

http://www.smashingmagazine.com/2007/08/02/data-visualization-modern-approaches/

2010
01/28
CATEGORY
Data Visualisation
TAGS

Write comment

100 Online Brainstorming Tools to Help You Think Outside the Box

from: http://www.forensicsciencetechnician.org/?page_id=27

100 Online Brainstorming Tools to Help You Think Outside the Box

A lateral thinking process, brainstorming demands that people come up with ideas and thoughts that can seem shocking or crazy. By using these ideas as a starting point, you can then change and improve them into something useful and original. Below are the top 100 tools, tips, guides, and resources to help you think outside the box and maximize your ideas.

Tips, Sites, and Guides for Brainstorming

Learn how the professionals do it by reading the below. It is full of expert advice on what to do in sessions, as well as what to avoid.

1. Brainstorming Blog: This blog provides tons of resources to keep your brainstorming fresh, fun, interesting, and productive. Get recommendations for books, brainstorming tips, and much more.

2. The Right Way to Brainstorm: Brian Libby of BNET, offers simple steps to an effective brainstorming session. Be sure and check out the comments section for additional components and tips.

3. Ten Brainstorming Rules: Get advice on everything from setting the directions to knowing what ideas to eliminate. You can also get links to other helpful brainstorming information.

4. Brainstorming Basics: TRIZ Journal takes you through the basics of brainstorming in this helpful article. Learn the fundamentals, key warm-ups, and more.

5. A User’s Guide to Brainstorming: “U.S. News and World Report” brings you expert advice on brainstorming. Learn the pros and cons of brainstorming, along with tips to do it right.

6. Brainstorming: Start Here: This article is ideal for those who have no idea how to brainstorm. Learn the basics, how to work with others, and more.

7. The Right Way to Brainstorm: Also available as a pdf, this simple guide take you through the basics of brainstorming. Get the six essential steps for an effective brainstorming meeting.

8. Creative Brainstorming Techniques: Kim Gordon is a marketing expert at Entrepreneur and gives six ways to come up with million dollar ideas. Get tips on everything from suspending criticism to using other people’s ideas.

9. Applied Imagination: Steve is a blogger from Connecticut who writes about exploring ideas, creative thinking, problem solving, innovation, applied imagination, education, creative studies, and more. Get a host of new ideas and techniques for your brainstorming, or even send him a question.

10. Seven Secrets to Good Brainstorming: Learn how many participants to have, what time to do your brainstorming, and even what foods to have. You can also get useful links to related topics.

11. Six Surefire Ways to Kill a Brainstorm: Just as important as what to do is what never to do. Get the rules on who should speak, when to speak, and what to never say.

12. Eight Tips for Better Brainstorming: Learn when to work as an individual or group and more with this helpful guide from Business Week. Get the do’s and don’ts, as well as other useful information from Robert Sutton, professor at Stanford’s School of Engineering.

14. Tips for Personal Brainstorming: Chuck Frey is a brainstorming expert and offers these easy to follow tips. You can also get other creative solutions and inspiring quotes.

15. Creative Think: Roger von Oech is an award winning creative thinker and offers many brainstorming products on this site. You can get an application for your iPhone, an X-ball, or read his blog full of expert tips.

16. Brainstorming Strategy: Not settling on the first idea is just one of the tips you can get from this article. You can also get a hypothetical problem involving a furniture company along with solutions.

17. A Trojan Horse of Mediocrity: This author has a lot of experience running brainstorm sessions, and as a result hates them. Learn what pitfalls to avoid to keep participants from feeling the same way.

18. Brainstorming for Families: Got an issue at home that needs discussing or solving? Then use this guide from Home & Family to show you how to get kids, adults, and teens involved in the brainstorming process.

19. Brainstorming Handout: The University of North Carolina at Chapel Hill gives their students this handout to help them brainstorm. Get tips, techniques, and more.

20. How to Run a Brainstorming Meeting: Scott Berkun is a best selling author and offers his advice on brainstorming. Learn the necessities, processes, actions to take, and more when running a meeting.

21. Enhanced Creativity in Brainstorming: Karen is from the Carleton University in Canada and shows you how to work creativity into your brainstorming. Be sure to read the study results at the end to see which brainstorming groups were most effective.

22. Don’t Give In to Limited Thinking: Learn when to ditch an idea that isn’t going anywhere with the help of this article. You can also see when an idea needs to be modified, rather than thrown out.

23. Tips for Personal Brainstorming: Working on your own? Then follow these two easy steps for the best individual brainstorming.

24. Top 5 Brainstorming Tips: If your brainstorming sessions are coming up short, try these five tips. They include criticism, change, feeding the masses, beefing it up, and copying.

25. The Key to Effective Brainstorming: This article is full of easy to follow tips to get everyone involved in the brainstorming session. There are also links to related information.

26. Facilitating a Brainstorm Session: Get back to the basics of brainstorming with the help of Office Arrow. Read the four essential steps or get more information on a number of business related topics.

27. Step by Step Guide to Brainstorming: The organizational experts at JPB offer this free guide to brainstorming. Follow these seven easy steps to maximize your group session.

28. Brainstorming for Bloggers: Do you contribute to a blog but find yourself running out of ideas? Then read this article by Vandelay Design to get the creative juices going.

29. Brainstorming Help: With over 2,000 articles to choose from, eHow has tons of information on the practice. You can get everything from the generalized basics to brainstorming for specific ideas or projects.

30. Video Help: With everything from brainstorming for public speaking to holiday preparations, this series of videos has everything you need on the topic. You can find help with everything from creative to brainstorming for professionals.

Brainstorming Techniques

With tons of brainstorming techniques available, it is no wonder that the following are the most popular due to their accessibility and effectiveness for outside the box thinking.

31. 25 Brainstorming Techniques: Celestine left her career at a Fortune 100 company to help others find their passion. She also gives 25 essential techniques for maximizing brainstorming sessions.

32. SCAMPER: Developed by Bob Eberle, this brainstorming tool stands for: Substitute, Combine, Adapt, Modify, Put, Eliminate, and Reverse. Each step is identified and shown how to utilize in brainstorming.

33. Word Games: This easy and fun way to warm up your mind is an excellent way to begin a brainstorming session. With dozens of games to choose from, you can brainstorm just about anything.

34. Cause and Effect Analysis: Listing all the possible causes and effects of a problem is an efficient way to brainstorm it. Learn how to utilize this brainstorming method and get helpful diagrams as well.

35. Mind Maps: Have a bunch of random thoughts and need to get them organized? Then try the Mind Maps technique as taught on this site, complete with sample diagrams and tips.

36. SWOT Analysis: This brainstorming method is used to analysis a problem’s Strengths, Weaknesses, Opportunities, and Threats. Get an illustrated diagram, uses for corporate marketing, and more.

37. Random Input: When stuck in a particular mode of thinking, random input helps by associating previously unthought of nouns and verbs into brainstorming. Directions on how to use this tool, key points, and examples can be found on this site.

38. Kailedoscope Brainstorming Process: This five step method is ideal for those who want an advanced brainstorming session. Developed by Dr. Murthy of Nisvara Inc, this process makes use of both silence and communication.

39. The Reframing Matrix: This simple technique helps you look at business problems from a number of different viewpoints. An instructional diagram is included, along with other brainstorming tips.

40. Starbursting: By bombarding a topic or idea with questions, the answers can provide valuable brainstorming insight. This method comes with a sample worksheet and example diagram.

41. Business Impact Analysis: By imagining the best and worst case scenarios, brainstormers can get a leg up just as professional business planners do. Get examples and other business tools from this site.

42. Affinity Diagram: Have too much information and don’t know where to start? Then use this diagram to organize and sort through your ideas.

43. Reverse Brainstorming: By looking at a problem in reverse, you can approach it from a different angle. Sample reverse questions are provided, as well as tips and steps for using this method.

44. The Nominal Group Technique: Have one person who is talking over everyone else? Then try this technique to separate and assign, making sure everyone has a voice.

45. The Lotus Blossom Technique: Developed by Yasuo Matsumura, Director of the Clover Management Research in Japan, this technique is based upon the petals of the lotus flower. The six major steps of this technique are discussed, along with helpful diagrams.

46. The Crawford Slip Method: Need to get ideas from a large group of people with just a little time? Then try this method for involving everyone and collecting ideas.

47. Brainwriting: By drawing a simple sheet, identifying problems, and getting everyone to write a solution, you can utilize this simple technique. There are also examples of this technique to help you get started.

48. Bisociation: By forcing or banging ideas together, you can utilize this tool for your brainstorming sessions. You can also get information on forced association and cognitive dissonance.

49. Eye Wire Cards: This free pdf file contains 20 colorful creativity cards that can greatly help with brainstorming. Each card contains an exercise such as “change viewpoints,” ” think in opposites” and “shake your habits.”

50. The 100 What’s of Creativity: This free pdf contains one hundred questions to spur your creativity, unstick your mind, and maximize brainstorming. As the title suggests, questions range from “what if you asked a fool?” to “what if it was a superhero?”

52. Jump Start: This free internet tool helps in just about any brainstorming word problem. Just type in your challenge, and Jump Start will instantly provide a list of related adjectives.

53. The Idea Lottery: Make connections between seemingly unrelated elements with this free tool. In four easy steps, Idea Lottery will provide you with tons of answers to your challenges.

54. Job Force: Need to use a random challenge in your brainstorming session? Then visit job force for a list of brainstorming problems to use in your own session.

55. Random Word Technique: Type a description of your problem into the form, get random words, choose one, and get a list of solutions with this free online brainstorming service.

56. Watizit Creative Idea Generation Tool: Use Watizit to generate ideas for coming up with new approaches to problems, products, and other projects. By answering questions and utilizing pictures, this technique helps take your brainstorming in new directions.

57. Creative Problem Solving: The Solution Machine on this site allows you to mimic the brain’s creativity process. It can even evaluate the solutions you’ve already created.

58. Random Picture Generator: This site is ideal for those who need to throw something random into their brainstorming process. It offers dozens of thought provoking images at the click of a button.

59. Brain Teasers and Riddles: Need to engage a group quickly? Then choose one of the eleven problems on this site. Just scroll down for the answers if you’re stuck.

60. Visual Brainstorming: When verbal brainstorming fails, go visual. By collaboratively generating ideas without using the spoken or written word, you can come up with incredible solution. Examples and tips are given.

For Internet and Computer

Brainstormers who utilize there computers for sessions, research, and more will wonder how they ever got along without the help of these free and useful resources.

61. MindMeister: This is a free and easy way to make online mind maps. It allows you to brainstorm and share ideas, plan projects, think visually, and more.

62. FreeMind: One of the few brainstorming softwares available at no cost, Freemind works on all java capable programs. It is compatible with Windows, Linux, and Macintosh OS X.

63. Bubbl.us: This is a simple and free web application that lets you brainstorm online. Bubbl.us allows you to create maps online, embed in a blog or website, as well as the option to save or print your maps.

64. ThinkGraph: This free software uses a combination of 2D drawings and Concept Maps that allow you to easily illustrate your thoughts. You can also get tips, guides, and examples on the site.

65. XMind: Utilizing Web 2.0 concepts, this software infuses team brainstorming and personal mind mapping. Basic service is free and you can even convert mind maps into a pdf or PowerPoint.

66. Customize Google: This amazing Firefox extension enhances Google search results by adding extra information from Yahoo, Ask.com, MSN, etc. as well as removing ads and spam. This site also offers an instructional video to help you maximize your brainstorming.

67. GooglePreview: Get previews of websites after using a google search to see if it is worth visiting without navigating away. This add-on also gives popularity ranks and is compatible with Yahoo.

68. Googlebar Lite: A must for anyone doing web brainstorming. This lightweight toolbar gives you twelve types of searches, the ability to customize, and much more.

69. Drill Down: One of the easiest tools to use, this is a diagram to be filled out with your own ideas, along with each action for those ideas. It will help you stay on task and manage brainstorming time wisely.

70. Mycroft Project: Add thirty popular search plug ins with this one add-on. Brainstormers can instantly utilize Yahoo, eBay, Lycos, Altavista, and more.

71. BetterSearch: If you use Google, Yahoo, MSN, or Delicious for your brainstorming, try this add-on. It allows previews, the option to open in new window, site info, links, and a quick preview feature to the search results.

73. ErrorZilla Plus: This add-on is perfect for brainstormers who constantly get the same error page over and over. ErrorZilla will replace that page with help from Google Cache, Wayback, and others.

74. Googlepedia: Want to see the Wikipedia entry that goes with your brainstorm search? Then get this add on which also allows you to click on links in the article to start new searches.

75. Breadcrumbs: A search engine for your personal browsing history, Breadcrumbs saves your visited pages. Perfect for brainstormers with long research sessions, it also allows you to search your pages from a toolbar and stores data locally to protect your privacy.

76. Ressurect Pages: Dead pages and broken links are no longer a problem with this add-on. It searches caches and archives that mirrored the content while it was available, so that brainstorming sessions can continue without time-costly searches.

77. NextPlease: Hate hitting the Back button to go to the next item searched during a brainstorming session? Utilize NextPlease to continue searches without reloading pages.

78. StumbleUpon: Need to brainstorm on the web but don’t know where to start? Use Stumble Upon for web sites based on your interests and it even gives you recommendations.

79. DownloadHelper: This application is ideal for brainstormers who need to use pictures or videos in their sessions. With this add-on, you can easily save videos from some of the most popular video sites, as well as capture all the images from a gallery in a single operation.

80. DownThemAll: This download manager features an advanced accelerator that increases speed up to 400%. The only program of its kind built for Firefox, it allows you to pause and resume downloads at any time.

81. Stealther: Ideal for those who want to surf suspicious sites without leaving a trace. Turn on Stealther during your brainstorming session to temporarily disable history, cookies, and avoid crashes during meetings.

82. NoScript: Winner of the “2006 PC World Class Award,” this tool alerts you about the web bugs, ad networks, and widgets on every page on the web. It allows JavaScript and other executable content to run only from trusted domains of your choice, guarding you against cross-site scripting attacks and Clickjacking attempts.

83. Ghostery: Turn the tables on websites who are watching you with this add-on. It will alert you to web bugs, ad networks, and widgets on every page you visit.

84. Cleeki: Select any text on your screen, and Cleeki can search, share, publish, and preview results in the same page. Ideal for brainstormers who need to search multiple items.

85. Brain Power Newsletter: Constantly have to do brainstorming sessions? Then sign up for this free newsletter full of tips, ideas, articles, and information on increasing your brain power.

For Sale

Those with as little as four dollars can purchase some of these recommended brainstorming tools. Ranging from books to software, you are sure to find something useful.

86. MindManager: With a free 30 day trial, MindManager allows you to manage to do lists, run effective meetings, brainstorm without limitations, and much more. You can even get a mini preview on this site.

87. Mind Genius: This software is available for a free, 21 day trial. Whether for business or education, it allows you to quickly gather your thoughts, access your information, and get things done.

88. BrainStorm: This software makes it easy for you to capture information from your head, computer screen, or any file. Available for a free 30 day trial, you can even publish your brainstorms on the web.

89. VisiMap: This is a creativity and productivity application for Windows that saves time and offers new flexibility in exploring and organizing your thoughts. It is available for a free 60 day trial and you can get a screenshot gallery.

90. MindMapper: Offered as a free trial, this software allows you to input your ideas into your computer in various ways. They also have different versions for professionals, educators, and more.

91. Axon: An idea processing software, Axon lets you visualize your ideas and save them. It can be used for concept mapping, creative writing, and brainstorming.

92. Inspiration Software: This software can be used to successfully help project teams and brainstorming groups work productively and improve performance. You can download a free trial for business use, as well as for parents and educators.

93. Get Unstuck and Get Going: This book is so popular, it regularly sells out. Author Michael Bungay Stanier shows you how to pinpoint what matters, generate ideas, form an action plan, and get unstuck like the professionals do.

94. 30 Minutes to Brainstorm Great Ideas: For just $3.95 you can download this eBook and handy guide to brainstorming. It will show you how to get into the creative habit by making new connections, creating springboards, and brainstorming as part of a team.

95. Your Creative Power: Written by brainstorming expert, Alex F. Osborn, this book starts at $9.45. It shows you how to discover creative power and then how best to develop and hone that power.

96. Free the Genie: This deck of 55 creative thinking cards are a powerful brainstorming assistant available anytime, anywhere to help unstick your thinking. You can buy the deck, or play four online brainstorming games for free.

97. Unstuck: This is a self-help book, brainstorming tool, and action planning system all in one. You can even get a preview and quiz for free on the site.

98. Creativity a la Card: Each card contains a quote regarding creativity, a “Stretch-ercize” designed to get you thinking in new directions, and whimsical drawing that fits with the theme of each card. The cards also use the power of quotes to get you brainstorming.

99. Personality Poker: Got conflicting personalities in the room? Then use this game to help people understand each other and collaborate effectively.

100. ThinkCube: This new idea takes the brainstorming card deck to the next level. Not only does it provide a diverse selection of thought-provoking stimuli, it also incorporates a simple innovation process that results in a higher quality of ideas.

Brainstorming is one of the most challenging collaborative activities to carry out in a group. While some people think they know how to brainstorm, few have really gotten the basic rules needed to make a brainstorming session work effectively. Until now. With these 100 tools, your brainstorming sessions can go farther than ever.

2010
01/05
CATEGORY
Uncategorized
TAGS




2 comments

Basic and complex SQL joins made easy

http://articles.techrepublic.com.com/5100-10878_11-1046273.html?tag=rbxccnbtr1

Basic and complex SQL joins made easy

by Shelley Doll | Jul 08, 2002 7:00:00 AM

Tags: Shelley Doll

Takeaway: If you think SQL JOIN statements are beyond your reach, think again. This quick review of basic concepts makes joins easy by explaining each type and showing you how to use them.
If you’re new to SQL, joins can be a daunting concept. There are many different types of joins and seemingly little information available that explains and compares them. But after you digest a few basic concepts, the practice of joins actually isn’t very difficult.

Let’s look at the fundamentals that should give you a solid, functional knowledge of how to use the JOIN statement. We won’t consider some more advanced JOIN statement concepts, such as aliasing, join order, conceptual joins, and join hints. While the descriptions I’ll present in this article relate directly to Microsoft SQL Server, they also apply to any SQL–92-compliant database.

The JOIN concept
JOIN is a query clause that can be used with the SELECT, UPDATE, and DELETE data query statements to simultaneously affect rows from multiple tables. There are several distinct types of JOIN statements that return different data result sets.

Joined tables must each include at least one field in both tables that contain comparable data. For example, if you want to join a Customer table and a Transaction table, they both must contain a common element, such as a CustomerID column, to serve as a key on which the data can be matched. Tables can be joined on multiple columns so long as the columns have the potential to supply matching information. Column names across tables don’t have to be the same, although for readability this standard is generally preferred.

When you do use like column names in multiple tables, you must use fully qualified column names. This is a “dot” notation that combines the names of tables and columns. For example, if I have two tables, Customer and Transaction, and they both contain the column CustomerID, I’d use the dot notation, as in Customer.CustomerID and Transaction.CustomerID, to let the database know which column from which table I’m referring.

Now that we’ve examined the basic theory, let’s take a look at the various types of joins and examples of each.

The basic JOIN statement
A basic JOIN statement has the following format:
SELECT Customer.CustomerID, TransID, TransAmt
FROM Customer JOIN Transaction
ON Customer.CustomerID = Transaction.CustomerID;

In practice, you’d never use the example above because the type of join is not specified. In this case, SQL Server assumes an INNER JOIN. You can get the equivalent to this query by using the statement:
SELECT Customer.CustomerID, TransID, TransAmt
FROM Customer, Transaction;

However, the example is useful to point out a few noteworthy concepts:

  • TransID and TransAmt do not require fully qualified names because they exist in only one of the tables. You can use fully qualified names for readability if you wish.
  • The Customer table is considered to be the “left” table because it was called first. Likewise, the Transaction table is the “right” table.
  • You can use more than two tables, in which case each one is “naturally” joined to the cumulative result in the order they are listed, unless controlled by other functionality such as “join hints” or parenthesis.
  • You may use WHERE and ORDER BY clauses with any JOIN statement to limit the scope of your results. Note that these clauses are applied to the results of your JOIN statement.
  • SQL Server does not recognize the semicolon (;), but I use it in the included examples to denote the end of each statement, as would be expected by most other RDBMSs.

The notorious CROSS JOIN
The CROSS JOIN has earned a bad reputation because it’s very resource intensive and returns results of questionable usefulness. When you use the CROSS JOIN, you’re given a result set containing every possible combination of the rows returned from each table. Take the following example:
SELECT CustomerName, TransDate, TransAmt
FROM Customer CROSS JOIN Transaction;

With the CROSS JOIN, you aren’t actually free to limit the results, but you can use the ORDER BY clause to control the way they are returned. If the tables joined in this example contained only five rows each, you would get 25 rows of results. Every CustomerName would be listed as associated with every TransDate and TransAmt.

I really did try to come up with examples where this function was useful, and they were all very contrived. However, I’m sure someone out there is generating lists of all their products in all possible colors or something similar, or we wouldn’t have this wonderful but dangerous feature.

The INNER JOIN drops rows
When you perform an INNER JOIN, only rows that match up are returned. Any time a row from either table doesn’t have corresponding values from the other table, it is disregarded. Because stray rows aren’t included, you don’t have any of the “left” and “right” nonsense to deal with and the order in which you present tables matters only if you have more than two to compare. Since this is a simple concept, here’s a simple example:
SELECT CustomerName, TransDate
FROM Customer INNER JOIN Transaction
ON Customer.CustomerID = Transaction.CustomerID;

If a row in the Transaction table contains a CustomerID that’s not listed in the Customer table, that row will not be returned as part of the result set. Likewise, if the Customer table has a CustomerID with no corresponding rows in the Transaction table, the row from the Customer table won’t be returned.

The OUTER JOIN can include mismatched rows
OUTER JOINs, sometimes called “complex joins,” aren’t actually complicated. They are so-called because SQL Server performs two functions for each OUTER JOIN.

The first function performed is an INNER JOIN. The second function includes the rows that the INNER JOIN would have dropped. Which rows are included depends on the type of OUTER JOIN that is used and the order the tables were presented.

There are three types of an OUTER JOIN: LEFT, RIGHT, and FULL. As you’ve probably guessed, the LEFT OUTER JOIN keeps the stray rows from the “left” table (the one listed first in your query statement). In the result set, columns from the other table that have no corresponding data are filled with NULL values. Similarly, the RIGHT OUTER JOIN keeps stray rows from the right table, filling columns from the left table with NULL values. The FULL OUTER JOIN keeps all stray rows as part of the result set. Here is your example:
SELECT CustomerName, TransDate, TransAmt
FROM Customer LEFT OUTER JOIN Transaction
ON Customer.CustomerID = Transaction.CustomerID;

Customer names that have no associated transactions will still be displayed. However, transactions with no corresponding customers will not, because we used a LEFT OUTER JOIN and the Customer table was listed first.

In SQL Server, the word OUTER is actually optional. The clauses LEFT JOIN, RIGHT JOIN, and FULL JOIN are equivalent to LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN, respectively.

Another addition to your SQL toolbox
Although the JOIN statement is often perceived as a complicated concept, you can now see that it’s a powerful timesaving resource that’s relatively easy to understand. Use this functionality to get related information from multiple tables with a single query and to skillfully reference normalized data. Once you’ve mastered JOINs, you can elegantly maneuver within even the most complex database.

2010
01/04
CATEGORY
SQL Tips & Tricks
TAGS

Write comment
Follow

Get every new post delivered to your Inbox.