number of rows per table

Dear all,

any script to find out how many rows of data each table of the database has ?

I want to quickly found out if I need to create index for table which is so small ! I heard that if the table has <= 1000 rows, basically let the SQL optimizer do a full scan is faster than using the index, right?

is 1000 rows still right? or 2000 ? which is the maximum number of rows of a table should have that a full table scan is better than having index on it ?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
I wouldn't worry too much about how many rows are in the tables right now. I would create all indices upfront at the time of database design. First of all, the PRIMARY KEY constraints (and in most cases FOREIGN KEY constraints) are must-haves regardless of how many rows you have to preserve the data integrity. As for the other lookup indexes I would create the very obvious ones first (i.e. first name and surname lookups for the customer table, postcode/suburb lookups, date lookups in the transaction table) regardless of the number of records. I would then fine-tune the database based on the user experience. I think the modern databases are smart enough to prefer the table scans or indices based on their own internal metrics. You, as a database designer, should concentrate your job on the planning of the indices for possible lookup scenarios.
As for your question, you can retrieve the information suing this query:
SELECT o.name,
  ddps.row_count 
FROM sys.indexes AS i
  INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
  INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID
  AND i.index_id = ddps.index_id 
WHERE i.index_id < 2  AND o.is_ms_shipped = 0 ORDER BY o.NAME 

Open in new window

(the query is taken from the SQL Server Central - it works very fast)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MlandaTCommented:
I often use the attached script. Gives some nice stats on your tables: name, rows, reserved, data, index_size, unused, last_updated
ALL---Get-Table-Sizes.sql
marrowyungSenior Technical architecture (Data)Author Commented:
"I wouldn't worry too much about how many rows are in the tables right now. I would create all indices upfront at the time of database design. "

yeah, but at the time you create the index, the table already very small / no rows there and no matter indexed or not, it still fast ! this is not what my situation is!

"First of all, the PRIMARY KEY constraints (and in most cases FOREIGN KEY constraints) are must-haves regardless of how many rows you have to preserve the data integrity"

this is sure and this is part of business logic. our web developer will handle this but I am more concern on the post task/maintenance after 1-2 years the platform already here.

so basically right now the point is, when I join a new company I want to find out as much problem as I can which I CAN FIX THEM with solution.

"As for the other lookup indexes I would create the very obvious ones first (i.e. first name and surname lookups for the customer table, postcode/suburb lookups, date lookups in the transaction table) regardless of the number of records. I would then fine-tune the database based on the user experience."

exactly, I am now having the create missing index script and find unused index script on hand so that  I can have a quick check and see what is going on first and create the necessary index ! you seems working in the same way I hope and please follow me more on this forum ! where country you from? must not asia, right ?

"I think the modern databases are smart enough to prefer the table scans or indices based on their own internal metrics. "

yeah we think, I think about this too !

" You, as a database designer, should concentrate your job on the planning of the indices for possible lookup scenarios."

yeah, that's why I have missing index and unused index checking script handy

SELECT o.name,
  ddps.row_count 
FROM sys.indexes AS i
  INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
  INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID
  AND i.index_id = ddps.index_id 
WHERE i.index_id < 2  AND o.is_ms_shipped = 0 ORDER BY o.NAME 

Open in new window


this script return me nothing on my sql 2014 local instance which has no user database , that script don't check system database ?


MlandaT,

when I run you script on SQL server 2014, I got this :

Msg 15009, Level 16, State 1, Procedure sp_spaceused, Line 62
The object 'trace_xe_action_map' does not exist in database 'master' or is invalid for this operation.
Msg 15009, Level 16, State 1, Procedure sp_spaceused, Line 62
The object 'trace_xe_event_map' does not exist in database 'master' or is invalid for this operation.

Open in new window


what is the problem ? any other SP needs to be install before running this?
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

MlandaTCommented:
I'm afraid I have no idea what might be causing that error. I'm also using SQL 2014 and do not get the error (have never). I also cannot quite find refences online giving any guidance as to what might be causing that error. Permissions perhaps?
Sample output in a test database of mine.
marrowyungSenior Technical architecture (Data)Author Commented:
I tried on other SQL server 2014, still that error message:

Msg 15009, Level 16, State 1, Procedure sp_spaceused, Line 62
The object 'trace_xe_action_map' does not exist in database 'master' or is invalid for this operation.
Msg 15009, Level 16, State 1, Procedure sp_spaceused, Line 62
The object 'trace_xe_event_map' does not exist in database 'master' or is invalid for this operation.

Open in new window


wait, the trace xe option has to be turn on first ?
Scott PletcherSenior DBACommented:
"First of all, the PRIMARY KEY constraints (and in most cases FOREIGN KEY constraints) are must-haves regardless of how many rows you have to preserve the data integrity"

this is sure and this is part of business logic. our web developer will handle this

Terrible idea.  Developers should never design indexes, period.  A data analyst or DBA should do all db design, from logical thru the physical phases.
marrowyungSenior Technical architecture (Data)Author Commented:
"Terrible idea.  Developers should never design indexes, period.  A data analyst or DBA should do all db design, from logical thru the physical phases. "

how about DBA should involve in the design of the whole DB from logical to physical phrase ?

in all company I work with, developer handle all design and mostly index, so they will add wrong index and don't know what to do .

what is period in here actually ?
marrowyungSenior Technical architecture (Data)Author Commented:
ScottPletcher,

do you know why I get the error above ? what is missing ?
MlandaTCommented:
marrowyung, I dont know whether it's worth getting fixated on solving the error from the script I posted (unless of course you've seen the output - from the snapshot I sent and you really think it will be helpful in the end).
marrowyungSenior Technical architecture (Data)Author Commented:
yeah, I know what you mean, but I need it fix as I can't run it, let see what I can do on this. get back to you soon.
chaauCommented:
if you want to see the system table using my script remove the "AND o.is_ms_shipped = 0 " from it. However, I do not see a point to use for the system tables. Are you going to add some indices to the system tables?
marrowyungSenior Technical architecture (Data)Author Commented:
Chaau,

I find the view in your script can't show the respective DB name, what else can add to it??
chaauCommented:
Use SCHEMA_NAME(o.schema_id) to get the database name
marrowyungSenior Technical architecture (Data)Author Commented:
sure done.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.