Script SQL Server DDL

Scripting SQL Server DDLRichard SutherlandIf you buy into the theory that all database objects should be contained in a source management system such as Visual SourceSafe, and that deployment of database projects should be done from the source management system, then the manner in which Microsoft's Visual Studio 2005 Team Edition for Database Professionals [a.k.a., […]

What does this return?

select 10 / 3

Server Side Paging using SQL Server 2005

A common activity in applications is to page results or record sets from a database. This is usually done on the client using the client's paging functionality or on the server through a variety of methods. In SQL Server 2000 those server side methods typically used dynamic SQL or nested TOP clauses and weren't very efficient. Using Common Table Expressions in SQL Server 2005 we have a better way to page record sets on the server.

In SQL Server 2005 T-SQL, what does the...

Check if SQL Server version is 2005 or lower

This script shows a simple way to check if the current version of SQL Server is 2005 or lower.It exploits the new server property called 'BuildClrVersion' which is not defined on SQL Server 2000 (and lower) but has just been introduced with 2005.It may be useful to condition the execution of a piece of code […]

Validating Canadian Postal Codes

For fellow Canadians and neighbours to the south, you may find useful a function to validate the formatting of a Canadian postal code.  More than once I've had to work with web programmers on checking that a postal code meets the A1A1A1 style, so carry around a function that simply returns a true/false bit if […]

What the heck does ATN2 do?

You have installed a US English SQL Server...

What does this return?

declare @i int
select @i = -5
select...

Check and Set Default file Locations with TSQL

Checks the default file locations for your data and log files. If not set, it sets them to the specified values. This is written for a SQL 2005 default instance. Registry paths may be different for SQL 2000 or named instances.The script can be easily modified to update it if the current value is set […]