Share this:

For every developer whether he is fresher or experience should know how SQL internally execute the query in order to dig if there is challenge in future related to performance.

• whenever there is new request come for the SQL server in the form of SQL statement then, request created and pass to SQL server using network protocol, this task is queued in Task Queue and wait for the worker to take it further.
• Idle worker take the task and pass to execution unit, task execution unit perform the syntax checking using parsing and compilation process and once that is completed it look for the best execution plan using optimization process.
• Once execution plan is prepared, Operators access the data through buffer pool.
• Buffer pool is responsible for extracting data from file system (database).
• Data returns to the requested client and not wait for the completion, the worker process process not released till complete data extracted and return to the calling program.
• Once task completed, worker returns to the idle state.

 

Share this:

Generally, when the log file size become very huge and even after shrinking the same still we face file size issue in that case we take an action to regenerate the new LDF file for our database. There are different ways to achieve the same, but the best way to achieve it is as follow:

1. use sp_helpdb yourdbname and note down the path where .mdf and .ldf files are stored.
2. execute the sp_detach_dp yourdbname command to detach the database Syntax

   sp_detach_db [ @dbname = ] 'dbname' 
                [ , [ @skipchecks = ] 'skipchecks' ]

   example:

   EXEC sp_detach_db 'youdbname', 'false'

3. Move the original database files to some other location or rename them.
4. Create database with same name using CREATE DATABASE yourdbname command.
5. Stop SQL server and replace the .mdf file.
6. Start SQL server.
7. Database will be shown in pending recovery mode.
8. Issue below list of commands to bring the database online

Use Master

Go

Alter Database yourdbname Set Emergency

Go

Alter Database yourdbname Set Single_User

Go

DBCC CheckDB(yourdbname, Repair_Allow_Data_Loss)

Go

Alter Database yourdbname Set Multi_User

Go

Alter Database yourdbname Set Online

Go

 

Note*: You will receive error messages but database will be online.

 

Share this:

Extent – the basic unit in which space is managed. An extent is eight physically contiguous pages, or 64 KB. This means SQL Server databases have 16 extents per megabyte.

Uniform extents – owned by a single object; all eight pages in the extent can only be used by the owning object.

Mixed extents – shared by up to eight objects. Each of the eight pages in the extent can be owned by a different object.

Allocation Unit – a set of particular types of pages.

Partition – is a unit of data organization.

Heap – a table without a clustered index.

IAM – Index Allocation Map-the page that keeps track of all the pages allocated to a heap. (Can be more than one)

B+ Trees – B-tree stands for “balanced tree,” and SQL Server uses a special kind called B+ trees (pronounced “b-plus trees”) that are not kept strictly balanced in all ways at all times. Unlike a normal tree, B-trees are always inverted, with their root (a single page) at the top and their leaf level at the bottom.

Root Node – The top node of the B+ tree is called the root node.

SGAM – tracks shared extents.

GAM- tracks an allocation event.

Share this:

Page – The fundamental unit of data storage in SQL Server

DBCC PAGE – allows you to examine the contents of data and index pages.

DBCC IND – lists of all database pages that make up the selected index or partition.

Data Page – stores data,except text, ntext, image, nvarchar(max), varchar(max), varbinary(max), and xml data, when text in row is set to ON.

Log Files – a series of log records.

Extents – a collection of eight physically contiguous pages and are used to efficiently manage the pages.

.mdf – Primary data file.

.ndf – Secondary data file.

Share this:

This query the sys.dm_os_waiting_tasks DMV to show all waiting tasks currently active or blocked,
revealing the wait type, duration, and resource

SELECT blocking.session_id AS blocking_session_id ,
blocked.session_id AS blocked_session_id ,
waitstats.wait_type AS blocking_resource ,
waitstats.wait_duration_ms ,
waitstats.resource_description ,
blocked_cache.text AS blocked_text ,
blocking_cache.text AS blocking_text
FROM sys.dm_exec_connections AS blocking
INNER JOIN sys.dm_exec_requests blocked ON blocking.session_id = blocked.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) blocked_cache
CROSS APPLY sys.dm_exec_sql_text(blocking.most_recent_sql_handle) blocking_cache
INNER JOIN sys.dm_os_waiting_tasks waitstats ON waitstats.session_id = blocked.session_id

Output:

Share this:

Follow the below steps to configure email account and send email using SQL Server

–Enabling Database Mail
sp_configure ‘show advanced options’,1
reconfigure
go
sp_configure ‘Database Mail XPs’,1
reconfigure

–Creating a Profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = ‘SQLProfile’,
@description = ‘Mail Service for SQL Server’ ;

— Create a Mail account for gmail. We have to use our company mail account.
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = ‘SQL_Email_Account’,
@email_address = ‘abc@gmail.com’,
@mailserver_name = ‘smtp.gmail.com’,
@port=587,
@enable_ssl=1,
@username=’abc’,
@password=’password’

— Adding the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = ‘SQLProfile’,
@account_name = ‘SQL_Email_Account’,
@sequence_number =1

— Granting access to the profile to the DatabaseMailUserRole of MSDB
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = ‘SQLProfile’,
@principal_id = 0,
@is_default = 1 ;

–Sending Test Mail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘SQLProfile’,
@recipients = ‘abc@gmail.com’,
@body = ‘Database Mail Testing…’,
@subject = ‘Databas Mail from SQL Server’;

–Verifying, check status column
select * from sysmail_allitems

 

Share this:

–Joining two tables without a clause i.e, cross products for two tables

USE Pubs
SELECT * from authors,titleauthor
GO
SELECT * from authors cross join titleauthor
GO

This query is not actually joined on any common clause. It is a cartesian product, so if there are 20 records in first table
and 30 records in the second we will get a total of 600 records.
Not putting a where clause is same as a CROSS JOIN.

–Using cross products to generate unique derived fields

USE Pubs
SELECT distinct (stor_id + '-' + au_lname) compfld
from stores cross join authors
order by compfld

‘+’ operators concatenates two fields for composite field generation Primary key of two different tables can be used to create
a composite key in a link table this way.

–Using Inner Joins to find exact matching rows

SELECT au_id,pubdate
from titleauthor inner join titles
ON titleauthor.title_id = titles.title_id

INNER JOIN exactly matches the common key on either sides of the Join

–Conventional Inner Joins

SELECT au_id,pubdate
from titleauthor, titles
WHERE titleauthor.title_id = titles.title_id

–Inner Joins having more than two tables

SELECT   au_id,UPPER(pub_name) PUBLISHER,pubdate [Published On]
from       titleauthor
INNER    JOIN titles
ON          titleauthor.title_id = titles.title_id
JOIN      publishers
ON          publishers.pub_id = titles.pub_id
ORDER   BY PUBLISHER

INNER JOIN is the default type of Join. Specifying it is not always needed

–Inner Joins having more than two tables refined with WHERE, AND & OR

SELECT au_id AUTHOR,UPPER(pub_name) PUBLISHER,pubdate [Sys Pub Date],
Convert(char(12),pubdate) [PUBLISHED ON]
from titleauthor
JOIN titles
ON titleauthor.title_id = titles.title_id
JOIN publishers
ON publishers.pub_id = titles.pub_id
WHERE pub_name like 'ALGO%'
AND au_id >= '3%'

Multiple Inner Joins further refined by a Where / And Clause Convert function gives the date in a more friendly format.
Try : Convert(char(20),pubdate) or Convert(char(6),pubdate)
‘>=3%’ will give all the results with au_id starting from 3??-??-???? and above in a lexical order

–Left Join having two tables

select a.title_id,a.title ,b.au_ord ,b.au_id
from titles a
left join titleauthor b
ON a.title_id = b.title_id
order by a.title,b.au_ord

Left Join brings all the rows from table appearing first in the JOIN clause

–Right Join having two tables

select a.title_id,a.title ,b.au_ord ,b.au_id
from titleauthor b 
right join titles a
ON a.title_id = b.title_id
order by a.title,b.au_ord

Right Join brings all the rows from table appearing second in the JOIN clause.

–LeftJoin in two tables having a filter

select                    stor_name,CAST(discount AS VARCHAR) + '%' '%'
from                       discounts
LEFT JOIN              stores
ON                          discounts.stor_id = stores.stor_id
WHERE                   discounts.stor_id IS NOT NULL

The results can be filtered using a trailing Where.
Left join brings all the records from discounts & nulls for unrelated stores records CAST does the same jobs as convert but lacks formatting options Casting needed as % is a character.
Here first ‘%’ becomes part of the data & the second becomes header of the column.

Share this:

–Finding the Grouped SUM based on several filtering criteria

SELECT   STOR_ID [Store Id],SUM(QTY) [Total Quantity]
FROM     sales
WHERE   title_id like 'PC%'
AND       Stor_id between 7000 and 8000
OR          ord_date between '1994-01-01' and '1995-01-01'
GROUP BY STOR_ID

SUM acts just like Count, but instead of number of rows, accumulates the content of any numeric field like QTY here.

–Finding the Average based on several filtering criteria

select avg(discount) avg_discount from discounts
go
SELECT   PAYTERMS,AVG(QTY) [Average Quantity]
FROM     sales
where ord_date < '1994-01-01'
GROUP BY PAYTERMS

AVG is also a grouping function and would average out the quantity against the grouping parameter..

–Grouping By an expression containing a column

select distinct(Left(upper(name),1)) TABLE_INITIALS, count(*) OCCURENCES
from SysObjects
where xtype = 'U'
group by Left(upper(name),1)

Assigning priorities in filtering a query with the help of proper bracketting (parenthesis)

SELECT * from Pubs..sales
WHERE title_id like 'PC%'
AND (
Stor_id between 7000 and 8000
OR ord_date between '1994-01-01' and '1995-01-01'
)
-----
SELECT * from Pubs..sales
WHERE (
title_id like 'PC%'
and Stor_id between 7000 and 8000
)
OR ord_date between '1994-01-01' and '1995-01-01'

In the first query the bracketted WHERE clause gives 2 results and the AND clause gives 19 results. (select * from Pubs..sales where Stor_id between 7000 and 8000 OR ord_date between ‘1994-01-01’ and ‘1995-01-01’ gives 19 results). There intersection gives only 1 result as common.

–In the second query the bracketted WHERE clause gives 1 result
–and the OR clause gives 8 results.Since this is a cumulative OR clause,
–we would get 9 rows in total.

Share this:

–Selecting columns based on a Where clause which is further constrained by an AND clause
SELECT * from Northwind..Customers
WHERE Phone like ‘(5)%’
AND RIGHT(Postalcode,2) = ’33’

–Selecting columns based on a Where clause which is further constrained by an AND clause
SELECT * from Northwind..Customers
WHERE Phone like ‘(5)%’
AND RIGHT(Postalcode,2) = ’33’
OR  RIGHT(Postalcode,2) = ’23’

–Selecting columns based on a Where clause which is further constrained by a BETWEEN clause
SELECT Au_LName, YTD_SALES
from Pubs..titleView
where ytd_sales between 2000 and 4000
order by 2

–Finding the Grouped Count based on several filtering criteria
Use Pubs
SELECT STOR_ID, COUNT(*) count
FROM sales
WHERE title_id like ‘PC%’
AND Stor_id between 7000 and 8000
OR ord_date between ‘1994-01-01’ and ‘1995-01-01′
GROUP BY STOR_ID

–GetDate( ) processing with integral days processing
select getdate(), getdate()-1, getdate() + 365

–GetDate( ) processing  with fractional days increment
select getdate() + 1.5
go
select getdate() + 1.45
go

–DateAdd( ) function processing
select hire_date,dateadd(yy,2,hire_date)
from pubs..employee

select hire_date,dateadd(yyyy,2,hire_date)
from pubs..employee

–DateAdd( ) function processing for year increments
select ’15-Aug-1930′ birth_day,dateadd(yy,60,’15-Aug-1930′) retirement_day

–DateAdd( ) function processing for various input date formats
select dateadd(yy,60,’1930-12-13′),
dateadd(yy,60,’1930.12-13′) ,
dateadd(yy,60,’Dec,13 1930′)

–DateAdd( ) function processing for days & weeks increments
select dateadd(dd,-365,hire_date)
from pubs..employee
go

select dateadd(ww,1000,hire_date)
from pubs..employee
go

–DateAdd( ) function processing for months, minutes and seconds increments
Select DATEADD(mm,-3,getdate( ))

Select DATEADD(ss,86400,getdate( )) as Tommorow_Now

Select RIGHT(DATEADD(mi,150,getdate( )),8) as TestEndTime

–Date Conversion formats- Specifying SQL Output formats
select convert(varchar,getdate(), 1)          –Output in mm/dd/yy

select convert(varchar,getdate(), 2)          — Output in yy.mm.dd

select convert(varchar,getdate(), 3)          — Output in dd/mm/yy

select convert(varchar,getdate(), 4)          — Output in dd.mm.yy

select convert(varchar,getdate(), 5)          — Output in dd-mm-yy

select convert(varchar,getdate(), 6)          — Output in dd Mon yy

select convert(varchar,getdate(), 7)          — Output in Mon dd, yy

select convert(varchar,getdate(), 8)          — Output in hh+:mi:ss

select convert(varchar,getdate(), 9)          — Output in Mon dd yyyy hh:mi:ss:mmmXM

select convert(varchar,getdate(), 10)       — Output in mm-dd-yy

select convert(varchar,getdate(), 11)       — Output in yy/mm/dd

select convert(varchar,getdate(), 12)       — Output in yymmdd

select convert(varchar,getdate(), 13)       — Output in Mon dd yyyy hh+:mi:ss:mmm

select convert(varchar,getdate(), 14)       — Output in hh+:mi:ss:mmm

–Date Conversion formats in extended Year notation – specifying SQL Output formats
select convert(varchar,getdate(), 101)     — Output in mm/dd/ yyyy

select convert(varchar,getdate(),102)      — Output in yyyy.mm.dd

select convert(varchar,getdate(),103)      — Output in dd/mm/yyyy

select convert(varchar,getdate(),104)      — Output in dd.mm.yyyy

select convert(varchar,getdate(),105)      — Output in dd-mm-yyyy

select convert(varchar,getdate(),106)      — Output in dd Mon yyyy

select convert(varchar,getdate(),107)      — Output in Mon dd, yyyy

select convert(varchar,getdate(),108)      — Output in hh+:mi:ss

select convert(varchar,getdate(),109)      — Output in Mon dd yyyy hh:mi:ss.mmmXM

select convert(varchar,getdate(),110)      — Output in mm-dd-yyyy

select convert(varchar,getdate(),111)      — Output in yyyy/mm/dd

select convert(varchar,getdate(),112)      — Output in yyyymmdd

select convert(varchar,getdate(),113)      — Output in Mon dd yyyy hh+:mi:ss:mmm

select convert(varchar,getdate(),114)      — Output in hh+:mi:ss:mmm

–Date Conversion formats – specifying Input formats for the varchar dates
select convert(datetime,’12/11/1978′,101)              –Input in mm/dd/ yyyy

select convert(datetime,’1978.12.11′,102)                –Input in yyyy.mm.dd

select convert(datetime,’11/12/1978′,103)              –Input in dd/mm/yyyy

select convert(datetime,’11.12.1978′,104)                –Input in dd.mm.yyyy

select convert(datetime,’11-12-1978′,105)               –Input in dd-mm-yyyy

select convert(datetime,’11 Dec 1978′,106)             –Input in dd-mm-yyyy

select convert(datetime,’Dec 11,1978′,107)             –Input in Mon dd, yyyy

select convert(datetime,’12-11-1978′,110)               –Input in mm-dd-yyyy

select convert(datetime,’1978/12/11′,111)              –Input in yyyy/mm/dd

select convert(datetime,’19781211′,112)  –Input in yyyymmdd

–Date Conversion formats- Input Date format approximation for centuries.
select convert(datetime,’11 Dec 78′,6)      –Input in dd Mon yy

select convert(datetime,’11 Dec 03′,6)      –Input in dd Mon yy

–Date Conversion formats- Input Date format , longest forms and for timestamps .

select convert(datetime,’Dec 11 1978 22:12:12.121′,113)    –Input in Mon dd yyyy hh+:mi:ss:mmm

select convert(datetime,’Dec 11 1978 10:12:12.121PM’,109) –Input in Mon dd yyyy hh:mi:ss.mmmXM

select convert(datetime,’22:12:12.121′,114)             –Input in hh+:mi:ss:mmm

select convert(datetime,’22:12:22′,108)     –Input in hh+:mi:ss

–Date Conversion formats- Input Date format , longest forms and for timestamps .
select * from pubs..employee
where convert(datetime,hire_date,6) > ’11 Dec 93′

select * from pubs..employee where hire_date > ’11 Dec 93′

–Date Conversion formats- Input Date format , longest forms and for timestamps .
select ‘VERIFIED’
where convert(datetime,’11 Dec 93′,6) = convert(datetime,’19931211′,112)

–Date Conversion formats- the ODBC format .
select convert(varchar,hire_date, 121) from pubs..employee where hire_date > ’11 Dec 93′

select substring(convert(varchar,getdate(), 120),12,24)

–Small DateTime format
select getdate(), convert(smalldatetime,getdate( ))

–Days elasped since ‘1.1.1900’
select getdate(),convert(decimal,getdate())

select convert(int,getdate())-convert(int,hire_date) DaysInJob from pubs..employee

–Milliseconds elasped since ‘1.1.1900’

select replace(convert(varchar,getdate(), 114),’:’,”) — stripped time from hh+:mi:ss:mmm

select convert(varchar,getdate(), 112)+replace(convert(varchar,getdate(), 114),’:’,”)

–Unique string generation based on time elasped in milliseconds(?) from absolute zero.
select convert(timestamp,getdate())
go

–Finding the difference in two dates
select datediff(dd,hire_date,getdate()) EmploymentINDays from pubs..employee

select datediff(mm,hire_date,getdate()) EmploymentINMons from pubs..employee

select datediff(yy,hire_date,getdate()) EmploymntINYers from pubs..employee

select datediff(ww,hire_date,getdate()) EmploymntINWeeks from pubs..employee

select datediff(mi,hire_date,getdate()) EmploymntINMinutes from pubs..employee

–Finding Anniversaries in this year
select dateadd(yy,datediff(yy,hire_date,getdate()),hire_date) JOBANNIVERSARY
from pubs..employee
GO
select convert(varchar(6),
dateadd(yy,datediff(yy,hire_date,getdate()),hire_date)) JOBANNIVERSARY
from pubs..employee
GO

–Finding Job Lengths or duration from a fixed past date
select *,datediff(yy,hire_date,getdate()) AS JobYears
from pubs..employee
where datediff(yy,hire_date,getdate()) > 14

–Finding the Year, Month & Date parts from a date expression
select DAY(hire_date) from employee
select MONTH(hire_date) from employee
select YEAR(hire_date) from employee

–Date constituent functions properties
SELECT MONTH(0), DAY(0), YEAR(0)

–DatePart functions
select DATEPART(dw,hire_date) from employee — Day of week when Joining

select DATEPART(dy,hire_date) from employee — Nth Day of Year on Joining

select DATEPART(qq,hire_date) from employee — Quarter of Year at Joining

select DATEPART(mm,hire_date) from employee — Month of Year at Joining

select DATEPART(dd,hire_date) from employee — Day of Month in the year of Joining

Share this:

–Finding the number of rows to be fetched
Select COUNT(*) AS TOTAL_ROWS from Authors

–Finding the number of rows to be fetched, quantified on an attribute
Select state,COUNT(*) AS TOTAL_ROWS
from Authors
group by state

–Finding the number of rows to be fetched, quantified on an attribute,
–substituting any null attribute by some alias or field
Select ISNULL(state,’XX’) STATE,COUNT(*) NOFAUTHORS
from Authors
group by state

–Finding the number of rows to be fetched, quantified by more than one attribute
Select ISNULL(state,’XX’) STATE,Zip,COUNT(*) NOFAUTHORS
from Authors group by state,Zip

–Finding the number of rows, quantified on an attribute satisfying a particular criteria
select state, count(*)
from Authors
group by state
having state is not null

–Finding the number of rows, quantified on an attribute satisfying a particular criteria,
–ordered by the grouping (or a subset of grouping) fields
Select ISNULL(state,’XX’) STATE,Zip,COUNT(*) NOFAUTHORS from Authors
group by state,Zip
order by state,Zip ASC
— order by Zip DESC

–Finding the rows which appear more than once based on a selection criteria
select state, count(*)
from Authors
group by state
having count(*) > 1

–Finding the distinctive counts of an attribute in a table
select COUNT(*) NOF_ROWS,COUNT(state) NOF_VALIDSTATES,
COUNT(distinct state) NOF_DISTINCTSTATES
from Authors

Select COUNT(distinct state) states,  COUNT(distinct zip) zips from Authors

–Finding the resultset based on one restrictive condition
select * from Pubs..Authors
where state = ‘CA’

–Finding the resultset based on few restrictive conditions
select * from Pubs..Authors
where state = ‘CA’
or state = ‘UT’

–Finding the resultset based on few restrictive conditions
select * from Pubs..Authors where state in  ( ‘CA’ ,  ‘UT’  )
order by state desc

–Finding the resultset based bearing some restrictive textual pattern
select * from Northwind..Customers
where CompanyName like ‘B%’

–Finding the resultset based bearing some restrictive textual pattern
select * from Northwind..Customers
where Phone like ‘(5)%’

select * from Northwind..Customers
where substring(PostalCode,1,3) = ‘050’

select * from Northwind..Customers
where Right(Left(Phone,4),3) = ‘208’