Tables that do not have Clustered Index

I'd like to share with you this script that returns name of tables that do not have Clustered Index defined on.You may ask, why do we need to create a Clustered Index on the table at all? Well,I think it is a subject for another blog , my experience is that every table should have clustered index.

--SQL Server 2005 (SP2)

SELECT name AS object_name FROM sys.objects WHERE name NOT IN
(
SELECT o.name AS object_name
FROM sys.indexes i
JOIN sys.objects o
ON i.object_id = o.object_id
WHERE objectproperty(o.object_id,'IsMSShipped') =0
AND I.type=1 ---Clustered Index
) AND objectproperty(sys.objects.object_id,'IsMSShipped') =0 ---Only user objects
AND objectproperty(sys.objects.object_id,'IsTable') =1 ---Only tables
ORDER BY name

Columns_Updated() within a trigger

Happy New Year to everyone. Once someone asked me about what columns are affected by the UPDATE statement. As you probaly know that in SQL Server 2005 we can do that in more elegant way, but for SQL Server 2000 I decided perfrom some testing.

CREATE TABLE Test (c1 INT NOT NULL PRIMARY KEY, c2 CHAR(1),c3 INT)
GO
--Insert some data
INSERT INTO Test VALUES (1,'A',100)
INSERT INTO Test VALUES (2,'B',500)

--Create a trigger
CREATE TRIGGER tr_my_trigger ON Test FOR UPDATE
AS
DECLARE @ColumnID INT
DECLARE @Columns VARCHAR(8000)
SET @Columns = SPACE(0)
SET @ColumnID = 1
WHILE @ColumnID <= (SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Test')
BEGIN
IF (SUBSTRING(COLUMNS_UPDATED(),(@ColumnID - 1) / 8 + 1, 1)) &
POWER(2, (@ColumnID - 1) % 8) =
POWER(2, (@ColumnID - 1) % 8)
SET @Columns = @Columns + CAST(@ColumnID AS VARCHAR) + ','
SET @ColumnID = @ColumnID + 1
END
PRINT 'Updated columns are :' + @Columns

---
UPDATE Test SET c2='D',c3=2000 WHERE c1=1
--Updated columns are :2,3
We see that the second and the third column were affected according to the INFORMATION_SCHEMA.COLUMNS ordered by ORDINAL_POSITION (check it out). It is not comfortable to see those numbers , but with a little effort you can get also name of columns from the INFORMATION_SCHEMA.COLUMNS.

I suggest you not to run/use that on the production server as it may hurt performance.