T-SQL nightmares (help needed in porting db schema from MySQL to SQL Server) -- sql server new users

sql server new users : T-SQL nightmares (help needed in porting db schema from MySQL to SQL Server)

Bit Byte
2/21/2007 12:00:00 AM

I seem to be spending too much time "Googling" for info on what should
be a a fairly straight forward task - porting an mySQL db schema over to
SQL Server.

The T-SQL documentation I have seen so far do not give any useful (i.e.
detailed enough) examples. My tables generally tend to have indices, and
both PKs and FKs so I need an example that shows all of this in one
table (no such luck in my search so far) - and it is causing me to waste
valuable time.

I have included one of the tables in my schema - I would be grateful if
someone can give me the equivalent T-SQL statements for recreating the
table declared below, in SQL Server using T-SQL. Once I receive that, I
should be able to work out the relevant mappings for the rest of my
schema then.

Here's a sample table from my MySQL schema:

CREATE TABLE IF NOT EXISTS country_def (
id int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
name varchar(64) NOT NULL,
gzone_id int unsigned NOT NULL,
ccy_id int unsigned NOT NULL,
regtyp_id int unsigned NOT NULL,
eu_mbr smallint unsigned NOT NULL default 0,
createdate timestamp NOT NULL default CURRENT_TIMESTAMP,
creator_id int unsigned NOT NULL,

INDEX (eu_mbr),
INDEX (gzone_id),
INDEX (ccy_id),
INDEX (regtyp_id),

FOREIGN KEY (gzone_id) references gzone_def(id),
FOREIGN KEY (ccy_id) references currency_def(id),
FOREIGN KEY (regtyp_id) references region_type_def(id)

) ENGINE=InnoDB ;

CREATE UNIQUE INDEX idx_ctry ON country_def (gzone_id, name(8)) ;

PS: Sorry for the cross posting - I'm not sure which would be the most

Uri Dimant
2/21/2007 12:00:00 AM

Bit Byte
IF OBJECT_ID('country_def') IS NULL
BEGIN

CREATE TABLE country_def (
id int NOT NULL identity(1,1) PRIMARY KEY,
name varchar(64) NOT NULL,
gzone_id int NOT NULL FOREIGN KEY references gzone_def(id),
ccy_id int NOT NULL FOREIGN KEY references
currency_def(id),
regtyp_id int NOT NULL FOREIGN KEY references
region_type_def(id),
eu_mbr smallint NOT NULL default 0,
createdate DATETIME NOT NULL default CURRENT_TIMESTAMP,
creator_id int NOT NULL,
)

CREATE INDEX my_eu_mbr ON country_def (eu_mbr),
CREATE INDEX my_ccy_id ON country_def (ccy_id),
CREATE INDEX regtyp_id ON country_def (regtyp_id)

CREATE UNIQUE INDEX idx_ctry ON country_def (gzone_id) ;

--Or you can create covering index

---CREATE INDEX my_cov_ind ON country_def(eu_mbr,ccy_id,regtyp_id)

END

[quoted text, click to view]

Stu
2/21/2007 5:27:06 AM

[quoted text, click to view]

Hope this helps:

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'country_def')
BEGIN
CREATE TABLE country_def (
id int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
name varchar(64) NOT NULL,
gzone_id int NOT NULL,
ccy_id int NOT NULL,
regtyp_id int NOT NULL,
eu_mbr smallint NOT NULL default 0,
createdate datetime NOT NULL default CURRENT_TIMESTAMP,
creator_id int NOT NULL,
FOREIGN KEY (gzone_id) references gzone_def(id),
FOREIGN KEY (ccy_id) references currency_def(id),
FOREIGN KEY (regtyp_id) references region_type_def(id)

)

CREATE UNIQUE NONCLUSTERED INDEX idx_ctry ON country_def (gzone_id,
name)

CREATE NONCLUSTERED INDEX ix_country_def_eu_mbr ON country_def
(eu_mbr)
CREATE NONCLUSTERED INDEX ix_country_def_gzone_id ON country_def
(gzone_id)
CREATE NONCLUSTERED INDEX ix_country_def_ccy_id ON country_def
(ccy_id)
CREATE NONCLUSTERED INDEX ix_country_def_regtyp_id ON
country_def(regtyp_id)

END

Obviously, you need to have the foreign tables created before you can
run this.

Stu

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server new users

home · blog · groups

Questions? Comments? Contact the dn