Skip to main content

DDL Trigger – Prevent and Notify DDL Changes

SQL Server 2005 allows you to create DDL Triggers in a database, that fire whenever someone attempts to change the structure of your database. Ever accidentally created tables / views / sprocs in the master database? Well Greg Low published a trigger you can place in the master database to prevent changes there. Apparently Greg’s […]

Setting your default database to tempdb

Personally I like to set the default database for all logins to tempdb. It helps people remember to select the correct database before they start doing things. If someone forgets to change their database, i’d rather them attempting to do crazy things in tempdb instead of master. There are some programs / install scripts that […]

Go home early with WAITFOR

It’s late at night, you’re still at work, waiting for long running task to complete so you can go home. Well why not make SQL Server 2005 wait for you? Well gather your things and make use of the new WAITFOR statement, thats been introduced as part of Service Broker. You can ask SQL Server […]

COLLATE

COLLATE Keyword used to define string collation used for a database, column or server. Can also be used to cast the collation of a column or variable when used in a t-sql expression. SQL Server 2000 and SQL Server 2005 allow you to use Windows Collation Names or SQL Collation Names. To see a list […]

Collation Conflicts

If you’ve ever worked with databases from different sources, or with different collation settings, you’ve probably encountered an error like this: Msg 468, Level 16, State 9, Line 1 Cannot resolve collation conflict between ‘Latin1_General_CI_AS’ and ‘SQL_Latin1_General_CP1_CI_AS’ in equal to operation. The error is cause by having different collation settings for the columns in the […]

VLDB

VLDB Very Large Database. Typically considered a database with a total size ranging from hundreds of gigabytes (GB) to terabytes (TB), contining millions or billions or rows. Designed with scalability in mind, they use different performance optimisation techniques to smaller sized databases.

Moving Tempdb

So somebody setup sql server and you now want to move tempdb to another drive? Easy. Fire up Query Analyser and run the commands: ALTER DATABASE tempdb MODIFY FILE (name = ‘tempdev’, filename = ‘new-file-path‘) ALTER DATABASE tempdb MODIFY FILE (name = ‘templog’, filename = ‘new-file-path‘) Replace the new-file-path with the full path and filename […]