MS SQL Identity Column

IDENTITY in MS SQL is like auto_increment in MySQL.

Create table

CREATE TABLE tablename (id int IDENTIY, title varchar(20))

Inserting data

INSERT INTO tablename (title) VALUES ('title')

Find the Identity Value that was inserted

INSERT INTO tablename (title) VALUES ('title')
SELECT SCOPE_IDENTITY() AS LastIdentity

Find the Identity Value for a particular table

SELECT IDENT_CURRENT('tablename') AS LastIdentity

Inserting Explicit Values into an Identity Column

SET IDENTITY_INSERT tablename ON
INSERT INTO tablename (id, title) VALUES(2, 'newtitle')
SET IDENTITY_INSERT tablename OFF

MSSQL and MySQL

Current Date and Time

MS: SELECT GETDATE()
MY: SELECT NOW()

Optionally: Use CURDATE() for the date only.

Limiting Results

MS: SELECT TOP 10 * FROM table WHERE id = 1
MY: SELECT * FROM table WHERE id = 1 LIMIT 10

Date Field Default Value

MSSQL: DATETIME DEFAULT GETDATE()
MYSQL: DATETIME fields cannot have a default value, i.e. "GETDATE()"

You must use your INSERT statement to specify CURDATE() for the field.
Optionally: Use datatype TIMESTAMP DEFAULT CURRENT_TIMESTAMP

Character Length

MSSQL: LEN()
MYSQL: CHARACTER_LENGTH() Aliases: CHAR_LENGTH(), LENGTH()

Character Replace

MSSQL: REPLACE() works case insensitively
MYSQL: REPLACE() works case sensitively

Trim Functions

MSSQL: LTRIM() and RTRIM()
MYSQL: TRIM()

String Concatenation

MSSQL: CONCATENATION USING + (Does not automatically cast operands to compatible types)
MYSQL: CONCAT(string, string), which accepts two or more arguments.

(Automatically casts values into types which can be concatenated)

Auto Increment Field Definition

MSSQL: tablename_id INT IDENTITY PRIMARY KEY
MYSQL: tablename_id INTEGER AUTO_INCREMENT PRIMARY KEY

Get a List of Tables

MSSQL: SP_TABLES
MYSQL: SHOW TABLES

Get Table Properties

MSSQL: HELP tablename
MYSQL: DESCRIBE tablename

Get Database Version

MSSQL: SELECT @@VERSION
MYSQL: SELECT VERSION()

Recordset Paging

MSSQL: Recordset paging done by client side-ADO (very involved)
MYSQL: Add to end of SQL: "LIMIT " & ((intCurrentPage-1)*intRecsPerPage) & ", " & intRecsPerPage

LIMIT: The first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1).

Get ID of Newest Inserted Record

MSSQL: SET NOCOUNT ON; INSERT INTO...; SELECT id=@@IDENTITY; SET NOCOUNT OFF;
MYSQL: Two step process:
1. Execute your statement: objConn.Execute("INSERT INTO...")
2. Set objRS = objConn.Execute("SELECT LAST_INSERT_ID() AS ID")

Get a Random Record

MSSQL: SELECT TOP 1 * FROM Users ORDER BY NEWID()
MYSQL: SELECT * FROM Users ORDER BY RAND() LIMIT 1

Generate a Unique GUID

MSSQL: SELECT NEWID()
MYSQL: SELECT UUID()