QUOTENAME : A little known util function for "bracketing" a value

DECLARE @TEXT VARCHAR(50)
DECLARE @NUMBER INT
DECLARE @QUOTE CHAR(1)

SET @QUOTE = '"'

SET @TEXT = 'hello'

SET @NUMBER = 13

--bracketed with default brackets

SELECT  QUOTENAME(@TEXT), len(@TEXT), len(quotename(@TEXT))

--bracketed with default brackets

--note that an implicit conversion occurs)

SELECT  QUOTENAME(@NUMBER)

--bracketed with a double quote

SELECT  QUOTENAME(@TEXT, @QUOTE)

Great SQL Brain Teaser.... how many records are in the table?

Create Table TBL1 (col1 int, col2 int)

Create Table TBL2 (col1 int, col2 int)

 

--query1

select count(col1) from TBL1 where col2 >= 5

--results = 3

 

--query2

select count(col1) from TBL1 where col2 < 5

--results = 2

 

-- can you tell how many records are in the table?

-- how/why?

Find the most recently modified table constraints

USE somedatabasename
GO

SELECT DISTINCT CONSTRAINT_NAME

                                 ,modify_date
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
LEFT OUTER JOIN sys.all_objects
ON CONSTRAINT_NAME = name
WHERE TABLE_CATALOG = N'somedatabasename'
ORDER BY modify_date DESC

--complements to Igor Malkiman of Qwest Communications for this code

When were the stored procedures last modified?

USE [SOMEdbNAME]
GO
SELECT so.name, so.modify_date
FROM SYS.OBJECTS SO
WHERE SO.name in
   (SELECT ROUTINE_NAME
   FROM INFORMATION_SCHEMA.ROUTINES isr
   WHERE isr.ROUTINE_CATALOG='SOMEdbNAME')
ORDER BY so.modify_date desc

--complements to Igor Malkiman of Qwest Communications for this code

Deleting records from a source table as you add them to a destination

Frequently we come across a case where the records in a source table must meet a certain condition in order to be loaded into the destination table.  I recently came across a problem where the records were to remain in the source table if the conditions were not met. 

So the question was how to keep track of the inserted records? 

• Query the source table after the insert for records matching the destinationtable and then delete them? 
• Create a #Temp table of what is to be inserted and join that same table to the source for deletion?

Or:

• Use the OUTPUT clause (available on insert, update and delete statements) to populate a table of record-keys of what has been inserted and later use that to join to the source records for deletion

Here is an example:

CREATE TABLE #T1(COL1 INT)
CREATE TABLE #T2(COL1 INT)
CREATE TABLE #T3(COL1 INT)

DECLARE @iRec int

TRUNCATE TABLE #T1
TRUNCATE TABLE #T2
TRUNCATE TABLE #T3

-- populate some data 
set @iRec= 1
While @iRec <= 10
BEGIN
   INSERT #T1 VALUES(@iREC)
   if @iRec < 6
      insert #T2 values(@iREC)
   set @iRec = @iRec + 1
END

-- perform the deltion
DELETE #T1
      OUTPUT Deleted.*  INTO #T3
FROM #T1 ONE
JOIN #T2 TWO ON ONE.COL1 = TWO.col1

-show the results
SELECT '#T1 - original recs',* FROM #T1
SELECT '#T2 - deleted recs ',* FROM #T2
SELECT '#T3 - new table ',* FROM #T3

-- cleanup
DROP TABLE #T1
DROP TABLE #T2
DROP TABLE #T3

Restarting SSIS Packages without processing ALL the records over again

Here is a link to a little-known solution to a common problem.  Every time I have restarted a SQL SSIS package in the past that performs transformations on records, all the records get processed over again. 

This is a problem in a couple respects:

• Processing time wasted
• Code must check for records already processed
• Additional time is spent maintaining that code and procesing

Here is the link:
Restarting SSIS Packages with Checkpoints

While this article uses SQL 2008 as an example, SQL 2005 also employes this same feature.

What is Business Intelligence?

Business Intelligence (BI) refers to skills, technologies, applications and practices used to help a business acquire a better understanding of its commercial context. Business intelligence may also refer to the collected information itself.

BI technologies provide historical, current, and predictive views of business operations. Common functions of business intelligence technologies are reporting, OLAP, analytics, data mining, business performance management, benchmarking, text mining, and predictive analytics.

In a 1958 article, IBM researcher Hans Peter Luhn used the term business intelligence. He defined intelligence as:[1] "the ability to apprehend the interrelationships of presented facts in such a way as to guide action towards a desired goal."


In 1989 Howard Dresner (later a Gartner Group analyst) proposed BI as an umbrella term to describe  [2]"concepts and methods to improve business decision making by using fact-based support systems." 

It was not until the late 1990s that this usage was widespread.  More recently with the release of SQL 2005, the marketing department at Microsoft has trademarked the term with the introduction  of their "Business Intelligence" toolset.  Thus many believe BI to be something new on the scene.  Before the term "BI" gained popularity, terms such as Predictive Analytics, Decision Support Systems (DSS), and Executive Information Systems (EIS) covered earlier iterations of the same science.

[1] H. P. Luhn (October 1958). "A Business Intelligence System" (PDF). IBM Journal. http://www.research.ibm.com/journal/rd/024/ibmrd0204H.pdf. Retrieved 2008-07-10.


[2] D. J. Power (2007-03-10). "A Brief History of Decision Support Systems, version 4.0". DSSResources.COM. http://dssresources.com/history/dsshistory.html. Retrieved 2008-07-10.