Hi experts,
Select A.TableName
,B.Rows
From DBA.dbo.FragmentedIndexes A
LEFT OUTER JOIN DBA.dbo.RowCounts B
ON A.TableName = B.TableName
The problem is that any reference to the RowCounts table is underlined in red so it's not seeing that table. Therefore all Rows have Null in the Rows column. Yes both tables are in the DBA database.
It's something simple I'm sure. Thanks
If the statement ran, SQL is seeing the dbo.RowCounts table, otherwise you'd get a "Not found" error. As we don't have DDL for your tables, I can't really speculate as to why you're showing missing results. Maybe the dbo.RowCounts table is empty?!
At any rate, not sure why you'd need a RowCounts table unless you need historical row counts from some time in the past. sys.partitions.rows can provide a current row count without any extra overhead.
yes. I'm collecting row counts on a historical basis. I know this query, as written, may not return the latest row count but that's ok.
The RowCounts table is not empty. 90 percent of the rows should have a matching row in the other tables.This is the DDL
Can you show some sample data from the RowCounts table? Also - what is the collation for the database and specifically for each table?
There are some issues with your table design. Objects are defined with the data type sysname or nvarchar(128) and should be consistently set across all tables. Having different data types will force an implicit conversion - which can and will affect performance.
The RowCounts table does not have a defined relationship with the FragmentedIndexes table. You cannot relate solely on the TableName as you can have the same table in many databases across many different servers.
Since you are identifying indexes separately in FragmentedIndexes - you will get multiple rows from RowCounts for each table. This will require additional logic to remove the duplicates - making the queries harder to work with as well as take much longer to execute than necessary.
Guys, it's a data issue. One table has schema.Table as TableName and the other has only the actual table name. thus the join fails. I'll correct the data. Thanks
Don't you also need the db name in the join? The same table name could be in every db (theoretically).
And the server name - as you could have dev, test, qa, uat and prod systems with copies of the same database.