Find Missing Foreign Key References

If you database column naming convention says all foreign key columns must end with 'ID' then this is the query for you to find missing foreign key reference. E.g. PRIZE_ID means Foreign Key reference to PRIZE table.

SELECT 
    colT.TABLE_NAME , 
    colT.COLUMN_NAME 
FROM 
    INFORMATION_SCHEMA.COLUMNS AS colT 
WHERE 
    COLUMN_NAME LIKE '%id'
    AND 
    (colT.TABLE_NAME + colT.COLUMN_NAME) NOT IN
     ( SELECT 
          KOM.TABLE_NAME + KOM.COLUMN_NAME 
      FROM 
          INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS KOM)

Clear MSSQL Server Query Cache

NOTE: Strictly not for production servers and I mean it.

If you are first time optimizing query or choosing right index then you must know that SQL Server gives result from it's caches if query was already fired once. This is very useful feature but this behavior may tempt you to take wrong decision if this happens outside your knowledge.

You must see:

1. DBCC DROPCLEANBUFFERS -- Cleans temp buffers and dirty data.  You may want to use CHECKPOINT before this command. http://msdn.microsoft.com/en-us/library/ms187762.aspx

2. DBCC FREEPROCCACHE -- Use this to clear execution plans http://msdn.microsoft.com/en-us/library/ms174283.aspx 

I've not given FREEPROCCACHE with parameters which you may or may not be interested in but you should check out the link of microsoft site given there.

NOTE: Strictly not for production servers and I mean it.

Database Indexes: tips

Database Indexes are sometime tricky. But it's easy to fall into traps by following simple advices. Here are some common tips (specific to t-sql and it may or may not apply to other RDBMS) listed here:

Sample Database Table:

Customer_Id	Primary Key, Clustered Index, Numeric
Customer_Name	Part of multi-column index on "Customer_Name, Customer_Mobile, Customer_Location", Varchar
Customer_Mobile	Indexed, Varchar
Customer_Location	Part of multi-column index on "Customer_Name, Customer_Location"

1. Index is not used, when columns is in function

Example of Bad:
Select * from customer
where IsNull(Customer_Name,'!true') = IsNull(@CustomerName, '!true')

Good query:
Select * from customer
where Customer_Name= @CustomerName
OR (Customer_Name is null AND @CustomerName is null)

2. On DataType mismatch,

Example of Bad:
Select * from customer
where Customer_Mobile = 9900114477

Select * from customer
where Customer_Id = '2'

Good query:

Select * from customer
where Customer_Mobile = '9900114477'

Select * from customer
where Customer_Id = 2

3. Using Like on Wrong End:

Example of Bad:

Select * from customer
where Customer_Name LIKE '%ram'

Good Option:

If you are gonna use put % always at first, use reverse Indexing

4. : Multi-column Index is created with wrong sequence of columns

Example of Bad: 

Select * from Customer 

Where Customer_Name = 'CN' and Customer_Location = 'CL'

Good Option:

On Index is on columns: 

1. Customer_Name

2. Customer_Mobile 

3. Customer_Location

Which is only used when one of these is in your where clause:

1. Customer_Name

2. Customer_Name and Customer_Mobile 

3. All three, Customer_Name, Customer_Mobile and Customer_Location

So only option is to modify Index Or Add new index on Customer_Location 

Rounding Functions (with T-SQL)

Everybody knows floor and ceiling function.
They are same as in any other API.
Hope you already know those terms and how to use it.
And as you know floor and ceiling functions, you must be familiar the 'round' function.
Let that roam around 'round' first.
Round accepts two arguments, first as the number which you want to round and the other as to the decimal point upto you want to round.
round( input number, decimal point to round)
So input output will be as show as below:
round(136.84,2) -> 136.84
round(136.84,1) -> 136.80
round(136.84,0) -> 137.00
round(136.84,-1) -> 140.00
And you can go so on...

Indian currency is Rupee (INR). All transactions are stored in rupee.
Paise is 100th part of Rupee. And when we round money, we have to round to 25 paise or 50 paise. So simple round function shown above will never work. You have to write you own. Wait for a while , I will tell you what you can write.

Take another case. Suppose you are a shopkeeper and in order to be nice to your customer, you don't want to round merely 100.10 to 101 or 100.5. On the other hand, you cannot leave 99 paise on the a product of 97.99 rupees. So as a shop keeper, you will always love to provide rule for rounding. i.e. Round to lower digit upto 70 paise and round upward if it's more than 70 paisa.
Logic seems bigger but this can be achieved by just one multiplication, addition and devision.

For T-SQL,

declare @rnd_amt decimal(6,2)
declare @devide decimal(11,9)
set @rnd_amt = 1 -- Rounding amount.. by Rupee 1. 100 should be multiple of @rnd_amt.

-- Use any one of these three value of @devide according to you need i.e. round, floor, ceiling
set @devide = 0.00 -- Round to Floor
set @devide = 0.9999999 -- Roudn to Ceiling
set @devide = 0.70 -- Nearest Rounding. Devide. In above example it's 0.70 i.e.70% . So 70 paise or more than that will be rounded upward.
print floor(58.00001/@rnd_amt + @devide)*@rnd_amt

e.g.
print floor(58.7455698/1+.70)*1

Easy huh!!

How to find duplicate rows in a table? (For SQL Server 2005)

There are two possibilities,

1. Take account of all the rows: Your table doesn’t have any primary key and you want to check for duplicates.
2. Only for selected rows: Your table is having a primary key, so that will always be unique. Here you want to check for other columns.

They query is very easy for the first possibility.
Suppose a table ‘mytable’ has four columns a, b, c, d. (No Primary key)

I.e. select a,b,c,d from mytable group by a,b,c,d having count(*) > 1

But, the query is long for the second option.

Suppose a table ‘mytable’ has four columns a, b, c, d (a and b is composite primary key)

Then the query would be,

select T1.a,T1.b,T1.c,T1.d
from
mytab T1, mytab T2
where
(T1.c=T2.c or (T1.c is null and T2.c is null))
and (T1.d=T2.d or (T1.c is null and T2.c is null))
and T1.A != T2.A
and T1.b != T2.b
order by T1.c,T1.d

OR (this is same as above)

select t1.a,t1.b, t1.c,t1.d
from
mytab T1 inner join mytab T2
on
(T1.c=T2.c or (T1.c is null and T2.c is null))
and (T1.d=T2.d or (T1.c is null and T2.c is null))
where
T1.A != T2.A and T1.b != T2.b
order by t1.c,t1.d

How to work at same time with T-SQL Stored Procedure and Coding standards

I was having real bad time by modifying the T-SQL procedures according to our coding standards. It's because the editor which comes with SQL Sever 2005, doesn't provide auto-indent or auto-format functionality.

Another thing! Command to read stored i.e. SP_HelpText doen't read TABs (or there is a problem in my way of copy-pasting). So all my hardwork with go in vain if I don't take care about that. And the editor allows you to use TABs freely and, without mistake, it puts the tab character instead of bunch of spaces. 'Untabbify' functionality is there in the editor but I don't know why the hell it's not working everytime.

So currently, as I have to bear with this hell, I am following a new approach.

That is, first develop procedure in SQL server editor (fast for development) and then go to scintilla and format the shit. I got many problem with scintilla but all were solved when I downloaded MSI from http://gisdeveloper.tripod.com/scite.html This MSI comes with some good extensions and customized preferences. This preference are set exactly the same which I wanted (i.e. No tabs, tab size 4, font preferences etc).

So life is not so good because there is not directly 'auto-format' function, but the above approach is working until Microsoft really does something :)