Sunday, 23 December 2012

How to Generate Auto Increment IDs

DECLARE @Increment INT=1
DECLARE @NewProjectCode VARCHAR(100)

WHILE @Increment<100
SET @NewProjectCode='EMP'+ Right('0000'+CAST(@Increment as VARCHAR(4)),4)

PRINT @NewProjectCode
SET @Increment=@Increment+1

Wednesday, 12 December 2012

How to get random record from table in SQL

To get random record from table we use NEWID()function. Example

USE AdventureWorks

SELECT TOP 1 * FROM Production.Document

NEWID() is used to generate UNIQUEIDENTIFIER

Thursday, 5 July 2012

Send Mail From Database Sever using gmail

Send mail from SQL Database Server using following steps:

Step 1- Select Mananagement section in choose Database Mail

Step 2- Right-Click on Database Mail and choose Configure Database Mail menu
Step 3- Database Mail Configuration Wizard will open click on Next button.
Step 4- Select Configuration Task and select Set up Database Mail by performing the following tasks and  click on next button.

Step 5- Create New Profile by entering Profile name and Description and click on Add Button.

Step 6- Add Account to Profile, add Account Name to My Gmail Account and click on New Account Button.
Step 7- New Database Mail Account dialog box will open enter your gmail email address and Server name to and port 587. In SMTP Authentication select Basic authentication and enter your gmail credentials and click ok.

Step 8-Your new profile is created select public check box and set default profile yes and click on Next button.

Step 9- Configure System Parameters if you want otherwise remain it default and click on Next button.
Step 10-In this section your Database Mail Configuration Wizard Completed and click on Finished Button.
Step 11- Now close the Wizard after successful completion of Database Configuration Wizard.

Step 12- To test mail configuration right-click on Database Mail and choose Send Test E-Mail Menu

Step 13- Select Database Mail Profile and enter other detail and click on Send Test E-Mail button.

Tuesday, 12 June 2012

What is the difference between Primary Key and Unique Key

  • In a table there can be only one column on which we implement Primary Key Constraint while we can implement Unique Key Constraints on more than one column.
  • There can be no NULL value in column having Primary Key Constraint while NULL value can be allow in column having Unique Key Constraint.
  • In column having Primary Key Constraint Cluster Index created by default while in column having Unique Key Constraint NonCluster Index creates by default.

Monday, 28 May 2012

Indexes in SQL

An index is an internal table structure that SQL Server uses to provide quick access to rows of a table based on the values of one or more columns. Indexes in SQL Server are like the indexes at the back of a book, which help in locating content.

Advantages of Using Indexes
The primary purpose of an index is to provide faster access to data pages. Rather than scanning each page for the required data, the server will scan the index, get the address of the data storage location, and directly access the information. Indexes are also used as a mechanism of enforcing data uniqueness. Creating a unique index will force unique values into the columns. An index speeds up the processing of queries that use join or other clauses, like ORDER BY or GROUP BY, by allowing faster access to data. An indexes are said to have the following advantages:
·         Improve the speed of the execution of queries.
·         Enforce uniqueness of data.
·         Speed up joins between tables. 

Disadvantages of Using Indexes
In effectively designed, indexes improve the performance of queries. But it is not wise to index every column of table. You need to consider a few parameters while creating indexes, like:
·         It takes time to create an index.
·         Each index created requires space to store data along with the original data source- the table.
·         An index get updated each time the data is modified.

Types of Indexes
There are two types of indexes
·         Clustered Index
·         Non Clustered Index
Clustered Index
In a clustered Index:
·         The data is physically sorted.
·         Only one clustered index can be created per table, so you should build it on attributes that have a high percentage of unique and are not modified often.
How Clustered Indexes Work
In a cluster index, data is sorted at the level of the B-tree. The data page of a table are like folders stored in an alphabetical order in the filing, and the rows of data are like the document stored in folders.
NonClustered Index
 In nonclustered index:
·         The physical order of the rows is not the same as the index order.
·         Nonclustered indexes are typically created on columns used in joins and WHERE clauses, and whose values may be modified frequently.
·         SQL Server creates nonclustered index by default when the CREATE INDEX command is given
·         There can be as many as 259 (SQL Server 2005) nonclustered indexes per table.

How Clustered Indexes Work
SQL Server creates a nonclustered index by default. The data is present in a random order, but the logical ordering is specified by the index. The data rows may be randomly spread throughout a table.The nonclustered index tree contains key in a sorted order, with the leaf level of the index containing the pointer to the data page and the row number in the data page.

Indexes and Heap Structures
SQL Server supports indexes defined on any column in a table, including computer columns. If a table does not have any clustered index, data is not sorted in a particular order. This structure is called a heap
Each table in SQL Server is allocated one extent(eight contiguous 8KB pages) in the database file. When this one extent is filled with data, another is allocated to the table. However, these extents are not placed physically next to each other, they are scattered in the database file. Therefore, data access on a heap is slow as compared to tables have been indexed.
Features of Indexes
·         Indexes accelerate queries that join tables and  perform sorting and grouping.
·         Indexes can be used to enforce uniqueness of rows.
·         Indexes are useful on columns in which the majority of data is unique. An index on column containing a large amount of duplicate data is not useful.
·         When you modify the data of an indexed column, the associated index are automatically updates.
·         You require time and resources to maintain indexes. You should not create an index that is not used frequently.
·         A clustered index should be created before a nonclustered index. A clustered index changes the order of rows. A nonclustered index would need to be rebuilt before a clusterd index
·         Typically, nonclustered indexes are created on foreign keys

Guidelines to Create an Index
·         Identify the tables on which the index will be created.
·         Indentify the attribute on which the index will be created.
·         Identify the type of index to be created
·         Identify the name of the index to be created (Prefix an ‘idx’ to the name of the index to help in identifying the index)

Friday, 25 May 2012


We use FOR XML PATH in sql to get comma separated values   from tables have one to many relation.
For example we have two tables tblCategory and tblProduct and one category can have multiple products. Now we want to get all products of a category in comma separated in single columns then we can use FOR XML PATH

DVD Player

Use this query
SELECT     CategoryName, STUFF
                          ((SELECT     ',' + Product .ProductName
                              FROM         tblProduct  Product
                              WHERE     (CategoryID = Category.CategoryID) FOR XML PATH('')), 1, 1, '') AS Products
FROM         tblCategory  Category

Laptop, LCD TV,DVD Player

Sunday, 12 February 2012

DateTime Function in SQL Server

The most commonly used DateTime function in SQL Server are below
  • DAY()
  • MONTH()
  • YEAR()
GETDATE() is very common used method which returns exact date time from the system. It does not accept any parameter. Just call it like simple function.
Example :
Declare @Date datetime
set @Date = (SELECT GETDATE());
Print @Date
Aug 15 2009  9:04PM 
DATEADD() is used to add or subtract datetime. Its return a new datetime based on the added or subtracted interval.
General Syntax
DATEADD(datepart, number, date)
datepart is the parameter that specifies on which part of the date to return a new value. Number parameter is used to increment datepart.
Example :
Declare @Date datetime
set @Date = (SELECT GETDATE());
print  @Date -- Print Current Date
-- Adding 5 days with Current Date
SELECT DATEADD(day, 5,@Date ) AS NewTime
Aug 15 2009  9:19PM
2009-08-20 21:19:15.170
DATEPART() is used when we need a part of date or time from a datetime variable. We can use DATEPART() method only with select command.
DATEPART(datepart, date)
Example :
-- Get Only Year
-- Get Only Month
-- Get Only hour
DATEDIFF() is very common function to find out the difference between two DateTime elements.
DATEDIFF(datepart, startdate, enddate)
Example :
-- Declare Two DateTime Variable
Declare @Date1 datetime
Declare @Date2 datetime
-- Set @Date1 with Current Date
set @Date1 = (SELECT GETDATE());
-- Set @Date2 with 5 days more than @Date1
set @Date2 = (SELECT DATEADD(day, 5,@Date1 ))
-- Get The Date Difference
SELECT DATEDIFF(day, @Date1, @Date2) AS DifferenceOfDay
DATENAME() is very common and most useful function to find out the date name from the datetime value.
-- Get Today
SELECT DATENAME(dw, getdate()) AS 'Today Is'
-- Get Mont name
SELECT DATENAME(month, getdate()) AS 'Month'
Today Is
DAY() is used to get the day from any date time object.
SELECT DAY(getdate()) AS 'DAY'
SELECT MONTH(getdate()) AS 'Month'
SELECT YEAR(getdate()) AS 'Year'
Result :

Monday, 2 January 2012

Self-join in sql with example

Sql equivalent queries in Linq
Set all check boxes inside a div 
Get number of weekend between two dates
Bulk insert in Sql 2008
Get nth highest salary in a table
Use of Temp Table and Split Function

For self join in sql you can try the following example:
Create table employees:
emp_id emp_name emp_manager_id
1 John Null
2 Tom 1
3 Smith 1
4 Albert 2
5 David 2
6 Murphy 5
7 Petra 5
Now to get the names of managers from the above single table you can use sub queries or simply the self join.
Self Join SQL Query to get the names of manager and employees:
select e1.emp_name 'manager',e2.emp_name 'employee'
from employees e1 join employees e2
on e1.emp_id=e2.emp_manager_id
manager employee
John Tom
John Smith
Tom Albert
Tom David
David Murphy
David Petra

In the above self join query, employees table is joined with itself using table aliases e1 and e2. This creates the two views of a single table.
from employees e1 join employees e2
on e1.emp_id=e2.emp_manager_id
Here e.emp_manager_id passes the manager id from the 2nd view to the first aliased e1 table to get the names of managers.

 Explain it with another example

 Let’s illustrate the need for a self join with an example. Suppose we have the following table called emp_location, with both the employee name and their location:
New York
New York
Now, suppose we want to find out which employees are from the same location as the employee named Joe. What we could do is write a nested SQL query (basically a query within another query) like this:
SELECT employee_name
FROM employee
WHERE employee_location in
( SELECT employee_location
FROM employee
WHERE employee_name = "Joe")
A nested subquery for such a simple question is inefficient. Is there a more efficient and elegant solution to this problem?
It turns out that there is – we can use something called a self join. A self join is basically when a join is done on the same table – the best way to think of it is that we have 2 identical copies of the table, and we want to join them based on some predicate. If you need a refresher on join predicates (or just joins in general) then check this link out: Inner vs Outer joins.
Now, the key question is what would be our join predicate in this example? Well, we want to find all the employees who have the same location as Joe – so if we are doing a join we would want to make sure that the location is the same and that the employee name is Joe. So, our join predicate would be where e1.employee_location = e2.employee_location AND employee_name = "Joe". Note that e1 and e2 will represnt the 2 employee tables that we are doing a self join on. Now, here is what the SQL for a self join would look like to solve this problem:
SELECT e1.employee_name
FROM employee e1, employee e2
WHERE e1.employee_location = e2.employee_location
AND e2.employee_name="Joe";
This query will return the names Joe and Jack – since Jack is the only other person who lives in New York like Joe.
Generally, queries that refer to the same table can be greatly simplified by re-writing the queries as self joins. And, there is definitely a performance benefit for this as well.