Tuesday, 24 January 2006

Cast vs Convert downunder

Kathi Kellenberger provides some pointers on converting in her Cast vs Convert post.


Being from down under my "converts" are usually,

SELECT CONVERT(varchar(12), GetDate(), 106)
------------
24 Jan 2006

and

SELECT CONVERT(varchar(30), GetDate(), 113)
------------------------------
24 Jan 2006 18:44:51:087

I find that using the character version of the month avoid any nastiness when different regional settings are involved.


Also, it should be noted that CAST is the ANSI/FIPS standard, probably why "CAST is usually preferred" ;)


Friday, 20 January 2006

Squeet.com is sweet

Thanks to Scott Cate I'm trying out the new service from Squeet.com. Check out Scott's short and sweet review here.


// Now playing: The Verve - Weeping Willow //


Tuesday, 17 January 2006

Monday, 16 January 2006

Finally a win !!!

Thanks to a fine effort by all the bowlers and fielders which was backed up by a huge batting display from Sealy (40 not out) and Rohan (51 retired), we were able to post my first win a captain for the Murrumbeena 4ths on Sunday. Hopefully this leads on to a few more wins.


Match report is available on ResultsVault


Wednesday, 11 January 2006

Change scripts generated by SQL Enterprise Manager

I had an occasion today where I needed to create a Primary key constraint on an existing table. The database this table resides in is a copy of a third-party database that is maintained and developed by the third-party. For this table the primary key consists of three fields. For some reason our copy had one of these fields allowing nulls.


As a shortcut, I usually make table changes in EM, grab the change script it generates and run it in Query Analyzer [guess I just don't like typing ;)]. I was a bit surprised to see the script EM created for this change. To change the nullability of the column it took the costly route of creating a new table, copying the data across, dropping the original table, renaming the new one and finally creating the primary key constraint.


Now this was far from ideal as this table holds in excess of 2Mil records and is constantly being read from a 24x7 website. When I ran the script in our testing environment, the website was not functional and I gave up waiting for the script to complete after 5 minutes.


Here is the rather lengthy script generated by EM, only the names have been changed to protect those who shouldn't be innocent.


BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.[Tablename]
DROP CONSTRAINT DF_Tablename_CreatedOn
GO
ALTER TABLE dbo.[Tablename]
DROP CONSTRAINT DF_Tablename_UpdatedOn
GO
CREATE TABLE dbo.[Tmp_Tablename] (
field1 varchar(20) NOT NULL,
field2 varchar(20) NOT NULL,
field3 varchar(24) NOT NULL,
field4 decimal(25, 10) NULL,
field5 decimal(25, 10) NULL,
field6 decimal(25, 10) NULL,
field7 decimal(25, 10) NULL,
field8 decimal(25, 10) NULL,
field9 decimal(25, 10) NULL,
field10 decimal(25, 10) NULL,
field11 decimal(25, 10) NULL,
field12 decimal(25, 10) NULL,
field13 decimal(25, 10) NULL,
field14 decimal(25, 10) NULL,
field15 bit NULL,
field16 bit NULL,
CreatedOn datetime NOT NULL,
UpdatedOn datetime NOT NULL,
zChar1 varchar(40) NULL,
zChar2 varchar(40) NULL,
zDeci1 decimal(25, 10) NULL,
zDeci2 decimal(25, 10) NULL
) ON [PRIMARY]
GO

ALTER TABLE dbo.[Tmp_Tablename] ADD CONSTRAINT
DF_Tablename_CreatedOn DEFAULT (getdate()) FOR CreatedOn
GO

ALTER TABLE dbo.[Tmp_Tablename] ADD CONSTRAINT
DF_Tablename_UpdatedOn DEFAULT (getdate()) FOR UpdatedOn
GO

IF EXISTS(SELECT * FROM dbo.[Tablename])
EXEC('INSERT INTO dbo.[Tmp_Tablename] (field1, field2, field3, field4, field5
, field6, field7, field8, field9, field10, field11, field12, field13, field14, field15
, field16, CreatedOn, UpdatedOn, zChar1, zChar2, zDeci1, zDeci2)
SELECT field1, field2, field3, field4, field5, field6, field7, field8, field9
, field10, field11, field12, field13, field14, field15, field16, CreatedOn, UpdatedOn
, zChar1, zChar2, zDeci1, zDeci2 FROM dbo.[Tablename] (HOLDLOCK TABLOCKX)')
GO

DROP TABLE dbo.[Tablename]
GO

EXECUTE sp_rename N'dbo.[Tmp_Tablename]', N'Tablename', 'OBJECT'
GO

CREATE CLUSTERED INDEX IX_Tablename_field1 ON dbo.[Tablename] (
field1
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX IX_Tablename_field3 ON dbo.[Tablename] (
field3
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE dbo.[Tablename] ADD CONSTRAINT
[PK_Tablename] PRIMARY KEY NONCLUSTERED (
field1,
field2,
field3
) ON [PRIMARY]
GO
COMMIT

And here is a much more simplified script to achieve the same result with any blocking resolved before the connection timed out.


ALTER TABLE dbo.[Tablename]
ALTER COLUMN field2 varchar(20) NOT NULL
GO

ALTER TABLE dbo.[Tablename] ADD CONSTRAINT
[PK_Tablename] PRIMARY KEY NONCLUSTERED
(
field1,
field2,
field3
) ON [PRIMARY]
GO


Sort of makes you wonder what the thinking is behind generating the change scripts. I know which one I'd rather run :)


I want one of these dual-core laptops

Dell Releases 17-inch Dual Core Power Laptop


inspiron9400.jpgSo, there are 365 days in the year, and Dell decides to choose today of all days to unveil a new laptop. Yeah, they are going to get a little coverage now, but they will be buried by MacWorld news and information throughout the day. I'll give Dell a little love. This is the Inspiron 9400, a 17-inch widescreen laptop that features high-end 256MB GeForce Go 7800 graphics. It also features a 5-in-1 media card reader, FireWire, six USB ports, analog monitor connection, DVI connection, ExpressCard slot, Gigabit Ethernet, stereo headphones/speakers and a 6-cell lithium-ion battery. This entertaining powerhouse will be selling for $2,600.


Dell 17-inch Widescreen Dual Core Laptop [BIOS]


Comment on this post
Related: Nvidia to do SLI for Lappys
Related: Ripcord Says Buh-Bye to USB Cables in 2007
Related: HP Goes for Gold at CES



[via Gizmodo]

Monday, 9 January 2006

Martyn leads Aussies to Twenty20 victory

Martyn leads Aussies to Twenty20 victory

A stunning innings of 96 from Damien Martyn helped Australia to a massive 95-run victory over South Africa in the historic Twenty20 match at the Gabba in Brisbane on Monday night.