SQLServer function to update the same table where cursor selects values

--Check whether cursor is open

IF CURSOR_STATUS('global','c4') >= 0

BEGIN

        CLOSE c4

        DEALLOCATE c4

END

DECLARE @comparison VARCHAR(200)

DECLARE @source VARCHAR(200)

DECLARE @supplier VARCHAR(200)

DECLARE @itemname VARCHAR(200)

DECLARE @atc3names VARCHAR(max)

DECLARE c4 CURSOR FOR

SELECT DISTINCT

        [Comparison NAME],     

        [Data Supplier NAME],  

        [Data Source NAME],    

        [Item NAME]

FROM dbo.[Data Validation Attribute Items] WHERE [Comparison NAME] = 'Product'

FOR UPDATE OF [ATC3]

OPEN c4

FETCH NEXT FROM c4 INTO @comparison, @supplier, @source, @itemname

WHILE @@FETCH_STATUS = 0

BEGIN

        -- Combine multile rows in to a column seperated by a ';'

        SET @atc3names  = 'DECLARE @atc3names  AS VARCHAR(200)

        SELECT @atc3names = COALESCE( @atc3names + '';'', '''') + [ATC3 Name]

        FROM

        (

        SELECT DISTINCT [ATC3 Name]

        FROM dbo.[' + @source + ' Fact Data] a

                INNER JOIN dbo.[Product Dim Product-Pack-Country] b

ON a.[Product-Pack-Country KEY] = b.[Product-Pack-Country KEY]

                INNER JOIN dbo.[Product Dim Product-Country] c

ON b.[Product-Country KEY] = c.[Product-Country KEY]

                INNER JOIN dbo.[Product Dim Product-Pack] f 

                        ON b.[Product-Pack KEY] = f.[Product-Pack KEY]

                INNER JOIN dbo.[Product] e ON c.[Product KEY] = e.[Product KEY]

                INNER JOIN dbo.[ATC4] g ON f.[ATC4 KEY] = g.[ATC4 KEY]

                INNER JOIN dbo.[PRODUCT DIM ATC4] h ON h.[ATC4 KEY] = g.[ATC4 KEY]

                INNER JOIN dbo.[ATC3] i ON i.[ATC3 KEY] = h.[ATC3 KEY]

        WHERE e.[Product NAME] = ''' + @itemname + '''

        ) A

       

        UPDATE .dbo.[Data Validation Attribute Items]

        SET [ATC3] = @atc3names

        WHERE [Comparison NAME] = ''Product''

                AND [Data Source NAME] = ''' + @source + '''

                AND [Item NAME] = ''' + @itemname +''''

        EXEC (@atc3names)

        SET @atc3names  = '';

FETCH NEXT FROM c4 INTO @comparison, @supplier, @source, @itemname

END

CLOSE c4

DEALLOCATE c4

GO

Find an object in a SQL Server Instance (across databases)

Following block generates a concatenated string for a set of selected databases defined in the sub query.
DECLARE @command VARCHAR(max) ;
SELECT @command = coalesce (@command + 'union' , ' ') +
'    SELECT ''' + [DBName] + '''AS DBName,
    [type_desc] AS ObjectType
    FROM ['+ [DBName] + '].sys.objects
    WHERE [name] = ''ATC4''
'
FROM (SELECT TOP 1000 name AS DBName FROM master.sys.sysdatabases) d
EXEC (@command)

sp_MSforeachdb is an undocumented stored procedure, which can be used to run sql statements across databases in a SQL Server instance.
DECLARE @command varchar(1000)
SELECT @command =
'    USE [?] ; SELECT
    DB_NAME() AS DBName, [type_desc] AS ObjectType
    FROM sys.objects
    WHERE name = ''ATC4''
'
EXEC sp_MSforeachdb @command ;

Following cursor can also be used to execute SQL Statements across databases in a SQL Server instance.
DECLARE @DB_name varchar(1000)
DECLARE cur CURSOR LOCAL for
    SELECT name FROM master.sys.databases
OPEN cur
FETCH NEXT FROM cur into @DB_name

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC ('SELECT
    DB_NAME() AS DBName, [type_desc] AS ObjectType
    FROM [' + @DB_name + '].sys.objects
    where name = ''ATC4''
    ')
    FETCH NEXT FROM cur INTO @DB_name
END
CLOSE cur
DEALLOCATE cur

Generate date range (Year and Month)

Following  T-SQL query can be used to generate Month Names and Year Names within a given period of time.

DECLARE
    @StartDate DATETIME,
    @EndDate   DATETIME;
--Define start date and end date
SELECT @StartDate = '2013-04-01', @EndDate = '2014-03-01';
WITH cteDates AS (
    SELECT TOP (
        DATEDIFF (mm, @StartDate, @EndDate) + 1)
        DATEADD( mm, DATEDIFF(mm,0, @StartDate ) + (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1),0
     ) AS MonthDate
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
)
SELECT
    YEAR(MonthDate) AS [Year],
    MONTH(MonthDate) AS [Month]
FROM cteDates

Ref:http://stackoverflow.com/questions/4181286/t-sql-select-get-all-months-within-a-range-of-years

View meta data - system objects - SQLServer

View list of constraints for a specific table
Select table_catalog, table_name, constraint_name
from INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
where table_name = 'area' order by table_name asc

View constraint names and table name for a specific column within the DB
Select table_catalog, table_name, column_name, constraint_name
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
where column_name = 'product-pack key' order by table_name asc

Remove Duplicate Records

Following set of codes remove duplicate records from a SQL Server table, based on one or more unique fields.

WITH NewDataSet AS
( SELECT s.*, DENSE_RANK() OVER (PARTITION BY s.productid order by newID()) as r
FROM isuradb.dbo.Products s
)
SELECT r FROM NewDataSet
DELETE FROM NewDataSet WHERE r <> 1

DENSE_RANK() function returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.

Pentaho - Put a file with FTP

Following Kettle job was design to generate survey  result file and statistic file. This job loop through several folders and export output into a csv file, then send it to a FTP site.

 In FTP step, IP, port, username and password need to be give.

 In file tab,a local directory is needed to be given instead of a file path or mapped directory. In wildcard area, file name or regular expression to pick up the right files needed to be given.

Reset all sequences' current values + PostgreSQL

Following function allows to reset starting values of all sequences in particular PostgreSQL schema. In data migration process, this function can be used to reset all sequence values after importing data to destination database.
CREATE OR REPLACE FUNCTION seq_update()
  RETURNS void AS
$BODY$
Declare
tab1 varchar;
col1 varchar;
seqname1 varchar;
maxcolval integer;
ssql varchar;
BEGIN
FOR tab1, col1, seqname1 in Select distinct constraint_column_usage.table_name, 
constraint_column_usage.column_name,
replace(replace(columns.column_default,'''::regclass)',''),'nextval(''','')
 From information_schema.constraint_column_usage, information_schema.columns
 where constraint_column_usage.table_schema ='public' AND 
   columns.table_schema = 'public' 
    AND columns.table_name=constraint_column_usage.table_name
AND constraint_column_usage.column_name = columns.column_name
AND columns.column_default is not null 
AND constraint_column_usage.table_name not in ('user', 'usermodulespages')
--AND constraint_column_usage.table_name = 'role'
order by 1 
LOOP
ssql := 'select max(' || col1 || ') from ' || tab1 ;
RAISE NOTICE 'SQL : %', ssql;
execute ssql into maxcolval;
RAISE NOTICE 'max value : %', maxcolval;
EXECUTE 'alter sequence ' || seqname1 ||' restart  with ' || maxcolval;
END LOOP;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
Following command can be used to run the function
select * from seq_update()

SQL Query Optimization Techniques

1) SQL query became fast if you  select actual columns instead of selecting all values (*)
2) HAVING clause is used to filter the rows after all the rows are selected. It is just like a filter. Do not use HAVING clause for any other purposes. 
For Example: Write the query as
SELECT subject, count(subject) FROM student_details WHERE subject != 'Science' AND subject != 'Maths' GROUP BY subject;
Instead of:
SELECT subject, count(subject) FROM student_details GROUP BY subject HAVING subject!= Vancouver' AND subject!= 'Toronto';


3) Try to minimize the number of subquery block in your query. 
For Example: Write the query as
SELECT name FROM employee WHERE (salary, age ) = (SELECT MAX (salary), MAX (age) FROM employee_details) AND dept = 'Electronics'; 
Instead of:
SELECT name FROM employee WHERE salary = (SELECT MAX(salary) FROM employee_details) AND age = (SELECT MAX(age) FROM employee_details) AND emp_dept = 'Electronics';


4) Use operator EXISTS, IN and table joins appropriately in your query. 
    a) Usually IN has the slowest performance. 
    b) IN is efficient when most of the filter criteria is in the sub-query. 
    c) EXISTS is efficient when most of the filter criteria is in the main query.
For Example: Write the query as
Select * from product p where EXISTS 
(select * from order_items o where o.product_id = p.product_id)
Instead of:
Select * from product p where product_id IN (select product_id from order_items


5) Use EXISTS instead of DISTINCT when using joins which involves tables having one-to-many relationship. For Example: Write the query as
SELECT d.dept_id, d.dept FROM dept d 
WHERE EXISTS ( SELECT 'X' FROM employee e WHERE e.dept = d.dept);
Instead of:
SELECT DISTINCT d.dept_id, d.dept FROM dept d,employee e WHERE e.dept = e.dept;


6) Use of WHERE clause.
Symbol operator: For Example: Write the query as
SELECT id, first_name, age FROM student_details WHERE age > 10;
Instead of:
SELECT id, first_name, age FROM student_details WHERE age != 10; 


Wildcard vs sub-string: Write the query as:
SELECT id, first_name, age FROM student_details WHERE first_name LIKE 'Chan%';
Instead of:
SELECT id, first_name, age FROM student_details WHERE SUBSTR(first_name,1,3) = 'Cha';


Write the query as:
SELECT id, first_name, age FROM student_details WHERE first_name LIKE NVL ( :name, '%');
Instead of:

SELECT id, first_name, age FROM student_details WHERE first_name = NVL ( :name, first_name);

Write the query as:

SELECT id, first_name, age FROM student_details WHERE first_name LIKE NVL ( :name, '%');

Instead of:

SELECT id, first_name, age FROM student_details WHERE first_name = NVL ( :name, first_name); 

Write the query as:

SELECT product_id, product_name FROM product 

WHERE unit_price BETWEEN MAX(unit_price) and MIN(unit_price)

Instead of:

SELECT product_id, product_name FROM product WHERE unit_price >= MAX(unit_price) 

and unit_price <= MIN(unit_price) 

Write the query as:

SELECT id, name, salary FROM employee WHERE dept = 'Electronics' AND location = 'Bangalore';

Instead of:

SELECT id, name, salary FROM employee WHERE dept || location= 'Electronics Bangalore';

Use non-column expression on one side of the query because it will be processed earlier.

Write the query as:

SELECT id, name, salary FROM employee WHERE salary < 25000;

Instead of:

SELECT id, name, salary FROM employee WHERE salary + 10000 < 35000;

Write the query as:

SELECT id, first_name, age FROM student_details WHERE age > 10;

Instead of:

SELECT id, first_name, age FROM student_details WHERE age NOT = 10;



7) Utilize Union instead of OR
Indexes lose their speed advantage when using them in OR-situations
Write the query as:

SELECT * FROM TABLE WHERE COLUMN_A = 'value' OR COLUMN_B = 'value'

Instead of:

SELECT * FROM TABLE WHERE COLUMN_A = 'value'
UNION
SELECT * FROM TABLE WHERE COLUMN_B = 'value'

8) Use DECODE to avoid the scanning of same rows or joining the same table repetitively. DECODE can also be made used in place of GROUP BY or ORDER BY clause.

For Example: Write the query as

SELECT id FROM employee WHERE name LIKE 'Ramesh%' and location = 'Bangalore';

Instead of:

SELECT DECODE(location,'Bangalore', id, NULL) id FROM employee WHERE name LIKE 'Ramesh%';

10) To store large binary objects, first place them in the file system and add the file path in the database.

11) To write queries which provide efficient performance follow the general SQL standard rules.

     a) Use single case for all SQL verbs

     b) Begin all SQL verbs on a new line

     c) Separate all words with a single space 

     d) Right or left aligning verbs within the initial SQL verb

Reference: http://beginner-sql-tutorial.com/sql-query-tuning.htm

http://hungred.com/useful-information/ways-optimize-sql-queries/