This question popped in the microsoft.public.sqlserver.programming forum yesterday. A person wanted to change a column from varchar 500 to varchar 2000. This is actually pretty easy to do in SQL Server, you can use the alter table alter column command

Let's take a quick look at how this works

First create this table

Every now and then someone will ask how to return a list of all the identity values in a table that have been skipped. You will probably have a table with an identity column, the 'problem' with identity columns is that if an insert is rolled back or fails in any way then the identity value is not reused...you end up with gaps. Identifying gaps is pretty easy to do if you have a table of numbers in your database.

If you don't have a table of numbers, here is some code that will create a table with numbers between 1` and 2048

Best Practice: coding SQL Server triggers for multi-row operations

There are many forum posts where people code triggers but these triggers are coded incorrectly because they don't account for multi-row operations. A trigger fires per batch not per row, if you are lucky you will get an error...if you are not lucky you will not get an error but it might take a while before you notice that you are missing a whole bunch of data

Let's take a look, first create these two tables

The CSS SQL Server Engineers have posted an interesting post, I myself don't have any of the fake identity columns but I did many times suggest in newsgroups to use UPDLOCK and HOLDLOCK in a transaction to guarantee that 2 inserts would not generate the same key value. This stuff below is from the CSS SQL Server Engineers post here: http://blogs.msdn.com/psssql/archive/2009/10/26/reduce-locking-and-other-needs-when-updating-data-better-performance.aspx

I saw this question in in Google Analytics from a Google search that hit our site.

So, how can you determice if an index is clustered or not? There are two ways, you can use either the INDEXPROPERTY function or the sysindexes/sys.sysindexes system table/view

To see how it works we will create a table with one clustered and one non clustered index on it

Here is the code for that