The differences between LEN and DATALENGTH in SQL Server

The differences between LEN and DATALENGTH in SQL Server

LEN
Returns the number of characters, rather than the number of bytes,
of the given string expression, excluding trailing blanks.

DATALENGTH
Returns the number of bytes used to represent any expression.

So what does that mean? It means that the LEN function will first right
trim the value and then give you a count of the charaters, the DATALENGTH
function on the other hand does not right trim the value and gives you the
storage space required for the characters.

Take a look at this example
declare @v nchar(5)
select @v ='ABC  '


select len(@v),datalength(@v)
The output for len is 3 while the output for datalength =10. The reason that
datalength returns the value 10 is because nvarchar uses 2 bytes to store 1
character by using unicode while varchar is using ascii which requires 1 byte
per charaters

How to remove leading 0 in SQL

Remove leading 0 in SQL:

select substring(ColumnName, patindex('%[^0]%',ColumnName), len(ColumnName))

Last Executed Sql Queries

To day I write a sql query  which is more then 100 lines.But without saving the query I cancel the query window. Now write the sql again boaring work.

After searching in google I find the query. This will display the list of all query executebin sql server recently.

from here I copy my sql and I have save the time for re-writing the sql.

SELECT deqs.last_execution_time AS [Time], dest.text AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

Create Table from an existing table in SQL

SELECT * INTO dbo.newtable FROM dbo.oldtable WHERE 1 = 0;

Note that this creates the same column structure (including an IDENTITY column if one exists)
but it does not copy any indexes, constraints, triggers, etc.

If you want to make a copy of the table including all of the data, then leave out the WHERE clause.

SELECT * INTO dbo.newtable FROM dbo.oldtable

Sql Query Optimization

Sql Query Optimization in MS SQL Server To Improve Performance of ASP.NET web applications.

Data retrieval FROM database in asp.net applications is a most common task and writing optimized queries can have a huge impact on perfirmance of application if database is huge or containing thousands of records in table.

Here i am mentioning few tips to remember while writing sql queries for optimal performance.

1. Use columns name instead of * in SELECT statements.

Use:
SELECT column1,column2,column3 FROM TableName

Insted of
SELECT * FROM TableName

by doing so we reduce 1 extra step of converting * into column names by sql server when query is processed.


2. Always create primary key in table.

Making primary key in table ensures that table has a clustered index created.
By doing this SELECT statements using the primary key will make data retrieval very fast because of clustered index on it.


3. Create non-clustered indexes on columns

Columns frequently used in search criteria,joins,foreign keys or used in ORDER BY clause should have index on.

the following query perform better if it has index on column department.

SELECT firstname,lastname FROM Employee
WHERE department ='HR'


4. Avoid using function calls in queries.

function calls prevent sql server to use indexes.

SELECT OrderId,Amount FROM Orders
WHERE OrderDate > GetDate()

In this query despite of having index on OrderDate, a full table scan will be performed to search each and every record of table because of function call, hence no advantage of indexes and a huge loss in performance.

better solution for this query would be to avoid calling get date in query like this

Declare @DTime DateTime Set @DTime = GetDate()
SELECT OrderId,Amount FROM Orders
WHERE OrderDate > @DTime


5. avoid using COUNT() in a query to check existance of record.


COUNT() counts all matching values by doing a table scan.
In case of EXISTS,When it finds the first matching value, it returns TRUE and stops.

SELECT column1 FROM TableName WHERE 0 < (SELECT count(*) FROM TableName2 WHERE condition)
better solution would be:
SELECT column1 FROM TableName WHERE EXISTS (SELECT * FROM TableName2 WHERE condition)


6. Always try to use joins on indexed fields.

7. Avoid triggers as much as possible.

8. Use table variables insted of temporary tables.

Table variables reside in memory while temporary tables reside in the TempDb database So temporary tables require interaction with tempdb database.

9. Use UNION ALL instead of Using UNION.

UNION ALL is faster than UNION as it does not sort the result set for distinguished value.

10. Avoid using Cursors if we need records one by one, use while loop insted.

11. Avoid HAVING clause as it is just like filter after after all rows are SELECTed.

12. Use WHERE clause to narrow the search criteria and to reduce number of records returned in SELECT statment.

13. Use TOP keyword if we want TOP N records in SELECT statement.

and last but not the least

14. Use stored procedure instead of text queries.

In stored procedures

1. Always use object name with schema this helps in directly finding the compiled plan insted of searching other objects

Use:
SELECT * FROM dbo.TableName

insted of
SELECT * FROM TableName


2. Use SET NOCOUNT ON

sql server returns number of rows effected in any SELECT or DML statement and we can reduce this step by setting NOCOUNT ON like this

CREATE PROC dbo.MyProc
AS
SET NOCOUNT ON;

Avoid using COUNT() in a query to check existance of record.

Avoid using COUNT() in a query to check existance of record.


COUNT() counts all matching values by doing a table scan.
In case of EXISTS,When it finds the first matching value, it returns TRUE and stops.


SELECT column1 FROM TableName WHERE 0 < (SELECT count(*) FROM TableName2 WHERE condition)
better solution would be:

SELECT column1 FROM TableName WHERE EXISTS (SELECT * FROM TableName2 WHERE condition)

Avoid using function calls in queries.

Function calls prevent sql server to use indexes.

SELECT OrderId,Amount FROM Orders
WHERE OrderDate > GetDate()

In this query despite of having index on OrderDate, a full table scan will be performed to search each and every record of table because of function call, hence no advantage of indexes and a huge loss in performance.

better solution for this query would be to avoid calling get date in query like this

Declare @DTime DateTime Set @DTime = GetDate()
SELECT OrderId,Amount FROM Orders
WHERE OrderDate > @DTime

Reset Identity Column Value In Sql Server Table

How To Reset Identity Column In Sql Server Table To Start Auto Increment Or Seed From Desired Number

If we delete All data from Sql Server table having Identity Column with Identity Seed as 1 and insert new data Identity column value doesn't get reset and it starts from last number.

If table were having 10 records and we insert new records after deleting all records in table, identity column would start from 11 if identity seed is 1.

we can use below mentioned command to reset Identity Column.


DBCC CHECKIDENT('YouTableName', RESEED, 0)

Case Sensitive search with SQL

If we fire following Query,
"select * from users where name = 'raja'"
it will return us all columns. Cause, all column contains same data and record search is not case Sensitive.

Default Collation of the SQL Server installation SQL_Latin1_General_CP1_CI_AS and this is not case sensitive. here is syntax to change column collation. 

 If we fire following Query,
"select * from users where name COLLATE Latin1_General_CS_AS = 'raja'"
it will return  record search is case Sensitive.

what is the difference between the right and left outer joins?

 The difference is simple – in a left outer join, all of the rows from the “left” table will be displayed, regardless of whether there are any matching columns in the “right” table. In a right outer join, all of the rows from the “right” table will be displayed, regardless of whether there are any matching columns in the “left” table.

What is the difference between a Clustered and Non Clustered Index?

A clustered index determines the order in which the rows of a table are
stored on disk. If a table has a clustered index, then the rows of that
table will be stored on disk in the same exact order as the clustered index.
An example will help clarify what we mean by that.

An example of a clustered index

Suppose we have a table named Employee which has a column named EmployeeID.
Let’s say we create a clustered index on the EmployeeID column. What happens
when we create this clustered index? Well, all of the rows inside the Employee
table will be physically – sorted (on the actual disk) – by the values inside
the EmployeeID column. What does this accomplish? Well, it means that whenever
a lookup/search for a sequence of EmployeeID’s is done using that clustered index,
then the lookup will be much faster because of the fact that the sequence of
employee ID’s are physically stored right next to each other on disk – that is
the advantage with the clustered index. This is because the rows in the table
are sorted in the exact same order as the clustered index, and the actual table
data is stored in the leaf nodes of the clustered index.

Remember that an index is usually a tree data structure – and leaf nodes are the
nodes that are at the very bottom of that tree. In other words, a clustered index
basically contains the actual table level data in the index itself. This is very
different from most other types of indexes as you can read about below.
When would using a clustered index make sense?

Let’s go through an example of when and why using a clustered index would actually
make sense. Suppose we have a table named Owners and a table named Cars. This is
what the simple schema would look like – with the column names in each table:

Owners
Owner_Name
Owner_Age

Cars
Car_Type
Owner_Name

Let’s assume that a given owner can have multiple cars – so a single Owner_Name
can appear multiple times in the Cars table.
Now, let’s say that we create a clustered index on the Owner_Name column in the Cars
table. What does this accomplish for us? Well, because a clustered index is stored
physically on the disk in the same order as the index, it would mean that a given
Owner_Name would have all his/her car entries stored right next to each other on disk.
In other words, if there is an owner named “Joe Smith” or “Raj Gupta”, then each owner
would have all of his/her entries in the Cars table stored right next to each other
on the disk.

When is using a clustered index an advantage?

What is the advantage of this? Well, suppose that there is a frequently run query
which tries to find all of the cars belonging to a specific owner. With the
clustered index, since all of the car entries belonging to a single owner would
be right next to each other on disk, the query will run much faster than if the
rows were being stored in some random order on the disk. And that is the key
point to remember!

Why is it called a clustered index?

In our example, all of the car entries belonging to a single owner would be right
next to each other on disk. This is the “clustering”, or grouping of similar values,
which is referred to in the term “clustered” index.

Note that having an index on the Owner_Name would not necessarily be unique, because
there are many people who share the same name. So, you might have to add another
column to the clustered index to make sure that it’s unique.
What is a disadvantage to using a clustered index?

A disadvantage to using a clustered index is the fact that if a given row has a value
updated in one of it’s (clustered) indexed columns what typically happens is that the
database will have to move the entire row so that the table will continue to be sorted
in the same order as the clustered index column. Consider our example above to clarify
this. Suppose that someone named “Rafael Nadal” buys a car – let’s say it’s a Porsche –
from “Roger Federer”. Remember that our clustered index is created on the Owner_Name column.
This means that when we do a update to change the name on that row in the Cars table,
the Owner_Name will be changed from “Rajendra” to “Rajkumar”.

But, since a clustered index also tells the database in which order to physically store
the rows on disk, when the Owner_Name is changed it will have to move an updated row
so that it is still in the correct sorted order. So, now the row that used to belong to
“Roger Federer” will have to be moved on disk so that it’s grouped (or clustered) with all
the car entries that belong to “Rafael Nadal”. Clearly, this is a performance hit.
This means that a simple UPDATE has turned into a DELETE and then an INSERT – just
to maintain the order of the clustered index. For this exact reason, clustered indexes
are usually created on primary keys or foreign keys, because of the fact that those
values are less likely to change once they are already a part of a table.

A comparison of a non-clustered index with a clustered index with an example

As an example of a non-clustered index, let’s say that we have a non-clustered index
on the EmployeeID column. A non-clustered index will store both the value of the EmployeeID
AND a pointer to the row in the Employee table where that value is actually stored. But a
clustered index, on the other hand, will actually store the row data for a particular
EmployeeID – so if you are running a query that looks for an EmployeeID of 15, the data
from other columns in the table like EmployeeName, EmployeeAddress, etc. will all
actually be stored in the leaf node of the clustered index itself.

This means that with a non-clustered index extra work is required to follow that pointer
to the row in the table to retrieve any other desired values, as opposed to a clustered
index which can just access the row directly since it is being stored in the same order
as the clustered index itself. So, reading from a clustered index is generally faster
than reading from a non-clustered index.
A table can have multiple non-clustered indexes

A table can have multiple non-clustered indexes because they don’t affect the order
in which the rows are stored on disk like clustered indexes.
Why can a table have only one clustered index?

Because a clustered index determines the order in which the rows will be stored on disk,
having more than one clustered index on one table is impossible. Imagine if we have two
clustered indexes on a single table – which index would determine the order in which the
rows will be stored? Since the rows of a table can only be sorted to follow just one index,
having more than one clustered index is not allowed.
Summary of the differences between clustered and non-clustered indexes

Here’s a summary of the differences:

    A clustered index determines the order in which the rows of the table will be stored
on disk – and it actually stores row level data in the leaf nodes of the index itself.
A non-clustered index has no effect on which the order of the rows will be stored.
    Using a clustered index is an advantage when groups of data that can be clustered
are frequently accessed by some queries. This speeds up retrieval because the data lives
close to each other on disk. Also, if data is accessed in the same order as the
clustered index, the retrieval will be much faster because the physical data stored
on disk is sorted in the same order as the index.
    A clustered index can be a disadvantage because any time a change is made to a
value of an indexed column, the subsequent possibility of re-sorting rows to maintain
order is a definite performance hit.
    A table can have multiple non-clustered indexes. But, a table can have
only one clustered index.
    Non clustered indexes store both a value and a pointer to the actual row
that holds that value. Clustered indexes don’t need to store a pointer to the
actual row because of the fact that the rows in the table are stored on disk
in the same exact order as the clustered index – and the clustered index actually
stores the row-level data in it’s leaf nodes.

In SQL, what’s the difference between the having clause and the where clause?

Difference between having and where clause

So we can see that the difference between the having and where clause in sql is
that the where clause can not be used with aggregates, but the having clause can.
One way to think of it is that the having clause is an additional filter to the
where clause.

The difference between the having and where clause is best illustrated by an example.
Suppose we have a table called emp_bonus as shown below. Note that the table has multiple
entries for employees A and B.
emp_bonus

Employee     Bonus
A                 1000
B                 2000
A                500
C                700
B                1250

If we want to calculate the total bonus that each employee received, then we would
write a SQL statement like this:

select employee, sum(bonus) from emp_bonus group by employee;

The Group By Clause

In the SQL statement above, you can see that we use the "group by" clause with the
employee column. What the group by clause does is allow us to find the sum of the bonuses
for each employee. Using the ‘group by’ in combination with the ‘sum(bonus)’ statement will
give us the sum of all the bonuses for employees A, B, and C.


Running the SQL above would return this:
Employee     Sum(Bonus)
A                  1500
B                  3250
C                  700

Now, suppose we wanted to find the employees who received more than 1,000 in bonuses
for the year of 2007. You might think that we could write a query like this:

BAD SQL:
select employee, sum(bonus) from emp_bonus
group by employee where sum(bonus) > 1000;

The WHERE clause does not work with aggregates like SUM

The SQL above will not work, because the where clause doesn’t work with aggregates – like sum,
avg, max, etc.. Instead, what we will need to use is the having clause. The having clause was
added to sql just so we could compare aggregates to other values – just how the ‘where’ clause
can be used with non-aggregates. Now, the correct sql will look like this:

GOOD SQL:
select employee, sum(bonus) from emp_bonus
group by employee having sum(bonus) > 1000;

What’s referential integrity?

Referential integrity is a database concept that ensures that relationships between tables remain consistent.When one table has a foreign key to another table, the concept of referential integrity states that you may not add a record to the table that contains the foreign key unless there is a corresponding record in the linked table. It also includes the techniques known as cascading update and cascading delete, which ensure that changes made to the linked table are reflected in the primary table

SQL Server: Natural Key Verses Surrogate Key

When you design tables with SQL Server, a table typically has a column or a number of columns that are known as the primary key. The primary key is a unique value that identifies each record.  Sometimes the primary key is made up of real data and these are normally referred to as natural keys, while other times the key is generated when a new record is inserted into a table.   When a primary key is generated at runtime, it is called a surrogate key.   A surrogate key is typically a numeric value.  Within SQL Server, Microsoft allows you to define a column with an identity property to help generate surrogate key values.

A natural key is a single column or set of columns that uniquely identifies a single record in a table

A surrogate key like a natural key is a column that uniquely identifies a single record in a table.  But this is where the similarity stops.  Surrogate keys are similar to surrogate mothers.   They are keys that don’t have a natural relationship with the rest of the columns in a table.  The surrogate key is just a value that is generated and then stored with the rest of the columns in a record.  The key value is typically generated at run time right before the record is inserted into a table.   It is sometimes also referred to as a dumb key, because there is no meaning associated with the value.  Surrogate keys are commonly a numeric number.  

What are the differences between foreign, primary, and unique keys

While unique and primary keys both enforce uniqueness on the column(s) of one table, foreign keys define a relationship between two tables.
A foreign key identifies a column or group of columns in one (referencing) table that refers
to a column or group of columns in another (referenced) table.

Diffrence between ExecuteScalar() and ExecuteNonQuery()

ExecuteScalar() Method:

ExecuteScalar() method is used to retrieve a single value from database. It executes the defined query and returns the value in the first column of the first row in the selected result set and ignores all other columns and rows in the result set. It is use to get aggregate value from database, for example count or total of rows. So it works with non action queries that use aggregate functions. ExecuteScalar() method is a faster way when we compare it to other ways to retrieve single value from database. It returns a value as object and we have to cast it to appropriate type.


ExecuteNonQuery() Method:

ExecuteNonQuery() method is used to manipulate data in database and is used for statements without results such as CREATE, INSERT, UPDATE and DELETE commands. It does not return any data but it returns number of rows affected. If NO COUNT property is ON then it will not return number of rows affected. It will not give access to result set generated by the statement. The return value of number of rows affected is of type integer and you can get it in an integer variable. It will tell you how many rows have been affected in result of your statement. ExecuteNonQuery() method is a flexible method and we can use it input and output parameters.

SQL Related Some Query

-- DEFAULT CONSTANT
SELECT * FROM sys.objects
WHERE  Type='D' and create_date >= '20150401'
--AND create_date < GETDATE()

-- FOREIGN KEY
SELECT * FROM sys.objects
WHERE  Type='F' and create_date >= '20150401'
--AND create_date < GETDATE()

-- SCALAR FUNCTION
SELECT * FROM sys.objects
WHERE Type='FN' and  create_date >= '20150401'
--AND create_date < GETDATE()

-- INLINE TABLE VALUE FUNCTION
SELECT * FROM sys.objects
WHERE Type='IF' and  create_date >= '20150401'
--AND create_date < GETDATE()

-- INTERNAL TABLE
SELECT * FROM sys.objects
WHERE Type='IT' and  create_date >= '20150401'
--AND create_date < GETDATE()

-- PROCEDURE
SELECT * FROM sys.objects
WHERE Type='P' and  create_date >= '20150401'
--AND create_date < GETDATE()

-- PRIMARY KEY
SELECT * FROM sys.objects
WHERE Type='PK' and  create_date >= '20150401'
--AND create_date < GETDATE()

-- SEQUENCE OBJECT
SELECT * FROM sys.objects
WHERE  Type='SO' and create_date >= '20150401'
--AND create_date < GETDATE()

-- SERVICE QUEUE
SELECT * FROM sys.objects
WHERE Type='SQ' and  create_date >= '20150401'
--AND create_date < GETDATE()

-- TABLE VALUED FUNCTION
SELECT * FROM sys.objects
WHERE Type='TF' and  create_date >= '20150401'
--AND create_date < GETDATE()

-- TRIGGER
SELECT * FROM sys.objects
WHERE Type='TR' and  create_date >= '20150401'
--AND create_date < GETDATE()

-- TYPE TABLE
SELECT * FROM sys.objects
WHERE Type='TT' and  create_date >= '20150401'
--AND create_date < GETDATE()

-- USER TABLE
SELECT * FROM sys.objects
WHERE Type='U' and  create_date >= '20150401'
--AND create_date < GETDATE()

-- UNIQUE CONSTANT
SELECT * FROM sys.objects
WHERE Type='U' and  create_date >= '20150401'
--AND create_date < GETDATE()

-- VIEW
SELECT * FROM sys.objects
WHERE Type='V' and  create_date >= '20150401'
--AND create_date < GETDATE()

Concatenate Multiple Rows Within Single Row in SQL Server 2008

We can concatenate multiple rows within a single row using the predefined function STUFF available in SQL Server

Create 2 tables as in the following.

    Create Table Courses 
    ( 
      CourseID int primary key, 
      CourseName nvarchar(20) 
    ) 

INSERT INTO Courses(CourseId,CourseName) VALUES (1,'C#') 
INSERT INTO Courses(CourseId,CourseName) VALUES (2,'ASP.Net') 
INSERT INTO Courses(CourseId,CourseName) VALUES (3,'MVC') 
INSERT INTO Courses(CourseId,CourseName) VALUES (4,'WCF') 
INSERT INTO Courses(CourseId,CourseName) VALUES (5,'Share Point') 
INSERT INTO Courses(CourseId,CourseName) VALUES (6,'WPF') 
INSERT INTO Courses(CourseId,CourseName) VALUES (7,'SQL Server') 
INSERT INTO Courses(CourseId,CourseName) VALUES (8,'JQuery')

    CREATE TABLE StudentCourses 
    ( 
    StudentID int, 
    CourseID int 
    ) 

INSERT INTO StudentCourses(StudentID, CourseID) VALUES (1,1) 
INSERT INTO StudentCourses(StudentID, CourseID) VALUES (1,3) 
INSERT INTO StudentCourses(StudentID, CourseID) VALUES (1,5) 
INSERT INTO StudentCourses(StudentID, CourseID) VALUES (2,2) 
INSERT INTO StudentCourses(StudentID, CourseID) VALUES (2,4) 
INSERT INTO StudentCourses(StudentID, CourseID) VALUES (2,5) 
INSERT INTO StudentCourses(StudentID, CourseID) VALUES (3,3) 
INSERT INTO StudentCourses(StudentID, CourseID) VALUES (3,6) 
INSERT INTO StudentCourses(StudentID, CourseID) VALUES (4,7) 
INSERT INTO StudentCourses(StudentID, CourseID) VALUES (4,8) 
INSERT INTO StudentCourses(StudentID, CourseID) VALUES (5,1) 
INSERT INTO StudentCourses(StudentID, CourseID) VALUES (5,2)  

Execute this SQL Query to get the student courseIds separated by a comma.

    SELECT StudentID, 
    CourseIDs=STUFF 
    ( 
         ( 
           SELECT DISTINCT ', ' + CAST(CourseID AS VARCHAR(MAX)) 
           FROM StudentCourses t2  
           WHERE t2.StudentID = t1.StudentID  
           FOR XML PATH('') 
         ),1,1,'' 
    ) 
    FROM StudentCourses t1 
    GROUP BY StudentID 

Execute this query to get the student course names separated by a comma.

    SELECT StudentID, 
    CourseNames=STUFF 
    ( 
        ( 
          SELECT DISTINCT ', '+ CAST(g.CourseName AS VARCHAR(MAX)) 
          FROM Courses g,StudentCourses e  
          WHERE g.CourseID=e.CourseID and e.StudentID=t1.StudentID  
          FOR XMl PATH('') 
        ),1,1,'' 
    ) 
    FROM StudentCourses t1 
    GROUP BY StudentID 

List of table having no Cluster Index in SQL

This script will show table without clustered index:

SELECT sys.tables.name, sys.indexes.name
FROM sys.tables left join sys.indexes
ON sys.tables.object_id = sys.indexes.object_id
WHERE isnull(sys.indexes.name,'')=''
ORDER BY sys.tables.name

List Of Table having No Index in SQL

This script  will show table without any index:

SELECT a.name FROM sys.tables a
left join sys.indexes b
ON a.object_id = b.object_id
WHERE isnull(b.name,'')=''
and not exists (select 1 from sys.indexes c where isnull(name,'')<>''
and c.object_id=b.object_id)
ORDER BY a.name