Adding a Column Set

Question

Adding a Column Set

Steve Jones - SSC Editor

SSC Guru

Points: 737888
More actions
September 17, 2018 at 9:05 pm

#77520

Comments posted to this topic are about the item Adding a Column Set

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Stewart "Arturius" Campbell SSC Guru Points: 72710 More actions · Answer 1

Now that is interesting, thanks Steve.
good question

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

Carlo Romagnano SSC-Insane Points: 22745 More actions · Answer 2

Interesting!
Also, you don't need to drop the entire table, you can drop sparse columns and re-add them with the ALTER TABLE command

CREATE TABLE UserConfig ( UserConfigKey INT IDENTITY(1,1) NOT NULL CONSTRAINT UserConfigPK PRIMARY KEY , UserID INT )

ALTER TABLE UserConfig ADD Options XML COLUMN_SET FOR ALL_SPARSE_COLUMNS , IsActive BIT SPARSE , IsSubscriber BIT SPARSE , DefaultQuantity INT SPARSE

Shayn Thomas SSCertifiable Points: 5863 More actions · Answer 3

didn't know so learned something new, definitely not the answer i was expecting

Thanks for the question

---------------------------------------------------------------------------------------
The more you know, the more you know that you dont know

Nick Ryan SSCrazy Points: 2237 More actions · Answer 4

Sorry, I may be missing something but I was rather confused by this question because the question just reads "how do I add a column to this table" however the answers referred to XML data specifically.

Interesting topic though, learnt something new about SPARES data types.

Steve Jones - SSC Editor SSC Guru Points: 737888 More actions · Answer 5

Carlo Romagnano - Tuesday, September 18, 2018 12:53 AM
Interesting!
Also, you don't need to drop the entire table, you can drop sparse columns and re-add them with the ALTER TABLE command

That's true, though in either case, saving the data becomes a chore. I didn't think of just adding the sparse columns back and the column set, but that's cool

Steve Jones - SSC Editor SSC Guru Points: 737888 More actions · Answer 6

n.ryan - Tuesday, September 18, 2018 8:31 AM
Sorry, I may be missing something but I was rather confused by this question because the question just reads "how do I add a column to this table" however the answers referred to XML data specifically.
Interesting topic though, learnt something new about SPARES data types.

add a column set. That's a type of column, which is XML.

dstrickrott SSCommitted Points: 1951 More actions · Answer 7

Nice question and I learned something too. Thanks Steve.

Nick Ryan SSCrazy Points: 2237 More actions · Answer 8

Steve Jones - SSC Editor - Tuesday, September 18, 2018 11:21 AM
n.ryan - Tuesday, September 18, 2018 8:31 AM
Sorry, I may be missing something but I was rather confused by this question because the question just reads "how do I add a column to this table" however the answers referred to XML data specifically.
Interesting topic though, learnt something new about SPARES data types.
add a column set. That's a type of column, which is XML.

Did I misread it, or has it been amended?

Steve Jones - SSC Editor SSC Guru Points: 737888 More actions · Answer 9

misread. There are a series on column sets that are running across weeks.

Adding a Column Set

Cookies on SQLServerCentral