mrkeck Sharing the Love

29Jun/110

MSSQL to MySQL SQL Commands

replace(convert(varchar, NOW(), 101),'/','') to date_format(NOW(),'%m%d%Y')  = 06292011

'E-'+cast(stub_id as varchar(15)) to concat('E-',stub_id) = E-12345

GETDATE() to NOW() = Current Timestamp

Note: MySql dates are in 2011-06-29 form

SELECT TOP 1 * from table1 to SELECT * from table1 LIMIT 1 = Return one row

I did a conversion recently and these 4 are the things I ran into the most.

Filed under: MSSQL, mySQL No Comments
12May/110

MSSQL Performance

MSSQL 2000
Enterprise Manager=>Tools=>SQL Profiler=>New Trace
Let that run during as many combinations of normal activity so it can get a handle on all the ways you interact with the db. Once you feel you have enough(I usually let it run for an hour), stop it and save as a trace file.

Next open Index Tuning Wizard. Note: Everytime I try to open from SQL Profiler it says it can't connect.
Go to Enterprise Manager=>Tools=>Wizards=>Management=>Index Tuning Wizard
Load the Trace file you just made. Set what settings make sense to you. It will analyze the file and tell you what indexes need to be made. And it will also put them in place for you and tell you how much faster it will be. I did it today and it said it will be 43% faster. I'm pretty stoked!

Tagged as: , , , No Comments
12May/110

SQL Optimization

MSSQL
Ran this query in a table with indexes and 6 million records
select top 50 id from table1 where isBatched is null
Took 13 seconds.

select top 50 id from table1 where isBatched is null order by id desc
Took 0 seconds

All the records that are not batched are at the end of the table so it was able to do soooo much faster.
This appears to work this way on anything where all the results are at the end of the table.

Tagged as: , , No Comments
6May/110

MSSQL Date Only from Timestamp

Check it out

CAST(FLOOR( CAST( GETDATE() AS FLOAT ) )AS DATETIME)

Tagged as: No Comments