Category: SQL

SQL Server : Using $partition Function

Recently, I needed to validate date-range value on each partition on a partitioned table.  To do this, I utilized the SQL Server "$Partition" function.  Per MSDN, "$Partition returns the partition number into which a set of partitioning column values would be mapped for any specified partition function."

 

The syntax is:

 

[ database_name. ] $PARTITION.partition_function_name(expression)

 

This handy function can also be used to obtain column information, row counts, and other information you might want to know per partition:

 

–STEP ONE:

–Get the name of your partition function:

SELECT NAME FROM SYS.partition_functions

 

NAME

TableAPartitionFunc

 

–STEP TWO:

–Get the name of the column from which your table is partitioned

SELECT c.name AS PartitionedColumnName

FROM  sys.tables t

JOIN  sys.indexes  i

ON (i.object_id = t.object_id

AND i.index_id < 2)

JOIN  sys.index_columns  ic

ON(ic.partition_ordinal > 0

AND ic.index_id = i.index_id and ic.object_id = t.object_id)

JOIN  sys.columns c

ON (c.object_id = ic.object_id

AND c.column_id = ic.column_id)

WHERE t.object_id = LOWER(object_id(‘yourtablename’))

 

PartitionedColumnName

DatePort

 

–STEP THREE:

–Use acquired data to query your partition values:

 

SELECT DISTINCT $partition.TableAPartitionFunc(DatePort) AS PartitionNumber,

CONVERT(VARCHAR(8), DatePort, 1) AS RangedDate, ‘yourtablename’ AS PartitionedTable

from yourtablename

ORDER BY $partition. TableAPartitionFunc(DatePort)

 

PartitionNumber

RangedDate

PartitionedTable

1

5/13/2013

yourtablename

2

5/14/2013

yourtablename

3

5/15/2013

yourtablename

4

5/16/2013

yourtablename

 

SQL Server : Difference Between SP_SEND_DBMAIL & XP_SENDMAIL


Recently, I decided to send email notifications via SQL Server’s XP_SENDMAIL() if a stored procedure were unable to complete its processing.  For the same stored procedure, I was ALSO rolling back the transaction in the event of failure. However, I noticed that whenever the transactions failed, then I would not get the email.

After some research, I discovered that email messages sent using XP_SENDMAIL()  would be rolled back (not sent) if the transaction is rolled back.  However, I learned that if I were to use another SQL Server method, SP_SEND_DBMAIL(), then the email would be sent regardless of the end result of the transaction.

Therefore; in sum, if you want the email message to be sent regardless of the end result of the transaction you’ll need to use SP_SEND_DBMAIL().   Also, since XS_SENDMAIL() is apparently going to be deprecated, SP_SEND_DBMAIL may be the way to go for you. 

It appears that other important differences between the two email methods are:
1.      XP_SENDMAIL requires a MAPI client installed, such as Outlook, on the server. This is the only option for SQL Server 2000 and before.
2.      SP_SEND_DBMAIL is an SMTP solution, added for SQL Server 2005 and later.

Happy coding.

SQL Server : Query For A Quick Overview Of Your Partitions

— =============================================

— This is a basic query intended to provide

— a quick look at partition info.  

— =============================================

 

SELECT b.name as ‘table_name’, a.partition_number,

a.[rows] as ‘row_count’, a.partition_id,

a.object_id, a.hobt_id,

b.schema_id, c.name as ‘schema_name’, 

a.index_id, b.create_date, b.modify_date

FROM  sys.partitions AS a INNER JOIN

sys.objects AS b ON a.object_id = b.object_id

INNER JOIN sys.schemas AS c On c.schema_id = b.schema_id              

WHERE LOWER(b.name) = ‘yourPartitionedTableName’

–pull only first index for a simpler view

and a.index_id < 2

ORDER BY b.name, partition_number;

 

— =============================================

–Helpful info for a deeper dive

— =============================================

 

SELECT * FROM SYS.partition_range_values ;

SELECT * from sys.partitions;

SELECT * FROM SYS.partition_functions;

SELECT * FROM sys.partition_schemes;

SELECT * from sys.schemas;

SELECT * FROM sys.filegroups;

 

–query sys.partitions to view and verify that the table contains partitions as expected

SELECT * FROM sys.partitions where OBJECT_NAME(Object_Id)=‘yourPartitionedTableName’;

SELECT * FROM sys.objects where OBJECT_NAME(Object_Id)=‘yourPartitionedTableName’;