Monday, January 26, 2015

RIGHT() function in SQL Server

Right():- This function returns the specified number of characters from the Right part of the given character string.

Syntax:-

 Select RIGHT ( character_expression , integer_expression )

 For example, the below query will return two characters from the last.

 Select RIGHT('Vivek',2) 

 
 Result

 ek

DMCA.com

LEFT() function in SQL Server

Left():- This function returns the specified number of characters from the left part of the given character string.

Syntax:-


Select LEFT ( character_expression , integer_expression )
   
For example, the below query will return two characters from the beginning

Select LEFT  ('Vivek',2)

Result

Vi

DMCA.com

Between function in SQL Server

This function is used to select the values within a specified range. These values can be of Int type or Date data type or Text data type.

Syntax:-

    SELECT column_name(s)
    FROM table_name
    WHERE column_name BETWEEN value1 AND value2


In this article, we are going to use the following table for the examples:-

CREATE TABLE [dbo]. [Employee](
[Empid] [Int] IDENTITY (1, 1) NOT NULL Primary key,
[EmpNumber] [nvarchar](50) NOT NULL,
[EmpFirstName] [nvarchar](150) NOT NULL,
[EmpLAStName] [nvarchar](150) NULL,
[EmpEmail] [nvarchar](150) NULL,
[Salary] [int] NULL
)


We insert following data into the above tables:-

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A001','Vivek','Johari','samir@abcmail.com',200000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A002','Amit','Kumar','amit@abcmail.com',100000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A003','Neha','Sharma','neha@abcmail.com',300000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A004','Chandra','Singh','vivek@abcmail.com',320000)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,salary)
Values('A005','Avinash', 'Dubey','avinash@abcmail.com',250000)

Example


For example, below query will give the Empnumber of all the employee whose salary lies between 250000 and 320000 (Including both the values (250000 & 320000 ))

 SELECT EmpNumber
    FROM employee
    WHERE salary BETWEEN 250000 AND 320000

Results


A003
A004
A005


Between command includes both the values (value1 and value 2) for returning the result. For example, suppose we want to get the records of the employees whose empid is lies between 1 and 4 then the query will be:-


select  EmpNumber  from employee where empid between 1 and 4


Result

A001
A002
A003
A004


If we do not want to includes either of these specified minimum and maximum values or both these values , then we should use > or < operator instead of between command

For example below query will gives the EmpNumbers of employees whose empid are greater than 1 but less the 4

select  EmpNumber  from employee where empid>1and empid<4 span="">

Result

A002
A003


Not Between:- If we want to select the values outside the specified range, we can use the NOT between function. 

Syntax:-

SELECT column_name(s)
    FROM table_name
    WHERE column_name Not BETWEEN value1 AND value2


For example, below query will give the Empnumber of all the employee whose salary lies outside the range specified by the minimum value of 250000 and maximum 320000.

 SELECT EmpNumber
    FROM employee
    WHERE salary NOT BETWEEN 250000 AND 320000


Result

A001
A002 

 

DMCA.com

Sunday, January 25, 2015

SQL Script to find the missing indexes

Performance tuning in SQL is important exercise and index creation is an important part of it. Below script will help in finding the missing indexes. Once you create these indexes, it will help in improving the Performance.

SELECT db_name(d.database_id) dbname

, object_name(d.object_id) tablename
, d.equality_columns
, d.inequality_columns
, d.included_columns
,'CREATE INDEX [missing_index_' + CONVERT (varchar, g.index_group_handle) + '_' + CONVERT (varchar, d.index_handle)
      + '_' + LEFT (PARSENAME(d.statement, 1), 32) + ']'
      + ' ON ' + d.statement
      + ' (' + ISNULL (d.equality_columns,'')
        + CASE WHEN d.equality_columns IS NOT NULL AND d.inequality_columns IS NOT NULL THEN ',' ELSE '' END
        + ISNULL (d.inequality_columns, '')
      + ')'
      + ISNULL (' INCLUDE (' + d.included_columns + ')', '') AS create_index_statement
       FROM  sys.dm_db_missing_index_groups g
       join sys.dm_db_missing_index_group_stats gs ON gs.group_handle = g.index_group_handle
       join sys.dm_db_missing_index_details d ON g.index_handle = d.index_handle
WHERE  d.database_id =  'Database_id' and d.object_id =  d.object_id
ORDER BY 2 DESC  

We need to pass the id (Database_id) of the database in the above query. This id of the database can be get with the help of the below query.


select * from sys.databases


The above query will gives the id of all the databases deployed on the server in which this query is executed.