Difference between revisions of "Microsoft SQL Server"
(AUTOINCREMENT vs IDENTITY) |
(IGNORE) |
||
| Line 12: | Line 12: | ||
|- | |- | ||
|SELECT * FROM foo LIMIT x,y||''not sure how to implement yet'' | |SELECT * FROM foo LIMIT x,y||''not sure how to implement yet'' | ||
| + | |- | ||
| + | |INSERT IGNORE INTO foo (foo_id,bar) VALUES ('1','xyz') | ||
| + | |IF NOT EXISTS (SELECT * FROM foo WHERE foo_id = '1') INSERT INTO foo (foo_id,bar) VALUES ('1','xyz') | ||
|- | |- | ||
|IF(cond,trueVal,falseVal)||CASE WHEN cond THEN trueVal ELSE falseVal END | |IF(cond,trueVal,falseVal)||CASE WHEN cond THEN trueVal ELSE falseVal END | ||
| Line 39: | Line 42: | ||
*[http://msdn.microsoft.com/en-us/library/ms188783.aspx MSSQL reference] | *[http://msdn.microsoft.com/en-us/library/ms188783.aspx MSSQL reference] | ||
*[http://doc.ddart.net/mssql/sql70/ca-co_1.htm Type casting] | *[http://doc.ddart.net/mssql/sql70/ca-co_1.htm Type casting] | ||
| + | *[http://msdn.microsoft.com/en-us/library/ms187752.aspx TransactSQL datatypes] | ||
Revision as of 12:08, 10 May 2008
Contents
Syntax differences between MySQL and MSSQL
| MySQL | MSSQL |
|---|---|
| AUTO_INCREMENT | IDENTITY(1,1) |
| bool | bit |
| [UN]SIGNED | not valid |
| SELECT * FROM foo LIMIT x | SELECT TOP x * FROM foo |
| SELECT * FROM foo LIMIT x,y | not sure how to implement yet |
| INSERT IGNORE INTO foo (foo_id,bar) VALUES ('1','xyz') | IF NOT EXISTS (SELECT * FROM foo WHERE foo_id = '1') INSERT INTO foo (foo_id,bar) VALUES ('1','xyz') |
| IF(cond,trueVal,falseVal) | CASE WHEN cond THEN trueVal ELSE falseVal END |
| SHOW TABLES | SELECT * FROM INFORMATION_SCHEMA.TABLES |
| ENUM | not natively supported, change to text |
MSSQL Variables
| @@VERSION | Server version information |
| @@IDENTITY | Last inserted row |
| @@ERROR | Last error number |
AUTOINCREMENT vs IDENTITY
MySQL style AUTOINCREMENT columns are inplemented in MSSQL using IDENTITY(x,y) where x is the initial value and y is the amount to add on each insert. The last value resulting from an insert into an IDENTITY column is stored in the @@IDENTITY variable. These kinds of columns are usually used as primary keys and are therefore assigned the NOT NULL property.
In MySQL the standard way of inserting data into rows exhibiting AUTOINCREMENT columns is simply to use a NULL value which will be ignored. In MSSQL however assigning a NULL to an IDENTITY column is not allowed, instead the best way is not to include those items in the list of columns to be updated at all.
To get round this in the MediaWiki MSSQL layer, I've modified the insert wrapper in the DatabaseMssql class to check if the primary key is used in the insert and remove it if so. It checks this by assuming that the primary key will be of the same name as the table but with _id on the end, and that it will the first item in the list of columns to update.
