11

I would like alter my table and add SPARSE option to all fields that contain a lot of NULL values. What is the right syntax for this ALTER TABLE command?

jrara
  • 16,239
  • 33
  • 89
  • 120

3 Answers3

13

The other answers work, but you can also get away with:

ALTER TABLE #foo ALTER COLUMN bar ADD SPARSE;

This way you don't have to look up the column's type or nullability.

Atario
  • 1,371
  • 13
  • 24
11
CREATE TABLE #Foo
(
X INT NULL,
Y INT NULL
)


ALTER TABLE #Foo ALTER COLUMN Y INT  SPARSE NULL 

ALTER TABLE #Foo ALTER COLUMN X INT SPARSE NULL
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • Is there a way to do this in a single ALTER TABLE statement? – jrara Sep 30 '11 at 11:32
  • @jrara - No. [The `ALTER TABLE` grammar](http://msdn.microsoft.com/en-us/library/ms190273.aspx) allows you to add multiple columns but only alter one of them for some reason. – Martin Smith Sep 30 '11 at 11:35
1
ALTER TABLE Xtable
ADD myCol int sparse null
JStead
  • 1,710
  • 11
  • 12