number of rows per table

"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 

Select allOpen 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.

Select allOpen in new window

what is the problem ? any other SP needs to be install before running this?

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?

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.

Select allOpen in new window

wait, the trace xe option has to be turn on first ?

"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 ?

ScottPletcher,

do you know why I get the error above ? what is missing ?

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).

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.

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?

Chaau,

I find the view in your script can't show the respective DB name, what else can add to it??

Use SCHEMA_NAME(o.schema_id) to get the database name

sure done.