Defragmentation script for SQL Server

When you SQL server database becomes slower over time without apparent reasons, probably you’re facing a fragmentation problem. Database table fragmentation is one of the major performance killers. With the following maintenance script you defrag you tables and indexes. -- ============================================= -- Copyright by Author Sajal Dam, ISBN 1590594215 -- Description: Performs defragmentation actions -- tables and indexes in your database. -- Execute this script once a month or more often -- if necessary -- =======================================... [More]

How to change database primary key field with a foreign key constraint

Today I needed to increase a varchar database from 2 position to 50 positions. At first I thought this would be easy, just make a change script like this: 1: ALTER TABLE tb_Categories ALTER COLUMN Code varchar(50) not null After running in SSMS I got two error messages back: Msg 5074, Level 16, State 1, Line 1 The object 'FK_CategoryCode' is dependent on column 'Code'. Msg 4922, Level 16, State 9, Line 1 ALTER TABLE ALTER COLUMN Code failed because one or more objects access this column. Oops.. The Code field is used as a primary key here and ref... [More]

Easy way to clear your SQL server database

Sometimes you want to make a clean start with your SQL server database since it’s filled up with old demo data. We all now that you can delete or truncate your tables easily by executing some T-SQL statements. When you only have a couple of tables and no complex foreign key dependencies you can do it by hand. When you database is larger and for more complex it can be a time consuming task and it’s different for each database schema. The following script makes it really easy (beware: select the correct database!). 1: -----------------------------------------------------------... [More]