Jen McCown

I don't think this QUITE qualifies as a code sin, but it's close. I'm reviewing code today, and I wanted to prove beyond a shadow of a doubt that this:

UPDATE T2

SET col1 = CASE

WHEN ( col1– @d ) > 0 THEN ( col1– @d)

ELSE 0

END

Is better than this:

UPDATE T1

SET col1 = CASE

WHEN ( col1– @d ) > 0 THEN ( col1– @d)

WHEN ( col1– @d) <= 0 THEN 0

END

In short: In a CASE statement, if the second WHEN is the ONLY other option possible, just use ELSE. Here's my proof… first load a buttload of data into your tables (this used up about 8Gb in my test DB):

DECLARE @Q BIGINT

SET @Q = 1

CREATE TABLE t1 ( col1 BIGINT )

CREATE TABLE t2 ( col1 BIGINT )

WHILE @Q < 1000000

BEGIN

INSERT INTO t1 VALUES ( 123456789123456789 )

INSERT INTO t1 VALUES ( 321654987321654987 )

INSERT INTO t1 VALUES ( 987654321987654321 )

INSERT INTO t1 VALUES ( 741852963741852963 )

INSERT INTO t2 VALUES ( 123456789123456789 )

INSERT INTO t2 VALUES ( 321654987321654987 )

INSERT INTO t2 VALUES ( 987654321987654321 )

INSERT INTO t2 VALUES ( 741852963741852963 )

SET @Q = @Q + 1

END

Now test the difference:

SET STATISTICS TIME ON

DECLARE @Q BIGINT ,

@d BIGINT

UPDATE T1 SET col1 = CASE

WHEN ( col1 – @d ) > 0 THEN ( col1 – @d )

WHEN ( col1 – @d ) <= 0 THEN 0

END

UPDATE T2

SET col1 = CASE WHEN ( col1 – @d ) > 0 THEN ( col1 – @d )

ELSE 0

END

My results were as follows (STATISTICS IO for this was identical, by the way): The first update took 11,684ms, and the second took 7,637ms, an improvement of 35%.  And yes, every little bit counts. 

-Jen McCown, http://www.MidnightDBA.com

 

 

I'm altering a table in my dev environment, mostly because I didn't capitalize one column and we're picky about these things.  So what I need to do (in order to keep existing dev data) is to copy the data to another table, drop and recreate the original table, and reinsert the data. 

CREATE TABLE [dbo].[FreshPrince_TEMP] (

[FreshPrinceID] int PRIMARY KEY CLUSTERED ,

[prince] int NULL,

[Carlton] int NULL

) ON [PRIMARY]

INSERT INTO FreshPrince_TEMP

SELECT FreshPrinceID,

Prince,

Carlton

FROM FreshPrince

DROP TABLE FreshPrince

CREATE TABLE [dbo].[FreshPrince] (

[FreshPrinceID] int IDENTITY(1,1) PRIMARY KEY CLUSTERED ,

[Prince] int NULL,

[Carlton] int NULL

) ON [PRIMARY]

Of course my table has an IDENTITY column (we also like simple surrogate keys). What happens when you try to insert data to FreshPrince?

Msg 8101, Level 16, State 1, Line 1

An explicit value for the identity column in table 'FreshPrince' can only be specified when a column list is used and IDENTITY_INSERT is ON.

So we have a problem: you can't insert values into an IDENTITY column because it auto generates its own values. Enter: IDENTITY_INSERT!

IDENTITY_INSERT just lets you disable the IDENTITY property so you can do what you need to do.  Use with caution, of course, as you're messing with the fundamental purpose of the IDENTITY property.  Oh, and remember to use your column list, or it won't work!  Let's watch it in action:

SET IDENTITY_INSERT FreshPrince ON

INSERT INTO FreshPrince (FreshPrinceID,

Prince,

Carlton) — column list is required!

SELECT FreshPrinceID,

Prince,

Carlton

FROM FreshPrince_TEMP 

Beautifully done. IDENTITY_INSERT has session scope, so only your session will be able to insert to the identity row explicitly. AND only one table in a session can have IDENTITY_INSERT ON at a time. Remember to SET IDENTITY_INSERT OFF, though, to be thorough. If you leave it ON you might forget in the same session, and mess yourself up. How's that for technical jargon?

-Jen McCown, http://www.MidnightDBA.com

Dearie me, we do indeed have some goodies this week.  Let us rock it freestyle, shall we?

First there's your chance to win a "free book of your choice from Apress".

This week's free eBook comes from ITPublishing:The Shortcut Guide to Subject Alternative Name Certificates. What a great pick for Gramma's Christmas stocking!  You know, if she's into alternative name certificates.

We got some (actually, 25) lovely and free cheat sheets for web devs.

Check out SQLChicken's SQL University!  It's all online, all free, and looks pretty cool (I'm going to get caught up this weekend, matter of fact…)

And if you're going to Kevin Kline's SQL Seminar in Orlando next week, you get a free copy of "Database Benchmarking".

Finally, your, uh, free SQL exam discounts can be found here. I'd attend SQL U first, if I were you!

-Jen McCown, http://www.MidnightDBA.com