Essential viewing for web developers

Every now and again I come across some amazing videos on YouTube that I think are relevant to all web developers. One author (video blogger? – not sure what they are called) who is particularly brilliant is Tom Scott. He wonderfully articulates how web technologies and their exploits happen, and how to ensure that you don’t fall in to the same traps as many systems/sites out there.

One of Tom’s most brilliant videos is his explanation of how a self-re-tweeting tweet worked and how to ensure that you don’t have the same issues on your text boxes when writing their contents back out to the screen.

Essential viewing videos for development teams:

Tom Scott – Cracking Websites with Cross Site Scripting – Computerphile

Tom Scott – Hacking Websites with SQL Injection – Computerphile

Dr Mike Pound – Advanced SQL Injection

Tom Scott – Cross Site Request Forgery – Computerphile

Tom Scott – How Not To Store Passwords

Tom Scott – Hashing Algorithms and Security

 

If you are a developer, and have any suggestions of videos that should be added to this list, please add a comment with the URL!

SQL Server check table fragmentation level

If you are using SQL Server, and discover that what should be a simple select query, or particularly multiple select queries that are unioned together with a sort are running slowly, then the issue is your table indexes. This is particularly the case when using Date and DateTime fields for sorting.

If you are trying to sort a table or multiple tables by Date, then it is a good idea to add an index on the Date column, it makes things significantly faster.

Here is the SQL Query to check all tables in your database and the levels of fragmentation of each:

SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc