How to rename a column in a SQL Server table without using the designer

If you have a table and you want to rename a column without using the designer, how can you do that?

First create this table

CREATE TABLE TestColumnChange(id int)
INSERT TestColumnChange VALUES(1)


SELECT * FROM TestColumnChange

As you can see the select statement returns id as the column name. you can use ALTER table ALTER Column to change the dataype of a column but not the name.

Here is what we will do, execute the statement below

EXEC sp_rename 'TestColumnChange.[id]', 'value', 'COLUMN'


Now do the select, you will see that the column name has changed

SELECT * FROM TestColumnChange

That is it, very simple

Cannot resolve collation conflict for equal to operation.

You set up your linked server, you write a query which joins two tables, you execute the query and the error message is this
Cannot resolve collation conflict for equal to operation


What does this mean? This mean that the collation on the two tables is different

Let's look at an example. Le's create two tables, onme with Traditional_Spanish_CI_AI collation and one with the default. The default collation for me is SQL_Latin1_General_CP1_CI_AS.


CREATE TABLE #Foo (SomeCol varchar(50) COLLATE Traditional_Spanish_CI_AI)
CREATE TABLE #Foo2 (SomeCol varchar(50))


INSERT #Foo VALUES ('AAA')
INSERT #Foo VALUES ('BBB')
INSERT #Foo VALUES ('CCC')
INSERT #Foo VALUES ('DDD')

INSERT #Foo2 VALUES ('AAA')
INSERT #Foo2 VALUES ('BBB')
INSERT #Foo2 VALUES ('CCC')
INSERT #Foo2 VALUES ('DDD')

Now run this query and you will get the error message

SELECT * FROM #Foo F1
JOIN #Foo2 f2 ON f1.SomeCol = f2.SomeCol


Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.

Now add COLLATE Traditional_Spanish_CI_AI to #Foo2 SomeCol

SELECT * FROM #Foo F1
JOIN #Foo2 f2 ON f1.SomeCol = f2.SomeCol COLLATE Traditional_Spanish_CI_AI

That works, if you add COLLATE SQL_Latin1_General_CP1_CI_AS to #Foo SomeCol that will work also


SELECT * FROM #Foo F1
JOIN #Foo2 f2 ON f1.SomeCol COLLATE SQL_Latin1_General_CP1_CI_AS = f2.SomeCol


If you want to know what these collations mean then run the following query (yes that is not a typo it is indeed ::).

SELECT *
FROM ::fn_helpcollations()
WHERE name in('SQL_Latin1_General_CP1_CI_AS','Traditional_Spanish_CI_AI')

Traditional_Spanish_CI_AI
Traditional-Spanish,
case-insensitive,
accent-insensitive,
kanatype-insensitive,
width-insensitive

SQL_Latin1_General_CP1_CI_AS
Latin1-General,
case-insensitive,
accent-sensitive,
kanatype-insensitive,
width-insensitive for Unicode Data,
SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data

Fun With SQL Server Update Triggers

Below is some code that will show how to test for updated field values in an update trigger. As you can see the IF UPDATE (field) is true even when the values don’t change. Another thing to keep in mind is that if a value changes from NULL to something else and vice-versa, and you are comparing deleted and inserted tables without using COALESCE or ISNULL it won’t return those rows. Run the code below to see what I mean


CREATE TABLE TestTrigger (TestID INT identity,
name VARCHAR(20),
value DECIMAL(12,2) ,
CONSTRAINT chkPositiveValue CHECK (value > 0.00) )


INSERT INTO TestTrigger
SELECT 'SQL',500.23


CREATE TRIGGER trTest
ON TestTrigger
FOR UPDATE
AS

IF @@ROWCOUNT =0
RETURN

IF UPDATE(value)
BEGIN
SELECT '1', * FROM deleted d JOIN inserted i ON d.testid =i.testid
SELECT '2',* FROM deleted d JOIN inserted i ON d.testid =i.testid
AND i.value <> d.value
SELECT '3',* FROM deleted d JOIN inserted i ON d.testid =i.testid
AND COALESCE(i.value,-1) <> COALESCE(d.value,-1)
END
GO

--Let's update the value to 100
UPDATE TestTrigger SET value = 100 WHERE testid =1
--we get back all 3 rows


--Let's run the same statement
UPDATE TestTrigger SET value = 100 WHERE testid =1
--we get back the first row


--Let's really update
UPDATE TestTrigger SET value = 200 WHERE testid =1
--we get back all 3 rows

--Let's update with NULL
UPDATE TestTrigger SET value =NULL WHERE testid =1
--we get back rows 1 and 3, row 2 is not returned because it can't compare it

--Let's update with NULL again
UPDATE TestTrigger SET value =NULL WHERE testid =1
--we get back row 1

--Let's update with 300
UPDATE TestTrigger SET value =300 WHERE testid =1
--we get back rows 1 and 3, row 2 doesn't return because it can't compare NULL to 300

--Let's update with 500
UPDATE TestTrigger SET value =500 WHERE testid =1
--we get back all 3 rows