How to Generate Auto Increment IDs

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

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


PRINT @NewProjectCode
SET @Increment=@Increment+1
END

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

ORDER BY NEWID()

NEWID() is used to generate UNIQUEIDENTIFIER

SELECT  NEWID()

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 smtp.gmail.com 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.

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.

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)

FOR XML PATH IN SQL

FOR XML PATH in SQL

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

tblCategory

CategoryID	CategoryName
1	Electronics
2	Apparels

tblProduct

ProductID	ProductName	Cost	CategoryID
1	Laptop	45000	1
2	LCD TV	40000	1
3	DVD Player	5000	1

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

CategoryName	Products
Electronics	Laptop, LCD TV,DVD Player

DateTime Function in SQL Server

The most commonly used DateTime function in SQL Server are below

• GETDATE()
• DATEADD()
• DATEPART()
• DATEDIFF()
• DATENAME()
• DAY()
• MONTH()
• YEAR()

GETDATE()

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

Result:

Aug 15 2009  9:04PM 

DATEADD()

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

Result:

Aug 15 2009  9:19PM

NewTime

-----------------------

2009-08-20 21:19:15.170

DATEPART()

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.

Syntax

DATEPART(datepart, date)

Example :

-- Get Only Year

SELECT DATEPART(year, GETDATE()) AS 'Year'

-- Get Only Month

SELECT DATEPART(month, GETDATE()) AS 'Month'

-- Get Only hour

SELECT DATEPART(hour, GETDATE()) AS 'Hour

Result:

Year

-----------

2009

Month

-----------

8

Hour

-----------

21

DATEDIFF()

DATEDIFF() is very common function to find out the difference between two DateTime elements.

Syntax

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

Result:

DifferenceOfDay

---------------

5 

DATENAME()

DATENAME() is very common and most useful function to find out the date name from the datetime value.

Example

-- Get Today

SELECT DATENAME(dw, getdate()) AS 'Today Is'

-- Get Mont name

SELECT DATENAME(month, getdate()) AS 'Month'

Result:

Today Is

------------------------------

Saturday

Month

------------------------------

August

DAY()

DAY() is used to get the day from any date time object.

Example:

SELECT DAY(getdate()) AS 'DAY'

Result:

DAY

-----------

15

MONTH()

SELECT MONTH(getdate()) AS 'Month'

Result:

Month

-----------

8

YEAR()

SELECT YEAR(getdate()) AS 'Year'

Result :

Year

-----------

2009

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
Result:

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:

employee

employee_name employee_location Joe New York Sunil India Alex Russia Albert Canada Jack 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.