MSSQL equivalent for MYSQL

Question

MSSQL equivalent for MYSQL

apat

SSCertifiable

Points: 5929
More actions
September 9, 2010 at 8:44 am

#387755

Hello All,
Can someone please help me with the MSSQL equivalent of below MYSQL code?
alter table TestTable change column Col1
Col1 set('ab','cd','ef','gh','ij','kl');

Viewing 15 posts - 1 through 15 (of 16 total)

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

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 1

What are you actually wanting the alter statement to do? Change the name of the column? Change the default value? what????

Andrew SQLDBA

apat SSCertifiable Points: 5929 More actions · Answer 2

I dont really know what this statement means. I would like to convert this statement to SQL and apply it on one of my tables. Got this from the user and he is not available to discuss now.

Looks like it is applying check constraint for the values. I want to confirm if this is true.

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 3

I suggest that you read the MySQL documentation. Because if you do not know what the code is doing, how would you expect someone here to know, or to be able to help you?

You can also read the SQL Books Online about "Alter Table" statement.

Andrew SQLDBA

apat SSCertifiable Points: 5929 More actions · Answer 4

I didn't know and thought of taking help from people here who are experts and know much more than I do, that was the only reason I thought of posting... If I knew it there was no need to post at all... and I don't know doesn't mean I shall assume that others don't know as well.

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 5

I am only saying that :

Very few people on here would know MySQL. Hence the name of this site. "SQLServer"

The syntax is totally different.

I could just as easily go online and read the MySQL documentation and come back and tell you what the syntax means. But so can you. That is all that I can recommend.

Andrew SQLDBA

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

I suggest speak to the user, find out what he wants, then if you don't know how to implement it ask here.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

apat SSCertifiable Points: 5929 More actions · Answer 7

Thanks Andrew. Yes I know this is SQL Server site and I have been a member here for some time now. I must appriciate all the help I have got from fellow members and solve many of my problems quickly. I just thought IF someone knows and as usual give me a quick reply that will solve my problem.

Anyway, I will read the documentation. Thanks for the help.

Steve Jones - SSC Editor SSC Guru Points: 736222 More actions · Answer 8

From what I see in the MYSQL reference.

ALTER TABLE X CHANGE col1 col2

Does a name change of the column. So col1 is renamed col2.

http://php.about.com/od/learnmysql/p/alter_table.htm

The SET is something I don't see as part of the ALTER. Not sure what happens with it, but I'll ask a few people.

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

Just got this from a friend: http://dev.mysql.com/tech-resources/articles/mysql-set-datatype.html

He says SET is generally considered a bad idea. It only allow SET datatypes to be stored. Not sure what this would be in SQL Server. It's like an "IN" clause, but stored in a datatype. I guess some type of string that you would have to parse with a UDF.

KEKline SSC Enthusiast Points: 193 More actions · Answer 10

If you'll allow me to put in a plug for my book, SQL in a Nutshell, the ALTER TABLE reference for MySQL tells us that ALTER TABLE ... CHANGE COLUMN allows you to change the name or data type of a column. SET is one of MySQL's weird and unique data types that is kind'a a cross between a CHAR data type and a built-in CHECK constraint. Does that make any sense?

-Kevin Kline

Twitter @KEKline

More content at http://KevinEKline.com

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

So the SQL Server equivalent would be a char or varchar data type and a check constraint with a whole bunch of OR conditions in it?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Jones - SSC Editor SSC Guru Points: 736222 More actions · Answer 12

Kevin's book: SQL in a Nutshell (recommended).

It does sound like a varchar and a complex constraint from a DDL perspective. A nightmare from a DML perspective.

UMG Developer SSChampion Points: 13482 More actions · Answer 13

GilaMonster (9/9/2010)
So the SQL Server equivalent would be a char or varchar data type and a check constraint with a whole bunch of OR conditions in it?

Not exactly from reading the information in the link it would be like creating a lookup table and a cross-walk table with a FK link. (Since the field can contain one, two, ..., or all of the possible values all at the same time.)

Or like someone else mentioned you could use a varchar field that is managed by a UDF, or set of UDFs.

In any case I don't think there is an easy way to replicate this MYSQL functionality in SQL Server.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

UMG Developer (9/10/2010)
Not exactly from reading the information in the link it would be like creating a lookup table and a cross-walk table with a FK link. (Since the field can contain one, two, ..., or all of the possible values all at the same time.)

Yuk. And that's a feature?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass