Advertisements

Archive

Archive for the ‘SQL Server 2005’ Category

Using CROSS APPLY & OUTER APPLY operators with UDFs, Derived tables & XML data

January 3, 2012 4 comments

In my previous posts CROSS APPLY vs OUTER APPLY I discussed on the same topic, but didn’t covered much on examples. Here we will see more examples on using APPLY operators, their workarounds and performance.

>> Using APPLY operator with User Define Functions:

USE [AdventureWorks]
GO

-- Create an "Inline Table-valued Function" that returns Sales Details of a Contact (Customer):
CREATE FUNCTION [dbo].[ufnGetSalesInfo](@ContactID int)
	RETURNS TABLE 
AS 
RETURN (
	SELECT SalesOrderID, OrderDate, PurchaseOrderNumber, AccountNumber, CustomerID, TotalDue
	FROM Sales.SalesOrderHeader
	WHERE ContactID = @ContactID
)
GO

-- We can use this function with SELECT FROM statement like we do for a table:
SELECT * FROM [dbo].[ufnGetSalesInfo](100)

Here, we cannot provide multiple IDs or any variable to make it work for multiple or dynamic Contacts. To return Sales details for multiple Customers we need to JOIN it with Contacts table, but JOINS are now allowed with UFDs, they only work with Tables/Views.

So to JOIN a UDF with a TABLE we need an APPLY operator, check examples below:

-- CROSS APPLY acts as INNER JOIN & will return only matching rows, returns 513 rows:
SELECT 
	con.ContactID, con.FirstName, con.LastName, con.EmailAddress, con.Phone,
	x.*
FROM Person.Contact con
CROSS APPLY [dbo].[ufnGetSalesInfo](con.ContactID) x
WHERE con.ContactID <= 100

-- OUTER APPLY acts as OUTER LEFT JOIN & will return all matching & non-matching rows, returns 529 rows:
SELECT 
	con.ContactID, con.FirstName, con.LastName, con.EmailAddress, con.Phone,
	x.*
FROM Person.Contact con
OUTER APPLY [dbo].[ufnGetSalesInfo](con.ContactID) x
WHERE con.ContactID <= 100


-- Final Cleanup
DROP FUNCTION [dbo].[ufnGetSalesInfo]
GO

>> Using APPLY operator with Derived tables:

[Scenario]: Pull upto 5 Sales Orders for each Contact (Customer):

This is not possible with JOINS, because the condition to SELECT max 5 Orders is applied after the JOIN is made, we need to add the condition before JOIN. Using APPLY operator we can do this, let’s see how:

-- With Derived Table:
SELECT 
	con.ContactID, con.FirstName, con.LastName, con.EmailAddress, con.Phone,
	x.*
FROM Person.Contact con
CROSS APPLY (SELECT TOP 5 
				SalesOrderID, OrderDate, PurchaseOrderNumber, AccountNumber, CustomerID, TotalDue 
			 FROM Sales.SalesOrderHeader soh
			 WHERE soh.ContactID = con.ContactID) x
WHERE con.ContactID <= 100

-- We can also write the above query by using ROW_NUMBER() function & JOINs instead of the APPLY operator:
;WITH CTE AS (
	SELECT 
		SalesOrderID, OrderDate, PurchaseOrderNumber, AccountNumber, ContactID, TotalDue,
		ROW_NUMBER() OVER(PARTITION BY ContactID ORDER BY ContactID, SalesOrderID) AS ROWNUM
		FROM Sales.SalesOrderHeader)
SELECT 
	con.ContactID, con.FirstName, con.LastName, con.EmailAddress, con.Phone,
	CTE.*
FROM Person.Contact con
JOIN CTE cte
ON cte.ContactID = con.ContactID
WHERE con.ContactID <= 100
AND cte.ROWNUM <= 5
GO

Both of these queries do the same task. So to check which one is more optimized we can check the cost of each query in the batch. So the cost of query using APPLY operator is 84% and query using ROW_NUMBER & JOIN is 16%. Hence the second approach is much more performant & should be considered.

>> Using APPLY operator with XML data:

APPLY operators can also we used with XML data when there is a need to query parent/child nodes, check my previous blog post on this topic.

Advertisements

UPDATE statement with new .WRITE Clause – SQL Server

October 4, 2011 11 comments

As per MS BOL the new .WRITE clause of the UPDATE DML statement enables partial or full updates and high performance appends to varchar(max), nvarchar(max) and varbinary(max) data type columns.

The UPDATE statement is logged; however, partial updates to large value data types using the .WRITE clause are minimally logged.

Syntax: .WRITE ( expression, @Offset , @Length )

Usage: The string specified in the expression param is replaced by the number of characters specified in
@Length param starting from the position mentioned in @Offset param.

Let’s check this with an example mentioned below: The “Senior” character set is replaced by the 3 length character “Sr.” starting from the 18th character.

-- Create a table containing a VARCHAR(MAX), NVARCHAR(MAX) or VARBINARY(MAX) column:
CREATE TABLE CheckWrite (empID INT, eName VARCHAR(50), descr VARCHAR(MAX))

-- Insert test data in the table:
INSERT INTO CheckWrite
SELECT 101, 'Manoj Pandey', 'Manoj Pandey is a Sr. SQL Server developer and CRM analyst. He likes Photography & travelling.'

-- Check inserted record before UPDATE:
SELECT * FROM CheckWrite

-- Now UPDATE the descr column by using .WRITE clause:
UPDATE CheckWrite
SET descr .WRITE('Senior', 18, 3)
WHERE empID = 101

-- Check the updated result:
SELECT * FROM CheckWrite

-- Final cleanup
DROP TABLE CheckWrite

Output:

Note: The .WRITE clause cannot be used to update a NULL column or set the value of column_name to NULL.
 

>> Check & Subscribe my [YouTube videos] on SQL Server.
 


SQL DBA – Database Mail Setup – SQL Server 2005

September 29, 2010 14 comments

Database Mail, a new addition to the SQL Server 2005 database engine, is an enterprise solution for sending e-mail messages from the Microsoft SQL Server 2005 Database Engine. Using Database Mail, your database applications can send e-mail messages to users. The messages can contain query results, and can also include files from any resource on your network. Database Mail is designed for reliability, scalability, security, and supportability.

Database Mail provides a robust, high-performance replacement for the most commonly requested features of SQL Mail. Database Mail is designed to operate with SMTP servers, and is tested with Microsoft SMTP servers.
 

Note: Database Mail is not active by default. To use Database Mail, you must explicitly enable Database Mail by using either the “SQL Server Surface Area Configuration” tool or the “Database Mail Configuration Wizard“.
 

Database Mail Setup:

Enabling the Database Mail feature: By default the Database mail Stored-Procs are disabled.

For Express Edition, check the required files exists in MSSQL\Binn folder. If not then copy the following 3 files:
1. DatabaseMail90.exe
2. DatabaseMailEngine.dll
3. DatabaseMailProtocols.dll

Then execute the following Stored-Porcs:

exec dbo.sysmail_start_sp
exec dbo.sysmail_stop_sp

 

A User must be a member of the DatabaseMailUserRole database role in the msdb database.
 

To Enable Database Mail execute the following block of code:

use master
go
exec sp_configure 'show advanced options', 1
reconfigure
exec sp_configure 'Database Mail XPs', 1
reconfigure

 

Initial Cleanup:

IF EXISTS(
SELECT * FROM msdb.dbo.sysmail_profileaccount pa
      JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
      JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
WHERE
      p.name = @ProfileName AND
      a.name = @AccountName)
BEGIN
      PRINT 'Deleting Profile Account'
      EXECUTE sysmail_delete_profileaccount_sp
      @profile_name = @ProfileName,
      @account_name = @AccountName
END

IF EXISTS(
SELECT * FROM msdb.dbo.sysmail_profile p
WHERE p.name = @ProfileName)
BEGIN
      PRINT 'Deleting Profile.'
      EXECUTE sysmail_delete_profile_sp
      @profile_name = @ProfileName
END

IF EXISTS(
SELECT * FROM msdb.dbo.sysmail_account a
WHERE a.name = @AccountName)
BEGIN
      PRINT 'Deleting Account.'
      EXECUTE sysmail_delete_account_sp
      @account_name = @AccountName
END

 

Setting up Accounts & Profiles for DB Mail:
Create a Database Mail account for holding information about an SMTP account.
An Account holds information for only one e-mail server, such as the account name, e-mail address, reply-to e-mail address, server name or IP address, and some optional security settings.

--// Create a Database Mail account
EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = 'Test Mail Account',
    @description = 'Mail account for administrative e-mail.',
    @email_address = 'abc@xyz.com',
    @replyto_address = 'abc@xyz.com',
    @display_name = 'Manoj Pandey',
    @mailserver_name = 'smtp.xxxx.net',
	@port = 587,
	@username = 'xyz',
	@password = 'xxyyzz',
	@enable_ssl = 1

-- Create a Database Mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
    @profile_name = 'Test Mail Profile',
    @description = 'Profile used for administrative mail.'

-- Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'Test Mail Profile',
    @account_name = 'Test Mail Account',
    @sequence_number =1

-- Grant access to the profile to the DBMailUsers role
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = 'Test Mail Profile',
    @principal_name = 'public',
    @is_default = 1

 

Sending Mail and Check its status:

--Send mail
EXEC msdb.dbo.sp_send_dbmail
	@recipients=N'manub22@gmail.com',
	@body= 'Test Email Body',
	@subject = 'Test Email Subject',
	@profile_name = 'Test Mail Profile'
--Send mail with attachment
EXEC msdb.dbo.sp_send_dbmail
     @profile_name = 'DBMail'
    ,@recipients = 'GroupSQLDBA@MyCo.com'
    ,@from_address = 'DBMail@MyCo.com'
    ,@query = 'SELECT resource_type, resource_database_id,
                    request_mode, request_session_id
               FROM sys.dm_tran_locks
              WHERE request_mode IN (''IX'', ''X'')'
    ,@subject = 'Exclusive Locks'
    ,@attach_query_result_as_file = 1 ;

 

Important Tables used in configuring Database mail and check their status:

--Profiles
SELECT * FROM msdb.dbo.sysmail_profile

--Accounts
SELECT * FROM msdb.dbo.sysmail_account

--Profile Accounts
select * from msdb.dbo.sysmail_profileaccount

--Principal Profile
select * from msdb.dbo.sysmail_principalprofile

--Mail Server
SELECT * FROM msdb.dbo.sysmail_server
SELECT * FROM msdb.dbo.sysmail_servertype
SELECT * FROM msdb.dbo.sysmail_configuration

--Email Sent Status
SELECT * FROM msdb.dbo.sysmail_allitems
SELECT * FROM msdb.dbo.sysmail_sentitems
SELECT * FROM msdb.dbo.sysmail_unsentitems
SELECT * FROM msdb.dbo.sysmail_faileditems

--Email Status
SELECT SUBSTRING(fail.subject,1,25) AS 'Subject',
       fail.mailitem_id,
       LOG.description
FROM msdb.dbo.sysmail_event_log LOG
join msdb.dbo.sysmail_faileditems fail
ON fail.mailitem_id = LOG.mailitem_id
WHERE event_type = 'error'

--Mail Queues
EXEC msdb.dbo.sysmail_help_queue_sp

--DB Mail Status
EXEC msdb.dbo.sysmail_help_status_sp

 

Note: After performing above steps the mails were not getting sent. I was configuring DB mail from my client machine, even with the admin account. I was getting various errors like:

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 19 (2010-10-22T07:24:21). Exception Message: Could not connect to mail server. (A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond IP_ADD:25).

Then I tried to setup the DB mail from the server itself and it worked in one go.
 


Using OVER and PARTION BY clauses with Aggregate Functions in SQL Server

August 10, 2010 5 comments

In my [previous post] we learned about MS SQL Server 2005 Ranking functions.

Here we’ll see more scenarios where they can be used. We can use them with CTE i.e. Common Table Expressions, again a new gem with ver. 2005.

with myCTE as (
    select row_number() over(partition by class order by marks desc) as [RowNumber],
    class, marks, stuName from #tempTable)
select * from myCTE 
where RowNumber < 5
This gives following result:
row_num class marks name
1       A     90    pooja
2       A     90    saurabh
3       A     80    kanchan
4       A     80    manoj
1       B     90    paras
2       B     80    dinesh
3       B     70    hema
4       B     70    kapil

A CTE (Common Table Expression) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

More on CTE: http://msdn.microsoft.com/en-us/library/ms190766.aspx

The above mentioned PARTITION BY clause can also be used with SQL aggregate functions, like: AVG, COUNT, MIN, MAX, etc.

Here is an example:

select class, stuName, marks,
    AVG(marks) over(partition by class) AS [avg],
    SUM(marks) over(partition by class) AS [sum],
    MIN(marks) over(partition by class) AS [max],
    MAX(marks) over(partition by class) AS [min],
    COUNT(marks) over(partition by class) AS [count],
    STDEV(marks) over(partition by class) AS [ST_Dev],
    VAR(marks) over(partition by class) AS [VAR]
from #tempTable
This gives following result:
class name     marks AVG  SUM  MIN  MAX  COUNT STDEV   VAR
A     anita    50    76   460  50   90   6     15.0554 226.6667
A     harish   70    76   460  50   90   6     15.0554 226.6667
A     kanchan  80    76   460  50   90   6     15.0554 226.6667
A     manoj    80    76   460  50   90   6     15.0554 226.6667
A     pooja    90    76   460  50   90   6     15.0554 226.6667
A     saurabh  90    76   460  50   90   6     15.0554 226.6667
B     kamar    50    67   470  50   90   7     14.9603 223.8095
B     lalit    50    67   470  50   90   7     14.9603 223.8095
B     nitin    60    67   470  50   90   7     14.9603 223.8095
B     hema     70    67   470  50   90   7     14.9603 223.8095
B     kapil    70    67   470  50   90   7     14.9603 223.8095
B     dinesh   80    67   470  50   90   7     14.9603 223.8095
B     paras    90    67   470  50   90   7     14.9603 223.8095

I’ve rounded the last 2 column values to 4 decimal places.


Ranking Functions | ROW_NUMBER, RANK, DENSE_RANK, NTILE

August 9, 2010 9 comments

SQL Server 2005 provides functionality for using Ranking Functions with your result set. One can select a number of Ranking algorithms which are applied to a column of your table that you want to classify in a scope of your executing query. This feature is Dynamic and upon change of data (addition or removal of rows) it gives desired results the next time query is run.
 

–> Its 4 gems are:

1. ROW_NUMBER: Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
Syntax:

ROW_NUMBER() OVER ( [ < partition_by_clause > ] < order_by_clause > )

2. RANK: Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.
Syntax:

RANK() OVER ( [ < partition_by_clause > ] < order_by_clause > )

3. DENSE_RANK: Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.
Syntax:

DENSE_RANK() OVER( [ ] < order_by_clause > )

4. NTILE: Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.
Syntax:

NTILE(integer_expression) OVER( [ ] < order_by_clause > )

 

–> Now lets take an example, simple one of a class of students, their marks & class:

select 'A' [class], 80 [marks], 'manoj' stuName
into #tempTable
UNION
select 'A', 70 [marks],'harish' stuName
UNION
select 'A', 80 [marks],'kanchan' stuName
UNION
select 'A', 90 [marks],'pooja' stuName
UNION
select 'A', 90 [marks],'saurabh' stuName
UNION
select 'A', 50 [marks],'anita' stuName
UNION
select 'B', 60 [marks],'nitin' stuName
UNION
select 'B', 50 [marks],'kamar' stuName
UNION
select 'B', 80 [marks],'dinesh' stuName
UNION
select 'B', 90 [marks],'paras' stuName
UNION
select 'B', 50 [marks],'lalit' stuName
UNION
select 'B', 70 [marks],'hema' stuName

select * from #tempTable
Now on selection this gives you:
class 	marks   name
A 	50 	anita
A 	70 	harish
A 	80 	kanchan
A 	80 	manoj
A 	90 	pooja
A 	90 	saurabh
B 	50 	kamar
B 	50 	lalit
B 	60 	nitin
B 	70 	hema
B 	80 	dinesh
B 	90 	paras

–> The following query shows you how each function works:

select marks, stuName,
    ROW_NUMBER() over(order by marks desc) as [RowNum],
    RANK() over(order by marks desc) as [Rank],
    DENSE_RANK() over(order by marks desc) as [DenseRank],
    NTILE(3) over(order by marks desc) as [nTile]
from #tempTable
Result:
marks stuName  RowNum  Rank DenseRank  nTile
90    pooja    1       1    1          1
90    saurabh  2       1    1          1
90    paras    3       1    1          1
80    dinesh   4       4    2          1
80    kanchan  5       4    2          2
80    manoj    6       4    2          2
70    harish   7       7    3          2
70    hema     8       7    3          2
60    nitin    9       9    4          3
50    anita    10     10    5          3
50    kamar    11     10    5          3
50    lalit    12     10    5          3

–> Here:
– RowNum column lists unique ID’s of students, like Roll Numbers.
– Rank lists student rank with equal ranks those secured equal marks, thus there is no 2nd or 3rd.
– DenseRank lists student ranks with no gaps, so here 3 students came 1st &2nd and only 2 3rd.
– nTile listed students in different but equal groups, can be thought of as different sections.
 

–> Now, lets use the PARTITION BY option, its same as group by clause. Lets group/partition the students group by their classes A&B:

select class, marks, stuName,
    ROW_NUMBER() over(partition by class order by marks desc) as [RowNum],
    RANK() over(partition by class order by marks desc) as [Rank],
    DENSE_RANK() over(partition by class order by marks desc) as [DenseRank],
    NTILE(3) over(partition by class order by marks desc) as [nTile]
from #tempTable
Result:
class marks stuName  RowNum  Rank DenseRank  nTile
A     90    pooja    1       1    1          1
A     90    saurabh  2       1    1          1
A     80    kanchan  3       3    2          2
A     80    manoj    4       3    2          2
A     70    harish   5       5    3          3
A     50    anita    6       6    4          3
B     90    paras    1       1    1          1
B     80    dinesh   2       2    2          1
B     70    hema     3       3    3          2
B     60    nitin    4       4    4          2
B     50    kamar    5       5    5          3
B     50    lalit    6       5    5          3

You can clearly see that our query has grouped students in 2 Partitions (classes) and then Ranked them.
 

In my [next post] check how to use OVER Clause & Partition By option with Aggregate functions like, SUM, AVG, MIN, MAX, etc.
 

–> Check the same demo on YouTube: