-- =============================================
-- Query to determine if a partition's size may
-- be reaching a critical maximum
-- Note: Currently uses SP_SEND_DBMAIL instead of XP_SENDMAIL
-- =============================================
DECLARE @DbName VARCHAR(25)
DECLARE @sql VARCHAR(100)
DECLARE @ThresholdCountMsg INT
DECLARE @Subject VARCHAR(100)
SET @DbName = DB_NAME(); --get name of current database to use in email alert
SET @Subject = 'Partitioning Threshold Alert On ' + @DbName + '!'
SET @ThresholdCountMsg = 14000000; -- set partition row count
IF OBJECT_ID('tempdb..##yourPartitionSizeCheck') IS NOT NULL
BEGIN
DROP TABLE ##yourPartitionSizeCheck
END;
--use global temp table for email alert
CREATE TABLE ##yourPartitionSizeCheck
(
table_name varchar(20),
partition_number int,
row_count int
);
INSERT INTO ##yourPartitionSizeCheck
SELECT b.name AS 'table_name',
partition_number,
a.[rows] AS 'row_count'
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'
and a.index_id < 2
and a.[rows] > @ThresholdCountMsg;
IF @@ROWCOUNT > 0
EXEC MSDB.DBO.SP_SEND_DBMAIL
@profile_name = 'YourSqlmail',
@recipients = 'your.email@yourcompany.com',
@subject = @Subject,
@body = 'The following partitions may be approaching performance degradation: ',
@execute_query_database = @DbName,
@query = 'SELECT table_name, partition_number, row_count FROM ##yourPartitionSizeCheck';Tag: SQL
Error: Cannot resolve the collation conflict
This error started occurring after one of my SQL Server 2008 databases had been restored:
Msg 468, Level 16, State 9 …
Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “SQL_Latin1_General_CP1_CS_AS” in the equal to operation.
Supposedly, this type of error can occur when the tempdb collation does not match the current database collation — especially if using temp tables.
The easiest way to deal with this is to cast one of the field’s collation so it matches the other, or you can cast both if you need. I’m not sure if this is best practice in the long run; however, if you’re just doing some ad-hoc data mining then this is definitely a quick fix:
INNER JOIN [TABLE1].FIELD1 AS EA
ON EA.SERIAL_NBR COLLATE database_default = E.SERIAL_NBR COLLATE database_default
SQL: UNION vs. UNION ALL
I try to avoid UNION clauses in most SQL that I write. Historically, they have taken too much of a toll on performance. Now we have a speedier alternative to the UNION clause — UNION ALL.
In a nutshell, a UNION statement effectively does a SELECT DISTINCT on the results set. If you know that all the records returned are unique from your union, use UNION ALL instead, it gives faster results.
This difference is that UNION ALL will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.
UNION Definition:
The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.
UNION ALL Definition:
The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.
A great reference can be found in Pinal Dave’s blog.