This is another check you should perform once databases start to get large, as having fragmented indexes have a huge performance hit.
For more on what fragmented indexes are, read this fantastic article.
After significant trial and error we have come up with an SQL script that lists all indexes for every table and shows the percentage fragmentation for the selected database on your server in one go!
db_name(a.database_id) As DatabaseName,
sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS a
INNER JOIN sys.indexes AS b ON a.OBJECT_ID = b.OBJECT_ID AND a.index_id = b.index_id
a.database_id = DB_ID()
Its easily done, in the rush to create tables someone forgets to tell the auto-number field that it is also a primary key.
Its not an issue until the database gets big, at which point (as listed on Microsoft TechNet) the performance can suffer by anything between 13% and 460% – which is potentially crippling for your database.
Thankfully there is a simple SQL script you can run to double-check that there are no tables without a primary key.
SELECT SCHEMA_NAME(schema_id) AS SchemaName,name AS TableName
WHERE OBJECTPROPERTY(OBJECT_ID,’TableHasPrimaryKey’) = 0
ORDER BY SchemaName, TableName;
Its something that took a bit of finding, but is very simple to do. I wanted to automatically add a row number to returned rows from a table in a database
SELECT FirstNames, LastName, DOB FROM Staff ORDER BY FirstNames ASC
we can modify this so that it returns a row number by changing the SQL as follows:
SELECT ROW_NUMBER() OVER (ORDER BY FirstNames) As RowNumber, FirstNames, LastName, DOB FROM Staff ORDER BY FirstNames ASC
NB. The ROW_NUMBER() Requires that the over keyword be included after it, and that the order you choose should be the same as the order by on the query.
If you know how to do this in a different way, or a way that will work for a query that does not have an order by (a very rare occurence) then please do post a comment to let others know.