SQL Stuff

What does means data tuning? It is all about database performance. How you write your code for database, it is depend on. There is lots of trick to improve your performance. How you design your database that is also matters performance . How to Improve performance of SQL?    Some Of Points To Be Taken Care While Design or Develop SQL Database. Avoid number-to-character conversions because numbers and characters compare differently and lead to performance downgrade. While using SELECT statement, only fetch whatever information is required and avoid using * in your SELECT queries because it would load the system unnecessarily. Create your indexes carefully on all the tables where you have frequent search operations. Avoid index on the tables where you have less number of search operations and more number of insert and update operations. A full-table scan occurs when the columns in the WHERE clause do not have an index associated with them. You can avoid a ...

This post is Regarding Row_Number,RANK and Denserank. Row_Number:= Using this function one can retuen unique id to each row. Syntex: SELECT Col_Value, ROW_NUMBER() OVER (ORDER BY Col_Value) AS 'RowID' FROM Table; Result: Col RowID A 1 A 2 A 3 B 4 B 5 C 6 C 7 RANK:-This function will return a unique number to each distinct row, but it leaves a gap between the groups. Syntex:- SELECT Col_Value, Rank() OVER (ORDER BY Col_Value) AS 'RowID' FROM Table; Result:- Col RowID A 1 A 1 A 1 B 4 B 4 C 6 C 6 DENSE_RANK:-This function is similar to Rank with only difference, this will not leave gaps between groups. it generates a unique id for each group and without repetition. Syntex:- SELECT Col_Value, DENSE_RANK() OVER (ORDER BY Col_Value) AS 'RowID' FROM Table; Result:- Col RowID A 1 A 1 A 1 B 2 B 2 C 3 C 3