converting from mysql to mssql query

Question

I am trying to use codeigniter and MS-SQL. I have to use ci_sessions table to automatically store the sessions in the mssql server. The codeigniter have provided the ci_sessions table sql in user-guide as below:

CREATE TABLE IF NOT EXISTS  `ci_sessions` (
    session_id varchar(40) DEFAULT '0' NOT NULL,
    ip_address varchar(16) DEFAULT '0' NOT NULL,
    user_agent varchar(120) NOT NULL,
    last_activity int(10) unsigned DEFAULT 0 NOT NULL,
    user_data text NOT NULL,
    PRIMARY KEY (session_id),
    KEY `last_activity_idx` (`last_activity`)
);

. Here, I have to create the same table in MS SQL Server. What can be corresponding sql for MS SQL SERVER and also for below captcha sql table:

CREATE TABLE captcha (
    captcha_id bigint(13) unsigned NOT NULL auto_increment,
    captcha_time int(10) unsigned NOT NULL,
    ip_address varchar(16) default '0' NOT NULL,
    word varchar(20) NOT NULL,
    PRIMARY KEY `captcha_id` (`captcha_id`),
    KEY `word` (`word`)
);

How to create same table of above mysql query in MS SQL SERVER using sql query

msdn.microsoft.com/library/ms174979.aspx

dbrumann
– dbrumann

2012-03-05 07:10:16 +00:00
Commented Mar 5, 2012 at 7:10 — dbrumann
– dbrumann, Commented Mar 5, 2012 at 7:10

Devart · Accepted Answer · 2012-03-05 08:12:41Z

3

This is an example for the first table -

CREATE TABLE dbo.ci_sessions (
    session_id varchar(40) DEFAULT '0' NOT NULL,
    ip_address varchar(16) DEFAULT '0' NOT NULL,
    user_agent varchar(120) NOT NULL,
    last_activity int DEFAULT 0 NOT NULL,
    user_data varchar(max) NOT NULL, -- use varchar(max) instead of MySQL TEXT data type
    PRIMARY KEY (session_id)
)
GO

-- Simalate MySQL 'UNSIGNED' field option
ALTER TABLE dbo.ci_sessions ADD CONSTRAINT last_activity_unsigned
  CHECK (last_activity >= 0)
GO

CREATE INDEX last_activity_idx
  ON dbo.ci_sessions (last_activity)

Change 'dbo' schema with your schema.

Have a look at these references - CHECK Constraints, Data Types.

answered Mar 5, 2012 at 8:12

Devart

120k23 gold badges172 silver badges189 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

converting from mysql to mssql query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related