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.

SQL SERVER - Data types

In relational database, we store data in tabular form where data is divided into columns. Each column has a name and a data type which shows what type of data is going to store in that column. Below are the data types which are mostly used in SQL server. 

CHARACTER(n)    Character string of fixed length n

VARCHAR(n)        Character string of variable length with maximum length n

VARCHAR(MAX)   Character string of variable length with maximum length                                can exceed 8000 byes

NCHAR(n)            Fixed-length Unicode string data where n defines the string                            length and must be a value from 1 through 4,000

NVARCHAR(n)     Variable length Unicode string data where n defines the                                  string length and must be a value from 1 through 4,000

NVARCHAR(MAX) Variable length Unicode string data where MAX indicates                                 that the maximum storage size is 2^31-1 bytes (2 GB)

BINARY(n)           Binary string of fixed length n

BOOLEAN             It stores TRUE or FALSE values

VARBINARY(n)    Binary string of variable length with maximum length n

NTEXT                  Variable-length Unicode data with a maximum string length                            of 2^30 - 1 (1,073,741,823) bytes

TEXT                    Variable-length non-Unicode data in the code page of the                               server and with a maximum string length of 2^31-1                                       (2,147,483,647)

IMAGE                 Variable-length binary data from 0 through 2^31-1                                          (2,147,483,647) bytes.

INTEGER Or Int   A 32-bit signed integer value having range of INTEGER is                                -2147483648 to 2147483647

SMALLINT           A 16-bit signed integer value having range of SMALLINT is                              -32768 to 32767.

BIGINT               A 64-bit signed integer value having range of BIGINT is                                   -9223372036854775808 to 9223372036854775807

DECIMAL(p,s)    Exact numerical, precision p, scale s. For Example,                                         Decimal(5,3) means 22.212

NUMERIC(p,s)   Exact numerical, precision p, scale s. (Same as DECIMAL)

REAL                  Approximate numerical, mantissa precision 7

FLOAT                Approximate numerical, mantissa precision 16

DOUBLE             Approximate numerical, mantissa precision 16

DATE                  It stores year/month/day values

TIME                  It stores hour/minute/second values

TIMESTAMP       It stores year/month/day/hour/minute/second values

XML                    It stores XML data

SQL Server - Convert() function

Convert () function in SQL Server to convert an expression from one data type to another data type

Syntax for CONVERT function
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Convert functions is usually used with date/time data type to convert a date in different formats. Below examples will show the different formats in which we can display the date/time data type using Convert function. In the below examples we use getdate() to get the current date and time of the database server.

select convert(varchar(40), getdate(), 0)

Result:-
Jan 25 2015  9:36PM

select convert(varchar(40), getdate(), 101)

Result:-
01/25/2015

select convert(varchar(40), getdate(), 102)

Result:-
2015.01.25

select convert(varchar(40), getdate(), 103)

Result:-
25/01/2015

select convert(varchar(40), getdate(), 104)

Result:-
25.01.2015

select convert(varchar(40), getdate(), 105)

Result:-
25-01-2015

select convert(varchar(40), getdate(), 106)

Result:-
25 Jan 2015

select convert(varchar(40), getdate(), 107)

Result:-
Jan 25, 2015

select convert(varchar(40), getdate(), 108)

Result:-
21:39:21

select convert(varchar(40), getdate(), 109)

Result:-
Jan 25 2015 10:19:15:583PM

select convert(varchar(40), getdate(), 110)

Result:-
01-25-2015

select convert(varchar(40), getdate(), 111)

Result:-
2015/01/25

select convert(varchar(40), getdate(), 112)

Result:-
20150125

select convert(varchar(40), getdate(), 113)

Result:-
25 Jan 2015 23:22:49:453

select convert(varchar(40), getdate(), 114)

Result:-
23:23:09:970

select convert(varchar(40), getdate(), 120)

Result:-
2015-01-25 23:23:37

select convert(varchar(40), getdate(), 121)

Result:-
2015-01-25 23:24:07.577

select convert(varchar(40), getdate(), 126)

Result:-
2015-01-25T23:24:29.860

select convert(varchar(40), getdate(), 127)

Result:-
2015-01-25T23:24:55.037

select convert(varchar(40), getdate(), 131)

Result:-
 5/04/1436 11:25:23:483PM

From the above examples we can see that how we can use the Convert () function to display date time in different formats.

Tuesday, January 20, 2015

SQL Script to find the list of all the jobs failed yesterday

One of the important task of any DBA is to find out all the jobs which are failed yesterday. Below SQL Script can be used to find out all the jobs which are failed yesterday.

SELECT DISTINCT
CAST(CONVERT(datetime,CAST(run_date AS char(8)),101) AS char(11)) AS 'Failure Date',
SUBSTRING(T2.name,1,40) AS 'Job Name',
T1.step_id AS 'Step_id',
T1.step_name  AS 'Step Name',
LEFT(T1.[message],500) AS 'Error Message'
FROM msdb..sysjobhistory T1
JOIN msdb..sysjobs  T2
ON T1.job_id = T2.job_id
WHERE  T1.run_status NOT IN (1,4)
AND T1.step_id != 0
AND run_date >= CONVERT(char(8), (select dateadd (day,(-1), getdate())), 112) 

SQL Script to search stored procedures containing a given text

Sometimes we need to find out how many stored procedures contains a given text. Below SQL query can be used to find out the list of all the stored procedures which contains a  particular given text as input.

SELECT OBJECT_NAME(object_id), OBJECT_DEFINITION(object_id)
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%Given text%'

SQL Script to find the tables created from a given date

Below is the SQL query which can be used to find out the tables which are created or modified on or from a given date.

SELECT
        [name] as Tablename
       ,create_date
       ,modify_date
FROM
        sys.tables where cast(create_date as date)>='Given date' 

SQL Script to find the databases size

Sometimes we need to find out the size of the database on a server. Below SQL Scripts can be used to find out the size of all the databases created on the server. 

;WITH DBSize (SqlServerInstanceName, DatabaseName, DatabaseSize, DBLogSize, TotalDBSize)
AS
(
  SELECT      @@SERVERNAME SqlServerInstanceName,
            db.name AS DatabaseName,
            SUM(CASE WHEN af.groupid = 0 THEN 0 ELSE af.size / 128.0E END) AS DatabaseSize,
            SUM(CASE WHEN af.groupid = 0 THEN af.size / 128.0E ELSE 0 END) AS DBLogSize,
            SUM(af.size / 128.0E) AS TotalDBSize
FROM        master..sysdatabases AS db
INNER JOIN  master..sysaltfiles AS af ON af.[dbid] = db.[dbid]
WHERE       db.name NOT IN ('distribution', 'Resource', 'master', 'tempdb', 'model', 'msdb') -- System databases
            AND db.name NOT IN ('Northwind', 'pubs', 'AdventureWorks', 'AdventureWorksDW')   
GROUP BY    db.name
)
SELECT * FROM DBSize order by TotalDBSize desc

Friday, January 9, 2015

T-SQL script to find the growth size of database files

Below query can be used to see the growth size of database files.

DECLARE @filename NVARCHAR(1000);
DECLARE @bc INT;
DECLARE @ec INT;
DECLARE @bfn VARCHAR(1000);
DECLARE @efn VARCHAR(10);

-- Get the name of the current default trace
SELECT @filename = CAST(value AS NVARCHAR(1000))
FROM ::fn_trace_getinfo(DEFAULT)
WHERE traceid = 1 AND property = 2;

-- rip apart file name into pieces
SET @filename = REVERSE(@filename);
SET @bc = CHARINDEX('.',@filename);
SET @ec = CHARINDEX('_',@filename)+1;
SET @efn = REVERSE(SUBSTRING(@filename,1,@bc));
SET @bfn = REVERSE(SUBSTRING(@filename,@ec,LEN(@filename)));

-- set filename without rollover number
SET @filename = @bfn + @efn

-- process all trace files
SELECT 
  ftg.StartTime
,te.name AS EventName
,DB_NAME(ftg.databaseid) AS DatabaseName  
,ftg.Filename
,(ftg.IntegerData*8)/1024.0 AS GrowthMB 
,(ftg.duration/1000)AS DurMS
FROM ::fn_trace_gettable(@filename, DEFAULT) AS ftg 
INNER JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id  
WHERE (ftg.EventClass = 92  -- Date File Auto-grow

T-SQL script to find the highly populated tables in database

Sometimes we need to check which tables of our database is highly populated. Below is the T-SQL Script which we can use to find out that.

SELECT 
    t.NAME AS TableName,
    i.name as indexName,
    sum(p.rows) as RowCounts,
    sum(a.total_pages) as TotalPages, 
    sum(a.used_pages) as UsedPages, 
    sum(a.data_pages) as DataPages,
    (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, 
    (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, 
    (sum(a.data_pages) * 8) / 1024 as DataSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.NAME NOT LIKE 'dt%' AND
    i.OBJECT_ID > 255 AND   
    i.index_id <= 1
GROUP BY 
    t.NAME, i.object_id, i.index_id, i.name 
--ORDER BY 
--    object_name(i.object_id) 

ORDER BY SUM(p.rows) DESC




Thursday, January 8, 2015

SQL Script to find the last executed commands on the SQl Server

Sometimes we needs to find out the last executed commands on the database server. 
Below is the SQL script share by one of my friend Ken Watson to find out the last executed queries.

SELECT usecounts, cacheobjtype, objtype, text 
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
WHERE usecounts > 1 
ORDER BY usecounts DESC; 



About Ken
He is working as SQL DBA. 
Blog:-  interflextech.com/blog
Twitter:- @interflextech

Sunday, January 4, 2015

Script to find the Fragmentation of indexes

Below is the script to find the fragmentation of the indexes created on a database.

SELECT OBJECT_NAME(OBJECT_ID), index_id,index_type_desc,index_level,
avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count
FROM sys.dm_db_index_physical_stats (DB_ID( N'Database name') , NULL, NULL, NULL , 'SAMPLED') ORDER BY avg_fragmentation_in_percent DESC

avg_fragmentation_in_percent represents  logical fragmentation.

If this value is higher than 5% and less than 30%, then we should use 

ALTER INDEXREORGANIZE

If this value is higher than 30%, then we should use 

ALTER INDEX REBUILD WITH (ONLINE = ON)

Please note:- As per MSDN, rebuilding an index can be executed online or offline. Reorganizing an index is always executed online. To achieve availability similar to the reorganize option, you should rebuild indexes online.

Read more about Fragmentation

Read more about Indexes

Read more about Reorganize and Rebuild Indexes



Script to find all the running queries/jobs on the Database Server

Below is the query to find out all the Queries and their details like SSID etc. running on the SQL Server from all databases. It helps in finding out which queries/jobs running on the server. It can be helpful in detecting the blocking queries also.


USE Master
SET NOCOUNT ON   
SET ANSI_PADDING ON
SET QUOTED_IDENTIFIER ON  
DECLARE @record_id int, @SQLProcessUtilization int, @CPU int,@EventTime datetime--,@MaxCPUAllowed int   
select  top 1  @record_id =record_id,
      @EventTime=dateadd(ms, -1 * ((SELECT ms_ticks from sys.dm_os_sys_info) - [timestamp]), GetDate()),-- as EventTime,
      @SQLProcessUtilization=SQLProcessUtilization,
      --SystemIdle,
      --100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization,
      @CPU=SQLProcessUtilization + (100 - SystemIdle - SQLProcessUtilization) --as CPU_Usage
from (
      select
            record.value('(./Record/@id)[1]', 'int') as record_id,
            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') as SystemIdle,
            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') as SQLProcessUtilization,
            timestamp
      from (
            select timestamp, convert(xml, record) as record
            from sys.dm_os_ring_buffers
            where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
            and record like '%<SystemHealth>%') as x
      ) as y
order by record_id desc 
SELECT           
            x.session_id as [Sid],
            COALESCE(x.blocking_session_id, 0) as BSid,
            @CPU as CPU,   
            @SQLProcessUtilization as SQL,  
                             
            x.Status,  
            x.TotalCPU as [T.CPU],
            x.Start_time,    
            CONVERT(nvarchar(30), getdate()-x.Start_time, 108) as Elap_time, --x.totalElapsedTime as ElapTime,
            x.totalReads as [T.RD], -- total reads
            x.totalWrites as [T.WR], --total writes     
            x.Writes_in_tempdb as [W.TDB],
            (
                  SELECT substring(text,x.statement_start_offset/2,
                        (case when x.statement_end_offset = -1
                        then len(convert(nvarchar(max), text)) * 2
                        else x.statement_end_offset end - x.statement_start_offset+3)/2)
                  FROM sys.dm_exec_sql_text(x.sql_handle)
                  FOR XML PATH(''), TYPE
            ) AS Sql_text,
            db_name(x.database_id) as dbName,
            (SELECT object_name(objectid) FROM sys.dm_exec_sql_text(x.sql_handle)) as object_name,           
            x.Wait_type,
            x.Login_name,
            x.Host_name,
            CASE LEFT(x.program_name,15)
            WHEN 'SQLAgent - TSQL' THEN 
            (     select top 1 'SQL Job = '+j.name from msdb.dbo.sysjobs (nolock) j
                  inner join msdb.dbo.sysjobsteps (nolock) s on j.job_id=s.job_id
                  where right(cast(s.job_id as nvarchar(50)),10) = RIGHT(substring(x.program_name,30,34),10) )
            WHEN 'SQL Server Prof' THEN 'SQL Server Profiler'
            ELSE x.program_name
            END as Program_name,
            x.percent_complete,
            x.percent_complete, 
            (
                  SELECT
                        p.text
                  FROM
                  (
                        SELECT
                             sql_handle,statement_start_offset,statement_end_offset
                        FROM sys.dm_exec_requests r2
                        WHERE
                             r2.session_id = x.blocking_session_id
                  ) AS r_blocking
                  CROSS APPLY
                  (
                  SELECT substring(text,r_blocking.statement_start_offset/2,
                        (case when r_blocking.statement_end_offset = -1
                        then len(convert(nvarchar(max), text)) * 2
                        else r_blocking.statement_end_offset end - r_blocking.statement_start_offset+3)/2)
                  FROM sys.dm_exec_sql_text(r_blocking.sql_handle)
                  FOR XML PATH(''), TYPE
                  ) p (text)
            )  as blocking_text,
            (SELECT object_name(objectid) FROM sys.dm_exec_sql_text(
            (select top 1 sql_handle FROM sys.dm_exec_requests r3 WHERE r3.session_id = x.blocking_session_id))) as blocking_obj
       
      FROM
      (
            SELECT
                  r.session_id,
                  s.host_name,
                  s.login_name,
                  r.start_time,
                  r.sql_handle,
                  r.database_id,
                  r.blocking_session_id,
                  r.wait_type,
                  r.status,
                  r.statement_start_offset,
                  r.statement_end_offset,
                  s.program_name,
                  r.percent_complete,               
                  SUM(cast(r.total_elapsed_time as bigint)) /1000 as totalElapsedTime, --CAST AS BIGINT to fix invalid data convertion when high activity
                  SUM(cast(r.reads as bigint)) AS totalReads,
                  SUM(cast(r.writes as bigint)) AS totalWrites,
                  SUM(cast(r.cpu_time as bigint)) AS totalCPU,
                  SUM(tsu.user_objects_alloc_page_count + tsu.internal_objects_alloc_page_count) AS writes_in_tempdb
            FROM sys.dm_exec_requests r
            JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id
            JOIN sys.dm_db_task_space_usage tsu ON s.session_id = tsu.session_id and r.request_id = tsu.request_id
            WHERE r.status IN ('running', 'runnable', 'suspended')
            GROUP BY
                  r.session_id,
                  s.host_name,
                  s.login_name,
                  r.start_time,
                  r.sql_handle,
                  r.database_id,
                  r.blocking_session_id,
                  r.wait_type,
                  r.status,
                  r.statement_start_offset,
                  r.statement_end_offset,
                  s.program_name,
                  r.percent_complete
      ) x
      where x.session_id <> @@spid
      order by x.totalCPU desc
GO

I have get these helpful standard scripts through other sites like MSDN, technet etc. Just sharing so that other can also get benefited by using it.

Saturday, January 3, 2015

How to pass Microsoft Certification Exams

Many times I was asked how to pass Microsoft certifications and how difficult it is. In my point of view, it simple to crack Microsoft certification exams if you have worked hard on basic concepts and implemented most of the questions and scenarios mention in your study books/blogs on your system.

1) Take out the list of all the topics which are the part of your certification exams. You can take this from the Microsoft certification site.

2) Buy a study book specific for Microsoft exam preparation. This book will help you in tracking of topics which you have covered and which is remaining. Also these books contains practice sessions which help in passing the examination.
In my case, I bought the book recommended by the Microsoft and its practices examples help me a lot in passing the Microsoft certification

3)Prepare hard for each topic. Make sure you have a very good conceptual understanding of most of the topics covered in the exam. There are lots of blogs/forums where one can find good knowledge about these topics individually. 

4) Try to implements maximum practices questions and scenarios found in the study book and blogs in your system. More you practice these question easier it become to pass the examinations

6) Purchase Microsoft exam voucher and fixed the date at which you are comfortable. Sometimes Microsoft exam comes with offer where one retake is free. So if you failed in first attempt you can retry once more free of cost. I also benefited by this offer.

7) Be calm at the examination and read the questions carefully. Sometimes you can get the hints of possible answers from reading the question itself.

8) Even if you didn't get the passing score don't be disheartened. Few people passed the exam in their first attempt. Most of the people takes around 2 -3 attempts for passing the certification exams.

Please don't go for dump or question bank offered by many sites as you may pass the exam but you never get the knowledge and the basic purpose of getting the certification get defeated. There is no short cut to success.When you pass any certification, expectation become high from you and without knowledge, it start hurting your changes of getting good job by using your certification.

Even if you not able to pass the exam in first attempt, the knowledge you get while preparing for your exam will put you ahead from the other people.

One can also read about the benefits of Microsoft examination from this site.

If you any query/question/feedback regarding this article you can drop a mail to us at askvivekjohari@gmail.com.