get the sql server version

 We can get the sql server version by using this query :

DECLARE @ver nvarchar(128);
SET @ver = CAST(serverproperty('ProductVersion') AS nvarchar);
SET @ver = SUBSTRING(@ver, 1, CHARINDEX('.', @ver) - 1)

IF ( @ver = '7' )
   SELECT 'SQL Server 7'
ELSE IF ( @ver = '8' )
   SELECT 'SQL Server 2000'
ELSE IF ( @ver = '9' )
   SELECT 'SQL Server 2005'
ELSE IF ( @ver = '10' )
   SELECT 'SQL Server 2008/2008 R2'
ELSE IF ( @ver = '11' )
   SELECT 'SQL Server 2012'
ELSE
SELECT 'Unsupported SQL Server Version'
  

Read More

SQL Server Index Basics

One of the most important routes to high performance in a SQL Server database is the index. Indexes speed up the querying process by providing swift access to rows in the data tables, similarly to the way a book's index helps you find information quickly within that book.

Index Structures

Indexes are created on columns in tables or views. The index provides a fast way to look up data based on the values within those columns. For example, if you create an index on the primary key and then search for a row of data based on one of the primary key values, SQL Server first finds that value in the index, and then uses the index to quickly locate the entire row of data. Without the index, a table scan would have to be performed in order to locate the row, which can have a significant effect on performance.

You can create indexes on most columns in a table or a view. The exceptions are primarily those columns configured with large object (LOB) data types, such as image, text, and varchar(max). You can also create indexes on XML columns, but those indexes are slightly different from the basic index and are beyond the scope of this article. Instead, I'll focus on those indexes that are implemented most commonly in a SQL Server database.

An index is made up of a set of pages (index nodes) that are organized in a B-tree structure. This structure is hierarchical in nature, with the root node at the top of the hierarchy and the leaf nodes at the bottom, as shown in Figure 1.

Figure 1: B-tree structure of a SQL Server index

When a query is issued against an indexed column, the query engine starts at the root node and navigates down through the intermediate nodes, with each layer of the intermediate level more granular than the one above. The query engine continues down through the index nodes until it reaches the leaf node. For example, if you're searching for the value 123 in an indexed column, the query engine would first look in the root level to determine which page to reference in the top intermediate level. In this example, the first page points the values 1-100, and the second page, the values 101-200, so the query engine would go to the second page on that level. The query engine would then determine that it must go to the third page at the next intermediate level. From there, the query engine would navigate to the leaf node for value 123. The leaf node will contain either the entire row of data or a pointer to that row, depending on whether the index is clustered or nonclustered.

Clustered Indexes

A clustered index stores the actual data rows at the leaf level of the index. Returning to the example above, that would mean that the entire row of data associated with the primary key value of 123 would be stored in that leaf node. An important characteristic of the clustered index is that the indexed values are sorted in either ascending or descending order. As a result, there can be only one clustered index on a table or view. In addition, data in a table is sorted only if a clustered index has been defined on a table

.

Note: A table that has a clustered index is referred to as a clustered table. A table that has no clustered index is referred to as a heap.

Nonclustered Indexes

Unlike a clustered indexed, the leaf nodes of a nonclustered index contain only the values from the indexed columns and row locators that point to the actual data rows, rather than contain the data rows themselves. This means that the query engine must take an additional step in order to locate the actual data.

A row locator's structure depends on whether it points to a clustered table or to a heap. If referencing a clustered table, the row locator points to the clustered index, using the value from the clustered index to navigate to the correct data row. If referencing a heap, the row locator points to the actual data row.

Nonclustered indexes cannot be sorted like clustered indexes; however, you can create more than one nonclustered index per table or view. SQL Server 2005 supports up to 249 nonclustered indexes, and SQL Server 2008 support up to 999. This certainly doesn't mean you should create that many indexes. Indexes can both help and hinder performance, as I explain later in the article.

Index Types

In addition to an index being clustered or nonclustered, it can be configured in other ways:

• Composite index: An index that contains more than one column. In both SQL Server 2005 and 2008, you can include up to 16 columns in an index, as long as the index doesn't exceed the 900-byte limit. Both clustered and nonclustered indexes can be composite indexes.
• Unique Index: An index that ensures the uniqueness of each value in the indexed column. If the index is a composite, the uniqueness is enforced across the columns as a whole, not on the individual columns. For example, if you were to create an index on the FirstName and LastName columns in a table, the names together must be unique, but the individual names can be duplicated.

A unique index is automatically created when you define a primary key or unique constraint:

• Primary key: When you define a primary key constraint on one or more columns, SQL Server automatically creates a unique, clustered index if a clustered index does not already exist on the table or view. However, you can override the default behavior and define a unique, nonclustered index on the primary key.
• Unique: When you define a unique constraint, SQL Server automatically creates a unique, nonclustered index. You can specify that a unique clustered index be created if a clustered index does not already exist on the table.

• Covering index: A type of index that includes all the columns that are needed to process a particular query. For example, your query might retrieve the FirstName and LastName columns from a table, based on a value in the ContactID column. You can create a covering index that includes all three columns.

Reference  link : <https://www.simple-talk.com/sql/learn-sql-server/sql-server-index-basics/>

Read More

Insert multiple null values in Unique key index column in sql server

Here is i share Question from my friend : How I insert more than Null value in Unique key field in sql server

Answer  : we can do this using index on all not null value field.

Sample Code :

CREATE TABLE [dbo].[testUnique](

      [col1] [varchar](50) NULL,

      [ID] [int] IDENTITY(1,1) NOT NULL,

) ON [PRIMARY]

CREATE UNIQUE INDEX IX_testUnique ON testUnique(col1)

WHERE col1 IS NOT NULL –-Here we can set any value to which we want to allow multiple value

Insert into testUnique(col1)

values ('a'),(null),(null)

Post From :

<http://dotnetpro4u.blogspot.in/2013/05/insert-multiple-null-values-in-unique.html>

Read More

date function sql server

Question  1 : 

How to get current date time in sql server?

Ans :      

      

  SELECT GETDATE()

Question 2:   How to get Only Date from  datetime  in sql server ?

Ans :

SELECT

CONVERT(VARCHAR(8),GETDATE(),108) AS HourMinuteSecond,

CONVERT(VARCHAR,GETDATE(),101) AS DateOnly(MM/dd/yyyy),

CONVERT(VARCHAR,GETDATE(),103) AS DateOnly(dd/mm/yyyy),

CONVERT(VARCHAR,GETDATE(),105) AS DateOnly(dd-mm-yyyy);

Question 3 :    How to add  minutes,hour,day  in specific Date  in sql server

Ans :

 we can do this using DATEADD function .

Syntax :

DATEADD (datepart , number , date )

Example :

select DATEADD (hour , 1 ,getdate()) as Addhour,

DATEADD (month , 1 , getdate()) as Addmonth,

DATEADD (minute , 1 , getdate()) as Addminute,

DATEADD (day , 1 , getdate()) as Addminute

Question 4 :    How to get YearMonth  Date Format in  sql server 

Ans :

Select  left(convert(varchar,getdate(),112),6)

Output

201305

Read More

REPLACE Function in sql server

REPLACE  replaces part of a text string, based on the number of characters you specify, with a different text string.


Syntax :


REPLACE(<old_text>,  <chars>, <new_text>)





Parameters:

<old_text>     Any character string or binary expression that will be replace. <chars> Any character string or binary expression. This string is searching in  <old_text>.  <new_text> Any character string or binary expression. This string replace all occurrence of <chars>.

Example :




------------------------CREATE Table------------------------
create table #testtable
(
 [Product Code] varchar(200)
)
-------------------insert data----------------------
insert into #testtable
select 'NewProduct1'


--------------------Now use Replace function to replace 'N' char to 'OB'--------------------
select REPLACE([Product Code],'N','OB') from #testtable 


--------------------output-----------------------------
OBewProduct1




Drop table    #testtable

Read More

date time format in sql server

Question  1 : 

How to get current date time in sql server?

Ans :      

      

  SELECT GETDATE()

Question 2:   How to get Only Date from  datetime  in sql server ?

Ans :

SELECT

CONVERT(VARCHAR(8),GETDATE(),108) AS HourMinuteSecond,

CONVERT(VARCHAR,GETDATE(),101) AS DateOnly(MM/dd/yyyy),

CONVERT(VARCHAR,GETDATE(),103) AS DateOnly(dd/mm/yyyy),

CONVERT(VARCHAR,GETDATE(),105) AS DateOnly(dd-mm-yyyy);

Question 3 :    How to add  minutes,hour,day  in specific Date  in sql server

Ans :

 we can do this using DATEADD function .

Syntax :

DATEADD (datepart , number , date )

Example :

select DATEADD (hour , 1 ,getdate()) as Addhour,

DATEADD (month , 1 , getdate()) as Addmonth,

DATEADD (minute , 1 , getdate()) as Addminute,

DATEADD (day , 1 , getdate()) as Addminute

Question 4 :    How to get YearMonth  Date Format in  sql server 

Ans :

Select  left(convert(varchar,getdate(),112),6)

Output

201305

Read More

SQL FULL JOIN

SQL FULL JOIN Keyword

The FULL JOIN keyword return rows when there is a match in one of the tables.

SQL FULL JOIN Syntax

SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name

---

SQL FULL JOIN Example

The "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

The "Orders" table:

O_Id	OrderNo	P_Id
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	15

Now we want to list all the persons and their orders, and all the orders with their persons.
We use the following SELECT statement:

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName

The result-set will look like this:

LastName	FirstName	OrderNo
Hansen	Ola	22456
Hansen	Ola	24562
Pettersen	Kari	77895
Pettersen	Kari	44678
Svendson	Tove
		34764

The FULL JOIN keyword returns all the rows from the left table (Persons), and all the rows from the right table (Orders). If there are rows in "Persons" that do not have matches in "Orders", or if there are rows in "Orders" that do not have matches in "Persons", those rows will be listed as well.

GO BACK TO SQL JOIN PAGE 

Read More

SQL RIGHT JOIN

SQL RIGHT JOIN Keyword

The RIGHT JOIN keyword returns all the rows from the right table (table_name2), even if there are no matches in the left table (table_name1).

SQL RIGHT JOIN Syntax

SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

PS: In some databases RIGHT JOIN is called RIGHT OUTER JOIN.

---

SQL RIGHT JOIN Example

The "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

The "Orders" table:

O_Id	OrderNo	P_Id
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	15

Now we want to list all the orders with containing persons - if any, from the tables above.
We use the following SELECT statement:

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
RIGHT JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName

The result-set will look like this:

LastName	FirstName	OrderNo
Hansen	Ola	22456
Hansen	Ola	24562
Pettersen	Kari	77895
Pettersen	Kari	44678
		34764

The RIGHT JOIN keyword returns all the rows from the right table (Orders), even if there are no matches in the left table (Persons).

GO BACK TO SQL JOIN PAGE

Read More

SQL LEFT JOIN Keyword

SQL LEFT JOIN Keyword

The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).

SQL LEFT JOIN Syntax

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

PS: In some databases LEFT JOIN is called LEFT OUTER JOIN.

---

SQL LEFT JOIN Example

The "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

The "Orders" table:

O_Id	OrderNo	P_Id
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	15

Now we want to list all the persons and their orders - if any, from the tables above.
We use the following SELECT statement:

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName

The result-set will look like this:

LastName	FirstName	OrderNo
Hansen	Ola	22456
Hansen	Ola	24562
Pettersen	Kari	77895
Pettersen	Kari	44678
Svendson	Tove

The LEFT JOIN keyword returns all the rows from the left table (Persons), even if there are no matches in the right table (Orders).

Go Back to SQL JOIN PAGE

Read More

SQL INNER JOIN Keyword

SQL INNER JOIN Keyword

The INNER JOIN keyword returns rows when there is at least one match in both tables.

SQL INNER JOIN Syntax

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name

PS: INNER JOIN is the same as JOIN.

---

SQL INNER JOIN Example

The "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

The "Orders" table:

O_Id	OrderNo	P_Id
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	15

Now we want to list all the persons with any orders.
We use the following SELECT statement:

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName

The result-set will look like this:

LastName	FirstName	OrderNo
Hansen	Ola	22456
Hansen	Ola	24562
Pettersen	Kari	77895
Pettersen	Kari	44678

The INNER JOIN keyword returns rows when there is at least one match in both tables. If there are rows in "Persons" that do not have matches in "Orders", those rows will NOT be listed.

GO BACK TO SQL JOIN PAGE

Read More

SQL JOIN

SQL joins are used to query data from two or more tables, based on a relationship between certain columns in these tables.

---

SQL JOIN

The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables.
Tables in a database are often related to each other with keys.
A primary key is a column (or a combination of columns) with a unique value for each row. Each primary key value must be unique within the table. The purpose is to bind data together, across tables, without repeating all of the data in every table.
Look at the "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Note that the "P_Id" column is the primary key in the "Persons" table. This means that no two rows can have the same P_Id. The P_Id distinguishes two persons even if they have the same name.
Next, we have the "Orders" table:

O_Id	OrderNo	P_Id
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	15

Note that the "O_Id" column is the primary key in the "Orders" table and that the "P_Id" column refers to the persons in the "Persons" table without using their names.
Notice that the relationship between the two tables above is the "P_Id" column.

---

Different SQL JOINs

Before we continue with examples, we will list the types of JOIN you can use, and the differences between them.

• JOIN: Return rows when there is at least one match in both tables
• LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
• RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
• FULL JOIN: Return rows when there is a match in one of the tables

Read More

Mathematical Functions

Standard ANSI SQL-92 supports the following first four basic arithmetic operators:

+	addition
-	subtraction
*	multiplication
/	division
%	modulo

The modulo operator determines the integer remainder of the division. This operator is not ANSI SQL supported, however, most databases support it. The following are some more useful mathematical functions to be aware of since you might need them. These functions are not standard in the ANSI SQL-92 specs, therefore they may or may not be available on the specific RDBMS that you are using. However, they were available on several major database systems that I tested. They WILL work on this tutorial.

ABS(x)	returns the absolute value of x
SIGN(x)	returns the sign of input x as -1, 0, or 1 (negative, zero, or positive respectively)
MOD(x,y)	modulo - returns the integer remainder of x divided by y (same as x%y)
FLOOR(x)	returns the largest integer value that is less than or equal to x
CEILING(x) or CEIL(x)	returns the smallest integer value that is greater than or equal to x
POWER(x,y)	returns the value of x raised to the power of y
ROUND(x)	returns the value of x rounded to the nearest whole integer
ROUND(x,d)	returns the value of x rounded to the number of decimal places specified by the value d
SQRT(x)	returns the square-root value of x

For example:

SELECT round(salary), firstname

FROM employee_info

This statement will select the salary rounded to the nearest whole value and the firstname from the employee_info table.

Read More

sql_in_between_operatos

IN & BETWEEN

IN operator Syntax :

SELECT col1, SUM(col2)

FROM "list-of-tables"

WHERE col3 IN 
       (list-of-values);

BETWEEN operator Syntax :

SELECT col1, SUM(col2)


FROM "list-of-tables"

WHERE col3 BETWEEN value1 
AND value2;

The IN conditional operator is really a set membership test operator. That is, it is used to test whether or not a value (stated before the keyword IN) is "in" the list of values provided after the keyword IN.
For example:

SELECT employeeid, lastname, salary

FROM employee_info


WHERE lastname IN ('Hernandez', 'Jones', 'Roberts', 'Ruiz');

This statement will select the employeeid, lastname, salary from the employee_info table where the lastname is equal to either: Hernandez, Jones, Roberts, or Ruiz. It will return the rows if it is ANY of these values.
The IN conditional operator can be rewritten by using compound conditions using the equals operator and combining it with OR - with exact same output results:

SELECT employeeid, lastname, salary

FROM employee_info

WHERE lastname = 'Hernandez' OR lastname = 'Jones' OR lastname = 'Roberts'
OR lastname = 'Ruiz';

As you can see, the IN operator is much shorter and easier to read when you are testing for more than two or three values.
You can also use NOT IN to exclude the rows in your list.
The BETWEEN conditional operator is used to test to see whether or not a value (stated before the keyword BETWEEN) is "between" the two values stated after the keyword BETWEEN.
For example:

SELECT employeeid, age, lastname, salary

FROM employee_info
 
WHERE age BETWEEN 30 AND 40;

This statement will select the employeeid, age, lastname, and salary from the employee_info table where the age is between 30 and 40 (including 30 and 40).
This statement can also be rewritten without the BETWEEN operator:

SELECT employeeid, age, lastname, salary

FROM employee_info

WHERE age >= 30 AND age <= 40;

Read More

ORDER BY clause

ORDER BY is an optional clause which will allow you to display the results of your query in a sorted order (either ascending order or descending order) based on the columns that you specify to order by.

ORDER BY clause syntax:

SELECT column1, SUM(column2) FROM "list-of-tables" ORDER BY "column-list" [ASC | DESC];
[ ] = optional

This statement will select the employee_id, dept, name, age, and salary from the employee_info table where the dept equals 'Sales' and will list the results in Ascending (default) order based on their Salary.

ASC = Ascending Order - default

DESC = Descending Order 

For example:

SELECT employee_id, dept, name, age, salary FROM employee_info WHERE dept = 'Sales' ORDER BY salary;

If you would like to order based on multiple columns, you must seperate the columns with commas. For example:

SELECT employee_id, dept, name, age, salary


FROM employee_info

WHERE dept = 'Sales'

ORDER BY salary, age DESC;

Read More

GROUP BY - Multiple Grouping Columns - What if?

What if you ALSO want to display their lastname for the query below:

SELECT max(salary), dept
FROM employee
GROUP BY dept;

What you'll need to do is:

SELECT lastname, max(salary), dept
FROM employee
GROUP BY dept, lastname;

This is a called "multiple grouping columns".

Read More

GROUP BY clause

The GROUP BY clause will gather all of the rows together that contain data in the specified column(s) and will allow aggregate functions to be performed on the one or more columns. This can best be explained by an example:

GROUP BY clause syntax:

 
SELECT column1, 
SUM(column2)
 
FROM "list-of-tables"
 
GROUP BY "column-list";

Let's say you would like to retrieve a list of the highest paid salaries in each dept:

 
SELECT max(salary), dept
 
FROM employee 
 
GROUP BY dept;

This statement will select the maximum salary for the people in each unique department. Basically, the salary for the person who makes the most in each department will be displayed. Their, salary and their department will be returned.

Multiple Grouping Columns - What if I wanted to display their lastname too?


For example, take a look at the items_ordered table. Let's say you want to group everything of quantity 1 together, everything of quantity 2 together, everything of quantity 3 together, etc. If you would like to determine what the largest cost item is for each grouped quantity (all quantity 1's, all quantity 2's, all quantity 3's, etc.), you would enter:

 
SELECT quantity, max(price)
 
FROM items_ordered
 
GROUP BY quantity;

Enter the statement in above, and take a look at the results to see if it returned what you were expecting. Verify that the maximum price in each Quantity Group is really the maximum price.

Read More

Aggregate Functions

MIN	returns the smallest value in a given column
MAX	returns the largest value in a given column
SUM	returns the sum of the numeric values in a given column
AVG	returns the average value of a given column
COUNT	returns the total number of values in a given column
COUNT(*)	returns the number of rows in a table

Aggregate functions are used to compute against a "returned column of numeric data" from your SELECT statement. They basically summarize the results of a particular column of selected data. We are covering these here since they are required by the next topic, "GROUP BY". Although they are required for the "GROUP BY" clause, these functions can be used without the "GROUP BY" clause. For example:

SELECT AVG(salary)

FROM employee;

This statement will return a single result which contains the average value of everything returned in the salary column from the employee table.
Another example:

SELECT AVG(salary)


FROM employee;

WHERE title = 'Programmer';

This statement will return the average salary for all employees whose title is equal to 'Programmer'
Example:

SELECT Count(*)

FROM employees;

This particular statement is slightly different from the other aggregate functions since there isn't a column supplied to the count function. This statement will return the number of rows in the employees table.

Read More

SELECT & FROM clause review

SELECT first_column_name, second_column_name
FROM table_name
WHERE first_column_name > 1000;

*The column names that follow the SELECT keyword determine which columns will be returned in the results. You can select as many column names that you'd like, or you can use a * to select all columns. The order they are specified will be the order that they are returned in your query results.
*The table name that follows the keyword FROM specifies the table that will be queried to retrieve the desired results.
*The WHERE clause (optional) specifies which data values or rows will be returned or displayed, based on the criteria described after the keyword where.

Go Back to previous SELECT page

Read More

SELECT Statement

The SELECT statement is used to query the database and retrieve selected data that match the criteria that you specify.
The SELECT statement has five main clauses to choose from, although, FROM is the only required clause. Each of the clauses have a vast selection of options, parameters, etc.

 Here is the format of the SELECT statement:

SELECT [ALL | DISTINCT] column1[,column2] FROM table1[,table2] [WHERE "conditions"] [GROUP BY "column-list"] [HAVING "conditions] [ORDER BY "column-list" [ASC | DESC] ] 

FROM & WHERE clause quick review

 Example:

 
SELECT name, age, salary
 
FROM employee
 
WHERE age > 50;

The above statement will select all of the values in the name, age, and salary columns from the employee table whose age is greater than 50.
Note: Remember to put a semicolon at the end of your SQL statements. The ; indicates that your SQL statment is complete and is ready to be interpreted.
Comparison Operators

=	Equal
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than or equal to
<> or !=	Not equal to
LIKE	String comparison test

*Note about LIKE

 
Example:

SELECT name, title, dept FROM employee WHERE title LIKE 'Pro%'; 

The above statement will select all of the rows/values in the name, title, and dept columns from the employee table whose title starts with 'Pro'. This may return job titles including Programmer or Pro-wrestler.
ALL and DISTINCT are keywords used to select either ALL (default) or the "distinct" or unique records in your query results. If you would like to retrieve just the unique records in specified columns, you can use the "DISTINCT" keyword. DISTINCT will discard the duplicate records for the columns you specified after the
"SELECT" statement: For example:

 
SELECT DISTINCT age 
 
FROM employee_info;

This statement will return all of the unique ages in the employee_info table.
ALL will display "all" of the specified columns including all of the duplicates. The ALL keyword is the default if nothing is specified.

Read More

SQL SERVER – Avoid Using Function in WHERE Clause – Scan to Seek

-->

-->"Don't use functions in the WHERE clause, they reduce performance."

I hear this quite often. This is true but this subject is hard to understand in a single statement. Let us see what it means and how to use the function in the WHERE clause.

We will be using sample database AdventureWorks in this example. Additionally, turn on STATISTICS IO ON settings so we can see various statistics as well.

USE AdventureWorks2012
GO
SET STATISTICS IO ON
GO

Let us first execute following query and check the execution plan and statistics.

-- SCAN - Select values from SalesOrderDetail
SELECT [SalesOrderID], [SalesOrderDetailID], [ModifiedDate]
FROM [Sales].[SalesOrderDetail]
WHERE DATEDIFF(YEAR,ModifiedDate,GETDATE()) < 0
GO

Table 'SalesOrderDetail'. Scan count 1, logical reads 1246

You can see that the above query is scanning the whole table as well even though it is not returning any result it is reading 1246 pages from database.

In this case we are retrieving the data based on the ModifiedDate so we will create an index on the ModifiedDate Column.

-- Create Index on ModifiedDate
CREATE NONCLUSTERED INDEX [IX_SalesOrderDetail_ModifiedDate]
ON [Sales].[SalesOrderDetail] ([ModifiedDate])
GO

Now we have created an index on the ModifiedDate column we will once again execute the same query which we had run earlier.

-- SCAN - Select values from SalesOrderDetail
SELECT [SalesOrderID], [SalesOrderDetailID], [ModifiedDate]
FROM [Sales].[SalesOrderDetail]
WHERE DATEDIFF(YEAR,ModifiedDate,GETDATE()) < 0
GO

Table 'SalesOrderDetail'. Scan count 1, logical reads 338

You can see that the above query is still scanning the whole table as well even though it is not returning any result it is read over 338 pages from database.

The reason for the same is because in the query we are using the function DATEDIFF over the column ModifiedDate. As the outcome of this function is evaluated at the run time, the SQL Server Query engine has to scan the whole table to get necessary data.

To avoid this situation, let us try to avoid using the function on the column of the table and re-write the query. To re-write the query let us first understand what the query is retrieved. It is retrieving all the rows where the year difference between ModifiedDate and Current Date is less than 0. In other words what it means is that we need to retrieve the records which have a future date. We can simply re-write above query in the following way.

-- SEEK - Select values from SalesOrderDetail
SELECT [SalesOrderID], [SalesOrderDetailID], [ModifiedDate]
FROM [Sales].[SalesOrderDetail]
WHERE ModifiedDate > GETDATE()
GO

Table 'SalesOrderDetail'. Scan count 1, logical reads 3

Now let us execute the query and see the execution plan. We can see that there are only 3 logical read now and execution plan is also displaying Seek. This is because now we are not using any function over the column used in the WHERE clause.

To clean up you can run following script to drop the newly created index.

-- Cleanup
DROP INDEX [IX_SalesOrderDetail_ModifiedDate] ON [Sales].[SalesOrderDetail]
GO

In our case both the queries are returning the same result, a simple re-write can make a lot of difference.

Read More