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