Amazon Athena Overview

Amazon Athena is an interactive query service that makes it easy to analyze data directly in Amazon S3 using standard SQL.
Use cases : Buisness intelligence / analytics / reporting, analyze, & query VPC Flow logs, ELB logs, CloudTrails, etc.

- Serverless query service to analyse data stored in Amazon S3.
- Use standard SQL language to query files(built on pesto).
- Support CSV, JSON, ORC, Avro and Parquet.
- Pricing $5.00 per TB of data scanned.
- Commonly used with Amazon Quicksight for reporting/dashboards.

Amazon Athena : Performance Improvement

- Use Columnar Data for cost savings (by doing less scan).

- Apache Parquet or ORC is recommended.
- Huge performance improvement.
- Use Glue to convert your data to Parquet or ORC format.

- Compress data for smaller retrievals (bzip2, gzip, Iz4, snappy, zlip...)
- Partition datasets in S3 for easy querying on virtual columns

- s://yourbucketname/pathtotable

/[PARTITION_COLUMN_NAME]=[VALUE]
/[PARTITION_COLUMN_NAME]=[VALUE]
etc..

- Example s3://athenabucket/flight/parquet/year=1999/month=1/day=1/

- Use larger files (>128MB) to minimize overhead.

Amazon Athena : Federated Query

- Allows you to run SQL queries across data stored in relation, non-relational, objects or custom data sources (AWS or on-premises).

- Uses Data Sources Connectors that run on AWS Lambda to run federated queries (like on Cloudwatch logs, DynamoDB, RDS DB, Elasticache etc..)

- Store results back in Amazon S3.

SQL Server : Capture expensive queries using Server side trace

You can follow the below steps to capture the expensive queries by creating the trace and also from SQL cache plan DMV's :

1. Create Server Side trace: by defining a trace, its the events and columns to captured, and add the filter condition.

You can use the below Script to create the trace:

-- Create Server-side trace
-- Create a Queue
DECLARE @rc int
DECLARE @TraceID int
DECLARE @maxfilesize bigint
set @maxfilesize = 2048

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

DECLARE @MyFileName nvarchar(256)
SELECT @MyFileName='D:\RDSDBDATA\Log\RDSSQLTrace_' + CONVERT(nvarchar(10),GetDate(),121) + '_' + REPLACE(CONVERT(nvarchar(10),GetDate(),108),':','-')


exec @rc = sp_trace_create @TraceID output, 0, @MyFileName, @maxfilesize, NULL
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted
-- Set the events
DECLARE @on bit
set @on = 1 --sp_trace_setevent @traceid ,@eventid ,@columnid,@on

-- Capturing the RPC:Completed event

EXEC sp_trace_setevent @TraceID, 10, 16, @on
EXEC sp_trace_setevent @TraceID, 10, 1, @on
EXEC sp_trace_setevent @TraceID, 10, 17, @on
EXEC sp_trace_setevent @TraceID, 10, 14, @on
EXEC sp_trace_setevent @TraceID, 10, 18, @on
EXEC sp_trace_setevent @TraceID, 10, 12, @on
EXEC sp_trace_setevent @TraceID, 10, 13, @on
EXEC sp_trace_setevent @TraceID, 10, 8, @on
EXEC sp_trace_setevent @TraceID, 10, 10, @on
EXEC sp_trace_setevent @TraceID, 10, 11, @on
EXEC sp_trace_setevent @TraceID, 10, 35, @on

--Capturing the SQL:BatchCompleted event

EXEC sp_trace_setevent @TraceID, 12, 16, @on
EXEC sp_trace_setevent @TraceID, 12, 1, @on
EXEC sp_trace_setevent @TraceID, 12, 17, @on
EXEC sp_trace_setevent @TraceID, 12, 14, @on
EXEC sp_trace_setevent @TraceID, 12, 18, @on
EXEC sp_trace_setevent @TraceID, 12, 12, @on
EXEC sp_trace_setevent @TraceID, 12, 13, @on
EXEC sp_trace_setevent @TraceID, 12, 8, @on
EXEC sp_trace_setevent @TraceID, 12, 10, @on
EXEC sp_trace_setevent @TraceID, 12, 11, @on
EXEC sp_trace_setevent @TraceID, 12, 35, @on

-- Set the Filters
EXEC sp_trace_setfilter @TraceID,11,0,7,N'%NT AUTHORITY%' --SQL login
EXEC sp_trace_setfilter @TraceID, 10, 0, 7, N'RdsAdminService'

-- Set the trace status to start
EXEC sp_trace_setstatus @TraceID, 1

-- display trace id and file name, Note these details for future references
SELECT @TraceID,@MyFileName

goto finish

error:
select ErrorCode=@rc

finish:
go

2. Start the Trace using below command :

-- To Start the trace (Please update the trace_id as you may recived at the time of creation)
Exec sp_trace_setstatus @traceid = 2 , @on = 1 -- start

3. Start the application test run

4. Once test run is completed, Stop the trace using below command:

-- To Stop the trace (Please update the trace_id as you may recived at the time of creation)

Exec sp_trace_setstatus @traceid = 2 , @on = 0 -- stop
-- To Delete the trace, once application test is completed.
--Exec sp_trace_setstatus @traceid = 2 , @on = 0 -- Delete definition from server

5. Insert the trace output in dummy table, this helps to get the data in easy to read format.

USE [DB_name]
GO
SELECT * INTO RDSSQLTrace_20210722_1215 FROM ::fn_trace_gettable ('D:\RDSDBDATA\Log\RDSSQLTrace_2021-07-22_12-15-19.trc', default)
6. Capture the output of below commands and update the output in the attached excel sheet template in the TraceData sheet (File Name : Capture_ExpensiveQueries_Template.xlsx)

--To collect queries based on max Duration 
SELECT TOP 10 StartTime,EndTime,Reads, Writes, 
Duration/1000000 DurationSec,CPU, TextData,RowCounts,sqlHandle,[DatabaseName],
SPID,LoginName,EventClass FROM [gptst].[dbo].[RDSSQLTrace_20210722] 
ORDER BY Duration DESC

--To collect queries based on max CPU utilization
SELECT TOP 10 StartTime,EndTime,Reads, Writes, 
Duration/1000000 DurationMs,CPU, TextData,RowCounts,sqlHandle,[DatabaseName],
SPID,LoginName,EventClass FROM [gptst].[dbo].[RDSSQLTrace_20210722] 
ORDER BY CPU DESC

--To collect queries based on max Reads,Writes
SELECT TOP 10 StartTime,EndTime,Reads, Writes, 
Duration/1000000 DurationSec,CPU, TextData,RowCounts,sqlHandle,[DatabaseName],
SPID,LoginName,EventClass FROM [gptst].[dbo].[RDSSQLTrace_20210722]
ORDER BY Reads,Writes DESC

7. Also, capture the output of the below scripts as well and update the same in the attached excel sheet template in the CacheData sheet (File Name : Capture_ExpensiveQueries_Template.xlsx)

--TOP 10 Queries by Duration (Avg Sec) from system dmv's		
			
SELECT TOP 10 DB_NAME(qt.dbid) AS DBName,
o.name AS ObjectName,
qs.total_worker_time / 1000000 / qs.execution_count As Avg_CPU_time_sec,
qs.total_worker_time / 1000000 as Total_CPU_time_sec,
qs.total_elapsed_time / qs.execution_count / 1000000.0 AS Average_Seconds,
qs.total_elapsed_time / 1000000.0 AS Total_Seconds,
'',
qs.execution_count as Count,
qs.last_execution_time as Time,
SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS Query
--,qt.text [MainQuenry]
--,qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
where qs.execution_count>5 and DB_NAME(qt.dbid) not in ( 'master','msdb','model')
--and DB_NAME(qt.dbid) is not null
ORDER BY average_seconds DESC

--TOP 10 Queries by CPU (Avg CPU time) from system dmv's

SELECT TOP 10 DB_NAME(qt.dbid) as DBName,
	qs.total_worker_time / 1000000 / qs.execution_count AS Avg_CPU_time_sec,
	qs.total_worker_time / 1000000 As Total_CPU_time_sec,
	qs.total_elapsed_time / 1000000 / qs.execution_count As Average_Seconds,
	qs.total_elapsed_time / 1000000 As Total_Seconds,
    (total_logical_reads + total_logical_writes) / qs.execution_count as Average_IO,
	total_logical_reads + total_logical_writes as Total_IO,	
    qs.execution_count as Count,
    qs.last_execution_time as Time,
	SUBSTRING(qt.[text], (qs.statement_start_offset / 2) + 1,
		(
			(
				CASE qs.statement_end_offset
					WHEN -1 THEN DATALENGTH(qt.[text])
					ELSE qs.statement_end_offset
				END - qs.statement_start_offset
			) / 2
		) + 1
	) as Query
   ,qt.text
	,qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
where 
DB_NAME(qt.dbid) not in ( 'master','msdb','model')
ORDER BY Avg_CPU_time_sec DESC

-- Top 10 query by IO (Avg IO) from system dmv's

SELECT TOP 10 DB_NAME(qt.dbid) AS DBName,
o.name AS ObjectName,
qs.total_elapsed_time / 1000000 / qs.execution_count As Average_Seconds,
qs.total_elapsed_time / 1000000 As Total_Seconds,
(total_logical_reads + total_logical_writes ) / qs.execution_count AS Average_IO,
(total_logical_reads + total_logical_writes ) AS Total_IO,
'',
qs.execution_count AS Count,
last_execution_time As Time,
SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS Query
--,qt.text
--,qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
where last_execution_time > getdate()-1 
and DB_NAME(qt.dbid) not in ( 'master','msdb','model')
--and DB_NAME(qt.dbid) is not null
and qs.execution_count > 5 
ORDER BY average_IO DESC

Note : Please do capture the output after each run if running the test multiple times, to compare the result set.

SQL AlwaysON : TSQL Script to automatically add a new step to TestForPrimary

TSQL Script to automatically add a new step to TestForPrimary for SQL AlwaysOn Solution:

USE [DBA]
GO
SET NOCOUNT ON
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--Job

DECLARE @jobID nvarchar(40),@stepID nvarchar(40),@stepID_nxt nvarchar(40),@procName varchar (255),
@jobName varchar (255), @command_job varchar(8000),@command_step varchar(8000),
@command_end varchar(8000),@command_sched varchar(8000),
@CommandString NVARCHAR(1000),@command_main nvarchar(max)

DECLARE @job_name as varchar(255),@owner_login_name as varchar(255),@description as varchar(255),
@category_name as varchar(255),@enabled as nvarchar(4)

DECLARE @notify_level_email as nvarchar(4),@notify_level_page as nvarchar(4),
@notify_level_netsend as nvarchar(4)

DECLARE @notify_level_eventlog as nvarchar(4),@delete_level as nvarchar(4),
@start_step_id nvarchar(4)

--Job Steps

DECLARE @step_name varchar(255),@command varchar(8000),@database_name varchar(255),
@database_user_name varchar(255), @subsystem nvarchar(40)

DECLARE @cmdexec_success_code nvarchar(2),@flags nvarchar(2),@retry_attempts nvarchar(2),
@retry_interval nvarchar(2),@output_file_name varchar (255)

DECLARE @on_success_step_id nvarchar(3),@on_success_action nvarchar(2),
@on_fail_step_id nvarchar(2),@on_fail_action nvarchar(2)

     
SET @CommandString = 'use master
GO
Declare @AGName varchar(10);
SELECT TOP(1) @AGName= name FROM sys.availability_groups;

if (SELECT ars.role_desc from sys.dm_hadr_availability_replica_states
ars inner join sys.availability_groups ag 
on ars.group_id = ag.group_id where ag.name = @AGName and ars.is_local = 1) = ''PRIMARY''
BEGIN
PRINT ''This is Primary Replica - execute job''
END

ELSE

BEGIN
-- we''re not in the Primary - exit gracefully: Deliberately cause a Failure
SELECT 1/0
PRINT ''This is Secondary replica - exiting with success'';
END'


if EXISTS(select * from DBA.INFORMATION_SCHEMA.TABLES where table_name='server_agent_jobs')

BEGIN

DROP TABLE DBA..server_agent_jobs

End

--ignore multi-server jobs at this time

select sj.job_id, name, '0' as executed,category_id,'1' as spcreated,
'1999-12-31 00:0000000' as createddate,
('EXECUTE [dbo].[sp_create_server_agent_job_' + name +']') as excmd,
('sp_create_server_agent_job_' + name) as spname into DBA..server_agent_jobs 
from msdb..sysjobs sj inner join msdb..sysjobservers ss on ss.job_id = sj.job_id 
and ss.server_id = '0' order by sj.job_id

WHILE EXISTS (select job_id from DBA..server_agent_jobs where executed = '0')

BEGIN

--get working job_id and stored proc name

SELECT top 1 @jobID = job_id, @procName = 'sp_create_server_agent_job_' + name, @jobName = name
FROM DBA..server_agent_jobs WHERE executed = '0' order by job_id


--get job info from sysjobs

select @job_name = a.name,@owner_login_name = b.name,@description = a.description,
@category_name = c.name,@enabled = a.enabled,
@notify_level_email = a.notify_level_email,@notify_level_page = a.notify_level_page,
@notify_level_netsend = a.notify_level_netsend,@notify_level_eventlog = a.notify_level_eventlog,
@delete_level = a.delete_level, @start_step_id = a.start_step_id
FROM msdb..sysjobs a, master..syslogins b, msdb..syscategories c
WHERE a.owner_sid = b.sid and a.category_id = c.category_id and job_id = @jobID

--get job info from sysjobsteps

if EXISTS(select * from DBA.INFORMATION_SCHEMA.TABLES where table_name='server_agent_job_steps')

BEGIN
DROP TABLE DBA..server_agent_job_steps
End

select *, '0' as executed into DBA..server_agent_job_steps
from msdb..sysjobsteps sjs where job_id = @jobID order by sjs.step_id

if EXISTS(select * from DBA.INFORMATION_SCHEMA.TABLES
where table_name='server_agent_job_schedules')

BEGIN
DROP TABLE DBA..server_agent_job_schedules
End

select *, '0' as executed into DBA..server_agent_job_schedules from msdb..sysjobschedules sjs
where job_id = @jobID order by sjs.schedule_id

--drop proc if it exists before it's re-created

if EXISTS(select * from DBA.INFORMATION_SCHEMA.ROUTINES where routine_name = @procName)
BEGIN
exec('DROP PROCEDURE [' + @procName + ']')
End

SET CONCAT_NULL_YIELDS_NULL OFF

select @command_job = 'CREATE Procedure [dbo].[' + @procName + '](
@operator varchar(255)=NULL
)

AS

BEGIN
SET NOCOUNT OFF
DECLARE @ReturnCode nvarchar (40)--, @jobID nvarchar (40)

Begin Transaction

--delete job if it already exists (by job name -> @jobName)

if EXISTS(select * from msdb..sysjobs where name = ''' + @jobName + ''')
begin
Print ''Job Updated''
end

--add job steps

'
WHILE EXISTS (select * from DBA..server_agent_job_steps where executed = '0')

BEGIN
select top 1 @stepID = step_id from DBA..server_agent_job_steps
where executed = '0' order by step_id desc

SELECT @step_name = a.step_name,@command = a.command, @database_name = a.database_name,
@database_user_name = a.database_user_name,
@subsystem = a.subsystem, @cmdexec_success_code = a.cmdexec_success_code, @flags = a.flags,
@retry_attempts = a.retry_attempts,
@retry_interval = a.retry_interval, @output_file_name = a.output_file_name,
@on_success_step_id = a.on_success_step_id,
@on_success_action = a.on_success_action, @on_fail_step_id = a.on_fail_step_id,
@on_fail_action = a.on_fail_action
FROM DBA..server_agent_job_steps a where a.step_id = @stepID

select @command_job = @command_job +'
EXECUTE msdb.dbo.sp_delete_jobstep
@job_id = ''' + @JobID + ''',
@step_id = '+ @stepID +'

'
update DBA..server_agent_job_steps set executed = '1' where step_id = @stepID
END

update DBA..server_agent_job_steps set executed = '0'

SELECT @command_step = 'EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep 
@job_id=''' + @JobID + ''',
   @step_name =''TestForPrimary'',
                  @step_id=1,
                  @cmdexec_success_code=0,
                  @on_success_action=3,
                  @on_success_step_id=0,
                  @on_fail_action=1,
                  @on_fail_step_id=0,
                  @retry_attempts=0,
                  @retry_interval=0,
                  @os_run_priority=0,@subsystem=''TSQL'',
                  @command = ''' + REPLACE (@CommandString,'''','''''') + ''',
                  @database_name=''master'',
                  @flags=0

'

WHILE EXISTS (select * from DBA..server_agent_job_steps where executed = '0')

BEGIN
select top 1 @stepID = step_id from DBA..server_agent_job_steps
where executed = '0' order by step_id

SELECT @step_name = a.step_name,@command = a.command,
@database_name = a.database_name,@database_user_name = a.database_user_name,
@subsystem = a.subsystem, @cmdexec_success_code = a.cmdexec_success_code,
@flags = a.flags, @retry_attempts = a.retry_attempts,
@retry_interval = a.retry_interval, @output_file_name = a.output_file_name,
@on_success_step_id = a.on_success_step_id,
@on_success_action = a.on_success_action, @on_fail_step_id = a.on_fail_step_id,
@on_fail_action = a.on_fail_action
FROM DBA..server_agent_job_steps a where a.step_id = @stepID

SET @stepID_nxt=@stepID+1

select @command_step = @command_step + 'EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep

@job_id = ''' + @JobID + ''',
@step_id = '''+ @stepID_nxt +''',
@step_name = ''' + @step_name + ''',
@command = ''' + REPLACE (@command,'''','''''') + ''',
@database_name = ''' + @database_name + ''',
@server = ''' + '' + ''',
@database_user_name = ''' + @database_user_name + ''',
@subsystem = ''' + @subsystem + ''',
@cmdexec_success_code = ''' + @cmdexec_success_code + ''',
@flags = ''' + @flags + ''',
@retry_attempts = ''' + @retry_attempts + ''',
@retry_interval = ''' + @retry_interval + ''',
@output_file_name = ''' + @output_file_name + ''',
@on_success_step_id = ''' + @on_success_step_id + ''',
@on_success_action = ''' + @on_success_action + ''',
@on_fail_step_id = ''' + @on_fail_step_id + ''',
@on_fail_action = ''' + @on_fail_action + '''

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

'

update DBA..server_agent_job_steps set executed = '1' where step_id = @stepID
END

--update start step id from sysjobs

select @command_step = @command_step + 'EXECUTE @ReturnCode = msdb.dbo.sp_update_job
@job_id = ''' + @JobID + ''',
@start_step_id = ''' + @start_step_id + '''

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

'

select @command_end = '

COMMIT TRANSACTION
GOTO THE_END

QuitWithRollback:
print ''job failed''

IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

THE_END:
END'

--print (@command_job + @command_step + @command_sched + @command_end)

SET CONCAT_NULL_YIELDS_NULL ON

execute (@command_job + @command_step + @command_sched + @command_end)

update DBA..server_agent_jobs set executed = '1',createddate=getdate()
where DBA..server_agent_jobs.job_id = @jobID

select @command_step = ''

END

update DBA..server_agent_jobs set spcreated = 0,excmd='--NA',spname='--NA'
where spname NOT IN (SELECT SPECIFIC_Name FROM DBA.INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE' and SPECIFIC_NAME like 'sp_create_server_agent_job_%')

SQL Server Delete Duplicate Rows from Table

In this post, I will demonstrate techniques to delete duplicate rows from SQL Server database table.

One day I was working on one of the freelancing project and found duplicates records in some of client tables.
I have found the work around and created solution to delete duplicate rows.
Let’s create sample table and insert data into it:

CREATE TABLE tbl_DuplicateData
( 
 ID INTEGER PRIMARY KEY
 ,Name VARCHAR(150)
)
GO
 
INSERT INTO tbl_DuplicateData VALUES 
((1,'Roy'),(2,'Ashish'),(3,'Tarun'),
(4,'Rahul'),(5,'Kapoor'),
(6,'Nadun'),(7,'Roy'),(8,'Rahul'),(9,'Kapoor'),
(10,'Rahul'),(11,'Roy'))
GO

There can be multiple solution and different way to get out of this issue:
1st solution is using WITH CTE:

;WITH cte_FindDuplicateRows AS 
(
    SELECT 
       Name
       ,ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Name) RowNumber
    FROM  tbl_DuplicateData
)
DELETE FROM cte_FindDuplicateRows
WHERE RowNumber > 1
GO

Another way is using SELF JOIN:

DELETE FROM A
FROM tbl_DuplicateData AS A
INNER JOIN tbl_DuplicateData AS B
  ON A.Name = B.Name AND A.ID > B.ID
GO

Check the table after deleting duplicate rows:

SELECT *FROM tbl_DuplicateData ORDER BY ID

As per requirement you can choose any of the solution and before executing please must check your query plan and choose best solution to delete duplicate rows.

SQL Server Database Growth Rates

Script to find the Growth Size for All files in All Databases:

SELECT   'Database Name' = DB_NAME(database_id)
,'FileName' = NAME
,FILE_ID
,'size' = CONVERT(NVARCHAR(15), CONVERT(BIGINT, size) * 8) + N' KB'
,'maxsize' = (CASE max_size 
WHEN - 1 THEN N'Unlimited'
ELSE CONVERT(NVARCHAR(15), CONVERT(BIGINT, max_size) * 8) + N' KB'
END)
,'growth' = (CASE is_percent_growth
WHEN 1 THEN CONVERT(NVARCHAR(15), growth) + N'%'
ELSE CONVERT(NVARCHAR(15), CONVERT(BIGINT, growth) * 8) + N' KB'
END)
,'type_desc' = type_desc
FROM sys.master_files
ORDER BY database_id

SQL Server catalog stores informations about every single database backup in msdb..backupset.
If you don’t have other mechanism to collect historical database size then this can be achieve by calculating Backup size from msdb..backupset table. This is the best step to start for a capacity planning through Backup history.
Following points to remember before starting:
– msdb..backupset stores historical informations about backup size NOT database file size. This is good for you if you need to understand how your real stored data are growing day by day but obviously datafiles are typically larger: there is empty space inside for future data.
– Every full database backup contains a little part of logs used for recover. For this reason the size reported is not always exactly your data dimension but usually this is not relevant.

Script to find out current and previous size (in megabytes), as well as Increased_Size, for all databases:

SELECT
s.[database_name]
,s.[backup_start_date]
,CAST( ( s.[backup_size] / 1024 / 1024 ) AS INT ) AS [BackupSize(MB)]
,CAST( ( LAG( s.[backup_size] ) 
OVER (PARTITION BY s.[database_name] ORDER BY s.[backup_start_date])/1024/1024) AS INT ) 
AS [Previous_BackupSize(MB)]
,(CAST( ( s.[backup_size] / 1024 / 1024 ) AS INT )-CAST( ( LAG( s.[backup_size] ) 
OVER ( PARTITION BY s.[database_name] ORDER BY s.[backup_start_date] ) / 1024 / 1024 ) AS INT ))
AS [Increased_Size(MB)]
FROM [msdb]..[backupset] s
WHERE s.[type] = 'D' 
ORDER BY s.[database_name],s.[backup_start_date];
GO

To calculate the same for individual database and also by the no. of days:

Declare @dbname nvarchar(1024)    
Declare @days int              
          
--Configure HERE database name  
set @dbname ='YourDB_Name'  
--and number of days to analyse  
set @days   =365;  
  
--Daily Report  
WITH TempTable(Row,database_name,backup_start_date,Mb)   
as   
( SELECT   
ROW_NUMBER() OVER(order by backup_start_date) as Row, 
database_name,
backup_start_date,
cast(backup_size/1024/1024 as decimal(10,2)) MB
from msdb..backupset
where
type='D' and
database_name=@dbname and
backup_start_date>getdate()-@days )
select
A.database_name,
A.backup_start_date,
A.Mb as daily_backup
A.Mb - B.Mb as increment_mb
from TempTable A left join TempTable B on A.Row=B.Row+1  
order by database_name,backup_start_date 

SQL Server Basic Useful Queries

Basic and very useful commands\Scripts:

1. Command to get SQL Server Version Details

  SELECT
  SERVERPROPERTY    ('productversion') As ProductionVersion,
  SERVERPROPERTY    ('productlevel') As ProductionLevel,
  SERVERPROPERTY    ('edition') As Edition,
  @@version As Version;
  GO

2. Command to check database files full status by percent:
SELECT 
            name as DB_FileName,
     convert(varchar(100), FileName) as FileName, 
            cast(Size as decimal(15,2))*8/1024 as 'SizeMB', 
            FILEPROPERTY (name, 'spaceused')*8/1024 as 'UsedMB',
            Convert(decimal(10,2),convert(decimal(15,2), (FILEPROPERTY (name, 'spaceused')))/ 
            Size*100) as PercentFull,
            filegroup_name(groupid) as 'FileGroup',
            FILEPROPERTY (name, 'IsLogFile') as 'IsLogFile' 
      FROM dbo.sysfiles order by IsLogFile
      GO 

---

3. Steps to shrink database log file:

i. Check the log files status using above command.
ii. If log file is full means above 90%, then try to take the backup of the database.
and then run checkpoint using below command:

Use 
GO
CHECKPOINT;

iii. Once checkpoint finished, try to shrink the file using below command:

Use 
GO
DBCC SHRINKFILE(,1)
GO

That's it.

-- Command to get the list of shrink file command for 
--all the databases where log file is full more than 50%

  DECLARE @cSQl varchar(1000) 
  Set @cSQl= 'use [?];select ''use '' + DB_Name() + '' dbcc shrinkfile('' + name +'',1)'' from dbo.sysfiles
  Where FILEPROPERTY (name, ''IsLogFile'')=1 

  and Convert(decimal(10,2),

  convert(decimal(15,2),(FILEPROPERTY (name, ''spaceused'')))/Size*100)<50 

---

4. Command to get DB growth trend on backup basis.

        DECLARE @cmd varchar(4000)

        DECLARE @recid int
 DECLARE @busize decimal(10,2)
 DECLARE @prevbusize decimal(10,2)
 DECLARE @pctchange decimal(10,2)
 DECLARE @dbname varchar(128)

 SET @dbname = 'Master' -- SET THE DATABASE NAME HERE

 CREATE TABLE #dummybackupsizes
 (
 RecID int IDENTITY,
 DatabaseName varchar(128),
 StartDate datetime,
 FinishDate datetime,
 BackupSizeMB decimal(10,2),
 PctChangeFromPrev decimal(10,2)
 )

 INSERT INTO #dummybackupsizes
 SELECT CONVERT(varchar(128),S.database_name) AS DatabaseName, S.backup_start_date AS StartDate, 

        S.backup_finish_date AS FinishDate, CONVERT(decimal(10,2),S.backup_size/1024.0/1024.0) 

        AS BackupSizeMB, CONVERT(decimal(10,2),0.0) AS PctChangeFromPrev
 FROM msdb..backupset S 
 JOIN msdb..backupmediafamily M ON M.media_set_id=S.media_set_id
 WHERE S.database_name = @dbname AND S.type = 'D' AND (DAY(S.backup_start_date) = 1 

        OR DAY(S.backup_start_date) = 8 OR DAY(S.backup_start_date) = 15 

        OR DAY(S.backup_start_date) = 28)
 ORDER by backup_start_date DESC

 DECLARE myCursorVariable CURSOR FOR 
 SELECT RecID, BackupSizeMB FROM #dummybackupsizes

---

5. Script to find User Details:

 CREATE TABLE #DB (ServerName VARCHAR(100),
          DBName VARCHAR(100),
          USER_NAME VARCHAR(100),
          ROLES VARCHAR(100),
          LOGIN_NAME VARCHAR(100),
          Def_DB VARCHAR(100),
          Def_SCHEMA VARCHAR(100),
          USERID VARCHAR(100),
          SID VARCHAR(100)
       )

 Exec sp_MSforeachdb 'Use ? ;
 INSERT INTO #DB (User_Name, Roles,Login_Name,Def_db,Def_Schema,UserID,SID) EXEC SP_HELPUSER
 --SELECT @@servername as SERVER_NAME,DB_NAME() AS DATABASE_NAME,

LOGIN_NAME,USER_NAME,ROLES FROM #DB 
 Update #DB SET ServerName = @@SERVERNAME where ServerName IS NULL
 Update #DB SET DBNAME = DB_NAME() where DBNAME IS NULL'
 Select * from #DB
 DROP TABLE  #DB 

---

6. Script to Backup DB Roles & permission

--Script to backup DB roles & Users Permission—run as SQLCMD mode 

 :setvar dbname "test1"

 DECLARE @DBName varchar(100)
 SELECT @DBName=name from sys.databases where name='$(dbname)'
 IF @DBName IS NULL
 BEGIN
     SELECT '$(dbname)' + ' database does not exists' Message
     union
     SELECT 'No permisison generated'
 END
 ELSE IF @DBName IS NOT NULL
 BEGIN

 SET NOCOUNT ON
 --USE '$(dbname)'
 DECLARE @SQLStatement VARCHAR(4000)
 DECLARE @T_DBuser TABLE (DBName SYSNAME, UserName SYSNAME, AssociatedDBRole NVARCHAR(256),

[type] nvarchar(1) NULL)
 DECLARE @UserPermisison TABLE (ID INT IDENTITY(1,1),UserPermissonScript nvarchar(MAX))
 DECLARE @OutPermission Table (ID INT IDENTITY(1,1),UserPermissonScript nvarchar(MAX))
 DECLARE @Databasepermission Table (UserPermissonScript nvarchar(MAX))
 DECLARE @outpt Table (UserPermissonScript nvarchar(MAX))

 DECLARE @Start_loop int
 DECLARE @End_loop int

 SET @SQLStatement='SELECT 
    CASE dp.state_desc 
   WHEN ''GRANT_WITH_GRANT_OPTION'' THEN ''GRANT'' 
   ELSE dp.state_desc  
    END  
   + '' '' + dp.permission_name + '' ON '' + 
    CASE dp.class 
   WHEN 0 THEN ''DATABASE::['' + DB_NAME() + '']'' 
   WHEN 1 THEN ''OBJECT::['' + SCHEMA_NAME(o.schema_id) + ''].['' + o.[name] + '']'' 
   WHEN 3 THEN ''SCHEMA::['' + SCHEMA_NAME(dp.major_id) + '']'' 
    END  
   + '' TO ['' + USER_NAME(grantee_principal_id) + '']'' + 
    CASE dp.state_desc 
   WHEN ''GRANT_WITH_GRANT_OPTION'' THEN '' WITH GRANT OPTION;'' 
   ELSE '';''  
    END  
    COLLATE DATABASE_DEFAULT 
 FROM '+@DBName+'.sys.database_permissions dp 
   LEFT JOIN '+@DBName+'.sys.all_objects o 
  ON dp.major_id = o.OBJECT_ID 
 WHERE dp.class < 4 
   AND major_id >= 0 
   AND grantee_principal_id <> 1;'   

   INSERT @Databasepermission
 EXEC (@SQLStatement)

 SET @SQLStatement=''

 SET @SQLStatement='
 SELECT db_name() AS DBName ,dp.name AS UserName,
        USER_NAME(drm.role_principal_id) AS AssociatedDBRole,dp.type
 FROM '+@DBName+'.sys.database_principals dp
 LEFT OUTER JOIN '+@DBName+'.sys.database_role_members drm
 ON dp.principal_id=drm.member_principal_id
 WHERE dp.sid NOT IN (0x01) AND dp.sid IS NOT NULL AND dp.type NOT IN (''C'') AND dp.is_fixed_role <> 1
 AND dp.name NOT LIKE ''##%'' AND dp.name not in (''public'',''dbo'',''guest'') ORDER BY DBName'
 INSERT @T_DBuser
 EXEC (@SQLStatement)
 insert into @UserPermisison(UserPermissonScript)
 SELECT 'EXEC '+@DBName+'.dbo.sp_changedbowner ''sa'''
 UNION
 SELECT 'EXEC '+@DBName+'.dbo.sp_addrolemember ''' + AssociatedDBRole +''''+','''+UserName+'''' 

        FROM @T_DBuser where AssociatedDBRole IS NOT NULL
 UNION
 SELECT DISTINCT 'USE ' + @DBName + '  CREATE USER [' +   UserName +'] FOR LOGIN ['+UserName+']' 

         FROM @T_DBuser WHERE type='S'
 UNION
 SELECT DISTINCT 'USE ' + @DBName + '  CREATE USER [' +   UserName +'] FOR LOGIN ['+UserName+']' 

        FROM @T_DBuser WHERE type='U'
 UNION
 SELECT DISTINCT 'USE ' + @DBName + '  CREATE ROLE [' +   UserName +']' FROM @T_DBuser WHERE type='R'
 UNION
 SELECT * FROM @Databasepermission
 --write code

   SET @Start_loop=0
   SELECT @End_loop=count(*) FROM @UserPermisison 

   while @Start_loop<=@End_loop
   BEGIN
   insert into @OutPermission(UserPermissonScript)
   select UserPermissonScript from @UserPermisison where ID=@Start_loop
   insert into @OutPermission(UserPermissonScript) values ('GO')
              SET @Start_loop=@Start_loop + 1 
   END
   update @OutPermission SET UserPermissonScript=  REPLACE(UserPermissonScript, 'master', @DBName)
   select UserPermissonScript as 'SELECT @@serverName' from @OutPermission order by ID desc
   --select * from @T_DBuser
 SET NOCOUNT OFF
 END

---

7. Powershell command to get the Server Disk space details remotely:
/*Enter COMPUTERNAME */
Get-WmiObject Win32_volume -ComputerName COMPUTERNAME|Format-Table Name, Label, 
@{Name="Size(GB)";Expression={[decimal]("{0:N0}" -f($_.capacity/1gb))}}, 
@{Name="Free Space(GB)";Expression={[decimal]("{0:N0}" -f($_.freespace/1gb))}}, 
@{Name="Free (%)";Expression={"{0,6:P0}" -f(($_.freespace/1gb) / ($_.capacity/1gb))}} –AutoSize

---

 

8.Script to find out CPU Usage, I/O Usage and Memory Usage of database
--Database level / Database wise CPU, memory and I/O usage
--As part of DBA’s daily checklist, we need to monitor few parameters of a database throughout the day. 
--It includes CPU utilization, 

--Memory utilization and I/O utilization. Here are the T-SQL scripts to monitor sql server instances database wise.

 WITH DB_CPU_StatsAS(SELECT DatabaseID,DB_Name(DatabaseID)AS [DatabaseName],
 SUM(total_worker_time)AS [CPU_Time(Ms)]
 FROM sys.dm_exec_query_stats AS qsCROSS APPLY
 (SELECT CONVERT(int, value)AS [DatabaseID] 
 FROM sys.dm_exec_plan_attributes(qs.plan_handle) 
 WHERE attribute =N'dbid')AS epaGROUP BY DatabaseID)
 SELECT ROW_NUMBER()OVER(ORDER BY [CPU_Time(Ms)] DESC)
 AS [row_num],DatabaseName,[CPU_Time(Ms)],
 CAST([CPU_Time(Ms)] * 1.0 /SUM([CPU_Time(Ms)])OVER()* 100.0 AS DECIMAL(5, 2))AS [CPUPercent]
 FROM DB_CPU_StatsWHERE DatabaseID >4 --system databases
 AND DatabaseID <> 32767 -- Resource DB
 ORDER BY row_numOPTION(RECOMPILE);

---

9. Command to get Database last accessed date:

 SELECT DatabaseName, MAX(LastAccessDate) LastAccessDate
 FROM
 (SELECT
  DB_NAME(database_id) DatabaseName
 , last_user_seek
 , last_user_scan
  , last_user_lookup
 , last_user_update
 FROM sys.dm_db_index_usage_stats) AS PivotTable
 UNPIVOT 
 (LastAccessDate FOR last_user_access IN
 (last_user_seek
  , last_user_scan
 , last_user_lookup
 , last_user_update)
  ) AS UnpivotTable
 GROUP BY DatabaseName
 HAVING DatabaseName NOT IN ('master', 'tempdb', 'model', 'msdb')
  ORDER BY 2 

---

10. Command to check Blocking

 Sp_who2
-- To get the details of all the sessions
  SELECT s.session_id, r.blocking_session_id, db_name(r.database_id) as [Database], 

  r.wait_time, s.login_name, s.cpu_time/60000 as [CPU_Time(mins)], 
  s.memory_usage, r.[status], r.percent_complete, r.nest_level, [Text] 
  FROM sys.dm_exec_sessions as s inner join sys.dm_exec_requests as r on s.session_id = r.session_id 
  cross apply sys.dm_exec_sql_text (sql_handle) WHERE s.status = 'running' 
  order by s.cpu_time desc 
-- To get the details of Active sessions
   Sp_who2 ‘active’
-- Get the info in details
  SELECT text,program_name,hostname,* FROM master..sysprocesses 
  cross apply sys.dm_exec_sql_text(sql_handle) 
  WHERE spid='enterSpid'
--To Kill Process
  KILL Spid;
  Example:
  KILL 56 WITH STATUSONLY;
  GO

---

11.Command to get TOP 15 Cached plans Script
--Run the following query to get the TOP 15 cached plans that consumed the most cumulative CPU, 

--All times are in microseconds 
SELECT TOP 15 t.dbid, t.[text], qp.query_plan, qs.total_worker_time as total_cpu_time, qs.max_worker_time as max_cpu_time, qs.creation_time, qs.execution_count, qs.total_elapsed_time, qs.max_elapsed_time, qs.total_logical_reads, qs.max_logical_reads, qs.total_physical_reads, qs.max_physical_reads, t.objectid, t.encrypted, qs.plan_handle, qs.plan_generation_num FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp ORDER BY qs.total_worker_time DESC

---

12. Few useful queries to get cluster details:
Find name of the Node on which SQL Server Instance is Currently running
SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [CurrentNodeName] 
If the server is not cluster, then the above query returns the Host Name of the Server.
Find SQL Server Cluster Nodes
a. Using Function
SELECT * FROM fn_virtualservernodes() 

b. Using DMV 
SELECT * FROM sys.dm_os_cluster_nodes 

Find SQL Server Cluster Shared Drives
a. Using Function
SELECT * FROM fn_servershareddrives() 

b. Using DMV
SELECT * FROM sys.dm_io_cluster_shared_drives

---

13. Command to check Backup history:

SELECT 
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_start_date, 
msdb.dbo.backupset.backup_finish_date, 
msdb.dbo.backupset.expiration_date, 
CASE msdb..backupset.type 
WHEN 'D' THEN 'Database' 
WHEN 'L' THEN 'Log' 
END AS backup_type, 
msdb.dbo.backupset.backup_size, 
msdb.dbo.backupmediafamily.logical_device_name, 
msdb.dbo.backupmediafamily.physical_device_name, 
msdb.dbo.backupset.name AS backupset_name, 
msdb.dbo.backupset.description 
FROM msdb.dbo.backupmediafamily 
INNER JOIN msdb.dbo.backupset 
ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7) 
ORDER BY 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_finish_date desc

---

14.Query to check the running process status by percent:

select
@@servername
, session_id, start_time, status
, percent_complete
, total_elapsed_time/1000/60 as 'elapsed min'
, command, db_name(database_id) as dbname, blocking_session_id
, total_elapsed_time/1000/60/60 as 'elapsed Hrs'
,estimated_completion_time
from sys.dm_exec_requests
where session_id > 50
 

15. Identify the blocking query

SELECT
db.name DBName,
tl.request_session_id,
wt.blocking_session_id,
OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
tl.resource_type,
h1.TEXT AS RequestingText,
h2.TEXT AS BlockingTest,
tl.request_mode
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address =wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id =tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id =tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id =wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
GO
 
Script to view all current processes / sessions on the server

SELECT * from master.dbo.sysprocesses


16.  Query to identify Login details and created date

USE                               
GO                              
SELECT name, createDate                                 
FROM sysusers      
Where name = 'Domain\ID'
GO





17
--Server level Logins and roles
SELECT sp.name AS LoginName,sp.type_desc AS LoginType, 
sp.default_database_name AS DefaultDBName,slog.sysadmin AS SysAdmin,
slog.securityadmin AS SecurityAdmin,slog.serveradmin AS ServerAdmin, 
slog.setupadmin AS SetupAdmin, slog.processadmin AS ProcessAdmin, 
slog.diskadmin AS DiskAdmin, slog.dbcreator AS DBCreator,
slog.bulkadmin AS BulkAdmin
FROM sys.server_principals sp  JOIN master..syslogins slog
ON sp.sid=slog.sid 
WHERE sp.type  <> 'R' AND sp.name NOT LIKE '##%'






18
--Databases users and roles
DECLARE @SQLStatement VARCHAR(4000) 
DECLARE @T_DBuser TABLE (DBName SYSNAME, UserName SYSNAME, AssociatedDBRole NVARCHAR(256)) 
SET @SQLStatement='
SELECT ''?'' AS DBName,dp.name AS UserName,USER_NAME(drm.role_principal_id) AS AssociatedDBRole 
FROM ?.sys.database_principals dp
LEFT OUTER JOIN ?.sys.database_role_members drm
ON dp.principal_id=drm.member_principal_id 
WHERE dp.sid NOT IN (0x01) AND dp.sid IS NOT NULL AND dp.type NOT IN (''C'') 
AND dp.is_fixed_role <> 1 
AND dp.name NOT LIKE ''##%'' AND ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') ORDER BY DBName'
INSERT @T_DBuser
EXEC sp_MSforeachdb @SQLStatement
SELECT * FROM @T_DBuser ORDER BY DBName





19
--Get objects permission of specified user database 
USE 
GO
DECLARE @Obj VARCHAR(4000)
DECLARE @T_Obj TABLE (UserName SYSNAME, ObjectName SYSNAME, Permission NVARCHAR(128))
SET @Obj='
SELECT Us.name AS username, Obj.name AS object,  dp.permission_name AS permission 
FROM sys.database_permissions dp
JOIN sys.sysusers Us 
ON dp.grantee_principal_id = Us.uid 
JOIN sys.sysobjects Obj
ON dp.major_id = Obj.id '
INSERT @T_Obj 
EXEC sp_MSforeachdb @Obj
SELECT * FROM @T_Obj 


20.
Command to get the list of Stored Procedures
USE 
GO
SELECT name, create_date, modify_date 
FROM sys.objects
WHERE type = 'P' 


21. Script to get the UserDetails
-- This will give you userid, username, LoginType, DBROles, CreatedDate, is_disbaled status.

DECLARE @User_Tbl TABLE
(ServerName sysname, DBName sysname, UserID int, UserName sysname, LoginType sysname, 
AssociatedRole varchar(max), UserStatus varchar(10), create_date datetime,modify_date datetime)
 
INSERT @User_Tbl
EXEC sp_MSforeachdb
'USE [?]
SELECT 
@@ServerName As ServerName,
''?'' AS DB_Name,
su.uid,
case dp.name when ''dbo'' then dp.name + '' (''+ 
(select SUSER_SNAME(owner_sid) 
from master.sys.databases where name =''?'') + '')'' else dp.name end AS UserName,
dp.type_desc AS LoginType,
isnull(USER_NAME(mem.role_principal_id),'''') AS AssociatedRole ,
CASE sp.is_disabled 
WHEN 0 THEN ''Enabled''
WHEN 1 THEN ''Disabled''
ELSE ''NA''
END As [UserStatus],
dp.create_date,
dp.modify_date
FROM sys.database_principals dp
LEFT JOIN sysusers su ON dp.sid=su.sid
LEFT OUTER JOIN sys.database_role_members mem ON dp.principal_id=mem.member_principal_id
LEFT OUTER JOIN sys.Server_principals sp ON sp.sid=dp.sid
WHERE dp.sid IS NOT NULL and dp.sid NOT IN (0x00) and
dp.is_fixed_role <> 1 AND dp.name NOT LIKE ''##%'''
 
SELECT ServerName, DBname,UserID, UserName ,LoginType, 
STUFF((SELECT ',' + CONVERT(VARCHAR(200),associatedrole)
FROM @User_Tbl usertbl2
WHERE usertbl1.DBName=usertbl2.DBName AND usertbl1.UserName=usertbl2.UserName
FOR XML PATH('')),1,1,'') AS UserPermissions, UserStatus, Create_Date 
FROM @User_Tbl usertbl1  
GROUP BY ServerName, DBname ,UserID, UserName ,LoginType, UserStatus, Create_Date
ORDER BY DBName, UserName

SQL Basic Functions Chapter2

LTRIM and RTRIM Functions

Ø  LTRIM Function

Syntax:

SELECT LTRIM (' Amit') AS Names

Result:

Names

Amit

LTRIM in sql server removes blanks from the beginning (left) of a string. For example, if three blank spaces appear to the left of a string such as ' Amit', you can remove the blank spaces with the above query.

It does not matter how many blank spaces precede the non-blank character. All leading blanks will be excised.

Ø  RTRIM Function

Syntax:

SELECT RTRIM ('Amit ') + Bhardwaj AS Names

 Result:

Names

Amit Bhardwaj

Similarly, RTRIM in sql server removes blanks from the end (right) of a string. For example, if blank spaces appear to the right of Amit in the names column, you could remove the blank spaces using the RTRIM, and then concatenate "Bhardwaj" with the + sign, as shown above.

 

Length Function

 Syntax:

Len(string)
OR SELECT LEN(column_name) FROM tbl_name

Output:

SELECT Len ("Learnsqlserver")

would return 14

 

Use of LIKE in SQL Query

 The LIKE in sql server allows you to use wildcards in the where clause of an SQL server statement. This allows you to perform pattern matching.
You can use LIKE condition with in your select, insert, update, or delete sql statements.
The patterns that you can choose from are:

% allows you to match any string of any length.
_ allows you to match on a single character

E.g

SELECT * FROM tbl_Student WHERE sname LIKE '%Amit%'

 

Date Function

Syntax

SELECT GETDATE(), -- Current Date and Time
CURRENT_TIMESTAMP, -- Current Date and Time

Output

SELECT GETDATE()

would return 2011-09-14 20:24:17.060

 

 

REPLICATE Function

The REPLICATE function repeats a given character expression a designated number of times.

Syntax:

REPLICATE ( character_expression ,integer_expression )

Result:

SELECT REPLICATE ('AZ ', 15)

This returns:
AZ AZ AZ AZ AZ AZ AZ AZ AZ AZ AZ AZ AZ AZ AZ

SQL Basic Functions Chapter1

SQL Functions

 Ø  SUM Function: to get sum the values of the column

Syntax:

SELECT SUM (column_name) FROM Table_Name

Example:

SELECT SUM (Hours) FROM tbl_Abc

Ø  AVG Function: to get the avg of the column

SELECT AVG (hours) FROM tbl_Abc

Ø  Min/MAX Function:

Syntax:

SELECT MIN (wage) AS [Minimum Wage],
MAX(wage) AS [Maximum Wage] FROM tbl_Abc

Example:
MIN Function With BETWEEN Operator

SELECT OrderID, MIN(Quantity) as Quantity
FROM [Order Details]
WHERE OrderID BETWEEN 11000 AND 11002
GROUP BY OrderID

OrderID	Quantity
11000	25
11001	6
11002	15

Modify it one more time for the MAX function:

SELECT OrderID, MAX(Quantity) Quantity
FROM [Order Details]
WHERE OrderID BETWEEN 11000 AND 11002
GROUP BY OrderID

OrderID	Quantity
11000	30
11001	60
11002	56

Ø  Round Function

Syntax:

Select Round (number, n)

Example:

SELECT ROUND (1.2536, 3) as Roundof;
SELECT ROUND (1.2536, 2) as roundof;

Result:

Roundof: - 1.2540Roundof: - 1.2500

Ø  Ceiling Function

Syntax:

Ceiling (number value)

 Number value is the value which is used to find the smallest integer value. Example:

Select Ceiling (32.65) as Ceiled                                                   would return, Ceiled: 33
select Ceiling (32) as Ceiled                                                          would return, Ceiled: 32
Select Ceiling (-32.65) as Ceiled                                                  would return, Ceiled: 32
Select Ceiling (-32) as Ceiled                                                        would return, Ceiled: -32

 Ø  Floor Function:

        Syntax:

Floor (Any number)

 Example:

SELECT FLOOR(4.88)

would return 4

 

Ø  Sqrt Function:

Syntax:

sqrt( value)

Example:

SELECT SQRT(9)

would return 3

 

Ø  Top Function:

Syntax:
Select Top (any number) from table name

 Example:

SELECT TOP 5 Name, CreditRating

FROM tbl_Vendor
ORDER BY CreditRating DESC, Name;

Result:

Name	CreditRating
Merit Bikes	5
Victory Bikes	5
Proseware, Inc.	4
Recreation Place	4
Consumer Cycles	3

 Ø  Bottom Function:

Let’s we’ve below table

Name	Wage
Mike	$250
Jack	$328
Gonaa	$450
Darek	$678
jacob	$1,000
Michel	$870 Bottom of Form

 

 

 

Syntax with Example:

SELECT TOP 2 names, wage FROM tbl_Employee
ORDER BY wage DESC

Result:

Name	Wage
Michel	$870
jacob	$1,000 Bottom of Form

Ø  Distinct Function:

Let’s we’ve below table

Category

A

B

C

A

F

None

 

 

 

Syntax:

SELECT DISTINCT columns FROM tables WHERE predicates;

Example:

SELECT DISTINCT Category FROM Tbl_Category;

Result:

Category

Michel

jacob

 Ø  SQL LEFT/RIGHT

Syntax for LEFT:

SELECT names, LEFT (names, 3) AS [left] FROM tbl_Employee

Syntax for RIGHT:

SELECT names, Right (names, 3) AS [Right] FROM tbl_Employee

Names	Left	Right
Sumon Bagui	Sum	gui
Sudip Bagui	Sud	gui
Priyashi Saha	Pri	aha
Ed Evans	Ed	ans