INNER JOIN returns duplicate rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

INNER JOIN returns duplicate rows

Author

Topic

sql-lover
Yak Posting Veteran

99 Posts

Posted - 2015-03-16 : 16:45:50

I'm trying to build a DMV to get last time stats were refreshed plus the rowmodctr.

This one works fine:


SELECT	SCHEMA_NAME(o.Schema_ID) + N'.' + o.NAME AS [Object Name], 
		o.type_desc AS [Object Type],
		i.name AS [Index Name], 
		STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date], 
		s.auto_created, 
		s.no_recompute, 
		s.user_created, 
		st.row_count, 
		ssi.rowmodctr
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK) ON o.[object_id] = i.[object_id]
INNER JOIN sys.stats AS s WITH (NOLOCK)	ON i.[object_id] = s.[object_id] 
	AND i.index_id = s.stats_id
INNER JOIN sys.dm_db_partition_stats AS st WITH (NOLOCK) ON o.[object_id] = st.[object_id]
	AND i.[index_id] = st.[index_id]
WHERE o.[type] IN ('U', 'V')
	AND st.row_count > 0
ORDER BY STATS_DATE(i.[object_id], i.index_id) asc OPTION (RECOMPILE);

But this one, after adding a join on sys.sysindexes in order to get rowmodctr, does not


SELECT	SCHEMA_NAME(o.Schema_ID) + N'.' + o.NAME AS [Object Name], 
		o.type_desc AS [Object Type],
		i.name AS [Index Name], 
		STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date], 
		s.auto_created, 
		s.no_recompute, 
		s.user_created, 
		st.row_count, 
		ssi.rowmodctr
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK) ON o.[object_id] = i.[object_id]
INNER JOIN sys.sysindexes ssi ON i.object_id = ssi.id -- added this join so I can get row modifier
INNER JOIN sys.stats AS s WITH (NOLOCK)	ON i.[object_id] = s.[object_id] 
	AND i.index_id = s.stats_id
INNER JOIN sys.dm_db_partition_stats AS st WITH (NOLOCK) ON o.[object_id] = st.[object_id]
	AND i.[index_id] = st.[index_id]
WHERE o.[type] IN ('U', 'V')
	AND st.row_count > 0
ORDER BY STATS_DATE(i.[object_id], i.index_id) asc OPTION (RECOMPILE);

The 2nd TSQL query returns duplicates rows. Why?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-16 : 17:01:54

I cracked open what we have deployed to one of our servers:


	SELECT SCHEMA_NAME(soj.schema_id) AS SchemaName,
			si.id,
			soj.Name AS TableName,
			'' AS IndexName,
			MAX(tr.TotalRowsCount) AS NumRows,
			MAX(si.rowmodctr) AS RowsModified,
			CAST(MAX(si.rowmodctr) AS NUMERIC)/MAX(tr.TotalRowsCount) * 100 AS PercentMod
	FROM sys.objects soj 
		INNER JOIN sys.sysindexes si
			ON soj.object_id = si.id
			INNER JOIN 
		(SELECT si.id AS TableID, MAX(si.rowcnt) AS TotalRowsCount
			FROM sys.objects soj 
			INNER JOIN sys.sysindexes si
				ON soj.object_id = si.id
				WHERE soj.[type] = 'U'
				AND  si.indid <= 1
				AND OBJECTPROPERTY( si.id, 'IsMSShipped' ) = 0 
				AND OBJECTPROPERTY( si.id, 'IsUserTable' ) = 1
				GROUP BY si.id) AS tr
			ON si.id = tr.TableID
	WHERE soj.[type] = 'U'
		AND si.indid > 0 
		AND OBJECTPROPERTY( si.id, 'IsMSShipped' ) = 0 
		AND OBJECTPROPERTY( si.id, 'IsUserTable' ) = 1
		AND tr.TotalRowsCount <> 0

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

sql-lover
Yak Posting Veteran

99 Posts

Posted - 2015-03-17 : 10:50:38

Thanks Tara! I will try again at work.

Any reason why a subselect instead of a join?

** EDIT **

Spoke too soon, I'm getting this error:


Column 'sys.objects.schema_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-17 : 12:20:14

It's a derived table. You can replace with a CTE or rework it to use ROW_NUMBER(). It's there because of needing to get MAX.

Here's the actual code, I only gave you a snippet:


	SELECT SCHEMA_NAME(soj.schema_id) AS SchemaName,
			si.id AS TableID,
			soj.Name AS TableName,
			si.Name AS IndexName,
			tr.TotalRowsCount AS NumRows,
			si.rowmodctr AS RowsModified,
			CAST(si.rowmodctr AS NUMERIC)/tr.TotalRowsCount * 100 AS PercentMod
	FROM sys.objects soj 
		INNER JOIN sys.sysindexes si
			ON soj.object_id = si.id
			INNER JOIN 
		(SELECT si.id AS TableID, MAX(si.rowcnt) AS TotalRowsCount
			FROM sys.objects soj 
			INNER JOIN sys.sysindexes si
				ON soj.object_id = si.id
				WHERE soj.[type] = 'U'
				AND  si.indid <= 1
				AND OBJECTPROPERTY( si.id, 'IsMSShipped' ) = 0 
				AND OBJECTPROPERTY( si.id, 'IsUserTable' ) = 1
				GROUP BY si.id) AS tr
			ON si.id = tr.TableID
	WHERE soj.[type] = 'U'
		AND si.indid > 0
		AND OBJECTPROPERTY( si.id, 'IsMSShipped' ) = 0 
		AND OBJECTPROPERTY( si.id, 'IsUserTable' ) = 1
		AND tr.TotalRowsCount <> 0 -- exclude tables with 0 rows to avoid devide by zero error
		AND CAST(si.rowmodctr AS NUMERIC)/tr.TotalRowsCount * 100 >= @PercentChanged
		AND @Mode='INDIVIDUAL'
	UNION ALL
	SELECT SCHEMA_NAME(soj.schema_id) AS SchemaName,
			si.id,
			soj.Name AS TableName,
			'' AS IndexName,
			MAX(tr.TotalRowsCount) AS NumRows,
			MAX(si.rowmodctr) AS RowsModified,
			CAST(MAX(si.rowmodctr) AS NUMERIC)/MAX(tr.TotalRowsCount) * 100 AS PercentMod
	FROM sys.objects soj 
		INNER JOIN sys.sysindexes si
			ON soj.object_id = si.id
			INNER JOIN 
		(SELECT si.id AS TableID, MAX(si.rowcnt) AS TotalRowsCount
			FROM sys.objects soj 
			INNER JOIN sys.sysindexes si
				ON soj.object_id = si.id
				WHERE soj.[type] = 'U'
				AND  si.indid <= 1
				AND OBJECTPROPERTY( si.id, 'IsMSShipped' ) = 0 
				AND OBJECTPROPERTY( si.id, 'IsUserTable' ) = 1
				GROUP BY si.id) AS tr
			ON si.id = tr.TableID
	WHERE soj.[type] = 'U'
		AND si.indid > 0 
		AND OBJECTPROPERTY( si.id, 'IsMSShipped' ) = 0 
		AND OBJECTPROPERTY( si.id, 'IsUserTable' ) = 1
		AND tr.TotalRowsCount <> 0 -- exclude tables with 0 rows to avoid devide by zero error
		AND @Mode='ALL'
	GROUP BY SCHEMA_NAME(soj.schema_id),
		 si.id,
		 soj.Name
	HAVING
		CAST(MAX(si.rowmodctr) AS NUMERIC)/MAX(tr.TotalRowsCount) * 100 >= @PercentChanged
ORDER BY PercentMod DESC, NumRows DESC

I can't provide the rest, but this should give you an idea on how to implement it on your side.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources