Get Records between two dates in MS SQL

Here query show how to select record between two dates in sql server.
select * from ContentMst
where c_date> = '2012-06-01' and c_date <= '2012-07-01'
order by c_date desc

Read More

How to insert record from one database to other database server

Here example show i am retrieving record from one server(DBServer2) and inserting in other server(DBServer1).
insert into Category(DBServer1)
select * from [DBServer2].[DatabaeName].[dbo].Category
Note: Both database server should be linq server otherwise it will give error.

Read More

Get duplicated record from sql server table

Here query describes how to find duplicate record from a table in sql server

SELECT User_ID FROM tbl_Member
GROUP BY User_ID
HAVING count( * ) > 1

Thanks & Regards
Santosh Singh

Read More

select data from one database and insert into another table

insert into tblMonthMst
select * from EMPMain.[dbo].[tblMonthMst]

Read More

Sql Server query Optimization

Optimization in Practice

Example 1:
I want to retrieve the name and salary of the employees of the R&D department.


Original:
Query : Select * From Employees
In Program : Add a filter on Dept or use command : if Dept = R&D--

Corrected :
Select Name, Salary From Employees Where Dept = R&D--

In the corrected version, the DB filters data because it filters faster than the program.
Also, you only need the Name and Salary, so only ask for that.
The data that travels on the network will be much smaller, and therefore your performances will improve.

Example 2 (Sorting):

Original:
Select Name, Salary
From Employees
Where Dept = 'R&D'
Order By Salary

Do you need that Order By Clause? Often, people use Order By in development to make sure returned data are ok; remove it if you don't need it.
If you need to sort the data, do it in the query, not in the program.

Example 3:
Original:
For i = 1 to 2000
Call Query : Select salary From Employees Where EmpID = Parameter(i)

Corrected:
Select salary From Employees Where EmpID >= 1 and EmpID <= 2000

The original Query involves a lot of network bandwidth and will make your whole system slow.
You should do as much as possible in the Query or Stored Procedure. Going back and forth is plain stupid.
Although this example seems simple, there are more complex examples on that theme.
Sometimes, the processing is so great that you think it's better to do it in the code but it's probably not.
Sometimes, your Stored Procedure will be better off creating a temporary table, inserting data in it and returning it than going back and forth 10,000 times.
You might have a slower query that saves time on a greater number of records or that saves bandwidth.

Example 4 (Weak Joins):
You have two tables Orders and Customers. Customers can have many orders.

Original:
Select O.ItemPrice, C.Name
From Orders O, Customers C

Corrected:
Select O.ItemPrice, C.Name
From Orders O, Customers C
Where O.CustomerID = C.CustomerID

In that case, the join was not there at all or was not there on all keys. That would return so many records that your query might take hours.
It's a common mistake for beginners.

Corrected 2:
Depending on the DB you use, you will need to specify the Join type you want in different ways.
In SQL Server, the query would need to be corrected to:

Select O.ItemPrice, C.Name
From Orders O INNER JOIN Customers C ON O.CustomerID = C.CustomerID

Choose the good join type (INNER, OUTER, LEFT, ...).
Note that in SQL Server, Microsoft suggests you use the joins like in the Corrected 2 instead of the joins in the Where Clause because it will be more optimized.

Example 5 (Weak Filters):
This is a more complicated example, but it illustrates filtering at its best.
We have two tables -- Products (ProductID, DescID, Price) and Description(DescID, LanguageID, Text). There are 100,000 Products and unfortunately we need them all.
There are 100 languages (LangID = 1 = English). We only want the English descriptions for the products.

We are expecting 100 000 Products (ProductName, Price).

First try:
Select D.Text As ProductName, P.Price
From Products P INNER JOIN Description D On P.DescID = D.DescID
Where D.LangID = 1

That works but it will be really slow because your DB needs to match 100,000 records with 10,000,000 records and then filter that Where LangID = 1.
The solution is to filter On LangID = 1 before joining the tables.

Corrected:
Select D.Text As ProductName, P.Price
From (Select DescID, Text From Description Where D.LangID = 1) D
INNER JOIN Products P On D.DescID = P.DescID

Now, that will be much faster. You should also make that query a Stored Procedure to make it faster.

Example 6 (Views):
Create View v_Employees AS
Select * From Employees

Select * From v_Employees

This is just like running Select * From Employees twice.
You should not use the view in that case.

If you were to always use the data for employees of R&D and would not like to give the rights to everyone on that table because of salaries being confidential,
you could use a view like that:

Create View v_R&DEmployees AS
Select Name, Salary From Employees Where Dept = 1
(Dept 1 is R&D).

You would then give the rights to View v_R&DEmployees to some people and would restrict the rights to Employees table to the DBA only.

That would be a possibly good use of views.

Read More

Join Three Table

How to join three table
SELECT e.emp_name, d.DeptName
FROM Emp e INNER JOIN DeptName d
ON e.emp_id = d.emp_id JOIN Sal s
ON d.dept_id = s.dept_id


SELECT a.au_lname, a.au_fname, t.title
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id JOIN titles t
ON ta.title_id = t.title_id

select
r.recipe_name,
i.ingredients_list,
p.products_list
from
recipes r,
ingredients i,
products p
where
r.RecipeID = i.IngreRecipeID and
i.IngreProductID = p.ProductID
;

Read More

how to use Sql server Function

Sql server Function With Arguments

here it is simple example how to use function n sql server.
1.Create function
2.use this function where required.

CREATE FUNCTION AddTwoNumber(@Num1 Decimal(6,2),
@Num2 Decimal(6,2))
RETURNS Decimal(6,2)
BEGIN
DECLARE @Result Decimal(6,2)
SET @Result = @Num1 + @Num2
RETURN @Result
END;
GO


PRINT MyDbName.dbo.AddTwoNumber(100, 200);

Read More

Sp for Creating db

Stored procedure for creating database.
When you want to create database at run time using c# .net here is stored procedure execute this and pass database name as parameter.

Create proc [dbo].[usp_Database]
(
@dbName varchar(50)
)
as

IF NOT EXISTS (SELECT 'True' FROM INFORMATION_SCHEMA.SCHEMATA WHERE CATALOG_NAME = @dbName)
-- DROP DATABASE ' + @dbName + '

DECLARE @device_directory NVARCHAR(520)
SELECT @device_directory = SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
FROM sys.database_files
WHERE (name = N'master')

EXECUTE (N'CREATE DATABASE ' + @dbName + '
ON
(NAME = ' + @dbName + ',
FILENAME = ''' + @device_directory + '' + @dbName + '.mdf'',
SIZE = 50MB,
MAXSIZE = 125MB,
FILEGROWTH = 10MB)
LOG ON
(NAME = ''NorthwindBulkLog'',
FILENAME = ''' + @device_directory + '' + @dbName + '.ldf'',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB)')

Read More

How to check Record Exist in table before insert

Here i am explain you check if record is not exist in database then insert otherwise update the table.

CREATE PROCEDURE [dbo].[uspInst_Addupdate]
(
@InstId int,
@Inst varchar(50),
)
AS
BEGIN
IF @InstId=0
BEGIN
IF NOT EXISTS(SELECT * FROM InstMaster WHERE Inst= @Inst)
INSERT INTO InstMaster(Inst) VALUES(@Inst)
END
ELSE
BEGIN
UPDATE InstMaster
SET Inst= @Inst WHERE (Inst= @Inst)
END
END

Thanks & Regards
Santosh

Read More

Display all related data in comma separated list sql server

Concetenate the two fields and display the records with comma separated string in one field.

SELECT p_scrid,
SUBSTRING(
(
SELECT (' ,' + screen_name)
FROM tbl_SCRMst t2
WHERE t1.p_scrid = t2.p_scrid
ORDER BY
p_scrid,
screen_name
FOR XML PATH( '')
),3,1000)
FROM tbl_SCRMst t1
GROUP BY p_scrid

Thanks & Regards
Santosh

Read More

Single Stored Procedure for Insert and update record

It good idea to use a single stored proceude instead of write two stored procedure for insert and update.So i am here explainig how to do this.

here we can check if EnquiryId ==0 then insert otherwise update.

Create PROCEDURE [dbo].[uspEnquiry_AddEdit]
(
@EnquiryId bigint
, @EnquiryNo varchar(20) output
, @ClientId bigint
, @ContactPerson varchar(50)
, @Desig varchar(50)
, @Mobile varchar(15)
, @Phone varchar(50)
, @EmailId varchar(100)

)
AS
DECLARE @Message varchar(100)
IF(@EnquiryId=0)
BEGIN
IF EXISTS(SELECT * FROM Enquiry WHERE EnquiryNo=@EnquiryNo)
SET @Message='Record Exists In Database...!'

ELSE
BEGIN
INSERT INTO Enquiry
(
[EnquiryNo]
,[ClientId]
,[ContactPerson]
,[Desig]
,[Mobile]
,[Phone]
,[EmailId]
)
VALUES
(
@EnquiryNo
,@ClientId
,@ContactPerson
,@Desig
,@Mobile
,@Phone
,@EmailId
)
SELECT @EnquiryId = @@IDENTITY;
END
END
ELSE
BEGIN
UPDATE Enquiry
SET [EnquiryNo] = @EnquiryNo
,[ClientId] = @ClientId
,[ContactPerson] = @ContactPerson
,[Desig] = @Desig
,[Mobile] = @Mobile
,[Phone] = @Phone
,[EmailId] = @EmailId
WHERE EnquiryId = @EnquiryId

END
SELECT @EnquiryId


Thanks & Regards
Santosh Singh

Read More

Find tables that contain a certain field in database

Some times user need to get information about a table field,means how many table contains this field. A table field eg 'xyz' have relationship with many table and you want to see all table.Write below query.

select * from information_schema.columns where column_name = 'xyz'

Thanks & Regards
Santosh

Read More