Tuesday, 20 September 2011

COALESCE in sqlserver


DECLARE @str NVARCHAR(Max)
SELECT @str = COALESCE(@str + ';', '') + columnname FROM tablename
SELECT @str
- No comments:

Tuesday, 13 September 2011

Share image with facebook in asp.net

Design page
------------

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="Admin_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Test Image share with facebook </title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Label ID="lblShare" runat="server" Text="Label"></asp:Label>
    </div>
    </form>
</body>
</html>
------------------------
Code page
-----------------------

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

public partial class Admin_Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string str = "";
        str = " <div class='a2a_kit a2a_default_style'><a class='a2a_button_facebook'></a><a class='a2a_button_twitter'></a><a class='a2a_button_email'></a> </div> <script type='text/javascript'> var a2a_config = a2a_config || {};a2a_config.linkname = 'Capture America';a2a_config.linkurl = 'http://www.floripaweb.net/images/DOTNET%20logo.png'; </script> <script type='text/javascript' src='http://static.addtoany.com/menu/page.js'></script>";
        lblShare.Text = str;
    }
}

- No comments:

How Select a perticular item from checkListBox

"CheckBoxListName".FindByValue("Value").Selected = true;
- No comments:

PIVOT Table Example In Sqlserver


 A Pivot Table can automatically sort, count, and total the data stored in one table or spreadsheet and create a second table displaying the summarized data. The PIVOT operator turns the values of a specified column into column names, effectively rotating a table.
------------------------------------------


create table Test1 ( [No] int, [ID] int, Date Datetime, Value decimal (38,2))
------------------------------------------
insert into Test1 values (1, 1001, '05/01/2009', 101.00)
insert into Test1 values (1, 1001, '05/15/2009', 102.00)
insert into Test1 values (1, 1001, '05/20/2009', 105.00)
insert into Test1 values (2, 1001, '05/01/2009', 41.00)
insert into Test1 values (2, 1001, '05/15/2009', 44.00)
insert into Test1 values (3, 1001, '06/01/2009', 330.00)
----------------------------------------
select * from Test1
--------------------------------------
SELECT [No] , [ID] ,[05/01/2009] ,[05/15/2009] ,[05/20/2009] ,[06/01/2009]
FROM (
SELECT [No], [ID], [Date], [Value]
FROM Test1) up
PIVOT ( sum([Value]) FOR [Date] in ([05/01/2009] ,[05/15/2009] ,[05/20/2009] ,[06/01/2009]) )AS pvt
- No comments:

Friday, 1 July 2011

SQL Tuning/SQL Optimization Techniques

1) The sql query becomes faster if you use the actual columns names in SELECT statement instead of than '*'. 
For Example:
Write the query as
SELECT id, first_name, last_name, age, subject FROM student_details; 
Instead of:
SELECT * FROM student_details;
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) Sometimes you may have more than one subqueries in your main query. 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) Try to use UNION ALL in place of UNION.
For Example: Write the query as
SELECT id, first_name
FROM student_details_class10
UNION ALL
SELECT id, first_name
FROM sports_team;
Instead of:
SELECT id, first_name, subject
FROM student_details_class10
UNION
SELECT id, first_name
FROM sports_team; 
7) Be careful while using conditions in WHERE clause.
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;
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 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= 'ElectronicsBangalore';
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;
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%';


9) To store large binary objects, first place them in the file system and add the file path in the database.
10) 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
- No comments:

Monday, 13 June 2011

Find Nth Highest Salary of Employee – Query to Retrieve the Nth Maximum value

You can change and use it for getting nth highest salary from Employee table as follows
SELECT TOP 1 salary FROM (SELECT DISTINCT TOP n salaryFROM employee ORDER BY salary DESC) aORDER BY salary
where n > 1 (n is always greater than one)
with cte as
(
select salary ,ROW_NUMBER() over (order by salary desc) as 'rownum'
from employee 
)

select salary from cte where rownum = n  (n is always greater than one)
- No comments:

Count Duplicate Records – Rows

SELECT YourColumn, COUNT(*) TotalCount FROM YourTable GROUP BY YourColumn HAVING COUNT(*) > 1 ORDER BY COUNT(*) DESC
For Delete
------
delete 
FROM YourTable
WHERE ID NOT IN (SELECT MAX(ID) FROM YourTable GROUP BY YourColumn) and YourColumn=0 

delete
FROM Exam_Backup
WHERE ID NOT IN (SELECT MAX(ID) FROM Exam_Backup GROUP BY QuestionNo) and optionName=0

WITH CTE

WITH CTE (last_name,first_name,dcount) 
AS
(
SELECT last_name,first_name ,
ROW_NUMBER() OVER (PARTITION BY last_name,first_name ORDER BY last_name) AS dcount
FROM tbl_member 
)
SELECT * FROM CTE WHERE dcount > 1
- No comments:
Subscribe to: Posts (Atom)

Get all non-clustered indexes

DECLARE cIX CURSOR FOR     SELECT OBJECT_NAME(SI.Object_ID), SI.Object_ID, SI.Name, SI.Index_ID         FROM Sys.Indexes SI             ...